Click here to Skip to main content
Click here to Skip to main content
Articles » Web Development » ASP.NET » Howto » Downloads
 
Add your own
alternative version

MVC Techniques with jQuery, JSON, Knockout, and C#

, 2 Jan 2012
Developing an Order Entry application with MVC.
Prize winner in Competition "Best Web Dev article of December 2011"
CodeProjectMVC4.zip
ADODataService
ADODataService.csproj.user
bin
Debug
ADODataService.dll
Release
obj
Debug
TempPE
Release
Properties
NorthwindApplicationServices
bin
Debug
ADODataService.dll
NorthwindApplicationServices.dll
NorthwindBusinessServices.dll
NorthwindDataAccessServices.dll
NorthwindDataModel.dll
NorthwindViewModel.dll
Release
NorthwindApplicationServices.csproj.user
obj
Debug
TempPE
Release
Properties
NorthwindBusinessServices
bin
Debug
ADODataService.dll
NorthwindBusinessServices.dll
NorthwindDataAccessServices.dll
NorthwindDataModel.dll
NorthwindViewModel.dll
Release
NorthwindBusinessServices.csproj.user
obj
Debug
TempPE
Release
Properties
NorthwindDataAccessServices
bin
Debug
ADODataService.dll
NorthwindDataAccessServices.dll
NorthwindDataModel.dll
Release
NorthwindDataAccessServices.csproj.user
obj
Debug
TempPE
Release
Properties
NorthwindDataModel
bin
Debug
NorthwindDataModel.dll
Release
NorthwindDataModel.csproj.user
obj
Debug
TempPE
Release
Properties
NorthwindViewModel
bin
Debug
NorthwindDataModel.dll
NorthwindViewModel.dll
Release
NorthwindViewModel.csproj.user
obj
Debug
TempPE
Release
Properties
NorthwindWebApplication
App_Data
bin
ADODataService.dll
EntityFramework.dll
Microsoft.Web.Infrastructure.dll
Microsoft.Web.Optimization.dll
NorthwindApplicationServices.dll
NorthwindBusinessServices.dll
NorthwindDataAccessServices.dll
NorthwindDataModel.dll
NorthwindViewModel.dll
NorthwindWebApplication.dll
NorthwindWebControls.dll
System.Web.Helpers.dll
System.Web.Mvc.dll
System.Web.Providers.dll
System.Web.Razor.dll
System.Web.WebPages.Deployment.dll
System.Web.WebPages.dll
System.Web.WebPages.Razor.dll
Content
Images
DownArrow.gif
icon-calendar.gif
icon-delete.gif
icon-floppy.gif
icon-pencil-x.gif
icon-pencil.gif
Search3.gif
UpArrow.gif
themes
base
images
ui-bg_flat_0_aaaaaa_40x100.png
ui-bg_flat_75_ffffff_40x100.png
ui-bg_glass_55_fbf9ee_1x400.png
ui-bg_glass_65_ffffff_1x400.png
ui-bg_glass_75_dadada_1x400.png
ui-bg_glass_75_e6e6e6_1x400.png
ui-bg_glass_95_fef1ec_1x400.png
ui-bg_highlight-soft_75_cccccc_1x100.png
ui-icons_222222_256x240.png
ui-icons_2e83ff_256x240.png
ui-icons_454545_256x240.png
ui-icons_888888_256x240.png
ui-icons_cd0a0a_256x240.png
Controllers
Global.asax
Helpers
Images
accent.png
aspNetHome.png
bullet.png
facebook.png
findHosting.png
heroAccent.png
NuGetGallery.png
orderedListOne.png
orderedListThree.png
orderedListTwo.png
Search.gif
twitter.png
windowsLive.png
Models
NorthwindWebApplication.csproj.user
obj
Debug
TempPE
Properties
Scripts
Views
Account
Home
Orders
Products
Shared
NorthwindWebControls
bin
Debug
NorthwindWebControls.dll
Release
obj
Debug
TempPE
Properties
packages
AspNetMvc.4.0.10906.0
AspNetMvc.4.0.10906.0.nupkg
lib
net40
System.Web.Mvc.dll
AspNetWebPagesCore.2.0.10906.0
AspNetWebPagesCore.2.0.10906.0.nupkg
lib
net40
System.Web.Helpers.dll
System.Web.Razor.dll
System.Web.WebPages.Deployment.dll
System.Web.WebPages.dll
System.Web.WebPages.Razor.dll
EntityFramework.4.1.10331.0
EntityFramework.4.1.10331.0.nupkg
lib
net40
EntityFramework.dll
jQuery.1.6.2
Content
Scripts
jQuery.1.6.2.nupkg
jQuery.Ajax.Unobtrusive.1.0
Content
Scripts
jQuery.Ajax.Unobtrusive.1.0.nupkg
jQuery.UI.Combined.1.8.11
Content
Content
themes
base
images
ui-bg_flat_0_aaaaaa_40x100.png
ui-bg_flat_75_ffffff_40x100.png
ui-bg_glass_55_fbf9ee_1x400.png
ui-bg_glass_65_ffffff_1x400.png
ui-bg_glass_75_dadada_1x400.png
ui-bg_glass_75_e6e6e6_1x400.png
ui-bg_glass_95_fef1ec_1x400.png
ui-bg_highlight-soft_75_cccccc_1x100.png
ui-icons_222222_256x240.png
ui-icons_2e83ff_256x240.png
ui-icons_454545_256x240.png
ui-icons_888888_256x240.png
ui-icons_cd0a0a_256x240.png
Scripts
jQuery.UI.Combined.1.8.11.nupkg
jQuery.Validation.1.8
Content
Scripts
jQuery.Validation.1.8.nupkg
jQuery.Validation.Unobtrusive.1.0
Content
Scripts
jQuery.Validation.Unobtrusive.1.0.nupkg
knockoutjs.1.2.9.0
Content
Scripts
knockoutjs.1.2.9.0.nupkg
Microsoft.Web.Optimization.0.1
lib
net40
Microsoft.Web.Optimization.dll
Microsoft.Web.Optimization.0.1.nupkg
MicrosoftWebInfrastructure.1.0.0.0
lib
net40
Microsoft.Web.Infrastructure.dll
MicrosoftWebInfrastructure.1.0.0.0.nupkg
Modernizr.2.0.6
Content
Scripts
Modernizr.2.0.6.nupkg
System.Web.Providers.1.0.1
content
web.config.transform
EULA.rtf
lib
Net40
System.Web.Providers.dll
System.Web.Providers.1.0.1.nupkg
SQLScript
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;

            }

        }

    }

}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Mark J. Caplin
Software Developer Caplin Systems
United States United States
Mark Caplin has specialized in Information Technology solutions for 25 years. Specializing in full life-cycle development projects for both enterprise-wide systems and Internet/Intranet based solutions.
 
For the past ten years or so, Mark has specialized in the Microsoft .NET framework using both C# and VB.NET as his tools of choice.
 
When not coding, Mark enjoys playing tennis, listening to U2 music, watching Miami Dolphins football and watching movies in Blu-Ray technology.
 
In between all this, his wife of over 20 years, feeds him well with some great home cooked meals.
 
...

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 2 Jan 2012
Article Copyright 2011 by Mark J. Caplin
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid