自学内容网 自学内容网

.NET 6 API + Dapper + SQL Server 2014

 C# (.NET 6 API) + Dapper + SQL Server 2014及以上

Packages:

<PackageReference Include="Dapper" Version="2.0.143" />

<PackageReference Include="Serilog.Sinks.File" Version="5.0.0" />

<PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.1" />

创建ISqlConnectionResolver和他的实现类SqlConnectionResolver.cs,此方法用于在.NET6 API 中根据需要访问不通的数据库地址。

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Configuration;

namespace Data.DapperRepository
{
    public sealed class DBConnectionStrings
    {
        public const string DbConnectionReadOnly = "DbReadOnly";
        public const string DbConnectionReadWrite = "DbReadWrite";
        public const string ConfigurationDbConnection = "Configuration";
    }

    public interface ISqlConnectionResolver
    {
        SqlConnection GetSqlConnection(string connectionStringName);
    }

    public class SqlConnectionResolver : ISqlConnectionResolver
    {
        private readonly IConfiguration _configuration;

        public SqlConnectionResolver(IConfiguration configuration)
        {
            _configuration = configuration;
        }

        #region Web Api Implementation
        public SqlConnection GetSqlConnection(string connectionStringName)
        {
            string? connectionString = "";
            if (string.IsNullOrWhiteSpace(connectionStringName) || connectionStringName == DBConnectionStrings.DbConnectionReadWrite)
            {
                connectionString = _configuration.GetSection("ConnectionStrings")[DBConnectionStrings.DbConnectionReadWrite];
            }
            else if (connectionStringName == DBConnectionStrings.DbConnectionReadOnly)
            {
                connectionString = _configuration.GetSection("ConnectionStrings")[DBConnectionStrings.DbConnectionReadOnly];
            }
            else if (connectionStringName == DBConnectionStrings.ConfigurationDbConnection)
            {
                connectionString = _configuration.GetSection("ConnectionStrings")[DBConnectionStrings.ConfigurationDbConnection];
            }
            else
            {
                connectionString = _configuration.GetSection("ConnectionStrings")[DBConnectionStrings.DbConnectionReadWrite];
            }
            return new SqlConnection(connectionString);
        }

        #endregion
    }

 
}

SqlMapperExtensions.cs 用于扩张Dapper参数转换成Type类型的table(user definded table in SQL Server)。此方法用于解决 where Id in (1,2,3,...) 在 SQL server 中的限制。

/****** Object:  UserDefinedTableType [dbo].[IdTable]    Script Date: 9/20/2024 5:06:32 PM ******/
CREATE TYPE [dbo].[IdTable] AS TABLE(
[Id] [int] NOT NULL
)



--Demo store procedure to introduce how to call UserDefinedTableType 
/* TEST BELOW
DECLARE @UserIds [dbo].[IdTable]
INSERT INTO @UserIds
SELECT 10

INSERT INTO @UserIds
SELECT 833

 EXEC SP_GET_USERS_BY_IDS @UserIds

*/
CREATE PROCEDURE [dbo].[SP_GET_USERS_BY_IDS]
(
   @UserIds [dbo].[IdTable] READONLY
)
AS
BEGIN
SELECT n.Id,NetWorkUserName,FirstName,LastName FROM @UserIds m join ApplicationUsers n with (nolock) on m.Id=n.Id
END

 Insert store procedure demo

