//删除读者按钮函数
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();
}
}
