sqlserverblobstream.cs

neatupload

        /// <param name="dataColumnName"></param>
        /// <param name="identity"></param>
        /// <param name="fileNameColumnName"></param>
        /// <param name="MIMETypeColumnName"></param>
        /// <param name="access"></param>
        public SqlServerBlobStream(string connectionString, string tableName, string dataColumnName, int identity, string fileNameColumnName, string MIMETypeColumnName, FileAccess access)
        {            
            _identity = identity;
            _connection = new SqlConnection(connectionString);
            OpenBlob(tableName, dataColumnName, null, identity, fileNameColumnName, MIMETypeColumnName);
            GenerateReadCommand(tableName, dataColumnName, null);
        }

        /// <summary>
        /// Simple constructor mainly for reading, using stored procedures in the database
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="identity"></param>
        /// <param name="openProcedure"></param>
        /// <param name="readProcedure"></param>
        /// <param name="access"></param>
        public SqlServerBlobStream(string connectionString, int identity, string openProcedure, string readProcedure, FileAccess access)
        {
            _identity = identity;
            _connection = new SqlConnection(connectionString);
            OpenBlob(null, null, openProcedure, identity, null, null);
            GenerateReadCommand(null, null, readProcedure);
        }


        /// <summary>
        /// Initializor; creates a SqlServerBlobStream agains given database with given settings
        /// </summary>
        /// <param name="ConnectionString">Connection string agains the database to use</param>
        /// <param name="ConnectionName">Name of ConnectionString from web.config to use to cennct to database</param>
        /// <param name="CreatorProcedure"><c>Optional</c> Procedure to use when creating the file-containing row in the database</param>
        /// <param name="TableName">Name of table to which we write the streamed data</param>
        /// <param name="DataColumnName">Name of table column to store data into (usually of type <c>Text</c> or <c>Image</c>)</param>
        /// <param name="PartialFlagColumnName"><c>Optional</c> Name of table column to store a "partial"-flag in; while uploading this will be set to 1 (or set by CreatorProcedure), when done it will be set to 0</param>
        /// <param name="FileNameColumnName"><c>Optional</c> Name of table column where the name of the uploaded file will be stored</param>
        /// <param name="FileName"><c>Optional</c> Name of the uploaded file</param>
        /// <param name="MIMETypeColumnName"><c>Optional</c> Name of table column where the MIME-type of the uploaded file will be stored</param>
        /// <param name="MIMEType"><c>Optional</c> MIME-type of the uploaded file</param>
        private void Initialize(string connectionString, string tableName, string dataColumnName, string partialFlagColumnName, string fileNameColumnName, string fileName, string mimeTypeColumnName, string mimeType,
            string createProcedure, string openProcedure, string writeProcedure, string readProcedure, string cleanupProcedure, string renameProcedure, string storeHashProcedure, string deleteProcedure)
        {
            _fileName = fileName;
            _MIMEType = mimeType;

            _connection = new SqlConnection(connectionString);

            if (createProcedure != null && createProcedure.Length > 0)
            {
                SqlCommand createCommand = _connection.CreateCommand();
                createCommand.CommandType = System.Data.CommandType.StoredProcedure;
                createCommand.CommandText = createProcedure;

                //Assume the procedure accepts the parameters "FileName" and "MIMEType"
                AddWithValue(createCommand.Parameters, "@FileName", fileName);
                AddWithValue(createCommand.Parameters, "@MIMEType", mimeType);



                SqlParameter PointerOutParam = createCommand.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
                PointerOutParam.Direction = ParameterDirection.Output;
                SqlParameter IdentityOutParam = createCommand.Parameters.Add("@Identity", SqlDbType.Int);
                IdentityOutParam.Direction = ParameterDirection.Output;

                //Create the table row for the image data
                _connection.Open();
                try { createCommand.ExecuteNonQuery(); }
                finally { _connection.Close(); }

                _pointer = (byte[])PointerOutParam.Value;
                _identity = (int)IdentityOutParam.Value;
                _offset = 0;
            }
            else
                CreateBlob(tableName, dataColumnName, partialFlagColumnName, fileNameColumnName, fileName, mimeTypeColumnName, mimeType);
      

            GenerateCleanupCommand(tableName, dataColumnName, partialFlagColumnName, cleanupProcedure);
            GenerateDeleteCommand(tableName, deleteProcedure);
            GenerateWriteCommand(tableName, dataColumnName, writeProcedure);

            _isOpen = true;
        }


        /// <summary>Flush is not implemented since all data is sent through immediatly</summary>
        public override void Flush() { }

        private long _length = 0;
        public override long Length { get { return _length; } }

        /// <summary>Current filepointer position in file</summary>
        public override long Position
        {
            get { return _offset; }
            set { Seek(value, SeekOrigin.Begin); }
        }

        /// <summary>
        /// Moves filepointer to given location in file
        /// </summary>
        /// <param name="offset">New position of file pointer relative to the origin</param>
        /// <param name="origin">Origin to seek from</param>
        /// <returns></returns>
        public override long Seek(long offset, SeekOrigin origin) 
        {
            switch (origin)
            {
                case SeekOrigin.Begin: _offset = offset; break;
                case SeekOrigin.Current: _offset += offset; break;
                case SeekOrigin.End: _offset = this.Length + offset; break;
            }
            if (_offset < 0) _offset = 0;
            if (_offset > this.Length) _offset = this.Length;
            return _offset;
        }

        public override void SetLength(long value)
        {
            //if (!_isOpen) return new ObjectDisposedException("SqlServerBlobStream");
            throw new NotSupportedException("The method or operation 'SqlServerBlobStream.SetLength' is not implemented.");
        }

        public override int Read(byte[] buffer, int offset, int count)
        {
            //Implement standard stream behaviour
            if (!_isOpen) throw new ObjectDisposedException("SqlServerBlobStream");
            if (buffer == null) throw new ArgumentNullException("buffer");
            if (offset < 0) throw new ArgumentOutOfRangeException("offset");
            if (count < 0) throw new ArgumentOutOfRangeException("count");
            if (buffer.Length < offset + count) throw new ArgumentException("offset + count does not fit into buffer");

            //Make sure count does not take us outside the blob
            if (_offset + count > _length) 
                count = (int)(_length - _offset);  //Instead just read to the end

            if (count == 0) return 0;   //No bytes to read...
            
            try
            {
                /*
                _readCommand.Parameters["@Bytes"].Value = buffer;
                _readCommand.Parameters["@Bytes"].Size = count;
                _readCommand.Parameters["@Bytes"].Offset = offset;
                */
                _readCommand.Parameters["@Offset"].Value = _offset;
                _readCommand.Parameters["@Size"].Value = count;

                //Local buffer.
                //TODO: See if I can skip this somehow... hopefully the sqlclient namespace has some support for writing to existing buffers
                byte[] data;

                _connection.Open();
                //try { _readCommand.ExecuteNonQuery(); }
                try { data = (byte[])_readCommand.ExecuteScalar(); }
                finally { _connection.Close(); }


                //Just to make sure we got the right amount of bytes
                count = data.Length;
                
                System.Buffer.BlockCopy(data, 0, buffer, offset, count);

                _offset += count;
                return count;

            }
            catch (Exception ex)
            {
                throw new IOException("IO failed in SqlServerBlobStream.Read", ex);
            }
        }

        public override void Write(byte[] buffer, int offset, int count)
        {
            //Make sure stream is open
            if (!_isOpen) throw new ObjectDisposedException("SqlServerBlobStream");
            //Test standard stream test
            if (buffer.Length < offset + count) throw new ArgumentException("offset + count does not fit into buffer");
            if (buffer == null) throw new ArgumentNullException("buffer");


            //Write to database
            if (count == 0) return;
            try
            {
                _writeCommand.Parameters["@Bytes"].Value = buffer;
                _writeCommand.Parameters["@Bytes"].Size = count;
                _writeCommand.Parameters["@Bytes"].Offset = offset;

                _connection.Open();
                try { _writeCommand.ExecuteNonQuery(); }
                finally { _connection.Close(); }

                //Mark this stream as modified
                _modified = true;

                _writeCommand.Parameters["@Delete"].Value = 0; //Not sure if this is really neccessary...
                _offset += count;
                _writeCommand.Parameters["@Offset"].Value = _offset;
            }
            catch (Exception ex)
            {
                throw new IOException("Sql IO failed in SqlServerBlobStream.Write", ex);
            }

            //Update length if this operation made the blob larger
            _length = Math.Max(_offset, _length);
        }

        /// <summary>
        /// Closes the current stream and releases any resources
        /// </summary>
        public override void Close()
        {
            if (_isOpen)
            {
                //If we have a cleanup-command run it now
                if (_modified &&_cleanupCommand!=null)
                {
                    _connection.Open();
                    try { _cleanupCommand.ExecuteNonQuery(); }
                    finally { _connection.Close(); }
                }
                _isOpen = false;
            }
        }

        /// <summary>
        /// Reopens the stream if it has been closed
        /// </summary>
        public void ReOpen()
        {
            if (_disposed) throw new ObjectDisposedException("SqlServerBlobStream");
            _isOpen = true;
        }

        /// <summary>
        /// Frees all resources used by stream
        /// </summary>
        public new void Dispose()
        {
            if (!_disposed)
            {
                _disposed = true;
                Close();

                if (_writeCommand != null) _writeCommand.Dispose();
                if (_readCommand != null) _readCommand.Dispose();
                if (_cleanupCommand != null) _cleanupCommand.Dispose();
                if (_deleteCommand != null) _deleteCommand.Dispose();

                _connection.Dispose();
            }
        }

        /// <summary>
        /// Deletes the table row containing the current stream from the database
        /// </summary>
        public void Delete()
        {
            if (_deleteCommand == null) throw new Exception("Not configured to delete posts");
            _connection.Open();
            try { _deleteCommand.ExecuteNonQuery(); }
            finally { _connection.Close(); }
            _isOpen = false;
        }
        
        internal static SqlParameter AddWithValue(SqlParameterCollection parameters, string paramName, int paramValue)
        {
#if NETv2_0
            return parameters.AddWithValue(paramName, paramValue);
#else
            return parameters.Add(paramName, paramValue);
#endif
        }
        
        internal static SqlParameter AddWithValue(SqlParameterCollection parameters, string paramName, string paramValue)
        {
#if NETv2_0
            return parameters.AddWithValue(paramName, paramValue);
#else
            return parameters.Add(paramName, paramValue);
#endif
        }
                                                         
    }
}