学院派福利——C#+SQL Server图书管理系统

yuandan

　　这是sql server的第七、八次上机内容，抽了几天时间给做了
　　在原有的booksDB库中加了一个Admin表：UserName：root，PassWord：123456.
　　环境：Visual Studio 2010，SQL Server 2008
　　参考书籍：

　　项目结构：

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.Threading;
namespace WindowsFormsApplication2
{
public partial class Login : Form
{
public Login()
{
InitializeComponent();
}
private void label1_Click(object sender, EventArgs e)
{
}
private void textBox2_TextChanged(object sender, EventArgs e)
{
}
private void label1_Click_1(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{   
string User = UserName.Text;
string Pwd = PassWord.Text;
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "server=.;database=booksDB;integrated security=True";
try
{
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "select * from Admin where UserName='" + User + "'";
SqlDataReader reader = comm.ExecuteReader();
if (reader.Read())
{
string password = reader.GetString(reader.GetOrdinal("PassWord"));
if (Pwd == password)
{
MessageBox.Show("登陆成功！");
new Thread(() => Application.Run(new Menu())).Start();
this.Close();
//this.Hide();
//Form M = new Management();
//M.Show();
                    }
else
{
MessageBox.Show("密码错误！");
UserName.Text = " ";
PassWord.Text = " ";
}
}
else
{
MessageBox.Show("用户不存在！");
UserName.Text = " ";
PassWord.Text = " ";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "操作数据库出错");
}
finally
{
conn.Close();
}
}
}
}

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;
namespace WindowsFormsApplication2
{
public partial class Menu : Form
{
public Menu()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
this.Hide();
new ReaderTypeM().Show();
}
private void button2_Click(object sender, EventArgs e)
{
this.Hide();
new BookM().Show();
}
private void button3_Click(object sender, EventArgs e)
{
this.Hide();
new ReaderM().Show();
}
private void button4_Click(object sender, EventArgs e)
{
this.Hide();
new BorrowAndReturn().Show();
}
private void Menu_Load(object sender, EventArgs e)
{
}
private void Out_Click(object sender, EventArgs e)
{
this.Close();
}
}
}
　　连数据库前先用BindingSource控件绑定数据库数据。再用DataGridView控件展示数据。

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 WindowsFormsApplication2
{
public partial class ReaderTypeM : Form
{
public ReaderTypeM()
{
InitializeComponent();
}
private void BackMenu_Click(object sender, EventArgs e)
{
this.Hide();
new Menu().Show();
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
private void Add_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "insert into ReaderType values(@rdType,@rdTypeName,@canLendQty,@canLendDay)";
comm.Parameters.AddWithValue("@rdType", txtrdType.Text);
comm.Parameters.AddWithValue("@rdTypeName", txtrdTypeName.Text);
comm.Parameters.AddWithValue("@canLendQty", txtcanLendQty.Text);
comm.Parameters.AddWithValue("@canLendDay", txtcanLendDay.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("插入成功！");
DataBind();
}
catch (Exception ex)
{
MessageBox.Show("插入失败！" + ex.Message);
}
}
private void Search_Click(object sender, EventArgs e)
{
DataBind();
}
private void Delete_Click(object sender, EventArgs e)
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要删除吗?", "确定", messButton);
if (dr == DialogResult.OK)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "delete from ReaderType where rdType = @rdType";
comm.Parameters.AddWithValue("@rdType", txtrdType.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("删除成功！");
DataBind();
}
catch (Exception ex)
{
MessageBox.Show("删除失败！" + ex.Message);
}
}
}
private void Alter_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "update ReaderType set rdType=@rdType,rdTypeName=@rdTypeName,canLendQty=@canLendQty,canLendDay=@canLendDay where rdType=@rdType";
comm.Parameters.AddWithValue("@rdType", txtrdType.Text);
comm.Parameters.AddWithValue("@rdTypeName", txtrdTypeName.Text);
comm.Parameters.AddWithValue("@canLendQty", txtcanLendQty.Text);
comm.Parameters.AddWithValue("@canLendDay", txtcanLendDay.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("更新成功！");
DataBind();
}
catch (Exception ex)
{
MessageBox.Show("更新失败！" + ex.Message);
}
}
private void DataBind()
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "select rdType 类别号,rdTypeName 类别名称,canLendQty 可借数量,canLendDay 可借天数 from ReaderType";
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sda.Fill(ds);
dgvBook.DataSource = ds.Tables[0];
txtrdType.DataBindings.Clear();
txtrdTypeName.DataBindings.Clear();
txtcanLendQty.DataBindings.Clear();
txtcanLendDay.DataBindings.Clear();
txtrdType.DataBindings.Add("Text", ds.Tables[0], "类别号");
txtrdTypeName.DataBindings.Add("Text", ds.Tables[0], "类别名称");
txtcanLendQty.DataBindings.Add("Text", ds.Tables[0], "可借数量");
txtcanLendDay.DataBindings.Add("Text", ds.Tables[0], "可借天数");
conn.Close();
}
private void ReaderTypeM_Load(object sender, EventArgs e)
{
DataBind();
}
private void bindingSource1_CurrentChanged(object sender, EventArgs e)
{
}
}
}

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 WindowsFormsApplication2
{
public partial class BookM : Form
{
public BookM()
{
InitializeComponent();
}
private void bindingSource1_CurrentChanged(object sender, EventArgs e)
{
}
private void BackMenu_Click(object sender, EventArgs e)
{
this.Hide();
new Menu().Show();
}
private void Add_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "insert into Book values(@bkID,@bkName,@bkAuthor,@bkPress,@bkPrice,@bkStatus)";
comm.Parameters.AddWithValue("@bkID", txtbkID.Text);
comm.Parameters.AddWithValue("@bkName", txtbkName.Text);
comm.Parameters.AddWithValue("@bkAuthor", txtbkAuthor.Text);
comm.Parameters.AddWithValue("@bkPress", txtbkPress.Text);
comm.Parameters.AddWithValue("@bkPrice", txtbkPrice.Text);
comm.Parameters.AddWithValue("@bkStatus", txtbkStatus.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("插入成功！");
DataBind();
}
catch (Exception ex)
{
MessageBox.Show("插入失败！" + ex.Message);
}
}
private void Search_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "select bkID 书号,bkName 书名,bkAuthor 作者,bkPress 出版社,bkPrice 单价,bkStatus 状态 from Book where bkName like + @bkName + '%'  ";  
comm.Parameters.AddWithValue("@bkName", txtbkName.Text);
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sda.Fill(ds);
dgvBook.DataSource = ds.Tables[0];
}
private void Delete_Click(object sender, EventArgs e)
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要删除吗?", "确定", messButton);
if (dr == DialogResult.OK)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "delete from Book where bkID = @bkID";
comm.Parameters.AddWithValue("@bkID", txtbkID.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("删除成功！");
DataBind();
}
catch (Exception ex)
{
MessageBox.Show("删除失败！" + ex.Message);
}
}
}
private void Alter_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "update Book set bkID=@bkID,bkName=@bkName,bkAuthor=@bkAuthor,bkPress=@bkPress,bkPrice=@bkPrice,bkStatus=@bkStatus where bkID=@bkID";
comm.Parameters.AddWithValue("@bkID", txtbkID.Text);
comm.Parameters.AddWithValue("@bkName", txtbkName.Text);
comm.Parameters.AddWithValue("@bkAuthor", txtbkAuthor.Text);
comm.Parameters.AddWithValue("@bkPress", txtbkPress.Text);
comm.Parameters.AddWithValue("@bkPrice", txtbkPrice.Text);
comm.Parameters.AddWithValue("@bkStatus", txtbkStatus.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("更新成功！");
DataBind();
}
catch (Exception ex)
{
MessageBox.Show("更新失败！" + ex.Message);
}
}
private void DataBind()
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "select bkID 书号,bkName 书名,bkAuthor 作者,bkPress 出版社,bkPrice 单价,bkStatus 状态 from Book";
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sda.Fill(ds);
dgvBook.DataSource = ds.Tables[0];
txtbkID.DataBindings.Clear();
txtbkName.DataBindings.Clear();
txtbkAuthor.DataBindings.Clear();
txtbkPress.DataBindings.Clear();
txtbkPrice.DataBindings.Clear();
txtbkStatus.DataBindings.Clear();
txtbkID.DataBindings.Add("Text", ds.Tables[0], "书号");
txtbkName.DataBindings.Add("Text", ds.Tables[0], "书名");
txtbkAuthor.DataBindings.Add("Text", ds.Tables[0], "作者");
txtbkPress.DataBindings.Add("Text", ds.Tables[0], "出版社");
txtbkPrice.DataBindings.Add("Text",ds.Tables[0],"单价");
txtbkStatus.DataBindings.Add("Text",ds.Tables[0],"状态");
conn.Close();
}
private void BookM_Load(object sender, EventArgs e)
{
DataBind();
}
}
}

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 WindowsFormsApplication2
{
public partial class ReaderM : Form
{
public ReaderM()
{
InitializeComponent();
}
private void BackMenu_Click(object sender, EventArgs e)
{
this.Hide();
new Menu().Show();
}
private void Add_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "insert into Reader values(@rdID,@rdType,@rdName,@rdDept,@rdQQ,@rdBorrowQty)";
comm.Parameters.AddWithValue("@rdID", txtrdID.Text);
comm.Parameters.AddWithValue("@rdType", txtrdType.Text);
comm.Parameters.AddWithValue("@rdName", txtrdName.Text);
comm.Parameters.AddWithValue("@rdDept", txtrdDept.Text);
comm.Parameters.AddWithValue("@rdQQ", txtrdQQ.Text);
comm.Parameters.AddWithValue("@rdBorrowQty", txtrdBorrowQty.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("插入成功！");
DataBind();
}
catch (Exception ex)
{
MessageBox.Show("插入失败！" + ex.Message);
}
}
private void Search_Click(object sender, EventArgs e)
{
DataBind();
}
private void Delete_Click(object sender, EventArgs e)
{
MessageBoxButtons messButton = MessageBoxButtons.OKCancel;
DialogResult dr = MessageBox.Show("确定要删除吗?", "确定", messButton);
if (dr == DialogResult.OK)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "delete from Reader where rdID = @rdID";
comm.Parameters.AddWithValue("@rdID", txtrdID.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("删除成功！");
DataBind();
}
catch (Exception ex)
{
MessageBox.Show("删除失败！" + ex.Message);
}
}
}
private void Alter_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "update Reader set rdID=@rdID,rdType=@rdType,rdName=@rdName,rdDept=@rdDept,rdQQ=@rdQQ,rdBorrowQty=@rdBorrowQty where rdID=@rdID";
comm.Parameters.AddWithValue("@rdID", txtrdID.Text);
comm.Parameters.AddWithValue("@rdType", txtrdType .Text);
comm.Parameters.AddWithValue("@rdName", txtrdName.Text);
comm.Parameters.AddWithValue("@rdDept", txtrdDept.Text);
comm.Parameters.AddWithValue("@rdQQ", txtrdQQ.Text);
comm.Parameters.AddWithValue("@rdBorrowQty",txtrdBorrowQty.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("更新成功！");
DataBind();
}
catch (Exception ex)
{
MessageBox.Show("更新失败！" + ex.Message);
}
}
private void DataBind()
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "select rdID 读者编号,rdType 读者类别号,rdName 读者姓名,rdDept 读者单位,rdQQ 读者QQ,rdBorrowQty 已借书数量 from Reader";
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sda.Fill(ds);
dgvReader.DataSource = ds.Tables[0];
txtrdID.DataBindings.Clear();
txtrdType.DataBindings.Clear();
txtrdName.DataBindings.Clear();
txtrdDept.DataBindings.Clear();
txtrdQQ.DataBindings.Clear();
txtrdBorrowQty.DataBindings.Clear();
txtrdID.DataBindings.Add("Text", ds.Tables[0], "读者编号");
txtrdType.DataBindings.Add("Text", ds.Tables[0], "读者类别号");
txtrdName.DataBindings.Add("Text", ds.Tables[0], "读者姓名");
txtrdDept.DataBindings.Add("Text", ds.Tables[0], "读者单位");
txtrdQQ.DataBindings.Add("Text", ds.Tables[0], "读者QQ");
txtrdBorrowQty.DataBindings.Add("Text", ds.Tables[0], "已借书数量");
conn.Close();
}
private void ReaderM_Load(object sender, EventArgs e)
{
DataBind();
}
}
}

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 WindowsFormsApplication2
{
public partial class BorrowAndReturn : Form
{
public BorrowAndReturn()
{
InitializeComponent();
}
private void BorrowBook_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "exec usp_BorrowBook @rdID,@bkID";
comm.Parameters.AddWithValue("@rdID", txtrdID.Text);
comm.Parameters.AddWithValue("@bkID", txtbkID.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("借书成功！");
}
catch (Exception ex)
{
MessageBox.Show("借书失败！" + ex.Message);
}
}
private void ReturnBook_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "exec usp_ReturnBook @rdID,@bkID";
comm.Parameters.AddWithValue("@rdID", txtrdID.Text);
comm.Parameters.AddWithValue("@bkID", txtbkID.Text);
try
{
comm.ExecuteNonQuery();
MessageBox.Show("还书成功！");
}
catch (Exception ex)
{
MessageBox.Show("还书失败！" + ex.Message);
}
}
private void BorrowAndReturn_Load(object sender, EventArgs e)
{
}
private void BackMenu_Click(object sender, EventArgs e)
{
this.Hide();
new Menu().Show();
}
private void Borrow_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("server=.;database=booksDB;integrated security=True");
conn.Open();
SqlCommand comm = conn.CreateCommand();
comm.CommandText = "select rdID 读者编号,bkID 书号,DateBorrow 借书日期,DateLendPlan 应还日期,DateLendAct 实际还书日期 from Borrow";
SqlDataAdapter sda = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sda.Fill(ds);
dgvBorrow.DataSource = ds.Tables[0];
conn.Close();  
}
}
}