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