Detailed explanation of the encapsulation of smarter database operations in C# .NET

Foregoing:

The encapsulation of database operations, I believe that the network There are already a lot of ORM frameworks, or .NET's own EF, which all support database operations very well. This article is to share my thoughts on a simple encapsulation of database operations. For this article, I think that the focus of readers is how to analyze and design database operation encapsulation, and the code is secondary. Moreover, this is my first article. It took me a few days to figure out how to implement it. The code was written when the blog was published. So I think there may be bugs in the use, and there is no try catch exception design.

# This framework should be database-independent and can be used no matter which database it is. However, the focus is on analysis, not code. Therefore, in order to better explain, I only encapsulated SQL Server. For other words, viewers can design it themselves; the framework can support chain writing. I think that in many programming languages, everyone has a big problem with chain writing. It will be unfamiliar, so I think database access can also be made into a chain mode. This framework does not require writing SQL statements. For any operation, you only need to simply pass the required parameters and encapsulate the corresponding operations.

Before reading the article, it is best to have some basic knowledge of generics, reflection, and Link, otherwise reading may be a bit difficult.

Getting to the point:

The structure of the frame is relatively simple , uses the simple factory pattern, so the author does not draw a UML diagram to explain, but uses text to describe the methods inside.

When designing the factory interface, you should consider that the interface should contain the three stages (also called parts) necessary for chain writing: basic database operations (open, Closure, creation, etc.), database additions, deletions, modifications, data returned by the database (here I regard it as the execution stage, I guess you will be curious why it is not the previous stage, you will know by reading below) and non-essential transaction operations.

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

namespace Dal
{
    public interface DbHelper
    {
        /// <summary>
        /// 创建数据库连接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns></returns>
        DbHelper createConnection(string connectionString);

        /// <summary>
        /// 打开数据库
        /// </summary>
        /// <returns></returns>
        DbHelper openConnection();

        /// <summary>
        /// 关闭数据库
        /// </summary>
        /// <returns></returns>
        DbHelper closeConnection();

        /// <summary>
        /// 释放sqlConnection对象
        /// </summary>
        void DisposedConnection();

        /// <summary>
        /// 释放sqlCommand对象
        /// </summary>
        void DisposedCommand();

        /// <summary>
        /// 创建sqlCommand对象
        /// </summary>
        /// <returns></returns>
        DbHelper createCommand();

        /// <summary>
        /// 设置sqlCommand的类型
        /// </summary>
        /// <param name="type">CommandType枚举类型</param>
        /// <returns></returns>
        DbHelper setCommandType(CommandType type);
        
        /// <summary>
        /// 要查询的表（多表以逗号隔开）、存储过程、视图名
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        DbHelper FromName(string Name);

        /// <summary>
        /// 创建事务
        /// </summary>
        /// <returns></returns>
        DbHelper beginTransaction();

        /// <summary>
        /// 事务回滚
        /// </summary>
        /// <returns></returns>
        DbHelper TransactionRowBack();

        /// <summary>
        /// 事务提交
        /// </summary>
        /// <returns></returns>
        DbHelper TransactionCommit();

        /// <summary>
        /// 对多张表间的列进行联系
        /// </summary>
        /// <param name="Fields">表间联系的字段</param>
        /// <returns></returns>
        DbHelper ForMulTable(string Fields);

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="Fields">查询字段</param>
        /// <param name="Where">查询条件字典</param>
        /// <param name="otherWhere">其他条件</param>
        /// <returns></returns>
        DbHelper Select(string Fields = "*", Dictionary<string, object> Where = null, string otherWhere = "");

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model">需要更新的对象</param>
        /// <param name="Where">更新条件</param>
        /// <param name="Fields">更新字段</param>
        /// <param name="otherWhere">其他条件</param>
        /// <returns></returns>
        DbHelper Update(object model, Dictionary<string, object> Where, string Fields = "", string otherWhere = "");

        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="model">需要插入的对象</param>
        /// <param name="Fields">需要插入的字段</param>
        /// <returns></returns>
        DbHelper Insert(object model, string Fields = "");

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="Where">删除条件</param>
        /// <param name="otherWhere">其他条件</param>
        /// <returns></returns>
        DbHelper Delete(Dictionary<string, object> Where, string otherWhere = "");


