C#实现SQL执行组件

前面的文章实现了在C#代码中操作数据库的基本组件，可以通过数据库工厂生成添加、修改、删除和查询数据操作对象，简化了数据处理代码，也对SQL语句操作进行了抽象；当然，在组件的底层依然是将各种要素组合成SQL语句，然后在数据库中执行，并返回执行结果。如果在项目中需要大量使用SQL语句完成数据的操作，也可以通过实现SQL执行组件简化代码，本文将讨论相关内容。

tDbJet接口

首先来看tDbJet接口的定义，如下面的代码（cfx/data/tDbJet.cs）。

using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;

namespace cfx.data
{
    public interface tDbJet
    {
        string CnnStr { get; }
        string ParamChar { get; }
        //
        bool Connect();
        bool Execute(string sql, params object[] arg);
        long ExecuteNonQuery(string sql, params object[] arg);
        //
        tPair GetValue(string sql, params object[] arg);
        tPairList GetRow(string sql, params object[] arg);
        DataTable GetTable(string sql, params object[] arg);
        List<double> GetDblCol(string sql, params object[] arg);
        List<string> GetStrCol(string sql, params object[] arg);
    }
    // 其它代码
}

tDbJet接口中，首先定义了两个只读属性，分别是CnnStr和ParamChar，其中，CnnStr返回数据库连接字符串，ParamChar则是SQL中定义参数的字符。Connect()方法用于测试数据是否能够正确连接，正确连接时返回true，否则返回false。

接下来是执行SQL语句的方法，它们的参数是相同的，参数sql指定执行语句，参数arg需要按语句中的参数顺序指定对应的数据。这些方法的功能分别是：

Execute()方法，执行SQL，成功执行返回true，否则返回false。
ExecuteNonQuery()方法，执行SQL，返回影响的记录数量，如添加、修改、删除操作。
GetValue()方法，返回查询结果中第一行第一个字段的值，返回tPair类型，没有查询结果或出错时返回默认值的tPair对象。
GetRow()方法，返回查询结果的第一行，返回tPairList类型，元素中的Name属性保存字段名，Value属性保存数据；没有查询结果或出错返回0个元素的tPairList对象。
GetTable()方法，返回查询结果，返回DataTable类型，没有查询结果或出错返回null。
GetDblCol()方法，返回查询结果第一列数据组成的List<double>对象，没有查询结果或出错返回0个元素的List<double>对象。
GetStrCol()方法，返回查询结果第一列数据组成的List<string>对象，没有查询结果或出错返回0个元素的List<string>对象。

tDbJetBase基类

tDbJetBase类是实现tDbJet接口组件的基类，定义如下（cfx/data/tDbJet.cs）。

using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;

namespace cfx.data
{
    // 其它代码
    public abstract class tDbJetBase : tDbJet
    {
        public string CnnStr { get; private set; }
        public string ParamChar { get; private set; }
        //
        public tDbJetBase(string cnnstr, string paramChar)
        {
            CnnStr = cnnstr;
            ParamChar = paramChar;
        }
        // 从SQL语句中读取参数名称
        protected List<string> GetParamName(string sql)
        {
            string pattern = @"\" + ParamChar + @"[\w|\d|_]+";
            List<string> lst = new List<string>();
            MatchCollection mc = 
                Regex.Matches(sql, pattern, RegexOptions.IgnoreCase);
            foreach (Match m in mc)
            {
                lst.Add(m.Value);
            }
            return lst;
        }
        //
        public abstract bool Connect();
        public abstract bool Execute(string sql, params object[] arg);
        public abstract long ExecuteNonQuery(string sql, params object[] arg);
        //
        public abstract tPair GetValue(string sql, params object[] arg);
        public abstract tPairList GetRow(string sql, params object[] arg);
        public abstract DataTable GetTable(string sql, params object[] arg);
        public abstract List<double> GetDblCol(string sql, params object[] arg);
        public abstract List<string> GetStrCol(string sql, params object[] arg);
    }
}

tDbJetBase类中实现了CnnStr和ParamChar只读属性，这里使用了定义属性的简便方法，并没有使用内部字段保存属性值；除了这两个属性，tDbJet接口中方法都定义为抽象方法，需要在子类实现。

tDbJetBase类中还定义了GetParamName()方法，其功能是从SQL语句中提取参数名称，返回类型为List<string>对象，语句中没有参数时返回0个元素的List<string>对象；应注意，GetParamName()方法需要和ParamChar属性值配合使用，本合集文章约定MySQL语句参数使用?符号定义，SQL Server语句参数使用@符号定义。实际工作中，@符号可以在MySQL、SQL Server、SQLite等数据库的语句中定义参数。

