private static ImagesServerInformation PerformRowInsert(ImagesDto pData, SqlConnection conn, string insertTSql, int newId) { conn.Open(); using (var cmd = new SqlCommand(insertTSql, conn)) { cmd.Parameters.Add("@ID", SqlDbType.Int).Value = newId; cmd.Parameters.Add("@DOCS_ID", SqlDbType.VarChar).Value = pData.DocsId; cmd.Parameters.Add("@ORIG_IMAGE_FILE_EXT", SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.OriginalImageFileExt.ToUpper().Replace(".", "")); cmd.Parameters.Add("@MARKUP_IMAGE_FILE_EXT", SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.MarkupImageFileExt.ToUpper().Replace(".", "")); return(ReadImagesServerInformation(cmd)); } }
public void Update(ImagesDto pData) { var updateSql = String.Format( @"UPDATE {0}{1} set {2} = @DocsId, ORIG_IMAGE_FILE_EXT = @OrigImageFileExt, MARKUP_IMAGE_FILE_EXT = @MarkupImageFileExt where ID = @Id; SELECT ORIG_IMAGE_BLOB.PathName(), MARKUP_IMAGE_BLOB.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM {0}{1} WHERE ID = @Id" , DBUtils.SCHEMA_NAME, (pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_BLOB" : "ASSOCIATED_DOCS_BLOB", (pData.Type == ImagesDtoType.Inbound) ? "INBOUND_DOCS_ID" : "ASSOCIATED_DOCS_ID"); using (var ts = new TransactionScope()) { using (var conn = new SqlConnection(sqlConnStr)) { conn.Open(); ImagesServerInformation imagesServerInfo; using (var cmd = new SqlCommand(updateSql, conn)) { cmd.Parameters.AddWithValue("@Id", pData.ImageId); cmd.Parameters.AddWithValue("@DocsId", pData.DocsId); cmd.Parameters.AddWithValue("@OrigImageFileExt", DBUtils.ValueStringOrDBNull(pData.OriginalImageFileExt.ToUpper().Replace(".", ""))); cmd.Parameters.AddWithValue("@MarkupImageFileExt", DBUtils.ValueStringOrDBNull(pData.MarkupImageFileExt.ToUpper().Replace(".", ""))); imagesServerInfo = ReadImagesServerInformation(cmd); } SaveDocImage(pData.MarkupImage, imagesServerInfo.MarkupServerPath, imagesServerInfo.ServerTransaction); SaveDocImage(pData.OriginalImage, imagesServerInfo.OriginalServerPath, imagesServerInfo.ServerTransaction); } ts.Complete(); } }
public Int32 UpdateStatus(AssociatedDocsDto pAssocDocsData) { //Note -- this procedure also inserts data string updateSql = DBUtils.SCHEMA_NAME + "PKG_INBOUND$p_update_asso_status"; Int32 rowsUpdated = 0; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_inb_id", System.Data.SqlDbType.Int).Value = pAssocDocsData.InboundDocsId; cmd.Parameters.Add("@p_file_name", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pAssocDocsData.FileName); cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pAssocDocsData.TradeId; cmd.Parameters.Add("@p_status", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pAssocDocsData.DocStatusCode); cmd.Parameters.Add("@p_cdty_grp_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pAssocDocsData.CdtyGroupCode); cmd.Parameters.Add("@p_cpty_sn", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pAssocDocsData.CptySn); cmd.Parameters.Add("@p_broker_sn", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pAssocDocsData.BrokerSn); cmd.Parameters.Add("@p_rqmt_id", System.Data.SqlDbType.Int).Value = pAssocDocsData.TradeRqmtId; cmd.Parameters.Add("@p_rqmt_status", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pAssocDocsData.RqmtStatus); cmd.Parameters.Add("@p_rqmt_type", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pAssocDocsData.DocTypeCode); cmd.Parameters.Add("@p_sec_check", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pAssocDocsData.SecValidateReqFlag); cmd.Parameters.Add("@p_index_val", System.Data.SqlDbType.Int).Value = pAssocDocsData.IndexVal; SqlParameter returnValue = new SqlParameter { Direction = ParameterDirection.ReturnValue }; cmd.Parameters.Add(returnValue); conn.Open(); cmd.ExecuteNonQuery(); return(Convert.ToInt32(returnValue.Value)); } } return(rowsUpdated); }
public Int32 UpdateDetermineActions(Int32 pTradeId, string pOptsDetActFlag) { string updateSql = DBUtils.SCHEMA_NAME + "pkg_trade_summary$p_update_determine_actions"; Int32 rowsUpdated = 0; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pTradeId; cmd.Parameters.Add("@p_ops_det_act_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pOptsDetActFlag); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
public Int32 UpdateFinalApproval(Int32 pTradeId, string pFinalApprovalStatus) { string updateSql = DBUtils.SCHEMA_NAME + "pkg_trade_summary$p_update_final_approval"; Int32 rowsUpdated = 0; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pTradeId; cmd.Parameters.Add("@p_fnlapp_status", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pFinalApprovalStatus); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
public Int32 Update(UserFiltersOpsmgrDto pData) { Int32 rowsUpdated = 0; string sql = "update " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " + "set user_id = @user_id, descr = @descr, filter_expr = @filter_expr " + " where id = @id"; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pData.Id; cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId); cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr); cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
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 Int32 AddTradeRqmt(Int32 pTradeId, string pRqmtCode, string pReference, string pCmt) { string updateSql = DBUtils.SCHEMA_NAME + "PKG_TRADE_RQMT$P_ADD_TRADE_RQMT_WITH_ID"; Int32 rowsInserted = 0; Int32 seqNo = 0; string seqName = "SEQ_TRADE_RQMT"; seqNo = DBUtils.GetNextSequence(sqlConnStr, seqName); using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { //There are 3 overloaded versions of function, containing 3, 4 and 6 parameters. cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_id", System.Data.SqlDbType.Int).Value = seqNo; cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pTradeId; cmd.Parameters.Add("@p_rqmt_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pRqmtCode); cmd.Parameters.Add("@p_reference", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pReference); cmd.Parameters.Add("@p_cmt", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pCmt); conn.Open(); rowsInserted = cmd.ExecuteNonQuery(); } } if (rowsInserted == 0) { return(rowsInserted); } else { return(seqNo); } }
public Int32 Insert(InboundDocsDto pData) { Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME); string sql = "Insert into " + DBUtils.SCHEMA_NAME + "inbound_docs " + " (id, caller_ref, sent_to, rcvd_ts, file_name, sender, cmt, doc_status_code, has_auto_ascted_flag, " + " mapped_cpty_sn, mapped_brkr_sn, mapped_cdty_code, job_ref ) " + " Values " + " (@id, @caller_ref, @sent_to, @rcvd_ts, @file_name, @sender, @cmt, @doc_status_code, @has_auto_ascted_flag, " + " @mapped_cpty_sn, @mapped_brkr_sn, @mapped_cdty_code, @job_ref ) "; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = newId; cmd.Parameters.Add("@caller_ref", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.CallerRef); cmd.Parameters.Add("@sent_to", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.SentTo); cmd.Parameters.Add("@rcvd_ts", System.Data.SqlDbType.DateTime).Value = DBUtils.ValueDateTimeOrDbNull(pData.RcvdTs); cmd.Parameters.Add("@file_name", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FileName); cmd.Parameters.Add("@sender", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Sender); cmd.Parameters.Add("@cmt", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Cmt); cmd.Parameters.Add("@doc_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.DocStatusCode); cmd.Parameters.Add("@has_auto_ascted_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.HasAutoAsctedFlag); cmd.Parameters.Add("@mapped_cpty_sn", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.MappedCptySn); cmd.Parameters.Add("@mapped_brkr_sn", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.MappedBrkrSn); cmd.Parameters.Add("@mapped_cdty_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.MappedCdtyCode); cmd.Parameters.Add("@job_ref", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.JobRef); conn.Open(); cmd.ExecuteNonQuery(); } } return(newId); }
public Int32 Insert(InbAttribMapPhraseDto pData) { Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME); string sql = "Insert into " + DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase " + " (id, inb_attrib_map_val_id, phrase, active_flag) " + " Values " + " (@id, @inb_attrib_map_val_id, @phrase, @active_flag) "; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = newId; cmd.Parameters.Add("@inb_attrib_map_val_id", System.Data.SqlDbType.Int).Value = pData.InbAttribMapValId; cmd.Parameters.Add("@phrase", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Phrase); cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag); conn.Open(); cmd.ExecuteNonQuery(); } } return(newId); }
public Int32 Update(InbAttribMapPhraseDto pData) { Int32 rowsUpdated = 0; string sql = "update " + DBUtils.SCHEMA_NAME + "inb_attrib_map_phrase " + "set inb_attrib_map_val_id = @inb_attrib_map_val_id, phrase = @phrase, active_flag = @active_flag " + " where id = @id"; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pData.Id; cmd.Parameters.Add("@inb_attrib_map_val_id", System.Data.SqlDbType.Int).Value = pData.InbAttribMapValId; cmd.Parameters.Add("@phrase", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Phrase); cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag); conn.Open(); SqlTransaction trans = conn.BeginTransaction(); try { cmd.Transaction = trans; rowsUpdated = cmd.ExecuteNonQuery(); trans.Commit(); } catch (Exception e) { trans.Rollback(); //throw; } } } return(rowsUpdated); }
public Int32 Update(TradeRqmtConfirm pData) { string updateSql = DBUtils.SCHEMA_NAME + "PKG_RQMT_CONFIRM$p_update_rqmt_confirm"; Int32 rowsUpdated = 0; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_id", System.Data.SqlDbType.Int).Value = pData.Id; cmd.Parameters.Add("@p_rqmt_id", System.Data.SqlDbType.Int).Value = pData.RqmtId; cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pData.TradeId; cmd.Parameters.Add("@p_template_name", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.TemplateName); cmd.Parameters.Add("@p_confirm_cmt", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ConfirmCmt); cmd.Parameters.Add("@p_fax_telex_ind", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexInd); cmd.Parameters.Add("@p_fax_telex_number", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexNumber); cmd.Parameters.Add("@p_confirm_label", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ConfirmLabel); cmd.Parameters.Add("@p_next_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.NextStatusCode); cmd.Parameters.Add("@p_active_flag", System.Data.SqlDbType.VarChar).Value = pData.ActiveFlag; conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
public Int32 Insert(TradeRqmtConfirm pData) { string updateSql = DBUtils.SCHEMA_NAME + "PKG_RQMT_CONFIRM$p_insert_rqmt_confirm"; Int32 rowsInserted = 0; Int32 seqNo = 0; string seqName = "SEQ_TRADE_RQMT_CONFIRM"; seqNo = DBUtils.GetNextSequence(sqlConnStr, seqName); using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_id", System.Data.SqlDbType.Int).Value = seqNo; cmd.Parameters.Add("@p_rqmt_id", System.Data.SqlDbType.Int).Value = pData.RqmtId; cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pData.TradeId; cmd.Parameters.Add("@p_template_name", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.TemplateName); cmd.Parameters.Add("@p_confirm_cmt", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ConfirmCmt); cmd.Parameters.Add("@p_fax_telex_ind", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexInd); cmd.Parameters.Add("@p_fax_telex_number", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexNumber); cmd.Parameters.Add("@p_confirm_label", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ConfirmLabel); cmd.Parameters.Add("@p_next_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.NextStatusCode); conn.Open(); rowsInserted = cmd.ExecuteNonQuery(); } } if (rowsInserted == 0) { return(rowsInserted); } else { return(seqNo); } }
public Int32 Update(InbAttribMapValDto pData) { Int32 rowsUpdated = 0; string sql = "update " + DBUtils.SCHEMA_NAME + "inb_attrib_map_val " + "set inb_attrib_code = @inb_attrib_code, mapped_value = @mapped_value, " + " descr = @descr, active_flag = @active_flag " + " where id = @id"; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pData.Id; cmd.Parameters.Add("@inb_attrib_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.InbAttribCode); cmd.Parameters.Add("@mapped_value", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.MappedValue); cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr); cmd.Parameters.Add("@active_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.ActiveFlag); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
public Int32 UpdateFlag(InboundDocUserFlagDto pUserFlagData) { string updateSql = DBUtils.SCHEMA_NAME + "PKG_INBOUND_EXT$p_update_user_flag"; Int32 rowsUpdated = 0; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_inbound_doc_id", System.Data.SqlDbType.Int).Value = pUserFlagData.InboundDocId; cmd.Parameters.Add("@p_inbound_user", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pUserFlagData.InboundUser); cmd.Parameters.Add("@p_flag_type", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pUserFlagData.FlagType); cmd.Parameters.Add("@p_comment", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pUserFlagData.Comments); cmd.Parameters.Add("@p_update_delete_ind", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pUserFlagData.UpdateDeleteInd); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
public Int32 GetCount(Int32 pInboundDocsId, string pDocStatusCode) { Int32 result = 0; string sql = "select count(*) cnt from " + DBUtils.SCHEMA_NAME + "associated_docs " + "where inbound_docs_id = @inbound_docs_id and doc_status_code = @doc_status_code"; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@inbound_docs_id", System.Data.SqlDbType.Int).Value = pInboundDocsId; cmd.Parameters.Add("@doc_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pDocStatusCode); conn.Open(); using (SqlDataReader dataReader = cmd.ExecuteReader()) { if (dataReader.HasRows) { while (dataReader.Read()) { result = dataReader.GetInt32(dataReader.GetOrdinal("cnt")); } } } } } return(result); }
public Int32 MapCallerRef(string pCallerRef, string pCptySn, string pRefType) { string updateSql = DBUtils.SCHEMA_NAME + "PKG_RQMT_CONFIRM$p_map_caller_ref"; Int32 rowsUpdated = 0; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_caller_ref", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pCallerRef); cmd.Parameters.Add("@p_cpty_sn", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pCptySn); cmd.Parameters.Add("@p_ref_type", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pRefType); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
public Int32 Update(InboundDocsDto pData) { string updateSql = DBUtils.SCHEMA_NAME + "PKG_INBOUND$p_update_inbound_doc"; Int32 rowsUpdated = 0; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_id", System.Data.SqlDbType.Int).Value = pData.Id; cmd.Parameters.Add("@p_caller_ref", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.CallerRef); cmd.Parameters.Add("@p_sent_to", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.SentTo); cmd.Parameters.Add("@p_file_name", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FileName); cmd.Parameters.Add("@p_sender", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Sender); cmd.Parameters.Add("@p_cmt", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Cmt); cmd.Parameters.Add("@p_doc_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.DocStatusCode); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
public Int32 UpdateTradeRqmt(Int32 pId, DateTime pCompletedDt, string pSecondChk, string pStatusCode, string pReference, string pCmt) { string updateSql = DBUtils.SCHEMA_NAME + "PKG_TRADE_RQMT$p_update_trade_rqmt"; Int32 rowsUpdated = 0; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { //There are 3 overloaded versions of function, containing 3, 4 and 6 parameters. cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_rqmt_id", System.Data.SqlDbType.Int).Value = pId; cmd.Parameters.Add("@p_completion_date", System.Data.SqlDbType.Date).Value = DBUtils.ValueDateTimeOrDbNull(pCompletedDt); cmd.Parameters.Add("@p_second_chk", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pSecondChk); cmd.Parameters.Add("@p_status", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pStatusCode); cmd.Parameters.Add("@p_reference", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pReference); cmd.Parameters.Add("@p_cmt", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pCmt); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
public Int32 UpdateStatus(Int32 pId, string pStatus) { Int32 rowsUpdated = 0; string sql = "update " + DBUtils.SCHEMA_NAME + "inbound_docs " + " set doc_status_code = @doc_status_code " + " where id = @id"; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = pId; cmd.Parameters.Add("@doc_status_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pStatus); conn.Open(); rowsUpdated = cmd.ExecuteNonQuery(); } } return(rowsUpdated); }
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 SetFinalApprovalFlag(Int32 pTradeId, string pFinalApprovalFlag, string pOnlyIfReadyFlag, string pUserName) { string updateSql = DBUtils.SCHEMA_NAME + "pkg_trade_appr$p_set_final_approval_flag"; Int32 rowsUpdated = 0; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(updateSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pTradeId; cmd.Parameters.Add("@p_approval_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pFinalApprovalFlag); cmd.Parameters.Add("@p_only_if_ready_flag", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pOnlyIfReadyFlag); cmd.Parameters.Add("@p_appr_by_username", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pUserName); cmd.Parameters.Add("@open_rqmt_ct", System.Data.SqlDbType.Int).Direction = ParameterDirection.Output; conn.Open(); cmd.ExecuteNonQuery(); rowsUpdated = Convert.ToInt32(cmd.Parameters["@open_rqmt_ct"].Value); } } return(rowsUpdated); }
public void Insert(FaxLogSentDto pData) { //int newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME); string insertSql = DBUtils.SCHEMA_NAME + "PKG_RQMT_CONFIRM$p_insert_fax_log_sent"; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(insertSql, conn)) { cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Parameters.Add("@p_trade_id", System.Data.SqlDbType.Int).Value = pData.TradeId; cmd.Parameters.Add("@p_doc_type", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.DocType); cmd.Parameters.Add("@p_sender", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Sender); cmd.Parameters.Add("@p_telex_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexCode); cmd.Parameters.Add("@p_telex_number", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FaxTelexNumber); cmd.Parameters.Add("@p_doc_ref_code", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.DocRefCode); conn.Open(); cmd.ExecuteNonQuery(); } } //return newId; }
public Int32 Insert(UserFiltersOpsmgrDto pData) { Int32 newId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME); string sql = "Insert into " + DBUtils.SCHEMA_NAME + "user_filters_opsmgr " + " (id, user_id, descr, filter_expr) " + " Values " + " (@id, @user_id, @descr, @filter_expr) "; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { using (SqlCommand cmd = new SqlCommand(sql, conn)) { cmd.Parameters.Add("@id", System.Data.SqlDbType.Int).Value = newId; cmd.Parameters.Add("@user_id", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.UserId); cmd.Parameters.Add("@descr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.Descr); cmd.Parameters.Add("@filter_expr", System.Data.SqlDbType.VarChar).Value = DBUtils.ValueStringOrDBNull(pData.FilterExpr); conn.Open(); cmd.ExecuteNonQuery(); } } return(newId); }