        /// <summary>
        /// 查询返回List
        /// </summary>
        /// <typeparam name="T">模型</typeparam>
        /// <returns></returns>
        List<T> ToList<T>()
            where T : class ,new();

        
        /// <summary>
        /// 查询返回DataSet
        /// </summary>
        /// <param name="DatasetName"></param>
        /// <returns></returns>
        DataSet ToDataSet(string DatasetName);

        /// <summary>
        /// 查询返回DataTable
        /// </summary>
        /// <returns></returns>
        DataTable ToDataTable();

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="Parameter">存储过程参数</param>
        /// <returns></returns>
        DbHelper ExcuteProc(Dictionary<string, object> Parameter);

        /// <summary>
        /// 执行返回查询第一行第一列值
        /// </summary>
        /// <returns></returns>
        object Result();

        /// <summary>
        /// 返回执行的影响行数
        /// </summary>
        /// <returns></returns>
        int ExcuteResult();

        /// <summary>
        /// 用户自定义sqlCommand
        /// </summary>
        /// <param name="fun">委托</param>
        void UserDefineOperation(Action<dynamic> fun);
    }
}

Okay, after reading the code, everyone should still be confused about the specific implementation. Then, let’s analyze the specific implementation step by step, based on sql Server to analyze.

# In the specific implementation class of SQLHelper, it is a necessary field in the design. At the beginning of the design, I was thinking about how to make each database compatible, because the execution object Command they use is different, so in order to better encapsulate the library, I designed the sqlCommand not to be exposed to external use, but to be used internally. . The exposed method can set the attribute of com, and the ExcuteName object stores the execution data.

//连接字符串
        string ConnectionString;
        
        //数据库连接对象
        private SqlConnection conn;

        //执行对象
        SqlCommand com;

        //表、存储过程、视图名称
        string ExcuteName;

        //事务
        SqlTransaction tran;

        //sql语句
        StringBuilder sqlBuilderString;

        //参数
        SqlParameter[] paras;

Part 1: Basic database operations

createConnection method: This method is actually new sqlConnection, assigning connectionString to it, also adopts the single case mode that everyone generally uses, which will also be safer during execution. However, this singleton refers to a Helper corresponding to a sqlConnection, rather than being designed as static, because I think some projects may access multiple databases. And when creating it, open and close it once to check whether it can really be used.

public DbHelper createConnection(string connectionString)
        {
            if (!ConnectionCanUse())
            {
                this.ConnectionString = connectionString;
                conn = new SqlConnection(this.ConnectionString);
            }

            return this;
        }

        /// <summary>
        /// 检查conn是否能用
        /// </summary>
        /// <returns></returns>
        public bool ConnectionCanUse()
        {
            if (conn == null)
                return false;
            try
            {
                conn.Open();
                conn.Close();
            }catch(Exception e)
            {
                return false;
            }
            
            return true;
        }

I won’t explain opening, closing, releasing connection and creating command, because there is only one sentence in it.

Regarding basic operations, there is also the setting of sqlCommandType. Because the operation strings such as stored procedures and ordinary statements are obviously different, a method must be written. to set it up.

　　第二部分：增删改查的操作 这里就解释为什么sql语句不是在这个阶段执行。我觉得，如果将具体的执行放在这个阶段，那么就会导致方法重载过多，为什么？因为并不是所有人都能考虑到使用者要返回的类型，比如我想要List，或者DataSet等等，而且还会将这个方法的作用过重：在我设计的这些方法中，实操作的是对sql语句的生成，所以说为什么不能在这边执行，那么就不能重用。是吧，这样设计很灵活，将数据库真正执行放在下个阶段。而且这些方法都是链式的写法，所以会对执行能够很灵活的控制，最重要能够重用，不需要写别的重载方法，只需要一个方法。

　　生成sql语句在这也是简单的封装，如果要做起真的框架，我觉得sql字符串的组合还应该创建一个类，来更智能的组合用户的需求。

　　自然，里面使用到反射、Linq。不过笔者也一步步解释，将怎么设计分享给大家。

　　大家看到Select、Insert、Update、Delete的接口都有Where的条件字典。没错，反射就在这里使用。为了考虑到数据库的安全，所以sql自然只是简单的拼接，还应该使用参数。所以，反射就用在Where生成参数上。大家也许还看到别的otherWhere，这个怎么不设计成参数，因为Where能够实现的，其实就是赋值语句，也就是表内某字段 = 值，所以需要。在otherWhere中，存放的是其他特殊的条件。前面说这里设计的不完美，就因为如此，其实有些条件 like 或者 使用or ，虽然能够写在otherWhere中，但是没办法使用参数来控制。

