c#控件操作数据库

C#控件操作数据库
    本文主要用到两个控件SqlConnection, SqlDataAdapter。这两个控件的添加方法，ToolsChoose Toolbox Items中，可以找到这两个控件，添加完后，就可以在工具面板中看到这两个控件了。

一：SqlDataAdapter，SqlConnection控件设置。
    将SqlDataAdapter控件拖入窗体上，会弹出Data Adapter Configuration Wizard框，选择需要连接的数据库，这时就会在下面的Connection string中看到数据库的连接方式：Data Source=ZTE-798DA662B29/SQLEXPRESS;Initial Catalog=BookManager;Integrated Security=True。
    选择NEXT选择，进入下一页面，选择Use SQL statements后，
    继续选择NEXT选项进入下一页面。
    点击Query Builder控件，弹出当前数据库中的所有表格，选择需要操作的表格，和需要操作的数据项。
    点击NEXT，就会弹出当前SqlDataAdapter支持的选择，插入，更新，删除项，如果表格没有主键的话，就没有更新和删除这两项。
    至此设置完成，同时会自动创建SqlConnection控件。
    系统会自动设置连接字符串，如下：
    this.sqlConnection1.ConnectionString = "Data Source=ZTE-798DA662B29//SQLEXPRESS;Initial Catalog=BookManager;Integrated Security=True";
    系统也会自动创建各种操作SQL的字符串
    this.sqlSelectCommand1.CommandText = "SELECT Book.*/r/nFROM Book";
    this.sqlInsertCommand1.CommandText = "INSERT INTO [Book] ([BookID], [BookName], [BookWriter], [BookPublish], [BookPublishDate], [BookPrice], [BookSort], [BookAmount], [BookRemain]) VALUES (@BookID, @BookName, @BookWriter, @BookPublish, @BookPublishDate, @BookPrice, @BookSort, @BookAmount, @BookRemain); SELECT BookID, BookName, BookWriter, BookPublish, BookPublishDate, BookPrice, BookSort, BookAmount, BookRemain FROM Book WHERE (BookID = @BookID";
    this.sqlUpdateCommand1.CommandText = "UPDATE [Book] SET [BookID] = @BookID, [BookName] = @BookName, [BookWriter] = @BookWriter, [BookPublish] = @BookPublish, [BookPublishDate] = @BookPublishDate, [BookPrice] = @BookPrice, [BookSort] = @BookSort, [BookAmount] = @BookAmount, [BookRemain] = @BookRemain WHERE (([BookID] = @Original_BookID) AND ((@IsNull_BookName = 1 AND [BookName] IS NULL) OR ([BookName] = @Original_BookName)) AND ((@IsNull_BookWriter = 1 AND [BookWriter] IS NULL) OR ([BookWriter] = @Original_BookWriter)) AND ((@IsNull_BookPublish = 1 AND [BookPublish] IS NULL) OR ([BookPublish] = @Original_BookPublish)) AND ((@IsNull_BookPublishDate = 1 AND [BookPublishDate] IS NULL) OR ([BookPublishDate] = @Original_BookPublishDate)) AND ((@IsNull_BookPrice = 1 AND [BookPrice] IS NULL) OR ([BookPrice] = @Original_BookPrice)) AND ((@IsNull_BookSort = 1 AND [BookSort] IS NULL) OR ([BookSort] = @Original_BookSort)) AND ((@IsNull_BookAmount = 1 AND [BookAmount] IS NULL) OR ([BookAmount] = @Original_BookAmount)) AND ((@IsNull_BookRemain = 1 AND [BookRemain] IS NULL) OR ([BookRemain] = @Original_BookRemain))); SELECT BookID, BookName, BookWriter, BookPublish, BookPublishDate, BookPrice, BookSort, BookAmount, BookRemain FROM Book WHERE (BookID = @BookID";
    this.sqlDeleteCommand1.CommandText = "DELETE FROM [Book] WHERE (([BookID] = @Original_BookID) AND ((@IsNull_BookName = 1 AND [BookName] IS NULL) OR ([BookName] = @Original_BookName)) AND ((@IsNull_BookWriter = 1 AND [BookWriter] IS NULL) OR ([BookWriter] = @Original_BookWriter)) AND ((@IsNull_BookPublish = 1 AND [BookPublish] IS NULL) OR ([BookPublish] = @Original_BookPublish)) AND ((@IsNull_BookPublishDate = 1 AND [BookPublishDate] IS NULL) OR ([BookPublishDate] = @Original_BookPublishDate)) AND ((@IsNull_BookPrice = 1 AND [BookPrice] IS NULL) OR ([BookPrice] = @Original_BookPrice)) AND ((@IsNull_BookSort = 1 AND [BookSort] IS NULL) OR ([BookSort] = @Original_BookSort)) AND ((@IsNull_BookAmount = 1 AND [BookAmount] IS NULL) OR ([BookAmount] = @Original_BookAmount)) AND ((@IsNull_BookRemain = 1 AND [BookRemain] IS NULL) OR ([BookRemain] = @Original_BookRemain))";
    this.sqlDataAdapter1.DeleteCommand = this.sqlDeleteCommand1;
    this.sqlDataAdapter1.InsertCommand = this.sqlInsertCommand1;
    this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
    this.sqlDataAdapter1.UpdateCommand = this.sqlUpdateCommand1;

    备注：对于SqlConnection字符串，最好使用配置文件，放到配置文件里面，这样的话，修改配置方便。