GetParamName()方法中，提取参数名时使用了正则表达式，其模式为@"\" + ParamChar + @"[\w|\d|_]+"，即，使用参数字符开始，然后由单词字符、数字和下画线组成的参数名。

tMySqlJet类

下面的代码（cfx/data/mysql/tMySqlJet.cs）是MySQL数据库的tDbJet接口实现。

using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;

namespace cfx.data.mysql
{
    public class tMySqlJet : tDbJetBase
    {
        public tMySqlJet(string cnnstr) : base(cnnstr,"?") { }
        //
        public static tMySqlJet Create(string cnnstr)
        {
            return new tMySqlJet(cnnstr);
        }
        //
        public override bool Connect()
        {
            try
            {
                using(MySqlConnection cnn = new MySqlConnection(CnnStr))
                {
                    cnn.Open();
                    return true;
                }
            }
            catch { return false; }
        }
        //
        public override bool Execute(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) return false;
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) return false;
                //
                using (MySqlConnection cnn = new MySqlConnection(CnnStr))
                {
                    cnn.Open();
                    MySqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch { return false; }
        }
        //
        public override long ExecuteNonQuery(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) return -1001;
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) return -1002;
                //
                using (MySqlConnection cnn = new MySqlConnection(CnnStr))
                {
                    cnn.Open();
                    MySqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    return cmd.ExecuteNonQueryAsync().Result;
                }
            }
            catch { return -1000; }
        }
        //
        public override tPair GetValue(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) return tPair.Create();
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) return tPair.Create();
                //
                using (MySqlConnection cnn = new MySqlConnection(CnnStr))
                {
                    cnn.Open();
                    MySqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    return tPair.Create("", cmd.ExecuteScalarAsync().Result);
                }
            }
            catch { return tPair.Create(); }
        }
        //
        public override tPairList GetRow(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) 
                    return tPairList.Create();
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) 
                    return tPairList.Create();
                //
                using (MySqlConnection cnn = new MySqlConnection(CnnStr))
                {
                    cnn.Open();
                    MySqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    using (MySqlDataReader dr  = 
                        cmd.ExecuteReaderAsync().Result as MySqlDataReader)
                    {
                        tPairList pl = tPairList.Create();
                        if (dr.Read())
                        {
                            for (int col = 0; col < dr.FieldCount; col++)
                                pl.Add(dr.GetName(col), dr[col]);
                        }
                        return pl;
                    }
                }
            }
            catch { return tPairList.Create(); }
        }
        //
        public override DataTable GetTable(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) return null;
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) return null;
                //
                using (MySqlConnection cnn = new MySqlConnection(CnnStr))
                {
                    cnn.Open();
                    MySqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    using (MySqlDataAdapter ada = 
                        new MySqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        if (ada.Fill(ds) > 0) return ds.Tables[0];
                        else return null;
                    }
                }
            }
            catch { return null; }
        }
        //
        public override List<double> GetDblCol(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) 
                    return new List<double>();
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) 
                    return new List<double>();
                //
                using (MySqlConnection cnn = new MySqlConnection(CnnStr))
                {
                    cnn.Open();
                    MySqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    using (MySqlDataReader dr =
                        cmd.ExecuteReaderAsync().Result as MySqlDataReader)
                    {
                        List<double> lst = new List<double>();
                        while (dr.Read()) lst.Add(dr.GetDouble(0));
                        return lst;
                    }
                }
            }
            catch { return new List<double>(); }
        }
        //
        public override List<string> GetStrCol(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) 
                    return new List<string>();
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) 
                    return new List<string>();
                //
                using (MySqlConnection cnn = new MySqlConnection(CnnStr))
                {
                    cnn.Open();
                    MySqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    using (MySqlDataReader dr =
                        cmd.ExecuteReaderAsync().Result as MySqlDataReader)
                    {
                        List<string> lst = new List<string>();
                        while (dr.Read()) lst.Add(dr.GetString(0));
                        return lst;
                    }
                }
            }
            catch { return new List<string>(); }
        }
        //
    }
}

代码中实现了tMySqlJet类，其中，构造函数通过基类的构造函数实现，只需要数据库连接字符串一个参数，ParamChar属性值默认使用问号（?）。SQL执行方法中使用的MySqlConnection、MySqlCommand、MySqlDataReader、MySqlAdapter等组件在前面的文章中都有介绍，这里不再赘述。

tSqlJet类

下面的代码（cfx/data/sql/tSqlJet.cs）是操作SQL Server数据库的tDbJet组件实现。

