/// <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;
}
}
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();
}
}
}
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;
}
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();
}
}
}
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();
}
}
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();
}
}
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);
}
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;
}
}
}
}
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();
}
}
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();
}
}
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;
}
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);
}
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();
}
}
}
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;
}