二：创建数据集
    点击SqlDataAdapter的Generate DataSet选项。然后直接创建即可，创建完后会生成一个DataSet.xsd数据集控件。

三：控件和数据集中的字段进行绑定。
    设置控件属性的DataBinding中的tect属性即可。

四：在窗体LOAD的时候，将需要用到的数据集加载进来
    private void LoadDataSet()
    {
        DataSetUser objDataSetTemp = new DataSetUser();
        try
        {
            this.FillDataSet(objDataSetTemp);
        }
        catch (System.Exception E)
        {
            this.ErrorHandle(E);
        }
        try
        {
            this.objDataSetUser.Clear();
            this.objDataSetUser.Merge(objDataSetTemp);
        }
        catch (System.Exception E)
        {
            this.ErrorHandle(E);
        }
    }

    private void FillDataSet(DataSetUser dataset)
    {
        dataset.EnforceConstraints = false;
        try
        {
            this.sqlConnection1.Open();
            this.sqlDataAdapter1.Fill(dataset);
        }
        catch (System.Exception E)
        {
            this.ErrorHandle(E);
        }
        finally
        {
            dataset.EnforceConstraints = true;
            this.sqlConnection1.Close();
        }
    }

五：添加，修改数据
    添加和修改数据的差别仅仅是有没有下面一行代码
    this.BindingContext[this.objDataSetUser, "User"].AddNew();
    public void UpdateDataSet()
    {
        //供取消操作时调用
        this.BindingContext[this.objDataSetUser, "User"].EndCurrentEdit();
        DataSetUser objDataSetTemp = new DataSetUser();
        objDataSetTemp = (DataSetUser)(this.objDataSetUser.GetChanges());
        try
        {
            this.UpdateDataSource(objDataSetTemp);
            this.objDataSetUser.Merge(objDataSetTemp);
            this.objDataSetUser.AcceptChanges();
        }
        catch (System.Exception E)
        {
            this.ErrorHandle(E);
        }
    }

    public void UpdateDataSource(DataSetUser Changerows)
    {
        try
        {
            this.sqlConnection1.Open();
            this.sqlDataAdapter1.Update(Changerows);
        }
        catch (System.Exception E)
        {
            this.ErrorHandle(E);
        }
        finally
        {
            this.sqlConnection1.Close();
        }
    }

六：删除数据
    int currentPosition = this.BindingContext[this.objDataSetUser, "User"].Position;
    this.objDataSetUser.User.Rows[currentPosition].Delete();

七：取消上述操作。
    this.BindingContext[this.objDataSetUser, "User"].CancelCurrentEdit();

