C#连接数据库与SQL执行（MySQL）

文本讨论C#代码中如何操作数据库，包括如何连接数据库，如何执行SQL语句，如何获取执行结果，如何执行事务等；示例以MySQL数据库为主，并封装了数据库连接字符串生成方法和添加数据记录的操作组件。

安装MySQL支持组件

.NET Framework类库中，在System.Data、System.Data.Common命名空间定义了一些数据操作的基本资源，System.Data.SqlClient命名空间定义了SQL Server数据库的操作资源，而操作MySQL数据库时可以使用Oracle官方提供的组件，下面通过NuGet安装。

首先，通过Visual Studio环境菜单项“工具”>>“NuGet包管理器”>>“管理解决方案的NuGet程序包”打开NuGet包管理窗口，如下图所示。

打开NuGet管理

打开NuGet包管理窗口，在“浏览”页的搜索框中输入“mysql”，如下图所示。

选择MySql.Data

接下来，在搜索结果中选择Oracle公司的MySql.Data，并在右侧的项目列表中选中需要添加MySql.Data组件的项目，如下图所示。

安装MySql.Data

如上图所示，选中MySql.Data组件和安装的项目后，点击右下角的“安装”按钮，在同意一些协议后等待安装完成即可。安装成功后，可以在“解决方案资源管理器”中的“引用”列表中看到“MySql.Data”项，如下图所示。

确认安装MySql.Data

准备测试数据库

MySQL数据库的安装、配置和应用可以参考《MySQL数据库应用》合集，作者个人网站地址：http://caohuayu.com/article/Group.aspx?id=3。测试过程中，C#代码操作数据库的结果可以在HeidiSQL中查询验证。

可以通过HeidiSQL执行下面的代码，以添加测试数据库和数据表。

MySQL

create database cdb_cs1 default charset='utf8mb4';

use cdb_cs1;

create table t1(
recid bigint not null auto_increment primary key,
f1 varchar(30) not null unique,
f2 int not null default 0 check(f2 in (0,1,2)),
f3 datetime,
f4 varchar(30)
)engine=innodb default charset='utf8mb4';

代码的功能是创建cdb_cs1数据库和t1数据表；t1表中包含的字段有：

recid，自动ID字段，bigint类型。
f1，定义为变长字符类型，最多30个字符；字段还定义为唯一键，即所有数据行的f1字段数据不能重复。
f2，定义为整数，默认值为0，允许的数值范围是0、1、2。
f3，定义为日期时间类型。
f4，变长字符类型，最多30个字符。

接下来，在C#代码中会使用cdb_cs1数据库和t1表进行测试。

连接数据库

操作MySQL数据库时需要引用MySql.Data.MySqlClient命名空间。

连接数据库可以使用包含连接参数的字符串，MySQL数据库连接字符串可以借助MySqlConnectionStringBuilder类创建，如下面的代码（cfx/data/mysql/MySqlHelper.cs）。

using MySql.Data.MySqlClient;

namespace cfx.data.mysql
{
    public static class tMySqlHelper
    {
        //
        public static string GetCnnStr(string server,string database,
            string userid,string password,uint port = 3306)
        {
            MySqlConnectionStringBuilder sb =
                new MySqlConnectionStringBuilder();
            sb.Server = server;
            sb.Database = database;
            sb.UserID = userid;
            sb.Password = password;
            sb.Port = port;
            sb.Pooling = true;
            sb.ConnectionTimeout = 30;
            sb.DefaultCommandTimeout = 30;
            return sb.ConnectionString;
        }
        //
    }
}

代码中，将MySQL数据库辅助功能代码封装在MySqlHelper类中，这里定义了GetCnnStr()方法，其参数包括：

server，连接的MySQL服务器地址。
database，连接的默认数据库。
userid，数据库连接用户。
password，数据库连接密码。
port，MySQL服务端口，默认为3306。

MySqlConnectionStringBuilder类中使用的属性有：