/*TEST BELOW
EXEC SP_INSERT_API_AUDITTRAIL 59169,'2023-08-24 14:07',10,'Evan Test'
*/
CREATE PROCEDURE [dbo].[SP_INSERT_API_AUDITTRAIL]
@UserIdint = 0,
@ExecutionTimedatetime=null,
@ExecutionDurationint=0,
@ServiceNamenvarchar(256)=null,
@MethodNamenvarchar(256)=null,
@Parametersnvarchar(1024)=null,
@ClientIpAddressnvarchar(64)=null,
@BrowserInfonvarchar(256)=null,
@Exceptionnvarchar(2000)=null,
@CustomDatanvarchar(2000)=null
AS
BEGIN
if ISNULL(@ExecutionTime,'')=''
begin
set @ExecutionTime=GETUTCDATE();
end
insert into AbpAuditLogs(
 UserId
,ExecutionTime
,ExecutionDuration
,ServiceName
,MethodName
,[Parameters]
,ClientIpAddress
,BrowserInfo
,[Exception]
,CustomData
) values(
 @UserId
,@ExecutionTime
,@ExecutionDuration
,@ServiceName
,@MethodName
,@Parameters
,@ClientIpAddress
,@BrowserInfo
,@Exception
,@CustomData)

END
GO
using System.Data;
using Dapper;
using System.Reflection;

namespace Data.DapperRepository
{
    /// <summary>
    ///  Demo: new { UserIds = userIds.AsTableValuedParameter("dbo.IdTable", new List<string>() { "Id" }), Parameter2 = parameter2 }
    /// </summary>
    public static class SqlMapperExtensions
    {
        /// <summary>
        /// This extension converts an enumerable set to a Dapper TVP
        /// </summary>
        /// <typeparam name="T">type of enumerbale</typeparam>
        /// <param name="enumerable">list of values</param>
        /// <param name="typeName">database type name</param>
        /// <param name="orderedColumnNames">if more than one column in a TVP, 
        /// columns order must mtach order of columns in TVP</param>
        /// <returns>a custom query parameter</returns>
        public static SqlMapper.ICustomQueryParameter AsTableValuedParameter<T>
            (this IEnumerable<T> enumerable,
            string typeName, IEnumerable<string> orderedColumnNames = null)
        {
            var dataTable = new DataTable();
            if (typeof(T).IsValueType || typeof(T).FullName.Equals("System.String"))
            {
                dataTable.Columns.Add(orderedColumnNames == null ?
                    "NONAME" : orderedColumnNames.First(), typeof(T));
                foreach (T obj in enumerable)
                {
                    dataTable.Rows.Add(obj);
                }
            }
            else
            {
                PropertyInfo[] properties = typeof(T).GetProperties
                    (BindingFlags.Public | BindingFlags.Instance);
                PropertyInfo[] readableProperties = properties.Where
                    (w => w.CanRead).ToArray();
                if (readableProperties.Length > 1 && orderedColumnNames == null)
                    throw new ArgumentException("Ordered list of column names must be provided when TVP contains more than one column");

                var columnNames = (orderedColumnNames ??
                    readableProperties.Select(s => s.Name)).ToArray();
                foreach (string name in columnNames)
                {
                    dataTable.Columns.Add(name, readableProperties.Single
                        (s => s.Name.Equals(name)).PropertyType);
                }

                foreach (T obj in enumerable)
                {
                    dataTable.Rows.Add(
                        columnNames.Select(s => readableProperties.Single
                            (s2 => s2.Name.Equals(s)).GetValue(obj))
                            .ToArray());
                }
            }
            return dataTable.AsTableValuedParameter(typeName);
        }
    }
}

Interface for Repository

 using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;

namespace Data.DapperRepository.Interfaces
{
    public interface IDbRepository
    {
        Task<List<UserDto>> GetUsersByIdsAsync(List<int> userIds);

        bool AddAuditTrailLog(AbpAuditLogDto abpAuditLogDto);
    }
}

Implement class

using Dapper;
using Microsoft.Data.SqlClient;
using System.Data;
using Data.DapperRepository.Interfaces;

namespace Data.DapperRepository
{
    public class DbRepository : IDbRepository
    {
        private readonly ISqlConnectionResolver _sqlConnectionResolver;

        public CorproLogRepository(ISqlConnectionResolver sqlConnectionResolver)
        {
            _sqlConnectionResolver = sqlConnectionResolver ?? throw new ArgumentNullException(nameof(sqlConnectionResolver));
        }

