数据库操作类《SqlHelper》

 一．背景

在看了一本书叫《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呢？这个问题我会在下一篇博客中解决。