郑州企业网络推广_网站建设开发计划_seo 优化顾问_杭州网站seo优化

 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 Implementationpublic 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 BELOWDECLARE @UserIds [dbo].[IdTable]INSERT INTO @UserIdsSELECT 10INSERT INTO @UserIdsSELECT 833EXEC SP_GET_USERS_BY_IDS @UserIds*/
CREATE PROCEDURE [dbo].[SP_GET_USERS_BY_IDS]
(@UserIds [dbo].[IdTable] READONLY
)
AS
BEGINSELECT n.Id,NetWorkUserName,FirstName,LastName FROM @UserIds m join ApplicationUsers n with (nolock) on m.Id=n.Id
END

 Insert store procedure demo

/*	TEST BELOWEXEC SP_INSERT_API_AUDITTRAIL 59169,'2023-08-24 14:07',10,'Evan Test'
*/
CREATE PROCEDURE [dbo].[SP_INSERT_API_AUDITTRAIL]@UserId				int = 0,@ExecutionTime		datetime=null,@ExecutionDuration	int=0,@ServiceName		nvarchar(256)=null,@MethodName			nvarchar(256)=null,@Parameters			nvarchar(1024)=null,@ClientIpAddress	nvarchar(64)=null,@BrowserInfo		nvarchar(256)=null,@Exception			nvarchar(2000)=null,@CustomData			nvarchar(2000)=null
AS
BEGINif ISNULL(@ExecutionTime,'')=''beginset @ExecutionTime=GETUTCDATE();endinsert 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 Membersprivate string _connectionString { get; set; }#endregion#region Ctorspublic DapperManager(string connectionString){_connectionString = connectionString;}#endregion#region Functionspublic 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 Functionpublic SqlConnection GetSqlConnection(){return new SqlConnection(_connectionString);}#endregion}
}