这里写自定义目录标题
我利用C#以及数据库写了一个图书馆管理系统,本系统是基于所学的数据库原理课程使用的数据库来存储数据,使用SQL语句来进行数据的查询、添加、修改、以及删除。在界面显示该系统使用的是C#语言,使用了一些控件,来清楚地显示结果。本文主要介绍了图书管理系统的设计方法以及具体实现,详细阐述了整个系统的设计思路以及对各部分设计进行了详细的分析到最终完成整个系统。该系统的实现是主要是因为图书馆的图书数量很多,学生数量也很多,图书的情况是会随时改变的。本文介绍了系统的设计思路以及具体的设计过程。图书管理系统包括了登录界面,图书信息,学生信息,图书借阅以及统计情况。其中有编辑类的功能,例如增加、删除、修改等;浏览类的功能如查询、浏览等。
下面是我的系统的一些主要代码:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
public partial class Form2 : Form
{
public Form2()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string num = textBox1.Text;
string name = textBox2.Text;
string xibie = comboBox1.Text;
string major = comboBox2.Text;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
if (num != String.Empty && num.Length != 0)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql = string.Format("select* from 学生信息 where 借书证号 = '{0}'", num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
else
{
if (name != String.Empty && name.Length != 0&& comboBox1.Text.Trim() == "全部"&& comboBox2.Text.Trim() == "全部")
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql1 = string.Format("select* from 学生信息 where 姓名 = '{0}'", name); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql1, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
else
{
if (comboBox1.Text.Trim() == "全部"&& comboBox2.Text.Trim() == "全部")
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql2 = string.Format("select * from 学生信息 "); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql2, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
else if(comboBox2.Text.Trim() == "全部")
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql3 = string.Format("select * from 学生信息 where 系别='{0}'", xibie); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql3, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
else if (comboBox2.Text.Trim() == "全部")
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql4 = string.Format("select * from 学生信息 "); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql4, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
else
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql5 = string.Format("select * from 学生信息 where 专业 = '{0}'", major); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql5, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
}
}
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox3.Text == "" || textBox4.Text == "" || comboBox3.Text == "" || comboBox4.Text == "")
{
MessageBox.Show("不能为空!");
}
else
{
string num = textBox3.Text;
string name = textBox4.Text;
string xibie = comboBox3.Text;
string major = comboBox4.Text;
string count = textBox5.Text;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql1 = string.Format("select * from 学生信息 where 借书证号= '{0}' ", num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(); // 实例化适配器
myda.SelectCommand = new SqlCommand(sql1, conn);
DataSet myDataSet = new DataSet();
myda.Fill(myDataSet, "学生信息");
if (myDataSet.Tables[0].Rows.Count != 0)
{
MessageBox.Show("借书证号已存在");
}
else
{
String Insert = string.Format("insert into 学生信息(借书证号,姓名,系别,专业,图书上限数) values('{0}','{1}','{2}','{3}','{4}')", num, name, xibie, major,count);
SqlCommand cmd = new SqlCommand(Insert, conn);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("添加成功!");
}
String Insert1 = string.Format("insert into 学生借书统计(借书证号,姓名,借书总量) values('{0}','{1}','0')", num, name);
SqlCommand cmd1 = new SqlCommand(Insert1, conn);
if (cmd1.ExecuteNonQuery() > 0)
{
//MessageBox.Show("添加成功!");
}
conn.Close();
}
conn.ConnectionString = connsql;
conn.Open();
String sql = "select * from 学生信息 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
}
private void button5_Click(object sender, EventArgs e)
{
this.Close();
}
private void button3_Click(object sender, EventArgs e)
{
if (textBox3.Text == "")
{
MessageBox.Show("不能为空!");
}
else
{
string num = textBox3.Text;
string name = textBox4.Text;
string xibie = comboBox3.Text;
string major = comboBox4.Text;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql9= string.Format("select * from 借书信息 where 借书证号= '{0}' ", num); // 查询语句
SqlDataAdapter myda9 = new SqlDataAdapter(); // 实例化适配器
myda9.SelectCommand = new SqlCommand(sql9, conn);
DataSet myDataSet9 = new DataSet();
myda9.Fill(myDataSet9, "借书信息");
if (myDataSet9.Tables[0].Rows.Count != 0)
{
MessageBox.Show("该学生有图书没有归还,不能够删除!");
conn.Close();
}
else
{
String sql1 = string.Format("select * from 学生信息 where 借书证号= '{0}' ", num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(); // 实例化适配器
myda.SelectCommand = new SqlCommand(sql1, conn);
DataSet myDataSet = new DataSet();
myda.Fill(myDataSet, "学生信息");
String Delete = string.Format("delete from 学生信息 where 借书证号= '{0}' ", num);
SqlCommand cmd = new SqlCommand(Delete, conn);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("删除成功!");
}
String Delete1 = string.Format("delete from 学生借书统计 where 借书证号= '{0}' ", num);
SqlCommand cmd2 = new SqlCommand(Delete1, conn);
if (cmd2.ExecuteNonQuery() > 0)
{
//MessageBox.Show("删除成功!");
}
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String sql = "select * from 学生信息 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
}
private void button4_Click(object sender, EventArgs e)
{
if (textBox3.Text == "")
{
MessageBox.Show("借书证号不能为空!");
}
else
{
string num = textBox3.Text;
string name = textBox4.Text;
string xibie = comboBox3.Text;
string major = comboBox4.Text;
string count = textBox5.Text;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql1 = string.Format("select * from 学生信息 where 借书证号= '{0}' ", num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(); // 实例化适配器
myda.SelectCommand = new SqlCommand(sql1, conn);
DataSet myDataSet = new DataSet();
myda.Fill(myDataSet, "学生信息");
if (myDataSet.Tables[0].Rows.Count != 0)
{
String Update = string.Format("update 学生信息 set 系别='{0}',专业='{1} ',图书上限数='{2}' where 借书证号= '{3}' ;", xibie, major,count, num);
SqlCommand cmd = new SqlCommand(Update, conn);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("修改成功!");
}
conn.Close();
}
conn.ConnectionString = connsql;
conn.Open();
String sql = "select * from 学生信息 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void button6_Click(object sender, EventArgs e)
{
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据源
String sql = "select * from 学生信息 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
图书信息:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Runtime.InteropServices;
namespace WindowsFormsApplication1
{
public partial class Form3 : Form
{
public Form3()
{
InitializeComponent();
}
private void button2_Click(object sender, EventArgs e)
{
this.Close();
}
private void button1_Click(object sender, EventArgs e)
{
}
private void button2_Click_1(object sender, EventArgs e)
{
if (textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "" || textBox6.Text == "")
{
MessageBox.Show("不能为空!");
}
else
{
string num = textBox3.Text;
string name = textBox4.Text;
string author = textBox5.Text;
string chubanshe = textBox6.Text;
string place = textBox7.Text;
string count = textBox8.Text;
string surplus = textBox9.Text;
string date = textBox10.Text;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql1 = string.Format("select * from 图书情况 where 图书编号= '{0}' ", num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(); // 实例化适配器
myda.SelectCommand = new SqlCommand(sql1, conn);
DataSet myDataSet = new DataSet();
myda.Fill(myDataSet, "图书情况");
if (myDataSet.Tables[0].Rows.Count != 0)
{
MessageBox.Show("图书编号已存在");
}
else
{
String Insert = string.Format("insert into 图书情况(图书编号,图书名称,图书作者,出版日期,出版社,存放位置,图书总量,图书剩余量) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", num, name,author,date,chubanshe,place,count,surplus );
SqlCommand cmd = new SqlCommand(Insert, conn);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("添加成功!");
}
String Insert1 = string.Format("insert into 图书借阅统计(图书编号,图书名称,总借出量,库存量) values('{0}','{1}','0','{2}')", num, name,surplus);
SqlCommand cmd1 = new SqlCommand(Insert1, conn);
if (cmd1.ExecuteNonQuery() > 0)
{
//MessageBox.Show("添加成功!");
}
conn.Close();
}
conn.ConnectionString = connsql;
conn.Open();
String sql = string.Format("select * from 图书情况"); // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
}
private void button5_Click(object sender, EventArgs e)
{
this.Close();
}
private void button1_Click_1(object sender, EventArgs e)
{
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
if (textBox1.Text != String.Empty && textBox1.Text.Length != 0)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql = string.Format("select* from 图书情况 where 图书编号 = '{0}'", textBox1.Text); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
else
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql = string.Format("select* from 图书情况 where 图书名称 = '{0}'", textBox2.Text); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
}
private void textBox5_TextChanged(object sender, EventArgs e)
{
}
private void textBox1_TextChanged_1(object sender, EventArgs e)
{
}
private void button3_Click(object sender, EventArgs e)
{
if (textBox3.Text == "")
{
MessageBox.Show("不能为空!");
}
else
{
string num = textBox3.Text;
string name = textBox4.Text;
string author = textBox5.Text;
string chubanshe = textBox6.Text;
string place = textBox7.Text;
string count = textBox8.Text;
string surplus = textBox9.Text;
string date= textBox10.Text;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql9 = string.Format("select * from 借书信息 where 图书编号= '{0}' ", num); // 查询语句
SqlDataAdapter myda9 = new SqlDataAdapter(); // 实例化适配器
myda9.SelectCommand = new SqlCommand(sql9, conn);
DataSet myDataSet9 = new DataSet();
myda9.Fill(myDataSet9, "借书信息");
if (myDataSet9.Tables[0].Rows.Count != 0)
{
MessageBox.Show("该图书没有完全归还,不能够删除!");
conn.Close();
}
else
{
String sql1= string.Format("select * from 图书情况 where 图书编号= '{0}' ", num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(); // 实例化适配器
myda.SelectCommand = new SqlCommand(sql1, conn);
DataSet myDataSet = new DataSet();
myda.Fill(myDataSet, "图书情况");
String Delete = string.Format("delete from 图书情况 where 图书编号= '{0}' ", num);
SqlCommand cmd = new SqlCommand(Delete, conn);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("删除成功!");
}
String Delete1 = string.Format("delete from 图书借阅统计 where 图书编号= '{0}' ", num);
SqlCommand cmd1 = new SqlCommand(Delete1, conn);
if (cmd1.ExecuteNonQuery() > 0)
{
//MessageBox.Show("删除成功!");
}
conn.Close();
}
conn.ConnectionString = connsql;
conn.Open();
String sql = "select * from 图书情况 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
}
private void button4_Click(object sender, EventArgs e)
{
if (textBox3.Text == "")
{
MessageBox.Show("图书编号不能为空!");
}
else
{
string num = textBox3.Text;
string name = textBox4.Text;
string author = textBox5.Text;
string chubanshe = textBox6.Text;
string place = textBox7.Text;
string count = textBox8.Text;
string surplus = textBox9.Text;
string date = textBox10.Text;
try
{
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql1 = string.Format("select * from 图书情况 where 图书编号= '{0}' ", num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(); // 实例化适配器
myda.SelectCommand = new SqlCommand(sql1, conn);
DataSet myDataSet = new DataSet();
myda.Fill(myDataSet, "图书情况");
if (myDataSet.Tables[0].Rows.Count != 0)
{
String Update = string.Format("update 图书情况 set 存放位置='{0}',图书总量='{1} ',图书剩余量='{2}' where 图书编号= '{3}' ;",place,count,surplus,num);
SqlCommand cmd = new SqlCommand(Update, conn);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("修改成功!");
}
String Update1 = string.Format("update 图书借阅统计 set 库存量='{0}' where 图书编号= '{1}' ;", surplus, num);
SqlCommand cmd1 = new SqlCommand(Update1, conn);
if (cmd1.ExecuteNonQuery() > 0)
{
//MessageBox.Show("修改成功!");
}
conn.Close();
}
conn.ConnectionString = connsql;
conn.Open();
String sql = "select * from 图书情况"; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
}
private void textBox8_TextChanged(object sender, EventArgs e)
{
}
private void textBox10_TextChanged(object sender, EventArgs e)
{
}
private void button6_Click(object sender, EventArgs e)
{
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据源
String sql = "select * from 图书情况 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
public partial class Form4 : Form
{
public Form4()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string num = textBox1.Text;
string book_num = textBox2.Text;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
if (num != String.Empty && num.Length != 0)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql = string.Format("select* from 借书信息 where 借书证号 = '{0}'", num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
else
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql = string.Format("select* from 借书信息 where 图书编号 = '{0}'", book_num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void button3_Click(object sender, EventArgs e)
{
this.Close();
}
private void textBox9_TextChanged(object sender, EventArgs e)
{
}
private void button4_Click(object sender, EventArgs e)
{
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据源
String sql = "select * from 借书信息 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
private void button2_Click(object sender, EventArgs e)
{
if (textBox3.Text == "" || textBox4.Text == "" || textBox5.Text == "" || textBox6.Text == "" )
{
MessageBox.Show("不能为空!");
}
else
{
string num = textBox3.Text;
string name = textBox5.Text;
string book_num = textBox4.Text;
string book_name = textBox6.Text;
DateTime borrow_time = dateTimePicker1.Value;
DateTime return_time = dateTimePicker2.Value;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String query = string.Format("select 预计还书时间 from 借书信息 where 借书证号='{0}'", num);
SqlDataAdapter mydaa = new SqlDataAdapter(); // 实例化适配器
mydaa.SelectCommand = new SqlCommand(query, conn);
DataSet myDataSett = new DataSet();
mydaa.Fill(myDataSett, "学生信息");
int count = 0;
for (int i = 0; i < myDataSett.Tables[0].Rows.Count; i++)
{
int a = DateTime.Compare(DateTime.Parse(myDataSett.Tables[0].Rows[i][0].ToString()), DateTime.Now);
if (a < 0)
{
count++;
}
}
if(count==0)
{
String sql1 = string.Format("select * from 学生信息 where 借书证号= '{0}'and 姓名='{1}' ", num, name); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(); // 实例化适配器
myda.SelectCommand = new SqlCommand(sql1, conn);
DataSet myDataSet = new DataSet();
myda.Fill(myDataSet, "学生信息");
if (myDataSet.Tables[0].Rows.Count != 0)
{
String sql = string.Format("select * from 图书情况 where 图书编号= '{0}'and 图书名称='{1}' ", book_num, book_name); // 查询语句
SqlDataAdapter myda1 = new SqlDataAdapter(); // 实例化适配器
myda1.SelectCommand = new SqlCommand(sql, conn);
DataSet myDataSet1 = new DataSet();
myda1.Fill(myDataSet1, "图书情况");
if (myDataSet1.Tables[0].Rows.Count != 0)
{
//查询剩余量
String sql2 = string.Format("select 图书剩余量 from 图书情况 where 图书编号='{0}'", book_num); // 查询语句
SqlDataAdapter myda2 = new SqlDataAdapter(); // 实例化适配器
myda2.SelectCommand = new SqlCommand(sql2, conn);
DataSet myDataSet2 = new DataSet();
myda2.Fill(myDataSet2, "图书情况");
int value = (int)myDataSet2.Tables[0].Rows[0][0];
value = value - 1;//图书剩余量(库存量)
conn.Close();
if (value >= 0)
{
conn.ConnectionString = connsql;
conn.Open();
String Insert = string.Format("insert into 借书信息(借书证号,姓名,图书编号,图书名称,借书时间,预计还书时间) values('{0}','{1}','{2}','{3}','{4}','{5}')", num, name, book_num, book_name, borrow_time, return_time);
SqlCommand cmd = new SqlCommand(Insert, conn);
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String update1 = string.Format("update 图书情况 set 图书剩余量='{0}' where 图书编号='{1}';", value, book_num);
SqlCommand cmd1 = new SqlCommand(update1, conn);
if (cmd1.ExecuteNonQuery() > 0)
{
// MessageBox.Show("修改成功!");
}
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String sql3 = "select * from 借书信息 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql3, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String Count = string.Format("select 图书上限数 from 学生信息 where 借书证号 = '{0}';", num);
SqlDataAdapter myda6 = new SqlDataAdapter(); // 实例化适配器
myda6.SelectCommand = new SqlCommand(Count, conn);
DataSet myDataSet6 = new DataSet();
myda6.Fill(myDataSet6, "学生信息");
int value1 = (int)myDataSet6.Tables[0].Rows[0][0];
value1 = value1 - 1;
conn.Close();
if (value1 >= 0)
{
conn.ConnectionString = connsql;
conn.Open();
String update2 = string.Format("update 学生信息 set 图书上限数='{0}' where 借书证号='{1}';", value1, num);
SqlCommand cmd2 = new SqlCommand(update2, conn);
if (cmd2.ExecuteNonQuery() > 0)
{
//MessageBox.Show("修改成功!");
}
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("借书成功!");
}
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String sql7 = "select * from 借书信息 "; // 查询语句
SqlDataAdapter my1 = new SqlDataAdapter(sql7, conn); // 实例化适配器
DataTable dt1 = new DataTable(); // 实例化数据表
my.Fill(dt1); // 保存数据
dataGridView1.DataSource = dt1; // 设置到DataGridView中
conn.Close();
int value2 = 10 - value1;
label9.Text = ("该学生已借阅" + value2 + "本书,该学生还可以借阅" + value1 + "本书!");
conn.ConnectionString = connsql;
conn.Open();
String book = string.Format("select 总借出量 from 图书借阅统计 where 图书编号='{0}' ", book_num);
SqlDataAdapter myda8 = new SqlDataAdapter(); // 实例化适配器
myda8.SelectCommand = new SqlCommand(book, conn);
DataSet myDataSet8 = new DataSet();
myda8.Fill(myDataSet8, "图书借阅统计");
int Count1 = (int)myDataSet8.Tables[0].Rows[0][0];
Count1 = Count1 + 1;
String Updata1 = string.Format("update 图书借阅统计 set 总借出量='{0}',库存量='{1}' where 图书编号='{2}';", Count1, value, book_num);
SqlCommand smd = new SqlCommand(Updata1, conn);
if (smd.ExecuteNonQuery() > 0)
{
//MessageBox.Show("修改成功!");
}
String stu = string.Format("select 借书总量 from 学生借书统计 where 借书证号='{0}' ;", num);
SqlDataAdapter stu1 = new SqlDataAdapter(); // 实例化适配器
stu1.SelectCommand = new SqlCommand(stu, conn);
DataSet stu2 = new DataSet();
stu1.Fill(stu2, "学生借书统计");
int Count2 = (int)stu2.Tables[0].Rows[0][0];
Count2 = Count2 + 1;
String Updata2= string.Format("update 学生借书统计 set 借书总量='{0}' where 借书证号='{1}';", Count2, num);
SqlCommand smd1 = new SqlCommand(Updata2, conn);
if (smd1.ExecuteNonQuery() > 0)
{
//MessageBox.Show("修改成功!");
}
conn.Close();
}
else
{
MessageBox.Show("该同学借书已达上限数!");
}
}
else
{
MessageBox.Show("该书已经借完!");
}
}
else
{
MessageBox.Show("该图书编号或图书名称不存在!");
conn.Close();
}
}
else
{
MessageBox.Show("该借书证号或学生不存在!");
conn.Close();
}
}
else
{
MessageBox.Show("该学生有" + count + "本书已超期,不能继续借书!");
}
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
public partial class Form5 : Form
{
public Form5()
{
InitializeComponent();
}
private void button4_Click(object sender, EventArgs e)
{
this.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
try
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql = string.Format("select* from 还书信息 "); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
catch (Exception ex)
{
MessageBox.Show("错误信息:" + ex.Message, "出现错误");
}
}
private void button2_Click(object sender, EventArgs e)
{
string num = textBox1.Text;
string book_num = textBox2.Text;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
if (num != String.Empty && num.Length != 0)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql = string.Format("select* from 还书信息 where 借书证号 = '{0}'", num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
else
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql = string.Format("select* from 还书信息 where 图书编号 = '{0}'", book_num); // 查询语句
SqlDataAdapter myda = new SqlDataAdapter(sql, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
myda.Fill(dt); // 保存数据
dataGridView1.DataSource = dt; // 设置到DataGridView中
conn.Close(); // 关闭数据库连接
}
}
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void textBox3_TextChanged(object sender, EventArgs e)
{
}
private void button3_Click(object sender, EventArgs e)
{
string num = textBox3.Text;
string name = textBox5.Text;
string book_name = textBox6.Text;
string book_num = textBox4.Text;
DateTime actual = dateTimePicker1.Value;
string connsql = "Data Source=DESKTOP-MV0R1LQ;Initial Catalog=图书管理;Integrated Security=True";
if (num == "" && book_num == "")
{
MessageBox.Show("借书证号和图书编号不能为空!");
}
else
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = connsql;
conn.Open(); // 打开数据库连接
String sql = string.Format("select 预计还书时间 from 借书信息 where 借书证号 = '{0}'and 图书编号='{1}'", num, book_num); // 查询语句
SqlDataAdapter myda2 = new SqlDataAdapter(); // 实例化适配器
myda2.SelectCommand = new SqlCommand(sql, conn);
DataSet myDataSet2 = new DataSet();
myda2.Fill(myDataSet2, "借书信息");
string value = myDataSet2.Tables[0].Rows[0][0].ToString();
DateTime value1 = DateTime.Parse(value);
DateTime.Compare(actual, value1);
//MessageBox.Show("时间差" + DateTime.Compare(actual, value1));
conn.Close(); // 关闭数据库连接
if (DateTime.Compare(actual, value1) <= 0)
{
conn.ConnectionString = connsql;
conn.Open();
String sql1 = string.Format("insert into 还书信息 (借书证号,姓名,图书编号,图书名称,实际还书时间) values('{0}','{1}','{2}','{3}','{4}');", num, name, book_num, book_name, actual);
SqlDataAdapter myda = new SqlDataAdapter(); // 实例化适配器
myda.SelectCommand = new SqlCommand(sql1, conn);
DataSet myDataSet = new DataSet();
myda.Fill(myDataSet, "还书信息");
String Delete = string.Format("delete from 借书信息 where 借书证号= '{0}'and 图书编号='{1}' ", num, book_num);
SqlCommand cmd = new SqlCommand(Delete, conn);
if(cmd.ExecuteNonQuery() > 0)
{
//MessageBox.Show("删除成功!");
}
String sql2 = string.Format("select 图书剩余量 from 图书情况 where 图书编号='{0}'", book_num); // 查询语句
SqlDataAdapter myda3 = new SqlDataAdapter(); // 实例化适配器
myda3.SelectCommand = new SqlCommand(sql2, conn);
DataSet myDataSet3 = new DataSet();
myda3.Fill(myDataSet3, "图书情况");
int value2 = (int)myDataSet3.Tables[0].Rows[0][0];
value2 = value2 + 1;
String update1 = string.Format("update 图书情况 set 图书剩余量='{0}' where 图书编号='{1}';", value2, book_num);
SqlCommand cmd1 = new SqlCommand(update1, conn);
if (cmd1.ExecuteNonQuery() > 0)
{
//MessageBox.Show("修改成功!");
}
String update3= string.Format("update 图书借阅统计 set 库存量='{0}' where 图书编号='{1}';", value2, book_num);
SqlCommand cmd3 = new SqlCommand(update3, conn);
if (cmd3.ExecuteNonQuery() > 0)
{
//MessageBox.Show("修改成功!");
}
/*
String Count = string.Format("select 图书上限数 from 学生信息 where 借书证号 = '{0}';", num);
SqlDataAdapter myda6 = new SqlDataAdapter(); // 实例化适配器
myda6.SelectCommand = new SqlCommand(Count, conn);
DataSet myDataSet6 = new DataSet();
myda6.Fill(myDataSet6, "学生信息");
int value4 = (int)myDataSet6.Tables[0].Rows[0][0];
value4 = value4 + 1;
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String update2 = string.Format("update 学生信息 set 图书上限数='{0}' where 借书证号='{1}';", value4, num);
SqlCommand cmd2 = new SqlCommand(update2, conn);
if (cmd2.ExecuteNonQuery() > 0)
{
//MessageBox.Show("修改成功!");
}
*/
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("还书成功!");
}
String sql3 = "select * from 还书信息 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql3, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt;
conn.Close();
}
else
{
conn.ConnectionString = connsql;
conn.Open();
String sql3 = string.Format("insert into 缴费情况(借书证号,姓名,图书编号,图书名称,超期费用) values('{0}','{1}','{2}','{3}',0.02*datediff(day,'{4}','{5}'))", num, name, book_num, book_name, value1, actual);
SqlCommand cmd4 = new SqlCommand(sql3, conn);
if (cmd4.ExecuteNonQuery() > 0)
{
//MessageBox.Show("添加成功!");
}
/*SqlDataAdapter myda7 = new SqlDataAdapter(); // 实例化适配器
myda7.SelectCommand = new SqlCommand(sql3, conn);
DataSet myDataSet7 = new DataSet();
myda7.Fill(myDataSet7, "缴费情况");
*/
//TimeSpan ts = actual - value1;
conn.Close();
//得到应该交多少金额
conn.ConnectionString = connsql;
conn.Open();
String Price = string.Format("select 超期费用 from 缴费情况 where 借书证号='{0}'and 图书编号='{1}'", num, book_num); // 查询语句
SqlDataAdapter myda9 = new SqlDataAdapter(); // 实例化适配器
myda9.SelectCommand = new SqlCommand(Price, conn);
DataSet myDataSet9 = new DataSet();
myda9.Fill(myDataSet9, "缴费情况");
float value5 = float.Parse(myDataSet9.Tables[0].Rows[0][0].ToString());
MessageBox.Show("该图书已超期,请支付" + value5 + "元超期费用!");
if(MessageBox.Show("是否缴费:","提示",MessageBoxButtons.YesNo)==DialogResult.Yes)
{
String sql1 = string.Format("insert into 还书信息(借书证号,姓名,图书编号,图书名称,实际还书时间) values('{0}','{1}','{2}','{3}','{4}')", num, name, book_num, book_name, actual);
SqlCommand cmd3 = new SqlCommand(sql1, conn);
//SqlDataAdapter myda = new SqlDataAdapter(); // 实例化适配器
//myda.SelectCommand = new SqlCommand(sql1, conn);
//DataSet myDataSet = new DataSet();
//myda.Fill(myDataSet, "还书信息");
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String Delete = string.Format("delete from 借书信息 where 借书证号= '{0}'and 图书编号='{1}' ", num, book_num);
SqlCommand cmd = new SqlCommand(Delete, conn);
if (cmd.ExecuteNonQuery() > 0)
{
//MessageBox.Show("删除成功!");
}
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String sql2 = string.Format("select 图书剩余量 from 图书情况 where 图书编号='{0}'", book_num); // 查询语句
SqlDataAdapter myda3 = new SqlDataAdapter(); // 实例化适配器
myda3.SelectCommand = new SqlCommand(sql2, conn);
DataSet myDataSet3 = new DataSet();
myda3.Fill(myDataSet3, "图书情况");
int value2 = (int)myDataSet3.Tables[0].Rows[0][0];
value2 = value2 + 1;
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String update1 = string.Format("update 图书情况 set 图书剩余量='{0}' where 图书编号='{1}';", value2, book_num);
SqlCommand cmd1 = new SqlCommand(update1, conn);
if (cmd1.ExecuteNonQuery() > 0)
{
MessageBox.Show("修改成功!");
}
String update3 = string.Format("update 图书借阅统计 set 库存量='{0}' where 图书编号='{1}';", value2, book_num);
SqlCommand cmd5= new SqlCommand(update3, conn);
if (cmd5.ExecuteNonQuery() > 0)
{
MessageBox.Show("修改成功!");
}
String Count = string.Format("select 图书上限数 from 学生信息 where 借书证号 = '{0}'", num);
SqlDataAdapter myda6 = new SqlDataAdapter(); // 实例化适配器
myda6.SelectCommand = new SqlCommand(Count, conn);
DataSet myDataSet6 = new DataSet();
myda6.Fill(myDataSet6, "学生信息");
int value4 = (int)myDataSet6.Tables[0].Rows[0][0];
value4 = value4 + 1;
conn.Close();
conn.ConnectionString = connsql;
conn.Open();
String update2 = string.Format("update 学生信息 set 图书上限数='{0}' where 借书证号='{1}';", value4, num);
SqlCommand cmd2 = new SqlCommand(update2, conn);
if (cmd2.ExecuteNonQuery() > 0)
{
// MessageBox.Show("修改成功!");
}
if (cmd3.ExecuteNonQuery() > 0)
{
MessageBox.Show("还书成功!");
}
String sql4 = "select * from 还书信息 "; // 查询语句
SqlDataAdapter my = new SqlDataAdapter(sql4, conn); // 实例化适配器
DataTable dt = new DataTable(); // 实例化数据表
my.Fill(dt); // 保存数据
dataGridView1.DataSource = dt;
conn.Close();
}
else
{
String Delete1 = string.Format("delete from 缴费情况 where 借书证号= '{0}'and 图书编号='{1}' ", num, book_num);
SqlCommand cmd = new SqlCommand(Delete1, conn);
if (cmd.ExecuteNonQuery() > 0)
{
//MessageBox.Show("删除成功!");
}
MessageBox.Show("还书失败!");
conn.Close();
}
}
}
}
}
这里只是包含的C#中的代码,那些界面代码没有写,太多了,从代码中应该可以猜到是那个。
如果有错误,欢迎大家指正。