/// <summary>
        /// Creates a new instance of the <see cref="SqlFileStreamReadStream" /> class with the specified connection, table,
        /// data field, and where criteria action.
        /// </summary>
        /// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
        /// <param name="table">The table in which the data is stored.</param>
        /// <param name="dataField">The field in which the data is stored</param>
        /// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
        public SqlFileStreamReadStream(IDbConnection connection, string table, string dataField, BuildWhereCriteriaAction criteriaAction)
            : base(connection, OpenConnectionAndBeginTransaction(connection))
        {
            // TODO: add buffering

            try
            {
                using (SqlCommand cmd = ((SqlConnection)Connection).CreateCommand())
                {
                    cmd.CommandText = "SELECT " + dataField + ".PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM " + table + " WHERE " + criteriaAction(cmd);
                    cmd.Transaction = (SqlTransaction)Transaction;

                    using (SqlDataReader rd = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        rd.Read();

                        string path = rd.GetString(0);
                        byte[] context = (byte[])rd.GetValue(1);

                        // todo: fileoptions, allocationsize
                        _innerStream = new SqlFileStream(path, context, FileAccess.Read);
                    }
                }
            }
            catch
            {
                if (_innerStream != null)
                    _innerStream.Dispose();

                base.Close();

                // TODO: cleanup
                throw;
            }
        }
Beispiel #2
0
        private static void WriteSqlFileStream(Stream stream, int binaryPropertyId, FileStreamData fileStreamData = null)
        {
            SqlProcedure cmd = null;

            try
            {
                //If we did not receive a path and transaction context, retrieve it now from the database.
                if (fileStreamData == null)
                {
                    cmd = new SqlProcedure {
                        CommandText = UPDATE_BINARY_PROPERTY_FILESTREAM, CommandType = CommandType.Text
                    };
                    cmd.Parameters.Add("@Id", SqlDbType.Int).Value = binaryPropertyId;

                    string path;
                    byte[] transactionContext;

                    //Set Stream column to NULL and retrieve file path and
                    //transaction context for the Filestream column
                    using (var reader = cmd.ExecuteReader())
                    {
                        reader.Read();

                        path = reader.GetString(0);
                        transactionContext = reader.GetSqlBytes(1).Buffer;
                    }

                    fileStreamData = new FileStreamData {
                        Path = path, TransactionContext = transactionContext
                    };
                }

                stream.Seek(0, SeekOrigin.Begin);

                //Write data using SqlFileStream
                using (var fs = new System.Data.SqlTypes.SqlFileStream(fileStreamData.Path, fileStreamData.TransactionContext, FileAccess.Write))
                {
                    //default buffer size is 4096
                    stream.CopyTo(fs);
                }
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }
        }
Beispiel #3
0
        public Stream GetPhoto(string photoId)
        {
            var result = new MemoryStream();
            string filePath = null, contentType = "image/jpeg";
            byte[] txContext = null;

            using (var connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["PhotosDB"].ConnectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var cmd = new SqlCommand("GetPhoto", connection, transaction))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add("@photoId", SqlDbType.Int).Value = photoId;

                        using (var sdr = cmd.ExecuteReader())
                        {
                            if (sdr.Read())
                            {
                                filePath = sdr.GetValue<string>("PathName", String.Empty);
                                contentType = sdr.GetValue<string>("ContentType", "image/jpeg");
                                txContext = (byte[])sdr["txContext"];
                            }
                        }
                    }

                    if (!String.IsNullOrWhiteSpace(filePath))
                    {
                        using (var sqlStream = new SqlFileStream(filePath, txContext, FileAccess.Read))
                        {
                            sqlStream.CopyTo(result);
                            result.Position = 0;
                        }
                    }

                    transaction.Commit();
                }
            }

            WebOperationContext.Current.OutgoingResponse.ContentType = contentType;
            return result;
        }
