//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;
}
}