aspnetboilerplate && .net core 使用原生sql

利用aspnetboilerplate提供的工具类IDbContextProvider

private readonly IDbContextProvider<XXXDbContext> _provider;
public XXXAppService(IDbContextProvider<XXXDbContext> provider)
{
    _provider = provider;
}

public async Task Query()
{
    var sql="your sql";
    var tableList = _provider.GetDbContext().Query<Class>()
                             .FromSql(sql)
                             .AsNoTracking()
                             .ToList();
}

这里Class必须是Model，如果是联表查询，无法转Model，建议取出DataSet，再手动转Dto

参考资料

Raw SQL Queries
How to execute a sql string in the application layer

比较原始 && 通用的方式

public class SqlHelper
{

	public DataTable QueryForDatable(string sql)
	{
		SqlConnection conn = new System.Data.SqlClient.SqlConnection();
		conn.ConnectionString = GetConnectionString(conn);
		if (conn.State != ConnectionState.Open)
		{
			conn.Open();
		}

		SqlCommand cmd = new SqlCommand();
		cmd.Connection = conn;
		cmd.CommandText = sql;

		SqlDataAdapter adapter = new SqlDataAdapter(cmd);
		DataTable table = new DataTable();
		adapter.Fill(table);

		conn.Close();
		conn.Dispose();
		return table;

	}

	//简而言之，拿到数据库连接字符串
	private static string GetConnectionString(SqlConnection conn)
	{
	        //这里需要拿到appsettings.json所在文件夹路径
		var currentDirectoryPath = Directory.GetCurrentDirectory();
		var configuration = AppConfigurations.Get(currentDirectoryPath);
		//connectionStringName是数据库连接字符串在appsettings.json中的对应名称
		return configuration.GetConnectionString(connectionStringName);
	}

	/// <summary>
	/// SQL 语句返回 dataset
	/// </summary>
	/// <param name="sql"></param>
	/// <returns></returns>
	public DataSet QueryForListDataTable(string sql)
	{
		SqlConnection conn = new SqlConnection();
		conn.ConnectionString = GetConnectionString(conn);
		if (conn.State != ConnectionState.Open)
		{
			conn.Open();
		}
		SqlCommand cmd = new SqlCommand();
		cmd.Connection = conn;
		cmd.CommandText = sql;
		SqlDataAdapter adapter = new SqlDataAdapter(cmd);
		DataSet dataSet = new DataSet();
		adapter.Fill(dataSet);

		conn.Close();
		conn.Dispose();
		return dataSet;
	}
}