Beispiel #4
0
 private static void SaveDocImageFile(string clientPath, string serverPath, byte[] serverTxn)
 {
     const int BlockSize = 1024 * 512;
     using (FileStream source = new FileStream(clientPath, FileMode.Open, FileAccess.Read))
     {
         using (SqlFileStream dest = new SqlFileStream(serverPath, serverTxn, FileAccess.Write))
         {
             byte[] buffer = new byte[BlockSize];
             int bytesRead;
             while ((bytesRead = source.Read(buffer, 0, buffer.Length)) > 0)
             {
                 dest.Write(buffer, 0, bytesRead);
                 dest.Flush();
             }
             dest.Close();
         }
         source.Close();
     }
 }
 private void SaveDocImageByteArray(byte[] pDataBytes, string pServerPath, byte[] pServerTxn)
 {
     const int BlockSize = 1024 * 512;
     using (MemoryStream memStream = new MemoryStream(pDataBytes))
     //using (FileStream source = new FileStream(pFileNameAndPath, FileMode.Open, FileAccess.Read))
     {
         using (SqlFileStream dest = new SqlFileStream(pServerPath, pServerTxn, FileAccess.Write))
         {
             byte[] buffer = new byte[BlockSize];
             int bytesRead;
             while ((bytesRead = memStream.Read(buffer, 0, buffer.Length)) > 0)
             {
                 dest.Write(buffer, 0, bytesRead);
                 dest.Flush();
             }
             dest.Close();
         }
         //source.Close();
     }
 }
        public byte[] TestGetByteArray(int pImageId)
        {
            const string SelectTSql = @"
                SELECT
                Description, ImageType, DocImage.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
                FROM InboundImages
                WHERE ImageId = @ImageId";
            //Image docImage;
            string serverPath;
            byte[] serverTxn;
            byte[] returnVal;
            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection(sqlConnStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(SelectTSql, conn))
                    {
                        cmd.Parameters.Add("@ImageId", SqlDbType.Int).Value = pImageId;
                        using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
                        {
                            rdr.Read();
                            //desc = rdr.GetSqlString(0).Value;
                            //fileext = rdr.GetSqlString(1).Value;
                            serverPath = rdr.GetSqlString(2).Value;
                            serverTxn = rdr.GetSqlBinary(3).Value;
                            rdr.Close();
                        }
                    }

                    using (SqlFileStream sfs = new SqlFileStream(serverPath, serverTxn, FileAccess.Read))
                    {
                        using (MemoryStream memStream = new MemoryStream())
                        {
                            sfs.CopyTo(memStream);
                            returnVal = memStream.ToArray();
                        }
                        sfs.Close();
                    } 
                }
                ts.Complete();
            }
            return returnVal;
        }
        public void TestOverwriteFilestream(Int32 pImageId, byte[] pImageDataBytes)
        {
            const string InsertTSql = @"
                SELECT TOP(1) DocImage.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() 
                FROM InboundImages
                WHERE ImageId = @ImageId";
            using (SqlConnection conn = new SqlConnection(sqlConnStr))
            {
                conn.Open();

                //SqlCommand command = new SqlCommand("SELECT TOP(1) Photo.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM employees", connection);
                //SqlCommand command = new SqlCommand(InsertTSql, conn);
                using (SqlCommand cmd = new SqlCommand(InsertTSql, conn))
                {
                    SqlTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                    cmd.Transaction = tran;

                    cmd.Parameters.Add("@ImageId", SqlDbType.Int).Value = pImageId;
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            // Get the pointer for file 
                            string path = reader.GetString(0);
                            byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

                            // Create the SqlFileStream
                            using (Stream fileStream = new SqlFileStream(path, transactionContext, FileAccess.Write, FileOptions.SequentialScan, allocationSize: 0))
                            {
                                // Write a single byte to the file. This will
                                // replace any data in the file.
                                //fileStream.WriteByte(0x01);
                                fileStream.Write(pImageDataBytes, 0, pImageDataBytes.Length);
                            }
                        }
                    }
                    tran.Commit();
                }
            }
        }
Beispiel #8
0
 private static void WriteSqlFileStream(string pServerPath, byte[] pServerTxn, Stream source)
 {
     using (var dest = new SqlFileStream(pServerPath, pServerTxn, FileAccess.Write))
     {
         const int BlockSize = 1024*512;
         var buffer = new byte[BlockSize];
         int bytesRead;
         while ((bytesRead = source.Read(buffer, 0, buffer.Length)) > 0)
         {
             dest.Write(buffer, 0, bytesRead);
             dest.Flush();
         }
         dest.Close();
     }
 }
Beispiel #9
0
 private static void LoadDocImageIntoFile(string filePath, byte[] txnToken, string imageType)
 {
     using (SqlFileStream sfs = new SqlFileStream(filePath, txnToken, FileAccess.Read))
     {
         string fileName = TEMP_FILENAME + imageType;
         using (FileStream fstream = new FileStream(fileName, FileMode.Create))
         {
             sfs.CopyTo(fstream, 4096);
         }
         sfs.Close();
     }            
 }