　　那么接下来就是Fiels参数了，这个在各个方法充当不同的作用。Select是查询的字段，Update中是更新的字段，在Insert中是插入的字段，这样就灵活的控制了。在这些字段为空的时候，默认为全部，反射在这里就使用了，遍历模型对象中的属性，然后将它们一个个填进sql语句中。在这里比较注意的应该是插入，因为大家在写sql语句时候是这样的 insert tableName values(value,value....)这样的格式，这样是因为sql会自己对应值插入，而在程序中的模型类中，我想大家写属性可不是按顺序的吧，所以在反射遍历时候，就有可能将几个本来待在某个列位置的值去换了位置的情况。所以，这里在遍历的时候，应该按插入的完全格式来设计，也就是 insert tableName(Field,Field...) values(value,value...)。

　　在这几个方法中，Delete最简单。

public DbHelper Select(string Fields = "*",Dictionary<string,object> Where = null,string otherWhere = "")
        {
            sqlBuilderString = new StringBuilder();

            sqlBuilderString.AppendLine("select " + Fields + " from " + this.ExcuteName);
            List<SqlParameter> paras = new List<SqlParameter>();
            sqlBuilderString.AppendLine(" where 1 = 1 ");            
            if (Where != null && Where.Count > 0)
            {
                paras = new List<SqlParameter>();                //遍历Where，将里面的条件添加到sqlParamter和sql语句中
                Where.Keys.ToList().ForEach(o => {
                    sqlBuilderString.AppendLine(" and "+ o + " = @" + o);
                    paras.Add(new SqlParameter(o, Where[o]));
                });
            }            if(!string.IsNullOrEmpty(otherWhere))
            {
                sqlBuilderString.AppendLine(otherWhere);
            }            this.paras = paras.ToArray();            return this;
        }

public DbHelper Update(object model,Dictionary<string, object> Where,string Fields = "", string otherWhere = "")
        {
            Type t = model.GetType();
            List<string> keys = Where.Keys.ToList();
            sqlBuilderString = new StringBuilder();            bool firstnode = true;
            sqlBuilderString.AppendLine("update "+ExcuteName + " set ");
            List<SqlParameter> paras = new List<SqlParameter>();            if(string.IsNullOrEmpty(Fields))
            {
                t.GetProperties().ToList().ForEach(o =>
                {                    if (!firstnode)
                        sqlBuilderString.Append(",");                    else
                        firstnode = false;                    if(!keys.Contains(o.Name))
                    {
                        sqlBuilderString.AppendLine(o.Name + " = @"+o.Name);
                        paras.Add(new SqlParameter(o.Name,o.GetValue(model,null)));
                    }
                });
            }else
            {
                Fields.Split(&#39;,&#39;).ToList().ForEach(o =>
                {
                    sqlBuilderString.AppendLine(o + " = @" + o);
                    paras.Add(new SqlParameter(o, t.GetProperty(o).GetValue(model, null)));
                });
            }            this.paras = paras.ToArray();            return this;
        }