        private SqlConnection GetReadOnlyConnection()
        {
            return _sqlConnectionResolver.GetSqlConnection(DBConnectionStrings.DbConnectionReadOnly);
        }

        private SqlConnection GetReadWriteDbConnection()
        {
            return _sqlConnectionResolver.GetSqlConnection(DBConnectionStrings.DbConnectionReadWrite);
        }

        public async Task<List<UserDto>> GetUsersByIdsAsync(List<int> userIds)
        {
            List<UserDto> users = new();

            SqlConnection DbConnection = GetReadOnlyConnection();
            DbConnection.Open();
            Log.Debug($"GetFacilityLenderTreeNodesByFacilityId, Connection Name={DBConnectionStrings.DbConnectionReadOnly}");
            users = (await DbConnection.QueryAsync<UserDto>(sql: "SP_GET_USERS_BY_IDS",
                 param: new { UserIds = userIds.AsTableValuedParameter("dbo.IdTable", new List<string>() { "Id" }) },
                 commandType: CommandType.StoredProcedure)).ToList();

            DbConnection.Close();


            return users;
        }


        public bool AddAuditTrailLog(AbpAuditLogDto abpAuditLogDto)
        {

            SqlConnection dbConnection = GetReadWriteDbConnection();
            dbConnection.Open();
            int num = 0;
            SqlCommand comm = new("SP_INSERT_API_AUDITTRAIL", dbConnection)
            {
                CommandType = CommandType.StoredProcedure,
                CommandTimeout = 300
            };

            comm.Parameters.AddWithValue("@UserId", abpAuditLogDto.UserId);

            if (abpAuditLogDto.ExecutionTime.HasValue)
            {
                comm.Parameters.AddWithValue("@ExecutionTime", abpAuditLogDto.ExecutionTime);
            }
            comm.Parameters.AddWithValue("@ExecutionDuration", abpAuditLogDto.ExecutionDuration);
            comm.Parameters.AddWithValue("@ServiceName", abpAuditLogDto.ServiceName);
            comm.Parameters.AddWithValue("@MethodName", abpAuditLogDto.MethodName);
            comm.Parameters.AddWithValue("@Parameters", abpAuditLogDto.Parameters);
            comm.Parameters.AddWithValue("@ClientIpAddress", abpAuditLogDto.ClientIpAddress);
            comm.Parameters.AddWithValue("@BrowserInfo", abpAuditLogDto.BrowserInfo);
            if (!string.IsNullOrEmpty(abpAuditLogDto.Exception))
            {
                comm.Parameters.AddWithValue("@Exception", abpAuditLogDto.Exception);
            }
            if (!string.IsNullOrEmpty(abpAuditLogDto.CustomData))
            {
                comm.Parameters.AddWithValue("@CustomData", abpAuditLogDto.CustomData);
            }

            num = comm.ExecuteNonQuery();
            dbConnection.Close();
            return num > 0;

        }

    }

}

利用DI在.NET 6 API的Program.cs中注册ISqlConnectionResolver, and IDbRepository

//Register ISqlConnectionResolver, and IDbRepository
services.AddTransient<ISqlConnectionResolver, SqlConnectionResolver>();

services.AddTransient<IDbRepository, DbRepository>();

Dapper常用functions。 IDapperManager and DapperManager

using Microsoft.Data.SqlClient;
using System.Data;