Beispiel #10
0
        public override int Read(byte[] buffer, int offset, int count)
        {
            if (buffer == null)
            {
                throw new ArgumentNullException("buffer");
            }
            if (offset + count > buffer.Length)
            {
                throw new ArgumentException("Offset + count must not be greater than the buffer length.");
            }
            if (offset < 0)
            {
                throw new ArgumentOutOfRangeException("offset", "The offset must be greater than zero.");
            }
            if (count < 0)
            {
                throw new ArgumentOutOfRangeException("count", "The count must be greater than zero.");
            }

            // Calculate the maximum count of the bytes that can be read.
            // Return immediately if nothing to read.
            var maximumReadableByteCount = Length - Position;

            if (maximumReadableByteCount < 1)
            {
                return(0);
            }

            var isLocalTransaction = false;
            var realCount          = (int)Math.Min(count, maximumReadableByteCount);

            if (CanInnerBufferHandleReadRequest(realCount))
            {
                Array.Copy(_innerBuffer, (int)Position - _innerBufferFirstPostion, buffer, offset, realCount);
            }
            else
            {
                if (!TransactionScope.IsActive)
                {
                    //make sure we do not use an obsolete value
                    _fileStreamData = null;

                    //Start a new transaction here to serve the needs of the SqlFileStream type.
                    TransactionScope.Begin();
                    isLocalTransaction = true;
                }

                try
                {
                    //Load transaction data for SqlFilestream. If this is not a local transaction,
                    //than we will be able to use this data in the future if the client calls
                    //the Read method multiple times and will not have to execute SQL queries
                    //every time.
                    if (_fileStreamData == null || _fileStreamData.TransactionContext == null)
                    {
                        _fileStreamData = DataProvider.Current.LoadFileStreamData(this.BinaryPropertyId);
                    }

                    if (_fileStreamData == null)
                    {
                        throw new InvalidOperationException("Transaction data and file path could not be retrieved for SqlFilestream");
                    }

                    using (var fs = new System.Data.SqlTypes.SqlFileStream(_fileStreamData.Path, _fileStreamData.TransactionContext, FileAccess.Read, FileOptions.SequentialScan, 0))
                    {
                        fs.Seek(Position, SeekOrigin.Begin);

                        _innerBuffer = null;

                        var bytesRead = 0;
                        var bytesStoredInInnerBuffer = 0;

                        while (bytesRead < realCount)
                        {
                            var bytesToReadInThisIteration  = (int)Math.Min(this.Length - Position - bytesRead, RepositoryConfiguration.BinaryChunkSize);
                            var bytesToStoreInThisIteration = Math.Min(bytesToReadInThisIteration, realCount - bytesRead);
                            var tempBuffer = new byte[bytesToReadInThisIteration];

                            //copy the bytes from the file stream to the temp buffer
                            //(it is possible that we loaded a lot more bytes than the client requested)
                            fs.Read(tempBuffer, 0, bytesToReadInThisIteration);

                            //first iteration: create inner buffer for caching a part of the stream in memory
                            if (_innerBuffer == null)
                            {
                                _innerBuffer             = new byte[GetInnerBufferSize(realCount)];
                                _innerBufferFirstPostion = Position;
                            }

                            //store a fragment of the data in the inner buffer if possible
                            if (bytesStoredInInnerBuffer < _innerBuffer.Length)
                            {
                                var bytesToStoreInInnerBuffer = Math.Min(bytesToReadInThisIteration, _innerBuffer.Length - bytesStoredInInnerBuffer);

                                Array.Copy(tempBuffer, 0, _innerBuffer, bytesStoredInInnerBuffer, bytesToStoreInInnerBuffer);
                                bytesStoredInInnerBuffer += bytesToStoreInInnerBuffer;
                            }

                            //copy the chunk from the temp buffer to the buffer of the caller
                            Array.Copy(tempBuffer, 0, buffer, bytesRead, bytesToStoreInThisIteration);
                            bytesRead += bytesToReadInThisIteration;
                        }
                    }
                }
                catch
                {
                    if (isLocalTransaction && TransactionScope.IsActive)
                    {
                        TransactionScope.Rollback();

                        //cleanup
                        isLocalTransaction = false;
                        _fileStreamData    = null;
                    }

                    throw;
                }
                finally
                {
                    if (isLocalTransaction && TransactionScope.IsActive)
                    {
                        TransactionScope.Commit();

                        //Set filestream data to null as this was a local transaction and we cannot use it anymore
                        _fileStreamData = null;
                    }
                }
            }

            Position += realCount;

            return(realCount);
        }
