【手撸一个ORM】第三步、SQL语句构造器和SqlParameter封装

既然是数据库工具，自然少不了增删改查的sql语句，在这里将这些常用SQL拼接操作集成到 [SqlServerBuilder.cs] 当中，方便后面调用。

近几年在项目中一直使用Dapper操作数据库，感觉其实现的DynamicParameters特别炫，所以尝试封装了一个类似的方法  [MyDbParameters]，功能比较简单，仅覆盖了工作中用到的一部分功能，算是个简陋版吧。

一、SQL语句构造器，用于生成参数化查询、更新、插入的SQL语句

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MyOrm.Commons;
using MyOrm.Reflections;

namespace MyOrm.SqlBuilder
{
    public class SqlServerBuilder
    {
        protected readonly string Prefix = "@";

        #region 查询
        public string Select(string table, string columns, string where, string sort, int top = 0)
        {
            if (table == null) throw new ArgumentNullException(nameof(table));

            var sb = new StringBuilder("SELECT ");
            if (top > 0)
            {
                sb.Append("TOP ").Append(top.ToString()).Append(" ");
            }

            sb.Append(string.IsNullOrWhiteSpace(columns) ? "*" : columns);

            sb.Append(" FROM ").Append(table);

            if (!string.IsNullOrWhiteSpace(where))
            {
                sb.Append(" WHERE ").Append(where);
            }

            if (!string.IsNullOrWhiteSpace(sort))
            {
                sb.Append(" ORDER BY ").Append(sort);
            }

            return sb.ToString();
        }

        public string PagingSelect(string table, string columns, string where, string sort, int pageIndex, int pageSize)
        {
            pageIndex = pageIndex <= 0 ? 1 : pageIndex;
            pageSize = pageSize <= 0 ? 20 : pageSize;
            where = string.IsNullOrWhiteSpace(where) ? "1=1" : where;

            if (pageIndex == 1)
            {
                var sql = Select(table, columns, where, sort, pageSize);
                sql += $";SELECT @RecordCount=COUNT(0) FROM {table} WHERE {where}";
                return sql;
            }

            columns = string.IsNullOrWhiteSpace(columns) ? "*" : columns;
            sort = string.IsNullOrWhiteSpace(sort) ? "(SELECT 1)" : sort;

            var sb = new StringBuilder();
            sb.Append("SELECT ")
                .Append(columns)
                .Append(" FROM ")
                .Append(table)
                .Append(" WHERE ")
                .Append(where)
                .Append(" ORDER BY ")
                .Append(sort)
                .Append(" OFFSET ")
                .Append((pageIndex - 1) * pageSize)
                .Append(" ROWS FETCH NEXT ")
                .Append(pageSize)
                .Append(" ROWS ONLY;");
            sb.Append("SELECT @RecordCount=COUNT(0) FROM ")
                .Append(table)
                .Append(string.IsNullOrWhiteSpace(where) ? "" : " WHERE " + where);

            return sb.ToString();
        }

        public string PagingSelect2008(string tables, string cols, string condition, string orderBy, int index, int size)
        {
            if (string.IsNullOrWhiteSpace(orderBy))
            {
                orderBy = "(select 1)";
            }

            if (string.IsNullOrWhiteSpace(condition))
            {
                condition = "1=1";
            }

            if (index == 1)
            {
                var sql =
                    $"SELECT TOP {size} {cols} FROM {tables} WHERE {condition} ORDER BY {orderBy};SELECT {Prefix}RecordCount=COUNT(0) FROM {tables} WHERE {condition}";

                return sql;
            }
            else
            {
                var sb = new StringBuilder();
                sb.Append("FROM ").Append(tables);
                sb.Append(" WHERE ").Append(condition);
                var sql = $@"  WITH PAGEDDATA AS
                        (
                            SELECT TOP 100 PERCENT {cols}, ROW_NUMBER() OVER (ORDER BY {@orderBy}) AS FLUENTDATA_ROWNUMBER
                            {sb}
                        )
                        SELECT *
                        FROM PAGEDDATA
                        WHERE FLUENTDATA_ROWNUMBER BETWEEN {(index - 1) * size + 1} AND {index * size};
                        SELECT {Prefix}RecordCount=COUNT(0) FROM {tables} WHERE {condition}";
                return sql;
            }
        }
        #endregion

        #region 插入
        public string InsertIfNotExists(MyEntity entityInfo, string where)
        {
            var sb = new StringBuilder();
            sb.Append("IF NOT EXISTS (SELECT 1 FROM [")
                .Append(entityInfo.TableName)
                .Append("] WHERE ")
                .Append(where)
                .Append(")");
            sb.Append(Insert(entityInfo));
            return sb.ToString();
        }

        public string Insert(MyEntity entityInfo)
        {
            if (entityInfo == null) throw new ArgumentNullException(nameof(entityInfo));

            var sb = new StringBuilder();
            var columns = new List<string>();
            var parameters = new List<string>();

            foreach (var prop in entityInfo.Properties.Where(p => !p.InsertIgnore))
            {
                columns.Add("[" + prop.FieldName + "]");
                parameters.Add(Prefix + prop.Name);
            }

            sb.Append("INSERT INTO [")
                .Append(entityInfo.TableName)
                .Append("] (")
                .Append(string.Join(",", columns))
                .Append(") VALUES (")
                .Append(string.Join(",", parameters))
                .Append(");SELECT SCOPE_IDENTITY();");
            return sb.ToString();
        }
        #endregion

