Beispiel #1
0
/// <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;
        }
Beispiel #2
0
/// <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();
                                                                             }
                                                                         }
                                                                     }
                                                                 }