public static void ReadFilestream()
{
try
{
string connString = new SqlConnectionStringBuilder(DataTestUtility.TCPConnectionString)
{
InitialCatalog = SetupFileStreamDB(ref DataTestUtility.FileStreamDirectory, DataTestUtility.TCPConnectionString)
}.ConnectionString;
using SqlConnection connection = new(connString);
connection.Open();
string tempTable = SetupTable(connection);
int nRow = 0;
byte[] retrievedValue;
SqlCommand command = new($"SELECT Photo.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(),EmployeeId FROM {tempTable} ORDER BY EmployeeId", connection);
try
{
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = transaction;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// Get the pointer for the file.
string path = reader.GetString(0);
byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
// Create the SqlFileStream
using (Stream fileStream = new SqlFileStream(path, transactionContext, FileAccess.Read, FileOptions.SequentialScan, allocationSize: 0))
{
// Read the contents as bytes.
retrievedValue = new byte[fileStream.Length];
fileStream.Read(retrievedValue, 0, (int)(fileStream.Length));
// Reverse the byte array, if the system architecture is little-endian.
if (BitConverter.IsLittleEndian)
{
Array.Reverse(retrievedValue);
}
// Compare inserted and retrieved values.
Assert.Equal(s_insertedValues[nRow], BitConverter.ToInt32(retrievedValue, 0));
}
nRow++;
}
}
transaction.Commit();
}
finally
{
// Drop Table
ExecuteNonQueryCommand($"DROP TABLE {tempTable}", connection);
}
}
finally
{
DropFileStreamDb(ref DataTestUtility.FileStreamDirectory, DataTestUtility.TCPConnectionString);
}
}
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 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();
}
}
public void GetPhoto(int photoId, Stream output)
{
using (var ts = new TransactionScope())
using (var conn = new SqlConnection(_connectionString))
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText =
@"
SELECT
Photo.PathName() as path,
GET_FILESTREAM_TRANSACTION_CONTEXT() as txnToken
FROM
PhotoAlbum
WHERE
PhotoId = @PhotoId
";
cmd.Parameters.AddWithValue("@PhotoId", photoId);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var path = reader.GetString(reader.GetOrdinal("path"));
var txnToken = reader.GetSqlBinary(reader.GetOrdinal("txnToken")).Value;
using (var stream = new SqlFileStream(path, txnToken, FileAccess.Read))
{
stream.CopyTo(output);
}
}
}
ts.Complete();
}
}
// NOTE: We do not have to override Close() here. Stream.Close() calls
// Dispose(true) and we place all our logic in Dispose.
#endregion
#region Dispose
/// <summary>
/// Releases the unmanaged resources used by the <see cref="T:System.IO.Stream"/> and optionally releases the managed resources.
/// </summary>
/// <param name="disposing">true to release both managed and unmanaged resources; false to release only unmanaged resources.</param>
protected override void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
// Close inner stream.
if (innerStream != null)
{
innerStream.Close();
}
// complete the transaction.
if (innerTransaction != null)
{
innerTransaction.Commit();
}
// Close connection.
if (innerConnection != null)
{
innerConnection.Close();
}
base.Dispose(disposing);
}
innerStream = null;
innerTransaction = null;
innerConnection = null;
disposed = true;
}
}
/// <summary>
/// 附件读取
/// </summary>
/// <param name="id">附件id</param>
/// <param name="action">文件流操作</param>
/// <param name="unitOfWork">操作单元</param>
public void ReadFile(long?id, Action <Stream> action, IUnitOfWork unitOfWork = null)
{
if (!id.HasValue)
{
id = this.ID;
}
if (!(id > 0) || action == null)
{
return;
}
var builder = DBBuilder.Define(@"SELECT FileContent.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM STDAttach Where ID=@ID", new { ID = id });
(unitOfWork ?? this.UnitOfWork).ToReader(builder, reader =>
{
using (var sqlReader = (reader as IWrappedDataReader).Reader as SqlDataReader)
{
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
using (var stream = new SqlFileStream(sqlReader.GetString(0), sqlReader.GetSqlBytes(1).Buffer, FileAccess.Read))
{
action(stream);
}
}
}
}
});
}
public static void InsertPhoto(int photoId, string desc, Stream source)
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["PhotoLibraryDb"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("InsertPhotoRow", conn))
{
using (TransactionScope ts = new TransactionScope())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@PhotoId", photoId);
cmd.Parameters.AddWithValue("@PhotoDescription", desc);
string serverPathName = default(string);
byte[] serverTxnContext = default(byte[]);
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
rdr.Read();
serverPathName = rdr.GetSqlString(0).Value; //UNC format points to network share name, contain GUID value in the uniqueidentifier ROWGUIDCOL column of the BLOB's corresponding row
serverTxnContext = rdr.GetSqlBinary(1).Value;
rdr.Close();
}
conn.Close();
using (SqlFileStream dest = new SqlFileStream(serverPathName, serverTxnContext, FileAccess.Write))
{
source.CopyTo(dest, 4096);
dest.Close();
}
ts.Complete();
}
}
}
}
private void GetTenderFileByFileStream(TenderFile entity)
{
if (entity.File.Length > 8000) // Por algun motivo trae el archivo completo
{
return;
}
using (ISession session = SessionFactory.OpenSession())
{
using (ITransaction transaction = session.BeginTransaction())
{
SqlFileStream sqlFileStream = this.GetSqlFileStream(entity.TenderFileId, session, FileAccess.Read);
byte[] buffer = new Byte[sqlFileStream.Length];
sqlFileStream.Read(buffer, 0, buffer.Length);
MemoryStream ms = new MemoryStream(buffer);
entity.File = ms.ToArray();
sqlFileStream.Close();
transaction.Commit();
session.Close();
}
}
}
/// <summary>
/// Streams data to the Context Result,
/// The size of the buffer can be defined on DBContext.bufferSize
/// </summary>
/// <param name="context">Page context that will receive the data.</param>
/// <param name="sqlQuery">SQL Command</param>
/// <param name="offset">Start offset of the stream, used of big streams like videos.</param>
/// <param name="sqlParams">SQL Parameters</param>
public void FileStream(HttpContext context, string sqlQuery, long offset, params object[] sqlParams)
{
int i = 0;
SqlCommand cmd = new SqlCommand(sqlQuery, db)
{
CommandTimeout = _timeOut,
CommandType = CommandType.Text
};
if (sqlParams != null)
{
for (i = 0; i < sqlParams.Length; i++)
{
cmd.Parameters.AddWithValue("p" + i.ToString(), sqlParams[i] == null ? DBNull.Value : sqlParams[i]);
}
}
db.Open();
SqlDataReader reader = cmd.ExecuteReader();
//Get FilePath
string filePath = string.Empty;
long size = 0;
if (reader.Read())
{
filePath = reader["PathName"].ToString();
size = Convert.ToInt64(reader["Size"]);
}
reader.Close();
//Obtain Transaction for Blob
SqlTransaction sqlTransaction = db.BeginTransaction("fileStreamTransaction");
cmd.Transaction = sqlTransaction;
cmd.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()";
byte[] txtContent = (byte[])cmd.ExecuteScalar();
//Obtain Handle
SqlFileStream sqlFileStream = new SqlFileStream(filePath, txtContent, System.IO.FileAccess.Read);
//Read data
sqlFileStream.Seek(offset, System.IO.SeekOrigin.Begin);
try {
int bytesRead;
byte[] buffer = new byte[_bufferSize];
do
{
bytesRead = sqlFileStream.Read(buffer, 0, buffer.Length);
context.Response.OutputStream.Write(buffer, 0, bytesRead);
} while (bytesRead == buffer.Length);
} catch (Exception) { }
sqlFileStream.Close();
cmd.Transaction.Commit();
db.Close();
}
// GET api/values/5
public HttpResponseMessage Get(int id)
{
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
using (var tx = new TransactionScope(TransactionScopeOption.RequiresNew, TransactionScopeAsyncFlowOption.Enabled))
{
var db = new SampleFSEntities();
using (db.Database.Connection)
{
db.Database.Connection.Open();
var ctx = GetTransactionContext(db.Database.Connection as SqlConnection);
using (var fs = new SqlFileStream(@"\\US1171390W2\MSSQLSERVER\v02-A60EC2F8-2B24-11DF-9CC3-AF2E56D89593\SampleFS\dbo\AuditDocument\file_stream\62972138-21A6-E611-8508-3402867C2166\VolumeHint-HarddiskVolume1", ctx, FileAccess.Read))
{
var content = new byte[fs.Length];
fs.Read(content, 0, content.Length);
response.Content = new ByteArrayContent(content);
response.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
response.Content.Headers.ContentDisposition.FileName = "mama.pdf";
response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/pdf");
}
}
tx.Complete();
}
return(response);
}
public void SaveByStream(Guid id, Stream mediaMStream)
{
using (var tran = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted, Timeout = TransactionManager.DefaultTimeout
}))
{
// Find the appropriate media.
Medium media = UnitOfWork.Find <Medium>(id);
if (media == null)
{
throw new Exception(String.Format("No media exists with the ID {0}", id.ToString()));
}
// Attach a shell media storage.
var guidId = Guid.NewGuid();
media.MediaStorages.Add(new MediaStorage
{
MediaGuid = guidId,
Media = System.Text.Encoding.ASCII.GetBytes("0x00")
});
UnitOfWork.Commit();
// Attach the binary data via Filestream.
MediaStorageContext fsqry = UnitOfWork.Database.SqlQuery <MediaStorageContext>("SELECT Media.PathName() AS TPath, GET_FILESTREAM_TRANSACTION_CONTEXT() AS TContext from dbo.MediaStorages WHERE MediaGuid = {0}", guidId).FirstOrDefault();
if (fsqry != null)
{
using (var sfs = new SqlFileStream(fsqry.TPath, fsqry.TContext, FileAccess.Write))
{
mediaMStream.CopyTo(sfs);
}
}
tran.Complete();
}
}
/// <summary>
/// Loads a SqlFileStream object for the binary in the provided context and sets it to the required position.
/// If the filestream is shorter than the required offset, it extends the stream with empty bytes.
/// </summary>
private static SqlFileStream GetAndExtendFileStream(BlobStorageContext context, long offset)
{
var fsd = ((BuiltinBlobProviderData)context.BlobProviderData).FileStreamData;
if (fsd == null)
{
throw new InvalidOperationException("File row not found. FileId: " + context.FileId);
}
var fs = new SqlFileStream(fsd.Path, fsd.TransactionContext, FileAccess.ReadWrite, FileOptions.SequentialScan, 0);
// if the current stream is smaller than the position where we want to write the bytes
if (fs.Length < offset)
{
// go to the end of the existing stream
fs.Seek(0, SeekOrigin.End);
// calculate the size of the gap (warning: fs.Length changes during the write below!)
var gapSize = offset - fs.Length;
// fill the gap with empty bytes (one-by-one, because this gap could be huge)
for (var i = 0; i < gapSize; i++)
{
fs.WriteByte(0x00);
}
}
else if (offset > 0)
{
// otherwise we will append to the end or overwrite existing bytes
fs.Seek(offset, SeekOrigin.Begin);
}
return(fs);
}
/// <summary>
/// Initializes a new instance of the <see cref="KatmaiContentStream"/> class.
/// </summary>
/// <param name="storeConnectionString">The store connection string.</param>
/// <param name="commandTimeout">The command timeout.</param>
/// <param name="resourceId">The resource id.</param>
internal KatmaiContentStream(string storeConnectionString, int commandTimeout, Guid resourceId)
{
// Initialize inner connection.
innerConnection = new SqlConnection(storeConnectionString);
innerConnection.Open();
// Initialize inner transaction.
innerTransaction = innerConnection.BeginTransaction();
this.commandTimeout = commandTimeout;
// Get the content details.
string sqlFilePath;
byte[] transactionToken;
Utilities.GetPathNameAndTransactionToken(innerConnection, innerTransaction,
this.commandTimeout, resourceId, out sqlFilePath, out transactionToken);
// If the returned path is null, do nothing.
if (string.IsNullOrEmpty(sqlFilePath))
{
return;
}
// Create a stream.
innerStream = new SqlFileStream(sqlFilePath, transactionToken,
FileAccess.Read, FileOptions.SequentialScan, 0);
}
/// <summary>
/// 保存文件
/// </summary>
/// <param name="outputStream">需要保存的文件</param>
/// <param name="id">附件id</param>
/// <param name="unitOfWork">操作单元</param>
public void WriteFile(Stream outputStream, long?id = null)
{
if (!id.HasValue)
{
id = this.ID;
}
if (!(id > 0) || outputStream == null)
{
return;
}
var builder = DBBuilder.Define(@"UPDATE STDAttach SET FileContent=CAST('' as varbinary(max)) WHERE ID=@ID; SELECT FileContent.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM STDAttach Where ID=@ID", new { ID = id });
this.UnitOfWork.ToReader(builder, reader =>
{
using (var sqlReader = (reader as IWrappedDataReader).Reader as SqlDataReader)
{
while (sqlReader.Read())
{
using (var stream = new SqlFileStream(sqlReader.GetString(0), sqlReader.GetSqlBytes(1).Buffer, FileAccess.Write))
{
outputStream.CopyTo(stream);
}
}
}
});
}
private void button2_Click(object sender, EventArgs e)
{
string tresc;
string cs = @"Data Source=.;Initial Catalog=FileStreamDatabase;Integrated Security=True";
string nazwapliku = textBox1.Text;
using (SqlConnection con = new SqlConnection(cs))
{
con.Open();
SqlTransaction txn = con.BeginTransaction();
string sql = "SELECT data.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(), name FROM Pliczki where name=@nazwa";
SqlCommand cmd = new SqlCommand(sql, con, txn);
cmd.Parameters.Add("@nazwa", SqlDbType.VarChar).Value = nazwapliku;
SqlDataReader rdr = cmd.ExecuteReader();
rdr.Read();
byte[] strim;
tresc = rdr.GetSqlString(0).Value;
strim = rdr.GetSqlBinary(1).Value;
rdr.Close();
using (SqlFileStream sfs = new SqlFileStream(tresc, strim, FileAccess.Read))
{
pictureBox1.Image = Image.FromStream(sfs);
sfs.Close();
}
con.Close();
}
}
public void WriteFileToFileStream(Stream stream)
{
using (TransactionScope transactionScope = new TransactionScope())
{
SqlConnection sqlConnection1 = new SqlConnection(conn);
SqlCommand sqlCommand1 = sqlConnection1.CreateCommand();
string insertCommand = "Insert Into PictureTable(Description, FileData) values('" + Guid.NewGuid().ToString() + "',Cast('' As varbinary(Max)));",
selectCommand = "Select FileData.PathName() As Path From PictureTable Where PkId =@@Identity";
//First create an empty file to have the path and then select this path
sqlCommand1.CommandText = insertCommand + selectCommand;
sqlConnection1.Open();
string filePath1 = (string)sqlCommand1.ExecuteScalar();
SqlConnection sqlConnection2 = new SqlConnection(conn);
SqlCommand sqlCommand2 = sqlConnection2.CreateCommand();
sqlCommand2.CommandText = "Select GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext";
sqlConnection2.Open();
// Prepare the filestream (file) for write
byte[] transactionContext1 = (byte[])sqlCommand2.ExecuteScalar();
SqlFileStream sqlFileStream1 = new SqlFileStream(filePath1, transactionContext1, FileAccess.Write);
// Open the source file and read its content
this.WriteToFile(stream, 5, sqlFileStream1);
sqlFileStream1.Close();
transactionScope.Complete();
}
}
public static void OverwriteFilestream()
{
try
{
string connString = new SqlConnectionStringBuilder(DataTestUtility.TCPConnectionString)
{
InitialCatalog = SetupFileStreamDB(),
IntegratedSecurity = true
}.ConnectionString;
string tempTable = SetupTable(connString);
byte[] insertedValue = BitConverter.GetBytes(3);
// Reverse the byte array, if the system architecture is little-endian.
if (BitConverter.IsLittleEndian)
{
Array.Reverse(insertedValue);
}
try
{
using SqlConnection connection = new(connString);
connection.Open();
SqlCommand command = new($"SELECT TOP(1) Photo.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(),EmployeeId FROM {tempTable} ORDER BY EmployeeId", connection);
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = transaction;
using (SqlDataReader reader = command.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);
// Overwrite the first row in the table
fileStream.Write((insertedValue), 0, 4);
}
}
transaction.Commit();
// Compare inserted and retrieved value
byte[] retrievedValue = RetrieveData(tempTable, connString, insertedValue.Length);
Assert.Equal(insertedValue, retrievedValue);
}
finally
{
// Drop Table
ExecuteNonQueryCommand($"DROP TABLE {tempTable}", connString);
}
}
finally
{
DropFileStreamDb(DataTestUtility.TCPConnectionString);
}
}
/// <summary>
/// Extends BeginWrite so that when a state object is not needed, null does not need to be passed.
/// <example>
/// sqlfilestream.BeginWrite(buffer, offset, count, callback);
/// </example>
/// </summary>
public static IAsyncResult BeginWrite(this SqlFileStream sqlfilestream, Byte[] buffer, Int32 offset, Int32 count, AsyncCallback callback)
{
if (sqlfilestream == null)
{
throw new ArgumentNullException("sqlfilestream");
}
return(sqlfilestream.BeginWrite(buffer, offset, count, callback, null));
}
/// <summary>
/// Permite almacenar un archivo en la base de datos.
/// </summary>
/// <param name="path">Ruta física del archivo en el servidor.</param>
/// <param name="contenidoArchivo">Binarios del archivo.</param>
/// <param name="longitudArchivo">Tamaño del archivo.</param>
public void EjecutarWriteFileStream(string path, IList <byte> contenidoArchivo, int longitudArchivo)
{
byte[] objContext = ObtenerContextoFileStream();
using (SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Write))
{
byte[] objContenido = contenidoArchivo.ToArray();
objSqlFileStream.Write(objContenido, 0, longitudArchivo);
}
}
public async Task ModificarArchivoContenidoStream(int consecutivoArchivo, Stream sourceStream)
{
FileStreamRowData rowData = await _context.Database
.SqlQuery <FileStreamRowData>(RowDataStatement, new SqlParameter("Consecutivo", consecutivoArchivo))
.FirstOrDefaultAsync();
using (SqlFileStream dest = new SqlFileStream(rowData.Path, rowData.Transaction, FileAccess.ReadWrite))
{
await sourceStream.CopyToAsync(dest);
}
}
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();
}
}
private static async Task UploadStreamToDatabase(Stream inputStream, Guid id, SqlConnection sqlConnection, SqlTransaction sqlTransaction)
{
int bufferSize = 100 * 1024; // 100 kB buffer
byte[] buffer = new byte[bufferSize];
long totalbytesRead = 0;
SqlCommand checkFileStreamEnabled = new SqlCommand("SELECT TOP 1 1 FROM sys.columns c WHERE OBJECT_SCHEMA_NAME(C.object_id) = 'LightDMS' AND OBJECT_NAME(C.object_id) = 'FileContent' AND c.Name = 'Content' AND c.is_filestream = 1", sqlConnection, sqlTransaction);
if (checkFileStreamEnabled.ExecuteScalar() == null)
{ //FileStream is not supported
SqlCommand fileUpdateCommand = new SqlCommand("update LightDMS.FileContent set Content.WRITE(@Data, @Offset, null) where ID = @ID", sqlConnection, sqlTransaction);
fileUpdateCommand.Parameters.Add("@Data", SqlDbType.Binary);
fileUpdateCommand.Parameters.AddWithValue("@ID", id);
fileUpdateCommand.Parameters.AddWithValue("@Offset", 0);
var fileStream = inputStream;
var bytesRead = await fileStream.ReadAsync(buffer, 0, buffer.Length);
while (bytesRead > 0)
{
if (bytesRead < buffer.Length)
{
fileUpdateCommand.Parameters["@Data"].Value = buffer.Where((val, ix) => ix < bytesRead).ToArray();
}
else
{
fileUpdateCommand.Parameters["@Data"].Value = buffer;
}
fileUpdateCommand.Parameters["@Offset"].Value = totalbytesRead;
fileUpdateCommand.ExecuteNonQuery();
totalbytesRead += bytesRead;
bytesRead = await fileStream.ReadAsync(buffer, 0, buffer.Length);
}
fileUpdateCommand.Dispose();
fileStream.Close();
}
else
{
using (SqlFileStream sfs = SqlFileStreamProvider.GetSqlFileStreamForUpload(id, sqlTransaction))
{
while (totalbytesRead < inputStream.Length)
{
var readed = await inputStream.ReadAsync(buffer, 0, bufferSize);
sfs.Write(buffer, 0, readed);
totalbytesRead += readed;
}
sfs.Close();
}
}
}
/// <summary>
/// Permite obtener un archivo en la base de datos.
/// </summary>
/// <param name="path">Ruta física del archivo en el servidor.</param>
/// <returns>Retorna el contenido de un archivo.</returns>
public byte[] EjecutarReadFileStream(string path)
{
byte[] buffer = null;
byte[] objContext = ObtenerContextoFileStream();
using (SqlFileStream objSqlFileStream = new SqlFileStream(path, objContext, FileAccess.Read))
{
buffer = new byte[(int)objSqlFileStream.Length];
objSqlFileStream.Read(buffer, 0, buffer.Length);
}
return(buffer);
}
protected override byte[] LoadDocument(BiblosDS.Library.Common.Objects.Document Document)
{
SqlFileStream stream = null;
string connectionString = Document.Storage.MainPath;
string tableName = GetTableName(Document.Storage, Document.StorageArea);
SqlTransaction transaction = null;
using (SqlConnection cnn = new SqlConnection(connectionString))
{
string sqlPathFile = string.Format(@"SELECT Document.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM {0}
WHERE DocumentID = @DocumentID", tableName);
cnn.Open();
transaction = cnn.BeginTransaction(IsolationLevel.ReadCommitted);
try
{
using (SqlCommand cmd = new SqlCommand(sqlPathFile, cnn, transaction))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@DocumentID", Document.IdDocument);
string path = string.Empty;
using (SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow))
{
if (reader.Read())
{
path = reader[0].ToString();
stream = new SqlFileStream(path, (byte[])reader.GetValue(1), FileAccess.Write, FileOptions.SequentialScan, 0);
}
else
{
throw new Exception("Document Id: " + Document.IdDocument + " not found.");
}
}
}
transaction.Commit();
}
catch (Exception)
{
try
{
transaction.Rollback();
}
catch { }
throw;
}
}
byte[] contents = new byte[stream.Length];
stream.Read(contents, 0, contents.Length);
return(contents);
}
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);
}
public static void ReadFileStream(SqlConnectionStringBuilder connStringBuilder, int colId, string fileName, Stream str)
{
using (SqlConnection connection = new SqlConnection(connStringBuilder.ToString()))
{
connection.Open();
SqlCommand command = new SqlCommand($"SELECT TOP({colId}) FileStreamData.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Files WHERE Id={colId}", connection);
SqlTransaction tran = connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = tran;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// Get the pointer for the file
string path = reader.GetString(0);
byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
List <byte> byteRead = new List <byte>();
// Create the SqlFileStream
using (Stream fileSQLStream = new SqlFileStream(path, transactionContext, FileAccess.Read, FileOptions.SequentialScan, allocationSize: 0))
{
int highLevelDivider = 200;
long highLevelChunkSize = fileSQLStream.Length / highLevelDivider;
List <long> highLevelSizeList = Enumerable.Repeat(highLevelChunkSize, highLevelDivider).ToList();
for (int i = 0; i < highLevelSizeList.Count; i++)
{
if (SumOfElements(highLevelSizeList) == fileSQLStream.Length)
{
break;
}
else
{
highLevelSizeList[i]++;
}
}
for (int i = 0; i < highLevelDivider; i++)
{
byte[] highLevelBuffer = new byte[highLevelSizeList[i]];//1MB LowLvlChunk chunk
fileSQLStream.Read(highLevelBuffer, 0, highLevelBuffer.Length);
using (Stream fileChunk = new MemoryStream(highLevelBuffer))
{
str.Write(ReadAllBytes(fileChunk));
}
}
}
}
}
tran.Commit();
}
}
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 static void AppendFilestream()
{
using (SqlConnection connection = new SqlConnection(DataTestUtility.TcpConnStr))
{
connection.Open();
string tempTable = SetupTable(connection);
byte[] insertedValue = BitConverter.GetBytes(s_insertedValues[0]);
byte appendedByte = 0x04;
insertedValue = AddByteToArray(insertedValue, appendedByte);
// Reverse the byte array, if the system architecture is little-endian.
if (BitConverter.IsLittleEndian)
{
Array.Reverse(insertedValue);
}
SqlCommand command = new SqlCommand($"SELECT TOP(1) Photo.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT(),EmployeeId FROM {tempTable} ORDER BY EmployeeId", connection);
SqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = transaction;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// Get the pointer for file
string path = reader.GetString(0);
byte[] transactionContext = reader.GetSqlBytes(1).Buffer;
using (Stream fileStream = new SqlFileStream(path, transactionContext, FileAccess.ReadWrite, FileOptions.SequentialScan, allocationSize: 0))
{
// Seek to the end of the file
fileStream.Seek(0, SeekOrigin.End);
// Append a single byte
fileStream.WriteByte(appendedByte);
}
}
}
transaction.Commit();
// Compare inserted and retrieved value
byte[] retrievedValue = RetrieveData(tempTable, connection, insertedValue.Length);
Assert.Equal(insertedValue, retrievedValue);
// Drop Table
ExecuteNonQueryCommand($"DROP TABLE {tempTable}", connection);
}
}
/// <summary>
/// Extends BeginWrite so that buffer offset of 0 and call to Array.Length are not needed.
/// <example>
/// sqlfilestream.BeginWrite(buffer, callback);
/// </example>
/// </summary>
public static IAsyncResult BeginWrite(this SqlFileStream sqlfilestream, Byte[] buffer, AsyncCallback callback)
{
if (sqlfilestream == null)
{
throw new ArgumentNullException("sqlfilestream");
}
if (buffer == null)
{
throw new ArgumentNullException("buffer");
}
return(sqlfilestream.BeginWrite(buffer, 0, buffer.Length, callback));
}
public List <TradeRqmtConfirmBlobDto> GetAll()
{
const string selectTSqlCmd = @"SELECT ID, TRADE_RQMT_CONFIRM_ID, IMAGE_FILE_EXT, DOC_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM TRADE_RQMT_CONFIRM_BLOB";
var result = new List <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))
{
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();
}
result.Add(new TradeRqmtConfirmBlobDto
{
Id = DBUtils.HandleInt32IfNull(dataReader["ID"].ToString()),
TradeRqmtConfirmId = DBUtils.HandleInt32IfNull(dataReader["TRADE_RQMT_CONFIRM_ID"].ToString()),
ImageFileExt = dataReader["IMAGE_FILE_EXT"].ToString(),
DocBlob = blobByteArray
});
}
}
}
}
}
ts.Complete();
}
return(result);
}