示例#1
0
        /// <summary>
        /// 添加管理员
        /// </summary>
        /// <param name="a"></param>
        /// <returns></returns>
        public int AddAdmin(Admin a)
        {
            string sql = "insert into Admin select @LoginId,@LoginPwd,@LoginType,@LoginRemark";

            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));
        }
示例#2
0
        /// <summary>
        /// 修改管理员
        /// </summary>
        /// <param name="a"></param>
        /// <returns></returns>
        public int ExitAdmin(Admin 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));
        }
示例#3
0
        //查询图书借还表(表连接)
        public DataSet selectBorrowReturn(string BookId)
        {
            string sql = @"select BookInfo.BookId,BookName,Reader.ReaderId,ReaderName,BorrowTime,ReturnTime,FactReturnTime,Fine,RenewCount,BorrowRemark from BookInfo
                    inner join BorrowReturn on BorrowReturn.BookId=BookInfo.BookId
                    inner join Reader on Reader.ReaderId=BorrowReturn.ReaderId 
                    where BookInfo.BookId=@BookId ";

            SqlParameter[] sp =
            {
                new SqlParameter("@BookId", BookId)
            };

            return(DBhelp.Create().ExecuteAdater(sql, sp));
        }
示例#4
0
        //查询借还表信息
        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));
        }
示例#5
0
        /// <summary>
        /// 查询BookInfo表 目前可以借阅的图书
        /// </summary>
        /// <param name="BookId"></param>
        /// <returns></returns>
        public DataSet selectBookInfo2(string BookId)
        {
            string sql = @"select BookInfo.BookId as 'BookId',BookName,TimeIn,BookTypeName,Author,
                    BookNumber,Price,ISBS from BookInfo
                    inner join BookType on BookType.BookTypeId=BookInfo.BookTypeId
                   
                    where BookInfo.BookId like '%'+@BookId+'%' and BookInfo.BookId not in(select BookId from BorrowReturn where FactReturnTime is null )";

            SqlParameter[] sp =
            {
                new SqlParameter("@BookId", BookId)
            };
            return(DBhelp.Create().ExecuteAdater(sql, sp: sp));
        }
示例#6
0
        // 根据读者类型ID查询的读者信息
        public DataSet selectReader(int ReaderTypeId)
        {
            string sql = @"select ReaderId,ReaderName,TimeIn,TimeOut,ReaderTypeName,DepartmentName,ClassName,IdentityCard,Gender,Special,Phone,Email,Address,ReaderRemark from Reader 
                            inner join ReaderType on ReaderType.ReaderTypeId=Reader.ReaderTypeId
                            inner join Department on Department.DepartmentId=Reader.DepartmentId
                            inner join Class on Class.ClassId=Reader.ClassId
                            where ReaderType.ReaderTypeId=@ReaderTypeId ";

            SqlParameter[] sp =
            {
                new SqlParameter("@ReaderTypeId", ReaderTypeId)
            };
            return(DBhelp.Create().ExecuteAdater(sql, sp));
        }
示例#7
0
        /// <summary>
        /// 查询BookInfo表 目前可以借阅的图书
        /// </summary>
        /// <param name="BookId"></param>
        /// <returns></returns>
        public DataSet selectBookInfo2(string BookId)
        {
            string sql = @"select BookInfo.BookId as '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
                   
                    where BookInfo.BookId like '%'+@BookId+'%' and BookInfo.BookId not in(select BookId from BorrowReturn where FactReturnTime is null )";

            SqlParameter[] sp =
            {
                new SqlParameter("@BookId", BookId)
            };
            return(DBhelp.Create().ExecuteAdater(sql, sp: sp));
        }
示例#8
0
        public List <BookType> selectBookType()
        {
            string          sql    = "select *  from BookType";
            List <BookType> list   = new List <BookType>();
            SqlDataReader   reader = DBhelp.Create().ExecuteReader(sql);

            while (reader.Read())
            {
                BookType b = new BookType();
                b.BookTypeId   = reader.GetInt32(0);
                b.BookTypeName = reader.GetString(1);
                list.Add(b);
            }
            reader.Close();
            return(list);
        }
示例#9
0
        public int addDepartment(Department d)
        {
            string sql = "proc_addDepartment";

            SqlParameter[] sp =
            {
                new SqlParameter("@DepartmentId",   DbType.Int32),
                new SqlParameter("@DepartmentName", d.DepartmentName),
                new SqlParameter("@ReturnValue",    DbType.Int32)
            };
            sp[0].Direction = ParameterDirection.Output;
            sp[2].Direction = ParameterDirection.ReturnValue;
            DBhelp.Create().ExecuteNonQuery(sql, CommandType.StoredProcedure, sp);
            d.DepartmentId = (int)sp[0].Value;
            return((int)sp[2].Value);
        }
