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