private void Button1_Click(object sender, EventArgs e) { //读取借书证号,和书名 string cardNumber = textBox1.Text; string bookName = textBox2.Text; //建立连接 string connect = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= Database1.accdb"; OleDbConnection connection = new OleDbConnection(connect); connection.Open(); //查询该借书证号是否存在 string select1 = "SELECT *FROM cardData WHERE cardNumber = '" + cardNumber + "'"; OleDbCommand command1 = new OleDbCommand(select1, connection); OleDbDataReader reader1 = command1.ExecuteReader(); if (!reader1.Read()) { textBox1.Text = ""; Console.Beep(200, 300); //发出低沉警告音 borrowBookCardFail borrowBookCardFail1 = new borrowBookCardFail(); borrowBookCardFail1.StartPosition = FormStartPosition.CenterParent; //居中 borrowBookCardFail1.ShowDialog(); connection.Close(); //关闭连接 return; //如果不存在,程序不再执行 } //查询是否存在该书 string select2 = "SELECT *FROM bookData WHERE bookName = '" + bookName + "'"; OleDbCommand command2 = new OleDbCommand(select2, connection); OleDbDataReader reader2 = command2.ExecuteReader(); if (!reader2.Read()) { borrowBookNameFail borrowBookNameFail1 = new borrowBookNameFail(); textBox1.Text = ""; Console.Beep(200, 300); //发出低沉警告音 borrowBookNameFail1.StartPosition = FormStartPosition.CenterParent; //居中 borrowBookNameFail1.ShowDialog(); connection.Close(); return; } //查询该书是否被该借书证号借出 string select11 = "SELECT *FROM borrowData WHERE bookName = '" + bookName + "'AND cardNumber = '" + cardNumber + "'"; OleDbDataAdapter accessData11 = new OleDbDataAdapter(select11, connection); DataSet dataSet11 = new DataSet(); accessData11.Fill(dataSet11); int tableLength11 = dataSet11.Tables[0].Rows.Count; if (tableLength11 == 0) //未被借出报错 { notBorrow notBorrow1 = new notBorrow(); Console.Beep(200, 300); //发出低沉警告音 notBorrow1.StartPosition = FormStartPosition.CenterParent; //居中 notBorrow1.ShowDialog(); textBox2.Text = ""; connection.Close(); return; } //该书若被该借书证号借出,则删除borrowData中对该书被该借书证号借出的记录 string delete4 = "DELETE FROM borrowData WHERE bookName = '" + bookName + "' AND cardNumber = '" + cardNumber + "'"; OleDbCommand command4 = new OleDbCommand(delete4, connection); command4.ExecuteNonQuery(); //显示借书成功 returnBookSuccess returnBookSuccess1 = new returnBookSuccess(); Console.Beep(1200, 300); //发出高昂提示音 returnBookSuccess1.StartPosition = FormStartPosition.CenterParent; //居中 returnBookSuccess1.ShowDialog(); connection.Close();//关闭连接 }
private void Button1_Click(object sender, EventArgs e) { //读取借书证号,和书名 string cardNumber = textBox1.Text; string bookName = textBox2.Text; //获取当前时间 DateTime nowTime = DateTime.Now; string newTimeString = nowTime.ToString(); //建立连接 string connect = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source= Database1.accdb"; //第二个参数为文件的路径,记得在字符串前加@ OleDbConnection connection = new OleDbConnection(connect); //oledbconnection主要是应用于access的数据库连接,sqlconnection主要是针对sql server数据库连接的方法。 connection.Open(); //查询该借书证号是否存在 string select1 = "SELECT *FROM cardData WHERE cardNumber = '" + cardNumber + "'"; OleDbCommand command1 = new OleDbCommand(select1, connection); OleDbDataReader reader1 = command1.ExecuteReader(); if (!reader1.Read()) { textBox1.Text = ""; Console.Beep(200, 300); //发出低沉警告音 borrowBookCardFail borrowBookCardFail1 = new borrowBookCardFail(); borrowBookCardFail1.StartPosition = FormStartPosition.CenterParent; //居中 borrowBookCardFail1.ShowDialog(); connection.Close(); //关闭连接 return; //如果不存在,程序不再执行 } //每人允许借书数量(根据题意设置为1,如有需要修改此处即可) int canBorrow = 1; //检查该借书证号是否借满了借书允许数量 string select2 = "SELECT *FROM borrowData WHERE cardNumber = '" + cardNumber + "'"; OleDbDataAdapter accessData2 = new OleDbDataAdapter(select2, connection); DataSet dataSet = new DataSet(); accessData2.Fill(dataSet); int tableLength = dataSet.Tables[0].Rows.Count; if (tableLength == canBorrow) { haveBorrowOneBook haveBorrowOneBook1 = new haveBorrowOneBook(); textBox1.Text = ""; Console.Beep(200, 300);//发出低沉警告音 haveBorrowOneBook1.StartPosition = FormStartPosition.CenterParent; haveBorrowOneBook1.ShowDialog(); connection.Close(); return; } //查询该书是否存在 string select3 = "SELECT *FROM bookData WHERE bookName = '" + bookName + "'"; OleDbCommand command3 = new OleDbCommand(select3, connection); OleDbDataReader reader3 = command3.ExecuteReader(); if (!reader3.Read()) { borrowBookNameFail borrowBookNameFail1 = new borrowBookNameFail(); textBox2.Text = ""; Console.Beep(200, 300);//发出低沉警告音 borrowBookNameFail1.StartPosition = FormStartPosition.CenterParent; borrowBookNameFail1.ShowDialog(); connection.Close(); return; } //查询该书是否还有库存 //先确定书本被借出数量 string select11 = "SELECT *FROM borrowData WHERE bookName = '" + bookName + "'"; OleDbDataAdapter accessData11 = new OleDbDataAdapter(select11, connection); DataSet dataSet11 = new DataSet(); accessData11.Fill(dataSet11); int tableLength11 = dataSet11.Tables[0].Rows.Count; //再查找库存数量 string select12 = "SELECT *FROM bookData WHERE bookName = '" + bookName + "'"; OleDbDataAdapter accessData12 = new OleDbDataAdapter(select12, connection); DataSet dataSet12 = new DataSet(); accessData12.Fill(dataSet12); int bookNumbers = int.Parse(dataSet12.Tables[0].Rows[0]["numbers"].ToString()); if (tableLength11 == bookNumbers)//如果被借出数量等于库存数量,说明输入错误 { borrowed borrowed1 = new borrowed(); Console.Beep(200, 300);//发出低沉警告音 borrowed1.StartPosition = FormStartPosition.CenterParent; borrowed1.ShowDialog(); connection.Close(); return; } //获取author名字 string select5 = "SELECT *FROM bookData WHERE bookName = '" + bookName + "'"; OleDbDataAdapter accessData5 = new OleDbDataAdapter(select5, connection); DataSet dataSet5 = new DataSet(); accessData5.Fill(dataSet5); string author = dataSet5.Tables[0].Rows[0]["author"].ToString(); //获取借阅者名字,以供存入借阅信息时使用 string select6 = "SELECT *FROM cardData WHERE cardNumber = '" + cardNumber + "'"; OleDbDataAdapter accessData6 = new OleDbDataAdapter(select6, connection); DataSet dataSet6 = new DataSet(); accessData6.Fill(dataSet6); string borrowerName = dataSet6.Tables[0].Rows[0]["borrowerName"].ToString(); //在borrowData中存入借书记录 string insert9 = "INSERT INTO borrowData(cardNumber, borrowerName, bookName, author, borrowDate)VALUES('" + cardNumber + "', '" + borrowerName + "', '" + bookName + "', '" + author + "', '" + newTimeString + "')";//注意逗号,最好是从sql教学网站直接复制语句,这样就不会出错,因为照着打都可能错 //INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees') OleDbCommand command9 = new OleDbCommand(insert9, connection); command9.ExecuteNonQuery(); //弹出借书成功网页 borrowSuccess borrowSuccess1 = new borrowSuccess(); Console.Beep(1200, 300);//发出高昂提示音 borrowSuccess1.StartPosition = FormStartPosition.CenterParent; borrowSuccess1.ShowDialog(); connection.Close();//关闭连接 }
private void Button4_Click(object sender, EventArgs e) { //清空左侧文本框 string[] empty = { "" }; richTextBox1.Lines = empty; //通过作者姓名查询借书人 if (comboBox1.Text.Equals("借书人")) { //读取文本框信息 string author = textBox1.Text; //建立连接 string connect = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb";//第二个参数为文件的路径,记得在字符串前加@ OleDbConnection connection = new OleDbConnection(connect); connection.Open(); //获取该作者的所有书 string select1 = "SELECT *FROM bookData WHERE author = '" + author + "'"; OleDbDataAdapter accessData1 = new OleDbDataAdapter(select1, connection); DataSet dataSet1 = new DataSet(); accessData1.Fill(dataSet1); int tableLength = dataSet1.Tables[0].Rows.Count; //如果没有该作者的书 if (tableLength == 0) { wrongAuthor w1 = new wrongAuthor(); Console.Beep(200, 300); connection.Close(); w1.StartPosition = FormStartPosition.CenterParent;//居中 w1.ShowDialog(); textBox1.Text = ""; return;//结束程序 } //展示该作者的书的具体情况 string[] authorBooks = new string[tableLength]; //书名 int[] authorBooksNumbers = new int[tableLength]; //该书馆藏量 ArrayList showList = new ArrayList(); //预备显示内容 for (int i = 0; i < tableLength; i++) { authorBooks[i] = dataSet1.Tables[0].Rows[i]["bookName"].ToString(); authorBooksNumbers[i] = int.Parse(dataSet1.Tables[0].Rows[i]["numbers"].ToString()); //在馆藏图书表里找到书本名字,再去借书记录里找 string select11 = "SELECT *FROM borrowData WHERE bookName = '" + authorBooks[i] + "'"; OleDbDataAdapter accessData11 = new OleDbDataAdapter(select11, connection); DataSet dataSet11 = new DataSet(); accessData11.Fill(dataSet11); int tableLength11 = dataSet11.Tables[0].Rows.Count; showList.Add("书名:" + authorBooks[i] + " " + "馆藏" + authorBooksNumbers[i] + "本"); if (tableLength11 > 0) { showList.Add("借出该书" + tableLength11 + "本,借阅者如下:"); for (int k = 0; k < tableLength11; k++) { showList.Add("借书证号:" + dataSet11.Tables[0].Rows[k]["cardNumber"].ToString() + " " + "借阅人:" + dataSet11.Tables[0].Rows[k]["borrowerName"].ToString()); } showList.Add("馆中仍有该书" + (authorBooksNumbers[i] - tableLength11) + "本"); } else { showList.Add("该书全部未借出"); } showList.Add(""); } //将内容展示至左侧文本框 int showListLength = showList.Count; string[] show = new string[showListLength]; for (int i = 0; i < showListLength; i++) { show[i] = showList[i].ToString(); } richTextBox1.Lines = show; Console.Beep(1200, 300); connection.Close(); } //通过借书证号查询借书情况 if (comboBox1.Text.Equals("借书情况")) { //读取信息 string cardNumber = textBox1.Text; //建立连接 string connect = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb";//第二个参数为文件的路径,记得在字符串前加@ OleDbConnection connection = new OleDbConnection(connect); connection.Open(); //查询该借书证号是否存在 string select1 = "SELECT *FROM cardData WHERE cardNumber = '" + cardNumber + "'"; OleDbCommand command1 = new OleDbCommand(select1, connection); OleDbDataReader reader1 = command1.ExecuteReader(); if (!reader1.Read()) { textBox1.Text = ""; Console.Beep(200, 300); connection.Close(); borrowBookCardFail b1 = new borrowBookCardFail(); b1.StartPosition = FormStartPosition.CenterParent;//居中 b1.ShowDialog(); return; } //读取该借书证借书信息 string select2 = "SELECT *FROM borrowData WHERE cardNumber = '" + cardNumber + "'"; OleDbDataAdapter accessData2 = new OleDbDataAdapter(select2, connection); DataSet dataSet = new DataSet(); accessData2.Fill(dataSet); //展示该借书证借书情况至左侧文本框 int tableLength = dataSet.Tables[0].Rows.Count; if (tableLength == 0) { string[] noBorrow = { "该卡未借书" }; richTextBox1.Lines = noBorrow; } else { string[] borrowBook = new string[tableLength]; for (int i = 0; i < tableLength; i++) { borrowBook[i] = "书名:" + dataSet.Tables[0].Rows[i]["bookName"].ToString() + " 借书日期:" + Convert.ToDateTime(dataSet.Tables[0].Rows[i]["borrowDate"].ToString()).ToLongDateString().ToString(); } richTextBox1.Lines = borrowBook; } Console.Beep(1200, 300); connection.Close(); } //通过日期查询过期者 if (comboBox1.Text.Equals("过期者")) { //读取文本框信息 string input = textBox1.Text; //获取现在时间 DateTime dateNow; if (input == "") { dateNow = DateTime.Now; } else { try { dateNow = Convert.ToDateTime(input + " " + "23:59:59"); //为了更符合实际情况,最后还书日当天书就过期了 } //输入格式错误,则报错 catch (System.FormatException) { Console.Beep(200, 300); formatFalse formatFalse1 = new formatFalse(); formatFalse1.StartPosition = FormStartPosition.CenterParent; formatFalse1.ShowDialog(); return; } } //建立连接 string connect = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb";//第二个参数为文件的路径,记得在字符串前加@ OleDbConnection connection = new OleDbConnection(connect); connection.Open(); //获取所有借书记录 string select1 = "SELECT *FROM borrowData"; OleDbDataAdapter accessData = new OleDbDataAdapter(select1, connection); DataSet dataSet = new DataSet(); accessData.Fill(dataSet); //将所有过期记录全部在右侧文本框显示出来 ArrayList outDateRecords = new ArrayList(); int tableLength = dataSet.Tables[0].Rows.Count; DateTime outBorrowDate = dateNow.AddMonths(-1); for (int i = 0; i < tableLength; i++) { //比较时间是否超时 DateTime borrowDate = Convert.ToDateTime(dataSet.Tables[0].Rows[i]["borrowDate"].ToString()); if (borrowDate < outBorrowDate) { outDateRecords.Add("书名:" + dataSet.Tables[0].Rows[i]["bookName"].ToString()); outDateRecords.Add("借书证号:" + dataSet.Tables[0].Rows[i]["cardNumber"].ToString() + " " + "借书人:" + dataSet.Tables[0].Rows[i]["borrowerName"].ToString()); outDateRecords.Add("借书日期:" + borrowDate.ToLongDateString().ToString()); outDateRecords.Add(""); } } //将内容展示至左侧文本框 int listLength = outDateRecords.Count; if (listLength == 0) { //如果没有过期者 string[] nothing = new string[1]; nothing[0] = "没有过期者"; richTextBox1.Lines = nothing; Console.Beep(1200, 300); connection.Close(); return; } string[] show = new string[listLength]; for (int i = 0; i < listLength; i++) { show[i] = outDateRecords[i].ToString(); } Console.Beep(1200, 300); richTextBox1.Lines = show; connection.Close(); } }