Beispiel #11
0
        public int SavePhoto(int productId, byte[] imageFile, string createdBy)
        {
            int photoId = 0;
            string filePath = null;
            byte[] txContext = null;
            int width, height;
            byte[] hash;

            using (var memStream = new MemoryStream(imageFile))
            {
                using (var b = new Bitmap(memStream))
                {
                    width = b.Width;
                    height = b.Height;
                }
            }

            using (var hashGenerator = MD5.Create())
            {
                hash = hashGenerator.ComputeHash(imageFile);
            }

            using (var connection = new SqlConnection(
                ConfigurationManager.ConnectionStrings["PhotosDB"].ConnectionString))
            {
                connection.Open();
                using (var transaction = connection.BeginTransaction())
                {
                    using (var cmd = new SqlCommand("SavePhoto", connection, transaction))
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Parameters.Add("@productId", SqlDbType.Int).Value = productId;
                        cmd.Parameters.Add("@createdBy", SqlDbType.NVarChar, 50).Value = createdBy;

                        cmd.Parameters.Add("@widthInPixels", SqlDbType.Int).Value = width;
                        cmd.Parameters.Add("@heightInPixels", SqlDbType.Int).Value = height;
                        cmd.Parameters.Add("@lengthInBytes", SqlDbType.Int).Value = imageFile.Length;

                        cmd.Parameters.Add("@contentType", SqlDbType.VarChar, 255).Value = MimeType.GetMimeType(imageFile, null);
                        cmd.Parameters.Add("@md5Checksum", SqlDbType.VarBinary, 16).Value = hash;

                        using (var sdr = cmd.ExecuteReader())
                        {
                            if (sdr.Read())
                            {
                                filePath = sdr.GetValue<string>("PathName", String.Empty);
                                photoId = sdr.GetValue<int>("Id");
                                txContext = (byte[])sdr["txContext"];
                            }
                        }
                    }

                    if (!String.IsNullOrWhiteSpace(filePath))
                    {
                        using (var sqlStream = new SqlFileStream(filePath, txContext, FileAccess.Write))
                        {
                            sqlStream.Write(imageFile, 0, imageFile.Length);
                        }
                    }

                    transaction.Commit();
                }
            }

            return photoId;
        }
        /// <summary>
        /// Creates a new instance of the <see cref="SqlFileStreamWriteStream" /> class with the specified connection, transaction, table,
        /// data field, and where criteria action.
        /// </summary>
        /// <param name="connection">The <see cref="IDbConnection" /> to use.</param>
        /// <param name="transaction">The <see cref="IDbTransaction" /> to use.</param>
        /// <param name="table">The table in which the data is stored.</param>
        /// <param name="dataField">The field in which the data is stored</param>
        /// <param name="criteriaAction">The <see cref="BuildWhereCriteriaAction"/> to use to generate criteria that identifies the record.</param>
        public SqlFileStreamWriteStream(IDbConnection connection, IDbTransaction transaction, string table, string dataField, BuildWhereCriteriaAction criteriaAction)
            : base(connection, transaction)
        {
            // TODO: throw exception if no transaction

			// TODO: add buffering
            using (SqlCommand cmd = ((SqlConnection)Connection).CreateCommand())
            {
                cmd.CommandText = "UPDATE " + table + " SET " + dataField + "=CAST('' AS varbinary(MAX)) " +
                                  "OUTPUT INSERTED." + dataField + ".PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() WHERE " + criteriaAction(cmd);

                cmd.Transaction = (SqlTransaction)Transaction;

                try
                {
                    using (SqlDataReader rd = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (rd.Read())
                        {
                            string path = rd.GetString(0);
                            byte[] context = (byte[])rd.GetValue(1);

                            // todo: fileoptions, allocationsize
                            _innerStream = new SqlFileStream(path, context, FileAccess.Write);
                        }
                        else
                        {
                            // TODO: throw exception
                        }
                    }

                }
                catch
                {
                    if (_innerStream != null)
                        _innerStream.Dispose();

                    base.Close();

                    // TODO: cleanup
                    throw;
                }
            }
		}
        internal void CompleteAndClose(bool isSuccessful)
        {
            _innerStream.Close();

            _innerStream = null;

            if (isSuccessful)
                Transaction.Commit();
            else
                Transaction.Rollback();
        }
        // Returns a memory stream of the file, using FILESTREAM technology
        internal MemoryStream GetFileStream(string path, out string mimeType)
        {
            string sql = String.Format(@"
                    SELECT
                        MimeType,
                        [Data].PathName(),
                        GET_FILESTREAM_TRANSACTION_CONTEXT()
                    FROM [{0}]
                    WHERE Path = @path", this.TableName);

            using (TransactionScope tx = new TransactionScope())
            using (SqlConnection con = new SqlConnection(this.ConnectionString))
            using (SqlCommand cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue("@path", path);

                con.Open();

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    reader.Read();
                    mimeType = reader.GetFieldValue<string>(0);
                    string filePath = reader.GetFieldValue<string>(1);
                    byte[] txnToken = reader.GetFieldValue<byte[]>(2);
                    reader.Close();

                    using (SqlFileStream sqlFileStream = new SqlFileStream(filePath, txnToken, FileAccess.Read))
                    {
                        MemoryStream ms = new MemoryStream();
                        sqlFileStream.Seek(0, SeekOrigin.Begin);
                        sqlFileStream.CopyTo(ms);

                        tx.Complete();

                        return ms;
                    }
                }
            }
        }