namespace Data.Interfaces
{
    public interface IDapperManager
    {
        int Execute(string sqlCommand, object parameters, int? commandTimeout = null, CommandType commandType = CommandType.Text);
        int Execute(string sqlCommand, object parameters, CommandType commandType);
        List<SqlParameter> ExecuteWithOutput(string sqlCommand, object parameters, CommandType commandType);
        /// <summary>
        /// Function to invoke SQL command async with CommandType, and has Retry mode.
        /// </summary>
        Task<int> ExecuteAsync(string sqlCommand, object parameters);
        string ExecuteNonQuery(string sqlCommand, object parameters, CommandType commandType);
        SqlConnection GetSqlConnection();
        List<int> QueryEntities(string sqlCommand, object parameters);
        List<int> QueryEntities(string sqlCommand, object parameters, CommandType commandType);
        List<T> QueryEntities<T>(string sqlCommand, object parameters);
        List<T> QueryEntities<T>(string sqlCommand, object parameters, CommandType commandType);
        List<T6> QueryEntities<T1, T2, T3, T4, T5, T6>(string sqlCommand, object parameters, Func<T1, T2, T3, T4, T5, T6> mapLogic, string splitOn, CommandType commandType);
        List<T5> QueryEntities<T1, T2, T3, T4, T5>(string sqlCommand, object parameters, Func<T1, T2, T3, T4, T5> mapLogic, string splitOn);
        List<T5> QueryEntities<T1, T2, T3, T4, T5>(string sqlCommand, object parameters, Func<T1, T2, T3, T4, T5> mapLogic, string splitOn, CommandType commandType);
        List<T4> QueryEntities<T1, T2, T3, T4>(string sqlCommand, object parameters, Func<T1, T2, T3, T4> mapLogic, string splitOn);
        List<T4> QueryEntities<T1, T2, T3, T4>(string sqlCommand, object parameters, Func<T1, T2, T3, T4> mapLogic, string splitOn, CommandType commandType);
        List<T3> QueryEntities<T1, T2, T3>(string sqlCommand, object parameters, Func<T1, T2, T3> mapLogic, string splitOn, CommandType commandType = CommandType.Text);
        Task<List<T>> QueryEntitiesAsync<T>(string sqlCommand, object parameters) where T : class, new();
        Tuple<List<T1>, List<T2>, List<T3>> QueryMultiEntities<T1, T2, T3>(string sqlCommand, object parameters, CommandType commandType = CommandType.StoredProcedure);
        Tuple<List<T1>, List<T2>> QueryMultiEntities<T1, T2>(string sqlCommand, object parameters, CommandType commandType = CommandType.StoredProcedure);
    }
}
using Dapper;
using Microsoft.Data.SqlClient;
using Serilog;
using System.Data;
using static Dapper.SqlMapper;
using System.Data;
using Data.Interfaces;

namespace Data.Managers
{
    public class DapperManager : IDapperManager
    {
        #region Members

        private string _connectionString { get; set; }

        #endregion

        #region Ctors

        public DapperManager(string connectionString)
        {
            _connectionString = connectionString;
        }

        #endregion

        #region Functions

        public List<int> QueryEntities(string sqlCommand, object parameters, CommandType commandType)
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    List<int> returnValues = Connection
                        .Query<int>(sqlCommand, parameters, commandType: commandType)
                        .ToList();
                    Connection.Close();
                    Connection.Dispose();
                    return returnValues;
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
            return new List<int>();
        }

        public List<int> QueryEntities(string sqlCommand, object parameters)
        {
            return QueryEntities(sqlCommand, parameters, CommandType.Text);
        }

