/// <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));
}
/// <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));
}
//查询图书借还表（表连接）
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));
}
//查询借还表信息
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));
}
/// <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));
}
// 根据读者类型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));
}
/// <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));
}
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);
}
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);
}
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);
}
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);
}
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);
}
/// <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));
}
//修改读者信息
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));
}
/// <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));
}
//添加读者信息
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);
}
//查询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 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);
}
//返回用户编号，用户姓名
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 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 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));
}
/// <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 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));
}
public DataSet selectDepartment1()
{
string sql = "select * from Department";
return(DBhelp.Create().ExecuteAdater(sql));;
}