图书借阅系统2
2022-10-17 本文已影响0人
山猪打不过家猪
2.5 图书信息组合查询
image.png
- Ul层
FrmBookManager.cs初始化图书分类和出版社的下拉框
private BookManager objBookManager = new BookManager();
List<Books> listBooks = null;
public FrmBookManage()
{
InitializeComponent();
//显示图书分类下拉框
List<Category> listCatg = objBookManager.GetAllCategory();
listCatg.Insert(0,new Category() {CategoryId =-1,CategoryName="" }); //空白列，这样就可以默认不选中
this.cboCategory.DataSource = listCatg;
this.cboCategory.DisplayMember = "CategoryName";
this.cboCategory.ValueMember = "CategoryId";
this.cboCategory.SelectedIndex = -1;
//出版社下拉框
List<Publisher> listPublisher = objBookManager.GetAllPublisher();
listPublisher.Insert(0, new Publisher() { PublisherId = -1, PublisherName = "" }); //空白列，这样就可以默认不选中
this.cboCategory.DataSource = listPublisher;
this.cboCategory.DisplayMember = "PublisherName";
this.cboCategory.ValueMember = "PublisherId";
this.cboCategory.SelectedIndex = -1;
}
- DAL层
BookService.cs
#region 组合查询
public List<Books> GetBooks(int categoryId, string barCode, string bookName)
{
//定义参数集合
List<SqlParameter> paramList = new List<SqlParameter>();
//定义查询语句
string sql = "select BookId,BarCode,BookName,Author,PublisherId,PublishDate,BookCategory,UnitPrice," +
"BookImage,BookCount,Remainder,BookPosition,RegTime,PublisherName,CategoryName from Books" +
"inner join Publisers on Publishers.PublisherId = Books.PublisherId" +
"inner join Categoryies on Books.BookCategory = Categories.CategoryId" +
"where 1 =1";
//根据条件查询
if (barCode !=null && barCode.Length>0)
{
sql += "and BarCode=@BarCode";
paramList.Add(new SqlParameter("@BarCode", barCode));
}
else
{
if (categoryId !=-1)
{
sql += "and CategoryId = @CategoryId";
paramList.Add(new SqlParameter("@CategoryId", categoryId));
}
else if (bookName !=null && bookName.Length>0)
{
sql += "and BookName like @BookName +'%'";
paramList.Add(new SqlParameter("@BookName", bookName));
}
}
//执行查询
SqlDataReader objReader = SQLHelper.GetReader(sql, paramList.ToArray());
List<Books> bookList = new List<Books>();
while (objReader.Read())
{
bookList.Add(new Books()
{
Author = objReader["Author"].ToString(),
BarCode = objReader["BarCode"].ToString(),
CategoryName = objReader["CategoryName"].ToString(),
BookName = objReader["BookName"].ToString(),
PublisherName = objReader["PublisherName"].ToString(),
BookImage = objReader["BookImage"] is DBNull ? "" : objReader["BookImage"].ToString()
});
}
objReader.Close();
return bookList;
}
#endregion
注意：动态传参
先定义一个List,然后再往List里面添加SqlParameter对象,然后将List转为SqlParameter数组
List<SqlParameter> SqlParameterList = new List<SqlParameter>();
SqlParameterList.Add(new SqlParameter("@statusupload", statusupload));
SqlParameterList .Add(new SqlParameter("@ParamLike","%keyword%"));//Like的写法
SqlParameter[] param = SqlParameterList .ToArray();
这么做的原因是因为数组int[] aa = new int[]{1,2,3,4}，普通数组的参数个数和大小在创建的时候已经固定了，所以不可以实现动态传参，这里需要将普通的SqlParameter[]数组，先转为List<SqlParameter>然后再将List转为数组
- 一个简单的例子理解，原本的int[] aa是固定的数组，无法动态添加，改为List后就可以。
int[] aa = { };
List<int> bb = new List<int>();
bb.Add(1);
bb.Add(2);
foreach (var item in bb.ToArray())
{
Console.WriteLine(item);
};
2.6 会员管理模块
image.png
- 编写思路
1）先根据功能把DAL层需要用到的增删改查的方法写好
namespace DAL
{
public class ReaderService
{
//会员办证（添加读者信息）
//修改读者信息
//借证挂失
//查询全部会员角色
//根据借阅号查询读者信息
//根据身份证号查询读者信息
//根据会员角色查询读者信息
}
}
-
ReaderService.cs获取全部角色
public DataTable GetAllReaders()
{
string sql = "select RoleId,RoleName from ReaderRoles";
return SQLHelper.GetDateSet(sql).Tables[0];
}
SQLHelper.cs
public static DataSet GetDataSet(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);//创建数据适配器
DataSet ds = new DataSet(); //创建内存数据集
try
{
conn.Open();
da.Fill(ds);//使用数据适配器填充数据集
return ds;
}
catch (Exception ex)
{
string erroInfo = "调用 public static DataSet GetDataSet(string sql)发生错误：" + ex.Message;
//将异常保存到日志
WriteLog(erroInfo);
throw new Exception(erroInfo);
}
finally
{
conn.Close();
}
}
UI层使用DataTable
public FrmReaderManger()
{
InitializeComponent();
//初始化角色下拉框
DataTable dt = objReaderManager.GetAllReaderRole();
this.cboReaderRole.DataSource = dt;
this.cboReaderRole.DisplayMember = "RoleName";
this.cboReaderRole.ValueMember = "RoleId";
//初始化用于查询的角色下拉框
//this.cboRole.DataSource = objReaderManager.GetAllReaderRole(); ;
this.cboRole.DataSource = dt.Copy();//复制表结构和数据，防止选择时候同步变化
this.cboRole.DisplayMember = "RoleName";
this.cboReaderRole.ValueMember = "RoleId";
}
-
封装不同参数查询读者信息
image.png
//提取查询读者信息代码
private Reader GetReaderBySql(string wherSql, SqlParameter[] param)
{
string sql = "select ReaderId,ReadingCard,ReaderName,RoleId from Readers" +
"inner join RaderRoles on ReaderRoles.RoleId = Readers.RoleId" +
wherSql;
//执行查询
SqlDataReader objReader = SQLHelper.GetReader(sql, param);
Reader reader = null;
if (objReader.Read())
{
reader = new Reader()
{
ReaderId = Convert.ToInt32(objReader["ReaderId"]),
ReadingCard = objReader["ReadingCard"].ToString()
};
}
objReader.Close();
return reader;
}
//根据身份证号查询读者信息
public Reader GetReaderByIdCard(string IdCard)
{
return GetReaderBySql("where IdCard = @IdCard", new SqlParameter[] { new SqlParameter("@IdCard", IdCard) });
}
//根据借阅号查询读者信息
public Reader GetReaderByBorrowId(string BorrowId)
{
return GetReaderBySql("where BorrowId=@BorrowId", new SqlParameter[] { new SqlParameter("BorrowId", BorrowId) });
}
- 根据会员角色查询读者信息(同时找 到该会员的总数)，结果集跳转
//根据会员角色查询读者信息(同时找 到该会员的总数)
public List<Reader> GetReaderByRole(string roleId, out int readerCount)
{
//执行两个查询
string sql = "select ReaderId,ReadingCard,ReaderName,Gerder,PhoneNumber from Readers" +
"where RoleId = @RoleId;" +
"select readerCount = count(1) from Readers where RoleId = @RoleId";
SqlParameter[] param = new SqlParameter[] { new SqlParameter("@RoleId", roleId) };
SqlDataReader objReader = SQLHelper.GetReader(sql, param);
List<Reader> readerList = null;
while (objReader.Read())
{
readerList.Add(new Reader()
{
ReaderId = Convert.ToInt32(objReader["ReaderId"]),
ReadingCard = objReader["ReadingCard"].ToString()
});
}
if (objReader.NextResult()) //结果集跳转
{
if (objReader.Read())
{
readerCount = Convert.ToInt32(objReader["readerCount"]);
}
else
{
readerCount = 0;
}
}
else
{
readerCount = 0;
}
objReader.Close();
return readerList;
}
- BLL 层
ReaderManager.cs
namespace BLL
{
class ReaderManager
{
private ReaderService objReaderService = new ReaderService();
public List<Reader> GerReaderByRole(string roleId, out int readerCount)
{
List<Reader> readerList = objReaderService.GetReaderByRole(roleId,out readerCount);
//根据借阅证状态编号修改成对应名称
for (int i = 0; i < readerList.Count; i++)
{
switch (readerList[i].ReaderId)
{
case 1:
readerList[i].StatusDesc = "正常";
break;
case 0:
readerList[i].StatusDesc = "禁用";
break;
}
}
return readerList;
}
}
}
- UI层
private void btnQueryReader_Click(object sender, EventArgs e)
{
this.lvReader.Items.Clear();//清空所有内容
int readerCount = 0;//提前定一个变量，防止报错
List<Reader> readerList = objReaderManager.GerReaderByRole(this.cboRole.SelectedValue.ToString(), out readerCount);
//给ListView绑定数据源
foreach (Reader item in readerList)
{
//【1】创建一个ListView项
ListViewItem IvItem = new ListViewItem(item.ReaderId.ToString());
//【2】向ListViewItem对象添加到ListView中
this.lvReader.Items.Add(IvItem);
//【3】在当前ListViewItem对象中添加子项
IvItem.SubItems.AddRange(new string[] { item.ReaderId.ToString(), item.ReadingCard });
}
//显示当前角色的读者总数
this.lblReaderCount.Text = readerCount.ToString();
}
2.7 图书出借模块
image.png
- 编写存储过程，读者借阅总数
use LibraryDB
go
if exists(select * from sysobjects where name = 'usp_QueryBorrowCount')
drop procedure usp_QueryBorrowCount
go
create procedure usp_QueryBorrowCount
@ReadingCard varchar(20)
as
declare @BorrowCount int = 0
select @BorrowCount = SUM(NonReturnCount) from BorrowDetail
inner join BorrowInfo on BorrowInfo.BorrowId = BorrowDetail.BorrowId
inner join Readers on BorrowInfo.ReaderId = Readers.ReaderId
where NonReturnCount>0 and ReadingCard = @ReadingCard
if(@BorrowCount is null)
select BorrowCount =0
else
select BorrowCount=@BorrowCount
go
--数据库执行测试存储过程，返回结果为2
exec usp_QueryBorrowCount '0006417240'
- DAL层
BorrowManager.cs
namespace BLL
{
/// <summary>
/// 借书数据访问类
/// </summary>
public class BorrowManager
{
/// <summary>
/// 根据借阅证查询当前读者借书总数
/// </summary>
/// <param name="readingCard"></param>
/// <returns></returns>
public int GetBorrowCount(string readingCard)
{
object count = SQLHelper.UpdateByProdecure("usp_QueryBorrowCount", new SqlParameter[] { new SqlParameter("@ReadingCard", readingCard) });
return Convert.ToInt32(count);
}
}
}
根据扫码显示借书信息
image.png
-
流程
image.png
- 代码
BookManager objBookManager = new BookManager();
private BorrowManager objBorrowManager = new BorrowManager();
//保存当前借书明细
private List<BorrowDetail> detailList = new List<BorrowDetail>();
//保存当前读者对象
private Reader objCurrentReader = null;
private void txtBarCode_KeyDown(object sender, EventArgs e)
{
//【1】检查当前借书总数是否已经达到上限
if (this.txtBarCode.Text.Trim().Length !=0 && e.KeyValue == 13)
{
if (Convert.ToInt32(this.lbl_Remainder.Text) == 0)
{
MessageBox.Show("借书已经达到上限");
return;
}
//【2】根据条码从数据库查询图书信息
Books objBook = objBookManager.GetBookByBarCode(this.txtBarCode.Text.Trim());
if (objBook != null)
{
//【3】判断当前集合中是否存在改图书对象（如果存在更新图书的数量）
int count = (from b in this.detailList where b.BarCode.Equals(objBook.BarCode) select b).Count();//LinQ
if (count == 0) //如果不存在这添加一个图书对象
{
BorrowDetail bookDetail = new BorrowDetail()
{
BarCode = objBook.BarCode,
BookId = objBook.BookId
};
detailList.Add(bookDetail);
//【4】同步刷新列表数据
this.dgvBookList.DataSource = null;
this.dgvBookList.DataSource = this.detailList;
}
else //如果存在，更新图书数量
{
BorrowDetail bookDetail = (from b in this.detailList where b.BarCode.Equals(objBook.BarCode) select b).First<BorrowDetail>();//LinQ
bookDetail.BorrowCount += 1;
//【4】同步刷新列表数据
this.dgvBookList.Refresh();//刷新借书数量
}
//【5】同步显示借书数据
this.lblBorrowCount.Text = (Convert.ToInt32(this.lblBorrowCount.Text) + 1).ToString();
this.lbl_Remainder.Text = (Convert.ToInt32(this.lbl_Remainder.Text) - 1).ToString();
this.txtBarCode.Clear();//清除当前的条码
//【6】开启保存和删除按钮
this.btnSave.Enabled = true;
this.btnDel.Enabled = true;
}
else
{
MessageBox.Show("当前图书不存在！");
}
}
}
在UI中删除已借图书
private void btnDel_Click(object sender, EventArgs e)
{
//根据图书条码找到借书明细对象
string barCode = this.dgvBookList.CurrentRow.Cells["BarCode"].Value.ToString();
//根据条码找到对象
BorrowDetail bookDetail = (from b in this.detailList where b.BarCode.Equals(objBook.BarCode) select b).First<BorrowDetail>();//LinQ
//删除对象
this.detailList.Remove(bookDetail);
//同步显示列表
this.dgvBookList.DataSource = null;
this.dgvBookList.DataSource = this.detailList;
//【5】同步显示借书数据
this.lblBorrowCount.Text = (Convert.ToInt32(this.lblBorrowCount.Text) - bookDetail.BorrowCount).ToString();//当前减
this.lbl_Remainder.Text = (Convert.ToInt32(this.lbl_Remainder.Text)+ bookDetail.BorrowCount).ToString();//剩余+
//根据剩余对象个数，禁用相关按钮（保存，删除）
if (this.detailList.Count==0)
{
this.btnSave.Enabled = false;
this.btnDel.Enabled = false;
}
}
根据事务保存图书信息
DAL层BorrowService.cs
namespace DAL
{
public class BorrowService
{
public bool BorrowBook(BorrowInfo main, List<BorrowDetail> detail)
{
//【1】借书主表信息插入SQL语句
string mainSql = "insert into BorrowInfo(BorrowId,ReadId,AdminName_B)values(@BorrowId,@ReadId,@AdminName_B)";
//【2】借书明细表信息插入SQL语句
StringBuilder detailSql = new StringBuilder();
detailSql.Append("insert into BorrowDetail(BorrowId, ReadId)");
detailSql.Append("values(@BorrowId,@ReadId)");
//【3】创建借阅主表参数数组
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@BorrowId",main.BorrowId),
new SqlParameter("@ReadId",main.ReadId),
new SqlParameter("@AdminName_B",main.AdminName_B),
};
//【4】创建详细表参数数组
List<SqlParameter[]> detainParam = new List<SqlParameter[]>();
foreach (BorrowDetail item in detail)
{
detainParam.Add(new SqlParameter[]
{
new SqlParameter("@BorrowId",item.BorrowId),
new SqlParameter("@ReadId",item.ReadId)
});
}
//【5】启用事务提交多个对象
return SQLHelper.UpdateByTran(mainSql, mainParam, detialSql.ToString(), detainParam);
}
}
}
- 理解
List<SqlParameter[]> detainParam = new List<SqlParameter[]>();写法
static void Main(string[] args)
{
int[] a1 = { 1,2,3};
int[] a2 = { 4,5,6};
List<int[]> aa = new List<int[]>();
aa.Add(a1);
aa.Add(a2);
List<int> b1 = new List<int>() { 10, 20, 30 };
List<int> b2 = new List<int>() { 40, 50, 60 };
List<List<int>> bb = new List<List<int>>();
bb.Add(b1);
bb.Add(b2);
foreach (int[] item in aa)
{
foreach (int i in item)
{
Console.WriteLine(i);
}
}
foreach (List<int> item in bb)
{
foreach (int i in item)
{
Console.WriteLine(i);
}
}
}
image.png
- UI层
FrmBorrowBook.cs
private void btnSave_Click(object sender, EventArgs e)
{
//数据验证
//封装对象【主表】
BorrowInfo main = new BorrowInfo()
{
ReaderId = this.objCurrentReader.ReaderId,
BorrowId = DateTime.Now.ToString("yyyyMMddhhmmssms")
};
//封装明细表对象（给当前明细对象未封装的属性补充数据）
for (int i = 0; i < this.detailList.Count; i++)
{
detailList[i].BorrowId = main.BorrowId;
detailList[i].NonReturnCount = main.NonReturnCount;
}
try
{
objBorrowManager.BorrowBook(main, detailList);//将封装的对象传递给业务逻辑，完成数据保存
//各种数据复位，清空
this.txtBarCode.Clear();
}
catch (Exception)
{
throw;
}
}