八：浏览数据

    查看第一条数据：
    this.BindingContext[this.objDataSetUser, "User"].Position = 0;
    查看下一条数据：
    this.BindingContext[this.objDataSetUser, "User"].Position += 1;
    查看前一条数据：
    this.BindingContext[this.objDataSetUser, "User"].Position -= 1;
    查看最后一条数据：
    this.BindingContext[this.objDataSetUser,"User"].Position=this.BindingContext[this.objDataSetUser,"User"].Count-1;

九：对于一些不能进行数据绑定的操作，比如涉及到操作时间的，无法在控件上设置，这时就需要手动操作了。
    DataRow row = this.objDataSetBorrowReturn.Tables["BorrowBook"].NewRow();
    row["ReaderID"] = this.textReaderID.Text;
    row["BookID"] = this.textBookID.Text;
    row["BorrowDate"] = System.DateTime.Today;
    row["ReturnDate"] = DateTime.Today.AddMonths(1);
    this.objDataSetBorrowReturn.Tables["BorrowBook"].Rows.Add(row);
    public void UpdateDataSet()
    {
        DataSetBorrowReturn objDataSetTemp = new DataSetBorrowReturn();
        objDataSetTemp = (DataSetBorrowReturn)(this.objDataSetBorrowReturn.GetChanges());
        try
        {
            this.UpdateDataSource(objDataSetTemp);
            this.objDataSetBorrowReturn.Merge(objDataSetTemp);
            this.objDataSetBorrowReturn.AcceptChanges();
        }
        catch (System.Exception E)
        {
            this.ErrorHandle(E);
        }
    }

    public void UpdateDataSource(DataSetBorrowReturn Changerows)
    {
        try
        {
            this.sqlConnection1.Open();
            this.sqlDataAdapter1.Update(Changerows);
        }
        catch (System.Exception E)
        {
            this.ErrorHandle(E);
        }
        finally
        {
            this.sqlConnection1.Close();
        }
    }

