/// <summary> /// This method is just for getting rows from the MailingRec_Lookup table /// The Stored Procedure will make three responces /// 1. if @Category = '' and @SubCategory = '' ... where [SystemID] = @SystemID /// 2. else if @SystemID = 0 ... where [Category]=@Category and [SubCategory]=@SubCategory /// 3. else ... where [Category]=@Category and [SubCategory]=@SubCategory and [SystemID] = @SystemID /// #Note that this is duplicated in DalMon.cs /// </summary> /// <param name="cErrorToken">To return any errors</param> /// <param name="cLookupToken">To send and receive lookup values</param> public void do_sp_GetMailingReq_Lookup(c_ErrorToken cErrorToken, List <c_LookupToken> lstcLookupToken, string strCategory, string strSubCategory, int iRenderingSystemID) { DataSet dataset = new DataSet(); cErrorToken.Reset(); strCurrentMethod = "do_sp_GetMailingReq_Lookup()-strCategory=" + strCategory + ", strSubCategory=" + strSubCategory + ", iRenderingSystemID=" + iRenderingSystemID.ToString() + "."; try { using (SqlConnection connection = new SqlConnection()) { using (SqlCommand command = new SqlCommand()) { using (SqlDataAdapter adapter = new SqlDataAdapter()) { connection.ConnectionString = this.strConnectionString; command.CommandType = CommandType.StoredProcedure; command.CommandText = "sp_GetMailingReq_Lookup"; command.Parameters.Add("@Category", SqlDbType.VarChar, 64); command.Parameters.Add("@SubCategory", SqlDbType.VarChar, 64); command.Parameters.Add("@SystemID", SqlDbType.Int); command.Parameters.Add("@RetCode", SqlDbType.Int); command.Parameters["@RetCode"].Direction = ParameterDirection.Output; command.Parameters.Add("@RetStr", SqlDbType.VarChar, 255); command.Parameters["@RetStr"].Direction = ParameterDirection.Output; command.Parameters["@Category"].Value = strCategory; command.Parameters["@SubCategory"].Value = strSubCategory; command.Parameters["@SystemID"].Value = iRenderingSystemID; adapter.SelectCommand = command; connection.Open(); command.Connection = connection; adapter.Fill(dataset); if (String.IsNullOrEmpty(command.Parameters["@RetStr"].Value.ToString()) == false) { cErrorToken.setAllErrorsStrings("SQL Error(D1568):" + command.Parameters["@RetStr"].Value.ToString() + ". Code=" + command.Parameters["@RetCode"].Value.ToString() + ".", "", strCurrentMethod); } lstcLookupToken.Clear(); // get rid of any already there if (dataset.Tables.Count > 0) { //if (dataset.Tables[0].Rows.Count > 0) //{ foreach (DataRow dr in dataset.Tables[0].Rows) { c_LookupToken cLookupToken = new c_LookupToken(); cLookupToken.Category = dr["Category"].ToString(); cLookupToken.SubCategory = dr["SubCategory"].ToString(); cLookupToken.sValue = dr["sValue"].ToString(); string str = dr["iValue"].ToString(); int ii = 0; if (Int32.TryParse(str, out ii) == true) { cLookupToken.iValue = ii; } str = dr["SystemID"].ToString(); ii = 0; if (Int32.TryParse(str, out ii) == true) { cLookupToken.SystemID = ii; } lstcLookupToken.Add(cLookupToken); } //} } } } } } catch (SqlException sqlEx) { cErrorToken.setAllErrorsStrings("SQL Error(D1599)", sqlEx.Message.ToString(), strCurrentMethod); } catch (Exception ex) { cErrorToken.setAllErrorsStrings("Error(D1603)", ex.Message.ToString(), strCurrentMethod); } return; }
/// <summary> /// This one is just for updating a single row in the MailingRec_Lookup table /// </summary> /// <param name="cErrorToken">To return any errors</param> /// <param name="cLookupToken">To send and receive lookup values</param> public void do_sp_UpdateMailingReq_Lookup(c_ErrorToken cErrorToken, c_LookupToken cLookupToken) { cErrorToken.Reset(); strCurrentMethod = "do_sp_UpdateMailingReq_Lookup()-" + cLookupToken.ToString() + "."; using (SqlConnection connection = new SqlConnection()) { connection.ConnectionString = this.strConnectionString; using (SqlCommand command = new SqlCommand()) { command.Connection = connection; command.CommandType = CommandType.StoredProcedure; command.CommandText = "sp_UpdateMailingReq_Lookup"; command.Parameters.Add("@Category", SqlDbType.VarChar, 64); command.Parameters.Add("@SubCategory", SqlDbType.VarChar, 64); command.Parameters.Add("@SystemID", SqlDbType.Int); command.Parameters.Add("@sValue", SqlDbType.VarChar, 512); command.Parameters.Add("@iValue", SqlDbType.Int); command.Parameters.Add("@RetCode", SqlDbType.Int); command.Parameters["@RetCode"].Direction = ParameterDirection.Output; command.Parameters.Add("@RetStr", SqlDbType.VarChar, 255); command.Parameters["@RetStr"].Direction = ParameterDirection.Output; command.Parameters["@Category"].Value = cLookupToken.Category; command.Parameters["@SubCategory"].Value = cLookupToken.SubCategory; command.Parameters["@SystemID"].Value = cLookupToken.SystemID; command.Parameters["@sValue"].Value = cLookupToken.sValue; command.Parameters["@iValue"].Value = cLookupToken.iValue; try { connection.Open(); command.ExecuteNonQuery(); if (String.IsNullOrEmpty(command.Parameters["@RetStr"].Value.ToString()) == false) { cErrorToken.setAllErrorsStrings("SQL Error(D1137):" + command.Parameters["@RetStr"].Value.ToString() + ". Code=" + command.Parameters["@RetCode"].Value.ToString() + ".", "", strCurrentMethod); } } catch (SqlException sqlEx) { cErrorToken.setAllErrorsStrings("SQL Error(D1391)", sqlEx.Message.ToString(), strCurrentMethod); } catch (Exception ex) { cErrorToken.setAllErrorsStrings("Error(D1195)", ex.Message.ToString(), strCurrentMethod); } finally { connection.Close(); } } } }