using System; using System.Text; namespace Northwind45.DataLayer.Base { public sealed class ProductsSql { private ProductsSql() { } public static string SelectByPrimaryKey() { string selectStatement = GetSelectStatement(); StringBuilder sb = new StringBuilder(); sb.Append(selectStatement); sb.Append(" WHERE "); sb.Append("[ProductID] = @productID "); return sb.ToString(); } public static string GetRecordCount() { StringBuilder sb = new StringBuilder(); sb.Append("SELECT COUNT(*) AS RecordCount FROM [dbo].[Products]"); return sb.ToString(); } /// <summary> /// Related to column SupplierID /// </summary> public static string GetRecordCountBySupplierID(int supplierID) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT COUNT(*) AS RecordCount FROM [dbo].[Products] "); sb.Append("WHERE [SupplierID] = @supplierID "); return sb.ToString(); } /// <summary> /// Related to column CategoryID /// </summary> public static string GetRecordCountByCategoryID(int categoryID) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT COUNT(*) AS RecordCount FROM [dbo].[Products] "); sb.Append("WHERE [CategoryID] = @categoryID "); return sb.ToString(); } public static string GetRecordCountDynamicWhere() { StringBuilder sb = new StringBuilder(); sb.Append("SELECT COUNT(*) AS RecordCount FROM [dbo].[Products]"); sb.Append(" WHERE "); sb.Append("([ProductID] = @productID OR @productID IS NULL) AND "); sb.Append("([ProductName] LIKE '%' + @productName + '%' OR @productName IS NULL) AND "); sb.Append("([SupplierID] = @supplierID OR @supplierID IS NULL) AND "); sb.Append("([CategoryID] = @categoryID OR @categoryID IS NULL) AND "); sb.Append("([QuantityPerUnit] LIKE '%' + @quantityPerUnit + '%' OR @quantityPerUnit IS NULL) AND "); sb.Append("([UnitPrice] = @unitPrice OR @unitPrice IS NULL) AND "); sb.Append("([UnitsInStock] = @unitsInStock OR @unitsInStock IS NULL) AND "); sb.Append("([UnitsOnOrder] = @unitsOnOrder OR @unitsOnOrder IS NULL) AND "); sb.Append("([ReorderLevel] = @reorderLevel OR @reorderLevel IS NULL) AND "); sb.Append("([Discontinued] = @discontinued OR @discontinued IS NULL) "); return sb.ToString(); } public static string SelectSkipAndTake() { string selectStatement = GetSelectStatement(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); sb.Append("[ProductID], "); sb.Append("[ProductName], "); sb.Append("[SupplierID], "); sb.Append("[CategoryID], "); sb.Append("[QuantityPerUnit], "); sb.Append("[UnitPrice], "); sb.Append("[UnitsInStock], "); sb.Append("[UnitsOnOrder], "); sb.Append("[ReorderLevel], "); sb.Append("[Discontinued] "); sb.Append("FROM [dbo].[Products] "); sb.Append("ORDER BY "); sb.Append("CASE WHEN @sortByExpression = 'ProductID' THEN [ProductID] END, "); sb.Append("CASE WHEN @sortByExpression = 'ProductID desc' THEN [ProductID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'ProductName' THEN [ProductName] END, "); sb.Append("CASE WHEN @sortByExpression = 'ProductName desc' THEN [ProductName] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'SupplierID' THEN [SupplierID] END, "); sb.Append("CASE WHEN @sortByExpression = 'SupplierID desc' THEN [SupplierID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'CategoryID' THEN [CategoryID] END, "); sb.Append("CASE WHEN @sortByExpression = 'CategoryID desc' THEN [CategoryID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'QuantityPerUnit' THEN [QuantityPerUnit] END, "); sb.Append("CASE WHEN @sortByExpression = 'QuantityPerUnit desc' THEN [QuantityPerUnit] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitPrice' THEN [UnitPrice] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitPrice desc' THEN [UnitPrice] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsInStock' THEN [UnitsInStock] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsInStock desc' THEN [UnitsInStock] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsOnOrder' THEN [UnitsOnOrder] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsOnOrder desc' THEN [UnitsOnOrder] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'ReorderLevel' THEN [ReorderLevel] END, "); sb.Append("CASE WHEN @sortByExpression = 'ReorderLevel desc' THEN [ReorderLevel] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'Discontinued' THEN [Discontinued] END, "); sb.Append("CASE WHEN @sortByExpression = 'Discontinued desc' THEN [Discontinued] END DESC "); sb.Append("OFFSET @start ROWS "); sb.Append("FETCH NEXT @end ROWS ONLY "); return sb.ToString(); } public static string SelectSkipAndTakeBySupplierID(int supplierID) { string selectStatement = GetSelectStatement(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); sb.Append("[ProductID], "); sb.Append("[ProductName], "); sb.Append("[SupplierID], "); sb.Append("[CategoryID], "); sb.Append("[QuantityPerUnit], "); sb.Append("[UnitPrice], "); sb.Append("[UnitsInStock], "); sb.Append("[UnitsOnOrder], "); sb.Append("[ReorderLevel], "); sb.Append("[Discontinued] "); sb.Append("FROM [dbo].[Products] "); sb.Append("WHERE [SupplierID] = @supplierID "); sb.Append("ORDER BY "); sb.Append("CASE WHEN @sortByExpression = 'ProductID' THEN [ProductID] END, "); sb.Append("CASE WHEN @sortByExpression = 'ProductID desc' THEN [ProductID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'ProductName' THEN [ProductName] END, "); sb.Append("CASE WHEN @sortByExpression = 'ProductName desc' THEN [ProductName] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'SupplierID' THEN [SupplierID] END, "); sb.Append("CASE WHEN @sortByExpression = 'SupplierID desc' THEN [SupplierID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'CategoryID' THEN [CategoryID] END, "); sb.Append("CASE WHEN @sortByExpression = 'CategoryID desc' THEN [CategoryID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'QuantityPerUnit' THEN [QuantityPerUnit] END, "); sb.Append("CASE WHEN @sortByExpression = 'QuantityPerUnit desc' THEN [QuantityPerUnit] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitPrice' THEN [UnitPrice] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitPrice desc' THEN [UnitPrice] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsInStock' THEN [UnitsInStock] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsInStock desc' THEN [UnitsInStock] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsOnOrder' THEN [UnitsOnOrder] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsOnOrder desc' THEN [UnitsOnOrder] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'ReorderLevel' THEN [ReorderLevel] END, "); sb.Append("CASE WHEN @sortByExpression = 'ReorderLevel desc' THEN [ReorderLevel] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'Discontinued' THEN [Discontinued] END, "); sb.Append("CASE WHEN @sortByExpression = 'Discontinued desc' THEN [Discontinued] END DESC "); sb.Append("OFFSET @start ROWS "); sb.Append("FETCH NEXT @end ROWS ONLY "); return sb.ToString(); } public static string SelectSkipAndTakeByCategoryID(int categoryID) { string selectStatement = GetSelectStatement(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); sb.Append("[ProductID], "); sb.Append("[ProductName], "); sb.Append("[SupplierID], "); sb.Append("[CategoryID], "); sb.Append("[QuantityPerUnit], "); sb.Append("[UnitPrice], "); sb.Append("[UnitsInStock], "); sb.Append("[UnitsOnOrder], "); sb.Append("[ReorderLevel], "); sb.Append("[Discontinued] "); sb.Append("FROM [dbo].[Products] "); sb.Append("WHERE [CategoryID] = @categoryID "); sb.Append("ORDER BY "); sb.Append("CASE WHEN @sortByExpression = 'ProductID' THEN [ProductID] END, "); sb.Append("CASE WHEN @sortByExpression = 'ProductID desc' THEN [ProductID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'ProductName' THEN [ProductName] END, "); sb.Append("CASE WHEN @sortByExpression = 'ProductName desc' THEN [ProductName] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'SupplierID' THEN [SupplierID] END, "); sb.Append("CASE WHEN @sortByExpression = 'SupplierID desc' THEN [SupplierID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'CategoryID' THEN [CategoryID] END, "); sb.Append("CASE WHEN @sortByExpression = 'CategoryID desc' THEN [CategoryID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'QuantityPerUnit' THEN [QuantityPerUnit] END, "); sb.Append("CASE WHEN @sortByExpression = 'QuantityPerUnit desc' THEN [QuantityPerUnit] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitPrice' THEN [UnitPrice] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitPrice desc' THEN [UnitPrice] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsInStock' THEN [UnitsInStock] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsInStock desc' THEN [UnitsInStock] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsOnOrder' THEN [UnitsOnOrder] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsOnOrder desc' THEN [UnitsOnOrder] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'ReorderLevel' THEN [ReorderLevel] END, "); sb.Append("CASE WHEN @sortByExpression = 'ReorderLevel desc' THEN [ReorderLevel] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'Discontinued' THEN [Discontinued] END, "); sb.Append("CASE WHEN @sortByExpression = 'Discontinued desc' THEN [Discontinued] END DESC "); sb.Append("OFFSET @start ROWS "); sb.Append("FETCH NEXT @end ROWS ONLY "); return sb.ToString(); } public static string SelectSkipAndTakeDynamicWhere() { StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); sb.Append("[ProductID], "); sb.Append("[ProductName], "); sb.Append("[SupplierID], "); sb.Append("[CategoryID], "); sb.Append("[QuantityPerUnit], "); sb.Append("[UnitPrice], "); sb.Append("[UnitsInStock], "); sb.Append("[UnitsOnOrder], "); sb.Append("[ReorderLevel], "); sb.Append("[Discontinued] "); sb.Append("FROM [dbo].[Products] "); sb.Append(" WHERE "); sb.Append("([ProductID] = @productID OR @productID IS NULL) AND "); sb.Append("([ProductName] LIKE '%' + @productName + '%' OR @productName IS NULL) AND "); sb.Append("([SupplierID] = @supplierID OR @supplierID IS NULL) AND "); sb.Append("([CategoryID] = @categoryID OR @categoryID IS NULL) AND "); sb.Append("([QuantityPerUnit] LIKE '%' + @quantityPerUnit + '%' OR @quantityPerUnit IS NULL) AND "); sb.Append("([UnitPrice] = @unitPrice OR @unitPrice IS NULL) AND "); sb.Append("([UnitsInStock] = @unitsInStock OR @unitsInStock IS NULL) AND "); sb.Append("([UnitsOnOrder] = @unitsOnOrder OR @unitsOnOrder IS NULL) AND "); sb.Append("([ReorderLevel] = @reorderLevel OR @reorderLevel IS NULL) AND "); sb.Append("([Discontinued] = @discontinued OR @discontinued IS NULL) "); sb.Append("ORDER BY "); sb.Append("CASE WHEN @sortByExpression = 'ProductID' THEN [ProductID] END, "); sb.Append("CASE WHEN @sortByExpression = 'ProductID desc' THEN [ProductID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'ProductName' THEN [ProductName] END, "); sb.Append("CASE WHEN @sortByExpression = 'ProductName desc' THEN [ProductName] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'SupplierID' THEN [SupplierID] END, "); sb.Append("CASE WHEN @sortByExpression = 'SupplierID desc' THEN [SupplierID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'CategoryID' THEN [CategoryID] END, "); sb.Append("CASE WHEN @sortByExpression = 'CategoryID desc' THEN [CategoryID] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'QuantityPerUnit' THEN [QuantityPerUnit] END, "); sb.Append("CASE WHEN @sortByExpression = 'QuantityPerUnit desc' THEN [QuantityPerUnit] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitPrice' THEN [UnitPrice] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitPrice desc' THEN [UnitPrice] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsInStock' THEN [UnitsInStock] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsInStock desc' THEN [UnitsInStock] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsOnOrder' THEN [UnitsOnOrder] END, "); sb.Append("CASE WHEN @sortByExpression = 'UnitsOnOrder desc' THEN [UnitsOnOrder] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'ReorderLevel' THEN [ReorderLevel] END, "); sb.Append("CASE WHEN @sortByExpression = 'ReorderLevel desc' THEN [ReorderLevel] END DESC, "); sb.Append("CASE WHEN @sortByExpression = 'Discontinued' THEN [Discontinued] END, "); sb.Append("CASE WHEN @sortByExpression = 'Discontinued desc' THEN [Discontinued] END DESC "); sb.Append("OFFSET @start ROWS "); sb.Append("FETCH NEXT @end ROWS ONLY "); return sb.ToString(); } public static string SelectTotals() { string selectStatement = GetSelectStatement(); StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); sb.Append("SUM([UnitPrice]) AS [UnitPriceTotal] "); sb.Append("FROM [dbo].[Products]"); return sb.ToString(); } public static string SelectAll() { string selectStatement = GetSelectStatement(); StringBuilder sb = new StringBuilder(); sb.Append(selectStatement); return sb.ToString(); } /// <summary> /// Related to column SupplierID /// </summary> public static string SelectAllBySupplierID(int supplierID) { string selectStatement = GetSelectStatement(); StringBuilder sb = new StringBuilder(); sb.Append(selectStatement); sb.Append("WHERE [SupplierID] = " + supplierID); return sb.ToString(); } /// <summary> /// Related to column CategoryID /// </summary> public static string SelectAllByCategoryID(int categoryID) { string selectStatement = GetSelectStatement(); StringBuilder sb = new StringBuilder(); sb.Append(selectStatement); sb.Append("WHERE [CategoryID] = " + categoryID); return sb.ToString(); } public static string SelectAllDynamicWhere() { string selectStatement = GetSelectStatement(); StringBuilder sb = new StringBuilder(); sb.Append(selectStatement); sb.Append(" WHERE "); sb.Append("([ProductID] = @productID OR @productID IS NULL) AND "); sb.Append("([ProductName] LIKE '%' + @productName + '%' OR @productName IS NULL) AND "); sb.Append("([SupplierID] = @supplierID OR @supplierID IS NULL) AND "); sb.Append("([CategoryID] = @categoryID OR @categoryID IS NULL) AND "); sb.Append("([QuantityPerUnit] LIKE '%' + @quantityPerUnit + '%' OR @quantityPerUnit IS NULL) AND "); sb.Append("([UnitPrice] = @unitPrice OR @unitPrice IS NULL) AND "); sb.Append("([UnitsInStock] = @unitsInStock OR @unitsInStock IS NULL) AND "); sb.Append("([UnitsOnOrder] = @unitsOnOrder OR @unitsOnOrder IS NULL) AND "); sb.Append("([ReorderLevel] = @reorderLevel OR @reorderLevel IS NULL) AND "); sb.Append("([Discontinued] = @discontinued OR @discontinued IS NULL) "); return sb.ToString(); } /// <summary> /// Selects ProductID and ProductName columns for use with a DropDownList web control /// </summary> public static string SelectProductsDropDownListData() { string selectStatement = "SELECT [ProductID], [ProductName] FROM [dbo].[Products] ORDER BY [ProductName] ASC "; StringBuilder sb = new StringBuilder(); sb.Append(selectStatement); return sb.ToString(); } public static string Insert() { StringBuilder sb = new StringBuilder(); sb.Append("INSERT INTO [dbo].[Products] "); sb.Append("("); sb.Append("[ProductName], "); sb.Append("[SupplierID], "); sb.Append("[CategoryID], "); sb.Append("[QuantityPerUnit], "); sb.Append("[UnitPrice], "); sb.Append("[UnitsInStock], "); sb.Append("[UnitsOnOrder], "); sb.Append("[ReorderLevel], "); sb.Append("[Discontinued] "); sb.Append(") "); sb.Append("OUTPUT inserted.[ProductID] "); sb.Append("VALUES "); sb.Append("("); sb.Append("@productName,"); sb.Append("@supplierID,"); sb.Append("@categoryID,"); sb.Append("@quantityPerUnit,"); sb.Append("@unitPrice,"); sb.Append("@unitsInStock,"); sb.Append("@unitsOnOrder,"); sb.Append("@reorderLevel,"); sb.Append("@discontinued"); sb.Append(")"); return sb.ToString(); } public static string Update() { StringBuilder sb = new StringBuilder(); sb.Append("UPDATE [dbo].[Products] "); sb.Append("SET "); sb.Append("[ProductName] = @productName,"); sb.Append("[SupplierID] = @supplierID,"); sb.Append("[CategoryID] = @categoryID,"); sb.Append("[QuantityPerUnit] = @quantityPerUnit,"); sb.Append("[UnitPrice] = @unitPrice,"); sb.Append("[UnitsInStock] = @unitsInStock,"); sb.Append("[UnitsOnOrder] = @unitsOnOrder,"); sb.Append("[ReorderLevel] = @reorderLevel,"); sb.Append("[Discontinued] = @discontinued"); sb.Append(" WHERE "); sb.Append("[ProductID] = @productID "); return sb.ToString(); } public static string Delete() { StringBuilder sb = new StringBuilder(); sb.Append("DELETE FROM [dbo].[Products] "); sb.Append(" WHERE "); sb.Append("[ProductID] = @productID "); return sb.ToString(); } private static string GetSelectStatement() { StringBuilder sb = new StringBuilder(); sb.Append("SELECT "); sb.Append("[ProductID], "); sb.Append("[ProductName], "); sb.Append("[SupplierID], "); sb.Append("[CategoryID], "); sb.Append("[QuantityPerUnit], "); sb.Append("[UnitPrice], "); sb.Append("[UnitsInStock], "); sb.Append("[UnitsOnOrder], "); sb.Append("[ReorderLevel], "); sb.Append("[Discontinued] "); sb.Append("FROM [dbo].[Products] "); return sb.ToString(); } } }