using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using NorthwindDataModel;
namespace NorthwindDataAccessServices
{
public class OrderDataAccessService
{
/// <summary>
/// Load Order Customer
/// </summary>
/// <param name="sqlDataReader"></param>
/// <returns></returns>
private OrdersCustomer LoadOrderCustomer(SqlDataReader sqlDataReader)
{
OrdersCustomer orderCustomer = new OrdersCustomer();
DataReader dataReader = new DataReader(sqlDataReader);
orderCustomer.Customer.CustomerID = dataReader.GetString("CustomerID");
orderCustomer.Customer.CompanyName = dataReader.GetString("CompanyName");
orderCustomer.Customer.Address = dataReader.GetString("Address");
orderCustomer.Customer.City = dataReader.GetString("City");
orderCustomer.Customer.ContactName = dataReader.GetString("ContactName");
orderCustomer.Customer.ContactTitle = dataReader.GetString("ContactTitle");
orderCustomer.Customer.Fax = dataReader.GetString("Fax");
orderCustomer.Customer.Phone = dataReader.GetString("Phone");
orderCustomer.Customer.PostalCode = dataReader.GetString("PostalCode");
orderCustomer.Customer.Region = dataReader.GetString("Region");
orderCustomer.Customer.Country = dataReader.GetString("Country");
orderCustomer.Order.CustomerID = dataReader.GetString("CustomerID");
orderCustomer.Order.OrderDate = dataReader.GetDateTime("OrderDate");
orderCustomer.Order.OrderID = dataReader.GetInt32("OrderID");
orderCustomer.Order.ShipAddress = dataReader.GetString("ShipAddress");
orderCustomer.Order.ShipCity = dataReader.GetString("ShipCity");
orderCustomer.Order.ShipCountry = dataReader.GetString("ShipCountry");
orderCustomer.Order.ShipName = dataReader.GetString("ShipName");
orderCustomer.Order.ShipPostalCode = dataReader.GetString("ShipPostalCode");
orderCustomer.Order.ShipRegion = dataReader.GetString("ShipRegion");
orderCustomer.Order.ShipVia = dataReader.GetInt32("ShipVia");
orderCustomer.Order.RequiredDate = dataReader.GetDateTime("RequiredDate");
orderCustomer.Shipper.CompanyName = dataReader.GetString("ShipperName");
return orderCustomer;
}
/// <summary>
/// Load Order Details and Products
/// </summary>
/// <param name="sqlDataReader"></param>
/// <returns></returns>
private OrderDetailsProducts LoadOrderDetailsProducts(SqlDataReader sqlDataReader)
{
OrderDetailsProducts orderDetailsProduct = new OrderDetailsProducts();
DataReader dataReader = new DataReader(sqlDataReader);
orderDetailsProduct.Products.ProductID = dataReader.GetInt32("ProductID");
orderDetailsProduct.Products.ProductName = dataReader.GetString("ProductName");
orderDetailsProduct.Products.QuantityPerUnit = dataReader.GetString("QuantityPerUnit");
orderDetailsProduct.Products.UnitPrice = dataReader.GetFloat("UnitPrice");
orderDetailsProduct.OrderDetails.ProductID = dataReader.GetInt32("ProductID");
orderDetailsProduct.OrderDetails.Quantity = dataReader.GetInt16("Quantity");
orderDetailsProduct.OrderDetails.UnitPrice = dataReader.GetFloat("UnitPrice");
orderDetailsProduct.OrderDetails.Discount = dataReader.GetFloat("Discount");
orderDetailsProduct.OrderDetails.OrderID = dataReader.GetInt32("OrderID");
orderDetailsProduct.Products.ProductIDFormatted = orderDetailsProduct.Products.ProductID.ToString("D" + 6);
orderDetailsProduct.OrderDetails.ProductIDFormatted = orderDetailsProduct.Products.ProductID.ToString("D" + 6);
orderDetailsProduct.OrderDetails.DiscountFormatted = orderDetailsProduct.OrderDetails.Discount.ToString("F4");
orderDetailsProduct.Products.UnitPriceFormatted = orderDetailsProduct.OrderDetails.UnitPrice.ToString("C");
orderDetailsProduct.OrderDetails.UnitPriceFormatted = orderDetailsProduct.OrderDetails.UnitPrice.ToString("C");
return orderDetailsProduct;
}
/// <summary>
/// Get Order
/// </summary>
/// <param name="orderID"></param>
/// <returns></returns>
public OrdersCustomer GetOrder(int orderID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT O.*, C.*, S.COMPANYNAME AS SHIPPERNAME FROM ORDERS O ");
sqlBuilder.Append(" INNER JOIN CUSTOMERS C ON C.CUSTOMERID = O.CUSTOMERID ");
sqlBuilder.Append(" LEFT JOIN SHIPPERS S ON S.SHIPPERID = O.SHIPVIA ");
sqlBuilder.Append(" WHERE O.ORDERID = @ORDERID ");
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add("@ORDERID", SqlDbType.Int);
sqlCommand.Parameters["@ORDERID"].Value = orderID;
sqlCommand.CommandText = sql;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
OrdersCustomer orderCustomer = new OrdersCustomer();
SqlDataReader sqlDataReader = adoDataService.ExecuteReader(sqlCommand);
sqlDataReader.Read();
orderCustomer = LoadOrderCustomer(sqlDataReader);
sqlDataReader.Close();
adoDataService.Dispose();
return orderCustomer;
}
}
/// <summary>
/// Get Order
/// </summary>
/// <param name="orderID"></param>
/// <returns></returns>
public double GetOrderTotal(int orderID)
{
double orderTotal = 0.00;
string sumValue;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT SUM( (unitprice*quantity)*(1.00-discount) ) as order_total FROM [ORDER DETAILS] ");
sqlBuilder.Append(" WHERE ORDERID = @ORDERID ");
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add("@ORDERID", SqlDbType.Int);
sqlCommand.Parameters["@ORDERID"].Value = orderID;
sqlCommand.CommandText = sql;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
using (SqlDataReader sqlDataReader = adoDataService.ExecuteReader(sqlCommand))
{
if (sqlDataReader.Read()==true) {
sumValue = Convert.ToString(sqlDataReader[0]);
if (sumValue!=String.Empty)
orderTotal = Convert.ToDouble(sqlDataReader[0]);
}
sqlDataReader.Close();
}
adoDataService.Dispose();
return orderTotal;
}
}
/// <summary>
/// Get Order
/// </summary>
/// <param name="orderID"></param>
/// <returns></returns>
public List<OrderDetailsProducts> GetOrderDetails(int orderID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT O.*, OD.*, P.* FROM [ORDER DETAILS] OD ");
sqlBuilder.Append(" INNER JOIN PRODUCTS P ON P.PRODUCTID = OD.PRODUCTID ");
sqlBuilder.Append(" INNER JOIN ORDERS O ON O.ORDERID = OD.ORDERID ");
sqlBuilder.Append(" WHERE OD.ORDERID = @ORDERID ");
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add("@ORDERID", SqlDbType.Int);
sqlCommand.Parameters["@ORDERID"].Value = orderID;
sqlCommand.CommandText = sql;
List<OrderDetailsProducts> orderDetailsProductsList = new List<OrderDetailsProducts>();
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
OrderDetailsProducts orderDetailsProducts = new OrderDetailsProducts();
using (SqlDataReader sqlDataReader = adoDataService.ExecuteReader(sqlCommand))
{
while (sqlDataReader.Read())
{
orderDetailsProducts = LoadOrderDetailsProducts(sqlDataReader);
orderDetailsProductsList.Add(orderDetailsProducts);
}
sqlDataReader.Close();
sqlDataReader.Dispose();
}
adoDataService.Dispose();
return orderDetailsProductsList;
}
}
/// <summary>
/// Get Order
/// </summary>
/// <param name="orderID"></param>
/// <returns></returns>
public OrderDetailsProducts GetOrderDetails(int orderID, int productID)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT O.*, OD.*, P.* FROM [ORDER DETAILS] OD ");
sqlBuilder.Append(" INNER JOIN PRODUCTS P ON P.PRODUCTID = OD.PRODUCTID ");
sqlBuilder.Append(" INNER JOIN ORDERS O ON O.ORDERID = OD.ORDERID ");
sqlBuilder.Append(" WHERE OD.ORDERID = @ORDERID AND OD.PRODUCTID = @PRODUCTID");
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add("@ORDERID", SqlDbType.Int);
sqlCommand.Parameters["@ORDERID"].Value = orderID;
sqlCommand.Parameters.Add("@PRODUCTID", SqlDbType.Int);
sqlCommand.Parameters["@PRODUCTID"].Value = productID;
sqlCommand.CommandText = sql;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
OrderDetailsProducts orderDetailsProducts;
using (SqlDataReader sqlDataReader = adoDataService.ExecuteReader(sqlCommand))
{
sqlDataReader.Read();
orderDetailsProducts = LoadOrderDetailsProducts(sqlDataReader);
sqlDataReader.Close();
sqlDataReader.Dispose();
}
adoDataService.Dispose();
return orderDetailsProducts;
}
}
/// <summary>
/// Get Order
/// </summary>
/// <param name="orderID"></param>
/// <returns></returns>
public Boolean IsDuplicateLineItem(int orderID, int productID)
{
Boolean duplicateStatus = false;
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" SELECT OD.* FROM [ORDER DETAILS] OD ");
sqlBuilder.Append(" WHERE OD.ORDERID = @ORDERID AND OD.PRODUCTID = @PRODUCTID ");
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Parameters.Add("@ORDERID", SqlDbType.Int);
sqlCommand.Parameters["@ORDERID"].Value = orderID;
sqlCommand.Parameters.Add("@PRODUCTID", SqlDbType.Int);
sqlCommand.Parameters["@PRODUCTID"].Value = productID;
sqlCommand.CommandText = sql;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
using (SqlDataReader sqlDataReader = adoDataService.ExecuteReader(sqlCommand))
{
if (sqlDataReader.Read() == true)
duplicateStatus = true;
sqlDataReader.Close();
sqlDataReader.Dispose();
}
adoDataService.Dispose();
}
return duplicateStatus;
}
/// <summary>
/// Get Shippers
/// </summary>
/// <returns></returns>
public List<Shippers> GetShippers()
{
string sql = "SELECT * FROM SHIPPERS ORDER BY CompanyName ";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = sql;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
List<Shippers> shippers = new List<Shippers>();
using (SqlDataReader sqlDataReader = adoDataService.ExecuteReader(sqlCommand))
{
while (sqlDataReader.Read())
{
Shippers shipper = new Shippers();
shipper.CompanyName = sqlDataReader["CompanyName"].ToString();
shipper.ShipperID = Convert.ToInt32(sqlDataReader["ShipperID"]);
shippers.Add(shipper);
}
sqlDataReader.Close();
sqlDataReader.Dispose();
}
adoDataService.Dispose();
return shippers;
}
}
/// <summary>
/// Create Order Detail Line Item
/// </summary>
/// <param name="orderDetails"></param>
public void CreateOrderDetailLineItem(OrderDetails orderDetails)
{
ProductDataAccessService productDataAccessService = new ProductDataAccessService();
Products product = productDataAccessService.GetProductInformation(orderDetails.ProductID);
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" INSERT INTO [ORDER DETAILS] (");
sqlBuilder.Append("OrderID,");
sqlBuilder.Append("ProductID,");
sqlBuilder.Append("Quantity,");
sqlBuilder.Append("UnitPrice,");
sqlBuilder.Append("Discount");
sqlBuilder.Append(") VALUES (");
sqlBuilder.Append("@OrderID,");
sqlBuilder.Append("@ProductID,");
sqlBuilder.Append("@Quantity,");
sqlBuilder.Append("@UnitPrice,");
sqlBuilder.Append("@Discount");
sqlBuilder.Append(")");
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = sql;
sqlCommand.Parameters.Add("@OrderID", SqlDbType.Int);
sqlCommand.Parameters.Add("@ProductID", SqlDbType.Int);
sqlCommand.Parameters.Add("@Quantity", SqlDbType.Int);
sqlCommand.Parameters.Add("@Discount", SqlDbType.Float);
sqlCommand.Parameters.Add("@UnitPrice", SqlDbType.Float);
sqlCommand.Parameters["@OrderID"].Value = orderDetails.OrderID;
sqlCommand.Parameters["@ProductID"].Value = orderDetails.ProductID;
sqlCommand.Parameters["@Quantity"].Value = orderDetails.Quantity;
sqlCommand.Parameters["@UnitPrice"].Value = product.UnitPrice;
Double discount = orderDetails.Discount;
if (discount > 1) discount = discount / 100.00;
sqlCommand.Parameters["@Discount"].Value = discount;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
adoDataService.BeginTransaction();
adoDataService.ExecuteNonQuery(sqlCommand);
adoDataService.CommitTransaction();
adoDataService.Dispose();
}
}
/// <summary>
/// Update Order Detail Line Item
/// </summary>
/// <param name="orderDetails"></param>
public void UpdateOrderDetailLineItem(OrderDetails orderDetails)
{
ProductDataAccessService productDataAccessService = new ProductDataAccessService();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" UPDATE [ORDER DETAILS] SET ");
sqlBuilder.Append(" Quantity=@QUANTITY,");
sqlBuilder.Append(" Discount=@DISCOUNT");
sqlBuilder.Append(" WHERE ORDERID = @ORDERID AND PRODUCTID=@PRODUCTID");
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = sql;
sqlCommand.Parameters.Add("@OrderID", SqlDbType.Int);
sqlCommand.Parameters.Add("@ProductID", SqlDbType.Int);
sqlCommand.Parameters.Add("@Quantity", SqlDbType.Int);
sqlCommand.Parameters.Add("@Discount", SqlDbType.Float);
sqlCommand.Parameters["@OrderID"].Value = orderDetails.OrderID;
sqlCommand.Parameters["@ProductID"].Value = orderDetails.ProductID;
sqlCommand.Parameters["@Quantity"].Value = orderDetails.Quantity;
Double discount = orderDetails.Discount;
if (discount > 1) discount = discount / 100.00;
sqlCommand.Parameters["@Discount"].Value = discount;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
adoDataService.BeginTransaction();
adoDataService.ExecuteNonQuery(sqlCommand);
adoDataService.CommitTransaction();
adoDataService.Dispose();
}
}
/// <summary>
/// Delete Order Detail Line Item
/// </summary>
/// <param name="orderDetails"></param>
public void DeleteOrderDetailLineItem(int orderID, int productID)
{
ProductDataAccessService productDataAccessService = new ProductDataAccessService();
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" DELETE FROM [ORDER DETAILS] ");
sqlBuilder.Append(" WHERE ORDERID = @ORDERID AND PRODUCTID=@PRODUCTID");
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = sql;
sqlCommand.Parameters.Add("@OrderID", SqlDbType.Int);
sqlCommand.Parameters.Add("@ProductID", SqlDbType.Int);
sqlCommand.Parameters["@OrderID"].Value = orderID;
sqlCommand.Parameters["@ProductID"].Value = productID;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
adoDataService.BeginTransaction();
adoDataService.ExecuteNonQuery(sqlCommand);
adoDataService.CommitTransaction();
adoDataService.Dispose();
}
}
/// <summary>
/// Create Order
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public Boolean CreateOrder(Orders order)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" INSERT INTO ORDERS (");
sqlBuilder.Append("CustomerID,");
sqlBuilder.Append("OrderDate,");
sqlBuilder.Append("RequiredDate,");
sqlBuilder.Append("ShipVia,");
sqlBuilder.Append("Freight,");
sqlBuilder.Append("ShipName,");
sqlBuilder.Append("ShipAddress,");
sqlBuilder.Append("ShipCity,");
sqlBuilder.Append("ShipRegion,");
sqlBuilder.Append("ShipPostalCode,");
sqlBuilder.Append("ShipCountry) VALUES (");
sqlBuilder.Append("@CustomerID,");
sqlBuilder.Append("@OrderDate,");
sqlBuilder.Append("@RequiredDate,");
sqlBuilder.Append("@ShipVia,");
sqlBuilder.Append("@Freight,");
sqlBuilder.Append("@ShipName,");
sqlBuilder.Append("@ShipAddress,");
sqlBuilder.Append("@ShipCity,");
sqlBuilder.Append("@ShipRegion,");
sqlBuilder.Append("@ShipPostalCode,");
sqlBuilder.Append("@ShipCountry)");
string sql = sqlBuilder.ToString() + ";SELECT @@IDENTITY as 'Identity'";
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = sql;
sqlCommand.Parameters.Add("@CustomerID", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@OrderDate", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@RequiredDate", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@ShipVia", SqlDbType.Int);
sqlCommand.Parameters.Add("@Freight", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipName", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipAddress", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipCity", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipRegion", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipPostalCode", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipCountry", SqlDbType.VarChar);
sqlCommand.Parameters["@CustomerID"].Value = order.CustomerID;
sqlCommand.Parameters["@OrderDate"].Value = DateTime.Now;
sqlCommand.Parameters["@RequiredDate"].Value = order.RequiredDate;
sqlCommand.Parameters["@ShipVia"].Value = order.ShipVia;
sqlCommand.Parameters["@Freight"].Value = 0.00;
sqlCommand.Parameters["@ShipName"].Value = order.ShipName;
sqlCommand.Parameters["@ShipAddress"].Value = order.ShipAddress;
sqlCommand.Parameters["@ShipCity"].Value = order.ShipCity;
sqlCommand.Parameters["@ShipRegion"].Value = order.ShipRegion;
sqlCommand.Parameters["@ShipPostalCode"].Value = order.ShipPostalCode;
sqlCommand.Parameters["@ShipCountry"].Value = order.ShipCountry;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
adoDataService.BeginTransaction();
Int32 orderID = adoDataService.ExecuteScalar(sqlCommand);
adoDataService.CommitTransaction();
adoDataService.Dispose();
order.OrderID = orderID;
}
return true;
}
/// <summary>
/// Update Order
/// </summary>
/// <param name="order"></param>
/// <returns></returns>
public Boolean UpdateOrder(Orders order)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append(" UPDATE ORDERS SET ");
sqlBuilder.Append(" RequiredDate = @RequiredDate, ");
sqlBuilder.Append(" ShipVia = @ShipVia, ");
sqlBuilder.Append(" ShipName = @ShipName, ");
sqlBuilder.Append(" ShipAddress = @ShipAddress, ");
sqlBuilder.Append(" ShipCity = @ShipCity, ");
sqlBuilder.Append(" ShipRegion = @ShipRegion, ");
sqlBuilder.Append(" ShipPostalCode = @ShipPostalCode, ");
sqlBuilder.Append(" ShipCountry = @ShipCountry ");
sqlBuilder.Append(" WHERE ORDERID = @ORDERID ");
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = sql;
sqlCommand.Parameters.Add("@OrderID", SqlDbType.Int);
sqlCommand.Parameters.Add("@RequiredDate", SqlDbType.DateTime);
sqlCommand.Parameters.Add("@ShipVia", SqlDbType.Int);
sqlCommand.Parameters.Add("@ShipName", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipAddress", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipCity", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipRegion", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipPostalCode", SqlDbType.VarChar);
sqlCommand.Parameters.Add("@ShipCountry", SqlDbType.VarChar);
sqlCommand.Parameters["@OrderID"].Value = order.OrderID;
sqlCommand.Parameters["@RequiredDate"].Value = order.RequiredDate;
sqlCommand.Parameters["@ShipVia"].Value = order.ShipVia;
sqlCommand.Parameters["@ShipName"].Value = order.ShipName;
sqlCommand.Parameters["@ShipAddress"].Value = order.ShipAddress;
sqlCommand.Parameters["@ShipCity"].Value = order.ShipCity;
sqlCommand.Parameters["@ShipRegion"].Value = order.ShipRegion;
sqlCommand.Parameters["@ShipPostalCode"].Value = order.ShipPostalCode;
sqlCommand.Parameters["@ShipCountry"].Value = order.ShipCountry;
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
adoDataService.BeginTransaction();
adoDataService.ExecuteNonQuery(sqlCommand);
adoDataService.CommitTransaction();
adoDataService.Dispose();
}
return true;
}
/// <summary>
/// Order Inquiry
/// </summary>
/// <param name="customerID"></param>
/// <param name="customerName"></param>
/// <param name="currentPageNumber"></param>
/// <param name="pageSize"></param>
/// <param name="totalRecords"></param>
/// <returns></returns>
public List<OrdersCustomer> OrderInquiry(Orders order,
Customer customer,
int currentPageNumber,
string sortExpression,
string sortDirection,
int pageSize, out int totalRecords)
{
int maxRowNumber;
int minRowNumber;
string sqlWhere = String.Empty;
if (sortExpression == "") sortExpression = "CompanyName";
if (sortDirection == "") sortDirection = "ASC";
minRowNumber = (pageSize * (currentPageNumber - 1)) + 1;
maxRowNumber = pageSize * currentPageNumber;
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder sqlWhereBuilder = new StringBuilder();
if (customer.CompanyName != null && customer.CompanyName.Trim().Length > 0)
sqlWhereBuilder.Append(" c.CompanyName LIKE @COMPANYNAME AND ");
if (customer.CustomerID != null && customer.CustomerID.Trim().Length > 0)
sqlWhereBuilder.Append(" c.CustomerID LIKE @CUSTOMERID AND ");
if (order.OrderID > 0 )
sqlWhereBuilder.Append(" o.OrderID = @ORDERID AND ");
if (sortExpression == "CustomerID")
sortExpression = "o.CustomerID";
if (sqlWhereBuilder.Length > 0)
sqlWhere = " WHERE " + sqlWhereBuilder.ToString().Substring(0, sqlWhereBuilder.Length - 4);
sqlBuilder.Append(" SELECT COUNT(*) as total_records FROM Orders o ");
sqlBuilder.Append(" INNER JOIN Customers c ON c.CustomerID = o.CustomerID ");
sqlBuilder.Append(sqlWhere);
sqlBuilder.Append(";");
sqlBuilder.Append(" SELECT * FROM ( ");
sqlBuilder.Append(" SELECT (ROW_NUMBER() OVER (ORDER BY " + sortExpression + " " + sortDirection + ")) as record_number, ");
sqlBuilder.Append(" c.CustomerID as CustomerID, c.CompanyName as CompanyName, o.OrderID as OrderID, o.OrderDate as OrderDate");
sqlBuilder.Append(" FROM Orders o");
sqlBuilder.Append(" INNER JOIN Customers c ON c.CustomerID = o.CustomerID ");
sqlBuilder.Append(sqlWhere);
sqlBuilder.Append(" ) Rows ");
sqlBuilder.Append(" where record_number between " + minRowNumber + " and " + maxRowNumber);
string sql = sqlBuilder.ToString();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandText = sql;
List<OrdersCustomer> listOfOrders = new List<OrdersCustomer>();
if (customer.CustomerID != null && customer.CustomerID.Trim().Length > 0)
{
sqlCommand.Parameters.Add("@CUSTOMERID", SqlDbType.VarChar);
sqlCommand.Parameters["@CUSTOMERID"].Value = customer.CustomerID + "%";
}
if (customer.CompanyName != null && customer.CompanyName.Trim().Length > 0)
{
sqlCommand.Parameters.Add("@COMPANYNAME", SqlDbType.VarChar);
sqlCommand.Parameters["@COMPANYNAME"].Value = customer.CompanyName + "%";
}
if (order.OrderID > 0 )
{
sqlCommand.Parameters.Add("@ORDERID", SqlDbType.Int);
sqlCommand.Parameters["@ORDERID"].Value = order.OrderID;
}
using (ADODataService.DataAccess adoDataService = new ADODataService.DataAccess())
{
SqlDataReader sqlDataReader = adoDataService.ExecuteReader(sqlCommand);
sqlDataReader.Read();
totalRecords = Convert.ToInt32(sqlDataReader["Total_Records"]);
sqlDataReader.NextResult();
while (sqlDataReader.Read())
{
OrdersCustomer orderInformation = LoadOrderCustomer(sqlDataReader);
listOfOrders.Add(orderInformation);
}
sqlDataReader.Close();
adoDataService.Dispose();
return listOfOrders;
}
}
}
}
Submit an article or tip