Beispiel #15
0
 private static byte[] ReadBytesFromSqlFileStream(string serverPath, byte[] serverTxn)
 {
     byte[] blobByteArray;
     using (var sqlFileStr = new SqlFileStream(serverPath, serverTxn, FileAccess.Read))
     {
         using (var memStream = new MemoryStream())
         {
             sqlFileStr.CopyTo(memStream);
             blobByteArray = memStream.ToArray();
         }
         sqlFileStr.Close();
     }
     return blobByteArray;
 }
        public TradeRqmtConfirmBlobDto Get(Int32 pTradeRqmtConfirmId)
        {
            const string selectTSqlCmd = @"SELECT ID, TRADE_RQMT_CONFIRM_ID, IMAGE_FILE_EXT, DOC_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
                    FROM TRADE_RQMT_CONFIRM_BLOB WHERE TRADE_RQMT_CONFIRM_ID = @TRADE_RQMT_CONFIRM_ID";
            TradeRqmtConfirmBlobDto trqmtConfirmBlobResult = new TradeRqmtConfirmBlobDto();
            string serverPath;
            byte[] serverTxn;
            byte[] blobByteArray;
            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection(sqlConnStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(selectTSqlCmd, conn))
                    {
                        cmd.Parameters.Add("@TRADE_RQMT_CONFIRM_ID", SqlDbType.Int).Value = pTradeRqmtConfirmId;
                        using (SqlDataReader dataReader = cmd.ExecuteReader())
                        {

                            if (dataReader.HasRows)
                            {
                                while (dataReader.Read())
                                {
                                    serverPath = dataReader.GetSqlString(3).Value;
                                    serverTxn = dataReader.GetSqlBinary(4).Value;
                                    using (SqlFileStream sqlFileStr = new SqlFileStream(serverPath, serverTxn, FileAccess.Read))
                                    {
                                        using (MemoryStream memStream = new MemoryStream())
                                        {
                                            sqlFileStr.CopyTo(memStream);
                                            blobByteArray = memStream.ToArray();
                                        }
                                        sqlFileStr.Close();
                                    }

                                    trqmtConfirmBlobResult.Id = DBUtils.HandleInt32IfNull(dataReader["ID"].ToString());
                                    trqmtConfirmBlobResult.TradeRqmtConfirmId = DBUtils.HandleInt32IfNull(dataReader["TRADE_RQMT_CONFIRM_ID"].ToString());
                                    trqmtConfirmBlobResult.ImageFileExt = dataReader["IMAGE_FILE_EXT"].ToString();
                                    trqmtConfirmBlobResult.DocBlob = blobByteArray;
                                }
                            }
                        }
                    }
                }
                ts.Complete();
            }
            return trqmtConfirmBlobResult;
        }
        public void Update(TradeRqmtConfirmBlobDto pData)
        {
            const string updateTSql =
                @"UPDATE TRADE_RQMT_CONFIRM_BLOB
                SET TRADE_RQMT_CONFIRM_ID = @TRADE_RQMT_CONFIRM_ID, IMAGE_FILE_EXT = @IMAGE_FILE_EXT
                WHERE TRADE_RQMT_CONFIRM_ID = @TRADE_RQMT_CONFIRM_ID
                SELECT TOP(1) DOC_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() 
                FROM TRADE_RQMT_CONFIRM_BLOB
                WHERE TRADE_RQMT_CONFIRM_ID = @TRADE_RQMT_CONFIRM_ID";

            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection(sqlConnStr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(updateTSql, conn))
                    {
                        //cmd.Parameters.Add("@ID", SqlDbType.Int).Value = pData.Id;
                        cmd.Parameters.Add("@TRADE_RQMT_CONFIRM_ID", SqlDbType.VarChar).Value = pData.TradeRqmtConfirmId;
                        cmd.Parameters.Add("@IMAGE_FILE_EXT", SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ImageFileExt.ToUpper());
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                // Get the pointer for file 
                                string path = reader.GetString(0);
                                byte[] transactionContext = reader.GetSqlBytes(1).Buffer;

                                // Create the SqlFileStream
                                using (Stream fileStream = new SqlFileStream(path, transactionContext, FileAccess.Write, FileOptions.SequentialScan, allocationSize: 0))
                                {
                                    // Write a byte array to the file. This will replace any data in the file.
                                    //fileStream.WriteByte(0x01);
                                    fileStream.Write(pData.DocBlob, 0, pData.DocBlob.Length);
                                }
                            }
                        }
                    }
                }
                ts.Complete();
            }
        }
