/// <summary>
/// 根据表头获取相应的对象
/// </summary>
/// <param name="Id">图书的Id</param>
/// <returns></returns>
public Model.T_Stock_OutHead GetHead(int Id)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = connstring;
co.Open();
Book.Model.T_Stock_OutHead item = new Model.T_Stock_OutHead();
SqlCommand cm = new SqlCommand();
cm.CommandText = "select * from V_OutHead_Customer where Id = @headid";
cm.Parameters.AddWithValue("@headid", Id);
cm.Connection = co;
item.Customer = new Model.T_Base_Customer();
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
item.Id = Id;
item.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
item.CustomerId = Convert.ToInt32(dr["CustomerId"]);
item.UserName = Convert.ToString(dr["UserName"]);
item.Customer.Name = Convert.ToString(dr["Name"]);
}
dr.Close();
co.Close();
return(item);
}
public List <Book.Model.T_Stock_Out> GetList(int CurrentPage, int PageSize, String search = "")
{
SqlConnection co = new SqlConnection();
co.ConnectionString = connstring;
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
search = "'%" + search + "%'";
cm.CommandText = "select top " + PageSize + " * from [V_OutHead_Customer] where id not in (select top " + PageSize * (CurrentPage - 1) + " id from [V_OutHead_Customer] where (username like " + search + " or name like " + search + ")) and (username like " + search + " or name like " + search + ")";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Stock_Out> lst = new List <Model.T_Stock_Out>();
while (dr.Read())
{
Book.Model.T_Stock_Out outStock = new Model.T_Stock_Out();
outStock.Customer = new Model.T_Base_Customer();
Book.Model.T_Stock_OutHead head = new Model.T_Stock_OutHead();
outStock.Id = head.Id = Convert.ToInt32(dr["Id"]);
head.Customer = new Model.T_Base_Customer();
outStock.CreateTime = head.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
outStock.Customer.Id = head.Customer.Id = Convert.ToInt32(dr["CustomerId"]);
outStock.TotalMoney = head.TotalMoney = Convert.ToDecimal(dr["TotalMoney"]);
outStock.UserName = head.UserName = Convert.ToString(dr["UserName"]);
Book.Model.T_Base_Customer Customer = new Model.T_Base_Customer();
Customer.Id = Convert.ToInt32(dr["Id"]);
Customer.Name = Convert.ToString(dr["Name"]);
Customer.Tel = Convert.ToString(dr["Tel"]);
Customer.Fax = Convert.ToString(dr["Fax"]);
Customer.Memo = Convert.ToString(dr["Memo"]);
head.Customer = Customer;
outStock.Head = head;
outStock.Items = null;
lst.Add(outStock);
}
dr.Close();
co.Close();
return(lst);
}
///分页实现过程
public List <Book.Model.T_Stock_Out> GetList(int pageNumber, int pageSize)
{
List <Book.Model.T_Stock_Out> list = new List <Model.T_Stock_Out>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + pageSize + " * from v_outhead_customer where Id not in(select top " + (pageNumber - 1) * pageSize + " Id from v_outhead_customer)";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Book.Model.T_Stock_Out item = new Model.T_Stock_Out();
Book.Model.T_Stock_OutHead head = new Model.T_Stock_OutHead();
Book.Model.T_Base_Customer customer = new Model.T_Base_Customer();
head.Id = Convert.ToInt32(reader["Id"]);
head.UserName = Convert.ToString(reader["UserName"]);
head.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
head.TotalMoney = Convert.ToDecimal(reader["TotalMoney"]);
head.CustomerId = Convert.ToInt32(reader["CustomerId"]);
customer.Id = Convert.ToInt32(reader["CustomerId"]);
customer.CustomerName = Convert.ToString(reader["CustomerName"]);
customer.CustomerTel = Convert.ToString(reader["CustomerTel"]);
customer.CustomerPwd = Convert.ToString(reader["CustomerPwd"]);
customer.CustomerPostbox = Convert.ToString(reader["CustomerPostbox"]);
head.Customer = customer;
item.Head = head;
item.Items = null;
list.Add(item);
}
reader.Close();
co.Close();
return(list);
}
//查询全部
public List <Book.Model.T_Stock_Out> GetAll()
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from V_InHead_Customer";
SqlDataReader reader = cmd.ExecuteReader();
List <Book.Model.T_Stock_Out> list = new List <Model.T_Stock_Out>();
while (reader.Read())
{
Book.Model.T_Stock_Out item = new Model.T_Stock_Out();
Book.Model.T_Stock_OutHead head = new Model.T_Stock_OutHead();
Book.Model.T_Base_Customer customer = new Model.T_Base_Customer();
head.Id = Convert.ToInt32(reader["Id"]);
head.UserName = Convert.ToString(reader["UserName"]);
head.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
head.TotalMoney = Convert.ToDecimal(reader["TotalMoney"]);
head.CustomerId = Convert.ToInt32(reader["CustomerId"]);
customer.Id = Convert.ToInt32(reader["CustomerId"]);
customer.CustomerName = Convert.ToString(reader["CustomerName"]);
customer.CustomerTel = Convert.ToString(reader["CustomerTel"]);
customer.CustomerPwd = Convert.ToString(reader["CustomerPwd"]);
customer.CustomerPostbox = Convert.ToString(reader["CustomerPostbox"]);
head.Customer = customer;
item.Head = head;
item.Items = null;
list.Add(item);
}
reader.Close();
co.Close();
return(list);
}
