public IQueryable<Order> GetAll()
{
List<Order> lsor = new List<Order>();
DateTime date = DateTime.Today;
string sqlcm = "Select top 10 OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate, Shipvia, Freight from Orders";
using (IDataReader dr = ConnectionClass.GetInstance().ExecuteReader(sqlcm))
{
while (dr.Read())
{
Order or = new Order();
or.OrderID = dr.GetInt32(0);
or.CustomerID = dr.GetString(1);
or.OrderDate = dr.GetDateTime(2);
or.RequireDate = dr.GetDateTime(3);
if (dr.IsDBNull(4))
{
or.ShippedDate = null;
}
else
{
or.ShippedDate = dr.GetDateTime(4);
}
or.ShipVia = dr.GetInt32(5);
or.Freight = dr.GetDecimal(6);
lsor.Add(or);
}
dr.Close();
}
return lsor.AsQueryable();
}
public int UpdateOrder(Order or)
{
_Orderservice = new OrderService();
if (_Orderservice.Update(or) == 1)
return 1;
else
return 0;
}
public int AddOrder(Order or)
{
_Orderservice = new OrderService();
if (_Orderservice.Add(or) == 1)
return 1;
else
return 0;
}
public IQueryable<Order> GetOrderByDate(string cusid,DateTime fromdate, DateTime todate)
{
List<Order> lsor = new List<Order>();
string sqlcm = "select distinct OrderID, a.CustomerID, OrderDate, RequiredDate, ShippedDate, Shipvia, Freight from Orders a, Customers where a.CustomerID='"+ cusid +"' and a.OrderDate between '" + fromdate.ToString("yyyy-MM-dd") + "' and '" + todate.ToString("yyyy-MM-dd") + "'";
using (IDataReader dr = ConnectionClass.GetInstance().ExecuteReader(sqlcm))
{
while (dr.Read())
{
Order or = new Order();
or.OrderID = dr.GetInt32(0);
or.CustomerID = dr.GetString(1);
or.OrderDate = dr.GetDateTime(2);
or.RequireDate = dr.GetDateTime(3);
if (dr.IsDBNull(4))
{
or.ShippedDate = null;
}
else
{
or.ShippedDate = dr.GetDateTime(4);
}
or.ShipVia = dr.GetInt32(5);
or.Freight = dr.GetDecimal(6);
lsor.Add(or);
}
dr.Close();
}
return lsor.AsQueryable();
}
public int Update(Order or)
{
string sqlcm = "Update Orders set " +
"CustomerID='"+ or.CustomerID +"',OrderDate='"+ or.OrderDate.ToShortDateString() +
"',RequiredDate='"+ or.RequireDate.ToShortDateString() +"',ShippedDate='" + or.ShippedDate +
"',Shipvia=" + or.ShipVia + ",Freight=" + or.Freight + " where OrderID=" + or.OrderID;
return ConnectionClass.GetInstance().ExecuteNonQuery(sqlcm);
}
public int Add(Order or)
{
string shipped;
DateTime ship;
shipped = or.ShippedDate.ToString();
ship = DateTime.Parse(shipped);
string sqlcm = "insert into Orders(CustomerID,OrderDate,RequiredDate,ShippedDate,Shipvia,Freight) values('" + or.CustomerID + "','" + or.OrderDate.ToString("yyyy-MM-dd") + "','" + or.RequireDate.ToString("yyyy-MM-dd") + "','" + ship.ToString("yyyy-MM-dd") + "',3," + or.Freight + ")";
return ConnectionClass.GetInstance().ExecuteNonQuery(sqlcm);
}
public IQueryable<Order> LoadOrderBySP(string cusid, int numofrow,int numofnext)
{
List<Order> lsor = new List<Order>();
using (IDataReader dr = ConnectionClass.GetInstance().ExcuteSP("loadorder", cusid, numofrow, numofnext))
{
while (dr.Read())
{
Order or = new Order();
or.OrderID = dr.GetInt32(0);
or.CustomerID = dr.GetString(1);
or.OrderDate = dr.GetDateTime(2);
or.RequireDate = dr.GetDateTime(3);
if (dr.IsDBNull(4))
{
or.ShippedDate = null;
}
else
{
or.ShippedDate = dr.GetDateTime(4);
}
or.ShipVia = dr.GetInt32(5);
or.Freight = dr.GetDecimal(6);
lsor.Add(or);
}
dr.Close();
}
return lsor.AsQueryable();
}