示例#10
0
        public List <Department> selectDepartment()
        {
            string            sql    = "select * from Department";
            List <Department> list   = new List <Department>();
            SqlDataReader     reader = DBhelp.Create().ExecuteReader(sql);

            while (reader.Read())
            {
                Department d = new Department();
                d.DepartmentId   = (int)reader["DepartmentId"];
                d.DepartmentName = reader["DepartmentName"].ToString();
                list.Add(d);
            }
            reader.Close();
            return(list);
        }
        //添加读者类型
        public int addReaderType(ReaderType r)
        {
            string sql = "proc_addReaderType";

            SqlParameter[] sp =
            {
                new SqlParameter("@ReaderTypeId",   DbType.Int32),
                new SqlParameter("@ReaderTypeName", r.ReaderTypeName),
                new SqlParameter("@ReturnValue",    DbType.Int32)
            };
            sp[0].Direction = ParameterDirection.Output;
            sp[2].Direction = ParameterDirection.ReturnValue;
            DBhelp.Create().ExecuteNonQuery(sql, CommandType.StoredProcedure, sp);
            r.ReaderTypeId = (int)sp[0].Value;
            return((int)sp[2].Value);
        }
        //查询全部的读者类型
        public List <ReaderType> selectReaderType()
        {
            List <ReaderType> list   = new List <ReaderType>();
            string            sql    = @"select * from ReaderType";
            SqlDataReader     reader = DBhelp.Create().ExecuteReader(sql);

            while (reader.Read())
            {
                ReaderType type = new ReaderType();
                type.ReaderTypeId   = reader.GetInt32(0);
                type.ReaderTypeName = reader.GetString(1);
                list.Add(type);
            }
            reader.Close();
            return(list);
        }
示例#13
0
        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);
        }
示例#14
0
        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);
        }
示例#15
0
        public int AddBookTypeInfo(BookType type)
        {
            string sql = "proc_AddBookTypeInfo";

            SqlParameter[] sp =
            {
                new SqlParameter("@BookTypeId",   SqlDbType.Int),
                new SqlParameter("@BookTypeName", type.BookTypeName),
                new SqlParameter("@ReturnValue",  SqlDbType.Int)
            };
            sp[0].Direction = ParameterDirection.Output;
            sp[2].Direction = ParameterDirection.ReturnValue;
            DBhelp.Create().ExecuteNonQuery(sql, CommandType.StoredProcedure, sp);

            type.BookTypeId = (int)sp[0].Value;
            return((int)sp[2].Value);
        }
示例#16
0
        /// <summary>
        /// 添加图书信息
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        public int AddBookInfo(BookInfo book)
        {
            string sql = @"insert into BookInfo select @BookId,@BookName,@TimeIn,@BookTypeId,@Author,
                    @BookNumber,@Price,@ISBS";

            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("@BookNumber", book.BookNumber),
                new SqlParameter("@Price",      book.Price),
                new SqlParameter("@ISBS",       book.ISBS)
            };
            return(DBhelp.Create().ExecuteNonQuery(sql, sp: sp));
        }
示例#17
0
        //修改读者信息
        public int updateReader(Reader reader)
        {
            string sql = @"update Reader set ReaderName=@ReaderName,TimeIn=@TimeIn,TimeOut=@TimeOut,ReaderTypeId=@ReaderTypeId,
                            DepartmentId=@DepartmentId,ClassId=@ClassId,IdentityCard=@IdentityCard,Gender=@Gender where ReaderId=@ReaderId";

            SqlParameter[] sp =
            {
                new SqlParameter("@ReaderName",   reader.ReaderName),
                new SqlParameter("@TimeIn",       reader.TimeIn),
                new SqlParameter("@TimeOut",      reader.TimeOut),
                new SqlParameter("@ReaderTypeId", reader.ReaderTypeId),
                new SqlParameter("@DepartmentId", reader.DepartmentId),
                new SqlParameter("@ClassId",      reader.ClassId),
                new SqlParameter("@IdentityCard", reader.IdentityCard),
                new SqlParameter("@Gender",       reader.Gender),
                new SqlParameter("@ReaderId",     reader.ReaderId)
            };
            return(DBhelp.Create().ExecuteNonQuery(sql, sp: sp));
        }
示例#18
0
        /// <summary>
        /// 修改图书信息
        /// </summary>
        /// <param name="book"></param>
        /// <returns></returns>
        public int ExitBookInfo(BookInfo book)
        {
            string sql = @"update BookInfo set BookName=@BookName,TimeIn=@TimeIn,BookTypeId=@BookTypeId,
            Author=@Author,BookNumber=@BookNumber,Price=@Price,ISBS=@ISBS where BookId=@BookId";

            SqlParameter[] sp =
            {
                new SqlParameter("@BookName",   book.BookName),
                new SqlParameter("@TimeIn",     book.TimeIn),
                new SqlParameter("@BookTypeId", book.BookTypeId),
                new SqlParameter("@Author",     book.Author),
                new SqlParameter("@BookNumber", book.BookNumber),
                new SqlParameter("@Price",      book.Price),
                new SqlParameter("@ISBS",       book.ISBS),
                new SqlParameter("@BookId",     book.BookId)
            };

            return(DBhelp.Create().ExecuteNonQuery(sql, sp: sp));
        }
示例#19
0
        //添加读者信息
        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);
        }
示例#20
0
        //查询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));
        }
示例#21
0
        // 返回读者编号,读者姓名
        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);
        }
示例#22
0
        //返回用户编号,用户姓名
        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);
        }
示例#23
0
        // 借书
        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);
        }
示例#24
0
        //修改用户信息
        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));
        }
示例#25
0
        //添加图书信息
        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));
        }
        //查询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));
        }
示例#27
0
        /// <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));
        }
示例#28
0
        public DataSet selectBookType1()
        {
            string sql = "select BookTypeId,BookTypeName from BookType";

            return(DBhelp.Create().ExecuteAdater(sql));
        }
        //查询全部的读者类型
        public DataSet selectReaderType1()
        {
            string sql = @"select * from ReaderType";

            return(DBhelp.Create().ExecuteAdater(sql));
        }
示例#30
0
        public DataSet selectDepartment1()
        {
            string sql = "select * from Department";

            return(DBhelp.Create().ExecuteAdater(sql));;
        }