資料庫操作類《SqlHelper》_ZenDei技術網路在線

一．背景在看了一本書叫《Visual Studio 2010（C#）Windows資料庫項目開發》後，覺得很多編程技術需要積累，因為一個簡單的項目里包含的技術太多了，容易忘記。每次需要用到某個技術時，就免不了查閱資料，然後整理一堆操作後，發現浪費很多時間。現在呢，由於這個書沒有對資料庫操作做一個 ...

一．背景

在看了一本書叫《Visual Studio 2010（C#）Windows資料庫項目開發》後，覺得很多編程技術需要積累，因為一個簡單的項目里包含的技術太多了，容易忘記。每次需要用到某個技術時，就免不了查閱資料，然後整理一堆操作後，發現浪費很多時間。

現在呢，由於這個書沒有對資料庫操作做一個公共類設計，每次訪問資料庫的時候都會造成冗餘代碼。為瞭解決這個問題，我查閱了很多網上資源，當然裡面也有一丟丟自己的思考，總結成一個編程技術學習日記，以便日後自己翻閱。

二．設計

資料庫的操作主要包括：查詢，添加，刪減，更新。目前只討論“增刪查改”的實現，資料庫中的存儲過程、視圖、函數、觸發器等知識點這裡就不說了，後面在實現這個學生選課系統項目時再討論吧。

0. 首先定義一個用於初始化SqlCommand對象的方法：

//初始化SqlCommand對象
public static void InitializeCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] paras)
{
    if(conn.State != ConnectionState.Open())
        conn.Open();
    cmd.Connection = conn;
    cmd.CommandText = cmdText;
    if(trans != null)
        cmd.Transaction = trans;
    cmd.CommandType = cmdType;
    if(paras != null)
        foreach( SqlParameter item in paras)
        {
            cmd.Parameters.Add(item);
        }
}

1. 查詢

查詢返回一個數據集DataSet或者數據表DataTable，通用型的資料庫查詢方法：

//查詢數據，返回相應的數據表
public static DataTable GetDataTable(string connString, CommandType cmdType, string cmdText, params SqlParameter[] paras)
{
    SqlCommand cmd = new SqlCommand();
    using(SqlConnection conn = new SqlConnection(connString))
    {
        InitializeCommand(cmd,conn,null,cmdType,cmdText,paras);
        using(SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            DataTable dt = new DataTable();
            adapter.Fill(dt);
            cmd.Parameters.Clear();
            return dt;
        }
    }
}

查詢返回第一列第一行的數據，即返回單個數據的查詢方法：

//查詢單個數據，返回第一列第一行的數據
public static object ExecuteScalar(string connString, CommandType cmdType, string cmdText, params SqlParameter[] paras)
{
    SqlCommand cmd = new SqlCommand();
    using(SqlConnection conn = new SqlConnection(connString)
    {
        InitializeCommand(cmd,conn,null,cmdType,cmdText,paras);
        object obj = cmd.ExecuteScalar();
        cmd.Parameters.Clear();
        return obj;
    }
}

查詢返回一個SqlDataReader數據讀取器，當這個數據讀取器在讀取資料庫數據的時候一直和資料庫保持連接，並且一行一行地讀取，此查詢的方法：

//查詢返回SqlDataReader數據讀取器，用於一行一行地讀取資料庫數據
public static SqlDataReader ExecuteReader(string connString, CommandType cmdType,string cmdText,params SqlParameter[] paras)
{
    SqlCommand cmd = new SqlCommand();
    SqlConnection conn = new SqlConnection(connString)；
    try
    {
        InitializeCommand(cmd,conn,null,cmdType,cmdText,paras);
        SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        cmd.Parameters.Clear();
        return reader;
    }
    catch
    {
        conn.Close();
        throw;
    }
}

2. 添加、刪除、更新

資料庫的增刪改可以通過傳遞不同的SQL命令，以一種通用型的方法來實現增刪改。具體的方法為：

//針對資料庫的增刪改，設計一個通用型的方法，返回值為受影響的行數
public static int ExecuteNonQuery(string connString, CommandType cmdType, string cmdText, params SqlParameter[] paras)
{
    SqlCommand cmd = new SqlCommand();
    using(SqlConnection conn = new SqlConnection(connString))
    {
        InitializeCommand(cmd,conn,null,cmdType,cmdText,paras);
        int rows = cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
        retuen rows;
    }
}

三．實踐

寫好了資料庫操作類SqlHelper後，運用實際例子來驗證一下。

1.打開資料庫，創建資料庫命名為XK，新建一個tb_Departdent表，預先插入三行數據，用於增刪查改操作。

create database XK
create table tb_Department
(
    DeptNO int not null primary key identity(1,1),
    DeptName nvarchar(32) null
)
insert into tb_Department(DeptName) values(N'電腦應用工程系')
insert into tb_Department(DeptName) values(N'建築工程系')
insert into tb_Department(DeptName) values(N'旅游系')

2.打開VS，新建一個窗體項目,將窗體UI設計成如下圖所示。（toolstrip和datagridview控制項的設置我這裡就不說了，很簡單，不知道可以在園裡找找）

窗體在載入的時候將從資料庫中讀取tb_Department表中的全部數據，並顯示在datagridview控制項中。載入事件處理程式如下：

