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();
}
}
public Int32 Insert(TradeRqmtConfirmBlobDto pData)
{
const string insertTSql =
@"INSERT INTO TRADE_RQMT_CONFIRM_BLOB(ID, TRADE_RQMT_CONFIRM_ID, IMAGE_FILE_EXT)
VALUES(@ID, @TRADE_RQMT_CONFIRM_ID, @IMAGE_FILE_EXT);
SELECT DOC_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM TRADE_RQMT_CONFIRM_BLOB
WHERE ID = @ID";
int newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string serverPath;
byte[] serverTxn;
using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(insertTSql, conn))
{
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = newId;
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 rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
rdr.Read();
serverPath = rdr.GetSqlString(0).Value;
serverTxn = rdr.GetSqlBinary(1).Value;
rdr.Close();
}
}
//SaveDocImageFile(pFileName, serverPath, serverTxn);
SaveDocImageByteArray(pData.DocBlob, serverPath, serverTxn);
}
ts.Complete();
}
return(newId);
}
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();
}
}
public Int32 Insert(TradeRqmtConfirmBlobDto pData)
{
const string insertTSql =
@"INSERT INTO TRADE_RQMT_CONFIRM_BLOB(ID, TRADE_RQMT_CONFIRM_ID, IMAGE_FILE_EXT)
VALUES(@ID, @TRADE_RQMT_CONFIRM_ID, @IMAGE_FILE_EXT);
SELECT DOC_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT()
FROM TRADE_RQMT_CONFIRM_BLOB
WHERE ID = @ID";
int newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME);
string serverPath;
byte[] serverTxn;
using (TransactionScope ts = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(sqlConnStr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(insertTSql, conn))
{
cmd.Parameters.Add("@ID", SqlDbType.Int).Value = newId;
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 rdr = cmd.ExecuteReader(CommandBehavior.SingleRow))
{
rdr.Read();
serverPath = rdr.GetSqlString(0).Value;
serverTxn = rdr.GetSqlBinary(1).Value;
rdr.Close();
}
}
//SaveDocImageFile(pFileName, serverPath, serverTxn);
SaveDocImageByteArray(pData.DocBlob, serverPath, serverTxn);
}
ts.Complete();
}
return newId;
}
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;
}