using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace cfx.data.sql 
{
    public class tSqlJet : tDbJetBase
    {
        public tSqlJet(string cnnstr) : base(cnnstr,"@") { }
        //
        public static tSqlJet Create(string cnnstr)
        {
            return new tSqlJet(cnnstr);
        }
        //
        public override bool Connect()
        {
            try
            {
                using(SqlConnection cnn = new SqlConnection(CnnStr))
                {
                    cnn.Open();
                    return true;
                }
            }
            catch { return false; }
        }
        //
        public override bool Execute(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) return false;
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) return false;
                //
                using (SqlConnection cnn = new SqlConnection(CnnStr))
                {
                    cnn.Open();
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch { return false; }
        }
        //
        public override long ExecuteNonQuery(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) return -1001;
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) return -1002;
                //
                using (SqlConnection cnn = new SqlConnection(CnnStr))
                {
                    cnn.Open();
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    return cmd.ExecuteNonQueryAsync().Result;
                }
            }
            catch { return -1000; }
        }
        //
        public override tPair GetValue(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) return tPair.Create();
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) return tPair.Create();
                //
                using (SqlConnection cnn = new SqlConnection(CnnStr))
                {
                    cnn.Open();
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    return tPair.Create("", cmd.ExecuteScalarAsync().Result);
                }
            }
            catch { return tPair.Create(); }
        }
        //
        public override tPairList GetRow(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) 
                    return tPairList.Create();
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) 
                    return tPairList.Create();
                //
                using (SqlConnection cnn = new SqlConnection(CnnStr))
                {
                    cnn.Open();
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    using (SqlDataReader dr  = 
                        cmd.ExecuteReaderAsync().Result)
                    {
                        tPairList pl = tPairList.Create();
                        if (dr.Read())
                        {
                            for (int col = 0; col < dr.FieldCount; col++)
                                pl.Add(dr.GetName(col), dr[col]);
                        }
                        return pl;
                    }
                }
            }
            catch { return tPairList.Create(); }
        }
        //
        public override DataTable GetTable(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) return null;
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) return null;
                //
                using (SqlConnection cnn = new SqlConnection(CnnStr))
                {
                    cnn.Open();
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    using (SqlDataAdapter ada = new SqlDataAdapter(cmd))
                    {
                        DataSet ds = new DataSet();
                        if (ada.Fill(ds) > 0) return ds.Tables[0];
                        else return null;
                    }
                }
            }
            catch { return null; }
        }
        //
        public override List<double> GetDblCol(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) 
                    return new List<double>();
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) 
                    return new List<double>();
                //
                using (SqlConnection cnn = new SqlConnection(CnnStr))
                {
                    cnn.Open();
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    using (SqlDataReader dr =
                        cmd.ExecuteReaderAsync().Result)
                    {
                        List<double> lst = new List<double>();
                        while (dr.Read()) lst.Add(dr.GetDouble(0));
                        return lst;
                    }
                }
            }
            catch { return new List<double>(); }
        }
        //
        public override List<string> GetStrCol(string sql, params object[] arg)
        {
            try
            {
                // 检查SQL及参数数量
                if (sql == null || sql.Length == 0) 
                    return new List<string>();
                List<string> paramName = GetParamName(sql);
                if (paramName.Count != arg.Length) 
                    return new List<string>();
                //
                using (SqlConnection cnn = new SqlConnection(CnnStr))
                {
                    cnn.Open();
                    SqlCommand cmd = cnn.CreateCommand();
                    cmd.CommandText = sql;
                    for (int i = 0; i < arg.Length; i++)
                        cmd.Parameters.AddWithValue(paramName[i], arg[i]);
                    using (SqlDataReader dr =
                        cmd.ExecuteReaderAsync().Result)
                    {
                        List<string> lst = new List<string>();
                        while (dr.Read()) lst.Add(dr.GetString(0));
                        return lst;
                    }
                }
            }
            catch { return new List<string>(); }
        }
        //
    }
}

代码中定义了tSqlJet类，其中，构造函数中指定SQL语句的参数使用@符号定义。

tDbJet组件的应用

tDbJet组件可以单独使用，比如，可以在tApp类中创建DbJet字段，并定义为某个数据库的tDbJet组件对象，如下面的代码。

using cfx.data;
using cfx.data.mysql;

public static class tApp
{
    // 数据库连接字符串
    private static string cnnstr1 =
        @"server=127.0.0.1;database=cdb_cs1;
user id=root;password=DEV_Test123456;port=3306;
pooling=True;connectiontimeout=30;defaultcommandtimeout=30";
    // 数据库工厂
    public static tDbFactory dbFact1 = tMySqlFactory.Create(cnnstr1);
    // 数据SQL引擎
    public static tDbJet DbJet = tMySqlJet.Create(cnnstr1);
}

下面的代码，在Program.cs代码中测试tApp.DbJet对象的使用。

using System;
using cfx.data;
namespace csfx_demo
{
    class Program
    {
        static void Main(string[] args)
        {
            string sql = @"select f1 from t1 where f1=?arg";
            tPair result = tApp.DbJet.GetValue(sql, "user01");
            Console.WriteLine(result.Value);
        }
    }
}

