bookborrowform.aspx.cs

基于C#和MYSQL的图书管理系统

//文件名：BookBorrowForm.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
public partial class BorrowManage_BookBorrowForm : System.Web.UI.Page
{
    private static int MyDays = 0;
    private static int MyBooks = 0;
    private static int MyIndex = 0;
    private static DataTable MyBorrowTable = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        string MyForbidString = Session["MyForbid"].ToString();
        if (MyForbidString.IndexOf("C1") > 1)
        {
            Server.Transfer("~/SystemManage/AllErrorHelp.aspx");
        }
        this.Button4.OnClientClick = "return confirm('是否确认新增当前选择的图书？')";
        this.Button2.OnClientClick = "return confirm('请检查读者借阅信息是否正确，一旦保存就无法修改，是否继续？')";
    }
    protected void Button1_Click(object sender, EventArgs e)
    {//新增借书清单
        String MySQLConnectionString = ConfigurationManager.ConnectionStrings["MyBooksDBConnectionString"].ConnectionString;
        SqlConnection MyConnection = new SqlConnection(MySQLConnectionString);
        MyConnection.Open();
        DataTable MyReaderTable = new DataTable();
        string MySQL = "Select 读者姓名,会员等级 From 有效读者视图 WHERE 读者编号= '" + this.TextBox1.Text  + "'";
        SqlDataAdapter MyAdapter = new SqlDataAdapter(MySQL, MyConnection);
        MyAdapter.Fill(MyReaderTable);
        if (MyReaderTable.Rows.Count > 0)
        {
            this.TextBox2.Text = MyReaderTable.Rows[0][0].ToString();
            this.TextBox3.Text = MyReaderTable.Rows[0][1].ToString();
            DataTable MyAssociatorTable = new DataTable();
            MySQL = "Select 出借天数,出借册数 From 会员等级 WHERE 会员名称= '" + MyReaderTable.Rows[0][1].ToString() + "'";
            MyAdapter = new SqlDataAdapter(MySQL, MyConnection);
            MyAdapter.Fill(MyAssociatorTable);
            MyDays = Convert.ToInt16(MyAssociatorTable.Rows[0][0].ToString());
            MyBooks = Convert.ToInt16(MyAssociatorTable.Rows[0][1].ToString());
            //获取读者已经借出图书的数量
            MySQL = "Select COUNT(*) From 借阅管理 WHERE 归还日期 IS NULL AND 遗失图书 IS NULL AND 读者编号= '"+ this.TextBox1.Text + "'";
            SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
            string MyCount = MyCommand.ExecuteScalar().ToString();
            MyBooks = MyBooks - Convert.ToInt16(MyCount);
            //创建无连接的数据表
            DataColumn[] MyKey = new DataColumn[1];
            MyBorrowTable = new DataTable("图书出借明细表");
            DataColumn MyColumn = new DataColumn();
            MyColumn.DataType = System.Type.GetType("System.String");
            MyColumn.ColumnName = "图书书号";
            MyBorrowTable.Columns.Add(MyColumn);
            MyKey[0] = MyColumn;
            MyBorrowTable.PrimaryKey = MyKey;
            MyBorrowTable.Columns.Add("图书书名", System.Type.GetType("System.String"));
            MyBorrowTable.Columns.Add("读者编号", System.Type.GetType("System.String"));
            MyBorrowTable.Columns.Add("读者姓名", System.Type.GetType("System.String"));
            MyBorrowTable.Columns.Add("会员等级", System.Type.GetType("System.String"));
            MyBorrowTable.Columns.Add("借出日期", System.Type.GetType("System.DateTime"));
            MyBorrowTable.Columns.Add("应还日期", System.Type.GetType("System.DateTime"));
            this.GridView1.DataSource = MyBorrowTable;
            this.GridView1.DataBind();
            MyIndex = 0;
        }
        else
        {
            this.TextBox1.Text = "";
            this.TextBox2.Text = "";
            this.TextBox3.Text = "";
        }
        if (MyConnection.State == ConnectionState.Open)
        {
            MyConnection.Close();
        }
    }
    protected void Button4_Click(object sender, EventArgs e)
    {//新增选择的图书
        if (this.TextBox2.Text.Length < 1)
        {
            this.Page.RegisterStartupScript("msgOnlyAlert","<script>alert('请输入读者编号！');</script>");
            return;
        }
        if ((MyBooks - MyIndex) <= 0)
        {
            this.Page.RegisterStartupScript("msgOnlyAlert", "<script>alert('借书数量过多！');</script>");
            return;
        }
        DateTime MyReturnDate = DateTime.Parse(this.TextBox5.Text);
        DateTime MyBorrowDate = DateTime.Parse(this.TextBox4.Text);
        TimeSpan MySpan = MyReturnDate.Subtract(MyBorrowDate);
        if (MyDays < MySpan.Days)
        {
            this.Page.RegisterStartupScript("msgOnlyAlert", "<script>alert('借书日期过长！');</script>");
            return;
        }
        if (MySpan.Days <= 0)
        {
            this.Page.RegisterStartupScript("msgOnlyAlert", "<script>alert('应还日期不正确！');</script>");
            return;
        }
        DataRow MyRow = MyBorrowTable.NewRow();
        MyIndex = MyIndex + 1;
        MyRow[0] = MyIndex;
        MyRow["读者编号"] = this.TextBox1.Text;
        MyRow["读者姓名"] = this.TextBox2.Text;
        MyRow["会员等级"] = this.TextBox3.Text;
        MyRow["借出日期"] = this.TextBox4.Text;
        MyRow["应还日期"] = this.TextBox5.Text;
        MyRow["图书书号"] = this.GridView2.SelectedRow.Cells[1].Text.ToString();
        MyRow["图书书名"] = this.GridView2.SelectedRow.Cells[2].Text.ToString();
        MyBorrowTable.Rows.Add(MyRow);        
        this.GridView1.DataSource = MyBorrowTable;
        this.GridView1.DataBind();
    }
    protected void Button2_Click(object sender, EventArgs e)
    {//保存借书清单
        String MySQLConnectionString = ConfigurationManager.ConnectionStrings["MyBooksDBConnectionString"].ConnectionString;
        SqlConnection MyConnection = new SqlConnection(MySQLConnectionString);
        MyConnection.Open();
        for (int i = 0; i < MyBorrowTable.Rows.Count; i++)
        {
            string MySQL = "INSERT INTO 借阅管理(读者编号,读者姓名,会员等级,图书书号,图书书名,借出日期,应还日期)VALUES(@读者编号,@读者姓名,@会员等级,@图书书号,@图书书名,@借出日期,@应还日期)";
            SqlCommand MyCommand = MyConnection.CreateCommand();
            MyCommand.CommandText = MySQL;
            MyCommand.Parameters.Add(new SqlParameter("@读者编号", SqlDbType.VarChar));
            MyCommand.Parameters.Add(new SqlParameter("@读者姓名", SqlDbType.VarChar));
            MyCommand.Parameters.Add(new SqlParameter("@会员等级", SqlDbType.VarChar));
            MyCommand.Parameters.Add(new SqlParameter("@图书书号", SqlDbType.VarChar));
            MyCommand.Parameters.Add(new SqlParameter("@图书书名", SqlDbType.VarChar));
            MyCommand.Parameters.Add(new SqlParameter("@借出日期", SqlDbType.DateTime));
            MyCommand.Parameters.Add(new SqlParameter("@应还日期", SqlDbType.DateTime));
            MyCommand.Parameters["@读者编号"].Value = MyBorrowTable.Rows[i][2].ToString();
            MyCommand.Parameters["@读者姓名"].Value = MyBorrowTable.Rows[i][3].ToString();
            MyCommand.Parameters["@会员等级"].Value = MyBorrowTable.Rows[i][4].ToString();
            MyCommand.Parameters["@图书书号"].Value = MyBorrowTable.Rows[i][0].ToString();
            MyCommand.Parameters["@图书书名"].Value = MyBorrowTable.Rows[i][1].ToString();
            MyCommand.Parameters["@借出日期"].Value = MyBorrowTable.Rows[i][5].ToString();
            MyCommand.Parameters["@应还日期"].Value = MyBorrowTable.Rows[i][6].ToString();
            MyCommand.ExecuteNonQuery();
            MySQL = "Update 馆藏图书 Set 出借数量=出借数量+1 WHERE 图书书号='" + MyBorrowTable.Rows[i][0].ToString() + "'";
            MyCommand.CommandText = MySQL;
            MyCommand.ExecuteNonQuery();
        }
        if (MyConnection.State == ConnectionState.Open)
        {
            MyConnection.Close();
        }
        Button1_Click(null,null);
        this.GridView2.DataBind();
    }
}