using System; using System.Collections.Generic; using System.Linq; using ACG6MvcAdHcApi; using ACG6MvcAdHcApi.BusinessLayer; using ACG6MvcAdHcApi.Models; // using 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 is sealed and 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. /// These examples shows how to call the Business Layer (or the Middler Layer or Middler Tier) from a client. /// A client can be a Windows Form, WPF app, Silverlight app, ASP.NET Web Form/MVC/Razor app, a Web API, a Class, and many more. /// </summary> public sealed class ProductExample { // inject the business layer objects in the constructor private readonly IProductBusinessLayer _productBusinessLayer; private readonly ISupplierBusinessLayer _supplierBusinessLayer; private readonly ICategoryBusinessLayer _categoryBusinessLayer; public ProductExample(IProductBusinessLayer productBusinessLayer, ISupplierBusinessLayer supplierBusinessLayer, ICategoryBusinessLayer categoryBusinessLayer) { _productBusinessLayer = productBusinessLayer; _supplierBusinessLayer = supplierBusinessLayer; _categoryBusinessLayer = categoryBusinessLayer; } /// <summary> /// Shows how to Select all records. It also shows how to sort, bind, and loop through records. /// </summary> private async void SelectAllAsync() { // select all records List<Product> objProductsList = await _productBusinessLayer.SelectAllAsync(); // Example 1: you can optionally sort the collection in ascending order by your chosen field objProductsList.Sort(ProductBusinessLayer.ByProductName); // Example 2: to sort in descending order, add this line to the Sort code in Example 1 objProductsList.Reverse(); // Example 3: directly bind to a GridView - for ASP.NET Web Forms // GridView grid = new GridView(); // grid.DataSource = objProductsList; // grid.DataBind(); // Example 4: loop through all the Products foreach (Product objProduct in objProductsList) { int productID = objProduct.ProductID; string productName = objProduct.ProductName; int? supplierID = objProduct.SupplierID; int? categoryID = objProduct.CategoryID; string quantityPerUnit = objProduct.QuantityPerUnit; decimal? unitPrice = objProduct.UnitPrice; Int16? unitsInStock = objProduct.UnitsInStock; Int16? unitsOnOrder = objProduct.UnitsOnOrder; Int16? reorderLevel = objProduct.ReorderLevel; bool discontinued = objProduct.Discontinued; // get the Suppliers related to SupplierID. if (objProduct.SupplierID != null) { Supplier objSupplierRelatedToSupplierID = await _supplierBusinessLayer.SelectByPrimaryKeyAsync(supplierID.Value); } // get the Categories related to CategoryID. if (objProduct.CategoryID != null) { Category objCategoryRelatedToCategoryID = await _categoryBusinessLayer.SelectByPrimaryKeyAsync(categoryID.Value); } } } /// <summary> /// Shows how to Select all records sorted by column name in either ascending or descending order. /// </summary> private async void SelectAllWithSortExpression() { // select all records sorted by ProductID in ascending order string sortBy = "ProductID"; // ascending order //string sortBy = "ProductID desc"; // descending order List<Product> objProductsList = await _productBusinessLayer.SelectAllAsync(sortBy); } /// <summary> /// Shows how to Select a record by Primary Key. It also shows how to retrieve Lazily-loaded related Objects. Related Objects are assigned for each Foreign Key. /// </summary> private async void SelectByPrimaryKeyAsync() { int productIDSample = 1; // select a record by primary key(s) Product objProduct = await _productBusinessLayer.SelectByPrimaryKeyAsync(productIDSample); if (objProduct != null) { // if record is found, a record is returned int productID = objProduct.ProductID; string productName = objProduct.ProductName; int? supplierID = objProduct.SupplierID; int? categoryID = objProduct.CategoryID; string quantityPerUnit = objProduct.QuantityPerUnit; decimal? unitPrice = objProduct.UnitPrice; Int16? unitsInStock = objProduct.UnitsInStock; Int16? unitsOnOrder = objProduct.UnitsOnOrder; Int16? reorderLevel = objProduct.ReorderLevel; bool discontinued = objProduct.Discontinued; // get the Suppliers related to SupplierID. if (objProduct.SupplierID != null) { Supplier objSupplierRelatedToSupplierID = await _supplierBusinessLayer.SelectByPrimaryKeyAsync(supplierID.Value); } // get the Categories related to CategoryID. if (objProduct.CategoryID != null) { Category objCategoryRelatedToCategoryID = await _categoryBusinessLayer.SelectByPrimaryKeyAsync(categoryID.Value); } } } /// <summary> /// The example below shows how to Select the 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 async void SelectProductDropDownListDataAsync() { List<Product> objProductsList = await _productBusinessLayer.SelectProductDropDownListDataAsync(); // Example 1: directly bind to a drop down list - for ASP.NET Web Forms // DropDownList ddl1 = new DropDownList(); // ddl1.DataValueField = "ProductID"; // ddl1.DataTextField = "ProductName"; // ddl1.DataSource = objProductsList; // ddl1.DataBind(); // Example 2: add each item through a loop - for ASP.NET Web Forms // DropDownList ddl2 = new DropDownList(); // foreach (Product objProduct in objProductsList) // { // ddl2.Items.Add(new ListItem(objProduct.ProductName, objProduct.ProductID.ToString())); // } // Example 3: bind to a combo box. for Windows Forms (WinForms) // ComboBox cbx1 = new ComboBox(); // foreach (Product objProduct in objProductsList) // { // cbx1.Items.Add(new ListItem(objProduct.ProductName, objProduct.ProductID.ToString())); // } } /// <summary> /// Shows how to Insert or Create a New Record /// </summary> private async void Insert() { // first instantiate a new Product Product objProduct = new(); // assign values you want inserted objProduct.ProductName = "Chai"; objProduct.SupplierID = 1; objProduct.CategoryID = 1; objProduct.QuantityPerUnit = "10 boxes x 20 bags"; objProduct.UnitPrice = Convert.ToDecimal("18.0000"); objProduct.UnitsInStock = 39; objProduct.UnitsOnOrder = 0; objProduct.ReorderLevel = 12; objProduct.Discontinued = false; // finally, insert a new record // the insert method returns the newly created primary key int newlyCreatedPrimaryKey = await _productBusinessLayer.InsertAsync(objProduct); } /// <summary> /// Shows how to Update an existing record by Primary Key /// </summary> private async void UpdateAsync() { // first instantiate a new Product Product objProduct = new(); // assign the existing primary key(s) // of the record you want updated objProduct.ProductID = 1; // assign values you want updated objProduct.ProductName = "Chai"; objProduct.SupplierID = 1; objProduct.CategoryID = 1; objProduct.QuantityPerUnit = "10 boxes x 20 bags"; objProduct.UnitPrice = Convert.ToDecimal("18.0000"); objProduct.UnitsInStock = 39; objProduct.UnitsOnOrder = 0; objProduct.ReorderLevel = 12; objProduct.Discontinued = false; // finally, update an existing record await _productBusinessLayer.UpdateAsync(objProduct); } /// <summary> /// Shows how to Delete an existing record by Primary Key /// </summary> private async void DeleteAsync() { // delete a record by primary key // using business layer; await _productBusinessLayer.DeleteAsync(2325); } /// <summary> /// Shows how to Delete Multiple records by Primary Key /// </summary> private async void DeleteMultpleAsync() { // sample data only, in the real world you should use different values string ids = "2325, 2325"; // split ids into a List List<Int32> productIDList = ids.Split(",").Select(Int32.Parse).ToList(); // using business layer, delete multiple records based on a list of ids (primary keys) await _productBusinessLayer.DeleteMultipleAsync(productIDList); } /// <summary> /// Shows how to get the total number of records /// </summary> private async void GetRecordCountAsync() { // get the total number of records in the Product table int totalRecordCount; // using the business layer totalRecordCount = await _productBusinessLayer.GetRecordCountAsync(); } /// <summary> /// Shows how to get the total number of records by SupplierID /// </summary> private async void GetRecordCountBySupplierIDAsync() { // get the total number of records in the Product table by SupplierID // 1 here is just a sample SupplierID change the value as you see fit int totalRecordCount; // using business layer totalRecordCount = await _productBusinessLayer.GetRecordCountBySupplierIDAsync(1); } /// <summary> /// Shows how to get the total number of records by CategoryID /// </summary> private async void GetRecordCountByCategoryIDAsync() { // get the total number of records in the Product table by CategoryID // 1 here is just a sample CategoryID change the value as you see fit int totalRecordCount; // using business layer totalRecordCount = await _productBusinessLayer.GetRecordCountByCategoryIDAsync(1); } /// <summary> /// Shows how to get the total number of records based on Search Parameters. /// </summary> private async void GetRecordCountDynamicWhereAsync() { // search parameters, everything 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. int? productID = 1; string productName = "ch"; // will translate to: SELECT....WHERE productID = 1 AND productName LIKE '%ch%' int? productID = null; string productName = null; int? supplierID = null; int? categoryID = null; string quantityPerUnit = null; decimal? unitPrice = null; Int16? unitsInStock = null; Int16? unitsOnOrder = null; Int16? reorderLevel = null; bool? discontinued = null; int totalRecordCount = 0; // using business layer totalRecordCount = await _productBusinessLayer.GetRecordCountDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); } /// <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 there are 200 records 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 async void SelectSkipAndTakeAsync() { int numberOfRecordsToRetrieve = 10; //string sortByDesc = "ProductID desc"; // 1. select a specific number of sorted records starting from the index you specify List<Product> objProductsList; // using business layer int startRetrievalFromRecordIndex = 0; string sortByAsc = "ProductID"; objProductsList = await _productBusinessLayer.SelectSkipAndTakeAsync(numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, sortByAsc); // to use objProductsList please see the SelectAll() method examples // No need for Examples 1 and 2 because the Collection here is already sorted // Example 2: directly bind to a GridView - for ASP.NET Web Forms // Example 3: loop through all the Product(s). The example above will only loop for 10 items. } /// <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 there are 200 records, 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 async void SelectSkipAndTakeBySupplierIDAsync() { int numberOfRecordsToRetrieve = 10; int startRetrievalFromRecordIndex = 0; string sortBy = "ProductID"; //string sortBy = "ProductID desc"; // 1. select a specific number of sorted records with a SupplierID = 1 // starting from the index you specify List<Product> objProductsList; // using business layer objProductsList = await _productBusinessLayer.SelectSkipAndTakeBySupplierIDAsync(numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, sortBy, 1); // to use objProductsList 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 - for ASP.NET Web Forms // Example 4: loop through all the Products. The example above will only loop for 10 items. } /// <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 there are 200 records, 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 async void SelectSkipAndTakeByCategoryIDAsync() { int numberOfRecordsToRetrieve = 10; int startRetrievalFromRecordIndex = 0; string sortBy = "ProductID"; //string sortBy = "ProductID desc"; // 1. select a specific number of sorted records with a CategoryID = 1 // starting from the index you specify List<Product> objProductsList; // using business layer objProductsList = await _productBusinessLayer.SelectSkipAndTakeByCategoryIDAsync(numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, sortBy, 1); // to use objProductsList 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 - for ASP.NET Web Forms // Example 4: loop through all the Products. The example above will only loop for 10 items. } /// <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 async void SelectSkipAndTakeDynamicWhereAsync() { int startRetrievalFromRecordIndex = 0; int numberOfRecordsToRetrieve = 10; string sortBy = "ProductID"; //string sortBy = "ProductID desc"; List<Product> objProductsList; // search parameters, everything 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. int? productID = 1; string productName = "ch"; // will translate to: SELECT....WHERE productID = 1 AND productName LIKE '%ch%' int? productID = null; string productName = null; int? supplierID = null; int? categoryID = null; string quantityPerUnit = null; decimal? unitPrice = null; Int16? unitsInStock = null; Int16? unitsOnOrder = null; Int16? reorderLevel = null; bool? discontinued = null; // using business layer // 1. select a specific number of sorted records starting from the index you specify based on Search Parameters objProductsList = await _productBusinessLayer.SelectSkipAndTakeDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued, numberOfRecordsToRetrieve, startRetrievalFromRecordIndex, sortBy); // to use objProductsList 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 - for ASP.NET Web Forms // Example 4: loop through all the Products. The example above will only loop for 10 items. } /// <summary> /// Shows how to get all records based on Search Parameters. /// </summary> private async void SelectAllDynamicWhereAsync() { List<Product> objProductsList; // search parameters, everything 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. int? productID = 1; string productName = "ch"; // will translate to: SELECT....WHERE productID = 1 AND productName LIKE '%ch%' int? productID = null; string productName = null; int? supplierID = null; int? categoryID = null; string quantityPerUnit = null; decimal? unitPrice = null; Int16? unitsInStock = null; Int16? unitsOnOrder = null; Int16? reorderLevel = null; bool? discontinued = null; // using business layer objProductsList = await _productBusinessLayer.SelectAllDynamicWhereAsync(productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); } }