Server，MySQL服务器地址。
Database，默认连接的数据库名称。
UserID，连接MySQL服务的用户名。
Password，连接MySQL服务的密码。
Port，MySQL服务端口，默认为3306。
Pooling，是否启动连接池。
ConnectionTimeout，数据库连接超时时间，单位为秒。
DefaultCommandTimeout，默认的SQL语句执行超时时间，单位为秒。
ConnectionString，返回数据库连接字符串。

添加数据组件（tInsert接口）

下面的代码（cfx/data/tInsert.cs），使用tInsert接口定义添加数据的操作组件。

using System.Collections.Generic;

namespace cfx.data
{
    public interface tInsert
    {
        string CnnStr { get; }
        string Table { get; }
        tInsert AddData(string name, object val);
        tInsert AddData(Dictionary<string, object> data);
        tInsert AddData(tPairList data);
        tInsert ClearData();
        long Insert();
    }
    //
    public abstract class tInsertBase : tInsert
    {
        protected string myCnnStr;
        protected string myTable;
        protected tPairList myData = tPairList.Create();
        //
        public tInsertBase(string cnnstr,string table)
        {
            myCnnStr = cnnstr;
            myTable = table;
        }
        //
        public string CnnStr { get {return myCnnStr; } }
        public string Table { get { return myTable; } }
        //
        public tInsert AddData(string name, object val)
        {
            myData.Add(name, val);
            return this;
        }
        //
        public tInsert AddData(Dictionary<string, object> data)
        {
            if (data == null) return this;
            foreach(string k in data.Keys)
            {
                myData.Add(k, data[k]);
            }
            return this;
        }
        public tInsert AddData(tPairList data)
        {
            if (data == null) return this;
            myData.AddRange(data);
            return this;
        }
        //
        public tInsert ClearData()
        {
            myData.Clear();
            return this;
        }
        //
        public abstract long Insert();
    }
}

代码中首先定义了tInsert接口，其成员包括：

CnnStr只读属性，返回数据库连接字符串。
Table只读属性，返回操作的数据表名称。
AddData(string name, object val)方法，添加数据项，返回tInsert接口类型（当前实例）。
AddData(Dictionary<string, object> data)方法，通过字典添加数据项，返回tInsert接口类型（当前实例）。
AddData(tPairList data)方法，通过tPairList对象添加数据项，返回tInsert接口类型（当前实例）。
ClearData()方法，清除数据后返回tInsert接口类型（当前实例）。
Insert()方法，执行insert操作，返回新记录的ID（大于0），出错时返回小于0的整数。

接下来是tInsertBase类，定义为抽象类，并实现tInsert接口，可以看到，除了Insert()方法，其它接口成员都已经实现；其中，构造函数参数需要设置数据库连接字符串和数据表名称。

MySqlCommand类

通过SQL语句操作MySQL数据库并返回执行结果时会使用到MySqlCommand类，同样定义在MySql.Data.MySqlClient命名空间。

MySqlCommand类中执行SQL语句相关的常用属性包括：

CommandText，设置命令文本，如SQL语句、存储过程名称、表名（只应用于OLEDB数据源）。
CommandType，指定CommandText属性中的命令类型，使用CommandType枚举值（System.Data命名空间），包括Text（默认值，SQL语句），StoredProcedure（存储过程名称），TableDirect（表名）。
CommandTimeout，设置命令执行超时时间。

执行SQL语句的基本方法包括：

ExeucteNonQuery()方法，执行SQL并返回影响的记录数量，如添加、修改、删除的记录数量。返回类型为int。
ExecuteScalar()方法，返回查询结果中第一行第一个字段的值，返回类型为object。
ExecuteReader()方法，获取查询结果数据集合，返回类型为MySqlDataReader对象，后续文章会介绍如何从中读取数据。

这三个方法还有异步版本，分别是：