Beispiel #18
0
 private static bool TransferFile(string clientFilePath, string serverFilePath, byte[] txnToken)
 {
     if (File.Exists(clientFilePath))
     {
         File.Delete(clientFilePath);
     }
     SqlFileStream sfs = new SqlFileStream(serverFilePath, txnToken, FileAccess.Read);
     FileStream fStream = new FileStream(clientFilePath, FileMode.CreateNew);
     sfs.CopyTo(fStream, 8192);
     sfs.Close();
     fStream.Close();
     return true;
 }
        private MensajeDto RecuperarDocumento(string nombre, string rutaDestino)
        {
            var mensajeConnString = RecuperarElconnectionStrings("ArchivosDb");
            if (mensajeConnString.Error) {
                return mensajeConnString;
            }
            string CadConexion = mensajeConnString.Valor;

            using (SqlConnection conexionBD = new SqlConnection(CadConexion)) {
                try {
                    conexionBD.Open();
                    if (conexionBD.State == ConnectionState.Open) {
                        using (SqlTransaction transaccion = conexionBD.BeginTransaction()) {
                            byte[] arrayContexto = null;
                            string ubicacionFichero = string.Empty;
                            string cadSql = string.Empty;

                            //obtengo el PathName()
                            cadSql = "SELECT DocumentoFile.PathName() from dbo.[ArchivosMovimientos] where NombreArchivo=@nombre";
                            using (SqlCommand cmdUbicacion = new SqlCommand(cadSql, conexionBD, transaccion)) {
                                cmdUbicacion.Parameters.AddWithValue("@Nombre", nombre);
                                object objUbicacion = cmdUbicacion.ExecuteScalar();
                                if (objUbicacion != null) {
                                    ubicacionFichero = objUbicacion.ToString();
                                }
                            }
                            if (!string.IsNullOrEmpty(ubicacionFichero)) {
                                //todas las operaciones FILESTREAM BLOB deben ocurrir dentro del contexto de una transaccion
                                cadSql = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
                                using (SqlCommand cmdContexto = new SqlCommand(cadSql, conexionBD, transaccion)) {
                                    object objContexto = cmdContexto.ExecuteScalar();
                                    if (objContexto != null) {
                                        arrayContexto = (byte[])objContexto;
                                    }
                                }

                                //obtener el handle que sera pasado al WIN32 API FILE
                                using (SqlFileStream sqlFS = new SqlFileStream(ubicacionFichero, arrayContexto, FileAccess.Read)) {
                                    byte[] datosFichero = new byte[sqlFS.Length];

                                    sqlFS.Read(datosFichero, 0, (int)sqlFS.Length);
                                    var rutaCompleta = Path.Combine(rutaDestino, nombre);
                                    File.WriteAllBytes(rutaCompleta, datosFichero);
                                    sqlFS.Close();
                                }
                            }
                            transaccion.Commit();
                        }
                    }

                } catch (UnauthorizedAccessException ex) {
                    return new MensajeDto() {
                        Error = true,
                        MensajeDelProceso = "No tiene autorizacion para acceder al recurso: " + ex.Message
                    };
                } catch (Exception ex) {
                    return new MensajeDto() {
                        Error = true,
                        MensajeDelProceso = "Error: " + ex.Message
                    };
                } finally {
                    if ((conexionBD != null) && (conexionBD.State == ConnectionState.Open)) {
                        conexionBD.Close();
                    }
                }
            }

            return new MensajeDto() {
                Error = false,
                MensajeDelProceso = "Archivo generado",
                Valor = nombre
            };
        }
       public void CaptureVaultedDocsBlob(VaultedDocs vaultedDoc)
       {
          string sql = String.Format("SELECT ID,DOC_BLOB.PathName(),GET_FILESTREAM_TRANSACTION_CONTEXT() FROM {0}VAULTED_DOCS_BLOB{1} WHERE ID={2}"
              , DbContext.SCHEMA_NAME, DbContext.NO_LOCK,vaultedDoc.VaultedDocsBlobId);         
            using (TransactionScope ts = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection(_connectionString))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {                    
                        using (SqlDataReader dataReader = cmd.ExecuteReader())
                        {

                            if (dataReader.HasRows)
                            {
                                while (dataReader.Read())
                                {
                                    using (SqlFileStream sqlFileStr = new SqlFileStream(dataReader.GetSqlString(1).Value, dataReader.GetSqlBinary(2).Value, FileAccess.Read))
                                    {
                                        using (MemoryStream memStream = new MemoryStream())
                                        {
                                            sqlFileStr.CopyTo(memStream);
                                            vaultedDoc.VaultedDocsBlobDocBlob = memStream.ToArray();
                                        }
                                        sqlFileStr.Close();
                                    }                                    
                                }
                            }
                        }
                    }
                }
                ts.Complete();
            }                  
       }
