using System;
using System.Data;
using System.Data.SqlClient;
using Northwind45.BusinessObject;
 
namespace Northwind45.DataLayer.Base
{
     /// <summary>
     /// Base class for ProductsDataLayer.  Do not make changes to this class,
     /// instead, put additional code in the ProductsDataLayer class
     /// </summary>
     public class ProductsDataLayerBase
     {
         // constructor
         public ProductsDataLayerBase()
         {
         }
 
         /// <summary>
         /// Selects a record by primary key(s)
         /// </summary>
         public static Products SelectByPrimaryKey(int productID)
         {
              string storedProcName = "[dbo].[aspx_Products_SelectByPrimaryKey]";
              SqlConnection connection = Dbase.GetConnection();
              SqlCommand command = Dbase.GetCommand(storedProcName, connection);
 
              // parameters
              command.Parameters.AddWithValue("@productID", productID);
 
              DataSet ds = Dbase.GetDbaseDataSet(command);
              Products objProducts = null;
 
              if (ds.Tables[0].Rows.Count > 0)
                  objProducts = CreateProductsFromDataRowShared(ds.Tables[0].Rows[0]);
 
              command.Dispose();
              connection.Close();
              connection.Dispose();
              ds.Dispose();
 
              return objProducts;
         }
 
         /// <summary>
         /// Gets the total number of records in the Products table
         /// </summary>
         public static int GetRecordCount()
         {
             return GetRecordCountShared("[dbo].[aspx_Products_GetRecordCount]", null, null, true, null);
         }
 
         /// <summary>
         /// Gets the total number of records in the Products table by SupplierID
         /// </summary>
         public static int GetRecordCountBySupplierID(int supplierID)
         {
             return GetRecordCountShared("[dbo].[aspx_Products_GetRecordCountBySupplierID]", "supplierID", supplierID, true, null);
         }
 
         /// <summary>
         /// Gets the total number of records in the Products table by CategoryID
         /// </summary>
         public static int GetRecordCountByCategoryID(int categoryID)
         {
             return GetRecordCountShared("[dbo].[aspx_Products_GetRecordCountByCategoryID]", "categoryID", categoryID, true, null);
         }
 
         public static int GetRecordCountShared(string storedProcName = null, string param = null, object paramValue = null, bool isUseStoredProc = true, string dynamicSQL = null)
         {
              SqlConnection connection = Dbase.GetConnection();
              SqlCommand command;
 
              if (isUseStoredProc)
                  command = Dbase.GetCommand(storedProcName, connection);
              else
                  command = new SqlCommand(dynamicSQL, connection);
 
              // parameters
              switch (param)
              {
                  case "supplierID":
                      command.Parameters.AddWithValue("@supplierID", paramValue);
                      break;
                  case "categoryID":
                      command.Parameters.AddWithValue("@categoryID", paramValue);
                      break;
                  default:
                      break;
              }
 
              DataSet ds = Dbase.GetDbaseDataSet(command);
              int recordCount = 0;
 
              if (ds.Tables[0].Rows.Count > 0)
                  recordCount = (int)ds.Tables[0].Rows[0]["RecordCount"];
 
              command.Dispose();
              connection.Close();
              connection.Dispose();
              ds.Dispose();
 
              return recordCount;
         }
 
         /// <summary>
         /// Gets the total number of records in the Products table based on search parameters
         /// </summary>
         public static int GetRecordCountDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued)
         {
              string storedProcName = "[dbo].[aspx_Products_GetRecordCountWhereDynamic]";
              SqlConnection connection = Dbase.GetConnection();
              SqlCommand command = Dbase.GetCommand(storedProcName, connection);
 
              // search parameters
              AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued);
 
              DataSet ds = Dbase.GetDbaseDataSet(command);
              int recordCount = 0;
 
              if (ds.Tables[0].Rows.Count > 0)
                  recordCount = (int)ds.Tables[0].Rows[0]["RecordCount"];
 
