CPQuery SQL字符串拼接之 —— 凑热闹

昨天拜读了一下 Fish Li 的 CPQuery, 解决拼接SQL的新方法，整体上还是很赞的，提供了一种新的数据库查询方式，这让我想起了很久之前 CoolCode 写的一个字符串拼接类（StringJoiner），个人认为很不错，确实拯救了不少前同事留下来的烂代码。

 

不过，我认为 Fish Li 的代码还有一些不足之处，因为涉及的改动比较多，所以我在这里另辟一块地方，发表出来，希望 Fish Li 理解我的意思，谢谢！代码整体上借用 Fish Li 的思想，不过在一些细节问题上的处理有些不同，不多说了。上代码：

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

namespace TestParameterJoin
{
    class Program
    {
        static void Main(string[] args)
        {
            var con = new SqlConnection();
            var cmd = con.CreateCommand();
            cmd.CommandText = "select * from table where 1 = 1";

            var builder = cmd.AsBuilder() + " and a = " + "abc" +
                                            " and b = " + 3 +
                                            " and c = " + true +
                                            " and d = " + DateTime.Now;

            // Build: select * from table where 1 = 1 and a = @p0 and b = @p1 and c = @p2 and d = @p3
            builder.Build();

            // 接着做你的数据库操作
        }
    }

    public sealed class DbCommandBuilder
    {
        private StringBuilder sbSql = new StringBuilder();
        private IDbCommand cmd = null;
        private bool isBuilded = false;

        public DbCommandBuilder(IDbCommand command)
        {
            cmd = command;
        }

        public static DbCommandBuilder operator +(DbCommandBuilder builder, string obj)
        {
            if (builder.isBuilded) throw new ApplicationException("Has builded!");

            var isParameter = obj.IndexOf('=') == -1;
            var parameterName = GetParameterName(builder);

            if (isParameter)
                builder.AppendParameter(DbType.String, () => (object)obj ?? DBNull.Value);
            else
                builder.sbSql.Append(obj).Append(parameterName);

            return builder;
        }

        public static DbCommandBuilder operator +(DbCommandBuilder builder, int? obj)
        {
            if (builder.isBuilded) throw new ApplicationException("Has builded!");

            return builder.AppendParameter(DbType.Int32, () => obj.HasValue ? (object)obj.Value : DBNull.Value);
        }

        public static DbCommandBuilder operator +(DbCommandBuilder builder, bool? obj)
        {
            if (builder.isBuilded) throw new ApplicationException("Has builded!");

            return builder.AppendParameter(DbType.Boolean, () => obj.HasValue ? (object)(obj.Value ? 1 : 0) : DBNull.Value);
        }

        public static DbCommandBuilder operator +(DbCommandBuilder builder, DateTime? obj)
        {
            if (builder.isBuilded) throw new ApplicationException("Has builded!");

            return builder.AppendParameter(DbType.DateTime, () => obj.HasValue ? (object)obj.Value : DBNull.Value);
        }

        // 这里你还可以继续添加你想要的类型

        private DbCommandBuilder AppendParameter(DbType dbType, Func<object> func)
        {
            var parameterName = GetParameterName(this);

            var param = cmd.CreateParameter();
            param.DbType = dbType;
            param.Value = func();
            param.ParameterName = parameterName;

            cmd.Parameters.Add(param);

            return this;
        }

        private static string GetParameterName(DbCommandBuilder builder)
        {
            return string.Format("@p{0}", builder.cmd.Parameters.Count);
        }

        public IDbCommand Build()
        {
            isBuilded = true;
            var sql = sbSql.ToString();
            if (!string.IsNullOrWhiteSpace(sql))
                cmd.CommandText += sql;
            return cmd;
        }

        public override string ToString()
        {
            return cmd.CommandText + (isBuilded ? string.Empty : sbSql.ToString());
        }
    }

    public static class DbCommandBuilderExtension
    {
        public static DbCommandBuilder AsBuilder(this IDbCommand cmd)
        {
            return new DbCommandBuilder(cmd);
        }
    }
}