How to write a simple SQL Server Data Access Layer – Part-2

Part 2 of this series gives you the detail on how to use the DAL to insert records using Stored Procedures. The function ExecuteNonQueryProcedure inside the DAL Class accepts parameters in the form of HashTable. First parameter to this function is the name of the Stored Procedure, second parameter the HashTable we construct with parameter values to insert.

Below is the code which inserts a record into the application table with the “appID”, “appCat”, “appName”, “appDesc”, “appStatus” columns. We also constructed a hash table called parameterlist to pass the required values. Here is how you can call this function.

   ///

        /// InsertApplication method will insert a record in the Table.
        ///
        ///
        ///Integer Status
        public void InsertApplication(int appID, int appCat, string appName, string appDesc, bool appStatus)
        {
            Hashtable parameterlist = new Hashtable();
            parameterlist.Add("@IN_APP_ID", Convert.ToInt32(appID));
            parameterlist.Add("@IN_CAT_ID", Convert.ToInt32(appCat));
            parameterlist.Add("@IN_APP_NAME", (appName != null ? (appName.Trim().Length == 0 ? Convert.DBNull : appName) : Convert.DBNull));
            parameterlist.Add("@IN_APP_DESC", (appDesc != null ? (appDesc.Trim().Length == 0 ? Convert.DBNull : appDesc) : Convert.DBNull));
            parameterlist.Add("@IN_APP_STATUS", appStatus);
            try
            {
                SqlServerDAL objSqlServerDAL = SqlServerDAL.GetInstance(ConfigurationManager.ConnectionStrings["SQLConnStr"].ToString().Trim());
                objSqlServerDAL.Open();
                int status = objSqlServerDAL.ExecuteNonQueryProcedure("USP_APP_INSERT", parameterlist);
                objSqlServerDAL.Dispose();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

The connection string is stored in the web.config under the configuration section. Here is how iam storing.