如果t1表中还保留f1等于user01的记录，则执行代码会显示user01，否则会显示空行。

此外，也可以将tDbJet组件扩展到tDbFactory接口，如修改tDbFactory接口和基类定义如下（cfx/data/tDbFactory）：

namespace cfx.data
{
    public interface tDbFactory
    {
        string CnnStr { get; }
        tInsert GetInsert(string table);
        tUpdate GetUpdate(string table);
        tDelete GetDelete(string table);
        tQuery GetQuery(string source);
        //
        tDbJet GetJet();
    }
    //
    public abstract class tDbFactoryBase : tDbFactory
    {
        protected string myCnnStr;
        public tDbFactoryBase(string cnnstr)
        {
            myCnnStr = cnnstr;
        }
        //
        public string CnnStr { get { return myCnnStr; } }
        //
        public abstract tInsert GetInsert(string table);
        public abstract tUpdate GetUpdate(string table);
        public abstract tDelete GetDelete(string table);
        public abstract tQuery GetQuery(string source);
        //
        public abstract tDbJet GetJet();
    }
}

代码中，在tDbFactory接口和tDbFactoryBase类中添加了GetJet()方法。

下面的代码（cfx/data/mysql/tMySqlFactory.cs），在tMySqlFactory类中添加GetJet()方法的实现。

namespace cfx.data.mysql
{
    public class tMySqlFactory : tDbFactoryBase
    {
        public tMySqlFactory(string cnnstr) : base(cnnstr) { }
        //
        public static tMySqlFactory Create(string cnnstr)
        {
            return new tMySqlFactory(cnnstr);
        }
        //
        public override tInsert GetInsert(string table)
        {
            return tMySqlInsert.Create(myCnnStr, table);
        }
        //
        public override tUpdate GetUpdate(string table)
        {
            return tMySqlUpdate.Create(myCnnStr, table);
        }
        //
        public override tDelete GetDelete(string table)
        {
            return tMySqlDelete.Create(myCnnStr, table);
        }
        //
        public override tQuery GetQuery(string source)
        {
            return tMySqlQuery.Create(myCnnStr, source);
        }
        //
        public override tDbJet GetJet()
        {
            return tMySqlJet.Create(myCnnStr);
        }
        //
    }
}

如实现了SQL Server数据库组件，需要同步修改tSqlFactory类的实现，如下面的代码（cfx/data/sql/tSqlFactory.cs）。

namespace cfx.data.sql
{
    public class tSqlFactory : tDbFactoryBase
    {
        public tSqlFactory(string cnnstr) : base(cnnstr) { }
        //
        public static tSqlFactory Create(string cnnstr)
        {
            return new tSqlFactory(cnnstr);
        }
        //
        public override tInsert GetInsert(string table)
        {
            return tSqlInsert.Create(myCnnStr, table);
        }
        //
        public override tUpdate GetUpdate(string table)
        {
            return tSqlUpdate.Create(myCnnStr, table);
        }
        //
        public override tDelete GetDelete(string table)
        {
            return tSqlDelete.Create(myCnnStr, table);
        }
        //
        public override tQuery GetQuery(string source)
        {
            return tSqlQuery.Create(myCnnStr, source);
        }
        //
        public override tDbJet GetJet()
        {
            return tSqlJet.Create(myCnnStr);
        }
        //
    }
}

下面的代码，在Program.cs文件中使用tApp.DbFact1对象创建tDbJet组件，并使用SQL语句操作数据库。

using System;
using cfx.data;

namespace csfx_demo
{
    class Program
    {
        static void Main(string[] args)
        {
            string sql = 
                @"insert into t1(f1,f2,f3,f4)values(?f1,?f2,?f3,?f4);
select @@identity";
            tPair result = tApp.dbFact1.GetJet()
                .GetValue(sql,"user11",1,DateTime.Now,"111");
            Console.WriteLine(result.Value);
        }
    }
}

示例中使用了MySQL数据库，约定参数使用问号（?）定义。SQL语句的功能是在t1表中添加一条记录，f1、f2、f3、f4字段对应的数据使用?f1、?f2、?f3、?f4四个参数传入，分别是"user11"、1、系统当前时间、"111"；执行代码会返回新记录的ID字段（recid）数据。

下面的代码会修改f4字段数据。

using System;
namespace csfx_demo
{
    class Program
    {
        static void Main(string[] args)
        {
            string sql = @"update t1 set f4=?data0 where f1=?data1";
            long result = tApp.dbFact1.GetJet()
                .ExecuteNonQuery(sql,"222.001","user11");
            Console.WriteLine(result);
        }
    }
}

执行代码会将t1表中f1字段为user11记录的f4字段修改为"222.001"，执行代码会显示修改记录的数量。