DataAccess Layer

DataAccess Layer

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Web.UI.WebControls;
using .PurchaseOrder.BusinessLogicLayer;

namespace .PurchaseOrder.DataAccessLayer
{
    public class OrdersDataAccess : DataAccess
    {
        //TextHeader.iId,cPrefix+cOrder as OrderNo,dWindowStart,TextFile.dUpload,TextProductionZone.cProductionZone,cName,dWindowEnd,cStatus
        private const string SP_ORDERS_GET = "TextGetOrders";
        private const string SP_ORDERS_GET2 = "TextGetOrdersSort";

        private void TGenerateOrderItemListFromReader(SqlDataReader returnData, ref List userInfoList)
        {
            while (returnData.Read())
            {
                OrderItem orderItem = new OrderItem();

                orderItem.OrderID = (int)returnData["iId"];
                orderItem.OrderNo = (string)returnData["OrderNo"];
                orderItem.OrderDate = (DateTime)returnData["dWindowStart"];
                orderItem.DateUploaded = (DateTime)returnData["dUpload"];
                orderItem.ProductionZone = (string)returnData["cProductionZone"];
                orderItem.Manufacturer = (string)returnData["cName"];
                orderItem.PrintDate = (DateTime)returnData["dWindowEnd"];
                orderItem.Status = (string)returnData["cStatus"];

                userInfoList.Add(orderItem);
            }
        }

        public List GetOrdersByID(int TextId,int UserType)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@TextID", SqlDbType.Int, 0, ParameterDirection.Input, TextId);
            AddParamToSQLCmd(sqlCmd, "@UserType", SqlDbType.Int, 0, ParameterDirection.Input, UserType);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_ORDERS_GET);

            List orderItemList = new List();

            TExecuteReaderCmd(sqlCmd, TGenerateOrderItemListFromReader, ref orderItemList);

            return orderItemList;
        }
        public List GetOrdersByID(int TextId, int UserType,string sortParameter)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@TextID", SqlDbType.Int, 0, ParameterDirection.Input, TextId);
            AddParamToSQLCmd(sqlCmd, "@UserType", SqlDbType.Int, 0, ParameterDirection.Input, UserType);
            AddParamToSQLCmd(sqlCmd, "@ColumnName", SqlDbType.Char, 50, ParameterDirection.Input, sortParameter);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_ORDERS_GET2);

            List orderItemList = new List();

            TExecuteReaderCmd(sqlCmd, TGenerateOrderItemListFromReader, ref orderItemList);

            return orderItemList;
        }
    }
}

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using .AIA.Common;
using System.Collections;
using System.Collections.Specialized;
using System.Collections.Generic;

namespace .AIA.DataAccessLayer
{
    public class DataAccess
    {
        protected string ConnectionString = Settings.ConnectionString;
       
        /*** DELEGATE ***/
        protected delegate void TGenerateListFromReader(SqlDataReader returnData, ref List tempList);

        /*****************************  BASE CLASS IMPLEMENTATION *****************************/

        /*****************************  SQL HELPER METHODS *****************************/
        protected void AddParamToSQLCmd(SqlCommand sqlCmd,
                                      string paramId,
                                      SqlDbType sqlType,
                                      int paramSize,
                                      ParameterDirection paramDirection,
                                      object paramvalue)
        {

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));
            if (paramId == string.Empty)
                throw (new ArgumentOutOfRangeException("paramId"));

            SqlParameter newSqlParam = new SqlParameter();
            newSqlParam.ParameterName = paramId;
            newSqlParam.SqlDbType = sqlType;
            newSqlParam.Direction = paramDirection;

            if (paramSize > 0)
                newSqlParam.Size = paramSize;

            if (paramvalue != null)
                newSqlParam.Value = paramvalue;

            sqlCmd.Parameters.Add(newSqlParam);
        }

        protected int ExecuteScalarCmd(SqlCommand sqlCmd)
        {
            int strOuput = 0;
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;
                cn.Open();
                sqlCmd.ExecuteScalar();
                strOuput = (int)sqlCmd.Parameters["@ArtWorkId"].Value;
            }
            return strOuput;
        }

        protected void SetCommandType(SqlCommand sqlCmd, CommandType cmdType, string cmdText)
        {
            sqlCmd.CommandType = cmdType;
            sqlCmd.CommandText = cmdText;
        }

        protected void TExecuteReaderCmd(SqlCommand sqlCmd, TGenerateListFromReader gcfr, ref List List)
        {
            if (ConnectionString == string.Empty)
                throw (new ArgumentOutOfRangeException("ConnectionString"));

            if (sqlCmd == null)
                throw (new ArgumentNullException("sqlCmd"));

            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                sqlCmd.Connection = cn;

                cn.Open();

                gcfr(sqlCmd.ExecuteReader(), ref List);
            }
        }

        /*****************************  GENARATE List HELPER METHODS  *****************************/
    }
}