十：存储过程的使用，比如借书
    比如，对于想从图书馆借书的人来说
    private void btnBorrow_Click(object sender, EventArgs e)
    {
        DataRow row = this.objDataSetBorrowReturn.Tables["BorrowBook"].NewRow();
        row["ReaderID"] = this.textReaderID.Text;
        row["BookID"] = this.textBookID.Text;
        row["BorrowDate"] = System.DateTime.Today;
        row["ReturnDate"] = DateTime.Today.AddMonths(1);
        this.objDataSetBorrowReturn.Tables["BorrowBook"].Rows.Add(row);
        //判断是否有该书，如果有的话，库存减一
        //判断是否有该人，且借的书书目小于8本，则借出，且该借书人借书书名加1
        if (BorrowBook(this.textBookID.Text) && BorrowReader(this.textReaderID.Text))
        {
            this.UpdateDataSet();
            this.dataGrid1.Refresh();
            this.textInformation.Text = this.textReaderID.Text + "借阅图书" + this.textBookID.Text + "成功";
        }
    }
    private bool BorrowBook(string BookID)
    {
        if (BorrowBookNumber(this.textBookID.Text) > 0)
        {
            SqlCommand borrowbook = new SqlCommand();
            borrowbook.Connection = this.sqlConnection1;
            borrowbook.CommandType = CommandType.StoredProcedure;
            borrowbook.CommandText = "dbo.StoredProcedureBorrowBook";
            SqlParameter parinput = borrowbook.Parameters.Add("@BookID", SqlDbType.Char);
            parinput.Direction = ParameterDirection.Input;
            parinput.Value = BookID;

            //ALTER PROCEDURE dbo.StoredProcedureBorrowBook
            //(
            //    @BookID  char(10)       
            //)

            //AS   
            //    update Book
            //        set BookRemain=BookRemain-1
            //        where BookID=@BookID;
            //RETURN

            try
            {
                this.sqlConnection1.Open();
                borrowbook.ExecuteNonQuery();
                this.sqlConnection1.Close();
                return true;
            }
            catch (System.Exception e)
            {
                this.ErrorHandle(e);
                this.sqlConnection1.Close();
                return false;
            }
        }
        else
            return false;

    }
    private int BorrowBookNumber(string BookID)
    {
        SqlCommand borrowbook = new SqlCommand();
        borrowbook.Connection = this.sqlConnection1;
        borrowbook.CommandType = CommandType.StoredProcedure;
        borrowbook.CommandText = "dbo.StoredProcedureBookNumber";
        SqlParameter parinput = borrowbook.Parameters.Add("@BookID", SqlDbType.Char);
        parinput.Direction = ParameterDirection.Input;
        parinput.Value = BookID;
        SqlParameter paroutput = borrowbook.Parameters.Add("@BookNumber", SqlDbType.Int);
        paroutput.Direction = ParameterDirection.Output;

        //ALTER PROCEDURE dbo.StoredProcedureBookNumber
        //(       
        //    @BookID char(10),
        //    @BookNumber int OUTPUT
        //)
        //AS
        //    select @BookNumber=BookRemain
        //        from Book where BookID=@BookID
        //RETURN

        try
        {
            this.sqlConnection1.Open();
            borrowbook.ExecuteNonQuery();
            this.sqlConnection1.Close();
            return Convert.ToInt16(paroutput.Value);

        }
        catch (System.Exception e)
        {
            this.ErrorHandle(e);
            this.sqlConnection1.Close();
            return 0;

        }


    }
    private int ReaderBorrowedNumber(string ReaderID)
    {
        SqlCommand borrowbook = new SqlCommand();
        borrowbook.Connection = this.sqlConnection1;
        borrowbook.CommandType = CommandType.StoredProcedure;
        borrowbook.CommandText = "dbo.StoredProcedureReaderBorrowedNumber";
        SqlParameter parinput = borrowbook.Parameters.Add("@ReaderID", SqlDbType.Char);
        parinput.Direction = ParameterDirection.Input;
        parinput.Value = ReaderID;
        SqlParameter paroutput = borrowbook.Parameters.Add("@BorrowedNumber", SqlDbType.Int);
        paroutput.Direction = ParameterDirection.Output;

        //ALTER PROCEDURE dbo.StoredProcedureReaderBorrowedNumber
        //(       
        //    @ReaderID char(10),
        //    @BorrowedNumber int OUTPUT
        //)
        //AS
        //    select @BorrowedNumber=ReaderBorrowedbooks
        //    from Reader where ReaderID=@ReaderID
        //RETURN

        try
        {
            this.sqlConnection1.Open();
            borrowbook.ExecuteNonQuery();
            this.sqlConnection1.Close();
            return Convert.ToInt16(paroutput.Value);

        }
        catch (System.Exception e)
        {
            this.ErrorHandle(e);
            this.sqlConnection1.Close();
            return 8;

        }
    }

    private bool BorrowReader(string ReaderID)
    {
        if (ReaderBorrowedNumber(ReaderID) < 8)
        {
            SqlCommand borrowbook = new SqlCommand();
            borrowbook.Connection = this.sqlConnection1;
            borrowbook.CommandType = CommandType.StoredProcedure;
            borrowbook.CommandText = "dbo.StoredProcedureBorrowReader";
            SqlParameter parinput = borrowbook.Parameters.Add("@ReaderID", SqlDbType.Char);
            parinput.Direction = ParameterDirection.Input;
            parinput.Value = ReaderID;

            //ALTER PROCEDURE dbo.StoredProcedureBorrowReader
            //(
            //    @ReaderID char(6)   
            //)
            //AS   
            //    update Reader
            //    set ReaderBorrowedBooks=ReaderBorrowedbooks+1
            //    where ReaderID=@ReaderID;
            //RETURN

            try
            {
                this.sqlConnection1.Open();
                borrowbook.ExecuteNonQuery();
                this.sqlConnection1.Close();
                return true;
            }
            catch (System.Exception e)
            {
                this.ErrorHandle(e);
                this.sqlConnection1.Close();
                return false;

            }
        }
        else
            return false;
    }
    public void UpdateDataSet()
    {
        DataSetBorrowReturn objDataSetTemp = new DataSetBorrowReturn();
        objDataSetTemp = (DataSetBorrowReturn)(this.objDataSetBorrowReturn.GetChanges());
        try
        {
            this.UpdateDataSource(objDataSetTemp);
            this.objDataSetBorrowReturn.Merge(objDataSetTemp);
            this.objDataSetBorrowReturn.AcceptChanges();
        }
        catch (System.Exception E)
        {
            this.ErrorHandle(E);
        }
    }

    public void UpdateDataSource(DataSetBorrowReturn Changerows)
    {
        try
        {
            this.sqlConnection1.Open();
            this.sqlDataAdapter1.Update(Changerows);
        }
        catch (System.Exception E)
        {
            this.ErrorHandle(E);
        }
        finally
        {
            this.sqlConnection1.Close();
        }
    }

