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();//关闭连接
        }
예제 #2
0
        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();//关闭连接
        }