public Book.Model.T_Base_Provider 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_Provider where Id=@Id";
cm.Parameters.AddWithValue("@Id", Id);
SqlDataReader dr = cm.ExecuteReader();
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
while (dr.Read())
{
provider.Id = Convert.ToInt32(dr["Id"]);
provider.Name = Convert.ToString(dr["Name"]);
provider.Tel = Convert.ToString(dr["Tel"]);
provider.Fax = Convert.ToString(dr["Fax"]);
provider.Memo = Convert.ToString(dr["Memo"]);
}
dr.Close();
co.Close();
return(provider);
}
//查询全部
public List <Book.Model.T_Stock_In> GetAll()
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from V_InHead_Provider";
SqlDataReader reader = cmd.ExecuteReader();
List <Book.Model.T_Stock_In> list = new List <Model.T_Stock_In>();
while (reader.Read())
{
Book.Model.T_Stock_In item = new Model.T_Stock_In();
Book.Model.T_Stock_InHead head = new Model.T_Stock_InHead();
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
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.ProviderId = Convert.ToInt32(reader["ProviderId"]);
provider.Id = Convert.ToInt32(reader["ProviderId"]);
provider.ProviderName = Convert.ToString(reader["ProviderName"]);
provider.ProviderTel = Convert.ToString(reader["ProviderTel"]);
provider.Memo = Convert.ToString(reader["Memo"]);
head.Provider = provider;
item.Head = head;
item.Items = null;
list.Add(item);
}
reader.Close();
co.Close();
return(list);
}
public List <Book.Model.T_Base_Provider> 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_provider";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Base_Provider> lst = new List <Model.T_Base_Provider>();
while (dr.Read())
{
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
provider.Id = Convert.ToInt32(dr["Id"]);
provider.Name = Convert.ToString(dr["Name"]);
provider.Tel = Convert.ToString(dr["Tel"]);
provider.Memo = Convert.ToString(dr["Memo"]);
provider.Fax = Convert.ToString(dr["Fax"]);
lst.Add(provider);
}
dr.Close();
co.Close();
return(lst);
}
///分页实现过程
public List <Book.Model.T_Base_Provider> GetList(int PageNumber, int PageSize)
{
List <Book.Model.T_Base_Provider> list = new List <Model.T_Base_Provider>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + PageSize + " * from t_base_provider where Id not in(select top " + (PageNumber - 1) * PageSize + " Id from t_base_provider)";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
provider.Id = Convert.ToInt32(reader["Id"]);
provider.ProviderName = Convert.ToString(reader["ProviderName"]);
provider.ProviderTel = Convert.ToString(reader["ProviderTel"]);
provider.Memo = Convert.ToString(reader["Memo"]);
list.Add(provider);
}
reader.Close();
co.Close();
return(list);
}
public List <Book.Model.T_Base_Provider> GetList(int CurrentPage, int PageSize)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = "server=10.132.239.3;uid=sa;pwd=Jsj123456;database=152111160209";
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select top " + PageSize + " * from t_base_provider where id not in (select top " + PageSize * (CurrentPage - 1) + " id from t_base_provider)";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Base_Provider> lst = new List <Model.T_Base_Provider> ();
while (dr.Read())
{
Book.Model.T_Base_Provider item = new Model.T_Base_Provider();
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_Provider> GetSearch(string Name, int matchCount)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = "server=10.132.239.3;uid=sa;pwd=Jsj123456;database=152111160209";
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select top " + matchCount + " * from t_base_provider where Name like '%" + Name + "%'";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Base_Provider> lst = new List <Model.T_Base_Provider>();
while (dr.Read())
{
Book.Model.T_Base_Provider item = new Model.T_Base_Provider();
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 ActionResult AddSave(string Name, string Tel, string Fax, string Memo)
{
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
provider.Name = Name;
provider.Tel = Tel;
provider.Fax = Fax;
provider.Memo = Memo;
Book.BLL.T_Base_Provider bll = new BLL.T_Base_Provider();
bll.Add(provider);
return(Redirect("Index"));
}
public ActionResult AlterSave(int Id, string ProviderName, string ProviderTel, string Memo)
{
Book.BLL.T_Base_Provider bll = new BLL.T_Base_Provider();
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
provider.Id = Id;
provider.ProviderName = ProviderName;
provider.ProviderTel = ProviderTel;
provider.Memo = Memo;
bll.AlterSave(provider);
return(RedirectToAction("Index"));
}
public ActionResult AddSave(string ProviderName, string ProviderTel, string Memo)
{
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
provider.ProviderName = ProviderName;
provider.ProviderTel = ProviderTel;
provider.Memo = Memo;
Book.BLL.T_Base_Provider bll = new BLL.T_Base_Provider();
int result = bll.AddSave(provider);
return(Redirect("/Provider/Index"));
}
public ActionResult Update(int id)
{
DAL.T_Base_Provider dal = new DAL.T_Base_Provider();
book.Model.T_Base_Provider book = new Model.T_Base_Provider();
book = dal.GetModel(id);
ViewBag.book = book;
if (book == null)
{
return(Content("资料不存在"));
}
return(View());
}
public ActionResult AddSave(string Name, string Tel, string Fax, string Memo)
{
Book.Model.T_Base_Provider item = new Model.T_Base_Provider();
item.Name = Name;
item.Tel = Tel;
item.Fax = Fax;
item.Memo = Memo;
Book.BLL.T_Base_Provider bll = new BLL.T_Base_Provider();
int result = bll.Add(item);
return(Redirect("/Provider/Index"));
}
public void AlterSave(Model.T_Base_Provider provider)
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "update t_base_provider set ProviderName = @ProviderName," +
"ProviderTel = @ProviderTel,Memo = @Memo where Id = @Id";
cmd.Parameters.AddWithValue("@Id", provider.Id);
cmd.Parameters.AddWithValue("@ProviderName", provider.ProviderName);
cmd.Parameters.AddWithValue("@ProviderTel", provider.ProviderTel);
cmd.Parameters.AddWithValue("@Memo", provider.Memo);
cmd.ExecuteNonQuery();
co.Close();
}
public List <Book.Model.T_Stock_In> GetList(int CurrentPage, int PageSize)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = connstring;
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select top " + PageSize + " * from [V_InHead_Provider] where id not in (select top " + PageSize * (CurrentPage - 1) + " id from [V_InHead_Provider] )";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Stock_In> lst = new List <Model.T_Stock_In>();
while (dr.Read())
{
Book.Model.T_Stock_In inStock = new Model.T_Stock_In();
Book.Model.T_Stock_InHead head = new Model.T_Stock_InHead();
inStock.Id = Convert.ToInt32(dr["Id"]);
inStock.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
inStock.ProviderId = Convert.ToInt32(dr["ProviderId"]);
inStock.TotalMoney = Convert.ToDecimal(dr["TotalMoney"]);
inStock.UserName = Convert.ToString(dr["UserName"]);
head.Id = Convert.ToInt32(dr["Id"]);
head.CreateTime = Convert.ToDateTime(dr["CreateTime"]);
head.ProviderId = Convert.ToInt32(dr["ProviderId"]);
head.TotalMoney = Convert.ToDecimal(dr["TotalMoney"]);
head.UserName = Convert.ToString(dr["UserName"]);
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
provider.Id = Convert.ToInt32(dr["Id"]);
provider.Name = Convert.ToString(dr["Name"]);
provider.Tel = Convert.ToString(dr["Tel"]);
provider.Fax = Convert.ToString(dr["Fax"]);
provider.Memo = Convert.ToString(dr["Memo"]);
head.Provider = provider;
inStock.Head = head;
inStock.Items = null;
lst.Add(inStock);
}
dr.Close();
co.Close();
return(lst);
}
///分页实现过程
public List <Book.Model.T_Stock_In> GetList(int pageNumber, int pageSize)
{
List <Book.Model.T_Stock_In> list = new List <Model.T_Stock_In>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + pageSize + " * from v_InHead_Provider where Id not in(select top " + (pageNumber - 1) * pageSize + " Id from v_InHead_Provider)";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Book.Model.T_Stock_In item = new Model.T_Stock_In();
Book.Model.T_Stock_InHead head = new Model.T_Stock_InHead();
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
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.ProviderId = Convert.ToInt32(reader["ProviderId"]);
provider.Id = Convert.ToInt32(reader["ProviderId"]);
provider.ProviderName = Convert.ToString(reader["ProviderName"]);
provider.ProviderTel = Convert.ToString(reader["ProviderTel"]);
provider.Memo = Convert.ToString(reader["Memo"]);
head.Provider = provider;
item.Head = head;
item.Items = null;
list.Add(item);
}
reader.Close();
co.Close();
int result = GetCount();
return(list);
}
public List <Model.T_Base_Provider> GetSearch(string query, int mathCount)
{
List <Model.T_Base_Provider> list = new List <Model.T_Base_Provider>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + mathCount + " * from t_base_provider where ProviderName like '%" + query + "%' or ProviderTel like '%" + query + "%'";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Model.T_Base_Provider provider = new Model.T_Base_Provider();
provider.Id = Convert.ToInt32(reader["Id"]);
provider.ProviderName = Convert.ToString(reader["ProviderName"]);
provider.ProviderTel = Convert.ToString(reader["ProviderTel"]);
provider.Memo = Convert.ToString(reader["Memo"]);
list.Add(provider);
}
co.Close();
return(list);
}
public Model.T_Base_Provider Alter(int Id)
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from t_base_provider where Id = @Id";
cmd.Parameters.AddWithValue("@Id", Id);
SqlDataReader reader = cmd.ExecuteReader();
Book.Model.T_Base_Provider provider = new Model.T_Base_Provider();
while (reader.Read())
{
provider.Id = Convert.ToInt32(reader["Id"]);
provider.ProviderName = Convert.ToString(reader["ProviderName"]);
provider.ProviderTel = Convert.ToString(reader["ProviderTel"]);
provider.Memo = Convert.ToString(reader["Memo"]);
}
reader.Close();
co.Close();
return(provider);
}
public void AlterSave(Model.T_Base_Provider provider)
{
Book.DAL.T_Base_Provider dll = new DAL.T_Base_Provider();
dll.AlterSave(provider);
}