        public List<T> QueryEntities<T>(string sqlCommand, object parameters, CommandType commandType)
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    List<T> returnValues = Connection.Query<T>(sqlCommand, parameters, commandType: commandType).ToList();
                    Connection.Close();
                    Connection.Dispose();
                    return returnValues;
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                    throw;
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
        }

        public List<T> QueryEntities<T>(string sqlCommand, object parameters)
        {
            return QueryEntities<T>(sqlCommand, parameters, CommandType.Text);
        }

        public async Task<List<T>> QueryEntitiesAsync<T>(string sqlCommand, object parameters) where T : class, new()
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    var returnValues = Connection.QueryAsync<T>(sqlCommand, parameters);
                    await returnValues;
                    Connection.Close();
                    Connection.Dispose();
                    return returnValues.Result.ToList();
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
            return new List<T>();
        }

        public List<T3> QueryEntities<T1, T2, T3>(string sqlCommand, object parameters, Func<T1, T2, T3> mapLogic, string splitOn, CommandType commandType = CommandType.Text)
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    List<T3> returnValues = Connection
                        .Query(sqlCommand, mapLogic, parameters, commandType: commandType, splitOn: splitOn)
                        .ToList();
                    Connection.Close();
                    Connection.Dispose();
                    return returnValues;
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
            return new List<T3>();
        }

        public List<T4> QueryEntities<T1, T2, T3, T4>(string sqlCommand, object parameters, Func<T1, T2, T3, T4> mapLogic, string splitOn)
        {
            return QueryEntities(sqlCommand, parameters, mapLogic, splitOn, CommandType.Text);
        }

        public List<T4> QueryEntities<T1, T2, T3, T4>(string sqlCommand, object parameters, Func<T1, T2, T3, T4> mapLogic, string splitOn, CommandType commandType)
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    List<T4> returnValues = Connection
                        .Query(sqlCommand, mapLogic, parameters, splitOn: splitOn, commandType: commandType)
                        .ToList();
                    Connection.Close();
                    Connection.Dispose();
                    return returnValues;
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
            return new List<T4>();
        }

        public List<T5> QueryEntities<T1, T2, T3, T4, T5>(string sqlCommand, object parameters, Func<T1, T2, T3, T4, T5> mapLogic, string splitOn, CommandType commandType)
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    List<T5> returnValues = Connection
                        .Query(sqlCommand, mapLogic, parameters, splitOn: splitOn, commandType: commandType)
                        .ToList();
                    Connection.Close();
                    Connection.Dispose();
                    return returnValues;
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
            return new List<T5>();
        }

        public List<T5> QueryEntities<T1, T2, T3, T4, T5>(string sqlCommand, object parameters, Func<T1, T2, T3, T4, T5> mapLogic, string splitOn)
        {
            return QueryEntities(sqlCommand, parameters, mapLogic, splitOn, CommandType.Text);
        }

        public List<T6> QueryEntities<T1, T2, T3, T4, T5, T6>(string sqlCommand, object parameters, Func<T1, T2, T3, T4, T5, T6> mapLogic, string splitOn, CommandType commandType)
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    List<T6> returnValues = Connection
                        .Query(sqlCommand, mapLogic, parameters, splitOn: splitOn, commandType: commandType)
                        .ToList();
                    Connection.Close();
                    Connection.Dispose();
                    return returnValues;
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }

            return new List<T6>();
        }

        public int Execute(string sqlCommand, object parameters, int? commandTimeout = null, CommandType commandType = CommandType.Text)
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    int returnValues = Connection.Execute(sqlCommand, parameters, commandTimeout: commandTimeout, commandType: commandType);
                    Connection.Close();
                    Connection.Dispose();
                    return returnValues;
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
            return -1;  // Failure.
        }

        public int Execute(string sqlCommand, object parameters, CommandType commandType)
        {
            using (SqlConnection connection = GetSqlConnection())
            using (SqlCommand command = connection.CreateCommand())
            {
                try
                {
                    command.CommandText = sqlCommand;
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var p in (List<SqlParameter>)parameters)
                        {
                            command.Parameters.Add(p);
                        }
                    }

                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (parameters != null && parameters is List<SqlParameter>)
                    {
                        SqlParameter sqlParameter = ((List<SqlParameter>)parameters).Last();
                        if (sqlParameter != null && (sqlParameter.Direction == ParameterDirection.ReturnValue || sqlParameter.Direction == ParameterDirection.Output) && sqlParameter.SqlDbType == SqlDbType.Int)
                        {
                            result = (int)((List<SqlParameter>)parameters).Last().Value;
                        }
                    }
                    return result;
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }

        public List<SqlParameter> ExecuteWithOutput(string sqlCommand, object parameters, CommandType commandType)
        {
            using (SqlConnection connection = GetSqlConnection())
            using (SqlCommand command = connection.CreateCommand())
            {
                try
                {
                    command.CommandText = sqlCommand;
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var p in (List<SqlParameter>)parameters)
                        {
                            command.Parameters.Add(p);
                        }
                    }
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (parameters != null && parameters is List<SqlParameter>)
                    {
                        var sqlParameters = (List<SqlParameter>)parameters;
                        return sqlParameters.Where(x => x.Direction == ParameterDirection.Output).ToList();
                    }
                }
                catch (Exception ex)
                {
                    Log.Error(ex.Message, ex);
                }
                finally
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return null;
        }

        public async Task<int> ExecuteAsync(string sqlCommand, object parameters)
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    int returnValues = await Connection.ExecuteAsync(sqlCommand, parameters, commandType: CommandType.StoredProcedure);
                    Connection.Close();
                    Connection.Dispose();
                    return returnValues;
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
            return 0;
        }

        /// <summary>
        /// Function to ExecuteNonQuery and return messages for this sqlCommand result.
        /// </summary>
        public string ExecuteNonQuery(string sqlCommand, object parameters, CommandType commandType)
        {
            var messages = string.Empty;

            using (var connection = GetSqlConnection())
            using (var command = connection.CreateCommand())
            {
                try
                {
                    connection.Open();
                    command.CommandText = sqlCommand;
                    command.CommandType = commandType;
                    if (parameters != null)
                    {
                        foreach (var p in (List<SqlParameter>)parameters)
                        {
                            command.Parameters.Add(p);
                        }
                    }
                    connection.FireInfoMessageEventOnUserErrors = true;
                    connection.InfoMessage += (sender, args) =>
                    {
                        messages += "\r\n" + args.Message;
                    };

                    command.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                    messages += "\r\n" + e.Message;
                }
                finally
                {
                    connection.Close();
                }
            }

            return messages;
        }

        public Tuple<List<T1>, List<T2>> QueryMultiEntities<T1, T2>(string sqlCommand, object parameters, CommandType commandType = CommandType.StoredProcedure)
        {
            using (SqlConnection Connection = GetSqlConnection())
            {
                try
                {
                    Connection.Open();
                    var reader = Connection
                        .QueryMultiple(sqlCommand, parameters, commandType: commandType);
                    List<T1> list1 = new List<T1>();
                    List<T2> list2 = new List<T2>();
                    if (reader != null)
                    {
                        list1 = reader?.Read<T1>()?.ToList();
                        list2 = reader?.Read<T2>()?.ToList();
                    }
                    Connection.Close();
                    Connection.Dispose();
                    return Tuple.Create(list1, list2);
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
            return null;
        }

        public Tuple<List<T1>, List<T2>, List<T3>> QueryMultiEntities<T1, T2, T3>(string sqlCommand, object parameters, CommandType commandType = CommandType.StoredProcedure)
        {
            using (SqlConnection Connection = new SqlConnection())
            {
                try
                {
                    Connection.Open();
                    GridReader reader = Connection
                        .QueryMultiple(sqlCommand, parameters, commandType: commandType);
                    List<T1> list1 = new List<T1>();
                    List<T2> list2 = new List<T2>();
                    List<T3> list3 = new List<T3>();
                    if (reader != null)
                    {
                        IEnumerable<T1> en1 = reader.Read<T1>();
                        IEnumerable<T2> en2 = reader.Read<T2>();
                        list3 = reader.Read<T3>().ToList();
                        if (en1 != null)
                        {
                            list1 = en1.ToList();
                        }
                        if (en2 != null)
                        {
                            list2 = en2.ToList();
                        }
                    }
                    Connection.Close();
                    Connection.Dispose();
                    return Tuple.Create(list1, list2, list3);
                }
                catch (Exception e)
                {
                    Log.Error(e.Message, e);
                }
                finally
                {
                    Connection.Close();
                    Connection.Dispose();
                }
            }
            return null;
        }

        #endregion

        #region Help Function

        public SqlConnection GetSqlConnection()
        {
            return new SqlConnection(_connectionString);
        }

        #endregion
    }
}


原文地址:https://blog.csdn.net/wzcool273509239/article/details/142390275

免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!