.NET 6 API + Dapper + SQL Server 2014

🕗 发布于 2024-09-21 05:43 c# .net 数据库

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）！

上一篇：Docker学习
下一篇：零工市场小程序：推动零工市场建设

算数基本定理@质因数分解原理
把自然数写成素数的乘积，结论就是著名的算术基本定理。此定理建立了自然数与素数之间的一个重要的关系式。算数基本定理是整除理论性质和结论的精华,是整个初等数论的基础证明一些方程是否有整数解能够从公式的角度
阅读更多2024-11-15
用 Python 从零开始创建神经网络（六）：优化（Optimization）介绍
在随机初始化的模型中，或者即使是采用更复杂方法初始化的模型中，我们的目标是随着时间的推移培训或教育一个模型。为了训练一个模型，我们调整权重和偏差以提高模型的准确性和置信度。为此，我们需要计算模型的错误
阅读更多2024-11-15
海思3403对RTSP进行目标检测
用ss928 实现rtsp流的目标检测
阅读更多2024-11-15
亮数据——助力全球数据抓取的高效代理平台
特别是在需要跨境数据采集的场景中，亮数据的全球代理池能够让用户根据目标网站的地域需求，选择不同地区的IP地址，以此提升数据抓取的稳定性和成功率。亮数据不仅提供了丰富的IP资源，还内置了智能管理系统，可
阅读更多2024-11-15
【卡尔曼滤波】递归算法Recursive的应用 C语言、Python实现（Kalman Filter）
【卡尔曼滤波】递归算法Recursive的应用 C语言、Python实现（Kalman Filter）
阅读更多2024-11-15
记一次工作中订单幂等处理方案
当然可以考虑在数据库新增一张表，这张表上有第三方订单号的字段，并为此字段建立唯一索引，每次下单前预先往这张表插入数据，如果此第三方订单号已存在，那么插入数据必然报错。第一次是通过分布式锁检测，这是在
阅读更多2024-11-15
大数据湖项目建设方案（100页WORD）
随着信息技术的飞速发展，数据量呈现出爆炸性增长的趋势。企业、政府机构及科研单位在运营过程中积累了大量的数据资源，这些数据中蕴含着巨大的价值，但如何高效地存储、处理和分析这些数据，成为了一个亟待解决的问
阅读更多2024-11-15
深度学习笔记14-卷积神经网络2
卷积神经网络，是包含卷积运算且具有深度结构的前馈神经网络。在卷积神经网络中，包含卷积层、池化层和全连接层三种重要的结构。相比前馈神经网络，卷积层和池化层是新增的网络结构，在提取特征时，卷积神经网络使用
阅读更多2024-11-15
[Mysql基础] 表的操作
1.1 语法说明：field表示列名datatype表示列的类型字符集，如果没有指定字符集，则以所在数据库的字符集为准collate校验规则，如果没有指定校验规则，则以所在数据库的校验规则为准。
阅读更多2024-11-15
Mysql-DDL语句
Mysql DDL语句的使用方法和配置命令，可以更加快速熟悉的掌握Mysql基本操作命令
阅读更多2024-11-15

.NET 6 API + Dapper + SQL Server 2014

相关文章