十二：还书
    //更加读者ID和书籍编号查找到该书
    public DataRow[] GetSpecialRecord(string ReaderID, string BookID)
    {
        try
        {
            DataRow[] rows = this.objDataSetBorrowReturn.Tables["BorrowBook"].Select("ReaderID= '" + ReaderID + "'" + "and BookID='" + BookID + "'");
            return rows;
        }
        catch (Exception ex)
        {
            Console.WriteLine("GetSpecialExpert failed in ExpertAccess!" + ex.StackTrace.ToString());
            return null;
        }
    }
    private void btnReturn_Click(object sender, EventArgs e)
    {
        DataRow[] rows = this.GetSpecialRecord(this.textReaderID.Text, this.textBookID.Text);

        foreach (DataRow drow in rows)
        {
            int dday = System.DateTime.Today.DayOfYear - ((System.DateTime)drow["ReturnDate"]).DayOfYear;
            if (dday > 0)
                if (MessageBox.Show(this.textInformation.Text = this.textReaderID.Text + "读者你的" + this.textBookID.Text + "图书已经过期" + Convert.ToString(dday)
                    + "天,罚    款" + Convert.ToString(dday / 10) + "元RMB", "过期", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.Cancel)
                {
                    return;
                }
            drow.Delete();
        }

        if (ReturnReader(this.textReaderID.Text) && ReturnBook(this.textBookID.Text))
        {
            UpdateDataSet();
            dataGrid1.Refresh();
            this.textInformation.Text = this.textReaderID.Text + "归还图书" + this.textBookID.Text + "成功";
        }   
    }
   private bool ReturnBook(string BookID)
    {
        SqlCommand returnbook = new SqlCommand();
        returnbook.Connection = this.sqlConnection1;
        returnbook.CommandType = CommandType.StoredProcedure;
        returnbook.CommandText = "dbo.StoredProcedureReturnBook";
        SqlParameter parinput = returnbook.Parameters.Add("@BookID", SqlDbType.Char);
        parinput.Direction = ParameterDirection.Input;
        parinput.Value = BookID;
        //ALTER PROCEDURE dbo.StoredProcedureReturnBook
        //(
        //    @BookID char(10)   
        //)
        //AS   
        //    update Book
        //    set BookRemain=BookRemain+1
        //    where BookID=@BookID;
        //RETURN
        try
        {
            this.sqlConnection1.Open();
            returnbook.ExecuteNonQuery();
            this.sqlConnection1.Close();
            return true;
        }
        catch (System.Exception e)
        {
            this.ErrorHandle(e);
            this.sqlConnection1.Close();
            return false;

        }
    }
    private bool ReturnReader(string ReaderID)
    {
        SqlCommand returnbook = new SqlCommand();
        returnbook.Connection = this.sqlConnection1;
        returnbook.CommandType = CommandType.StoredProcedure;
        returnbook.CommandText = "dbo.StoredProcedureReturnReader";
        SqlParameter parinput = returnbook.Parameters.Add("@ReaderID", SqlDbType.Char);
        parinput.Direction = ParameterDirection.Input;
        parinput.Value = ReaderID;
        //ALTER PROCEDURE dbo.StoredProcedureReturnReader
        //(
        //    @ReaderID char(6)   
        //)
        //AS   
        //    update Reader
        //    set ReaderBorrowedBooks=ReaderBorrowedbooks-1
        //    where ReaderID=@ReaderID;
        //RETURN
        try
        {
            this.sqlConnection1.Open();
            returnbook.ExecuteNonQuery();
            this.sqlConnection1.Close();
            return true;
        }
        catch (System.Exception e)
        {
            this.ErrorHandle(e);
            this.sqlConnection1.Close();
            return false;

        }
    }