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