        private void LoadData()
        {
            //首先定義用於查詢的SQL腳本
            string cmdText = @"select DeptNO,DeptName from tb_Department";
            //使用SqlHelper操作類來讀取表數據
            //定義一個用於存儲讀取過來的表數據DataTable對象
            DataTable dt = SqlHelper.GetDataTable(SqlHelper.ConnString, CommandType.Text, cmdText);
            this.dgvDept.DataSource = dt;
        }
        //窗體載入時導入表數據
        private void DeptForm_Load(object sender, EventArgs e)
        {
            LoadData();
        }

載入後的效果，groupbox控制項設置為不可用。

3.增加、刪除、修改的方法實現。

        /// <summary>
        /// 插入數據
        /// </summary>
        private void InsertData()
        {
            //判斷系部名稱是否為空
            if (string.IsNullOrEmpty(this.txtDeptName.Text.Trim()))
            {
                MessageBox.Show("系部名稱不能為空！");
                return;
            }
            //定義插入數據的Sql腳本
            string cmdText = @"insert into tb_Department(DeptName) values(@DeptName)";
            //SQL腳本參數設置
            SqlParameter[] parameters =
            {
                //new SqlParameter("@DeptNO",(object)this.lbDeptNO.Text),
                new SqlParameter("@DeptName",(object)this.txtDeptName.Text.Trim())
            };
            //執行插入，並返回受影響的行數
            int rows = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, cmdText, parameters);
            //判斷是否插入成功，並提示
            if (rows > 0)
            {
                //更新datagridview控制項的數據
                LoadData();
                MessageBox.Show("插入成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                MessageBox.Show("插入失敗！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
        /// <summary>
        /// 刪除數據
        /// </summary>
        private void DeleteData()
        {
            //確認是否刪除
            if (MessageBox.Show("確定要刪除該行數據嗎？","提示",MessageBoxButtons.YesNo,MessageBoxIcon.Warning) == DialogResult.Yes)
            {
                //定義刪除相應數據的SQL腳本
                string cmdText = @"delete from tb_Department where DeptNO=@DeptNO";
                //定義SQL參數
                SqlParameter[] parameters =
                {
                    new SqlParameter("@DeptNO",(object)this.lbDeptNO.Text)
                };
                //接受返回的受影響的行數
                int rows = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, cmdText, parameters);
                //判斷是否插入成功，並提示
                if (rows > 0)
                {
                    //更新datagridview控制項的數據
                    LoadData();
                    MessageBox.Show("刪除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("刪除失敗！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
        }

        /// <summary>
        /// 更新數據
        /// </summary>
        private void UpdateData()
        {
            //判斷系部名稱是否為空
            if (string.IsNullOrEmpty(this.txtDeptName.Text.Trim()))
            {
                MessageBox.Show("系部名稱不能為空！");
                return;
            }
            //確認是否修改
            if (MessageBox.Show("確定要修改該行數據嗎？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) == DialogResult.Yes)
            {
                //定義刪除相應數據的SQL腳本
                string cmdText = @"update tb_Department set DeptName=@DeptName where DeptNO=@DeptNO";
                //定義SQL參數
                SqlParameter[] parameters =
                {
                    new SqlParameter("@DeptNO",(object)this.lbDeptNO.Text),
                    new SqlParameter("@DeptName",(object)this.txtDeptName.Text)
                };
                //接受返回的受影響的行數
                int rows = SqlHelper.ExecuteNonQuery(SqlHelper.ConnString, CommandType.Text, cmdText, parameters);
                //判斷是否插入成功，並提示
                if (rows > 0)
                {
                    //更新datagridview控制項的數據
                    LoadData();
                    MessageBox.Show("修改成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    MessageBox.Show("修改失敗！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
        }

4.為“提交”按鈕編寫點擊處理事件之前，我們需要設計一個CmdType枚舉來標識點擊提交按鈕執行的是新增，刪除或者更新操作。

    /// <summary>
    /// 用於標識命令操作的類型
    /// </summary>
    public enum CmdType
    {
        Insert,
        Delete,
        Update
    }

5.分別為窗體的增加，刪除，修改和提交按鈕編寫點擊處理程式。

        /// <summary>
        /// 用於標識SQL命令操作的類型
        /// </summary>
        private CmdType cmdType { get; set; }

        private void tsbInsert_Click(object sender, EventArgs e)
        {
            cmdType = CmdType.Insert;
            //將gbDept控制項設置可用,textbox控制項設為可用,並將Text屬性清空
            this.gbDept.Enabled = true;
            this.txtDeptName.Enabled = true;
            this.txtDeptName.Text = string.Empty;
            this.lbDeptNO.Text = "DeptNO";
        }

        private void tsbDelete_Click(object sender, EventArgs e)
        {
            //將sql命令類型設置為刪除
            cmdType = CmdType.Delete;
            //啟用groupbox控制項,textbox設置不可用
            this.gbDept.Enabled = true;
            this.txtDeptName.Enabled = false;
            //獲取要刪除的行數據，並賦值給相應控制項
            this.lbDeptNO.Text = dgvDept.SelectedRows[0].Cells["DeptNO"].Value.ToString();
            this.txtDeptName.Text = dgvDept.SelectedRows[0].Cells["DeptName"].Value.ToString();
        }

        private void tsbUpdate_Click(object sender, EventArgs e)
        {
            //將sql命令類型設置為刪除
            cmdType = CmdType.Update;
            //啟用groupbox控制項,textbox控制項設置為可用
            this.gbDept.Enabled = true;
            this.txtDeptName.Enabled = true;
            //獲取要刪除的行數據，並賦值給相應控制項
            this.lbDeptNO.Text = dgvDept.SelectedRows[0].Cells["DeptNO"].Value.ToString();
            this.txtDeptName.Text = dgvDept.SelectedRows[0].Cells["DeptName"].Value.ToString();
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            //執行增刪改操作
            switch (cmdType)
            {
                case CmdType.Insert:
                    InsertData();
                    break;
                case CmdType.Delete:
                    DeleteData();
                    break;
                case CmdType.Update:
                    UpdateData();
                    break;
            }
        }

四．結果

應用程式的UI和編碼工作做完之後，當然就是調試各項功能了。

1.增加功能：點擊增加按鈕，在系部名稱輸入要插入的系部：“哈哈系”，然後點擊提交按鈕，數據被插入到資料庫中了，為了後續操作，再插入“嘿嘿系”和“嘟嘟系”。

2.刪除功能：選擇要刪除的行數據（將“嘿嘿系”刪除），點擊刪除按鈕，窗體下方會顯示要刪除的行數據信息，點擊提交按鈕後會提示用戶是否確定要刪除數據，點擊“是”後顯示刪除成功，datagridview控制項中確實沒有“嘿嘿系”了。

3.修改功能：選擇要修改的行數據，窗體下方會顯示相應的信息，textbox控制項為可編輯，我們把“哈哈系”改成“光電系”,點擊提交按鈕，確認修改，datagridview控制項中“哈哈系”變成了“光電系”，修改成功。

四．總結

本文主要針對資料庫操作類SqlHelper的產生背景，設計過程，實踐運用以及調試結果進行講解，雖然描述的很粗糙，但是也算是一種資料庫操作技術的積累。

實踐運用遇到的主要問題：

新插入數據行中的自增長主鍵，由資料庫自動增加，不受人為控制，比如在講解增加功能的時候，新插入的行時，資料庫會自動給自增長欄位賦一個值，而這個值不是我們希望的。比如一個表中有Id，Name欄位，Id為自增長主鍵，原有表中有1-a，2-b，3-c三行數據，現在刪除3-c這一行的數據，然後在插入c，資料庫會自動給Id賦值為4，即：4-c，而不是我們想要的3-c。

如何在插入新數據時，自動獲得缺失的自增值來賦值給Id呢？這個問題我會在下一篇博客中解決。