public static List <Order> GetOrderList()
{
List <Order> OrderList = new List <Order>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
try
{
sql.Append("SELECT O.Id,OrderNumber,OrderDate, ");
sql.Append("C.FirstName + ' ' + C.LastName AS 'Customer Name',TotalAmount ");
sql.Append("FROM[dbo].[Order] AS O ");
sql.Append("JOIN Customer AS C ON(O.CustomerId = C.Id) ");
sql.Append("ORDER BY OrderNumber ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Order o = new Order();
o.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
o.OrderNumber = (dr.IsDBNull(1) ? null : dr.GetString(1));
o.OrderDate = (dr.IsDBNull(2) ? new DateTime() : dr.GetDateTime(2));
o.CustomerName = (dr.IsDBNull(3) ? null : dr.GetString(3));
o.TotalAmount = (dr.IsDBNull(4) ? 0.00m : dr.GetDecimal(4));
OrderList.Add(o);
}
return(OrderList);
}
catch (Exception ex)
{
throw new Exception("Errore durante il recupero degli Order", ex);
}
}
public static List <Product> GetProductList()
{
List <Product> ProductList = new List <Product>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
try
{
sql.Append("SELECT P.Id,P.ProductName,S.ContactName,P.UnitPrice,P.Package,P.IsDiscontinued ");
sql.Append("FROM Product AS P ");
sql.Append("JOIN Supplier AS S ON(P.SupplierId = S.Id) ");
sql.Append("ORDER BY P.ProductName ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Product p = new Product();
p.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
p.ProductName = (dr.IsDBNull(1) ? null : dr.GetString(1));
p.SupplierName = (dr.IsDBNull(2) ? null : dr.GetString(2));
p.UnitPrice = (dr.IsDBNull(3) ? 0.00m : dr.GetDecimal(3));
p.Package = (dr.IsDBNull(4) ? null : dr.GetString(4));
p.IsDiscontinued = (dr.IsDBNull(5) ? false : dr.GetBoolean(5));
ProductList.Add(p);
}
return(ProductList);
}
catch (Exception ex)
{
throw new Exception("Errore durante il recupero degli Product", ex);
}
}
public static List <Customer> GetCustomerList()
{
List <Customer> customerList = new List <Customer>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
try
{
sql.Append("SELECT Id,LastName,FirstName,City,Country,Phone ");
sql.Append("FROM Customer ");
sql.Append("ORDER BY LastName ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Customer c = new Customer();
c.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
c.Surname = (dr.IsDBNull(1) ? null : dr.GetString(1));
c.Name = (dr.IsDBNull(2) ? null : dr.GetString(2));
c.City = (dr.IsDBNull(3) ? null : dr.GetString(3));
c.Country = (dr.IsDBNull(4) ? null : dr.GetString(4));
c.Phone = (dr.IsDBNull(5) ? null : dr.GetString(5));
customerList.Add(c);
}
return(customerList);
}
catch (Exception ex)
{
throw new Exception("Errore durante il recupero dei Customer", ex);
}
}
public static List <OrderItem> GetOrderItemList()
{
List <OrderItem> OrderItemList = new List <OrderItem>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
try
{
sql.Append("SELECT OI.Id,P.ProductName,O.OrderNumber,OI.UnitPrice,OI.Quantity ");
sql.Append("FROM OrderItem AS OI ");
sql.Append("JOIN[dbo].[Order] AS O ON(OI.OrderId = O.Id) ");
sql.Append("JOIN Product AS P ON(OI.ProductId = P.Id) ");
sql.Append("ORDER BY ProductName ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
OrderItem o = new OrderItem();
o.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
o.ProductName = (dr.IsDBNull(1) ? null : dr.GetString(1));
o.OrderNumber = (dr.IsDBNull(2) ? null : dr.GetString(2));
o.UnitPrice = (dr.IsDBNull(3) ? 0.00m : dr.GetDecimal(3));
o.Quantity = (dr.IsDBNull(4) ? -1 : dr.GetInt32(4));
OrderItemList.Add(o);
}
return(OrderItemList);
}
catch (Exception ex)
{
throw new Exception("Errore durante il recupero degli OrderItem", ex);
}
}
public static List <Supplier> SearchSupplier(string suppName, string compName, string title, string city, string country, string phone, string fax)
{
List <Supplier> supplierList = new List <Supplier>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
suppName = "%" + suppName + "%";
compName = "%" + compName + "%";
title = "%" + title + "%";
city = "%" + city + "%";
country = "%" + country + "%";
phone = "%" + phone + "%";
fax = "%" + fax + "%";
try
{
sql.Append("SELECT Id,ContactName,CompanyName,ContactTitle,City,Country,Phone,Fax ");
sql.Append("FROM Supplier ");
sql.Append("WHERE ContactName LIKE @pSuppName AND CompanyName LIKE @pCompName ");
sql.Append("AND ISNULL(ContactTitle,'') LIKE @pTitle ");
sql.Append("AND City LIKE @pCity AND Country LIKE @pCountry AND Phone LIKE @pPhone ");
sql.Append("AND ISNULL(Fax, '') LIKE @pFax ");
sql.Append("ORDER BY ContactName ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
cmd.Parameters.Add(new SqlParameter("pSuppName", suppName));
cmd.Parameters.Add(new SqlParameter("pCompName", compName));
cmd.Parameters.Add(new SqlParameter("pTitle", title));
cmd.Parameters.Add(new SqlParameter("pCity", city));
cmd.Parameters.Add(new SqlParameter("pCountry", country));
cmd.Parameters.Add(new SqlParameter("pPhone", phone));
cmd.Parameters.Add(new SqlParameter("pFax", fax));
dr = cmd.ExecuteReader();
while (dr.Read())
{
Supplier s = new Supplier();
s.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
s.ContactName = (dr.IsDBNull(1) ? null : dr.GetString(1));
s.CompanyName = (dr.IsDBNull(2) ? null : dr.GetString(2));
s.ContactTitle = (dr.IsDBNull(3) ? null : dr.GetString(3));
s.City = (dr.IsDBNull(4) ? null : dr.GetString(4));
s.Country = (dr.IsDBNull(5) ? null : dr.GetString(5));
s.Phone = (dr.IsDBNull(6) ? null : dr.GetString(6));
s.Fax = (dr.IsDBNull(7) ? null : dr.GetString(7));
supplierList.Add(s);
}
return(supplierList);
}
catch (Exception ex)
{
throw new Exception("Errore durante la ricerca degli Supplier", ex);
}
}
public static List <Product> SearchProduct(string prodName, string suppName, string unitPrice, string pack, string isDiscont)
{
List <Product> ProductList = new List <Product>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
prodName = "%" + prodName + "%";
suppName = "%" + suppName + "%";
unitPrice = unitPrice + "%";
pack = "%" + pack + "%";
isDiscont = "%" + isDiscont + "%";
try
{
sql.Append("SELECT P.Id,P.ProductName,S.ContactName,P.UnitPrice,P.Package,P.IsDiscontinued ");
sql.Append("FROM Product AS P ");
sql.Append("JOIN Supplier AS S ON(P.SupplierId = S.Id) ");
sql.Append("WHERE P.ProductName LIKE @pProdName AND S.ContactName LIKE @pContName AND P.UnitPrice LIKE @pUnitPrice ");
sql.Append("AND P.Package LIKE @pPack AND P.IsDiscontinued LIKE @pIsDiscont ");
sql.Append("ORDER BY P.ProductName ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
cmd.Parameters.Add(new SqlParameter("pProdName", prodName));
cmd.Parameters.Add(new SqlParameter("pContName", suppName));
cmd.Parameters.Add(new SqlParameter("pUnitPrice", unitPrice));
cmd.Parameters.Add(new SqlParameter("pPack", pack));
cmd.Parameters.Add(new SqlParameter("pIsDiscont", isDiscont));
dr = cmd.ExecuteReader();
while (dr.Read())
{
Product p = new Product();
p.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
p.ProductName = (dr.IsDBNull(1) ? null : dr.GetString(1));
p.SupplierName = (dr.IsDBNull(2) ? null : dr.GetString(2));
p.UnitPrice = (dr.IsDBNull(3) ? 0.00m : dr.GetDecimal(3));
p.Package = (dr.IsDBNull(4) ? null : dr.GetString(4));
p.IsDiscontinued = (dr.IsDBNull(5) ? false : dr.GetBoolean(5));
ProductList.Add(p);
}
return(ProductList);
}
catch (Exception ex)
{
throw new Exception("Errore durante la ricerca degli Product", ex);
}
}
public static List <Order> SearchOrder(string ordNum, string ordDate, string custName, string amount)
{
List <Order> OrderList = new List <Order>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
ordNum = "%" + ordNum + "%";
ordDate = "%" + ordDate + "%";
custName = "%" + custName + "%";
amount = amount + "%";
try
{
sql.Append("SELECT O.Id,OrderNumber,OrderDate, ");
sql.Append("C.FirstName + ' ' + C.LastName AS 'Customer Name',TotalAmount ");
sql.Append("FROM[dbo].[Order] AS O ");
sql.Append("JOIN Customer AS C ON(O.CustomerId = C.Id) ");
sql.Append("WHERE OrderNumber LIKE @pOrdNum ");
sql.Append("AND C.FirstName + ' ' + C.LastName LIKE @pCustName AND TotalAmount LIKE @pAmount ");
sql.Append("AND OrderDate LIKE @pOrdDate ");
sql.Append("ORDER BY LastName ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
cmd.Parameters.Add(new SqlParameter("pOrdNum", ordNum));
cmd.Parameters.Add(new SqlParameter("pOrdDate", ordDate));
cmd.Parameters.Add(new SqlParameter("pCustName", custName));
cmd.Parameters.Add(new SqlParameter("pAmount", amount));
dr = cmd.ExecuteReader();
while (dr.Read())
{
Order o = new Order();
o.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
o.OrderNumber = (dr.IsDBNull(1) ? null : dr.GetString(1));
o.OrderDate = (dr.IsDBNull(2) ? new DateTime() : dr.GetDateTime(2));
o.CustomerName = (dr.IsDBNull(3) ? null : dr.GetString(3));
o.TotalAmount = (dr.IsDBNull(4) ? 0.00m : dr.GetDecimal(4));
OrderList.Add(o);
}
return(OrderList);
}
catch (Exception ex)
{
throw new Exception("Errore durante la ricerca degli Order", ex);
}
}
public static List <Customer> SearchCustomer(string surname, string name, string city, string country, string phone)
{
List <Customer> customerList = new List <Customer>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
name = "%" + name + "%";
surname = "%" + surname + "%";
city = "%" + city + "%";
country = "%" + country + "%";
phone = "%" + phone + "%";
try
{
sql.Append("SELECT Id,LastName,FirstName,City,Country,Phone ");
sql.Append("FROM Customer ");
sql.Append("WHERE LastName LIKE @pLastName AND FirstName LIKE @pFirstName ");
sql.Append("AND City LIKE @pCity AND Country LIKE @pCountry AND Phone LIKE @pPhone ");
sql.Append("ORDER BY LastName ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
cmd.Parameters.Add(new SqlParameter("pLastName", surname));
cmd.Parameters.Add(new SqlParameter("pFirstName", name));
cmd.Parameters.Add(new SqlParameter("pCity", city));
cmd.Parameters.Add(new SqlParameter("pCountry", country));
cmd.Parameters.Add(new SqlParameter("pPhone", phone));
dr = cmd.ExecuteReader();
while (dr.Read())
{
Customer c = new Customer();
c.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
c.Surname = (dr.IsDBNull(1) ? null : dr.GetString(1));
c.Name = (dr.IsDBNull(2) ? null : dr.GetString(2));
c.City = (dr.IsDBNull(3) ? null : dr.GetString(3));
c.Country = (dr.IsDBNull(4) ? null : dr.GetString(4));
c.Phone = (dr.IsDBNull(5) ? null : dr.GetString(5));
customerList.Add(c);
}
return(customerList);
}
catch (Exception ex)
{
throw new Exception("Errore durante la ricerca dei Customer", ex);
}
}
public static List <OrderItem> SearchOrderItem(string prodName, string ordNum, string unitPrice, string qnt)
{
List <OrderItem> OrderItemList = new List <OrderItem>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
prodName = "%" + prodName + "%";
ordNum = ordNum + "%";
unitPrice = unitPrice + "%";
qnt = qnt + "%";
try
{
sql.Append("SELECT OI.Id,P.ProductName,O.OrderNumber,OI.UnitPrice,OI.Quantity ");
sql.Append("FROM OrderItem AS OI ");
sql.Append("JOIN[dbo].[Order] AS O ON(OI.OrderId = O.Id) ");
sql.Append("JOIN Product AS P ON(OI.ProductId = P.Id) ");
sql.Append("WHERE P.ProductName LIKE @pProdName AND O.OrderNumber LIKE @pOrdNum AND OI.UnitPrice LIKE @pUnitPrice AND OI.Quantity LIKE @pQnt ");
sql.Append("ORDER BY ProductName ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
cmd.Parameters.Add(new SqlParameter("pProdName", prodName));
cmd.Parameters.Add(new SqlParameter("pOrdNum", ordNum));
cmd.Parameters.Add(new SqlParameter("pUnitPrice", unitPrice));
cmd.Parameters.Add(new SqlParameter("pQnt", qnt));
dr = cmd.ExecuteReader();
while (dr.Read())
{
OrderItem o = new OrderItem();
o.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
o.ProductName = (dr.IsDBNull(1) ? null : dr.GetString(1));
o.OrderNumber = (dr.IsDBNull(2) ? null : dr.GetString(2));
o.UnitPrice = (dr.IsDBNull(3) ? 0.00m : dr.GetDecimal(3));
o.Quantity = (dr.IsDBNull(4) ? -1 : dr.GetInt32(4));
OrderItemList.Add(o);
}
return(OrderItemList);
}
catch (Exception ex)
{
throw new Exception("Errore durante la ricerca degli OrderItem", ex);
}
}
public static List <Supplier> GetSupplierList()
{
List <Supplier> supplierList = new List <Supplier>();
SqlConnection cn = BaseDAO.GetConnection();
SqlDataReader dr = null;
StringBuilder sql = new StringBuilder();
try
{
sql.Append("SELECT Id,ContactName,CompanyName,ContactTitle,City,Country,Phone,Fax ");
sql.Append("FROM Supplier ");
sql.Append("ORDER BY ContactName ASC ");
SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
Supplier s = new Supplier();
s.Id = (dr.IsDBNull(0) ? -1 : dr.GetInt32(0));
s.ContactName = (dr.IsDBNull(1) ? null : dr.GetString(1));
s.CompanyName = (dr.IsDBNull(2) ? null : dr.GetString(2));
s.ContactTitle = (dr.IsDBNull(3) ? null : dr.GetString(3));
s.City = (dr.IsDBNull(4) ? null : dr.GetString(4));
s.Country = (dr.IsDBNull(5) ? null : dr.GetString(5));
s.Phone = (dr.IsDBNull(6) ? null : dr.GetString(6));
s.Fax = (dr.IsDBNull(7) ? null : dr.GetString(7));
supplierList.Add(s);
}
return(supplierList);
}
catch (Exception ex)
{
throw new Exception("Errore durante il recupero degli Supplier", ex);
}
}