public Model.T_Base_Book Alter(int Id)
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from t_base_book where Id = @Id";
cmd.Parameters.AddWithValue("@Id", Id);
SqlDataReader dr = cmd.ExecuteReader();
Book.Model.T_Base_Book book = new Model.T_Base_Book();
while (dr.Read())
{
book.id = Convert.ToInt32(dr["Id"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.Author = Convert.ToString(dr["Author"]);
book.ISBN = Convert.ToString(dr["ISBN"]);
book.PressName = Convert.ToString(dr["PressName"]);
book.Version = Convert.ToInt32(dr["Version"]);
book.Price = Convert.ToDecimal(dr["Price"]);
}
dr.Close();
co.Close();
return book;
}
//子表数据查询
public List <Book.Model.T_Stock_OutItems> GetModel(int headId)
{
List <Book.Model.T_Stock_OutItems> list = new List <Model.T_Stock_OutItems>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from v_outItem_book where HeadId = " + headId;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Book.Model.T_Stock_OutItems item = new Model.T_Stock_OutItems();
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.id = Convert.ToInt32(reader["BookId"]);
book.BookName = Convert.ToString(reader["BookName"]);
book.Author = Convert.ToString(reader["Author"]);
book.PressName = Convert.ToString(reader["PressName"]);
book.ISBN = Convert.ToString(reader["ISBN"]);
book.Price = Convert.ToDecimal(reader["Price"]);
book.Version = Convert.ToInt32(reader["Version"]);
item.Book = book;
item.HeadId = headId;
item.Discount = Convert.ToDecimal(reader["Discount"]);
item.Amount = Convert.ToInt32(reader["Amount"]);
list.Add(item);
}
co.Close();
return(list);
}
public Book.Model.T_Base_Book 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_Book where Id=@Id";
cm.Parameters.AddWithValue("@Id", Id);
SqlDataReader dr = cm.ExecuteReader();
Book.Model.T_Base_Book book = new Model.T_Base_Book();
while (dr.Read())
{
book.Id = Convert.ToInt32(dr["Id"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.PressName = Convert.ToString(dr["PressName"]);
book.Author = Convert.ToString(dr["Author"]);
book.Version = Convert.ToInt32(dr["Version"]);
book.SN = Convert.ToString(dr["SN"]);
book.Price = Convert.ToDecimal(dr["Price"]);
}
dr.Close();
co.Close();
return(book);
}
public List <Model.T_Base_Book> GetFind(string SN)
{
//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 * from t_base_book WHERE id = " + SN + "";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Base_Book> lst = new List <Model.T_Base_Book>();
while (dr.Read())
{
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.Id = Convert.ToInt32(dr["Id"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.PressName = Convert.ToString(dr["PressName"]);
book.Author = Convert.ToString(dr["Author"]);
book.Version = Convert.ToInt32(dr["Version"]);
book.SN = Convert.ToString(dr["SN"]);
book.Price = Convert.ToDecimal(dr["Price"]);
lst.Add(book);
}
dr.Close();
co.Close();
return(lst);
}
public List <Book.Model.T_Base_Book> GetList(int currentPage, int pageSize, String BookName = "", String Author = "")
{
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_book where id not in (select top " + pageSize * (currentPage - 1) + " id from t_base_book where BookName like \'%" + BookName + "%\' and Author like \'%" + Author + "%\') and BookName like \'%" + BookName + "%\' and Author like \'%" + Author + "%\'";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Base_Book> lst = new List <Model.T_Base_Book>();
while (dr.Read())
{
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.Id = Convert.ToInt32(dr["Id"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.PressName = Convert.ToString(dr["PressName"]);
book.Author = Convert.ToString(dr["Author"]);
book.Version = Convert.ToInt32(dr["Version"]);
book.SN = Convert.ToString(dr["SN"]);
book.Price = Convert.ToDecimal(dr["Price"]);
lst.Add(book);
}
dr.Close();
co.Close();
return(lst);
}
public List <Model.T_Base_Book> GetSearch2(string SN)
{
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 t_base_book.* from t_base_book,t_stock_report WHERE sn like '%" + SN + "%' and t_base_book.id = t_stock_report.id";
SqlDataReader dr = cm.ExecuteReader();
List <Book.Model.T_Base_Book> lst = new List <Model.T_Base_Book>();
while (dr.Read())
{
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.Id = Convert.ToInt32(dr["Id"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.PressName = Convert.ToString(dr["PressName"]);
book.Author = Convert.ToString(dr["Author"]);
book.Version = Convert.ToInt32(dr["Version"]);
book.SN = Convert.ToString(dr["SN"]);
book.Price = Convert.ToDecimal(dr["Price"]);
lst.Add(book);
}
dr.Close();
co.Close();
return(lst);
}
public List <book.Model.T_Base_Book> GetListByPageIndex(int CurrentPageIndex, int PageSize, string where)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = System.Configuration.ConfigurationManager.AppSettings["sqlconnection"];
co.Open();
SqlCommand cm = new SqlCommand();
cm.Connection = co;
cm.CommandText = "select top " + PageSize + " * from t_base_book where id not in ( select top " + PageSize * (CurrentPageIndex - 1) + " id from T_Base_Book where " + where + ") and " + where;
SqlDataReader dr = cm.ExecuteReader();
List <book.Model.T_Base_Book> lst = new List <Model.T_Base_Book>();
while (dr.Read())
{
#region 模型转换
book.Model.T_Base_Book book = new Model.T_Base_Book();
book.Id = Convert.ToInt32(dr["Id"]);
book.Code = Convert.ToString(dr["Code"]);
book.Author = Convert.ToString(dr["Author"]);
book.Price = Convert.ToDecimal(dr["Price"]);
book.PYear = Convert.ToDateTime(dr["PYear"]);
book.Version = Convert.ToString(dr["Version"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.Pic = Convert.ToString(dr["Pic"]);
#endregion
lst.Add(book);
}
co.Close();
return(lst);
}
public book.Model.T_Base_Book getModelById(int id)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = "server=10.132.239.215;uid=14jb;pwd=14jb;database=14211160205_book";
co.Open();
SqlCommand cm = new SqlCommand();
cm.CommandText = "select * from t_base_book where id =@id";
cm.Parameters.AddWithValue("@id", id);
cm.Connection = co;
SqlDataReader dr = cm.ExecuteReader();
book.Model.T_Base_Book book = null;
while (dr.Read())
{
book = new Model.T_Base_Book();
book.Id = Convert.ToInt32(dr["Id"]);
book.Code = Convert.ToString(dr["Code"]);
book.Author = Convert.ToString(dr["Author"]);
book.Price = Convert.ToDecimal(dr["Price"]);
book.PYear = Convert.ToDateTime(dr["PYear"]);
book.Version = Convert.ToString(dr["Version"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.Pic = Convert.ToString(dr["Pic"]);
}
co.Close();
dr.Close();
return(book);
}
public ActionResult AddSave(string BookName, string Author,
string ISBN, string PressName, int Version, decimal Price)
{
//图片保存
var file = Request.Files["ImgFile"];
string path = Server.MapPath("\\upLoad\\");
string fileName = Guid.NewGuid().ToString() + ".jpg";
file.SaveAs(path + fileName);
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.BookName = BookName;
book.Author = Author;
book.ISBN = ISBN;
book.PressName = PressName;
book.Version = Version;
book.Price = Price;
book.Img = fileName;
Book.BLL.T_Base_Book bookAddSave = new BLL.T_Base_Book();
int result = bookAddSave.AddSave(book);
return(Redirect("/Book/Index"));
}
public List <book.Model.T_Base_Book> GetList()
{
SqlConnection co = new SqlConnection();
co.ConnectionString = "server=10.132.239.215;uid=14jb;pwd=14jb;database=14211160205_book";
co.Open();
SqlCommand cm = new SqlCommand();
cm.CommandText = "select * from t_base_book";
cm.Connection = co;
SqlDataReader dr = cm.ExecuteReader();
List <book.Model.T_Base_Book> lst = new List <Model.T_Base_Book>();
while (dr.Read())
{
book.Model.T_Base_Book book = new Model.T_Base_Book();
book.Id = Convert.ToInt32(dr["Id"]);
book.Code = Convert.ToString(dr["Code"]);
book.Author = Convert.ToString(dr["Author"]);
book.Price = Convert.ToDecimal(dr["Price"]);
book.PYear = Convert.ToDateTime(dr["PYear"]);
book.Version = Convert.ToString(dr["Version"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.Pic = Convert.ToString(dr["Pic"]);
lst.Add(book);
}
dr.Close();
co.Close();
return(lst);
}
public List<Book.Model.T_Base_Book> GetAll()
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select * from t_base_book";
SqlDataReader dr = cmd.ExecuteReader();
List<Book.Model.T_Base_Book> list = new List<Model.T_Base_Book>();
while (dr.Read())
{
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.id = Convert.ToInt32(dr["Id"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.Author = Convert.ToString(dr["Author"]);
book.ISBN = Convert.ToString(dr["ISBN"]);
book.PressName = Convert.ToString(dr["PressName"]);
book.Version = Convert.ToInt32(dr["Version"]);
book.Price = Convert.ToDecimal(dr["Price"]);
list.Add(book);
}
dr.Close();
co.Close();
return list;
}
///分页实现过程
public List<Book.Model.T_Base_Book> GetList(int PageNumber, int PageSize, string Query)
{
List<Book.Model.T_Base_Book> list = new List<Model.T_Base_Book>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + PageSize + " * from t_base_book where Id not in(select top " + (PageNumber - 1) * PageSize + " Id from t_base_book "+
"where BookName like '%"+Query+"%' or Author like '%"+Query+"%' or ISBN like '%"+Query+ "%') and (BookName like '%"+Query+"%' or Author like '%"+Query+"%' or ISBN like '%"+Query+"%')";
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.id = Convert.ToInt32(reader["Id"]);
book.BookName = Convert.ToString(reader["BookName"]);
book.Author = Convert.ToString(reader["Author"]);
book.ISBN = Convert.ToString(reader["ISBN"]);
book.PressName = Convert.ToString(reader["PressName"]);
book.Version = Convert.ToInt32(reader["Version"]);
book.Price = Convert.ToDecimal(reader["Price"]);
book.Img = Convert.ToString(reader["Img"]);
list.Add(book);
}
reader.Close();
co.Close();
return list;
}
public List<Model.T_Base_Book> GetSearch(string query, int mathCount)
{
List<Model.T_Base_Book> list = new List<Model.T_Base_Book>();
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "select top " + mathCount + " * from t_base_book where BookName like '%"
+ query + "%' or ISBN like '%"+ query + "%' or Author like '%" + query + "%'";
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
Model.T_Base_Book book = new Model.T_Base_Book();
book.id = Convert.ToInt32(read["Id"]);
book.BookName = Convert.ToString(read["BookName"]);
book.Author = Convert.ToString(read["Author"]);
book.ISBN = Convert.ToString(read["ISBN"]);
book.PressName = Convert.ToString(read["PressName"]);
book.Version = Convert.ToInt32(read["Version"]);
book.Price = Convert.ToDecimal(read["Price"]);
book.Img = Convert.ToString(read["Img"]);
list.Add(book);
}
co.Close();
return list;
}
public ActionResult Update(int id)
{
DAL.DalT_Base_Book dal = new DAL.DalT_Base_Book();
book.Model.T_Base_Book book = new Model.T_Base_Book();
book = dal.getModelById(id);
ViewBag.book = book;
return(View());
}
public ActionResult AddSave(string BookName, string Author, string PressName, string SN, int Version, decimal Price)
{
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.Author = Author;
book.BookName = BookName;
book.PressName = PressName;
book.Price = Price;
book.SN = SN;
book.Version = Version;
Book.BLL.T_Base_Book bll = new BLL.T_Base_Book();
bll.Add(book);
return(Redirect("Index"));
}
public void AlterSave(Model.T_Base_Book book)
{
SqlConnection co = SQLServerOpen();
SqlCommand cmd = new SqlCommand();
cmd.Connection = co;
cmd.CommandText = "update t_base_book set BookName = @BookName," +
"Author = @Author,ISBN = @ISBN,PressName = @PressName," +
"Price = @Price,Img=@Img" + " where Id = @Id";
cmd.Parameters.AddWithValue("@Id", book.id);
cmd.Parameters.AddWithValue("@BookName", book.BookName);
cmd.Parameters.AddWithValue("@Author", book.Author);
cmd.Parameters.AddWithValue("@ISBN", book.ISBN);
cmd.Parameters.AddWithValue("@PressName", book.PressName);
cmd.Parameters.AddWithValue("@Version", book.Version);
cmd.Parameters.AddWithValue("@Price", book.Price);
cmd.Parameters.AddWithValue("@Img", book.Img);
cmd.ExecuteNonQuery();
co.Close();
}
public Book.Model.T_Stock_In GetModel(int HeadId)
{
SqlConnection co = new SqlConnection();
co.ConnectionString = connstring;
co.Open();
Book.Model.T_Stock_In stockIn = new Model.T_Stock_In();
//stockIn.Head.Id = HeadId;
stockIn.Head = null;
stockIn.Items = new List <Model.T_Stock_InItems>();
SqlCommand cm = new SqlCommand();
cm.CommandText = "select * from V_InItem_Book where headid = @headid";
cm.Parameters.AddWithValue("@headid", HeadId);
cm.Connection = co;
SqlDataReader dr = cm.ExecuteReader();
while (dr.Read())
{
Book.Model.T_Stock_InItems item = new Model.T_Stock_InItems();
item.HeadId = HeadId;
item.Id = Convert.ToInt32(dr["Id"]);
item.Discount = Convert.ToDecimal(dr["Discount"]);
Book.Model.T_Base_Book book = new Model.T_Base_Book();
book.Author = Convert.ToString(dr["Author"]);
book.BookName = Convert.ToString(dr["BookName"]);
book.Id = Convert.ToInt32(dr["BookId"]);
book.PressName = Convert.ToString(dr["PressName"]);
book.Price = Convert.ToDecimal(dr["Price"]);
book.SN = Convert.ToString(dr["SN"]);
book.Version = Convert.ToInt32(dr["Version"]);
item.BookId = book.Id;
item.Book = book;
item.Amount = Convert.ToInt32(dr["Amount"]);
stockIn.Items.Add(item);
}
dr.Close();
co.Close();
return(stockIn);
}
public void AlterSave(Model.T_Base_Book alterBook)
{
Book.DAL.T_Base_Book dll = new DAL.T_Base_Book();
dll.AlterSave(alterBook);
}
