//修改管理员 public int ExitAdmin(User a) { string sql = "update Admin set LoginId=@LoginId,LoginPwd=@LoginPwd,LoginType=@LoginType,LoginRemark=@LoginRemark where LoginId=@LoginId"; SqlParameter[] sp = { new SqlParameter("@LoginId", a.LoginId), new SqlParameter("@LoginPwd", a.LoginPwd), new SqlParameter("@LoginType", a.LoginType), new SqlParameter("@LoginRemark", a.LoginRemark), }; return(DBhelp.Create().ExecuteNonQuery(sql, sp: sp)); }
/// <summary> /// 获取房间 /// </summary> /// <returns></returns> public List <Room> SelectRoom() { string sqlStr = "select RoomID,RoomName from tb_Room"; List <Room> rtnList = new List <Room>(); SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr); while (reader.Read()) { rtnList.Add(new Room(reader.GetInt32(0), reader.GetString(1))); } reader.Close(); return(rtnList); }
/// <summary> /// 无条件查询读者信息 /// </summary> /// <returns></returns> public List <PersonInfo> SelectPersonName() { string sql = @"select PersonID,PersonName from tb_PersonInfo"; List <PersonInfo> list = new List <PersonInfo>(); SqlDataReader reader = DBhelp.Create().ExecuteReader(sql); while (reader.Read()) { list.Add(new PersonInfo(reader.GetGuid(0).ToString(), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5))); } reader.Close(); return(list); }
/// <summary> /// 查询部门信息 /// </summary> /// <returns></returns> public List <DeptInfo> SelectDeptInfo() { string sql = "select * from tb_DeptInfo"; List <DeptInfo> rtnList = new List <DeptInfo>(); SqlDataReader reader = DBhelp.Create().ExecuteReader(sql); while (reader.Read()) { rtnList.Add(new DeptInfo(reader.GetString(0), reader.GetString(1))); } reader.Close(); return(rtnList); }
/// <summary> /// 查询资料计量单位 /// </summary> /// <returns></returns> public List <MatUnit> SelectMatUnit() { string sql = "select * from tb_MatUnit order by MatUnitID desc"; List <MatUnit> list = new List <MatUnit>(); SqlDataReader reader = DBhelp.Create().ExecuteReader(sql); while (reader.Read()) { MatUnit tempMatType = new MatUnit(reader.GetInt32(0), reader.GetString(1)); list.Add(tempMatType); } reader.Close(); return(list); }
/// <summary> /// 查询资料计量单位 /// </summary> /// <returns></returns> public List <EquipType> SelectEquipType() { string sql = "select * from tb_EquipType order by EquipTypeID asc"; List <EquipType> list = new List <EquipType>(); SqlDataReader reader = DBhelp.Create().ExecuteReader(sql); while (reader.Read()) { EquipType tempEquipType = new EquipType(reader.GetInt32(0), reader.GetString(1)); list.Add(tempEquipType); } reader.Close(); return(list); }
/// <summary> /// 查询所有的密级信息 /// </summary> /// <returns></returns> public List <SecurityInfo> SelectSecurityInfo() { List <SecurityInfo> rtnList = new List <SecurityInfo>(); string sqlStr = @"SELECT * FROM [db_RMS].[dbo].[tb_SecurityInfo]"; SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr); while (reader.Read()) { rtnList.Add(new SecurityInfo(reader.GetString(0), reader.GetString(1))); } reader.Close(); return(rtnList); }
/// <summary> /// 查询图书类型 /// </summary> /// <returns></returns> public List <BookType> SelectBookType() { string sql = "select [BookTypeGuid],[BookTypeName] from tb_BookType order by BookTypeName asc"; List <BookType> list = new List <BookType>(); SqlDataReader reader = DBhelp.Create().ExecuteReader(sql); while (reader.Read()) { BookType tempBookType = new BookType(reader.GetGuid(0).ToString(), reader.GetString(1)); list.Add(tempBookType); } reader.Close(); return(list); }
//查询借还表信息 public DataSet selectHostory(BorrowReturn b, string radioName, String cboBorrowTimeType, Boolean checkTime) { string sql = string.Format(@"select BookInfo.BookId as 'BookId',Reader.ReaderId as 'ReaderId',BookName,ReaderName, BookTypeName,ReaderTypeName,Gender,IdentityCard, BorrowTime,ReturnTime,FactReturnTime,Fine,RenewCount from BorrowReturn inner join BookInfo on BookInfo.BookId=BorrowReturn.BookId inner join Reader on Reader.ReaderId=BorrowReturn.ReaderId inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId inner join ReaderType on ReaderType.ReaderTypeId=Reader.ReaderTypeId where BookInfo.BookId like '%{0}%' and Reader.ReaderId like '%{1}%' ", b.BookId, b.ReaderId); if (radioName == "全部") { } else if (radioName == "已借") { sql += " and FactReturnTime is null "; } else if (radioName == "已还") { sql += " and FactReturnTime is not null "; } if (checkTime) { if (cboBorrowTimeType == "日期") { sql += string.Format(@" and BorrowId in( select BorrowId from BorrowReturn where BorrowTime between '{0}' and '{1}' union select BorrowId from BorrowReturn where ReturnTime between '{0}' and '{1}' union select BorrowId from BorrowReturn where FactReturnTime between '{0}' and '{1}' ) ", b.TimeIn, b.TimeOut); } else if (cboBorrowTimeType == "借书日期") { sql += @" and BorrowTime between '" + b.TimeIn + "' and '" + b.TimeOut + "' "; } else if (cboBorrowTimeType == "应还书日期") { sql += @" and ReturnTime between '" + b.TimeIn + "' and '" + b.TimeOut + "' "; } else if (cboBorrowTimeType == "实际还书日期") { sql += @" and FactReturnTime between '" + b.TimeIn + "' and '" + b.TimeOut + "' "; } } return(DBhelp.Create().ExecuteAdater(sql)); }
public List <Class> selectClass() { string sql = "select * from Class"; List <Class> list = new List <Class>(); SqlDataReader reader = DBhelp.Create().ExecuteReader(sql); while (reader.Read()) { Class c = new Class(); c.ClassId = reader.GetInt32(0); c.ClassName = reader.GetString(1); list.Add(c); } reader.Close(); return(list); }
public int AddClass(Class c) { string sql = "proc_addClass"; SqlParameter[] sp = { new SqlParameter("@ClassId", DbType.Int32), new SqlParameter("@ClassName", c.ClassName), new SqlParameter("@ReturnValue", DbType.Int32) }; sp[0].Direction = ParameterDirection.Output; sp[2].Direction = ParameterDirection.ReturnValue; DBhelp.Create().ExecuteNonQuery(sql, CommandType.StoredProcedure, sp); c.ClassId = (int)sp[0].Value; return((int)sp[2].Value); }
/// <summary> /// /// </summary> /// <param name="matID"></param> /// <returns></returns> public BookInfo_View SelectBookInfo_V(string bookGuid) { BookInfo_View bookInfo_V = new BookInfo_View(); string sqlStr = @"SELECT [BookGuid],[BookISBN],[BookRawName],[BookCNName],[BookAuthor]," + "[BookPress],[BookPubDate],[BookNumber],[BookBuyDate],[BookBuyPerson],[BookBuyShop]," + "[BookTypeName],[BookLanType],[BookAnnotation],[BookIsDelete] FROM [db_RMS].[dbo].[View_BookInfo] " + "where BookGuid='" + bookGuid + "'"; SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr); if (reader.Read()) { return(new BookInfo_View(reader.GetGuid(0).ToString(), reader.GetString(1).Trim().ToString(), reader.GetString(2).Trim().ToString(), reader.GetString(3).ToString(), reader.GetString(4).Trim().ToString(), reader.GetString(5).Trim().ToString(), reader.GetDateTime(6), reader.GetInt32(7), reader.GetDateTime(8), reader.GetString(9).Trim().ToString(), reader.GetString(10).Trim().ToString(), reader.GetString(11).Trim().ToString(), reader.GetString(12).Trim().ToString(), reader.GetString(13).Trim().ToString(), reader.GetBoolean(14))); } return(bookInfo_V); }
//返回编号,姓名 public List <PersonInfo> 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 <PersonInfo> list = new List <PersonInfo>(); while (reader.Read()) { PersonInfo r = new PersonInfo(reader.GetGuid(0).ToString(), reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5)); list.Add(r); } reader.Close(); return(list); }
/// <summary> /// 根据资料类型查询资料 /// 并按照配属装备类型筛选 /// </summary> /// <param name="checkedMatTypeList"></param> /// <param name="equipTypeName"></param> /// <returns></returns> public List <BookInfo_View> SelectMatInfoByEquipType(List <string> checkedMatTypeList, string equipTypeName) { List <BookInfo_View> rtnList = new List <BookInfo_View>(); foreach (string matTypeName in checkedMatTypeList) { string sqlStr = @"SELECT [MatGuid],[MatID],[MatName],[MatUnitName],[MatNumber],[SendDept],[StartTime],[EndTime],[RoomName],[PersonName]," + "[MatTypeName],[EquipTypeName],[Annotation] FROM [db_RMS].[dbo].[View_MatInfo] where MatTypeName='" + matTypeName + "' and EquipTypeName='" + equipTypeName + "'"; SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr); while (reader.Read()) { rtnList.Add(new BookInfo_View(reader.GetGuid(0).ToString(), reader.GetString(1).Trim().ToString(), reader.GetString(2).Trim().ToString(), reader.GetString(3).ToString(), reader.GetString(4).Trim().ToString(), reader.GetString(5).Trim().ToString(), reader.GetDateTime(6), reader.GetInt32(7), reader.GetDateTime(8), reader.GetString(9).Trim().ToString(), reader.GetString(10).Trim().ToString(), reader.GetString(11).Trim().ToString(), reader.GetString(12).Trim().ToString(), reader.GetString(13).Trim().ToString(), reader.GetBoolean(14))); } reader.Close(); } return(rtnList); }
/// <summary> /// 修改资料信息 /// tb_MatInfo /// </summary> /// <param name="bookInfo"></param> /// <returns></returns> public int UpdateMatInfo(BookInfo_Table bookInfo) { string sqlStr = @"UPDATE [db_RMS].[dbo].[tb_BookInfo] " + "SET [BookGuid] ='" + bookInfo.BookGuid + "'" + ",[BookISBN]='" + bookInfo.BookISBN + "'" + ",[BookRawName]='" + bookInfo.BookRawName + "'" + ",[BookCNName]='" + bookInfo.BookCNName + "'" + ",[BookPubDate]='" + bookInfo.BookPubDate + "'" + ",[BookNumber] ='" + bookInfo.BookNumber + "'" + ",[BookBuyDate] ='" + bookInfo.BookBuyDate + "'" + ",[BookBuyPerson] ='" + bookInfo.BookBuyPerson + "'" + ",[BookBuyShop] = '" + bookInfo.BookBuyShop + "'" + ",[BookTypeGuid] ='" + bookInfo.BookTypeGuid + "'" + ",[BookLanType]='" + bookInfo.BookLanType + "'" + ",[BookAnnotation] ='" + bookInfo.BookAnnotation + "'" + ",[BookIsDelete]='" + bookInfo.BookIsDelete + "'" + " WHERE [BookGuid] = '" + bookInfo.BookGuid + "'"; return(DBhelp.Create().ExecuteNonQuery(sqlStr)); }
//借书 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); }
/// <summary> /// 无筛选状态下,根据图书类型查询资料 /// </summary> /// <param name="checkedBookTypeList"></param> /// <returns></returns> public List <BookInfo_View> SelectBookInfo(List <string> checkedBookTypeList) { List <BookInfo_View> rtnList = new List <BookInfo_View>(); foreach (string bookTypeName in checkedBookTypeList) { string sqlStr = @"SELECT [BookGuid],[BookISBN],[BookRawName],[BookCNName]," + "[BookAuthor],[BookPress],[BookPubDate],[BookNumber],[BookBuyDate]," + "[BookBuyPerson],[BookBuyShop],[BookTypeName],[BookLanType],[BookAnnotation]," + "[BookIsDelete] FROM [db_RMS].[dbo].[View_BookInfo] where BookTypeName='" + bookTypeName + "'"; SqlDataReader reader = DBhelp.Create().ExecuteReader(sqlStr); while (reader.Read()) { rtnList.Add(new BookInfo_View(reader.GetGuid(0).ToString(), reader.GetString(1).Trim().ToString(), reader.GetString(2).Trim().ToString(), reader.GetString(3).ToString(), reader.GetString(4).Trim().ToString(), reader.GetString(5).Trim().ToString(), reader.GetDateTime(6), reader.GetInt32(7), reader.GetDateTime(8), reader.GetString(9).Trim().ToString(), reader.GetString(10).Trim().ToString(), reader.GetString(11).Trim().ToString(), reader.GetString(12).Trim().ToString(), reader.GetString(13).Trim().ToString(), reader.GetBoolean(14))); } reader.Close(); } return(rtnList); }
/// <summary> /// 添加图书信息 /// </summary> /// <param name="bookInfo"></param> /// <returns></returns> public int AddBookInfo(BookInfo_Table bookInfo) { string sql = @"INSERT INTO tb_BookInfo ([BookGuid],[BookISBN],[BookRawName],[BookCNName],[BookAuthor],[BookPress],[BookPubDate]," + "[BookNumber],[BookBuyDate],[BookBuyPerson],[BookBuyShop],[BookTypeGuid],[BookLanType],[BookAnnotation],[BookIsDelete]) " + "VALUES ('" + bookInfo.BookGuid + "','" + bookInfo.BookISBN + "','" + bookInfo.BookRawName + "','" + bookInfo.BookCNName + "','" + bookInfo.BookAuthor + "','" + bookInfo.BookPress + "','" + bookInfo.BookPubDate + "','" + bookInfo.BookNumber + "','" + bookInfo.BookBuyDate + "','" + bookInfo.BookBuyPerson + "','" + bookInfo.BookBuyShop + "','" + bookInfo.BookTypeGuid + "','" + bookInfo.BookLanType + "','" + bookInfo.BookAnnotation + "','" + bookInfo.BookIsDelete + "')"; return(DBhelp.Create().ExecuteNonQuery(sql)); }
/// <summary> /// 查询BookInfo表,是否存在bookISBN /// </summary> /// <param name="matID"></param> /// <returns></returns> public int IsBookISBNHaving(string bookISBN) { string sql = "select count(*) from tb_BookInfo where BookISBN='" + bookISBN + "'"; return(DBhelp.Create().ExecuteNonQuery(sql)); }
/// <summary> /// 修改材料类型 /// </summary> /// <param name="matUnit"></param> /// <returns></returns> public int UpdateMatUnit(MatUnit matUnit) { string sqlStr = "update tb_MatType set MatUnitName='" + matUnit.MatUnitName + "' where MatUnitID='" + matUnit.MatUnitID + "'"; return(DBhelp.Create().ExecuteNonQuery(sqlStr)); }
/// <summary> /// 修改材料类型 /// </summary> /// <param name="bookType"></param> /// <returns></returns> public int UpdateBookType(BookType bookType) { string sqlStr = "update tb_BookType set BookTypeName='" + bookType.BookTypeName + "' where BookTypeGuid='" + bookType.BookTypeGuid + "'"; return(DBhelp.Create().ExecuteNonQuery(sqlStr)); }
public DataSet selectClass1() { string sql = "select * from Class"; return(DBhelp.Create().ExecuteAdater(sql)); }
/// <summary> /// 查询是否存在该人员类型 /// </summary> /// <param name="personTypeName"></param> /// <returns></returns> public int CountPersonType(string personTypeName) { string sqlStr = "select count(*) from tb_PersonType where PersonTypeName='" + personTypeName + "'"; return(DBhelp.Create().ExecuteScalar(sqlStr)); }
/// <summary> /// 添加计量单位 /// </summary> /// <param name="type"></param> /// <returns></returns> public int AddMatUnit(string matUnitName) { string sqlStr = "insert into tb_MatUnit(MatUnitName) values ('" + matUnitName + "')"; return(DBhelp.Create().ExecuteNonQuery(sqlStr)); }
/// <summary> /// 查询是否存在该资料类型 /// </summary> /// <param name="matTypeName"></param> /// <returns></returns> public int CountMatUnit(string matUnitName) { string sqlStr = "select count(*) from tb_MatUnit where MatUnitName='" + matUnitName + "'"; return(DBhelp.Create().ExecuteScalar(sqlStr)); }
/// <summary> /// 添加图书类型 /// </summary> /// <param name="bookType"></param> /// <returns></returns> public int AddBookType(BookType bookType) { string sqlStr = "insert into tb_BookType([BookTypeGuid],[BookTypeName]) values ('" + bookType.BookTypeGuid + "','" + bookType.BookTypeName.ToString() + "')"; return(DBhelp.Create().ExecuteNonQuery(sqlStr)); }
/// <summary> /// 删除图书类型 /// 此为软删除 /// </summary> /// <param name="bookTypeGuid"></param> /// <returns></returns> public int DeleteBookType(string bookTypeGuid) { string sqlStr = "delete from tb_BookType where BookTypeGuid='" + bookTypeGuid + "'"; return(DBhelp.Create().ExecuteNonQuery(sqlStr)); }
/// <summary> /// 查询构建视图模型,查询图书信息 /// 用于显示dgv的信息 /// </summary> /// <returns></returns> public DataSet SelectBookInfo() { string sqlStr = @"SELECT * FROM [db_RMS].[dbo].[View_BookInfo]"; return(DBhelp.Create().ExecuteAdater(sqlStr)); }
/// <summary> /// 查询是否存在该资料类型 /// </summary> /// <param name="bookTypeName"></param> /// <returns></returns> public int CountBookType(string bookTypeName) { string sqlStr = "select count(*) from tb_BookType where BookTypeName='" + bookTypeName + "'"; return(DBhelp.Create().ExecuteScalar(sqlStr)); }
/// <summary> /// 删除资料类型 /// </summary> /// <param name="matUnitID"></param> /// <returns></returns> public int DeleteMatUnit(int matUnitID) { string sqlStr = "delete from tb_MatUnit where MatUnitID='" + matUnitID + "'"; return(DBhelp.Create().ExecuteNonQuery(sqlStr)); }