///分页实现过程
public List <Book.Model.T_Base_Customer> GetList(int pageNumber, int pageSize)
{
List <Book.Model.T_Base_Customer> list = new List <Model.T_Base_Customer>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + pageSize + " * from t_base_Customer where Id not in(select top " + (pageNumber - 1) * pageSize + " Id from t_base_customer)";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Book.Model.T_Base_Customer item = new Model.T_Base_Customer();
item.Id = Convert.ToInt32(reader["Id"]);
item.CustomerName = Convert.ToString(reader["CustomerName"]);
item.CustomerPwd = Convert.ToString(reader["CustomerPwd"]);
item.CustomerPostbox = Convert.ToString(reader["CustomerPostbox"]);
item.CustomerTel = Convert.ToString(reader["CustomerTel"]);
list.Add(item);
}
reader.Close();
co.Close();
return(list);
}
public List <Book.Model.T_Base_Customer> GetAll()
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from t_base_customer";
SqlDataReader reader = cmd.ExecuteReader();
List <Book.Model.T_Base_Customer> list = new List <Model.T_Base_Customer>();
while (reader.Read())
{
Book.Model.T_Base_Customer user = new Model.T_Base_Customer();
user.Id = Convert.ToInt32(reader["Id"]);
user.CustomerName = Convert.ToString(reader["CustomerName"]);
user.CustomerPwd = Convert.ToString(reader["CustomerPwd"]);
user.CustomerPostbox = Convert.ToString(reader["CustomerPostbox"]);
user.CustomerTel = Convert.ToString(reader["CustomerTel"]);
list.Add(user);
}
reader.Close();
co.Close();
return(list);
}
public Model.T_Base_Customer Alter(int Id)
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from t_base_customer where Id = @Id";
cmd.Parameters.AddWithValue("@Id", Id);
SqlDataReader dr = cmd.ExecuteReader();
Book.Model.T_Base_Customer user = new Model.T_Base_Customer();
while (dr.Read())
{
user.Id = Convert.ToInt32(dr["Id"]);
user.CustomerName = Convert.ToString(dr["CustomerName"]);
user.CustomerPwd = Convert.ToString(dr["CustomerPwd"]);
user.CustomerPostbox = Convert.ToString(dr["CustomerPostbox"]);
user.CustomerTel = Convert.ToString(dr["CustomerTel"]);
}
dr.Close();
co.Close();
return(user);
}
public Book.Model.T_Base_Customer GetModal(int Id)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = "server=10.132.239.3;uid=sa;pwd=Jsj123456;database=15211160113";
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select * from T_Base_Customer where Id=@Id";
cm.Parameters.AddWithValue("@Id", Id);
SqlDataReader dr = cm.ExecuteReader();
Book.Model.T_Base_Customer Customer = new Model.T_Base_Customer();
while (dr.Read())
{
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"]);
}
dr.Close();
co.Close();
return(Customer);
}
public List <Book.Model.T_Base_Customer> GetList(int currentPage, int pageSize, String Name = "")
{
SqlConnection co = new SqlConnection();
co.ConnectionString = "server=10.132.239.3;uid=sa;pwd=Jsj123456;database=15211160113";
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select top " + pageSize + " * from t_base_Customer where id not in (select top " + pageSize * (currentPage - 1) + " id from t_base_Customer where Name like '%" + Name + "%') and Name like '%" + Name + "%'";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Base_Customer> lst = new List <Model.T_Base_Customer>();
while (dr.Read())
{
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.Memo = Convert.ToString(dr["Memo"]);
Customer.Fax = Convert.ToString(dr["Fax"]);
lst.Add(Customer);
}
dr.Close();
co.Close();
return(lst);
}
public List <Model.T_Base_Customer> GetSearch(string Name, int matchCount)
{
//throw new NotImplementedException();
SqlConnection co = new SqlConnection();
co.ConnectionString = "server=10.132.239.3;uid=sa;pwd=Jsj123456;database=15211160113";
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select top " + matchCount + " * from t_base_Customer where Name like '%" + Name + "%'";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Base_Customer> lst = new List <Model.T_Base_Customer>();
while (dr.Read())
{
Book.Model.T_Base_Customer item = new Model.T_Base_Customer();
item.Id = Convert.ToInt32(dr["Id"]);
item.Name = Convert.ToString(dr["Name"]);
item.Tel = Convert.ToString(dr["Tel"]);
item.Fax = Convert.ToString(dr["Fax"]);
item.Memo = Convert.ToString(dr["Memo"]);
lst.Add(item);
}
dr.Close();
co.Close();
return(lst);
}
public List <Book.Model.T_Base_Customer> GetAll()
{
SqlConnection co = new SqlConnection();
co.ConnectionString = "server=10.132.239.3;uid=sa;pwd=Jsj123456;database=15211160113";
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select * from t_base_Customer";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Base_Customer> lst = new List <Model.T_Base_Customer>();
while (dr.Read())
{
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.Memo = Convert.ToString(dr["Memo"]);
Customer.Fax = Convert.ToString(dr["Fax"]);
lst.Add(Customer);
}
dr.Close();
co.Close();
return(lst);
}
public ActionResult AddSave(string Name, string Tel, string Fax, string Memo)
{
Book.Model.T_Base_Customer Customer = new Model.T_Base_Customer();
Customer.Name = Name;
Customer.Tel = Tel;
Customer.Fax = Fax;
Customer.Memo = Memo;
Book.BLL.T_Base_Customer bll = new BLL.T_Base_Customer();
bll.Add(Customer);
return(Redirect("Index"));
}
public ActionResult AddSave(string CustomerName, string CustomerPwd,
string CustomerPostbox, string CustomerTel)
{
Book.Model.T_Base_Customer user = new Model.T_Base_Customer();
user.CustomerName = CustomerName;
user.CustomerPwd = CustomerPwd;
user.CustomerPostbox = CustomerPostbox;
user.CustomerTel = CustomerTel;
Book.BLL.T_Base_Customer userAddSave = new BLL.T_Base_Customer();
int result = userAddSave.AddSave(user);
return(Redirect("/Customer/Index"));
}
public ActionResult AlterSave(int Id, string CustomerName,
string CustomerPwd, string CustomerPostbox, string CustomerTel)
{
Book.BLL.T_Base_Customer bll = new BLL.T_Base_Customer();
Book.Model.T_Base_Customer alterCustomer = new Model.T_Base_Customer();
alterCustomer.Id = Id;
alterCustomer.CustomerName = CustomerName;
alterCustomer.CustomerPwd = CustomerPwd;
alterCustomer.CustomerPostbox = CustomerPostbox;
alterCustomer.CustomerTel = CustomerTel;
bll.AlterSave(alterCustomer);
return(RedirectToAction("Index"));
}
public void AlterSave(Model.T_Base_Customer alterCustomer)
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "update t_base_customer set CustomerName = @CustomerName," +
"CustomerPwd = @CustomerPwd,CustomerPostbox = @CustomerPostbox,CustomerTel = @CustomerTel" + " where Id = @Id";
cmd.Parameters.AddWithValue("@Id", alterCustomer.Id);
cmd.Parameters.AddWithValue("@CustomerName", alterCustomer.CustomerName);
cmd.Parameters.AddWithValue("@CustomerPwd", alterCustomer.CustomerPwd);
cmd.Parameters.AddWithValue("@CustomerPostbox", alterCustomer.CustomerPostbox);
cmd.Parameters.AddWithValue("@CustomerTel", alterCustomer.CustomerTel);
cmd.ExecuteNonQuery();
co.Close();
}
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 <Model.T_Base_Customer> GetSearch(string query, int mathCount)
{
List <Model.T_Base_Customer> list = new List <Model.T_Base_Customer>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + mathCount + " * from t_base_customer where CustomerName like '%" + query + "%' or CustomerTel like '%" + query + "%' or CustomerPostbox like '%" + query + "%'";
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
Model.T_Base_Customer user = new Model.T_Base_Customer();
user.Id = Convert.ToInt32(read["Id"]);
user.CustomerName = Convert.ToString(read["CustomerName"]);
user.CustomerPwd = Convert.ToString(read["CustomerPwd"]);
user.CustomerPostbox = Convert.ToString(read["CustomerPostbox"]);
user.CustomerTel = Convert.ToString(read["CustomerTel"]);
list.Add(user);
}
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);
}
public void AlterSave(Model.T_Base_Customer alterBook)
{
Book.DAL.T_Base_Customer dll = new DAL.T_Base_Customer();
dll.AlterSave(alterBook);
}
