//END - read //BEGIN - sql view public List <Vwtbl_exceptionloggingtodatabase> GetAllVwtbl_exceptionloggingtodatabases() { try // handle exogenous exceptions { try // log all exceptions { List <Vwtbl_exceptionloggingtodatabase> Vwtbl_Exceptionloggingtodatabases = new List <Vwtbl_exceptionloggingtodatabase>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("SELECT * FROM Vwtbl_Exceptionloggingtodatabase", con); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Vwtbl_exceptionloggingtodatabase vwtbl_exceptionloggingtodatabase = new Vwtbl_exceptionloggingtodatabase { // EXAMPLES: //EmployeeId = Convert.ToInt32(rdr["EmployeeId"]), //Name = rdr["Name"].ToString(), //IsPermanent = (bool)rdr["IsPermanent"], //Salary = Convert.ToDecimal(rdr["Salary"]), //DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]) //Logid = (long)rdr["Logid"] Logid = rdr["Logid"] == DBNull.Value ? default(long) : (long)rdr["Logid"] //,ExceptionMsg = (string)rdr["ExceptionMsg"] , ExceptionMsg = rdr["ExceptionMsg"] == DBNull.Value ? "" : (string)rdr["ExceptionMsg"] //,ExceptionType = (string)rdr["ExceptionType"] , ExceptionType = rdr["ExceptionType"] == DBNull.Value ? "" : (string)rdr["ExceptionType"] //,ExceptionSource = (string)rdr["ExceptionSource"] , ExceptionSource = rdr["ExceptionSource"] == DBNull.Value ? "" : (string)rdr["ExceptionSource"] //,ExceptionURL = (string)rdr["ExceptionURL"] , ExceptionURL = rdr["ExceptionURL"] == DBNull.Value ? "" : (string)rdr["ExceptionURL"] //,Logdate = (System.DateTime?)rdr["Logdate"] , Logdate = rdr["Logdate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["Logdate"] }; Vwtbl_Exceptionloggingtodatabases.Add(vwtbl_exceptionloggingtodatabase); } con.Close(); cmd.Dispose(); } return(Vwtbl_Exceptionloggingtodatabases); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - readBy //BEGIN - create public void AddTbl_Exceptionloggingtodatabase(Tbl_ExceptionloggingtodatabaseSingle tbl_exceptionloggingtodatabase) { try // handle exogenous exceptions { try // log all exceptions { BusinessLayer.Validation isdte = new BusinessLayer.Validation(); if (isdte.IsDate(tbl_exceptionloggingtodatabase.Logdate) == false) { tbl_exceptionloggingtodatabase.Logdate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue; } using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spAddTbl_Exceptionloggingtodatabase", con) { CommandType = CommandType.StoredProcedure }; SqlParameter paramLogid = new SqlParameter { ParameterName = "@Logid", Value = tbl_exceptionloggingtodatabase.Logid }; cmd.Parameters.Add(paramLogid); SqlParameter paramExceptionMsg = new SqlParameter { ParameterName = "@ExceptionMsg", Value = tbl_exceptionloggingtodatabase.ExceptionMsg }; cmd.Parameters.Add(paramExceptionMsg); SqlParameter paramExceptionType = new SqlParameter { ParameterName = "@ExceptionType", Value = tbl_exceptionloggingtodatabase.ExceptionType }; cmd.Parameters.Add(paramExceptionType); SqlParameter paramExceptionSource = new SqlParameter { ParameterName = "@ExceptionSource", Value = tbl_exceptionloggingtodatabase.ExceptionSource }; cmd.Parameters.Add(paramExceptionSource); SqlParameter paramExceptionURL = new SqlParameter { ParameterName = "@ExceptionURL", Value = tbl_exceptionloggingtodatabase.ExceptionURL }; cmd.Parameters.Add(paramExceptionURL); SqlParameter paramLogdate = new SqlParameter { ParameterName = "@Logdate", Value = tbl_exceptionloggingtodatabase.Logdate }; cmd.Parameters.Add(paramLogdate); con.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); } } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
public ActionResult Edit(int EmployeeID, string sortOrder, string currentFilter, string searchString, int?page, string PgeSize) { try // handle exogenous exceptions { try // log all exceptions { EmployeesBusinessModelLayers employeesLUBusinessModelLayers = new EmployeesBusinessModelLayers(); ViewBag.Employeess = new SelectList(employeesLUBusinessModelLayers.EmployeesSelect, "EmployeeID", "LastName"); ViewBag.CurrentSort = sortOrder; ViewBag.EmployeeID = EmployeeID; BusinessLayer.Validation pge = new BusinessLayer.Validation(); List <SelectListItem> PgeSizes = pge.PageSize(); //Assigning generic list to ViewBag ViewBag.PgeSizeList = PgeSizes; if (searchString != null) { page = 1; } else { searchString = currentFilter; } ViewBag.CurrentFilter = searchString; //EMPLOYEES TABLE MASTER BusinessModelLayer.Employees.EmployeesMasterDetailModel pageModel = new BusinessModelLayer.Employees.EmployeesMasterDetailModel(); EmployeesBusinessModelLayers employeesBusinessModelLayers = new EmployeesBusinessModelLayers(); Employees.EmployeesMasterDetailModel employeesMasterDetailModel = new Employees.EmployeesMasterDetailModel(); pageModel.Employees = employeesBusinessModelLayers.GetAllEmployeess().FirstOrDefault(x => x.EmployeeID == EmployeeID); int pageSize = 10; if (PgeSize != null) { Int32.TryParse(PgeSize, out pageSize); } ViewBag.PgeSize = pageSize; int pageNumber = (page ?? 1); //EMPLOYEETERRITORIES DETAIL BEGIN ViewBag.EmployeeIDSortParm = String.IsNullOrEmpty(sortOrder) ? "EmployeeID_desc" : ""; EmployeeterritoriesBusinessModelLayers employeeterritoriesBusinessModelLayers = new EmployeeterritoriesBusinessModelLayers(); List <BusinessModelLayer.Vwemployeeterritories> employeeterritoriess = employeeterritoriesBusinessModelLayers.GetAllVwemployeeterritoriess().FindAll(x => x.EmployeeID == EmployeeID).ToList(); if (!String.IsNullOrEmpty(searchString)) { employeeterritoriess = employeeterritoriess.Where(s => s.EmployeeID == EmployeeID && s.TerritoryID.ToString().ToLower().Contains(searchString.ToLower())).ToList(); } switch (sortOrder) { case "EmployeeID_desc": employeeterritoriess = employeeterritoriess.OrderByDescending(s => s.EmployeeID).ToList(); break; default: // EmployeeID ascending employeeterritoriess = employeeterritoriess.OrderBy(s => s.EmployeeID).ToList(); break; } pageModel.EmployeeterritoriesVwDetl = employeeterritoriess.ToPagedList(pageNumber, pageSize); //EMPLOYEETERRITORIES DETAIL END return(View(pageModel)); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); throw; } } catch (Exception) { throw; } }
//BEGIN - readBy public OrdersSingle GetOrdersData(int OrderID) { try // handle exogenous exceptions { try // log all exceptions { OrdersSingle orders = new OrdersSingle(); using (SqlConnection con = new SqlConnection(connectionString)) { string sqlQuery = "SELECT * FROM [Orders] WHERE OrderID= " + OrderID.ToString(); using (SqlCommand cmd = new SqlCommand(sqlQuery, con)) { con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { //orders.OrderID = (int)rdr["OrderID"]; orders.OrderID = rdr["OrderID"] == DBNull.Value ? default(int) : (int)rdr["OrderID"]; //orders.CustomerID = (string)rdr["CustomerID"]; orders.CustomerID = rdr["CustomerID"] == DBNull.Value ? "" : (string)rdr["CustomerID"]; //orders.EmployeeID = (int?)rdr["EmployeeID"]; orders.EmployeeID = rdr["EmployeeID"] == DBNull.Value ? default(int?) : (int?)rdr["EmployeeID"]; //orders.OrderDate = (System.DateTime?)rdr["OrderDate"]; orders.OrderDate = rdr["OrderDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["OrderDate"]; //orders.RequiredDate = (System.DateTime?)rdr["RequiredDate"]; orders.RequiredDate = rdr["RequiredDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["RequiredDate"]; //orders.ShippedDate = (System.DateTime?)rdr["ShippedDate"]; orders.ShippedDate = rdr["ShippedDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["ShippedDate"]; //orders.ShipVia = (int?)rdr["ShipVia"]; orders.ShipVia = rdr["ShipVia"] == DBNull.Value ? default(int?) : (int?)rdr["ShipVia"]; //orders.Freight = (decimal?)rdr["Freight"]; orders.Freight = rdr["Freight"] == DBNull.Value ? default(decimal?) : (decimal?)rdr["Freight"]; //orders.ShipName = (string)rdr["ShipName"]; orders.ShipName = rdr["ShipName"] == DBNull.Value ? "" : (string)rdr["ShipName"]; //orders.ShipAddress = (string)rdr["ShipAddress"]; orders.ShipAddress = rdr["ShipAddress"] == DBNull.Value ? "" : (string)rdr["ShipAddress"]; //orders.ShipCity = (string)rdr["ShipCity"]; orders.ShipCity = rdr["ShipCity"] == DBNull.Value ? "" : (string)rdr["ShipCity"]; //orders.ShipRegion = (string)rdr["ShipRegion"]; orders.ShipRegion = rdr["ShipRegion"] == DBNull.Value ? "" : (string)rdr["ShipRegion"]; //orders.ShipPostalCode = (string)rdr["ShipPostalCode"]; orders.ShipPostalCode = rdr["ShipPostalCode"] == DBNull.Value ? "" : (string)rdr["ShipPostalCode"]; //orders.ShipCountry = (string)rdr["ShipCountry"]; orders.ShipCountry = rdr["ShipCountry"] == DBNull.Value ? "" : (string)rdr["ShipCountry"]; //EXAMPLES: //employee.EmployeeId = Convert.ToInt32(rdr["EmployeeID"]); //employee.Name = rdr["Name"].ToString(); //employee.Gender = rdr["Gender"].ToString(); //employee.Salary = (decimal)rdr["Salary"]; //employee.City = rdr["City"].ToString(); //employee.IsPermanent = (bool)rdr["IsPermanent"]; //employee.DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]); } } } return(orders); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//BEGIN - readBy public ProductsSingle GetProductsData(int ProductID) { try // handle exogenous exceptions { try // log all exceptions { ProductsSingle products = new ProductsSingle(); using (SqlConnection con = new SqlConnection(connectionString)) { string sqlQuery = "SELECT * FROM [Products] WHERE ProductID= " + ProductID.ToString(); using (SqlCommand cmd = new SqlCommand(sqlQuery, con)) { con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { //products.ProductID = (int)rdr["ProductID"]; products.ProductID = rdr["ProductID"] == DBNull.Value ? default(int) : (int)rdr["ProductID"]; //products.ProductName = (string)rdr["ProductName"]; products.ProductName = rdr["ProductName"] == DBNull.Value ? "" : (string)rdr["ProductName"]; //products.SupplierID = (int?)rdr["SupplierID"]; products.SupplierID = rdr["SupplierID"] == DBNull.Value ? default(int?) : (int?)rdr["SupplierID"]; //products.CategoryID = (int?)rdr["CategoryID"]; products.CategoryID = rdr["CategoryID"] == DBNull.Value ? default(int?) : (int?)rdr["CategoryID"]; //products.QuantityPerUnit = (string)rdr["QuantityPerUnit"]; products.QuantityPerUnit = rdr["QuantityPerUnit"] == DBNull.Value ? "" : (string)rdr["QuantityPerUnit"]; //products.UnitPrice = (decimal?)rdr["UnitPrice"]; products.UnitPrice = rdr["UnitPrice"] == DBNull.Value ? default(decimal?) : (decimal?)rdr["UnitPrice"]; //products.UnitsInStock = (short?)rdr["UnitsInStock"]; products.UnitsInStock = rdr["UnitsInStock"] == DBNull.Value ? default(short?) : (short?)rdr["UnitsInStock"]; //products.UnitsOnOrder = (short?)rdr["UnitsOnOrder"]; products.UnitsOnOrder = rdr["UnitsOnOrder"] == DBNull.Value ? default(short?) : (short?)rdr["UnitsOnOrder"]; //products.ReorderLevel = (short?)rdr["ReorderLevel"]; products.ReorderLevel = rdr["ReorderLevel"] == DBNull.Value ? default(short?) : (short?)rdr["ReorderLevel"]; //products.Discontinued = (bool)rdr["Discontinued"]; products.Discontinued = rdr["Discontinued"] == DBNull.Value ? false : (bool)rdr["Discontinued"]; //EXAMPLES: //employee.EmployeeId = Convert.ToInt32(rdr["EmployeeID"]); //employee.Name = rdr["Name"].ToString(); //employee.Gender = rdr["Gender"].ToString(); //employee.Salary = (decimal)rdr["Salary"]; //employee.City = rdr["City"].ToString(); //employee.IsPermanent = (bool)rdr["IsPermanent"]; //employee.DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]); } } } return(products); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - readBy //BEGIN - create public void AddProducts(ProductsSingle products) { try // handle exogenous exceptions { try // log all exceptions { using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spAddProducts", con) { CommandType = CommandType.StoredProcedure }; SqlParameter paramProductID = new SqlParameter { ParameterName = "@ProductID", Value = products.ProductID }; cmd.Parameters.Add(paramProductID); SqlParameter paramProductName = new SqlParameter { ParameterName = "@ProductName", Value = products.ProductName }; cmd.Parameters.Add(paramProductName); SqlParameter paramSupplierID = new SqlParameter { ParameterName = "@SupplierID", Value = products.SupplierID }; cmd.Parameters.Add(paramSupplierID); SqlParameter paramCategoryID = new SqlParameter { ParameterName = "@CategoryID", Value = products.CategoryID }; cmd.Parameters.Add(paramCategoryID); SqlParameter paramQuantityPerUnit = new SqlParameter { ParameterName = "@QuantityPerUnit", Value = products.QuantityPerUnit }; cmd.Parameters.Add(paramQuantityPerUnit); SqlParameter paramUnitPrice = new SqlParameter { ParameterName = "@UnitPrice", Value = products.UnitPrice }; cmd.Parameters.Add(paramUnitPrice); SqlParameter paramUnitsInStock = new SqlParameter { ParameterName = "@UnitsInStock", Value = products.UnitsInStock }; cmd.Parameters.Add(paramUnitsInStock); SqlParameter paramUnitsOnOrder = new SqlParameter { ParameterName = "@UnitsOnOrder", Value = products.UnitsOnOrder }; cmd.Parameters.Add(paramUnitsOnOrder); SqlParameter paramReorderLevel = new SqlParameter { ParameterName = "@ReorderLevel", Value = products.ReorderLevel }; cmd.Parameters.Add(paramReorderLevel); SqlParameter paramDiscontinued = new SqlParameter { ParameterName = "@Discontinued", Value = products.Discontinued }; cmd.Parameters.Add(paramDiscontinued); con.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); } } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
public ActionResult Edit_Post(OrdersSingle orders, string sortOrder, string currentFilter, string searchString, int?page, string PgeSize) { try // handle exogenous exceptions { try // log all exceptions { OrdersBusinessModelLayers ordersBusinessModelLayers = new OrdersBusinessModelLayers(); if (ModelState.IsValid) { //mm ordersBusinessModelLayers.UpdateOrders(orders); return(RedirectToAction("List")); } ViewBag.CurrentSort = sortOrder; ViewBag.OrderID = orders.OrderID; BusinessLayer.Validation pge = new BusinessLayer.Validation(); List <SelectListItem> PgeSizes = pge.PageSize(); //Assigning generic list to ViewBag ViewBag.PgeSizeList = PgeSizes; if (searchString != null) { page = 1; } else { searchString = currentFilter; } ViewBag.CurrentFilter = searchString; //ORDERS TABLE MASTER BusinessModelLayer.Orders.OrdersMasterDetailModel pageModel = new BusinessModelLayer.Orders.OrdersMasterDetailModel { Orders = ordersBusinessModelLayers.GetAllOrderss().Single(x => x.OrderID == orders.OrderID) }; int pageSize = 10; if (PgeSize != null) { Int32.TryParse(PgeSize, out pageSize); } ViewBag.PgeSize = pageSize; int pageNumber = (page ?? 1); //ORDER_DETAILS DETAIL BEGIN ViewBag.OrderIDSortParm = String.IsNullOrEmpty(sortOrder) ? "OrderID_desc" : ""; Order_DetailsBusinessModelLayers order_detailsBusinessModelLayers = new Order_DetailsBusinessModelLayers(); pageModel.Order_DetailsVwDetl = order_detailsBusinessModelLayers.GetAllVworder_detailss().FindAll(x => x.OrderID == orders.OrderID).ToList().ToPagedList(pageNumber, pageSize); if (!String.IsNullOrEmpty(searchString)) { pageModel.Order_DetailsVwDetl = pageModel.Order_DetailsVwDetl.Where(s => s.OrderID.ToString().ToLower().Contains(searchString.ToLower())).ToList().ToPagedList(pageNumber, pageSize); } switch (sortOrder) { case "OrderID_desc": pageModel.Order_DetailsVwDetl = pageModel.Order_DetailsVwDetl.OrderByDescending(s => s.OrderID).ToList().ToPagedList(pageNumber, pageSize); break; default: // OrderID ascending pageModel.Order_DetailsVwDetl = pageModel.Order_DetailsVwDetl.OrderBy(s => s.OrderID).ToList().ToPagedList(pageNumber, pageSize); break; } //ORDER_DETAILS DETAIL END return(View(pageModel)); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); throw; } } catch (Exception) { throw; } }
//END - read //BEGIN - sql view public List <Vwcustomers> GetAllVwcustomerss() { try // handle exogenous exceptions { try // log all exceptions { List <Vwcustomers> Vwcustomerss = new List <Vwcustomers>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("SELECT * FROM Vwcustomers", con); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Vwcustomers vwcustomers = new Vwcustomers { // EXAMPLES: //EmployeeId = Convert.ToInt32(rdr["EmployeeId"]), //Name = rdr["Name"].ToString(), //IsPermanent = (bool)rdr["IsPermanent"], //Salary = Convert.ToDecimal(rdr["Salary"]), //DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]) //CustomerID = (string)rdr["CustomerID"] CustomerID = rdr["CustomerID"] == DBNull.Value ? "" : (string)rdr["CustomerID"] //,CompanyName = (string)rdr["CompanyName"] , CompanyName = rdr["CompanyName"] == DBNull.Value ? "" : (string)rdr["CompanyName"] //,ContactName = (string)rdr["ContactName"] , ContactName = rdr["ContactName"] == DBNull.Value ? "" : (string)rdr["ContactName"] //,ContactTitle = (string)rdr["ContactTitle"] , ContactTitle = rdr["ContactTitle"] == DBNull.Value ? "" : (string)rdr["ContactTitle"] //,Address = (string)rdr["Address"] , Address = rdr["Address"] == DBNull.Value ? "" : (string)rdr["Address"] //,City = (string)rdr["City"] , City = rdr["City"] == DBNull.Value ? "" : (string)rdr["City"] //,Region = (string)rdr["Region"] , Region = rdr["Region"] == DBNull.Value ? "" : (string)rdr["Region"] //,PostalCode = (string)rdr["PostalCode"] , PostalCode = rdr["PostalCode"] == DBNull.Value ? "" : (string)rdr["PostalCode"] //,Country = (string)rdr["Country"] , Country = rdr["Country"] == DBNull.Value ? "" : (string)rdr["Country"] //,Phone = (string)rdr["Phone"] , Phone = rdr["Phone"] == DBNull.Value ? "" : (string)rdr["Phone"] //,Fax = (string)rdr["Fax"] , Fax = rdr["Fax"] == DBNull.Value ? "" : (string)rdr["Fax"] }; Vwcustomerss.Add(vwcustomers); } con.Close(); cmd.Dispose(); } return(Vwcustomerss); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - read //BEGIN - sql view public List <Vworder_details> GetAllVworder_detailss() { try // handle exogenous exceptions { try // log all exceptions { List <Vworder_details> Vworder_Detailss = new List <Vworder_details>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("SELECT * FROM Vworder_Details", con); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Vworder_details vworder_details = new Vworder_details { // EXAMPLES: //EmployeeId = Convert.ToInt32(rdr["EmployeeId"]), //Name = rdr["Name"].ToString(), //IsPermanent = (bool)rdr["IsPermanent"], //Salary = Convert.ToDecimal(rdr["Salary"]), //DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]) //OrderID = (int)rdr["OrderID"] OrderID = rdr["OrderID"] == DBNull.Value ? default(int) : (int)rdr["OrderID"] //,ProductID = (int)rdr["ProductID"] , ProductID = rdr["ProductID"] == DBNull.Value ? default(int) : (int)rdr["ProductID"] //,UnitPrice = (decimal)rdr["UnitPrice"] , UnitPrice = rdr["UnitPrice"] == DBNull.Value ? default(decimal) : (decimal)rdr["UnitPrice"] //,Quantity = (short)rdr["Quantity"] , Quantity = rdr["Quantity"] == DBNull.Value ? default(short) : (short)rdr["Quantity"] //,Discount = (float)rdr["Discount"] , Discount = rdr["Discount"] == DBNull.Value ? default(float) : (float)rdr["Discount"] }; Vworder_Detailss.Add(vworder_details); } con.Close(); cmd.Dispose(); } return(Vworder_Detailss); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - readBy //BEGIN - create public void AddOrder_Details(Order_DetailsSingle order_details) { try // handle exogenous exceptions { try // log all exceptions { using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spAddOrder_Details", con) { CommandType = CommandType.StoredProcedure }; SqlParameter paramOrderID = new SqlParameter { ParameterName = "@OrderID", Value = order_details.OrderID }; cmd.Parameters.Add(paramOrderID); SqlParameter paramProductID = new SqlParameter { ParameterName = "@ProductID", Value = order_details.ProductID }; cmd.Parameters.Add(paramProductID); SqlParameter paramUnitPrice = new SqlParameter { ParameterName = "@UnitPrice", Value = order_details.UnitPrice }; cmd.Parameters.Add(paramUnitPrice); SqlParameter paramQuantity = new SqlParameter { ParameterName = "@Quantity", Value = order_details.Quantity }; cmd.Parameters.Add(paramQuantity); SqlParameter paramDiscount = new SqlParameter { ParameterName = "@Discount", Value = order_details.Discount }; cmd.Parameters.Add(paramDiscount); con.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); } } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
public ActionResult Edit(int CategoryID, string sortOrder, string currentFilter, string searchString, int?page, string PgeSize) { try // handle exogenous exceptions { try // log all exceptions { ViewBag.CurrentSort = sortOrder; ViewBag.CategoryID = CategoryID; BusinessLayer.Validation pge = new BusinessLayer.Validation(); List <SelectListItem> PgeSizes = pge.PageSize(); //Assigning generic list to ViewBag ViewBag.PgeSizeList = PgeSizes; if (searchString != null) { page = 1; } else { searchString = currentFilter; } ViewBag.CurrentFilter = searchString; //CATEGORIES TABLE MASTER BusinessModelLayer.Categories.CategoriesMasterDetailModel pageModel = new BusinessModelLayer.Categories.CategoriesMasterDetailModel(); CategoriesBusinessModelLayers categoriesBusinessModelLayers = new CategoriesBusinessModelLayers(); Categories.CategoriesMasterDetailModel categoriesMasterDetailModel = new Categories.CategoriesMasterDetailModel(); pageModel.Categories = categoriesBusinessModelLayers.GetAllCategoriess().FirstOrDefault(x => x.CategoryID == CategoryID); int pageSize = 10; if (PgeSize != null) { Int32.TryParse(PgeSize, out pageSize); } ViewBag.PgeSize = pageSize; int pageNumber = (page ?? 1); //PRODUCTS DETAIL BEGIN ViewBag.CategoryIDSortParm = String.IsNullOrEmpty(sortOrder) ? "CategoryID_desc" : ""; ProductsBusinessModelLayers productsBusinessModelLayers = new ProductsBusinessModelLayers(); List <BusinessModelLayer.Vwproducts> productss = productsBusinessModelLayers.GetAllVwproductss().FindAll(x => x.CategoryID == CategoryID).ToList(); if (!String.IsNullOrEmpty(searchString)) { productss = productss.Where(s => s.CategoryID == CategoryID && s.ProductName.ToString().ToLower().Contains(searchString.ToLower())).ToList(); } switch (sortOrder) { case "CategoryID_desc": productss = productss.OrderByDescending(s => s.CategoryID).ToList(); break; default: // CategoryID ascending productss = productss.OrderBy(s => s.CategoryID).ToList(); break; } pageModel.ProductsVwDetl = productss.ToPagedList(pageNumber, pageSize); //PRODUCTS DETAIL END return(View(pageModel)); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); throw; } } catch (Exception) { throw; } }
//END - read //BEGIN - sql view public List <Vworders> GetAllVworderss() { try // handle exogenous exceptions { try // log all exceptions { List <Vworders> Vworderss = new List <Vworders>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("SELECT * FROM Vworders", con); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Vworders vworders = new Vworders { // EXAMPLES: //EmployeeId = Convert.ToInt32(rdr["EmployeeId"]), //Name = rdr["Name"].ToString(), //IsPermanent = (bool)rdr["IsPermanent"], //Salary = Convert.ToDecimal(rdr["Salary"]), //DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]) //OrderID = (int)rdr["OrderID"] OrderID = rdr["OrderID"] == DBNull.Value ? default(int) : (int)rdr["OrderID"] //,CustomerID = (string)rdr["CustomerID"] , CustomerID = rdr["CustomerID"] == DBNull.Value ? "" : (string)rdr["CustomerID"] //,EmployeeID = (int?)rdr["EmployeeID"] , EmployeeID = rdr["EmployeeID"] == DBNull.Value ? default(int?) : (int?)rdr["EmployeeID"] //,OrderDate = (System.DateTime?)rdr["OrderDate"] , OrderDate = rdr["OrderDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["OrderDate"] //,RequiredDate = (System.DateTime?)rdr["RequiredDate"] , RequiredDate = rdr["RequiredDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["RequiredDate"] //,ShippedDate = (System.DateTime?)rdr["ShippedDate"] , ShippedDate = rdr["ShippedDate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["ShippedDate"] //,ShipVia = (int?)rdr["ShipVia"] , ShipVia = rdr["ShipVia"] == DBNull.Value ? default(int?) : (int?)rdr["ShipVia"] //,Freight = (decimal?)rdr["Freight"] , Freight = rdr["Freight"] == DBNull.Value ? default(decimal?) : (decimal?)rdr["Freight"] //,ShipName = (string)rdr["ShipName"] , ShipName = rdr["ShipName"] == DBNull.Value ? "" : (string)rdr["ShipName"] //,ShipAddress = (string)rdr["ShipAddress"] , ShipAddress = rdr["ShipAddress"] == DBNull.Value ? "" : (string)rdr["ShipAddress"] //,ShipCity = (string)rdr["ShipCity"] , ShipCity = rdr["ShipCity"] == DBNull.Value ? "" : (string)rdr["ShipCity"] //,ShipRegion = (string)rdr["ShipRegion"] , ShipRegion = rdr["ShipRegion"] == DBNull.Value ? "" : (string)rdr["ShipRegion"] //,ShipPostalCode = (string)rdr["ShipPostalCode"] , ShipPostalCode = rdr["ShipPostalCode"] == DBNull.Value ? "" : (string)rdr["ShipPostalCode"] //,ShipCountry = (string)rdr["ShipCountry"] , ShipCountry = rdr["ShipCountry"] == DBNull.Value ? "" : (string)rdr["ShipCountry"] }; Vworderss.Add(vworders); } con.Close(); cmd.Dispose(); } return(Vworderss); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - create //BEGIN - update public void UpdateOrders(OrdersSingle orders) { try // handle exogenous exceptions { try // log all exceptions { BusinessLayer.Validation isdte = new BusinessLayer.Validation(); if (isdte.IsDate(orders.OrderDate) == false) { orders.OrderDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue; } if (isdte.IsDate(orders.RequiredDate) == false) { orders.RequiredDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue; } if (isdte.IsDate(orders.ShippedDate) == false) { orders.ShippedDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue; } using (SqlConnection con = new SqlConnection(connectionString)) { using (SqlCommand cmd = new SqlCommand("spUpdateOrders", con) { CommandType = CommandType.StoredProcedure }) { cmd.Parameters.AddWithValue("@OrderID", orders.OrderID); cmd.Parameters.AddWithValue("@CustomerID", (object)orders.CustomerID ?? DBNull.Value); cmd.Parameters.AddWithValue("@EmployeeID", (object)orders.EmployeeID ?? DBNull.Value); cmd.Parameters.AddWithValue("@OrderDate", (object)orders.OrderDate ?? DBNull.Value); cmd.Parameters.AddWithValue("@RequiredDate", (object)orders.RequiredDate ?? DBNull.Value); cmd.Parameters.AddWithValue("@ShippedDate", (object)orders.ShippedDate ?? DBNull.Value); cmd.Parameters.AddWithValue("@ShipVia", (object)orders.ShipVia ?? DBNull.Value); cmd.Parameters.AddWithValue("@Freight", (object)orders.Freight ?? DBNull.Value); cmd.Parameters.AddWithValue("@ShipName", (object)orders.ShipName ?? DBNull.Value); cmd.Parameters.AddWithValue("@ShipAddress", (object)orders.ShipAddress ?? DBNull.Value); cmd.Parameters.AddWithValue("@ShipCity", (object)orders.ShipCity ?? DBNull.Value); cmd.Parameters.AddWithValue("@ShipRegion", (object)orders.ShipRegion ?? DBNull.Value); cmd.Parameters.AddWithValue("@ShipPostalCode", (object)orders.ShipPostalCode ?? DBNull.Value); cmd.Parameters.AddWithValue("@ShipCountry", (object)orders.ShipCountry ?? DBNull.Value); con.Open(); cmd.ExecuteNonQuery(); } con.Close(); } } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - delete //BEGIN - read public List <Tbl_LoginSingle> GetAllTbl_Logins() { try // handle exogenous exceptions { try // log all exceptions { List <Tbl_LoginSingle> tbl_logins = new List <Tbl_LoginSingle>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spGetAllTbl_Login", con) { CommandType = CommandType.StoredProcedure }; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Tbl_LoginSingle tbl_login = new Tbl_LoginSingle { // EXAMPLES: //EmployeeId = Convert.ToInt32(rdr["EmployeeId"]), //Name = rdr["Name"].ToString(), //IsPermanent = (bool)rdr["IsPermanent"], //Salary = Convert.ToDecimal(rdr["Salary"]), //DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]) //Username = (string)rdr["Username"] Username = rdr["Username"] == DBNull.Value ? "" : (string)rdr["Username"] //,Password = (string)rdr["Password"] , Password = rdr["Password"] == DBNull.Value ? "" : (string)rdr["Password"] //,Roles = (string)rdr["Roles"] , Roles = rdr["Roles"] == DBNull.Value ? "" : (string)rdr["Roles"] //,ActiveStatus = (bool)rdr["ActiveStatus"] , ActiveStatus = rdr["ActiveStatus"] == DBNull.Value ? false : (bool)rdr["ActiveStatus"] }; tbl_logins.Add(tbl_login); } con.Close(); cmd.Dispose(); } return(tbl_logins); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
public ActionResult Edit(int OrderID, string sortOrder, string currentFilter, string searchString, int?page, string PgeSize) { try // handle exogenous exceptions { try // log all exceptions { ShippersBusinessModelLayers shippersLUBusinessModelLayers = new ShippersBusinessModelLayers(); ViewBag.Shipperss = new SelectList(shippersLUBusinessModelLayers.ShippersSelect, "ShipperID", "CompanyName"); EmployeesBusinessModelLayers employeesLUBusinessModelLayers = new EmployeesBusinessModelLayers(); ViewBag.Employeess = new SelectList(employeesLUBusinessModelLayers.EmployeesSelect, "EmployeeID", "LastName"); CustomersBusinessModelLayers customersLUBusinessModelLayers = new CustomersBusinessModelLayers(); ViewBag.Customerss = new SelectList(customersLUBusinessModelLayers.CustomersSelect, "CustomerID", "CompanyName"); ViewBag.CurrentSort = sortOrder; ViewBag.OrderID = OrderID; BusinessLayer.Validation pge = new BusinessLayer.Validation(); List <SelectListItem> PgeSizes = pge.PageSize(); //Assigning generic list to ViewBag ViewBag.PgeSizeList = PgeSizes; if (searchString != null) { page = 1; } else { searchString = currentFilter; } ViewBag.CurrentFilter = searchString; //ORDERS TABLE MASTER BusinessModelLayer.Orders.OrdersMasterDetailModel pageModel = new BusinessModelLayer.Orders.OrdersMasterDetailModel(); OrdersBusinessModelLayers ordersBusinessModelLayers = new OrdersBusinessModelLayers(); Orders.OrdersMasterDetailModel ordersMasterDetailModel = new Orders.OrdersMasterDetailModel(); pageModel.Orders = ordersBusinessModelLayers.GetAllOrderss().FirstOrDefault(x => x.OrderID == OrderID); int pageSize = 10; if (PgeSize != null) { Int32.TryParse(PgeSize, out pageSize); } ViewBag.PgeSize = pageSize; int pageNumber = (page ?? 1); //ORDER_DETAILS DETAIL BEGIN ViewBag.OrderIDSortParm = String.IsNullOrEmpty(sortOrder) ? "OrderID_desc" : ""; Order_DetailsBusinessModelLayers order_detailsBusinessModelLayers = new Order_DetailsBusinessModelLayers(); List <BusinessModelLayer.Vworder_details> order_detailss = order_detailsBusinessModelLayers.GetAllVworder_detailss().FindAll(x => x.OrderID == OrderID).ToList(); if (!String.IsNullOrEmpty(searchString)) { order_detailss = order_detailss.Where(s => s.OrderID == OrderID && s.ProductID.ToString().ToLower().Contains(searchString.ToLower())).ToList(); } switch (sortOrder) { case "OrderID_desc": order_detailss = order_detailss.OrderByDescending(s => s.OrderID).ToList(); break; default: // OrderID ascending order_detailss = order_detailss.OrderBy(s => s.OrderID).ToList(); break; } pageModel.Order_DetailsVwDetl = order_detailss.ToPagedList(pageNumber, pageSize); //ORDER_DETAILS DETAIL END return(View(pageModel)); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); throw; } } catch (Exception) { throw; } }
//BEGIN - readBy public CustomersSingle GetCustomersData(string CustomerID) { try // handle exogenous exceptions { try // log all exceptions { CustomersSingle customers = new CustomersSingle(); using (SqlConnection con = new SqlConnection(connectionString)) { string sqlQuery = "SELECT * FROM [Customers] WHERE CustomerID= '" + CustomerID.ToString() + "'"; using (SqlCommand cmd = new SqlCommand(sqlQuery, con)) { con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { //customers.CustomerID = (string)rdr["CustomerID"]; customers.CustomerID = rdr["CustomerID"] == DBNull.Value ? "" : (string)rdr["CustomerID"]; //customers.CompanyName = (string)rdr["CompanyName"]; customers.CompanyName = rdr["CompanyName"] == DBNull.Value ? "" : (string)rdr["CompanyName"]; //customers.ContactName = (string)rdr["ContactName"]; customers.ContactName = rdr["ContactName"] == DBNull.Value ? "" : (string)rdr["ContactName"]; //customers.ContactTitle = (string)rdr["ContactTitle"]; customers.ContactTitle = rdr["ContactTitle"] == DBNull.Value ? "" : (string)rdr["ContactTitle"]; //customers.Address = (string)rdr["Address"]; customers.Address = rdr["Address"] == DBNull.Value ? "" : (string)rdr["Address"]; //customers.City = (string)rdr["City"]; customers.City = rdr["City"] == DBNull.Value ? "" : (string)rdr["City"]; //customers.Region = (string)rdr["Region"]; customers.Region = rdr["Region"] == DBNull.Value ? "" : (string)rdr["Region"]; //customers.PostalCode = (string)rdr["PostalCode"]; customers.PostalCode = rdr["PostalCode"] == DBNull.Value ? "" : (string)rdr["PostalCode"]; //customers.Country = (string)rdr["Country"]; customers.Country = rdr["Country"] == DBNull.Value ? "" : (string)rdr["Country"]; //customers.Phone = (string)rdr["Phone"]; customers.Phone = rdr["Phone"] == DBNull.Value ? "" : (string)rdr["Phone"]; //customers.Fax = (string)rdr["Fax"]; customers.Fax = rdr["Fax"] == DBNull.Value ? "" : (string)rdr["Fax"]; //EXAMPLES: //employee.EmployeeId = Convert.ToInt32(rdr["EmployeeID"]); //employee.Name = rdr["Name"].ToString(); //employee.Gender = rdr["Gender"].ToString(); //employee.Salary = (decimal)rdr["Salary"]; //employee.City = rdr["City"].ToString(); //employee.IsPermanent = (bool)rdr["IsPermanent"]; //employee.DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]); } } } return(customers); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - delete //BEGIN - read public List <CategoriesSingle> GetAllCategoriess() { try // handle exogenous exceptions { try // log all exceptions { List <CategoriesSingle> categoriess = new List <CategoriesSingle>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spGetAllCategories", con) { CommandType = CommandType.StoredProcedure }; con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { CategoriesSingle categories = new CategoriesSingle { // EXAMPLES: //EmployeeId = Convert.ToInt32(rdr["EmployeeId"]), //Name = rdr["Name"].ToString(), //IsPermanent = (bool)rdr["IsPermanent"], //Salary = Convert.ToDecimal(rdr["Salary"]), //DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]) //CategoryID = (int)rdr["CategoryID"] CategoryID = rdr["CategoryID"] == DBNull.Value ? default(int) : (int)rdr["CategoryID"] //,CategoryName = (string)rdr["CategoryName"] , CategoryName = rdr["CategoryName"] == DBNull.Value ? "" : (string)rdr["CategoryName"] //,Description = (string)rdr["Description"] , Description = rdr["Description"] == DBNull.Value ? "" : (string)rdr["Description"] //,Picture = (byte[])rdr["Picture"] , Picture = rdr["Picture"] == DBNull.Value ? default(byte[]) : (byte[])rdr["Picture"] }; categoriess.Add(categories); } con.Close(); cmd.Dispose(); } return(categoriess); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - readBy //BEGIN - create public void AddCustomers(CustomersSingle customers) { try // handle exogenous exceptions { try // log all exceptions { using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spAddCustomers", con) { CommandType = CommandType.StoredProcedure }; SqlParameter paramCustomerID = new SqlParameter { ParameterName = "@CustomerID", Value = customers.CustomerID }; cmd.Parameters.Add(paramCustomerID); SqlParameter paramCompanyName = new SqlParameter { ParameterName = "@CompanyName", Value = customers.CompanyName }; cmd.Parameters.Add(paramCompanyName); SqlParameter paramContactName = new SqlParameter { ParameterName = "@ContactName", Value = customers.ContactName }; cmd.Parameters.Add(paramContactName); SqlParameter paramContactTitle = new SqlParameter { ParameterName = "@ContactTitle", Value = customers.ContactTitle }; cmd.Parameters.Add(paramContactTitle); SqlParameter paramAddress = new SqlParameter { ParameterName = "@Address", Value = customers.Address }; cmd.Parameters.Add(paramAddress); SqlParameter paramCity = new SqlParameter { ParameterName = "@City", Value = customers.City }; cmd.Parameters.Add(paramCity); SqlParameter paramRegion = new SqlParameter { ParameterName = "@Region", Value = customers.Region }; cmd.Parameters.Add(paramRegion); SqlParameter paramPostalCode = new SqlParameter { ParameterName = "@PostalCode", Value = customers.PostalCode }; cmd.Parameters.Add(paramPostalCode); SqlParameter paramCountry = new SqlParameter { ParameterName = "@Country", Value = customers.Country }; cmd.Parameters.Add(paramCountry); SqlParameter paramPhone = new SqlParameter { ParameterName = "@Phone", Value = customers.Phone }; cmd.Parameters.Add(paramPhone); SqlParameter paramFax = new SqlParameter { ParameterName = "@Fax", Value = customers.Fax }; cmd.Parameters.Add(paramFax); con.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); } } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
public ActionResult Edit(string CustomerTypeID, string sortOrder, string currentFilter, string searchString, int?page, string PgeSize) { try // handle exogenous exceptions { try // log all exceptions { ViewBag.CurrentSort = sortOrder; ViewBag.CustomerTypeID = CustomerTypeID; BusinessLayer.Validation pge = new BusinessLayer.Validation(); List <SelectListItem> PgeSizes = pge.PageSize(); //Assigning generic list to ViewBag ViewBag.PgeSizeList = PgeSizes; if (searchString != null) { page = 1; } else { searchString = currentFilter; } ViewBag.CurrentFilter = searchString; //CUSTOMERDEMOGRAPHICS TABLE MASTER BusinessModelLayer.Customerdemographics.CustomerdemographicsMasterDetailModel pageModel = new BusinessModelLayer.Customerdemographics.CustomerdemographicsMasterDetailModel(); CustomerdemographicsBusinessModelLayers customerdemographicsBusinessModelLayers = new CustomerdemographicsBusinessModelLayers(); Customerdemographics.CustomerdemographicsMasterDetailModel customerdemographicsMasterDetailModel = new Customerdemographics.CustomerdemographicsMasterDetailModel(); pageModel.Customerdemographics = customerdemographicsBusinessModelLayers.GetAllCustomerdemographicss().FirstOrDefault(x => x.CustomerTypeID == CustomerTypeID); int pageSize = 10; if (PgeSize != null) { Int32.TryParse(PgeSize, out pageSize); } ViewBag.PgeSize = pageSize; int pageNumber = (page ?? 1); //CUSTOMERCUSTOMERDEMO DETAIL BEGIN ViewBag.CustomerTypeIDSortParm = String.IsNullOrEmpty(sortOrder) ? "CustomerTypeID_desc" : ""; CustomercustomerdemoBusinessModelLayers customercustomerdemoBusinessModelLayers = new CustomercustomerdemoBusinessModelLayers(); List <BusinessModelLayer.Vwcustomercustomerdemo> customercustomerdemos = customercustomerdemoBusinessModelLayers.GetAllVwcustomercustomerdemos().FindAll(x => x.CustomerTypeID == CustomerTypeID).ToList(); if (!String.IsNullOrEmpty(searchString)) { customercustomerdemos = customercustomerdemos.Where(s => s.CustomerTypeID == CustomerTypeID && s.CustomerTypeID.ToString().ToLower().Contains(searchString.ToLower())).ToList(); } switch (sortOrder) { case "CustomerTypeID_desc": customercustomerdemos = customercustomerdemos.OrderByDescending(s => s.CustomerTypeID).ToList(); break; default: // CustomerTypeID ascending customercustomerdemos = customercustomerdemos.OrderBy(s => s.CustomerTypeID).ToList(); break; } pageModel.CustomercustomerdemoVwDetl = customercustomerdemos.ToPagedList(pageNumber, pageSize); //CUSTOMERCUSTOMERDEMO DETAIL END return(View(pageModel)); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); throw; } } catch (Exception) { throw; } }
//END - read //BEGIN - sql view public List <Vwproducts> GetAllVwproductss() { try // handle exogenous exceptions { try // log all exceptions { List <Vwproducts> Vwproductss = new List <Vwproducts>(); using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("SELECT * FROM Vwproducts", con); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { Vwproducts vwproducts = new Vwproducts { // EXAMPLES: //EmployeeId = Convert.ToInt32(rdr["EmployeeId"]), //Name = rdr["Name"].ToString(), //IsPermanent = (bool)rdr["IsPermanent"], //Salary = Convert.ToDecimal(rdr["Salary"]), //DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]) //ProductID = (int)rdr["ProductID"] ProductID = rdr["ProductID"] == DBNull.Value ? default(int) : (int)rdr["ProductID"] //,ProductName = (string)rdr["ProductName"] , ProductName = rdr["ProductName"] == DBNull.Value ? "" : (string)rdr["ProductName"] //,SupplierID = (int?)rdr["SupplierID"] , SupplierID = rdr["SupplierID"] == DBNull.Value ? default(int?) : (int?)rdr["SupplierID"] //,CategoryID = (int?)rdr["CategoryID"] , CategoryID = rdr["CategoryID"] == DBNull.Value ? default(int?) : (int?)rdr["CategoryID"] //,QuantityPerUnit = (string)rdr["QuantityPerUnit"] , QuantityPerUnit = rdr["QuantityPerUnit"] == DBNull.Value ? "" : (string)rdr["QuantityPerUnit"] //,UnitPrice = (decimal?)rdr["UnitPrice"] , UnitPrice = rdr["UnitPrice"] == DBNull.Value ? default(decimal?) : (decimal?)rdr["UnitPrice"] //,UnitsInStock = (short?)rdr["UnitsInStock"] , UnitsInStock = rdr["UnitsInStock"] == DBNull.Value ? default(short?) : (short?)rdr["UnitsInStock"] //,UnitsOnOrder = (short?)rdr["UnitsOnOrder"] , UnitsOnOrder = rdr["UnitsOnOrder"] == DBNull.Value ? default(short?) : (short?)rdr["UnitsOnOrder"] //,ReorderLevel = (short?)rdr["ReorderLevel"] , ReorderLevel = rdr["ReorderLevel"] == DBNull.Value ? default(short?) : (short?)rdr["ReorderLevel"] //,Discontinued = (bool)rdr["Discontinued"] , Discontinued = rdr["Discontinued"] == DBNull.Value ? false : (bool)rdr["Discontinued"] }; Vwproductss.Add(vwproducts); } con.Close(); cmd.Dispose(); } return(Vwproductss); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
public ActionResult Edit(int RegionID, string sortOrder, string currentFilter, string searchString, int?page, string PgeSize) { try // handle exogenous exceptions { try // log all exceptions { ViewBag.CurrentSort = sortOrder; ViewBag.RegionID = RegionID; BusinessLayer.Validation pge = new BusinessLayer.Validation(); List <SelectListItem> PgeSizes = pge.PageSize(); //Assigning generic list to ViewBag ViewBag.PgeSizeList = PgeSizes; if (searchString != null) { page = 1; } else { searchString = currentFilter; } ViewBag.CurrentFilter = searchString; //REGION TABLE MASTER BusinessModelLayer.Region.RegionMasterDetailModel pageModel = new BusinessModelLayer.Region.RegionMasterDetailModel(); RegionBusinessModelLayers regionBusinessModelLayers = new RegionBusinessModelLayers(); Region.RegionMasterDetailModel regionMasterDetailModel = new Region.RegionMasterDetailModel(); pageModel.Region = regionBusinessModelLayers.GetAllRegions().FirstOrDefault(x => x.RegionID == RegionID); int pageSize = 10; if (PgeSize != null) { Int32.TryParse(PgeSize, out pageSize); } ViewBag.PgeSize = pageSize; int pageNumber = (page ?? 1); //TERRITORIES DETAIL BEGIN ViewBag.RegionIDSortParm = String.IsNullOrEmpty(sortOrder) ? "RegionID_desc" : ""; TerritoriesBusinessModelLayers territoriesBusinessModelLayers = new TerritoriesBusinessModelLayers(); List <BusinessModelLayer.Vwterritories> territoriess = territoriesBusinessModelLayers.GetAllVwterritoriess().FindAll(x => x.RegionID == RegionID).ToList(); if (!String.IsNullOrEmpty(searchString)) { territoriess = territoriess.Where(s => s.RegionID == RegionID && s.TerritoryDescription.ToString().ToLower().Contains(searchString.ToLower())).ToList(); } switch (sortOrder) { case "RegionID_desc": territoriess = territoriess.OrderByDescending(s => s.RegionID).ToList(); break; default: // RegionID ascending territoriess = territoriess.OrderBy(s => s.RegionID).ToList(); break; } pageModel.TerritoriesVwDetl = territoriess.ToPagedList(pageNumber, pageSize); //TERRITORIES DETAIL END return(View(pageModel)); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); throw; } } catch (Exception) { throw; } }
public ViewResult List(string sortOrder, string currentFilter, string searchString, int?page, string PgeSize) { try // handle exogenous exceptions { try // log all exceptions { ViewBag.CurrentSort = sortOrder; ViewBag.LastNameSortParm = String.IsNullOrEmpty(sortOrder) ? "LastName_desc" : ""; BusinessLayer.Validation pge = new BusinessLayer.Validation(); List <SelectListItem> PgeSizes = pge.PageSize(); //Assigning generic list to ViewBag ViewBag.PgeSizeList = PgeSizes; if (searchString != null) { page = 1; } else { searchString = currentFilter; } ViewBag.CurrentFilter = searchString; EmployeesBusinessModelLayers employeesBusinessModelLayers = new EmployeesBusinessModelLayers(); List <BusinessModelLayer.Vwemployees> vwemployeess = employeesBusinessModelLayers.GetAllVwemployeess(); if (!String.IsNullOrEmpty(searchString)) { vwemployeess = vwemployeess.Where(s => s.LastName.ToString().ToLower().Contains(searchString.ToLower())).ToList(); } switch (sortOrder) { case "LastName_desc": vwemployeess = vwemployeess.OrderByDescending(s => s.LastName).ToList(); break; //case "!!!": // vwemployeess = vwemployeess.OrderBy(s => s.!!!).ToList(); // break; //case "!!!_desc": // vwemployeess = vwemployeess.OrderByDescending(s => s.!!!).ToList(); // break; default: // LastName ascending vwemployeess = vwemployeess.OrderBy(s => s.LastName).ToList(); break; } int pageSize = 10; if (PgeSize != null) { Int32.TryParse(PgeSize, out pageSize); } ViewBag.PgeSize = pageSize; int pageNumber = (page ?? 1); return(View(vwemployeess.ToPagedList(pageNumber, pageSize))); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); throw; } } catch (Exception) { throw; } }
//BEGIN - readBy public Tbl_ExceptionloggingtodatabaseSingle GetTbl_ExceptionloggingtodatabaseData(long Logid) { try // handle exogenous exceptions { try // log all exceptions { Tbl_ExceptionloggingtodatabaseSingle tbl_exceptionloggingtodatabase = new Tbl_ExceptionloggingtodatabaseSingle(); using (SqlConnection con = new SqlConnection(connectionString)) { string sqlQuery = "SELECT * FROM [tbl_ExceptionLoggingToDataBase] WHERE Logid= " + Logid.ToString(); using (SqlCommand cmd = new SqlCommand(sqlQuery, con)) { con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { //tbl_exceptionloggingtodatabase.Logid = (long)rdr["Logid"]; tbl_exceptionloggingtodatabase.Logid = rdr["Logid"] == DBNull.Value ? default(long) : (long)rdr["Logid"]; //tbl_exceptionloggingtodatabase.ExceptionMsg = (string)rdr["ExceptionMsg"]; tbl_exceptionloggingtodatabase.ExceptionMsg = rdr["ExceptionMsg"] == DBNull.Value ? "" : (string)rdr["ExceptionMsg"]; //tbl_exceptionloggingtodatabase.ExceptionType = (string)rdr["ExceptionType"]; tbl_exceptionloggingtodatabase.ExceptionType = rdr["ExceptionType"] == DBNull.Value ? "" : (string)rdr["ExceptionType"]; //tbl_exceptionloggingtodatabase.ExceptionSource = (string)rdr["ExceptionSource"]; tbl_exceptionloggingtodatabase.ExceptionSource = rdr["ExceptionSource"] == DBNull.Value ? "" : (string)rdr["ExceptionSource"]; //tbl_exceptionloggingtodatabase.ExceptionURL = (string)rdr["ExceptionURL"]; tbl_exceptionloggingtodatabase.ExceptionURL = rdr["ExceptionURL"] == DBNull.Value ? "" : (string)rdr["ExceptionURL"]; //tbl_exceptionloggingtodatabase.Logdate = (System.DateTime?)rdr["Logdate"]; tbl_exceptionloggingtodatabase.Logdate = rdr["Logdate"] == DBNull.Value ? default(DateTime) : (System.DateTime?)rdr["Logdate"]; //EXAMPLES: //employee.EmployeeId = Convert.ToInt32(rdr["EmployeeID"]); //employee.Name = rdr["Name"].ToString(); //employee.Gender = rdr["Gender"].ToString(); //employee.Salary = (decimal)rdr["Salary"]; //employee.City = rdr["City"].ToString(); //employee.IsPermanent = (bool)rdr["IsPermanent"]; //employee.DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]); } } } return(tbl_exceptionloggingtodatabase); } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - readBy //BEGIN - create public void AddEmployees(EmployeesSingle employees) { try // handle exogenous exceptions { try // log all exceptions { BusinessLayer.Validation isdte = new BusinessLayer.Validation(); if (isdte.IsDate(employees.BirthDate) == false) { employees.BirthDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue; } if (isdte.IsDate(employees.HireDate) == false) { employees.HireDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue; } using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spAddEmployees", con) { CommandType = CommandType.StoredProcedure }; SqlParameter paramEmployeeID = new SqlParameter { ParameterName = "@EmployeeID", Value = employees.EmployeeID }; cmd.Parameters.Add(paramEmployeeID); SqlParameter paramLastName = new SqlParameter { ParameterName = "@LastName", Value = employees.LastName }; cmd.Parameters.Add(paramLastName); SqlParameter paramFirstName = new SqlParameter { ParameterName = "@FirstName", Value = employees.FirstName }; cmd.Parameters.Add(paramFirstName); SqlParameter paramTitle = new SqlParameter { ParameterName = "@Title", Value = employees.Title }; cmd.Parameters.Add(paramTitle); SqlParameter paramTitleOfCourtesy = new SqlParameter { ParameterName = "@TitleOfCourtesy", Value = employees.TitleOfCourtesy }; cmd.Parameters.Add(paramTitleOfCourtesy); SqlParameter paramBirthDate = new SqlParameter { ParameterName = "@BirthDate", Value = employees.BirthDate }; cmd.Parameters.Add(paramBirthDate); SqlParameter paramHireDate = new SqlParameter { ParameterName = "@HireDate", Value = employees.HireDate }; cmd.Parameters.Add(paramHireDate); SqlParameter paramAddress = new SqlParameter { ParameterName = "@Address", Value = employees.Address }; cmd.Parameters.Add(paramAddress); SqlParameter paramCity = new SqlParameter { ParameterName = "@City", Value = employees.City }; cmd.Parameters.Add(paramCity); SqlParameter paramRegion = new SqlParameter { ParameterName = "@Region", Value = employees.Region }; cmd.Parameters.Add(paramRegion); SqlParameter paramPostalCode = new SqlParameter { ParameterName = "@PostalCode", Value = employees.PostalCode }; cmd.Parameters.Add(paramPostalCode); SqlParameter paramCountry = new SqlParameter { ParameterName = "@Country", Value = employees.Country }; cmd.Parameters.Add(paramCountry); SqlParameter paramHomePhone = new SqlParameter { ParameterName = "@HomePhone", Value = employees.HomePhone }; cmd.Parameters.Add(paramHomePhone); SqlParameter paramExtension = new SqlParameter { ParameterName = "@Extension", Value = employees.Extension }; cmd.Parameters.Add(paramExtension); SqlParameter paramPhoto = new SqlParameter { ParameterName = "@Photo", Value = employees.Photo }; cmd.Parameters.Add(paramPhoto); SqlParameter paramNotes = new SqlParameter { ParameterName = "@Notes", Value = employees.Notes }; cmd.Parameters.Add(paramNotes); SqlParameter paramReportsTo = new SqlParameter { ParameterName = "@ReportsTo", Value = employees.ReportsTo }; cmd.Parameters.Add(paramReportsTo); SqlParameter paramPhotoPath = new SqlParameter { ParameterName = "@PhotoPath", Value = employees.PhotoPath }; cmd.Parameters.Add(paramPhotoPath); con.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); } } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }
//END - readBy //BEGIN - create public void AddOrders(OrdersSingle orders) { try // handle exogenous exceptions { try // log all exceptions { BusinessLayer.Validation isdte = new BusinessLayer.Validation(); if (isdte.IsDate(orders.OrderDate) == false) { orders.OrderDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue; } if (isdte.IsDate(orders.RequiredDate) == false) { orders.RequiredDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue; } if (isdte.IsDate(orders.ShippedDate) == false) { orders.ShippedDate = (DateTime)System.Data.SqlTypes.SqlDateTime.MinValue; } using (SqlConnection con = new SqlConnection(connectionString)) { SqlCommand cmd = new SqlCommand("spAddOrders", con) { CommandType = CommandType.StoredProcedure }; SqlParameter paramOrderID = new SqlParameter { ParameterName = "@OrderID", Value = orders.OrderID }; cmd.Parameters.Add(paramOrderID); SqlParameter paramCustomerID = new SqlParameter { ParameterName = "@CustomerID", Value = orders.CustomerID }; cmd.Parameters.Add(paramCustomerID); SqlParameter paramEmployeeID = new SqlParameter { ParameterName = "@EmployeeID", Value = orders.EmployeeID }; cmd.Parameters.Add(paramEmployeeID); SqlParameter paramOrderDate = new SqlParameter { ParameterName = "@OrderDate", Value = orders.OrderDate }; cmd.Parameters.Add(paramOrderDate); SqlParameter paramRequiredDate = new SqlParameter { ParameterName = "@RequiredDate", Value = orders.RequiredDate }; cmd.Parameters.Add(paramRequiredDate); SqlParameter paramShippedDate = new SqlParameter { ParameterName = "@ShippedDate", Value = orders.ShippedDate }; cmd.Parameters.Add(paramShippedDate); SqlParameter paramShipVia = new SqlParameter { ParameterName = "@ShipVia", Value = orders.ShipVia }; cmd.Parameters.Add(paramShipVia); SqlParameter paramFreight = new SqlParameter { ParameterName = "@Freight", Value = orders.Freight }; cmd.Parameters.Add(paramFreight); SqlParameter paramShipName = new SqlParameter { ParameterName = "@ShipName", Value = orders.ShipName }; cmd.Parameters.Add(paramShipName); SqlParameter paramShipAddress = new SqlParameter { ParameterName = "@ShipAddress", Value = orders.ShipAddress }; cmd.Parameters.Add(paramShipAddress); SqlParameter paramShipCity = new SqlParameter { ParameterName = "@ShipCity", Value = orders.ShipCity }; cmd.Parameters.Add(paramShipCity); SqlParameter paramShipRegion = new SqlParameter { ParameterName = "@ShipRegion", Value = orders.ShipRegion }; cmd.Parameters.Add(paramShipRegion); SqlParameter paramShipPostalCode = new SqlParameter { ParameterName = "@ShipPostalCode", Value = orders.ShipPostalCode }; cmd.Parameters.Add(paramShipPostalCode); SqlParameter paramShipCountry = new SqlParameter { ParameterName = "@ShipCountry", Value = orders.ShipCountry }; cmd.Parameters.Add(paramShipCountry); con.Open(); cmd.ExecuteNonQuery(); cmd.Dispose(); } } catch (Exception ex) { BusinessLayer.ExceptionLogging exlog = new BusinessLayer.ExceptionLogging(); exlog.SendExcepToDB(ex); //errResult = "A Technical Error occurred, Please visit after some time."; throw; } } catch (Exception fx) { errResult = fx.Message.ToString(); throw; } }