用多个输出包装多个查询

本文关键字：查询 包装 输出 | 更新日期: 2023-09-27 18:17:26

我有这个类，在这个类中，我正在包装dapper调用，以便做一些像

var results = SqlWrapper.ExecuteQuery<Product,Customer>("SELECT id FROM Products; SELECT id FROM Customers;");

, 

results[0] = List<Product>
results[1] = List<Customer>

我支持1,2,3个输出对象，但希望是任意的。这个类也很丑，充满了复制和粘贴的代码。如果我想通过可选地传递连接来重用连接，但代码似乎不干净。我真正想要的是一种定义params T[]的方法，但据我所知，这行不通。这段代码可以清理/缩短吗?

using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
namespace SqlWrapper
{
    public static class SqlWrapper
    {
        private const string SqlConnectionString = "Server=localhost;Database=TTDS;User Id=sa;Password=sa;";

        public static List<T> ExecuteQuery<T>(string sql, object param = null, SqlConnection sqlConnection = null)
        {
            if (sqlConnection != null)
            {
                return sqlConnection.Query<T>(sql, param).ToList();
            }
            using (var tempSqlConnection = new SqlConnection(SqlConnectionString))
            {
                tempSqlConnection.Open();
                return tempSqlConnection.Query<T>(sql, param).ToList();
            }
        }

        public static List<dynamic> ExecuteQuery<T1, T2>(string sql, object param = null, SqlConnection sqlConnection = null)
        {
            if (sqlConnection != null)
            {
                return MultiQuery<T1, T2>(sqlConnection, sql, param);
            }
            using (var tempSqlConnection = new SqlConnection(SqlConnectionString))
            {
                return MultiQuery<T1, T2>(tempSqlConnection, sql, param);
            }
        }
        public static List<dynamic> ExecuteQuery<T1, T2, T3>(string sql, object param = null,
            SqlConnection sqlConnection = null)
        {
            if (sqlConnection != null)
            {
                return MultiQuery<T1, T2, T3>(sqlConnection, sql, param);
            }
            using (var tempSqlConnection = new SqlConnection(SqlConnectionString))
            {
                return MultiQuery<T1, T2, T3>(tempSqlConnection, sql, param);
            }
        }
        private static List<dynamic> MultiQuery<T1, T2>(SqlConnection sqlConnection, string sql, object param = null)
        {
            var rv = new List<dynamic>();
            using (var grid = sqlConnection.QueryMultiple(sql, param))
            {
                rv.Add(grid.Read<T1>().ToList());
                rv.Add(grid.Read<T2>().ToList());
            }
            return rv;
        }
        private static List<dynamic> MultiQuery<T1, T2, T3>(SqlConnection sqlConnection, string sql, object param = null)
        {
            var rv = new List<dynamic>();
            using (var grid = sqlConnection.QueryMultiple(sql, param))
            {
                rv.Add(grid.Read<T1>().ToList());
                rv.Add(grid.Read<T2>().ToList());
                rv.Add(grid.Read<T3>().ToList());
            }
            return rv;
        }

        public static void ExecuteNonQuery(SqlConnection sqlConnection, string sql, object param, int? timeout = null)
        {
            if (sqlConnection != null)
            {
                sqlConnection.Execute(sql, param, commandTimeout: timeout);
            }
            else
            {
                using (var tempSqlConnection = new SqlConnection(SqlConnectionString))
                {
                    tempSqlConnection.Open();
                    tempSqlConnection.Execute(sql, param, commandTimeout: timeout);
                }
            }
        }
    }
}

用多个输出包装多个查询

下面是一些未经测试的代码，它展示了我的一些想法。

  1. 虽然"using"非常棒，但如果您选择先创建连接，然后在必要时在finally块中处置sqlConnection，则可以减少一些代码。
  2. 如果你返回一个Tuple<List<T>,List<U>,List<V>>，你可以有强类型的返回值，你可以很容易地使用
  3. 如果你从那些不太复杂的函数中调用最复杂的函数，你可以最大限度地减少重复代码。
public static class SqlWrapper
{
    private const string SqlConnectionString = "Server=localhost;Database=TTDS;User Id=sa;Password=sa;";
    private class NoResult { }
    public static List<T1> ExecuteQuery<T1>(string sql, object param = null, SqlConnection sqlConnection = null)
    {
        return ExecuteQuery<T1, NoResult, NoResult>(sql, param, sqlConnection).Item1;
    }
    public static Tuple<List<T1>, List<T2>> ExecuteQuery<T1, T2>(string sql, object param = null, SqlConnection sqlConnection = null)
    {
        var result = ExecuteQuery<T1, T2, NoResult>(sql, param, sqlConnection);
        return Tuple.Create(result.Item1, result.Item2);
    }
    public static Tuple<List<T1>, List<T2>, List<T3>> ExecuteQuery<T1, T2, T3>(string sql, object param = null, SqlConnection sqlConnection = null)
    {
        List<T1> list1;
        List<T2> list2 = null;
        List<T3> list3 = null;
        bool needsDisposed = false;
        if (sqlConnection == null)
        {
            sqlConnection = new SqlConnection(SqlConnectionString);
            sqlConnection.Open();
            needsDisposed = true;
        }
        try
        {
            using (var grid = sqlConnection.QueryMultiple(sql, param))
            {
                list1 = grid.Read<T1>().ToList();
                if (typeof(T2) != typeof(NoResult))
                {
                    list2 = grid.Read<T2>().ToList();
                }
                if (typeof(T3) != typeof(NoResult))
                {
                    list3 = grid.Read<T3>().ToList();
                }
                return Tuple.Create(list1, list2, list3);
            }
        }
        finally { if (needsDisposed) sqlConnection.Dispose(); }
    }

    public static void ExecuteNonQuery(SqlConnection sqlConnection, string sql, object param, int? timeout = null)
    {
        bool needsDisposed = false;
        if (sqlConnection == null)
        {
            sqlConnection = new SqlConnection(SqlConnectionString);
            sqlConnection.Open();
            needsDisposed = true;
        }
        try { sqlConnection.Execute(sql, param, commandTimeout: timeout); }
        finally { if (needsDisposed) sqlConnection.Dispose(); }
    }
}
相关文章：