public static bool UpdateBook_lend(string bookid, string bookCount)
{
StringBuilder updateBuilder = new StringBuilder();
updateBuilder.Append("update book_info set ");
updateBuilder.Append("book_count = '");
updateBuilder.Append(bookCount);
updateBuilder.Append("' where book_id = '");
updateBuilder.Append(bookid + "'");
int row = DBhelp.ExecuteNonQuery(updateBuilder.ToString());
if (row > 0)
{
return(true);
}
else
{
return(false);
}
}
public static bool Change_info(string cnum, string cname)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("update component_info set ");
stringBuilder.Append("Component_name='");
stringBuilder.Append(cname + "' ");
stringBuilder.Append("where Component_id='");
stringBuilder.Append(cnum + "'");
int rows = DBhelp.ExecuteNonQuery(stringBuilder.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
//查询书籍信息
public static DataTable QueryBook(string book_id, string author, string name, string press)
{
StringBuilder queryBuilder = new StringBuilder();
queryBuilder.Append("select * from book_info where ");
queryBuilder.Append("book_id LIKE '%");
queryBuilder.Append(book_id);
queryBuilder.Append("%' and author LIKE '%");
queryBuilder.Append(author);
queryBuilder.Append("%' and name LIKE '%");
queryBuilder.Append(name);
/*queryBuilder.Append("' and name = '");
* queryBuilder.Append(name);*/
queryBuilder.Append("%' and publish LIKE '%");
queryBuilder.Append(press + "%'");
DataTable data = DBhelp.GetDataTable(queryBuilder.ToString());
return(data);
}
//查询BookInfo表 带全部查询(表中所有相关的字段)
public DataSet selectBookInfo1(List <string> list, string B)
{
string sql = "";
for (int i = 0; i < list.Count; i++)
{
if (i != list.Count - 1)
{
sql += string.Format(@"select BookId,BookName,TimeIn,BookTypeName,Author,PinYinCode,Translator,Language,BookNumber,Price,Layout,Address,ISBN,Versions,BookRemark from BookInfo
inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId
where {0} like '%{1}%' union ", list[i], B);
}
else
{
sql += string.Format(@"select BookId,BookName,TimeIn,BookTypeName,Author,PinYinCode,Translator,Language,BookNumber,Price,Layout,Address,ISBN,Versions,BookRemark from BookInfo
inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId
where {0} like '%{1}%' ", list[i], B);
}
}
return(DBhelp.Create().ExecuteAdater(sql));
}
//添加读者信息
public int addReader(Reader r)
{
string sql = "proc_AddReader";
SqlParameter[] sp =
{
new SqlParameter("@ReaderId", r.ReaderId),
new SqlParameter("@ReaderName", r.ReaderName),
new SqlParameter("@TimeIn", r.TimeIn),
new SqlParameter("@TimeOut", r.TimeOut),
new SqlParameter("@ReaderTypeId", r.ReaderTypeId),
new SqlParameter("@DepartmentId", r.DepartmentId),
new SqlParameter("@ClassId", r.ClassId),
new SqlParameter("@IdentityCard", r.IdentityCard),
new SqlParameter("@Gender", r.Gender),
new SqlParameter("@ReturnValue", DbType.Int32)
};
sp[sp.Length - 1].Direction = ParameterDirection.ReturnValue;
DBhelp.Create().ExecuteNonQuery(sql, CommandType.StoredProcedure, sp);
return((int)sp[sp.Length - 1].Value);
}
// 返回读者编号，读者姓名
public List <Reader> selectReaderId(string ReaderId)
{
string sql = "select ReaderId,ReaderName from Reader where ReaderId like '%'+@ReaderId+'%' ";
SqlParameter[] sp =
{
new SqlParameter("@ReaderId", ReaderId)
};
SqlDataReader reader = DBhelp.Create().ExecuteReader(sql, sp: sp);
List <Reader> list = new List <Reader>();
while (reader.Read())
{
Reader r = new Reader();
r.ReaderId = reader["ReaderId"].ToString();
r.ReaderName = reader["ReaderName"].ToString();
list.Add(r);
}
reader.Close();
return(list);
}
//update supplier
public static bool UpdateSupplier(string id, string name, string addr)
{
StringBuilder updatestr = new StringBuilder();
updatestr.Append("update supplier_info set Supplier_name = '");
updatestr.Append(name);
updatestr.Append("' , Supplier_addr = '");
updatestr.Append(addr + "'");
updatestr.Append("where Supplier_id = '");
updatestr.Append(id + "'");
int row = DBhelp.ExecuteNonQuery(updatestr.ToString());
if (row > 0)
{
return(true);
}
else
{
return(false);
}
}
//显示数据
public static List <T_Employee> GetList()
{
string sql = "select * from T_Employee";
DataTable table = DBhelp.Read(sql);
List <T_Employee> lst = new List <T_Employee>();
foreach (DataRow row in table.Rows)
{
T_Employee emp = new T_Employee
{
EmployeeId = Convert.ToInt32(row["EmployeeId"]),
EmployeeName = row["EmployeeName"].ToString(),
ImageUrl = row["ImageUrl"].ToString(),
DeptId = Convert.ToInt32(row["DeptId"]),
Sex = row["Sex"].ToString(),
Age = Convert.ToInt32(row["Age"])
};
lst.Add(emp);
}
return(lst);
}
/*
* 修改用户权限和密码
*/
public static bool Change_info(string uid, string pwd, string limit)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("update user_info set ");
stringBuilder.Append("User_pwd='");
stringBuilder.Append(pwd + "', User_limit='");
stringBuilder.Append(limit + "' ");
stringBuilder.Append("where User_id='");
stringBuilder.Append(uid + "'");
int rows = DBhelp.ExecuteNonQuery(stringBuilder.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
//返回用户编号，用户姓名
public List <User> selectUserId(string UserId)
{
string sql = "select UserId,UserName from User where UserId like '%'+@UserId+'%' ";
SqlParameter[] sp =
{
new SqlParameter("@UserId", UserId)
};
SqlDataReader User = DBhelp.Create().ExecuteReader(sql, sp: sp);
List <User> list = new List <User>();
while (User.Read())
{
User r = new User();
r.UserId = User["UserId"].ToString();
r.UserName = User["UserName"].ToString();
list.Add(r);
}
User.Close();
return(list);
}
// 借书
public int BorrowBook(BorrowReturn b)
{
string sql = "proc_BorrowBook";
SqlParameter[] sp =
{
new SqlParameter("@BorrowId", DbType.Int32),
new SqlParameter("@BookId", b.BookId),
new SqlParameter("@ReaderId", b.ReaderId),
new SqlParameter("@BorrowTime", b.BorrowTime),
new SqlParameter("@ReturnTime", b.ReturnTime),
new SqlParameter("@Fine", b.Fine),
new SqlParameter("@RenewCount", b.RenewCount),
new SqlParameter("@BorrowRemark", b.BorrowRemark),
new SqlParameter("@ReturnValue", DbType.Int32)
};
sp[0].Direction = ParameterDirection.Output;
sp[sp.Length - 1].Direction = ParameterDirection.ReturnValue;
DBhelp.Create().ExecuteNonQuery(sql, CommandType.StoredProcedure, sp);
b.BorrowId = (int)sp[0].Value;
return((int)sp[sp.Length - 1].Value);
}
public static bool Record_Lend(string bookid)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("insert into lend_list(book_id, reader_id, " +
"lend_date, back_date) values(");
stringBuilder.Append("'" + bookid + "' ,");
stringBuilder.Append("'" + ruid + "' ,");
stringBuilder.Append("'" + DateTime.Now.ToShortDateString() + "' ,");
stringBuilder.Append("'" + DateTime.Now.AddMonths(3).ToShortDateString() + "'");
stringBuilder.Append(")");
int rows = DBhelp.ExecuteNonQuery(stringBuilder.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
//更新图书
public static bool UpdateBook(string bookid, string name, string author, string press,
string intro, string price, string pubdate, string classid, string state, string bookCount)
{
StringBuilder updateBuilder = new StringBuilder();
updateBuilder.Append("update book_info set ");
updateBuilder.Append("name = '");
updateBuilder.Append(name);
updateBuilder.Append("' , author = '");
updateBuilder.Append(author);
updateBuilder.Append("' , publish = '");
updateBuilder.Append(press);
updateBuilder.Append("' , introduction = '");
updateBuilder.Append(intro);
updateBuilder.Append("' , price = '");
updateBuilder.Append(price);
updateBuilder.Append("' , pubdate = '");
updateBuilder.Append(pubdate);
updateBuilder.Append("' , class_id = '");
updateBuilder.Append(classid);
updateBuilder.Append("' , state = '");
updateBuilder.Append(state);
updateBuilder.Append("' , book_count = '");
updateBuilder.Append(bookCount);
updateBuilder.Append("' where book_id = '");
updateBuilder.Append(bookid + "'");
int row = DBhelp.ExecuteNonQuery(updateBuilder.ToString());
if (row > 0)
{
return(true);
}
else
{
return(false);
}
}
//修改用户信息
public int updateUser(User User)
{
string sql = @"update Reader set UserName=@UserName,TimeIn=@TimeIn,TimeOut=@TimeOut,UserTypeId=@UserTypeId,DepartmentId=@DepartmentId,ClassId=@ClassId,IdentityCard=@IdentityCard,Gender=@Gender,QQ=@QQ,Phone=@Phone,Email=@Email,Address=@Address,UserRemark=@UserRemark where UserId=@UserId";
SqlParameter[] sp =
{
new SqlParameter("@UserName", User.UserName),
new SqlParameter("@TimeIn", User.TimeIn),
new SqlParameter("@TimeOut", User.TimeOut),
new SqlParameter("@UserTypeId", User.UserTypeId),
new SqlParameter("@DepartmentId", User.DepartmentId),
new SqlParameter("@ClassId", User.ClassId),
new SqlParameter("@IdentityCard", User.IdentityCard),
new SqlParameter("@Gender", User.Gender),
new SqlParameter("@QQ", User.QQ),
new SqlParameter("@Phone", User.Phone),
new SqlParameter("@Email", User.Email),
new SqlParameter("@Address", User.Address),
new SqlParameter("@UserRemark", User.UserRemark),
new SqlParameter("@UserId", User.UserId)
};
return(DBhelp.Create().ExecuteNonQuery(sql, sp: sp));
}
public static bool update_ustate()
{
int count = Lend_num();
count++;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("update reader_info set ");
stringBuilder.Append("borrowable='");
stringBuilder.Append(count + "'");
stringBuilder.Append(" where reader_id='");
stringBuilder.Append(ruid + "'");
int rows = DBhelp.ExecuteNonQuery(stringBuilder.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
//添加图书信息
public int AddBookInfo(BookInfo book)
{
string sql = @"insert into BookInfo select @BookId,@BookName,@TimeIn,@BookTypeId,@Author,@PinYinCode,@Translator,@Language,@BookNumber,@Price,@Layout,@Address,@ISBN,@Versions,@BookRemark";
SqlParameter[] sp =
{
new SqlParameter("@BookId", book.BookId),
new SqlParameter("@BookName", book.BookName),
new SqlParameter("@TimeIn", book.TimeIn),
new SqlParameter("@BookTypeId", book.BookTypeId),
new SqlParameter("@Author", book.Author),
new SqlParameter("@PinYinCode", book.PinYinCode),
new SqlParameter("@Translator", book.Translator),
new SqlParameter("@Language", book.Language),
new SqlParameter("@BookNumber", book.BookNumber),
new SqlParameter("@Price", book.Price),
new SqlParameter("@Layout", book.Layout),
new SqlParameter("@Address", book.Address),
new SqlParameter("@ISBN", book.ISBN),
new SqlParameter("@Versions", book.Versions),
new SqlParameter("@BookRemark", book.BookRemark),
};
return(DBhelp.Create().ExecuteNonQuery(sql, sp: sp));
}
/*
* 获取用户账号
*/
public static string Get_num(string uname, string pwd)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select * from user_info");
stringBuilder.Append(" where ");
stringBuilder.Append("User_name='");
stringBuilder.Append(uname);
stringBuilder.Append("' and User_pwd=");
stringBuilder.Append(pwd);
SqlDataReader sqlDataReader = DBhelp.ExecuteReader(stringBuilder.ToString());
if (sqlDataReader.Read())
{
string num = sqlDataReader[0].ToString();
sqlDataReader.Close();
return(num);
}
else
{
sqlDataReader.Close();
return(null);
}
}
/// <summary>
/// 查询BookInfo表 带全部查询(表中所有相关的字段)
/// </summary>
/// <param name="list"></param>
/// <param name="B"></param>
/// <returns></returns>
public DataSet selectBookInfo1(List <string> list, string B)
{
string sql = "";
for (int i = 0; i < list.Count; i++)
{
if (i != list.Count - 1)
{
sql += string.Format(@"select BookId,BookName,TimeIn,BookTypeName,Author,
BookNumber,Price,ISBS from BookInfo
inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId
where {0} like '%{1}%' union ", list[i], B);
}
else
{
sql += string.Format(@"select BookId,BookName,TimeIn,BookTypeName,Author,
BookNumber,Price,ISBS from BookInfo
inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId
where {0} like '%{1}%' ", list[i], B);
}
}
return(DBhelp.Create().ExecuteAdater(sql));
}
public static bool addOrder(int orderNum, string supName, string ComName1, string ComName2, string ComName3, string ComName4, string ComName5)
{
StringBuilder addstr = new StringBuilder();
addstr.Append("insert into order_list select Su.Supplier_id, Co.Component_id, Order_num = ");
addstr.Append(orderNum);
addstr.Append(" from( select Supplier_id from supplier_info where Supplier_name = '");
addstr.Append(supName + "') as Su, (select Component_id from component_info where Component_name='");
addstr.Append(ComName1 + "' or Component_name='");
addstr.Append(ComName2 + "' or Component_name='");
addstr.Append(ComName3 + "' or Component_name='");
addstr.Append(ComName4 + "' or Component_name='");
addstr.Append(ComName5 + "' )as Co");
int row = DBhelp.ExecuteNonQuery(addstr.ToString());
if (row > 0)
{
return(true);
}
else
{
return(false);
}
}
//查询BorrowReturn表信息
public DataSet selectHostory(BorrowReturn b, string radioName, String cboBorrowTimeType, Boolean checkTime)
{
string sql = string.Format(@"select BookInfo.BookId as 'BookId',Reader.UserId as 'UserId',BookName,UserName,BookTypeName,UserTypeName,Gender,IdentityCard,BorrowTime,ReturnTime,FactReturnTime,Fine,RenewCount from BorrowReturn
inner join BookInfo on BookInfo.BookId=BorrowReturn.BookId
inner join Reader on Reader.UserId=BorrowReturn.UserId
inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId
inner join ReaderType on ReaderType.UserTypeId=Reader.UserTypeId
where BookInfo.BookId like '%{0}%' and
Reader.UserId like '%{1}%' ", b.BookId, b.UserId);
if (radioName == "全部")
{
}
else if (radioName == "已借")
{
sql += " and FactReturnTime is null ";
}
else if (radioName == "已还")
{
sql += " and FactReturnTime is not null ";
}
return(DBhelp.Create().ExecuteAdater(sql));
}
public DataSet selectBookType1()
{
string sql = "select BookTypeId,BookTypeName from BookType";
return(DBhelp.Create().ExecuteAdater(sql));
}
//删除
public static bool Delete(int EmployeeId)
{
string sql = string.Format("delete from T_Employee where EmployeeId='{0}'", EmployeeId);
return(DBhelp.Write(sql));
}
//添加员工--直接向T_Employee中加入
public static bool Add(T_Employee emp)
{
string sql = string.Format("insert into T_Employee values('{0}','{1}','{2}','{3}','{4}')", emp.EmployeeName, emp.ImageUrl, emp.DeptId, emp.Sex, emp.Age);
return(DBhelp.Write(sql));
}
//查询下拉列表中的列表
public static DataTable GetListTable()
{
string sql = "select * from T_Employee join T_Dept on T_Employee.DeptId=T_Dept.DeptId";
return(DBhelp.Read(sql));
}
public DataSet selectClass1()
{
string sql = "select * from Class";
return(DBhelp.Create().ExecuteAdater(sql));
}
//查询全部的读者类型
public DataSet selectReaderType1()
{
string sql = @"select * from ReaderType";
return(DBhelp.Create().ExecuteAdater(sql));
}
public static DataTable Getlist()
{
string sql = "select * from T_Dept";
return(DBhelp.Read(sql));
}
//查询全部
public DataSet selectAdmin()
{
string sql = @"select * from Admin";
return(DBhelp.Create().ExecuteAdater(sql));
}
public DataSet selectDepartment1()
{
string sql = "select * from Department";
return(DBhelp.Create().ExecuteAdater(sql));;
}
/// <summary>
/// 查询BookInfo表
/// </summary>
/// <returns></returns>
public DataSet selectBookInfo1()
{
string sql = @"select BookId,BookName,TimeIn,BookTypeName,Author,PinYinCode,Translator,Language,BookNumber,Price,Layout,Address,ISBS,Versions,BookRemark from BookInfo inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId";
return(DBhelp.Create().ExecuteAdater(sql));
}