private void bt_people_update_Click(object sender, EventArgs e)
{
SqlConnection conn = SqlConnect.getConn();
try
{
string sql = "update T_People set P_Name='{0}',P_Password='******' where P_Id = '{2}';";
//填充占位符
sql = string.Format(sql, tb_people_name.Text.Trim(), tb_people_password.Text.Trim(), tb_people_id.Text.Trim());
//创建SqlCommand类的对象
SqlCommand cmd = new SqlCommand(sql, conn);
//执行修改操作的SQL
cmd.ExecuteNonQuery();
MessageBox.Show("修改成功！");
//设置当前窗体DislogResult结果为OK
this.DialogResult = DialogResult.OK;
this.Close();
}
catch (Exception ex)
{
MessageBox.Show("修改失败！" + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
//图书出库按钮函数
private void bt_bookout_Click(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = SqlConnect.getConn();
string B_Id = null;
string strSQL = @"select * from T_Label where L_Id =" + "'" + this.tb_tagtext.Text.ToString().Trim() + "'";
SqlCommand cmd = new SqlCommand(strSQL, conn);
SqlDataReader sqlDataReader = cmd.ExecuteReader();
if (sqlDataReader.HasRows)
{
while (sqlDataReader.Read())
{
B_Id = sqlDataReader[0].ToString().Trim();
}
sqlDataReader.Close();
strSQL = @"update T_Book "
+ "set B_CountAll -= 1,"
+ "B_CountNow -= 1"
+ "where B_Id = '" + B_Id + "'";
cmd.CommandText = strSQL;
int updateBookNum = cmd.ExecuteNonQuery();
strSQL = @"delete from T_Label where B_Id = " + "'" + B_Id + "'"
+ " and "
+ "L_Id = '" + this.tb_tagtext.Text.ToString().Trim() + "'";
cmd.CommandText = strSQL;
int deleteLabelNum = cmd.ExecuteNonQuery();
if ((updateBookNum == 1) && (deleteLabelNum == 1))
{
MessageBox.Show("书号为：" + B_Id.Trim() +
" 标签号为：" + this.tb_tagtext.Text.ToString().Trim() +
" 的书已出库！", "出库成功");
this.tb_tagtext.Text = String.Empty;
}
else
{
throw new Exception("出库失败！");
}
}
else
{
sqlDataReader.Close();
this.tb_tagtext.Text = String.Empty;
MessageBox.Show("不存在此标签号！", "标签号错误");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "未知错误");
}
finally
{
conn.Close();
}
}
//查询读者按钮函数
private void bt_searchReader_Click_1(object sender, EventArgs e)
{
if (tb_readerid_input.Text.Trim() != "")
{
SqlConnection conn = SqlConnect.getConn();
try
{
string sql = "select * from T_People where P_Id= '{0}';";
//填充占位符
sql = string.Format(sql, tb_readerid_input.Text.Trim());
//创建SqlDataAdapter类的对象
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
//创建DataSet类的对象
DataSet ds = new DataSet();
//使用SqlDataAdapter对象sda将查新结果填充到DataSet对象ds中
sda.Fill(ds);
//设置表格控件的DataSource属性
dataGridView1.DataSource = ds.Tables[0];
//设置数据表格上显示的列标题
dataGridView1.Columns[0].HeaderText = "读者ID";
dataGridView1.Columns[1].HeaderText = "名字";
dataGridView1.Columns[2].HeaderText = "密码";
dataGridView1.Columns[0].Width = 125;
dataGridView1.Columns[1].Width = 125;
dataGridView1.Columns[2].Width = 125;
//设置数据表格为只读
dataGridView1.ReadOnly = true;
//不允许添加行
dataGridView1.AllowUserToAddRows = false;
//背景为白色
dataGridView1.BackgroundColor = Color.White;
//只允许选中单行
dataGridView1.MultiSelect = false;
//整行选中
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
catch (Exception ex)
{
MessageBox.Show("出现错误！" + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
else
{
QueryAllReader();
}
}
//读者信息管理
#region
//查询所有读者信息函数
private void QueryAllReader()
{
SqlConnection conn = SqlConnect.getConn();
try
{
string sql = "select * from T_People";
//创建SqlDataAdapter类的对象
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
//创建DataSet类的对象
DataSet ds = new DataSet();
//使用SqlDataAdapter对象sda将查新结果填充到DataSet对象ds中
sda.Fill(ds);
//设置表格控件的DataSource属性
dataGridView1.DataSource = ds.Tables[0];
//设置数据表格上显示的列标题
dataGridView1.Columns[0].HeaderText = "读者ID";
dataGridView1.Columns[1].HeaderText = "名字";
dataGridView1.Columns[2].HeaderText = "密码";
dataGridView1.Columns[0].Width = 125;
dataGridView1.Columns[1].Width = 125;
dataGridView1.Columns[2].Width = 125;
dataGridView1.Columns[0].MinimumWidth = 6;
dataGridView1.Columns[1].MinimumWidth = 6;
dataGridView1.Columns[2].MinimumWidth = 6;
//设置数据表格为只读
dataGridView1.ReadOnly = true;
//不允许添加行
dataGridView1.AllowUserToAddRows = false;
//背景为白色
dataGridView1.BackgroundColor = Color.White;
//只允许选中单行
dataGridView1.MultiSelect = false;
//整行选中
dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
}
catch (Exception ex)
{
MessageBox.Show("查询错误！" + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
//删除读者按钮函数
private void bt_delReader_Click_1(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count == 0)
{
MessageBox.Show("没有可删除的记录！");
}
else
{
//未选中记录
if (dataGridView1.SelectedRows.Count.Equals(0))
{
MessageBox.Show("请选中要删除的记录！");
return;
}
//获取DataGridView控件中选中行的编号列的值
string id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
SqlConnection conn = SqlConnect.getConn();
try
{
string sql = "delete from T_People where P_Id='{0}'";
//填充占位符
sql = string.Format(sql, id);
//创建SqlCommand类的对象
SqlCommand cmd = new SqlCommand(sql, conn);
//执行SQL语句
cmd.ExecuteNonQuery();
//弹出消息提示删除成功
MessageBox.Show("删除成功！");
//调用查询全部的方法，刷新DataGridView控件中的数据
QueryAllReader();
}
catch (Exception ex)
{
MessageBox.Show("删除失败！" + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
}
private void bt_people_add_Click(object sender, EventArgs e)
{
SqlConnection conn = SqlConnect.getConn();
try
{
string strSQL = @"select * from T_People where P_Id =" + "'" + this.tb_people_id.Text.ToString().Trim() + "'";
SqlCommand cmd = new SqlCommand(strSQL, conn);
SqlDataReader sqlDataReader = cmd.ExecuteReader();
//id号重复
if (sqlDataReader.HasRows)
{
sqlDataReader.Close();
MessageBox.Show("读者ID已存在，添加失败！");
}
else
{
sqlDataReader.Close();
string sql = "insert into T_People([P_Id],[P_Name],[P_Password]) values('{0}', '{1}', '{2}');";
//填充占位符
sql = string.Format(sql, tb_people_id.Text.Trim(), tb_people_name.Text.Trim(), tb_people_password.Text.Trim());
cmd = new SqlCommand(sql, conn);
//执行修改操作的SQL
cmd.ExecuteNonQuery();
MessageBox.Show("添加成功！");
//设置当前窗体DislogResult结果为OK
this.DialogResult = DialogResult.OK;
this.Close();
}
}
catch (Exception ex)
{
MessageBox.Show("添加失败！" + ex.Message);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
private void tb_bookid_TextChanged(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = SqlConnect.getConn();
string strSQL = @"select * from T_Book where B_Id = " + "'" + this.tb_bookid.Text.ToString().Trim() + "'";
SqlCommand cmd = new SqlCommand(strSQL, conn);
SqlDataReader sqlDataReader = cmd.ExecuteReader();
if (sqlDataReader.HasRows)
{
while (sqlDataReader.Read())
{
this.cbb_class.Text = sqlDataReader[1].ToString().Trim();
this.tb_author.Text = sqlDataReader[2].ToString().Trim();
this.tb_bookname.Text = sqlDataReader[3].ToString().Trim();
}
}
else
{
this.cbb_class.Text = String.Empty;
this.tb_author.Text = String.Empty;
this.tb_bookname.Text = String.Empty;
}
sqlDataReader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "未知错误");
}
finally
{
conn.Close();
}
}
//图书信息查询按钮函数
private void bt_searchbook_Click(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = SqlConnect.getConn();
string strSQL = "select * from T_Book ";
if (this.tb_BookName.Text != String.Empty)
{
strSQL = strSQL + "where B_Name = " + "'" + this.tb_BookName.Text.ToString().Trim() + "'";
if (this.tb_author.Text != String.Empty)
{
strSQL = strSQL + "and B_Author = " + "'" + this.tb_author.Text.ToString().Trim() + "'";
if (this.cb_class.Text != String.Empty)
{
strSQL = strSQL + "and B_Class = " + "'" + this.cb_class.Text.ToString().Trim() + "'";
}
}
else
{
if (this.cb_class.Text != String.Empty)
{
strSQL = strSQL + "and B_Class = " + "'" + this.cb_class.Text.ToString().Trim() + "'";
}
}
}
else
{
if (this.tb_author.Text != String.Empty)
{
strSQL = strSQL + "where B_Author = " + "'" + this.tb_author.Text.ToString().Trim() + "'";
if (this.cb_class.Text != String.Empty)
{
strSQL = strSQL + "and B_Class = " + "'" + this.cb_class.Text.ToString().Trim() + "'";
}
}
else
{
if (this.cb_class.Text != String.Empty)
{
strSQL = strSQL + "where B_Class = " + "'" + this.cb_class.Text.ToString().Trim() + "'";
}
}
}
GridView_Books.AutoGenerateColumns = false;
DataSet dataSet = new DataSet();
SqlCommand cmd = new SqlCommand(strSQL, conn);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(dataSet, "Books");
GridView_Books.Columns["Col1_BookId"].DataPropertyName = "B_Id";
GridView_Books.Columns["Col1_BookName"].DataPropertyName = "B_Name";
GridView_Books.Columns["Col1_Author"].DataPropertyName = "B_Author";
GridView_Books.Columns["Col1_Class"].DataPropertyName = "B_Class";
GridView_Books.Columns["Col1_AllNum"].DataPropertyName = "B_CountAll";
GridView_Books.Columns["Col1_NowNum"].DataPropertyName = "B_CountNow";
GridView_Books.DataSource = dataSet;
GridView_Books.DataMember = "Books";
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "未知错误");
}
finally
{
conn.Close();
}
}
//借阅信息管理
#region
//借阅信息查询按钮点击函数
private void bt_Search_Click(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = SqlConnect.getConn();
string strSQL = "select * from T_Borrow ";
bool nowNull = true;
if (this.tb_Pid.Text != String.Empty)
{
if (!nowNull)
{
strSQL = strSQL + "and ";
}
else
{
strSQL = strSQL + "where ";
}
strSQL = strSQL + "P_Id = " + "'" + this.tb_Pid.Text.ToString().Trim() + "'";
nowNull = false;
}
if (this.tb_Bid.Text != String.Empty)
{
if (!nowNull)
{
strSQL = strSQL + "and ";
}
else
{
strSQL = strSQL + "where ";
}
strSQL = strSQL + "B_Id = " + "'" + this.tb_Bid.Text.ToString().Trim() + "'";
nowNull = false;
}
if (this.cb_states.Text != String.Empty)
{
if (!nowNull)
{
strSQL = strSQL + "and ";
}
else
{
strSQL = strSQL + "where ";
}
if (this.cb_states.Text.ToString().Equals("已归还"))
{
strSQL = strSQL + "BR_IsReturn = 1";
}
else if (this.cb_states.Text.ToString().Equals("借阅中"))
{
strSQL = strSQL + "BR_IsReturn = 0";
}
else if (this.cb_states.Text.ToString().Equals("已超时"))
{
DateTime dateTime = DateTime.Now;
DateTime beginTime = dateTime.AddDays(-30);
strSQL = strSQL + "datediff(dd, " + "BR_Start, " + "'" + beginTime.ToString().Trim() + "'" + " ) >0";
}
nowNull = false;
}
if (this.tb_time.Text != String.Empty)
{
if (!nowNull)
{
strSQL = strSQL + "and ";
}
else
{
strSQL = strSQL + "where ";
}
string strTime = this.tb_time.Text;
DateTime dateTime = DateTime.Parse(strTime);
strTime = string.Format("{0:d}", dateTime);
strSQL = strSQL + "datediff(dd, BR_Start, " + "'" + strTime.ToString() + "'" + ") = 0 ";
nowNull = false;
}
Console.WriteLine(strSQL);
GridView_Borrow.AutoGenerateColumns = false;
DataSet dataSet = new DataSet();
SqlCommand cmd = new SqlCommand(strSQL, conn);
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);
sqlDataAdapter.Fill(dataSet, "Borrows");
GridView_Borrow.Columns["Col_ReaderID"].DataPropertyName = "P_Id";
GridView_Borrow.Columns["Col_BookId2"].DataPropertyName = "B_Id";
GridView_Borrow.Columns["Col3_StartDay"].DataPropertyName = "BR_Start";
GridView_Borrow.DataSource = dataSet;
GridView_Borrow.DataMember = "Borrows";
for (int i = 0; i < GridView_Borrow.Rows.Count; i++)
{
DateTime startTime = (DateTime)dataSet.Tables["Borrows"].Rows[i]["BR_Start"];
DateTime deadlineTime = startTime.AddDays(30);
GridView_Borrow.Rows[i].Cells["Col3_Deadline"].Value = deadlineTime.ToString();
if ((int)dataSet.Tables["Borrows"].Rows[i]["BR_IsReturn"] == 1)
{
GridView_Borrow.Rows[i].Cells["Col3_ReadDay"].Value = dataSet.Tables["Borrows"].Rows[i]["BR_Time"].ToString();
GridView_Borrow.Rows[i].Cells["Col_IsReturn"].Value = "已归还";
}
else
{
DateTime nowTime = DateTime.Now;
int readTime = new TimeSpan(nowTime.Ticks - startTime.Ticks).Days;
GridView_Borrow.Rows[i].Cells["Col3_ReadDay"].Value = readTime.ToString();
if (nowTime.CompareTo(deadlineTime) > 0)
{
GridView_Borrow.Rows[i].Cells["Col_IsReturn"].Value = "已超时";
}
else
{
GridView_Borrow.Rows[i].Cells["Col_IsReturn"].Value = "借阅中";
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "未知错误");
}
finally
{
conn.Close();
}
}
//添加入库按钮函数
private void bt_bookin_Click(object sender, EventArgs e)
{
SqlConnection conn = null;
try
{
conn = SqlConnect.getConn();
string strSQL = @"select * from T_Label where L_Id =" + "'" + this.tb_tagtext.Text.ToString().Trim() + "'";
SqlCommand cmd = new SqlCommand(strSQL, conn);
SqlDataReader sqlDataReader = cmd.ExecuteReader();
//标签号重复
if (sqlDataReader.HasRows)
{
sqlDataReader.Close();
MessageBox.Show("入库失败！此标签号已存在", "旧书入库失败");
}
//标签号不重复
else
{
sqlDataReader.Close();
strSQL = @"select * from T_Book where B_Id = " + "'" + this.tb_bookid.Text.ToString().Trim() + "'";
cmd = new SqlCommand(strSQL, conn);
sqlDataReader = cmd.ExecuteReader();
//旧书入库
if (sqlDataReader.HasRows)
{
sqlDataReader.Close();
//UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
strSQL = @"update T_Book "
+ "set B_CountAll += 1,"
+ "B_CountNow += 1"
+ "where B_Id = '" + this.tb_bookid.Text.ToString().Trim() + "'";
cmd.CommandText = strSQL;
int updateNum = cmd.ExecuteNonQuery();
strSQL = @"insert into T_Label "
+ "values("
+ "'" + this.tb_bookid.Text.ToString().Trim() + "'" + ","
+ "'" + this.tb_tagtext.Text.ToString().Trim() + "'"
+ ")";
cmd.CommandText = strSQL;
int insertLabelNum = cmd.ExecuteNonQuery();
if ((updateNum == 1) && (insertLabelNum == 1))
{
MessageBox.Show("书号为：" + this.tb_bookid.Text.ToString().Trim() +
" 标签号为：" + this.tb_tagtext.Text.ToString().Trim() +
" 的书已入库！", "已有图书入库成功");
this.tb_author.Text = String.Empty;
this.tb_bookid.Text = String.Empty;
this.tb_bookname.Text = String.Empty;
this.tb_tagtext.Text = String.Empty;
this.cbb_class.SelectedIndex = -1;
}
else
{
throw new Exception("入库失败！");
}
}
//新书入库
else
{
sqlDataReader.Close();
//INSERT INTO 表名称 VALUES (值1, 值2,....)
strSQL = @"insert into T_Book "
+ "values("
+ "'" + this.tb_bookid.Text.ToString().Trim() + "'" + ","
+ "'" + this.cbb_class.Text.ToString().Trim() + "'" + ","
+ "'" + this.tb_author.Text.ToString().Trim() + "'" + ","
+ "'" + this.tb_bookname.Text.ToString().Trim() + "'" + ","
+ " '1' , '1' )";
cmd.CommandText = strSQL;
int insertBookNum = cmd.ExecuteNonQuery();
strSQL = @"insert into T_Label "
+ "values("
+ "'" + this.tb_bookid.Text.ToString().Trim() + "'" + ","
+ "'" + this.tb_tagtext.Text.ToString().Trim() + "'"
+ ")";
cmd.CommandText = strSQL;
int insertLabelNum = cmd.ExecuteNonQuery();
if ((insertBookNum == 1) && (insertLabelNum == 1))
{
MessageBox.Show("书号为：" + this.tb_bookid.Text.ToString().Trim() +
" 标签号为：" + this.tb_tagtext.Text.ToString().Trim() +
" 的书已入库！", "新增图书入库成功");
this.tb_author.Text = String.Empty;
this.tb_bookid.Text = String.Empty;
this.tb_bookname.Text = String.Empty;
this.tb_tagtext.Text = String.Empty;
this.cbb_class.SelectedIndex = -1;
}
else
{
throw new Exception("入库失败！");
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "未知错误");
}
finally
{
conn.Close();
}
}