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();
}
}