Imports System Imports System.Text Namespace Northwind45.DataLayer.Base Public NotInheritable Class ProductsSql Private Sub New() End Sub Public Shared Function SelectByPrimaryKey() As String Dim selectStatement As String = GetSelectStatement() Dim sb As New StringBuilder() sb.Append(selectStatement) sb.Append(" WHERE ") sb.Append("[ProductID] = @productID ") Return sb.ToString() End Function Public Shared Function GetRecordCount() As String Dim sb As New StringBuilder() sb.Append("SELECT COUNT(*) AS RecordCount FROM [dbo].[Products]") Return sb.ToString() End Function ''' <summary> ''' Related to column SupplierID ''' </summary> Public Shared Function GetRecordCountBySupplierID(supplierID As Integer) As String Dim sb As New StringBuilder() sb.Append("SELECT COUNT(*) AS RecordCount FROM [dbo].[Products] ") sb.Append("WHERE [SupplierID] = @supplierID ") Return sb.ToString() End Function ''' <summary> ''' Related to column CategoryID ''' </summary> Public Shared Function GetRecordCountByCategoryID(categoryID As Integer) As String Dim sb As New StringBuilder() sb.Append("SELECT COUNT(*) AS RecordCount FROM [dbo].[Products] ") sb.Append("WHERE [CategoryID] = @categoryID ") Return sb.ToString() End Function Public Shared Function GetRecordCountDynamicWhere() As String Dim sb As 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() End Function Public Shared Function SelectSkipAndTake() As String Dim selectStatement As String = GetSelectStatement() Dim sb As 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() End Function Public Shared Function SelectSkipAndTakeBySupplierID(supplierID As Integer) As String Dim selectStatement As String = GetSelectStatement() Dim sb As 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() End Function Public Shared Function SelectSkipAndTakeByCategoryID(categoryID As Integer) As String Dim selectStatement As String = GetSelectStatement() Dim sb As 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() End Function Public Shared Function SelectSkipAndTakeDynamicWhere() As String Dim sb As 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() End Function Public Shared Function SelectTotals() As String Dim selectStatement As String = GetSelectStatement() Dim sb As New StringBuilder() sb.Append("SELECT ") sb.Append("SUM([UnitPrice]) AS [UnitPriceTotal] ") sb.Append("FROM [dbo].[Products]") Return sb.ToString() End Function Public Shared Function SelectAll() As String Dim selectStatement As String = GetSelectStatement() Dim sb As New StringBuilder() sb.Append(selectStatement) Return sb.ToString() End Function ''' <summary> ''' Related to column SupplierID ''' </summary> Public Shared Function SelectAllBySuppliers(ByVal supplierID As Integer) As String Dim selectStatement As String = GetSelectStatement() Dim sb As New StringBuilder() sb.Append(selectStatement) sb.Append("WHERE [SupplierID] = " & supplierID) Return sb.ToString() End Function ''' <summary> ''' Related to column CategoryID ''' </summary> Public Shared Function SelectAllByCategories(ByVal categoryID As Integer) As String Dim selectStatement As String = GetSelectStatement() Dim sb As New StringBuilder() sb.Append(selectStatement) sb.Append("WHERE [CategoryID] = " & categoryID) Return sb.ToString() End Function ''' <summary> ''' Selects ProductID and ProductName columns for use with a DropDownList web control ''' </summary> Public Shared Function SelectProductsDropDownListData() As String Dim selectStatement As String = "SELECT [ProductID], [ProductName] FROM [dbo].[Products] ORDER BY [ProductName] ASC " Dim sb As New StringBuilder() sb.Append(selectStatement) Return sb.ToString() End Function Public Shared Function Insert() As String Dim sb As 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() End Function Public Shared Function Update() As String Dim sb As 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() End Function Public Shared Function Delete() As String Dim sb As New StringBuilder() sb.Append("DELETE FROM [dbo].[Products] ") sb.Append(" WHERE ") sb.Append("[ProductID] = @productID ") Return sb.ToString() End Function Public Shared Function GetSelectStatement() As String Dim sb As 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() End Function End Class End Namespace