              command.Dispose();
              connection.Close();
              connection.Dispose();
              ds.Dispose();
 
              return recordCount;
         }
 
         /// <summary>
         /// Selects Products records sorted by the sortByExpression and returns records between the start and end
         /// </summary>
         public static ProductsCollection SelectSkipAndTake(string sortByExpression, int start, int end)
         {
             return SelectShared("[dbo].[aspx_Products_SelectSkipAndTake]", null, null, true, null, sortByExpression, start, end);
         }
 
         /// <summary>
         /// Selects records by SupplierID as a collection (List) of Products sorted by the sortByExpression and returns the maximumRows (# of records) starting from the startRowIndex
         /// </summary>
         public static ProductsCollection SelectSkipAndTakeBySupplierID(string sortByExpression, int start, int end, int supplierID)
         {
             return SelectShared("[dbo].[aspx_Products_SelectSkipAndTakeBySupplierID]", "supplierID", supplierID, true, null, sortByExpression, start, end);
         }
 
         /// <summary>
         /// Selects records by CategoryID as a collection (List) of Products sorted by the sortByExpression and returns the maximumRows (# of records) starting from the startRowIndex
         /// </summary>
         public static ProductsCollection SelectSkipAndTakeByCategoryID(string sortByExpression, int start, int end, int categoryID)
         {
             return SelectShared("[dbo].[aspx_Products_SelectSkipAndTakeByCategoryID]", "categoryID", categoryID, true, null, sortByExpression, start, end);
         }
 
         public static ProductsCollection SelectSkipAndTakeDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued, string sortByExpression, int start, int end)
         {
              string storedProcName = "[dbo].[aspx_Products_SelectSkipAndTakeWhereDynamic]";
              SqlConnection connection = Dbase.GetConnection();
              SqlCommand command = Dbase.GetCommand(storedProcName, connection);
 
              // select, skip, take, sort parameters
              command.Parameters.AddWithValue("@start", start);
              command.Parameters.AddWithValue("@end", end);
              command.Parameters.AddWithValue("@sortByExpression", sortByExpression);
 
              // search parameters
              AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued);
 
              DataSet ds = Dbase.GetDbaseDataSet(command);
              ProductsCollection objProductsCol = new ProductsCollection();
 
              if (ds.Tables[0].Rows.Count > 0)
              {
                  foreach (DataRow dr in ds.Tables[0].Rows)
                  {
                      Products objProducts = CreateProductsFromDataRowShared(dr);
                      objProductsCol.Add(objProducts);
                  }
              }
 
              command.Dispose();
              connection.Close();
              connection.Dispose();
              ds.Dispose();
 
              return objProductsCol;
         }
 
         /// <summary>
         /// Gets the grand total or sum of fields with a money of decimal data type
         /// </summary>
         public static Products SelectTotals()
         {
              string storedProcName = "[dbo].[aspx_Products_SelectTotals]";
              SqlConnection connection = Dbase.GetConnection();
              SqlCommand command = Dbase.GetCommand(storedProcName, connection);
 
              DataSet ds = Dbase.GetDbaseDataSet(command);
              Products objProducts = null;
 
              if (ds.Tables[0].Rows.Count > 0)
              {
                  objProducts = new Products();
 
                 if(ds.Tables[0].Rows[0]["UnitPriceTotal"] != DBNull.Value)
                      objProducts.UnitPriceTotal = (decimal)ds.Tables[0].Rows[0]["UnitPriceTotal"];
              }
 
              command.Dispose();
              connection.Close();
              connection.Dispose();
              ds.Dispose();
 
              return objProducts;
         }
 
         /// <summary>
         /// Selects all Products
         /// </summary>
         public static ProductsCollection SelectAll()
         {
             return SelectShared("[dbo].[aspx_Products_SelectAll]", String.Empty, null);
         }
 
         /// <summary>
         /// Selects records based on the passed filters as a collection (List) of Products.
         /// </summary>
         public static ProductsCollection SelectAllDynamicWhere(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued)
         {
              string storedProcName = "[dbo].[aspx_Products_SelectAllWhereDynamic]";
              SqlConnection connection = Dbase.GetConnection();
              SqlCommand command = Dbase.GetCommand(storedProcName, connection);
 
              // search parameters
              AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued);
 
              DataSet ds = Dbase.GetDbaseDataSet(command);
              ProductsCollection objProductsCol = new ProductsCollection();
 
              if (ds.Tables[0].Rows.Count > 0)
              {
                 foreach(DataRow dr in ds.Tables[0].Rows)
                 {
                     Products objProducts = CreateProductsFromDataRowShared(dr);
                     objProductsCol.Add(objProducts);
                 }
              }
 
              command.Dispose();
              connection.Close();
              connection.Dispose();
              ds.Dispose();
 
              return objProductsCol;
         }
 
         /// <summary>
         /// Selects all Products by Suppliers, related to column SupplierID
         /// </summary>
         public static ProductsCollection SelectProductsCollectionBySupplierID(int supplierID)
         {
             return SelectShared("[dbo].[aspx_Products_SelectAllBySupplierID]", "supplierID", supplierID);
         }
 
         /// <summary>
         /// Selects all Products by Categories, related to column CategoryID
         /// </summary>
         public static ProductsCollection SelectProductsCollectionByCategoryID(int categoryID)
         {
             return SelectShared("[dbo].[aspx_Products_SelectAllByCategoryID]", "categoryID", categoryID);
         }
 
         /// <summary>
         /// Selects ProductID and ProductName columns for use with a DropDownList web control
         /// </summary>
         public static ProductsCollection SelectProductsDropDownListData()
         {
              string storedProcName = "[dbo].[aspx_Products_SelectDropDownListData]";
              SqlConnection connection = Dbase.GetConnection();
              SqlCommand command = Dbase.GetCommand(storedProcName, connection);
 
              DataSet ds = Dbase.GetDbaseDataSet(command);
              ProductsCollection objProductsCol = new ProductsCollection();
              Products objProducts;
 
              if (ds.Tables[0].Rows.Count > 0)
              {
                 foreach(DataRow dr in ds.Tables[0].Rows)
                 {
                     objProducts = new Products();
                     objProducts.ProductID = (int)dr["ProductID"];
                     objProducts.ProductName = (string)(dr["ProductName"]);
 
                     objProductsCol.Add(objProducts);
                 }
              }
 
              command.Dispose();
              connection.Close();
              connection.Dispose();
              ds.Dispose();
 
              return objProductsCol;
         }
 
         public static ProductsCollection SelectShared(string storedProcName, string param, object paramValue, bool isUseStoredProc = true, string dynamicSQL = null, string sortByExpression = null, int? start = null, int? end = null)
         {
              SqlConnection connection = Dbase.GetConnection();
              SqlCommand command;
 
              if (isUseStoredProc)
                  command = Dbase.GetCommand(storedProcName, connection);
              else
                  command = new SqlCommand(dynamicSQL, connection);
 
              // select, skip, take, sort parameters
              if (!String.IsNullOrEmpty(sortByExpression) && start != null && end != null)
              {
                  command.Parameters.AddWithValue("@start", start.Value);
                  command.Parameters.AddWithValue("@end", end.Value);
                  command.Parameters.AddWithValue("@sortByExpression", sortByExpression);
              }
 
              // parameters
              switch (param)
              {
                  case "supplierID":
                      command.Parameters.AddWithValue("@supplierID", paramValue);
                      break;
                  case "categoryID":
                      command.Parameters.AddWithValue("@categoryID", paramValue);
                      break;
                  default:
                      break;
              }
 
              DataSet ds = Dbase.GetDbaseDataSet(command);
              ProductsCollection objProductsCol = new ProductsCollection();
 
              if (ds.Tables[0].Rows.Count > 0)
              {
                 foreach(DataRow dr in ds.Tables[0].Rows)
                 {
                     Products objProducts = CreateProductsFromDataRowShared(dr);
                     objProductsCol.Add(objProducts);
                 }
              }
 
              command.Dispose();
              connection.Close();
              connection.Dispose();
              ds.Dispose();
 
              return objProductsCol;
         }
 
         /// <summary>
         /// Inserts a record
         /// </summary>
         public static int Insert(Products objProducts)
         {
             string storedProcName = "[dbo].[aspx_Products_Insert]";
             return InsertUpdate(objProducts, false, storedProcName);
         }
 
         /// <summary>
         /// Updates a record
         /// </summary>
         public static void Update(Products objProducts)
         {
             string storedProcName = "[dbo].[aspx_Products_Update]";
             InsertUpdate(objProducts, true, storedProcName);
         }
 
         private static int InsertUpdate(Products objProducts, bool isUpdate, string storedProcName)
         {
             SqlConnection connection = Dbase.GetConnection();
             SqlCommand command = Dbase.GetCommand(storedProcName, connection);
 
             object supplierID = objProducts.SupplierID;
             object categoryID = objProducts.CategoryID;
             object quantityPerUnit = objProducts.QuantityPerUnit;
             object unitPrice = objProducts.UnitPrice;
             object unitsInStock = objProducts.UnitsInStock;
             object unitsOnOrder = objProducts.UnitsOnOrder;
             object reorderLevel = objProducts.ReorderLevel;
 
             if (objProducts.SupplierID == null)
                 supplierID = System.DBNull.Value;
 
             if (objProducts.CategoryID == null)
                 categoryID = System.DBNull.Value;
 
             if (String.IsNullOrEmpty(objProducts.QuantityPerUnit))
                 quantityPerUnit = System.DBNull.Value;
 
             if (objProducts.UnitPrice == null)
                 unitPrice = System.DBNull.Value;
 
             if (objProducts.UnitsInStock == null)
                 unitsInStock = System.DBNull.Value;
 
             if (objProducts.UnitsOnOrder == null)
                 unitsOnOrder = System.DBNull.Value;
 
             if (objProducts.ReorderLevel == null)
                 reorderLevel = System.DBNull.Value;
 
             // for update only
             if (isUpdate)
             {
                 command.Parameters.AddWithValue("@productID", objProducts.ProductID);
             }
 
             command.Parameters.AddWithValue("@productName", objProducts.ProductName);
             command.Parameters.AddWithValue("@supplierID", supplierID);
             command.Parameters.AddWithValue("@categoryID", categoryID);
             command.Parameters.AddWithValue("@quantityPerUnit", quantityPerUnit);
             command.Parameters.AddWithValue("@unitPrice", unitPrice);
             command.Parameters.AddWithValue("@unitsInStock", unitsInStock);
             command.Parameters.AddWithValue("@unitsOnOrder", unitsOnOrder);
             command.Parameters.AddWithValue("@reorderLevel", reorderLevel);
             command.Parameters.AddWithValue("@discontinued", objProducts.Discontinued);
 
             // execute and return value
             int newlyCreatedProductID = objProducts.ProductID;
 
             if (isUpdate)
                 command.ExecuteNonQuery();
             else
                 newlyCreatedProductID = (int)command.ExecuteScalar();
 
             command.Dispose();
             connection.Close();
             connection.Dispose();
 
             return newlyCreatedProductID;
         }
 
         /// <summary>
         /// Deletes a record based on primary key(s)
         /// </summary>
         public static void Delete(int productID)
         {
             SqlConnection connection = Dbase.GetConnection();
             SqlCommand command = Dbase.GetCommand("[dbo].[aspx_Products_Delete]", connection);
 
             command.Parameters.AddWithValue("@productID", productID);
 
             // execute stored proc
             command.ExecuteNonQuery();
             command.Dispose();
             connection.Close();
             connection.Dispose();
         }
 
         /// <summary>
         /// Adds search parameters to the Command object
         /// </summary>
         private static void AddSearchCommandParamsShared(SqlCommand command, int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued)
         {
              if(productID != null)
                  command.Parameters.AddWithValue("@productID", productID);
              else
                  command.Parameters.AddWithValue("@productID", System.DBNull.Value);
 
              if(!String.IsNullOrEmpty(productName))
                  command.Parameters.AddWithValue("@productName", productName);
              else
                  command.Parameters.AddWithValue("@productName", System.DBNull.Value);
 
              if(supplierID != null)
                  command.Parameters.AddWithValue("@supplierID", supplierID);
              else
                  command.Parameters.AddWithValue("@supplierID", System.DBNull.Value);
 
              if(categoryID != null)
                  command.Parameters.AddWithValue("@categoryID", categoryID);
              else
                  command.Parameters.AddWithValue("@categoryID", System.DBNull.Value);
 
              if(!String.IsNullOrEmpty(quantityPerUnit))
                  command.Parameters.AddWithValue("@quantityPerUnit", quantityPerUnit);
              else
                  command.Parameters.AddWithValue("@quantityPerUnit", System.DBNull.Value);
 
              if(unitPrice != null)
                  command.Parameters.AddWithValue("@unitPrice", unitPrice);
              else
                  command.Parameters.AddWithValue("@unitPrice", System.DBNull.Value);
 
              if(unitsInStock != null)
                  command.Parameters.AddWithValue("@unitsInStock", unitsInStock);
              else
                  command.Parameters.AddWithValue("@unitsInStock", System.DBNull.Value);
 
              if(unitsOnOrder != null)
                  command.Parameters.AddWithValue("@unitsOnOrder", unitsOnOrder);
              else
                  command.Parameters.AddWithValue("@unitsOnOrder", System.DBNull.Value);
 
              if(reorderLevel != null)
                  command.Parameters.AddWithValue("@reorderLevel", reorderLevel);
              else
                  command.Parameters.AddWithValue("@reorderLevel", System.DBNull.Value);
 
              if(discontinued != null)
                  command.Parameters.AddWithValue("@discontinued", discontinued);
              else
                  command.Parameters.AddWithValue("@discontinued", System.DBNull.Value);
 
         }
 
         /// <summary>
         /// Creates a Products object from the passed data row
         /// </summary>
         private static Products CreateProductsFromDataRowShared(DataRow dr)
         {
             Products objProducts = new Products();
 
             objProducts.ProductID = (int)dr["ProductID"];
             objProducts.ProductName = dr["ProductName"].ToString();
 
             if (dr["SupplierID"] != System.DBNull.Value)
                 objProducts.SupplierID = (int)dr["SupplierID"];
             else
                 objProducts.SupplierID = null;
 
 
             if (dr["CategoryID"] != System.DBNull.Value)
                 objProducts.CategoryID = (int)dr["CategoryID"];
             else
                 objProducts.CategoryID = null;
 
 
             if (dr["QuantityPerUnit"] != System.DBNull.Value)
                 objProducts.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
             else
                 objProducts.QuantityPerUnit = null;
 
             if (dr["UnitPrice"] != System.DBNull.Value)
                 objProducts.UnitPrice = (decimal)dr["UnitPrice"];
             else
                 objProducts.UnitPrice = null;
 
             if (dr["UnitsInStock"] != System.DBNull.Value)
                 objProducts.UnitsInStock = (Int16)dr["UnitsInStock"];
             else
                 objProducts.UnitsInStock = null;
 
             if (dr["UnitsOnOrder"] != System.DBNull.Value)
                 objProducts.UnitsOnOrder = (Int16)dr["UnitsOnOrder"];
             else
                 objProducts.UnitsOnOrder = null;
 
             if (dr["ReorderLevel"] != System.DBNull.Value)
                 objProducts.ReorderLevel = (Int16)dr["ReorderLevel"];
             else
                 objProducts.ReorderLevel = null;
             objProducts.Discontinued = (bool)dr["Discontinued"];
 
             return objProducts;
         }
     }
}