sqlserverblobstream.cs

neatupload

/*
 
SqlServerUploader - an addon to NeatUpload to allow uploading files to stream
directly into a database.
Copyright (C) 2006  Joakim Wennergren ([e-mail])

NeatUpload is an HttpModule and User Controls for uploading large files.
NeatUpload is created and maintained by Dean Brettle (www.brettle.com)

This library is free software; you can redistribute it and/or
modify it under the terms of the GNU Lesser General Public
License as published by the Free Software Foundation; either
version 2.1 of the License, or (at your option) any later version.

This library is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
Lesser General Public License for more details.

You should have received a copy of the GNU Lesser General Public
License along with this library; if not, write to the Free Software
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
*/

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Hitone.Web.SqlServerUploader
{
    /// <summary>
    /// Stream that writes directly into an SQL Server "Image"-column 
    /// </summary>
    public class SqlServerBlobStream : Stream
    {
        public override bool CanRead { get { return _access == FileAccess.ReadWrite || _access == FileAccess.Read; } }
        public override bool CanSeek { get { return true; } }
        public override bool CanWrite { get { return _access == FileAccess.ReadWrite || _access == FileAccess.Write; } }

        private SqlConnection _connection = null;
        private long _offset = 0;
        private int _identity = -1;
        private byte[] _pointer = null;

        private FileAccess _access = FileAccess.ReadWrite;
        private bool _isOpen = false;
        private bool _modified = false;
        private bool _disposed = false;

        // A set of SqlCommands, prepared for usage from the beginning
        private SqlCommand _writeCommand = null;
        private SqlCommand _readCommand = null;
        private SqlCommand _cleanupCommand = null;
        private SqlCommand _deleteCommand = null;

        private string _fileName = null;
        private string _MIMEType = null;
        public string FileName { get { return _fileName; } }
        public string MIMEType { get { return _MIMEType; } }

        /// <summary>
        /// Gets the identity column value for the created row in the database
        /// </summary>
        public int Identity { get { return _identity; } }


        /// <summary>
        /// Main constructor; creates a SqlServerBlobStream agains given database using given settings
        /// </summary>
        /// <param name="ConnectionString">Connections string agains the database to use</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>
        public SqlServerBlobStream(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)
        {
            Initialize(ConnectionString, TableName, DataColumnName, PartialFlagColumnName, FileNameColumnName, FileName, MIMETypeColumnName, MIMEType,
                         createProcedure, openProcedure, writeProcedure, readProcedure, cleanupProcedure, renameProcedure, storeHashProcedure, deleteProcedure);

        }

        /// <summary>
        /// Opens an exising blob for read/write access
        /// </summary>
        /// <param name="tableName">Name of table from which to retreive data</param>
        /// <param name="dataColumnName">Name of column from which to retreive data</param>
        /// <param name="identity">Identity of row to open</param>
        /// <param name="fileNameColumnName">If specified the filename will be retrieved from the database</param>
        /// <param name="MIMETypeColumnName">If specified the MIMEType will be retrieved from the database</param>
        private void OpenBlob(string tableName, string dataColumnName, string openProcedure, int identity, string fileNameColumnName, string MIMETypeColumnName)
        {
            SqlCommand openSqlCommand = _connection.CreateCommand();

            if (openProcedure != null && openProcedure.Length > 0) //We should use a stored procedure
            {
                openSqlCommand.CommandType = CommandType.StoredProcedure;
                openSqlCommand.CommandText = openProcedure;
            }
            else //We should generate SQL Queries
            {
                if (openProcedure != null) openProcedure = null; //Make sure the openProcedure parameter is null so future tests are simpler

                openSqlCommand.CommandText = string.Format("SELECT @Pointer = TEXTPTR([{0}]), @Size=datalength([{0}]){2}{3} FROM [{1}] WHERE $IDENTITY = @Identity", dataColumnName, tableName,
                    fileNameColumnName != null ? ",@FileName=" + fileNameColumnName : string.Empty,
                    MIMETypeColumnName != null ? ",@MIMEType=" + MIMETypeColumnName : string.Empty);
            }
            AddWithValue(openSqlCommand.Parameters, "@Identity", identity);
            openSqlCommand.Parameters.Add("@Pointer", SqlDbType.Binary, 16).Direction = ParameterDirection.Output;
            openSqlCommand.Parameters.Add("@Size", SqlDbType.Int).Direction = ParameterDirection.Output;

            if (openProcedure != null || fileNameColumnName != null) openSqlCommand.Parameters.Add("@FileName", SqlDbType.VarChar, 255).Direction = ParameterDirection.Output;
            if (openProcedure != null || MIMETypeColumnName != null) openSqlCommand.Parameters.Add("@MIMEType", SqlDbType.VarChar, 255).Direction = ParameterDirection.Output;

            
            //Try to open the blob
            _connection.Open();
            try { openSqlCommand.ExecuteNonQuery(); }
            finally { _connection.Close(); }

            //Store pointer and length
            _pointer = (byte[])openSqlCommand.Parameters["@Pointer"].Value;
            _length = (int)openSqlCommand.Parameters["@Size"].Value;

            if (openProcedure != null || fileNameColumnName != null) _fileName = (string)openSqlCommand.Parameters["@FileName"].Value;
            if (openProcedure != null || MIMETypeColumnName != null) _MIMEType = (string)openSqlCommand.Parameters["@MIMEType"].Value;

            //Start reading at the beginning
            _offset = 0;

            //Stream is now officially open
            _isOpen = true;
        }

        /// <summary>
        /// Creates a new row in the database for read/write access
        /// </summary>
        /// <param name="tableName">Name of table in which to insert data</param>
        /// <param name="dataColumnName">Name of column in which to insert data</param>
        /// <param name="partialFlagColumnName"><c>Optional</c> If specified, this column will be set to 1 during upload and then to 0 when the upload is complete</param>
        /// <param name="fileNameColumnName"><c>Optional</c> If specified, this column will be set to file specified file name</param>
        /// <param name="fileName"><c>Optional</c> name of file</param>
        /// <param name="MIMETypeColumnName"><c>Optional</c> If specified, this column will be set to the MIME type of the created file</param>
        /// <param name="MIMEType"><c>Optional</c> MIME type of file</param>
        private void CreateBlob(string tableName, string dataColumnName, string partialFlagColumnName, string fileNameColumnName, string fileName, string MIMETypeColumnName, string MIMEType)
        {
            SqlCommand createCommand = _connection.CreateCommand();

            //Builds a SQL query that creates a row in the table specified and gets a pointer to the data field
            StringBuilder sql = new StringBuilder("SET NOCOUNT ON;");
            sql.AppendFormat("INSERT INTO [{0}] ([{1}]", tableName, dataColumnName);
            if (partialFlagColumnName != null) sql.AppendFormat(",[{0}]", partialFlagColumnName);
            if (fileNameColumnName != null) sql.AppendFormat(",[{0}]", fileNameColumnName);
            if (MIMETypeColumnName != null) sql.AppendFormat(",[{0}]", MIMETypeColumnName);
            sql.Append(") VALUES (@Bytes");
            if (partialFlagColumnName != null) sql.Append(",1");

            if (fileNameColumnName != null)
            {
                sql.Append(",@FileName");
                AddWithValue(createCommand.Parameters, "@FileName", fileName);
            }
            if (MIMETypeColumnName != null)
            {
                sql.Append(",@MIMEType");
                AddWithValue(createCommand.Parameters, "@MIMEType", MIMEType);
            }

            // Add zero bytes to this new blob
            createCommand.Parameters.Add("@Bytes", SqlDbType.VarBinary);
            createCommand.Parameters["@Bytes"].Value = new byte[0];
            createCommand.Parameters["@Bytes"].Size = 0;
            createCommand.Parameters["@Bytes"].Offset = 0;

            sql.AppendFormat(");SELECT @Identity = SCOPE_IDENTITY(); SELECT @Pointer = TEXTPTR([{0}]) FROM [{1}] WHERE $IDENTITY = @Identity", dataColumnName, tableName);

            createCommand.CommandText = sql.ToString();

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

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

            //Get pointer and identity
            _pointer = (byte[])createCommand.Parameters["@Pointer"].Value;
            _identity = (int)createCommand.Parameters["@Identity"].Value;
            _offset = 0;
        }



        private void GenerateReadCommand(string tableName, string dataColumnName, string readProcedure)
        {
            if ((readProcedure == null || readProcedure.Length == 0) && (tableName == null || tableName.Length == 0 || dataColumnName == null || dataColumnName.Length == 0))
                _readCommand = null;
            else
            {
                _readCommand = _connection.CreateCommand();
                if (readProcedure != null && readProcedure.Length > 0) {
                    _readCommand.CommandType = CommandType.StoredProcedure;
                    _readCommand.CommandText = readProcedure;
                } else
                    _readCommand.CommandText = string.Format("READTEXT [{0}].[{1}] @Pointer @Offset @Size", tableName, dataColumnName);

                //@Identity is not used by the created code, but may be used by the procedure (if they choose to go with the "SUBSTRING"-approach)
                AddWithValue(_readCommand.Parameters, "@Identity", _identity);

                //Add required parameters
                _readCommand.Parameters.Add("@Pointer", SqlDbType.Binary, 16).Value = _pointer;
                _readCommand.Parameters.Add("@Offset", SqlDbType.Int);
                _readCommand.Parameters.Add("@Size", SqlDbType.Int);
            }
        }

        private void GenerateDeleteCommand(string tableName, string deleteProcedure)
        {
            if ((tableName == null || tableName.Length == 0) && ((deleteProcedure == null || deleteProcedure.Length == 0)))
                _deleteCommand = null;
            else
            {
                _deleteCommand = _connection.CreateCommand();
                if (deleteProcedure != null && deleteProcedure.Length > 0) {
                    _deleteCommand.CommandType = CommandType.StoredProcedure;
                    _deleteCommand.CommandText = deleteProcedure;
                } else
                    _deleteCommand.CommandText = string.Format("DELETE FROM [{0}] WHERE $IDENTITY = @Identity", tableName);
                AddWithValue(_deleteCommand.Parameters, "@Identity", _identity);
            }
        }

        private void GenerateWriteCommand(string tableName, string dataColumnName, string writeProcedure)
        {
            //Check if we have the data wee need to create a write command
            if ((tableName == null || tableName.Length == 0 || dataColumnName == null || dataColumnName.Length == 0) && ((writeProcedure == null || writeProcedure.Length == 0)))
                _writeCommand = null;
            else
            {
                _writeCommand = _connection.CreateCommand();
                if (writeProcedure != null && writeProcedure.Length > 0)
                {
                    _writeCommand.CommandType = CommandType.StoredProcedure;
                    _writeCommand.CommandText = writeProcedure;
                }
                else
                    _writeCommand.CommandText = string.Format("UPDATETEXT [{0}].[{1}] @Pointer @Offset @Delete WITH LOG @Bytes", tableName, dataColumnName);

                //@Identity is not used by the created code, but may be used by the procedure (if they choose to go with the ".WRITE"-approach)
                AddWithValue(_writeCommand.Parameters, "@Identity", _identity);

                //Add required parameters
                _writeCommand.Parameters.Add("@Pointer", SqlDbType.Binary, 16).Value = _pointer;
                _writeCommand.Parameters.Add("@Offset", SqlDbType.Int).Value = _offset;
                _writeCommand.Parameters.Add("@Delete", SqlDbType.Int).Value = 0; // delete inserted 0x0 character
                _writeCommand.Parameters.Add("@Bytes", SqlDbType.VarBinary);
            }
        }

        private void GenerateCleanupCommand(string tableName, string dataColumnName, string partialFlagColumnName, string cleanupProcedure)
        {
            //Check if we need a cleanup-command
            if (partialFlagColumnName == null && cleanupProcedure == null)
                _cleanupCommand = null;
            else
            {
                _cleanupCommand = _connection.CreateCommand();

                if (cleanupProcedure != null && cleanupProcedure.Length > 0)
                {
                    _cleanupCommand.CommandType = CommandType.StoredProcedure;
                    _cleanupCommand.CommandText = cleanupProcedure;
                }
                else
                    _cleanupCommand.CommandText = string.Format("UPDATE [{0}] SET [{1}]=0 WHERE $Identity=@Identity", tableName, partialFlagColumnName, _identity);

                AddWithValue(_cleanupCommand.Parameters, "@Identity", _identity);
            }
        }


        /// <summary>
        /// Simple constructor mainly for reading, using generated SQL queries
        /// </summary>
        /// <param name="connectionString"></param>
        /// <param name="tableName"></param>