using ACG6MvcAdHcApi.DataRepository.Helper; using ACG6MvcAdHcApi.Models; using System; using System.Data; using System.Data.SqlClient; using System.Collections.Generic; using System.Threading.Tasks; using ACG6MvcAdHcApi.BusinessLayer; namespace ACG6MvcAdHcApi.DataRepository { /// <summary> /// Works like the Base class for ProductRepository class. /// ************************************* Do not make changes to this class ************************************* /// ** Put your additional code in the ProductRepository class under the DataRepository folder. ** /// ************************************************************************************************************* /// </summary> public partial class ProductRepository : IProductRepository { private const CommandType _commandType = CommandType.Text; private readonly string _connectionString; internal ProductRepository(string connectionString) { _connectionString = connectionString; } /// <summary> /// Selects a record by primary key(s) /// </summary> async Task<DataTable> IProductRepository.SelectByPrimaryKeyAsync(int productID) { string sql = ProductSql.SelectByPrimaryKey(); List<SqlParameter> sqlParamList = new(); // add parameters to the sqlParams DatabaseFunctions.AddSqlParameter(sqlParamList, "@productID", productID); // get and return the data return await DatabaseFunctions.GetDataTableAsync(_connectionString, sql, sqlParamList, _commandType); } /// <summary> /// Gets the total number of records in the Products table /// </summary> async Task<int> IProductRepository.GetRecordCountAsync() { string sql = ProductSql.GetRecordCount(); return await this.GetRecordCountSharedAsync(sql, null, null); } /// <summary> /// Gets the total number of records in the Products table by SupplierID /// </summary> async Task<int> IProductRepository.GetRecordCountBySupplierIDAsync(int? supplierID) { string sql = ProductSql.GetRecordCountBySupplierID(); return await this.GetRecordCountSharedAsync(sql, "supplierID", supplierID); } /// <summary> /// Gets the total number of records in the Products table by CategoryID /// </summary> async Task<int> IProductRepository.GetRecordCountByCategoryIDAsync(int? categoryID) { string sql = ProductSql.GetRecordCountByCategoryID(); return await this.GetRecordCountSharedAsync(sql, "categoryID", categoryID); } /// <summary> /// Gets the total number of records in the Products table based on search parameters /// </summary> async Task<int> IProductRepository.GetRecordCountDynamicWhereAsync(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued) { List<SqlParameter> sqlParamList = new(); string sql = ProductSql.GetRecordCountDynamicWhere(); int recordCount = 0; // search parameters this.AddSearchCommandParamsShared(sqlParamList, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); // get the data DataTable dt = await DatabaseFunctions.GetDataTableAsync(_connectionString, sql, sqlParamList, _commandType); // convert data to an int - record count if (dt is not null && dt.Rows.Count > 0) recordCount = (int)dt.Rows[0]["RecordCount"]; return recordCount; } /// <summary> /// Selects Products table records sorted by the sortByExpression and returns records from the startRowIndex with rows (# of rows) /// </summary> async Task<DataTable> IProductRepository.SelectSkipAndTakeAsync(string sortByExpression, int startRowIndex, int rows) { string sql = ProductSql.SelectSkipAndTake(); return await this.SelectSharedAsync(sql, null, null, sortByExpression, startRowIndex, rows); } /// <summary> /// Selects records by SupplierID as a collection (List) of Product sorted by the sortByExpression. /// </summary> async Task<DataTable> IProductRepository.SelectSkipAndTakeBySupplierIDAsync(string sortByExpression, int startRowIndex, int rows, int? supplierID) { string sql = ProductSql.SelectSkipAndTakeBySupplierID(); return await this.SelectSharedAsync(sql, "supplierID", supplierID, sortByExpression, startRowIndex, rows); } /// <summary> /// Selects records by CategoryID as a collection (List) of Product sorted by the sortByExpression. /// </summary> async Task<DataTable> IProductRepository.SelectSkipAndTakeByCategoryIDAsync(string sortByExpression, int startRowIndex, int rows, int? categoryID) { string sql = ProductSql.SelectSkipAndTakeByCategoryID(); return await this.SelectSharedAsync(sql, "categoryID", categoryID, sortByExpression, startRowIndex, rows); } /// <summary> /// Selects Products table records sorted by the sortByExpression and returns records from the startRowIndex with rows (# of records) based on search parameters /// </summary> async Task<DataTable> IProductRepository.SelectSkipAndTakeDynamicWhereAsync(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued, string sortByExpression, int startRowIndex, int rows) { List<SqlParameter> sqlParamList = new(); string sql = ProductSql.SelectSkipAndTakeDynamicWhere(); // select, skip, take, sort parameters DatabaseFunctions.AddSelectSkipAndTakeParams(sqlParamList, sortByExpression, startRowIndex, rows); // search parameters this.AddSearchCommandParamsShared(sqlParamList, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); // get and return the data return await DatabaseFunctions.GetDataTableAsync(_connectionString, sql, sqlParamList, _commandType); } /// <summary> /// Selects all Products /// </summary> async Task<DataTable> IProductRepository.SelectAllAsync() { string sql = ProductSql.SelectAll(); return await this.SelectSharedAsync(sql, String.Empty, null, null, null, null); } /// <summary> /// Selects records based on the passed filters as a collection (List) of Product. /// </summary> async Task<DataTable> IProductRepository.SelectAllDynamicWhereAsync(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued) { string sql = ProductSql.SelectAllDynamicWhere(); List<SqlParameter> sqlParamList = new(); // search parameters this.AddSearchCommandParamsShared(sqlParamList, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued); // get and return the data return await DatabaseFunctions.GetDataTableAsync(_connectionString, sql, sqlParamList, _commandType); } /// <summary> /// Selects all Products by Suppliers, related to column SupplierID /// </summary> async Task<DataTable> IProductRepository.SelectProductCollectionBySupplierIDAsync(int supplierID) { string sql = ProductSql.SelectAllBySupplierID(); return await this.SelectSharedAsync(sql, "supplierID", supplierID, null, null, null); } /// <summary> /// Selects all Products by Categories, related to column CategoryID /// </summary> async Task<DataTable> IProductRepository.SelectProductCollectionByCategoryIDAsync(int categoryID) { string sql = ProductSql.SelectAllByCategoryID(); return await this.SelectSharedAsync(sql, "categoryID", categoryID, null, null, null); } /// <summary> /// Selects ProductID and ProductName columns for use with a DropDownList web control /// </summary> async Task<DataTable> IProductRepository.SelectProductDropDownListDataAsync() { List<SqlParameter> sqlParamList = new(); string sql = ProductSql.SelectProductDropDownListData(); // get and return the data return await DatabaseFunctions.GetDataTableAsync(_connectionString, sql, sqlParamList, _commandType); } /// <summary> /// Deletes a record based on primary key(s) /// </summary> async Task IProductRepository.DeleteAsync(int productID) { List<SqlParameter> sqlParamList = new(); string sql = ProductSql.Delete(); DatabaseFunctions.AddSqlParameter(sqlParamList, "@productID", productID); // delete record await DatabaseFunctions.ExecuteSqlCommandAsync(_connectionString, sql, sqlParamList, _commandType, DatabaseOperationType.Delete); } /// <summary> /// Inserts a record /// </summary> async Task<int> IProductRepository.InsertAsync(Product objProduct) { return await this.InsertUpdateAsync(objProduct, DatabaseOperationType.Create); } /// <summary> /// Updates a record /// </summary> async Task IProductRepository.UpdateAsync(Product objProduct) => await this.InsertUpdateAsync(objProduct, DatabaseOperationType.Update); /// <summary> /// Creates a new Product or Updates an existing Product base on the Operation Type. /// </summary> /// <param name="objProduct">The data to Create or Update</param> /// <param name="operationType">Accepts Create or Update only</param> /// <returns>Newly Created ProductID for create, or the ProductID for the record to be updated</returns> private async Task<int> InsertUpdateAsync(Product objProduct, DatabaseOperationType operationType) { if (operationType == DatabaseOperationType.RetrieveDataTable || operationType == DatabaseOperationType.Delete) throw new ArgumentException("Invalid DatabaseOperationType! Acceptable operation types are: Create or Update only.", "operationType: " + operationType.ToString()); List<SqlParameter> sqlParamList = new(); // set values for the Product to be created or updated int newlyCreatedProductID = objProduct.ProductID; // get the SQL script for the create or update operation string sql; if (operationType == DatabaseOperationType.Update) sql = ProductSql.Update(); else sql = ProductSql.Insert(); object supplierID = objProduct.SupplierID; object categoryID = objProduct.CategoryID; object quantityPerUnit = objProduct.QuantityPerUnit; object unitPrice = objProduct.UnitPrice; object unitsInStock = objProduct.UnitsInStock; object unitsOnOrder = objProduct.UnitsOnOrder; object reorderLevel = objProduct.ReorderLevel; if (objProduct.SupplierID is null) supplierID = System.DBNull.Value; if (objProduct.CategoryID is null) categoryID = System.DBNull.Value; if (String.IsNullOrEmpty(objProduct.QuantityPerUnit)) quantityPerUnit = System.DBNull.Value; if (objProduct.UnitPrice is null) unitPrice = System.DBNull.Value; if (objProduct.UnitsInStock is null) unitsInStock = System.DBNull.Value; if (objProduct.UnitsOnOrder is null) unitsOnOrder = System.DBNull.Value; if (objProduct.ReorderLevel is null) reorderLevel = System.DBNull.Value; // update parameters if (operationType == DatabaseOperationType.Update) { DatabaseFunctions.AddSqlParameter(sqlParamList, "@productID", objProduct.ProductID); } DatabaseFunctions.AddSqlParameter(sqlParamList, "@productName", objProduct.ProductName); DatabaseFunctions.AddSqlParameter(sqlParamList, "@supplierID", supplierID); DatabaseFunctions.AddSqlParameter(sqlParamList, "@categoryID", categoryID); DatabaseFunctions.AddSqlParameter(sqlParamList, "@quantityPerUnit", quantityPerUnit); DatabaseFunctions.AddSqlParameter(sqlParamList, "@unitPrice", unitPrice); DatabaseFunctions.AddSqlParameter(sqlParamList, "@unitsInStock", unitsInStock); DatabaseFunctions.AddSqlParameter(sqlParamList, "@unitsOnOrder", unitsOnOrder); DatabaseFunctions.AddSqlParameter(sqlParamList, "@reorderLevel", reorderLevel); DatabaseFunctions.AddSqlParameter(sqlParamList, "@discontinued", objProduct.Discontinued); // create a new record or update an existing record if (operationType == DatabaseOperationType.Update) await DatabaseFunctions.ExecuteSqlCommandAsync(_connectionString, sql, sqlParamList, _commandType, DatabaseOperationType.Update); else newlyCreatedProductID = (int)await DatabaseFunctions.ExecuteSqlCommandAsync(_connectionString, sql, sqlParamList, _commandType, DatabaseOperationType.Create, false); return newlyCreatedProductID; } /// <summary> /// Gets the Number of Records from the Products table based on the SQL script. /// </summary> /// <param name="sql">SQL Script</param> /// <param name="parameterName">Parameter Name used to create the SqlParameter</param> /// <param name="parameterValue">Parameter Value used to create the SqlParamater</param> /// <returns>Record Count - int</returns> private async Task<int> GetRecordCountSharedAsync(string sql, string parameterName, object parameterValue) { List<SqlParameter> sqlParamList = new(); // set SqlParameter when parameter name is one of the foreign keys if (parameterName is not null) this.SetForeignKeySqlParameter(sqlParamList, parameterName, parameterValue); return await DatabaseFunctions.GetRecordCountAsync(_connectionString, sql, sqlParamList, _commandType); } /// <summary> /// Selects records from the database. Used by other methods that selects data from the database. /// </summary> /// <param name="sql">SQL Script</param> /// <param name="parameterName">Parameter Name used to create the SqlParameter</param> /// <param name="parameterValue">Parameter Value used to create the SqlParamater</param>> /// <param name="sortByExpression">Sort by Column Name. E.g. "ProductID" sorts by ProductID in ascending order. "ProductID desc" sorts by ProductID in descending order.</param> /// <param name="startRowIndex">Optional. Index of the row to start taking data from. E.g. 0 will start taking data from the 1st row, 9 will start taking data from the 10th row.</param> /// <param name="rows">Optional. Number of rows to take.</param> /// <returns>List of Products</returns> private async Task<DataTable> SelectSharedAsync(string sql, string parameterName, object parameterValue, string sortByExpression, int? startRowIndex, int? rows) { List<SqlParameter> sqlParamList = new(); // select, skip, take, sort sql parameters if (!String.IsNullOrEmpty(sortByExpression) && startRowIndex is not null && rows is not null) DatabaseFunctions.AddSelectSkipAndTakeParams(sqlParamList, sortByExpression, startRowIndex.Value, rows.Value); // set SqlParameter when parameter name is one of the foreign keys if (parameterName is not null) this.SetForeignKeySqlParameter(sqlParamList, parameterName, parameterValue); // get and return the data return await DatabaseFunctions.GetDataTableAsync(_connectionString, sql, sqlParamList, _commandType); } /// <summary> /// Adds search parameters to the List of SqlParameter /// </summary> private void AddSearchCommandParamsShared(List<SqlParameter> sqlParamList, int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued) { if(productID is not null) DatabaseFunctions.AddSqlParameter(sqlParamList, "@productID", productID); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@productID", System.DBNull.Value); if(!String.IsNullOrEmpty(productName)) DatabaseFunctions.AddSqlParameter(sqlParamList, "@productName", productName); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@productName", System.DBNull.Value); if(supplierID is not null) DatabaseFunctions.AddSqlParameter(sqlParamList, "@supplierID", supplierID); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@supplierID", System.DBNull.Value); if(categoryID is not null) DatabaseFunctions.AddSqlParameter(sqlParamList, "@categoryID", categoryID); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@categoryID", System.DBNull.Value); if(!String.IsNullOrEmpty(quantityPerUnit)) DatabaseFunctions.AddSqlParameter(sqlParamList, "@quantityPerUnit", quantityPerUnit); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@quantityPerUnit", System.DBNull.Value); if(unitPrice is not null) DatabaseFunctions.AddSqlParameter(sqlParamList, "@unitPrice", unitPrice); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@unitPrice", System.DBNull.Value); if(unitsInStock is not null) DatabaseFunctions.AddSqlParameter(sqlParamList, "@unitsInStock", unitsInStock); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@unitsInStock", System.DBNull.Value); if(unitsOnOrder is not null) DatabaseFunctions.AddSqlParameter(sqlParamList, "@unitsOnOrder", unitsOnOrder); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@unitsOnOrder", System.DBNull.Value); if(reorderLevel is not null) DatabaseFunctions.AddSqlParameter(sqlParamList, "@reorderLevel", reorderLevel); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@reorderLevel", System.DBNull.Value); if(discontinued is not null) DatabaseFunctions.AddSqlParameter(sqlParamList, "@discontinued", discontinued); else DatabaseFunctions.AddSqlParameter(sqlParamList, "@discontinued", System.DBNull.Value); } /// <summary> /// Set the SqlParameter for the specific foreign key. /// </summary> /// <param name="sqlParamList">List of SqlParameters</param> /// <param name="parameterName">Parameter Name used to create the SqlParameter</param> /// <param name="parameterValue">Parameter Value used to create the SqlParamater</param> private void SetForeignKeySqlParameter(List<SqlParameter> sqlParamList, string parameterName, object parameterValue) { // add the (foreign key) sql parameters if (!String.IsNullOrEmpty(parameterName)) { if (parameterValue is null) parameterValue = DBNull.Value; switch (parameterName) { case "supplierID": DatabaseFunctions.AddSqlParameter(sqlParamList, "@supplierID", parameterValue); break; case "categoryID": DatabaseFunctions.AddSqlParameter(sqlParamList, "@categoryID", parameterValue); break; default: break; } } } } }