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 *****************************/
}
}