C#.net开发基于WinForm的简单图书馆系统，采用了三层架构，CS模式，数据库用sql server,代码可用来学习和参考
github链接：https://github.com/CodeZno1/librarySystem
这是系统的架构
Model层的代码
namespace Model
{
public class Admin
{
public int ID { get; set; }
public string username { get; set; }
public string password { get; set; }
}
}
namespace Model
{
public class BookInfo
{
public int ID { get; set; }
public string name { get; set; }
public int count { get; set; }
public string category1 { get; set; }
public string category2 { get; set; }
public string category3 { get; set; }
public string addr { get; set; }
}
namespace Model
{
public class Category
{
public string[] category1 = new string[] { "玄幻", "都市", "仙侠" };
public string[,] category2 = new string[,] { { "东方玄幻", "异世大陆", "高武世界" }, { "都市生活", "恩怨情仇", "青春校园" }, { "修真文明", "现代修真", "古典仙侠" } };
public string[,,] category3 = new string[,,] { { { "圣墟", "太初", "牧神", "武炼", "银霸" }, { "天尊", "监武","造化", "诸天", "万古"}, { "霸皇", "纯阳", "藏经", "星河", "神门" } },{ { "真聊", "纯真","今朝",
"重生","美食" }, { "市武", "侠警", "黑金", "纵横", "杀戮" },{ "似水", "重返", "沸腾", "心动", "天才" } }, { { "极道", "天魔", "大劫", "夏纪", "飞仙" },{ "数据", "无限", "悠闲", "仙尊", "夺运" },
{ "问道", "聊斋", "蜀山", "证道", "太玄" } } };
public Category()
{
}
}
}
BLL层的代码
namespace BLL
{
public class Admin_BLL
{
Admin_DAL aa = new Admin_DAL();
//删除管理员
public int deleteAdmin(String username)
{
return aa.deleteAdmin(username);
}
//修改管理员
public int ExitAdmin(Admin a)
{
return aa.ExitAdmin(a);
}
//添加管理员
public int AddAdmin(Admin a)
{
return aa.AddAdmin(a);
}
public DataSet selectAdmin()
{
return aa.selectAdmin();
}
public int Scalar(Admin a)
{
return aa.Scalar(a);
}
}
}
namespace BLL
{
public class BookInfo_BLL
{
BookInfo_DAL bf = new BookInfo_DAL();
public int deleteBookInfo(string id)
{
return bf.deleteBookInfo(id);
}
public int upadeBookInfo(BookInfo info)
{
return bf.updateBookInfo(info);
}
public DataTable getAllInfo()
{
try
{
SqlDataReader objReader = bf.selectBookInfo(); ;
if (!objReader.HasRows)
{
return null;
}
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("ID"), new DataColumn("名字"), new DataColumn("数量"), new DataColumn("类别1"), new DataColumn("类别2"), new DataColumn("类别3"), new DataColumn("地址") });
while (objReader.Read())
{
dt.Rows.Add(new object[] { Convert.ToString(objReader["ID"]), (string)objReader["name"], Convert.ToString(objReader["count"]), (string)objReader["category1"], (string)objReader["category2"], (string)objReader["category3"], (string)objReader["addr"] });
}
objReader.Close();
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public int addInfo(BookInfo info)
{
return bf.AddBookInfo(info);
}
public DataTable queryInfo(BookInfo info)
{
try
{
SqlDataReader objReader = bf.query(info); ;
if (!objReader.HasRows)
{
return null;
}
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] { new DataColumn("ID"), new DataColumn("名字"), new DataColumn("数量"), new DataColumn("类别1"), new DataColumn("类别2"), new DataColumn("类别3"), new DataColumn("地址") });
while (objReader.Read())
{
dt.Rows.Add(new object[] { Convert.ToString(objReader["ID"]), (string)objReader["name"], Convert.ToString(objReader["count"]), (string)objReader["category1"], (string)objReader["category2"], (string)objReader["category3"], (string)objReader["addr"] });
}
objReader.Close();
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
public int getTotal1(string category1)
{
try
{
int count = 0;
SqlDataReader objReader = bf.selectTotal1(category1);
if (!objReader.HasRows)
{
return 0;
}
while (objReader.Read())
{
count++;
}
objReader.Close();
return count;
}
catch (Exception ex)
{
throw ex;
}
}
public int getTotal2(string category1, string category2)
{
try
{
int count = 0;
SqlDataReader objReader = bf.selectTotal2(category1, category2);
if (!objReader.HasRows)
{
return 0;
}
while (objReader.Read())
{
count++;
}
objReader.Close();
return count;
}
catch (Exception ex)
{
throw ex;
}
}
public int getTotal3(string category1, string category2, string category3)
{
try
{
int count = 0;
SqlDataReader objReader = bf.selectTotal3(category1, category2, category3);
if (!objReader.HasRows)
{
return 0;
}
while (objReader.Read())
{
count++;
}
objReader.Close();
return count;
}
catch (Exception ex)
{
throw ex;
}
}
}
}
DAL层的代码
namespace DAL
{
public class Admin_DAL
{
/// <summary>
/// 查询全部
/// </summary>
/// <returns></returns>
public DataSet selectAdmin()
{
string sql = @"select * from Admin";
return DBhelp.Create().ExecuteAdater(sql);
}
/// <summary>
/// 删除管理员
/// </summary>
/// <param name="username"></param>
/// <returns></returns>
public int deleteAdmin(string username)
{
string sql = "delete from Admin where username=@username";
SqlParameter[] sp ={
new SqlParameter("@username",username)
};
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 username=@username,password=@password where username=@username";
SqlParameter[] sp = {
new SqlParameter("username",a.username),
new SqlParameter("password",a.password)
};
return DBhelp.Create().ExecuteNonQuery(sql, sp: sp);
}
/// <summary>
/// 添加管理员
/// </summary>
/// <param name="a"></param>
/// <returns></returns>
public int AddAdmin(Admin a)
{
string sql = "insert into Admin select @username,@password";
SqlParameter[] sp ={
new SqlParameter("username",a.username),
new SqlParameter("password",a.password)
};
return DBhelp.Create().ExecuteNonQuery(sql, sp: sp);
}
/// <summary>
/// 查询相同用户
/// </summary>
/// <param name="a"></param>
/// <returns></returns>
public int Scalar(Admin a)
{
string sql = "select COUNT(*) from Admin where username=@username and password=@password";
SqlParameter[] sp ={
new SqlParameter("@username",a.username),
new SqlParameter("@password",a.password)
};
return DBhelp.Create().ExecuteScalar(sql, sp);
}
}
}
namespace DAL
{
public class BookInfo_DAL
{
/// <summary>
/// 查询全部
/// </summary>
/// <returns></returns>
public SqlDataReader selectBookInfo()
{
string sql = @"select * from BookInfo";
return DBhelp.Create().ExecuteReader(sql);
}
public int deleteBookInfo(string ID)
{
string sql = "delete from BookInfo where ID=@ID";
SqlParameter[] sp ={
new SqlParameter("@ID",ID)
};
return DBhelp.Create().ExecuteNonQuery(sql, sp: sp);
}
public int updateBookInfo(BookInfo info)
{
string sql = "update BookInfo set name=@name,count=@count,category1=@category1,category2=@category2,category3=@category3,addr=@addr where ID=@ID";
SqlParameter[] sp = {
new SqlParameter("ID",info.ID),
new SqlParameter("name",info.name),
new SqlParameter("count",info.count),
new SqlParameter("category1",info.category1),
new SqlParameter("category2",info.category2),
new SqlParameter("category3",info.category3),
new SqlParameter("addr",info.addr)
};
return DBhelp.Create().ExecuteNonQuery(sql, sp: sp);
}
public int AddBookInfo(BookInfo info)
{
string sql = "insert into BookInfo (name,count,category1,category2,category3,addr) values( @name,@count,@category1,@category2,@category3,@addr )";
SqlParameter[] sp = {
new SqlParameter("name",info.name),
new SqlParameter("count",info.count),
new SqlParameter("category1",info.category1),
new SqlParameter("category2",info.category2),
new SqlParameter("category3",info.category3),
new SqlParameter("addr",info.addr)
};
return DBhelp.Create().ExecuteNonQuery(sql, sp: sp);
}
public SqlDataReader query(BookInfo info)
{
string sql = @"select * from BookInfo where name like '%'+@name+'%' and ID = @ID and category1 = @category1 and category2 = @category2 and category3 = @category3";
SqlParameter[] sp = {
new SqlParameter("name",info.name),
new SqlParameter("ID",info.ID),
new SqlParameter("category1",info.category1),
new SqlParameter("category2",info.category2),
new SqlParameter("category3",info.category3),
};
return DBhelp.Create().ExecuteReader(sql, sp: sp);
}
public SqlDataReader selectTotal1(string category1)
{
string sql = @"select * from BookInfo where category1 = @category1";
SqlParameter[] sp = {
new SqlParameter("category1",category1)
};
return DBhelp.Create().ExecuteReader(sql, sp: sp);
}
public SqlDataReader selectTotal2(string category1, string category2)
{
string sql = @"select * from BookInfo where category1 = @category1 and category2 = @category2";
SqlParameter[] sp = {
new SqlParameter("category1",category1),
new SqlParameter("category2",category2),
};
return DBhelp.Create().ExecuteReader(sql, sp: sp);
}
public SqlDataReader selectTotal3(string category1, string category2 , string category3)
{
string sql = @"select * from BookInfo where category1 = @category1 and category2 = @category2 and category3 = @category3";
SqlParameter[] sp = {
new SqlParameter("category1",category1),
new SqlParameter("category2",category2),
new SqlParameter("category3",category3),
};
return DBhelp.Create().ExecuteReader(sql, sp: sp);
}
}
}
namespace DAL
{
class DBhelp
{
private DBhelp() { }
private static DBhelp dbhelp = null;
public static DBhelp Create()
{
if (dbhelp == null)
dbhelp = new DBhelp();
return dbhelp;
}
string conString = ConfigurationManager.ConnectionStrings["a"].ConnectionString;
//返回一行一列
public int ExecuteScalar(string sql, params SqlParameter[] sp)
{
SqlConnection con = new SqlConnection(conString);
try
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
com.Parameters.AddRange(sp);
return (int)com.ExecuteScalar();
}
catch (Exception)
{
con.Close();
throw;
}
finally
{
con.Close();
}
}
//返回读取器对象
public SqlDataReader ExecuteReader(string sql, params SqlParameter[] sp)
{
SqlConnection con = new SqlConnection(conString);
try
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
com.Parameters.AddRange(sp);
return com.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
con.Close();
throw ex;
}
}
//返回数据集，
public DataSet ExecuteAdater(string sql, params SqlParameter[] sp)
{
SqlConnection con = new SqlConnection(conString);
try
{
SqlCommand com = new SqlCommand(sql, con);
com.Parameters.AddRange(sp);
SqlDataAdapter adapter = new SqlDataAdapter(com);
DataSet ds = new DataSet();
adapter.Fill(ds, "a");
return ds;
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
}
}
//返回受影响行数
public int ExecuteNonQuery(string sql, CommandType type = CommandType.Text, params SqlParameter[] sp)
{
SqlConnection con = new SqlConnection(conString);
try
{
con.Open();
SqlCommand com = new SqlCommand(sql, con);
com.Parameters.AddRange(sp);
com.CommandType = type;
return com.ExecuteNonQuery();
}
catch (Exception)
{
con.Close();
throw;
}
finally
{
con.Close();
}
}
}
}
主界面代码
namespace 简单图书馆系统
{
public partial class FrmMain : Form
{
public FrmMain()
{
InitializeComponent();
SetStyle(ControlStyles.UserPaint, true);
SetStyle(ControlStyles.AllPaintingInWmPaint, true); // 禁止擦除背景.
SetStyle(ControlStyles.DoubleBuffer, true); // 双缓冲
lbTitle.BackColor = Color.Transparent;
pictureBoxExit.BackColor = Color.Transparent;
category = new Category();
cbCategory1.DataSource = category.category1;
}
public Admin admin = null;
public Category category = null;
BookInfo_BLL bfBLL = new BookInfo_BLL();
private void FrmMain_FormClosed(object sender, FormClosedEventArgs e)
{
Application.Exit();
}
private void pictureBox2_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void tbCount_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == 0x20) e.KeyChar = (char)0; //禁止空格键
if ((e.KeyChar == 0x2D) && (((TextBox)sender).Text.Length == 0)) return; //处理负数
if (e.KeyChar > 0x20)
{
try
{
double.Parse(((TextBox)sender).Text + e.KeyChar.ToString());
}
catch
{
e.KeyChar = (char)0; //处理非法字符
}
}
}
private void cbCategory1_SelectedIndexChanged(object sender, EventArgs e)
{
List<string> c2 = new List<string>();
for (int i = 0; i < 3; i++)
{
c2.Add(category.category2[cbCategory1.SelectedIndex, i]);
}
cbCategory2.DataSource = c2;
}
private void pictureBoxMin_Click(object sender, EventArgs e)
{
this.WindowState = FormWindowState.Minimized;
}
private void btnAdd_Click(object sender, EventArgs e)
{
if (tbName.Text.Trim() != "" && tbCount.Text.Trim() != "")
{
BookInfo newBf = new BookInfo();
newBf.name = tbName.Text;
newBf.count = Convert.ToInt32(tbCount.Text);
newBf.category1 = cbCategory1.SelectedItem.ToString();
newBf.category2 = cbCategory2.SelectedItem.ToString();
newBf.category3 = cbCategory3.SelectedItem.ToString();
newBf.addr = "未填写";
int result = bfBLL.addInfo(newBf);
MessageBox.Show("成功增加" + result.ToString() + "条信息！");
}
else
{
MessageBox.Show("请填写必要的内容");
}
}
private void cbCategory2_SelectedIndexChanged(object sender, EventArgs e)
{
List<string> c3 = new List<string>();
for (int i = 0; i < 5; i++)
{
c3.Add(category.category3[cbCategory1.SelectedIndex, cbCategory2.SelectedIndex, i]);
}
cbCategory3.DataSource = c3;
}
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
switch (tabControl1.SelectedIndex)
{
case 1:
{
DataTable dt = bfBLL.getAllInfo();
dataGridViewDel.DataSource = dt;
if (dt != null)
{
dataGridViewDel.Columns[0].Width = 50;
dataGridViewDel.Columns[1].Width = 350;
dataGridViewDel.Columns[2].Width = 100;
dataGridViewDel.Columns[3].Width = 200;
dataGridViewDel.Columns[4].Width = 200;
dataGridViewDel.Columns[5].Width = 200;
dataGridViewDel.Columns[6].Width = 400;
}
break;
}
case 2:
{
DataTable dt = bfBLL.getAllInfo();
dataGridViewChange.DataSource = dt;
if (dt != null)
{
dataGridViewChange.Columns[0].Width = 50;
dataGridViewChange.Columns[1].Width = 350;
dataGridViewChange.Columns[2].Width = 100;
dataGridViewChange.Columns[3].Width = 200;
dataGridViewChange.Columns[4].Width = 200;
dataGridViewChange.Columns[5].Width = 200;
dataGridViewChange.Columns[6].Width = 400;
}
break;
}
case 3:
{
cbQCategory1.DataSource = category.category1;
break;
}
case 4:
{
cbCountA1.DataSource = category.category1;
cbCountB1.DataSource = category.category1;
cbCountC1.DataSource = category.category1;
break;
}
}
}
private void btnDel_Click(object sender, EventArgs e)
{
DialogResult RSS = MessageBox.Show(this, "确定要删除选中行数据码？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
switch (RSS)
{
case DialogResult.Yes:
for (int i = dataGridViewDel.SelectedRows.Count; i > 0; i--)
{
string strV1 = Convert.ToString(dataGridViewDel.SelectedRows[i - 1].Cells[0].Value);
dataGridViewDel.Rows.RemoveAt(dataGridViewDel.SelectedRows[i - 1].Index);
//使用获得的ID删除数据库的数据
int s = bfBLL.deleteBookInfo(strV1); //cl是操作类的一个对像，Execute()是类中的一个方法
if (s != 0)
{
MessageBox.Show("成功删除选中行数据！");
}
}
break;
case DialogResult.No:
break;
}
}
private void btnQueryAll_Click(object sender, EventArgs e)
{
DataTable dt = bfBLL.getAllInfo();
dataGridViewQuery.DataSource = dt;
if (dt != null)
{
dataGridViewQuery.Columns[0].Width = 50;
dataGridViewQuery.Columns[1].Width = 350;
dataGridViewQuery.Columns[2].Width = 100;
dataGridViewQuery.Columns[3].Width = 200;
dataGridViewQuery.Columns[4].Width = 200;
dataGridViewQuery.Columns[5].Width = 200;
dataGridViewQuery.Columns[6].Width = 400;
}
}
private void dataGridViewChange_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
{
tbChange.Text = dataGridViewChange.SelectedCells[0].Value.ToString();
}
private void tbChange_TextChanged(object sender, EventArgs e)
{
dataGridViewChange.SelectedCells[0].Value = tbChange.Text;
}
private void btnChange_Click(object sender, EventArgs e)
{
for (int i = 0; i < dataGridViewChange.RowCount - 1; i++)
{
BookInfo updateInfo = new BookInfo();
updateInfo.ID = Convert.ToInt32(dataGridViewChange.Rows[i].Cells[0].Value.ToString());
updateInfo.name = dataGridViewChange.Rows[i].Cells[1].Value.ToString();
updateInfo.count = Convert.ToInt32(dataGridViewChange.Rows[i].Cells[2].Value.ToString());
updateInfo.category1 = dataGridViewChange.Rows[i].Cells[3].Value.ToString();
updateInfo.category2 = dataGridViewChange.Rows[i].Cells[4].Value.ToString();
updateInfo.category3 = dataGridViewChange.Rows[i].Cells[5].Value.ToString();
updateInfo.addr = dataGridViewChange.Rows[i].Cells[6].Value.ToString();
bfBLL.upadeBookInfo(updateInfo);
}
MessageBox.Show("修改完成!");
}
private void dataGridViewChange_CellLeave(object sender, DataGridViewCellEventArgs e)
{
}
private void dataGridViewChange_CellClick(object sender, DataGridViewCellEventArgs e)
{
if (dataGridViewChange.Columns[dataGridViewChange.CurrentCell.ColumnIndex].HeaderText == "ID")
{
MessageBox.Show("ID不可以更改！");
return;
}
tbChange.Text = dataGridViewChange.SelectedCells[0].Value.ToString();
}
private void cbQCategory1_SelectedIndexChanged(object sender, EventArgs e)
{
List<string> c2 = new List<string>();
for (int i = 0; i < 3; i++)
{
c2.Add(category.category2[cbCategory1.SelectedIndex, i]);
}
cbQCategory2.DataSource = c2;
}
private void cbQCategory2_SelectedIndexChanged(object sender, EventArgs e)
{
List<string> c3 = new List<string>();
for (int i = 0; i < 5; i++)
{
c3.Add(category.category3[cbCategory1.SelectedIndex, cbCategory2.SelectedIndex, i]);
}
cbQCategory3.DataSource = c3;
}
private void btnQuery_Click(object sender, EventArgs e)
{
if (tbQID.Text.Trim() != "" && tbQName.Text.Trim() != "" && cbQCategory1.Text.Trim() != "" && cbQCategory2.Text.Trim() != "" && cbQCategory3.Text.Trim() != "")
{
BookInfo qInfo = new BookInfo();
qInfo.ID = Convert.ToInt32(tbQID.Text);
qInfo.name = tbQName.Text.Trim();
qInfo.category1 = cbQCategory1.Text.Trim();
qInfo.category2 = cbQCategory2.Text.Trim();
qInfo.category3 = cbQCategory3.Text.Trim();
DataTable dt = bfBLL.queryInfo(qInfo);
dataGridViewQuery.DataSource = dt;
if (dt != null)
{
dataGridViewQuery.Columns[0].Width = 50;
dataGridViewQuery.Columns[1].Width = 350;
dataGridViewQuery.Columns[2].Width = 100;
dataGridViewQuery.Columns[3].Width = 200;
dataGridViewQuery.Columns[4].Width = 200;
dataGridViewQuery.Columns[5].Width = 200;
dataGridViewQuery.Columns[6].Width = 400;
MessageBox.Show("查询完成！");
}
}
else
{
MessageBox.Show("请填入所有查询条件!");
}
}
private void tbQID_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == 0x20) e.KeyChar = (char)0; //禁止空格键
if ((e.KeyChar == 0x2D) && (((TextBox)sender).Text.Length == 0)) return; //处理负数
if (e.KeyChar > 0x20)
{
try
{
double.Parse(((TextBox)sender).Text + e.KeyChar.ToString());
}
catch
{
e.KeyChar = (char)0; //处理非法字符
}
}
}
private void dataGridViewChange_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
tbChange.Text = dataGridViewChange.SelectedCells[0].Value.ToString();
}
private void cbCountB1_SelectedIndexChanged(object sender, EventArgs e)
{
List<string> c2 = new List<string>();
for (int i = 0; i < 3; i++)
{
c2.Add(category.category2[cbCategory1.SelectedIndex, i]);
}
cbCountB2.DataSource = c2;
}
private void cbCountC1_SelectedIndexChanged(object sender, EventArgs e)
{
List<string> c2 = new List<string>();
for (int i = 0; i < 3; i++)
{
c2.Add(category.category2[cbCategory1.SelectedIndex, i]);
}
cbCountC2.DataSource = c2;
}
private void cbCountC2_SelectedIndexChanged(object sender, EventArgs e)
{
List<string> c3 = new List<string>();
for (int i = 0; i < 5; i++)
{
c3.Add(category.category3[cbCategory1.SelectedIndex, cbCategory2.SelectedIndex, i]);
}
cbCountC3.DataSource = c3;
}
private void btnTotal_Click(object sender, EventArgs e)
{
if (cbCountA1.Text.Trim() != "" && cbCountB1.Text.Trim() != "" && cbCountC1.Text.Trim() != "" && cbCountB2.Text.Trim() != "" && cbCountC2.Text.Trim() != "" && cbCountC3.Text.Trim() != "")
{
lbTotal1.Text = "总数：" + bfBLL.getTotal1(cbCountA1.Text).ToString();
lbTotal2.Text = "总数：" + bfBLL.getTotal2(cbCountB1.Text, cbCountB2.Text).ToString();
lbTotal3.Text = "总数：" + bfBLL.getTotal3(cbCountC1.Text, cbCountC2.Text, cbCountC3.Text).ToString();
MessageBox.Show("统计完成！");
}
else
{
MessageBox.Show("请填入正确的类别！");
}
}
}
}
下面是程序的运行效果图
317
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
