Imports System Imports NorthwindVB45.BusinessObject Imports System.Web.UI.WebControls ' Imports System.Windows.Forms ' Note: remove comment when using with windows forms ''' <summary> ''' These are data-centric code examples for the Products _table. ''' You can cut and paste the respective codes into your application ''' by changing the sample values assigned from these examples. ''' NOTE: This class contains private methods because they're ''' not meant to be called by an outside client. Each method contains ''' code for the respective example being shown ''' </summary> Public NotInheritable Class ProductsExample Private Sub New() End Sub Private Sub SelectAll() ' select all records Dim objProductsCol As ProductsCollection = Products.SelectAll() ' Example 1: you can optionally sort the collection in ascending order by your chosen field objProductsCol.Sort(Products.ByProductName) ' Example 2: to sort in descending order, add this line to the Sort code in Example 1 objProductsCol.Reverse() ' Example 3: directly bind to a GridView Dim grid As GridView = New GridView() grid.DataSource = objProductsCol grid.DataBind() ' Example 4: loop through all the Products For Each objProducts As Products In objProductsCol Dim productID As Integer = objProducts.ProductID Dim productName As String = objProducts.ProductName Dim supplierID As Integer = objProducts.SupplierID Dim categoryID As Integer = objProducts.CategoryID Dim quantityPerUnit As String = objProducts.QuantityPerUnit Dim unitPrice As Decimal = objProducts.UnitPrice Dim unitsInStock As Short = objProducts.UnitsInStock Dim unitsOnOrder As Short = objProducts.UnitsOnOrder Dim reorderLevel As Short = objProducts.ReorderLevel Dim discontinued As Boolean = objProducts.Discontinued ' optionally get the Suppliers related to SupplierID. ' Note this is lazily loaded which means there's no value until you ask for it If objProducts.SupplierID IsNot Nothing Then Dim objSuppliersRelatedToSupplierID As Suppliers If objProducts.Suppliers.IsValueCreated Then objSuppliersRelatedToSupplierID = objProducts.Suppliers.Value End If End If ' optionally get the Categories related to CategoryID. ' Note this is lazily loaded which means there's no value until you ask for it If objProducts.CategoryID IsNot Nothing Then Dim objCategoriesRelatedToCategoryID As Categories If objProducts.Categories.IsValueCreated Then objCategoriesRelatedToCategoryID = objProducts.Categories.Value End If End If Next End Sub Private Sub SelectByPrimaryKey() ' select a record by primary key(s) Dim objProducts As Products = Products.SelectByPrimaryKey(1) If objProducts IsNot Nothing Then ' if record is found, a record is returned Dim productID As Integer = objProducts.ProductID Dim productName As String = objProducts.ProductName Dim supplierID As Integer = objProducts.SupplierID Dim categoryID As Integer = objProducts.CategoryID Dim quantityPerUnit As String = objProducts.QuantityPerUnit Dim unitPrice As Decimal = objProducts.UnitPrice Dim unitsInStock As Short = objProducts.UnitsInStock Dim unitsOnOrder As Short = objProducts.UnitsOnOrder Dim reorderLevel As Short = objProducts.ReorderLevel Dim discontinued As Boolean = objProducts.Discontinued ' optionally get the Suppliers related to SupplierID. ' Note this is lazily loaded which means there's no value until you ask for it If objProducts.SupplierID IsNot Nothing Then Dim objSuppliersRelatedToSupplierID As Suppliers If objProducts.Suppliers.IsValueCreated Then objSuppliersRelatedToSupplierID = objProducts.Suppliers.Value End If End If ' optionally get the Categories related to CategoryID. ' Note this is lazily loaded which means there's no value until you ask for it If objProducts.CategoryID IsNot Nothing Then Dim objCategoriesRelatedToCategoryID As Categories If objProducts.Categories.IsValueCreated Then objCategoriesRelatedToCategoryID = objProducts.Categories.Value End If End If End If End Sub ''' <summary> ''' Select all records by Suppliers, related to column SupplierID ''' </summary> Private Sub SelectProductsCollectionBySuppliers() Dim objProductsCol As ProductsCollection = Products.SelectProductsCollectionBySuppliers(1) ' Example 1: you can optionally sort the collection in ascending order by your chosen field objProductsCol.Sort(Products.ByProductName) ' Example 2: to sort in descending order, add this line to the Sort code in Example 1 objProductsCol.Reverse() ' Example 3: directly bind to a GridView Dim grid As GridView = New GridView() grid.DataSource = objProductsCol grid.DataBind() ' Example 4: loop through all the Products For Each objProducts As Products In objProductsCol Dim productID As Integer = objProducts.ProductID Dim productName As String = objProducts.ProductName Dim supplierID As Integer = objProducts.SupplierID Dim categoryID As Integer = objProducts.CategoryID Dim quantityPerUnit As String = objProducts.QuantityPerUnit Dim unitPrice As Decimal = objProducts.UnitPrice Dim unitsInStock As Short = objProducts.UnitsInStock Dim unitsOnOrder As Short = objProducts.UnitsOnOrder Dim reorderLevel As Short = objProducts.ReorderLevel Dim discontinued As Boolean = objProducts.Discontinued ' optionally get the Suppliers related to SupplierID. ' Note this is lazily loaded which means there's no value until you ask for it If objProducts.SupplierID IsNot Nothing Then Dim objSuppliersRelatedToSupplierID As Suppliers If objProducts.Suppliers.IsValueCreated Then objSuppliersRelatedToSupplierID = objProducts.Suppliers.Value End If End If ' optionally get the Categories related to CategoryID. ' Note this is lazily loaded which means there's no value until you ask for it If objProducts.CategoryID IsNot Nothing Then Dim objCategoriesRelatedToCategoryID As Categories If objProducts.Categories.IsValueCreated Then objCategoriesRelatedToCategoryID = objProducts.Categories.Value End If End If Next End Sub ''' <summary> ''' Select all records by Categories, related to column CategoryID ''' </summary> Private Sub SelectProductsCollectionByCategories() Dim objProductsCol As ProductsCollection = Products.SelectProductsCollectionByCategories(1) ' Example 1: you can optionally sort the collection in ascending order by your chosen field objProductsCol.Sort(Products.ByProductName) ' Example 2: to sort in descending order, add this line to the Sort code in Example 1 objProductsCol.Reverse() ' Example 3: directly bind to a GridView Dim grid As GridView = New GridView() grid.DataSource = objProductsCol grid.DataBind() ' Example 4: loop through all the Products For Each objProducts As Products In objProductsCol Dim productID As Integer = objProducts.ProductID Dim productName As String = objProducts.ProductName Dim supplierID As Integer = objProducts.SupplierID Dim categoryID As Integer = objProducts.CategoryID Dim quantityPerUnit As String = objProducts.QuantityPerUnit Dim unitPrice As Decimal = objProducts.UnitPrice Dim unitsInStock As Short = objProducts.UnitsInStock Dim unitsOnOrder As Short = objProducts.UnitsOnOrder Dim reorderLevel As Short = objProducts.ReorderLevel Dim discontinued As Boolean = objProducts.Discontinued ' optionally get the Suppliers related to SupplierID. ' Note this is lazily loaded which means there's no value until you ask for it If objProducts.SupplierID IsNot Nothing Then Dim objSuppliersRelatedToSupplierID As Suppliers If objProducts.Suppliers.IsValueCreated Then objSuppliersRelatedToSupplierID = objProducts.Suppliers.Value End If End If ' optionally get the Categories related to CategoryID. ' Note this is lazily loaded which means there's no value until you ask for it If objProducts.CategoryID IsNot Nothing Then Dim objCategoriesRelatedToCategoryID As Categories If objProducts.Categories.IsValueCreated Then objCategoriesRelatedToCategoryID = objProducts.Categories.Value End If End If Next End Sub ''' <summary> ''' Selects ProductID and ProductName columns for use with a with a Drop Down List, Combo Box, Checked Box List, List View, List Box, etc ''' </summary> Private Sub SelectProductsDropDownListData() Dim objProductsCol As ProductsCollection = Products.SelectProductsDropDownListData() ' Example 1: directly bind to a drop down list Dim ddl1 As DropDownList = New DropDownList() ddl1.DataValueField = "ProductID" ddl1.DataTextField = "ProductName" ddl1.DataSource = objProductsCol ddl1.DataBind() ' Example 2: add each item through a loop Dim ddl2 As DropDownList = New DropDownList() For Each objProducts As Products In objProductsCol ddl2.Items.Add(new ListItem(objProducts.ProductName, objProducts.ProductID.ToString())) Next ' Example 3: bind to a combo box. Note: remove comment when using with windows forms ' Dim cbx1 As ComboBox = New ComboBox() ' For Each objProducts As Products In objProductsCol ' cbx1.Items.Add(new ListItem(objProducts.ProductName, objProducts.ProductID.ToString())) ' Next End Sub Private Sub Insert() ' first instantiate a new Products Dim objProducts As New Products() ' assign values you want inserted objProducts.ProductName = "Chai" objProducts.SupplierID = 1 objProducts.CategoryID = 1 objProducts.QuantityPerUnit = "1020" objProducts.UnitPrice = Convert.ToDecimal(18.0000) objProducts.UnitsInStock = 39 objProducts.UnitsOnOrder = 0 objProducts.ReorderLevel = 10 objProducts.Discontinued = false ' finally, insert a new record ' the insert method returns the newly created primary key Dim newlyCreatedPrimaryKey As Integer = objProducts.Insert() End Sub Private Sub Update() ' first instantiate a new Products Dim objProducts As New Products() ' assign the existing primary key(s) ' of the record you want updated objProducts.ProductID = 1 ' assign values you want updated objProducts.ProductName = "Chai" objProducts.SupplierID = 1 objProducts.CategoryID = 1 objProducts.QuantityPerUnit = "1020" objProducts.UnitPrice = Convert.ToDecimal(18.0000) objProducts.UnitsInStock = 39 objProducts.UnitsOnOrder = 0 objProducts.ReorderLevel = 10 objProducts.Discontinued = false ' finally, update an existing record objProducts.Update() End Sub Private Sub Delete() ' delete a record by primary key Products.Delete(4210) End Sub ''' <summary> ''' Shows how to get the total number of records ''' </summary> Private Sub GetRecordCount() ' get the total number of records in the Products table Dim totalRecordCount As Integer = Products.GetRecordCount() End Sub ''' <summary> ''' Shows how to get the total number of records by SupplierID ''' </summary> Private Sub GetRecordCountBySupplierID() ' get the total number of records in the Products table by SupplierID ' 1 here is just a sample SupplierID change the value as you see fit Dim totalRecordCount As Integer = Products.GetRecordCountBySupplierID(1) End Sub ''' <summary> ''' Shows how to get the total number of records by CategoryID ''' </summary> Private Sub GetRecordCountByCategoryID() ' get the total number of records in the Products table by CategoryID ' 1 here is just a sample CategoryID change the value as you see fit Dim totalRecordCount As Integer = Products.GetRecordCountByCategoryID(1) End Sub ''' <summary> ''' Shows how to get a specific number of sorted records, starting from an index. The total number of records are also retrieved when using the SelectSkipAndTake() method. ''' For example, if ther are 200 records (totalRecordCount), take only 10 records (numberOfRecordsToRetrieve), starting from the first index (startRetrievalFromRecordIndex = 0) ''' The example below uses some variables, here are their definitions: ''' totalRecordCount - total number of records if you were to retrieve everything ''' startRetrievalFromRecordIndex - the index to start taking records from. Zero (0) E.g. If you want to skip the first 20 records, then assign 19 here. ''' numberOfRecordsToRetrieve - take n records starting from the startRetrievalFromRecordIndex ''' sortBy - to sort in Ascending order by Field Name, just assign just the Field Name, do not pass 'asc' ''' sortBy - to sort in Descending order by Field Name, use the Field Name, a space and the word 'desc' ''' </summary> Private Sub SelectSkipAndTake() Dim totalRecordCount As Integer Dim startRetrievalFromRecordIndex As Integer = 0 Dim numberOfRecordsToRetrieve As Integer = 10 Dim sortBy As String = "ProductID" ' Dim sortBy As String = "ProductID desc" ' 1. select a specific number of sorted records starting from the index you specify with totalRecordCount Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTake(numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, totalRecordCount, sortBy) ' 2. or you can also select a specific number of sorted records starting from the index you specify Without the totalRecordCount Dim objProductsCol2 As ProductsCollection = Products.SelectSkipAndTake(numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, sortBy) ' to use objProductsCol please see the SelectAll() method examples ' No need for Examples 1 and 2 because the Collection here is already sorted ' Example 3: directly bind to a GridView ' Example 4: loop through all the Products(s). The example above will only loop for 10 items. End Sub ''' <summary> ''' Shows how to get a specific number of sorted records, starting from an index by the related Field Name. The total number of records are also retrieved when using the SelectSkipAndTake() method. ''' For example, if ther are 200 records (totalRecordCount), take only 10 records (numberOfRecordsToRetrieve), starting from the first index (startRetrievalFromRecordIndex = 0) ''' The example below uses some variables, here are their definitions: ''' totalRecordCount - total number of records if you were to retrieve everything ''' startRetrievalFromRecordIndex - the index to start taking records from. Zero (0) E.g. If you want to skip the first 20 records, then assign 19 here. ''' numberOfRecordsToRetrieve - take n records starting from the startRetrievalFromRecordIndex ''' sortBy - to sort in Ascending order by Field Name, just assign just the Field Name, do not pass 'asc' ''' sortBy - to sort in Descending order by Field Name, use the Field Name, a space and the word 'desc' ''' </summary> Private Sub SelectSkipAndTakeBySupplierID() Dim totalRecordCount As Integer Dim startRetrievalFromRecordIndex As Integer = 0 Dim numberOfRecordsToRetrieve As Integer = 10 Dim sortBy As String = "ProductID" ' Dim sortBy As String = "ProductID desc" ' 1. select a specific number of sorted records with a SupplierID = 1 ' starting from the index you specify with totalRecordCount Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTakeBySupplierID(numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, totalRecordCount, sortBy, 1) ' to use objProductsCol please see the SelectAll() method examples ' No need for Examples 1 and 2 because the Collection here is already sorted ' Example 3: directly bind to a GridView ' Example 4: loop through all the Products(s). The example above will only loop for 10 items. End Sub ''' <summary> ''' Shows how to get a specific number of sorted records, starting from an index by the related Field Name. The total number of records are also retrieved when using the SelectSkipAndTake() method. ''' For example, if ther are 200 records (totalRecordCount), take only 10 records (numberOfRecordsToRetrieve), starting from the first index (startRetrievalFromRecordIndex = 0) ''' The example below uses some variables, here are their definitions: ''' totalRecordCount - total number of records if you were to retrieve everything ''' startRetrievalFromRecordIndex - the index to start taking records from. Zero (0) E.g. If you want to skip the first 20 records, then assign 19 here. ''' numberOfRecordsToRetrieve - take n records starting from the startRetrievalFromRecordIndex ''' sortBy - to sort in Ascending order by Field Name, just assign just the Field Name, do not pass 'asc' ''' sortBy - to sort in Descending order by Field Name, use the Field Name, a space and the word 'desc' ''' </summary> Private Sub SelectSkipAndTakeByCategoryID() Dim totalRecordCount As Integer Dim startRetrievalFromRecordIndex As Integer = 0 Dim numberOfRecordsToRetrieve As Integer = 10 Dim sortBy As String = "ProductID" ' Dim sortBy As String = "ProductID desc" ' 1. select a specific number of sorted records with a CategoryID = 1 ' starting from the index you specify with totalRecordCount Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTakeByCategoryID(numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, totalRecordCount, sortBy, 1) ' to use objProductsCol please see the SelectAll() method examples ' No need for Examples 1 and 2 because the Collection here is already sorted ' Example 3: directly bind to a GridView ' Example 4: loop through all the Products(s). The example above will only loop for 10 items. End Sub ''' <summary> ''' Shows how to get fields with Totals ''' </summary> Private Sub SelectTotals() ' get all fields with totals for the Products table Dim objProducts As Products = Products.SelectTotals() ' assign each field with a total Dim unitPriceTotal As Decimal = objProducts.UnitPriceTotal End Sub ''' <summary> ''' Shows how to get a specific number of sorted records, starting from an index, based on Search Parameters. The number of records are also retrieved. ''' </summary> Private Sub SelectSkipAndTakeDynamicWhere() Dim totalRecordCount As Integer Dim startRetrievalFromRecordIndex As Integer = 0 Dim numberOfRecordsToRetrieve As Integer = 10 Dim sortBy As String = "ProductID" ' Dim sortBy As String = "ProductID desc" ' search parameters, verything is nullable, only items being searched for should be filled ' note: fields with String type uses a LIKE search, everything else uses an exact match ' also, every field you're searching for uses the AND operator ' e.g. Dim productID As Integer? = 1, Dim productName As String = "ch" ' will translate to: SELECT....WHERE productID = 1 AND productName LIKE '%ch%' Dim productID As Integer? = Nothing Dim productName As String = Nothing Dim supplierID As Integer? = Nothing Dim categoryID As Integer? = Nothing Dim quantityPerUnit As String = Nothing Dim unitPrice As Decimal? = Nothing Dim unitsInStock As Short? = Nothing Dim unitsOnOrder As Short? = Nothing Dim reorderLevel As Short? = Nothing Dim discontinued As Boolean? = Nothing ' 1. select a specific number of sorted records starting from the index you specify with totalRecordCount based on Search Parameters Dim objProductsCol As ProductsCollection = Products.SelectSkipAndTakeDynamicWhere(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, totalRecordCount, sortBy) ' to use objProductsCol please see the SelectAll() method examples ' No need for Examples 1 and 2 because the Collection here is already sorted ' Example 3: directly bind to a GridView ' Example 4: loop through all the Products(s). The example above will only loop for 10 items. End Sub End Class