/// <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();
}
}
}
}