ExecuteNonQueryAsync()方法，返回任务（Task）对象，使用Result属性读取执行结果（int类型）。
ExecuteScalarAsync()方法，返回任务（Task）对象，使用Result属性读取执行结果（object类型）。
ExeucteReaderAsync()方法，返回任务（Task）对象，使用Result属性读取执行结果（MySqlDataReader对象）。

此外，MySqlCommand对象的Parameters属性包含了SQL语句或存储过程需要传递的参数集合，可以使用AddWithValue()方法添加参数，包括参数名和数据。清除所有参数时可以使用Clear()方法。

实现tMySqlInsert类

下面的代码（cfx/data/mysql/tMySqlInsert.cs）是tMySqlInsert类的实现。

using System.Text;
using MySql.Data.MySqlClient;

namespace cfx.data.mysql
{
    public class tMySqlInsert : tInsertBase
    {
        public tMySqlInsert(string cnnstr, string table)
            : base(cnnstr, table) { }
        //
        public static tMySqlInsert Create(string cnnstr,string table)
        {
            return new tMySqlInsert(cnnstr, table);
        }
        //
        public override long Insert()
        {
            try
            {
                string sql = GetInsertSql();
                if (sql.Length == 0) return -1001;
                using (MySqlConnection cnn = new MySqlConnection(myCnnStr))
                {
                    cnn.Open();
                    MySqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < myData.Count; i++)
                        cmd.Parameters.AddWithValue("?data" + i, myData[i].Value);
                    //
                    if (cmd.ExecuteNonQueryAsync().Result == 1)
                    { return cmd.LastInsertedId; }
                    else
                    { return -1000; }
                }
            }
            catch { return -1000; }
        }
        //
        protected string GetInsertSql()
        {
            if (myCnnStr == null || myCnnStr.Length == 0 ||
                myTable == null || myTable.Length == 0 ||
                myData.Count == 0) return "";
            //
            StringBuilder sb = new StringBuilder(512);
            StringBuilder sbVal = new StringBuilder(256);
            sb.AppendFormat("insert into `{0}`(`{1}`", myTable, myData[0].Name);
            sbVal.Append(")values(?data0");
            for(int i = 1; i < myData.Count; i++)
            {
                sb.AppendFormat(",`{0}`", myData[i].Name);
                sbVal.AppendFormat(",?data{0}", i);
            }
            sb.Append(sbVal.ToString());
            sb.Append(")");
            return sb.ToString();
        }
        //
    }
}

代码中，首先定义了构造函数，参数为数据库连接字符串和数据表名称，通过base关键字调用基类的构造函数实现；接下来定义Create()静态方法，用于创建tMySqlInsert对象。

GetInsertSql()方法用于创建insert语句，语句的基本格式如下：

SQL

insert into <表名>(<字段1>,<字段2>,<字段3>,...)
values(<值1>,<值2>,<值3>,...);

生成的insert语句中，MySQL数据库的对象名使用一对反单引号定义，SQL参数名使用问号（?）定义，参数名称使用data加从0开始的索引，分别对应myData中的数据项。请注意，MySQL语句中的参数也可以使用@符号定义，本合集统一使用问号（?）；而操作SQL Server数据库语句的参数统一使用@符号定义。

GetInsertSql()方法中还进行了必要的数据检查，当数据不符合操作要求时会返回空字符串。在Insert()方法中调用GetInsertSql()方法创建insert语句，如果生成的语句为空字符串，则返回-1001。接下来使用MySqlConnection对象连接数据库，并使用MySqlCommand对象执行insert语句，执行完成后，如果成功添加一条记录，则通过MySqlCommand对象的LastInsertedId属性返回新记录的ID字段数据，如测试表t1中的recid字段。

下面的代码，在Program.cs文件中测试tMySqlInsert类的使用。

using System;
using System.Collections.Generic;
using cfx.data;
using cfx.data.mysql;