Beispiel #21
0
        private static Image LoadDocImage(string filePath, byte[] txnToken, string imageType)
        {
            Image docImage = null;
            Bitmap bmap;

            using (SqlFileStream sfs = new SqlFileStream(filePath, txnToken, FileAccess.Read))
            {
                if (imageType.Equals("TIF"))
                    docImage = Image.FromStream(sfs);
                else //if (imageType.Equals("PDF"))
                {
                    //MemoryStream memStream = new MemoryStream();
                    //storeStream.SetLength(sfs.Length);
                    //sfs.CopyTo(memStream);
                    //docImage = Image.FromStream(memStream, false, false);

                    //System.Drawing.ImageConverter converter = new System.Drawing.ImageConverter();
                    //byte[] m_Bytes = ReadToEnd(memStream);
                    //docImage = (Image)converter.ConvertFrom(m_Bytes);

                    FileStream fstream = new FileStream(TEMP_FILENAME, FileMode.Create);
                    sfs.CopyTo(fstream, 4096);

                    //docImage = Image.FromFile(TEMP_FILENAME);
                    docImage = Image.FromStream(fstream);

                    //docImage = (Image)bmap;
                    
                }
                sfs.Close();
            } 
            return docImage;
        }
Beispiel #22
0
        public int InsertBinaryProperty(int versionId, int propertyTypeId, BinaryDataValue value, bool isNewNode)
        {
            SqlProcedure cmd = null;
            var          id  = 0;

            try
            {
                var streamSize = value.Stream != null?Convert.ToInt32(value.Stream.Length) : 0;

                var useFileStream = RepositoryConfiguration.FileStreamEnabled &&
                                    streamSize > RepositoryConfiguration.MinimumSizeForFileStreamInBytes;

                cmd = useFileStream
                    ? new SqlProcedure {
                    CommandText = (isNewNode ? INSERT_BINARY_PROPERTY_FILESTREAM : DELETE_AND_INSERT_BINARY_PROPERTY_FILESTREAM), CommandType = CommandType.Text
                }
                    : new SqlProcedure {
                    CommandText = (isNewNode ? INSERT_BINARY_PROPERTY : DELETE_AND_INSERT_BINARY_PROPERTY), CommandType = CommandType.Text
                };

                cmd.Parameters.Add("@VersionId", SqlDbType.Int).Value             = (versionId != 0) ? (object)versionId : DBNull.Value;
                cmd.Parameters.Add("@PropertyTypeId", SqlDbType.Int).Value        = (propertyTypeId != 0) ? (object)propertyTypeId : DBNull.Value;
                cmd.Parameters.Add("@ContentType", SqlDbType.NVarChar, 450).Value = value.ContentType;
                cmd.Parameters.Add("@FileNameWithoutExtension", SqlDbType.NVarChar, 450).Value = value.FileName.FileNameWithoutExtension == null ? DBNull.Value : (object)value.FileName.FileNameWithoutExtension;
                cmd.Parameters.Add("@Extension", SqlDbType.NVarChar, 50).Value = ValidateExtension(value.FileName.Extension);
                cmd.Parameters.Add("@Size", SqlDbType.BigInt).Value            = Math.Max(0, value.Size);
                cmd.Parameters.Add("@Checksum", SqlDbType.VarChar, 200).Value  = (value.Checksum != null) ? (object)value.Checksum : DBNull.Value;

                if (value.Stream != null && value.Stream.Length > 0)
                {
                    value.Stream.Seek(0, SeekOrigin.Begin);

                    //use Filstream if it is enabled and the size is big enough
                    if (useFileStream)
                    {
                        //set old binary to NULL
                        cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.VarBinary)).Value = DBNull.Value;

                        string path;
                        byte[] transactionContext;

                        //insert binary row and retrieve file path and transaction context for the Filestream column
                        using (var reader = cmd.ExecuteReader())
                        {
                            reader.Read();

                            id = Convert.ToInt32(reader[0]);

                            path = reader.GetString(1);
                            transactionContext = reader.GetSqlBytes(2).Buffer;
                            value.Timestamp    = DataProvider.GetLongFromBytes((byte[])reader.GetValue(3));
                        }

                        //Write the data using SqlFileStream
                        using (var fs = new System.Data.SqlTypes.SqlFileStream(path, transactionContext, FileAccess.Write))
                        {
                            //default buffer size is 4096
                            value.Stream.CopyTo(fs);
                        }
                    }
                    else
                    {
                        if (value.Stream.Length > Int32.MaxValue)
                        {
                            throw new NotSupportedException(); // MS-SQL does not support stream size over [Int32.MaxValue]
                        }
                        //read the whole data the old way
                        var buffer = new byte[streamSize];
                        value.Stream.Read(buffer, 0, streamSize);

                        cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.VarBinary)).Value = buffer;
                    }
                }
                else
                {
                    cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.VarBinary)).Value = DBNull.Value;
                }

                //if Filestream is enabled but was not used due to the small file size: set it to null
                if (RepositoryConfiguration.FileStreamEnabled && !useFileStream)
                {
                    cmd.Parameters.Add(new SqlParameter("@FileStream", SqlDbType.VarBinary)).Value = DBNull.Value;
                }

                //If Filestream was not involved, execute the command
                //and get the new id the old way from the db.
                if (!useFileStream)
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        reader.Read();

                        id = Convert.ToInt32(reader[0]);
                        value.Timestamp = DataProvider.GetLongFromBytes((byte[])reader.GetValue(1));
                    }
                }
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                }
            }

            return(id);
        }