public DbHelper Insert(object model,string Fields = "")
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            Type t = model.GetType();
            sqlBuilderString = new StringBuilder();
            sqlBuilderString.AppendLine("insert " + ExcuteName);            if(string.IsNullOrEmpty(Fields))
            {                string s = "";                string s1="";
                t.GetProperties().ToList().ForEach(o =>
                {
                    s += o.Name + ",";
                    s1 += " @" + o.Name + ",";
                    paras.Add(new SqlParameter(o.Name, o.GetValue(model, null)));
                });
                s.Remove(s.LastIndexOf(&#39;,&#39;),1);
                s1.Remove(s.LastIndexOf(&#39;,&#39;), 1);
                sqlBuilderString.AppendLine("(" + s + ")");
                sqlBuilderString.AppendLine(" values(" + s1 + ")");
            }else
            {
                sqlBuilderString.AppendLine("(" + Fields + ")");                string s = "";
                Fields.Split(&#39;,&#39;).ToList().ForEach(o =>
                {
                    s += " @" + o + ",";
                    paras.Add(new SqlParameter(o, t.GetProperty(o).GetValue(model, null)));
                });
                sqlBuilderString.AppendLine(" values(" + s + ")");
            }            this.paras = paras.ToArray();            return this;
        }

public DbHelper Delete(Dictionary<string,object> Where,string otherWhere = "")
        {
            sqlBuilderString = new StringBuilder();
            List<SqlParameter> paras = new List<SqlParameter>();
            sqlBuilderString.AppendLine("delete " + ExcuteName);
            sqlBuilderString.AppendLine(" where 1 = 1 ");

            Where.Keys.ToList().ForEach(o =>
            {
                sqlBuilderString.AppendLine(" and " + o + " = @" + o);
                paras.Add(new SqlParameter(o, Where[o]));
            });            this.paras = paras.ToArray();            return this;
        }

最后一个阶段，那就是执行阶段，这里封装了些执行的方法。

这个也是简单，最重要的方法应该是setCommand，这个方法是对sqlCommand进行设置，执行的语句，以及添加参数。

private void setCommand()
        {            if(com.CommandType== CommandType.StoredProcedure)
            {                this.com.CommandText = ExcuteName;
            }else
            {                this.com.CommandText = sqlBuilderString.ToString();
            }            this.paras.ToList().ForEach(o =>
            {                this.com.Parameters.Add(o);
            });
        }

其他就是执行的语句。

public List<T> ToList<T>()            where T:class ,new()
        {
            List<T> list = new List<T>();
            setCommand();
            SqlDataReader reader = com.ExecuteReader();
            Type t = typeof(T);
            List<PropertyInfo> pros = t.GetProperties().ToList();            while(reader.Read())
            {
                T model = new T();
                pros.ForEach(o =>
                {
                    o.SetValue(model, reader[o.Name], null);
                });
                list.Add(model);
            }
            reader.Dispose();            return list;
        }        public DataSet ToDataSet(string DatasetName = "")
        {
            DataSet ds = new DataSet();
            setCommand();
            SqlDataAdapter adapter = new SqlDataAdapter(com);

            adapter.Fill(ds, string.IsNullOrEmpty(DatasetName) ? this.ExcuteName.Replace(",", "_") : DatasetName);
            adapter.Dispose();            return ds;
        }        public DataTable ToDataTable()
        {
            DataTable dt = new DataTable();
            setCommand();
            SqlDataAdapter adapter = new SqlDataAdapter(com);
            adapter.Fill(dt);
            adapter.Dispose();            return dt;
        }        public object Result()
        {
            setCommand();            return com.ExecuteScalar();
        }        public int ExcuteResult()
        {
            setCommand();            return com.ExecuteNonQuery();
        }        public DbHelper ExcuteProc(Dictionary<string,object> Parameter)
        {
            List<SqlParameter> paras = new List<SqlParameter>();

            Parameter.Keys.ToList().ForEach(o =>
            {
                paras.Add(new SqlParameter(o, Parameter[o]));
            });            return this;
        }

当然，还不能少了让用户自定义的方法，所以最后还留了个方法，参数是委托。委托里面的参数还是动态类型，这就懂了吧，想用户怎么用，你就怎么定义。

public void UserDefineOperation(Action<dynamic> fun)
        {
            fun(this.com);
        }

好了，设计也就到这里，下面就贴上SQLHelper完整的代码。

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Reflection;namespace Dal
{    public class SQLHelper:DbHelper
    {        //连接字符串
        string ConnectionString;        
        //数据库连接对象
        private SqlConnection conn;        //执行对象        SqlCommand com;        //表、存储过程、视图名称
        string ExcuteName;        //事务        SqlTransaction tran;        //sql语句        StringBuilder sqlBuilderString;        //参数        SqlParameter[] paras;        private SQLHelper()
        {

        }        /// 

        /// 创建sqlHelper静态方法        /// 
        /// 
        public static DbHelper getInstance()
        {            return new SQLHelper();
        }        /// 

        /// 
        /// 
        /// 
        /// 
        public DbHelper createConnection(string connectionString)
        {            if (!ConnectionCanUse())
            {                this.ConnectionString = connectionString;
                conn = new SqlConnection(this.ConnectionString);
            }            return this;
        }        /// 

        /// 检查conn是否能用        /// 
        /// 
        public bool ConnectionCanUse()
        {            if (conn == null)                return false;            try
            {
                conn.Open();
                conn.Close();
            }catch(Exception e)
            {                return false;
            }            
            return true;
        }        /// 

        /// 
        /// 
        /// 
        public DbHelper openConnection()
        {            if(conn.State != ConnectionState.Open)            this.conn.Open();            return this;
        }        /// 

        /// 
        /// 
        /// 
        public DbHelper closeConnection()
        {            if(conn.State != ConnectionState.Closed)            this.conn.Close();            return this;
        }        /// 

        /// 
        /// 
        public void DisposedConnection()
        {            if (!ConnectionBeUsed())                this.conn.Dispose();
        }        /// 

        /// 检查数据库是否在被打开使用        /// 
        /// 
        public bool ConnectionBeUsed()
        {            if(conn.State == ConnectionState.Open)                return true;            return false;
        }        /// 

        /// 
        /// 
        /// 
        public DbHelper createCommand()
        {            if (this.com == null)
            {                this.com = new SqlCommand();
                com.Connection = this.conn;
            }           
            return this;
        }        /// 

        /// 
        /// 
        public void DisposedCommand()
        {            this.com.Dispose();
        }        /// 

        /// 
        /// 
        /// 
        /// 
        public DbHelper setCommandType(CommandType type)
        {            this.com.CommandType = type;            return this;
        }        /// 

        /// 
        /// 
        /// 
        /// 
        public DbHelper FromName(string Name)
        {            this.ExcuteName = Name;            return this;
        }        /// 

        /// 
        /// 
        /// 
        public DbHelper beginTransaction()
        {            this.tran = conn.BeginTransaction();
            com.Transaction = this.tran;            return this;
        }        /// 

        /// 
        /// 
        /// 
        public DbHelper TransactionRowBack()
        {            if(tran!=null)
            {
                tran.Rollback();
            }            return this;
        }        /// 

        /// 
        /// 
        /// 
        public DbHelper TransactionCommit()
        {            if(tran!=null)
            {
                tran.Commit();
                tran = null;
            }            return this;
        }        /// 

        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        public DbHelper Select(string Fields = "*",Dictionary<string,object> Where = null,string otherWhere = "")
        {
            sqlBuilderString = new StringBuilder();

            sqlBuilderString.AppendLine("select " + Fields + " from " + this.ExcuteName);
            List<SqlParameter> paras = new List<SqlParameter>();
            sqlBuilderString.AppendLine(" where 1 = 1 ");            
            if (Where != null && Where.Count > 0)
            {
                paras = new List<SqlParameter>();                //遍历Where，将里面的条件添加到sqlParamter和sql语句中
                Where.Keys.ToList().ForEach(o => {
                    sqlBuilderString.AppendLine(" and "+ o + " = @" + o);
                    paras.Add(new SqlParameter(o, Where[o]));
                });
            }            if(!string.IsNullOrEmpty(otherWhere))
            {
                sqlBuilderString.AppendLine(otherWhere);
            }            this.paras = paras.ToArray();            return this;
        }        
        public DbHelper ForMulTable(string Fields)
        {
            List tables = ExcuteName.Split(',').ToList();
            Fields.Split(',').ToList().ForEach(o =>
            {                for(int i = 0;i
最后还有两个事务的方法，前面忘记说了，其实就是SqlTransaction，在里面的sqlCommand加上这个，就可以实现。或许有人会问，如果有同一时间段有好几个sqlCommand怎么办？不会的，sqlCommand我也设置成单例，就不会发生控制不了的事情了。
 
 Conclusion: This is my first blog. Although I have done a lot of "childish works", after all, I am a junior student. If I write casual articles, I am worried that I will just become a target. The object of ridicule, the childish "work" is too embarrassed to be posted online for everyone to see. So, after thinking about it for a few days, I wrote a package that I think is quite useful. Although it may not work for many projects, readers can think more deeply about it themselves. 
 I think this framework should be better encapsulated, such as combining SQL statements and handling exceptions when calling. How to better realize chain combination, multi-database processing control, and adding locks, I think it is also possible. After all, when doing web, it is not like winform, each end has its own connection. Another thing I think is good is to do processing on the model and add features so that the framework can identify primary keys and foreign keys, establish connections in SQL, etc. in the program. Then it’s up to the readers to think. 
 
The above is the detailed content of Detailed explanation of the encapsulation of smarter database operations in C# .NET. For more information, please follow other related articles on the PHP Chinese website!