namespace csfx_demo
{
    class Program
    {
        static void Main(string[] args)
        {
            string cnnstr = tMySqlHelper.GetCnnStr(
                "127.0.0.1", "cdb_cs1", "root", "DEV_Test123456", 3306);
            //
            long newid = tMySqlInsert.Create(cnnstr, "t1")
                .AddData("f1", "user01").AddData("f2", 0).AddData("f3", DateTime.Now)
                .AddData("f4", "111").Insert();
            Console.WriteLine(newid);
        }
    }
}

代码中，使用tMySqlHelper.GetCnnStr()方法创建了测试数据库的连接字符串，可根据实际测试环境修改参数。接下来，使用tMySqlInsert类的Create()方法创建实例，并通过链式方法调用AddData()方法添加数据，最后调用Insert()方法添加数据，执行成功后会显示新记录的ID值。在HeidiSQL中可以查看对t1表的修改结果，如下图所示。

通过tMySqlInsert类添加记录

执行事务与tSqlInsert类实现

下面的代码（cfx/data/sql/tSqlInsert.cs），使用tSqlInsert类实现在SQL Server数据库的表中添加记录的操作类。

using System.Text;
using System.Data.SqlClient;

namespace cfx.data.sql
{
    public class tSqlInsert : tInsertBase
    {
        public tSqlInsert(string cnnstr, string table)
            : base(cnnstr, table) { }
        //
        public static tSqlInsert Create(string cnnstr,string table)
        {
            return new tSqlInsert(cnnstr, table);
        }
        //
        public override long Insert()
        {
            try
            {
                string sql = GetInsertSql();
                if (sql.Length == 0) return -1001;
                using (SqlConnection cnn = new SqlConnection(myCnnStr))
                {
                    cnn.Open();
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < myData.Count; i++)
                        cmd.Parameters.AddWithValue("@data" + i, myData[i].Value);
                    //
                    using (SqlTransaction tran = cnn.BeginTransaction())
                    {
                        cmd.Transaction = tran;
                        if (cmd.ExecuteNonQueryAsync().Result == 1)
                        {
                            cmd.Parameters.Clear();
                            cmd.CommandText = "select @@identity";
                            long result = 
                                Obj.ToLng(cmd.ExecuteScalarAsync().Result);
                            tran.Commit();
                            return result; 
                        }
                        else
                        { return -1000; }
                    }
                }
            }
            catch { return -1000; }
        }
        //
        protected string GetInsertSql()
        {
            if (myCnnStr == null || myCnnStr.Length == 0 ||
                myTable == null || myTable.Length == 0 ||
                myData.Count == 0) return "";
            //
            StringBuilder sb = new StringBuilder(512);
            StringBuilder sbVal = new StringBuilder(256);
            sb.AppendFormat("insert into [{0}]([{1}]", myTable, myData[0].Name);
            sbVal.Append(")values(@data0");
            for(int i = 1; i < myData.Count; i++)
            {
                sb.AppendFormat(",[{0}]", myData[i].Name);
                sbVal.AppendFormat(",@data{0}", i);
            }
            sb.Append(sbVal.ToString());
            sb.Append(")");
            return sb.ToString();
        }
        //
    }
}

与tMySqlInsert类实现的主要区别如下。

支持SQL Server数据库的资源定义在System.Data.SqlClient命名空间。

GetInsertSql()方法中，SQL Server语句中的参数使用@符号定义，数据库对象使用一对方括号定义。

Insert()方法中，使用SqlTransaction类执行事务，并使用SqlConnection对象的BeginTransaction()方法创建事务，注意SqlCommand对象的Transaction属性要与SqlTransaction对象关联。执行insert语句成功添加一条记录后，使用“select @@identity”语句获取新添加记录的ID字段数据，并通过SqlCommand对象的ExecuteScalarAsync().Result读取返回的数据，注意要将object类型的数据转换为long类型。事务执行成功后，需要使用SqlTransaction对象的Commit()方法将操作结果提交到数据库，在using语句结构中，当事务执行出错时，会自动执行回滚（Rollback）操作。

MySQL数据库的事务操作也使用类似的代码，需要使用MySqlTransaction类，定义在MySql.Data.MySqlClient命名空间。