Beispiel #23
0
        private static void play_video()
        {
            string video_name;

            Console.WriteLine("Play Video ==========");

            list_video();
            Console.Write("Insert Video Name : ");
            video_name = Console.ReadLine();
            Console.Write("Retrieving Video ...");
            using (TransactionScope transactionScope = new TransactionScope())
            {
                SqlConnection sqlConnection = connect_database();
                using (SqlCommand command = new SqlCommand())
                {
                    command.Connection = sqlConnection;
                    command.CommandType = System.Data.CommandType.Text;
                    command.CommandText = String.Format("SELECT FileData.PathName() As Path, GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext FROM {0} WHERE description=@video_name", table_name);
                    command.Parameters.AddWithValue("@video_name", video_name);

                    try
                    {
                        SqlDataReader reader = command.ExecuteReader();
                        while (reader.Read()) {
                            string filePath = (string) reader["Path"];
                            byte[] transactionContext = (byte[]) reader["TransactionContext"];
                            SqlFileStream sqlFileStream = new SqlFileStream(filePath,transactionContext,FileAccess.Read);
                            byte[] video_data = new byte[sqlFileStream.Length];
                            sqlFileStream.Read(video_data,0,Convert.ToInt32(sqlFileStream.Length));
                            sqlFileStream.Close();

                            string filename = @"C:\Users\UA\Documents\Project\Database Systems\Program\temp\" + video_name;
                            FileStream fs = new FileStream(filename,FileMode.Create,FileAccess.Write,FileShare.Write);
                            fs.Write(video_data,0,video_data.Length);
                            fs.Flush();
                            fs.Close();
                            Console.WriteLine("Successfull");

                            Console.Write("Playing Video ...");
                            Process process = new Process();
                            process.StartInfo.FileName = filename;
                            process.Start();
                        }
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine("{0}", e);
                    }
                    disconnect_database(sqlConnection);
                    transactionScope.Complete();
                }
            }
        }
Beispiel #24
0
        private static Image LoadPhotoImage(string filePath, byte[] txnToken)
        {
            Image photo;

            using (SqlFileStream sfs =
              new SqlFileStream(filePath, txnToken, FileAccess.Read))
            {
                photo = Image.FromStream(sfs);
                sfs.Close();
            }

            return photo;
        }