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); }
private int GetNextRowId(ImagesDto pData) { var seqName = (pData.Type == ImagesDtoType.Inbound) ? "SEQ_INBOUND_DOCS_BLOB" : "SEQ_ASSOCIATED_DOCS_BLOB"; var newId = DBUtils.GetNextSequence(sqlConnStr, seqName); return(newId); }
public Int32 Group(List <TradeGroupDto> pDataList) { Int32 rowsInserted = 0; Int32 rowIns = 0; Int32 tradeGroupId = 0; string sql = "Insert into " + DBUtils.SCHEMA_NAME + "trade_group " + " (id, trade_id, xref) " + " Values " + " (@id, @trade_id, @xref) "; using (SqlConnection conn = new SqlConnection(sqlConnStr)) { conn.Open(); using (SqlTransaction trans = conn.BeginTransaction()) { using (SqlCommand cmd = new SqlCommand(sql, conn, trans)) { cmd.CommandType = System.Data.CommandType.Text; cmd.Parameters.Add("@id", System.Data.SqlDbType.Int); cmd.Parameters.Add("@trade_id", System.Data.SqlDbType.Int); cmd.Parameters.Add("@xref", System.Data.SqlDbType.VarChar); try { foreach (TradeGroupDto tradeGroupItem in pDataList) { tradeGroupId = DBUtils.GetNextSequence(sqlConnStr, SEQ_NAME); cmd.Parameters["@id"].Value = tradeGroupId; cmd.Parameters["@trade_id"].Value = tradeGroupItem.TradeId; cmd.Parameters["@xref"].Value = tradeGroupItem.Xref; rowIns = cmd.ExecuteNonQuery(); //rowIns return value is erratic. This approach produces reliable results. rowIns = rowIns > 0 ? 1 : 0; rowsInserted += rowIns; } trans.Commit(); } catch (Exception e) { trans.Rollback(); throw new Exception("An error occurred while updating the database." + Environment.NewLine + "Error CNF-366 in " + PROJ_FILE_NAME + ".Group(): " + e.Message); } } } } return(rowsInserted); }
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 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 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); }