        #region 更新
        public string Update(MyEntity entityInfo, string where)
        {
            if (entityInfo == null) throw new ArgumentNullException(nameof(entityInfo));

            var sb = new StringBuilder();
            sb.Append("UPDATE [")
                .Append(entityInfo.TableName)
                .Append("] SET ");

            var clauses = entityInfo.Properties.Where(p => !p.UpdateIgnore)
                .Select(p => $"{p.FieldName}={Prefix}{p.Name}");
            sb.Append(string.Join(",", clauses));

            sb.Append(string.IsNullOrWhiteSpace(where) ? $" WHERE [{entityInfo.KeyColumn}]={Prefix}Id" : where);

            return sb.ToString();
        }

        public string UpdateIgnore(MyEntity entityInfo, string[] propertyList, bool ignoreAttribute, string where)
        {
            if (entityInfo == null) throw new ArgumentNullException(nameof(entityInfo));

            var properties = entityInfo.Properties;

            var updateProperties = ignoreAttribute
                ? properties.Where(p => !propertyList.Contains(p.Name)).ToList()
                : properties.Where(p => !p.UpdateIgnore && !propertyList.Contains(p.Name)).ToList();

            if (properties.Count == 0)
            {
                throw new ArgumentNullException(nameof(properties), "要更新的列为空");
            }

            var sb = new StringBuilder();
            sb.Append("UPDATE [")
                .Append(entityInfo.TableName)
                .Append("] SET ");

            var clauses = updateProperties.Select(p => $"{p.FieldName}={Prefix}{p.Name}");
            sb.Append(string.Join(",", clauses));
            sb.Append(string.IsNullOrWhiteSpace(where) ? $" WHERE [{entityInfo.KeyColumn}]={Prefix}Id" : where);
            return sb.ToString();
        }

        public string Update(string table, DbKvs kvs, string where)
        {
            if (kvs == null || kvs.Count == 0) throw new ArgumentNullException(nameof(kvs));
            if (string.IsNullOrWhiteSpace(table)) throw new ArgumentNullException(nameof(table));

            var sb = new StringBuilder();
            sb.Append("UPDATE [")
                .Append(table)
                .Append("] SET ");
            var clauses = kvs.Select(kv => $"[{kv.Key}]={Prefix}{kv.Key}");
            sb.Append(string.Join(",", clauses));
            if (!string.IsNullOrWhiteSpace(where))
            {
                sb.Append(" WHERE ").Append(where);
            }

            return sb.ToString();
        }
        #endregion
    }
}

 二、MyDbParameters，在Add(object obj)方法中还是用到了反射，没想好怎么优化这部分内容，先留个坑

注意：若添加了同名参数，后面的会覆盖前面的。

using System.Data;

namespace MyOrm.DbParameters
{
    public class MyDbParameter
    {
        public string Name { get; set; }

        public object Value { get; set; }

        public ParameterDirection? Direction { get; set; }
    }

    public class MyDbParameters
    {
        private readonly List<MyDbParameter> _dict = new List<MyDbParameter>();

        private readonly string _prefix = "@";

        public MyDbParameters()
        { }

        public MyDbParameters(string prefix)
        {
            _prefix = prefix;
        }

        public SqlParameter[] Parameters
        {
            get
            {
                if (_dict.Count == 0)
                {
                    return new SqlParameter[]{};
                }

                var list = new List<SqlParameter>();
                foreach (var item in _dict)
                {
                    var param = new SqlParameter($@"{item.Name}", item.Value);
                    if (item.Direction != null)
                    {
                        param.Direction = item.Direction.Value;
                    }
                    list.Add(param);
                }

                return list.ToArray();
            }
        }

        public void Add(string parameterName, object value)
        {
            var item = _dict.FirstOrDefault(d => d.Name == parameterName);
            var newItem = new MyDbParameter {Name = parameterName, Value = value};

            if (item == null)
            {
                _dict.Add(newItem);
            }
            else
            {
                item = newItem;
            }
        }

        public void Add(string parameterName, object value, ParameterDirection direction)
        {
            var item = _dict.FirstOrDefault(d => d.Name == parameterName);
            var newItem = new MyDbParameter { Name = parameterName, Value = value, Direction = direction};

            if (item == null)
            {
                _dict.Add(newItem);
            }
            else
            {
                item = newItem;
            }
        }

        public void Add(object obj)
        {
            if (obj is MyDbParameter parameter)
            {
                _dict.Add(parameter);
            }
            else if(obj is IEntity)
            {
                var entityInfo = MyEntityContainer.Get(obj.GetType());
                foreach(var property in entityInfo.Properties.Where(p => p.IsMap))
                {
                    Add(property.Name, property.PropertyInfo.GetValue(obj));
                }
            }
            else
            {
                var properties = obj.GetType().GetProperties();
                foreach (var property in properties.Where(p => p.PropertyType.IsValueType || 
                                                               p.PropertyType == typeof(string)))
                {
                    Add(property.Name, property.GetValue(obj));
                }
            }
        }

        public void AddParameters(MyDbParameters parameters)
        {
            foreach (var item in parameters._dict)
            {
                Add(item);
            }
        }

        public void Add(SqlParameter parameter)
        {
            Add(parameter.ParameterName, parameter.Value);
        }

        public void Add(SqlParameter[] parameters)
        {
            foreach(var item in parameters)
            {
                Add(item);
            }
        }
    }
}

使用方法：

var student = new Student {
    Id = 3,
    Name = "张三"
}

var updateParameters = new MyDbParameters();
updateParameters.Add(student);

var searchParameters = new MyDbParameters();
searchParameters.Add("Name", "张三");
searchParameters.Add("Id", 3);

updateParameters.Add(searchParameters);

// 获取SqlParameter[]
var sqlParameters = updateParameters.Parameters;