咔叽游戏

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 305|回复: 0

[ASP.NET] .Net core下直接执行SQL语句并生成DataTable的实现方法

[复制链接]
  • TA的每日心情
    无聊
    2019-5-27 08:20
  • 签到天数: 4 天

    [LV.2]圆转纯熟

    发表于 2020-2-26 14:20:22 | 显示全部楼层 |阅读模式
    .net core可以执行SQL语句,但是只能生成强类型的返回结果。例如var blogs = context.Blogs.FromSql("SELECT * FROM dbo.Blogs").ToList()。而不允许返回DataSet、DataTable等弱类型。可能由于这个原因没有实现在.net core中DataTable,然而DataTable还是可能会用到的。我们这里就有一个数据仓库的需求,允许用户自行编写类似SQL语句,然后执行,以表格展示。因为语句是千变万化的,因此我也不知道用户的语句输出的是啥,更无法以类型来定义,因此只能采用DataTable方式。
    之前.net framework下,可以通过dataadpater很方便的填充datatable,然后将datatable的数据推送到客户端展示。但是.net core下,已经没有DataTable和DataSet,我们只能自行实现MicroDataTable。
    这里我们也按照DataTable的方式,MicroDataTable的列定义为MicroDataColumn,行定义为MicroDataRow。代码如下:

    public class MicroDataTable
    { /// <summary>
    /// 整个查询语句结果的总条数,而非本DataTable的条数
    /// </summary>
    public int TotalCount { get; set; }
    public List<MicroDataColumn> Columns { get; set; } = new List<MicroDataColumn>();
    public List<MicroDataRow> Rows { get; set; } = new List<MicroDataRow>();
    public MicroDataColumn[] PrimaryKey { get; set; }
    public MicroDataRow NewRow()
    {
    return new MicroDataRow(this.Columns, new object[Columns.Count]);
    }
    }
    public class MicroDataColumn
    {
    public string ColumnName { get; set; }
    public Type ColumnType { get; set; }
    }
    public class MicroDataRow
    {
    private object[] _ItemArray;
    public List<MicroDataColumn> Columns { get; private set; }
    public MicroDataRow(List<MicroDataColumn> columns, object[] itemArray)
    {
    this.Columns = columns;
    this._ItemArray = itemArray;
    }
    public object this[int index]
    {
    get { return _ItemArray[index]; }
    set { _ItemArray[index] = value; }
    }
    public object this[string columnName]
    {
    get
    {
    int i = 0;
    foreach (MicroDataColumn column in Columns)
    {
    if (column.ColumnName == columnName)
    break;
    i++;
    }
    return _ItemArray;
    }
    set
    {
    int i = 0;
    foreach (MicroDataColumn column in Columns)
    {
    if (column.ColumnName == columnName)
    break;
    i++;
    }
    _ItemArray = value;
    }
    }
    }需要注意的是TotalCount属性,在分页情况下,是指查询语句在数据库中查询出的所有记录条数,而MicroDataTable的数据是当前页面的记录。
    对于从数据库中获取DataTable的做法,采用类似SqlHelper的方式编写DbContext的ExecuteDataTable扩展方法,传入SQL语句和SQL语句的参数,生成MicroDataTable:

    public static MicroDataTable ExecuteDataTable(this DbContext context, string sql, params object[] parameters)
    {
    var concurrencyDetector = context.Database.GetService<IConcurrencyDetector>();
    using (concurrencyDetector.EnterCriticalSection())
    {
    var rawSqlCommand = context.Database.GetService<IRawSqlCommandBuilder>().Build(sql, parameters);
    RelationalDataReader query = rawSqlCommand.RelationalCommand.ExecuteReader(context.Database.GetService<IRelationalConnection>(), parameterValues: rawSqlCommand.ParameterValues);
    return MicroDataTableHelper.FillDataTable(query.DbDataReader, 0, int.MaxValue);
    }
    }
    public static MicroDataTable ExecuteDataTable(this DbContext context, string sql, int pageIndex, int pageSize, params object[] parameters)
    {
    var concurrencyDetector = context.Database.GetService<IConcurrencyDetector>();
    using (concurrencyDetector.EnterCriticalSection())
    {
    var rawSqlCommand = context.Database.GetService<IRawSqlCommandBuilder>().Build(sql, parameters);
    RelationalDataReader query = rawSqlCommand.RelationalCommand.ExecuteReader(context.Database.GetService<IRelationalConnection>(), parameterValues: rawSqlCommand.ParameterValues);
    return MicroDataTableHelper.FillDataTable(query.DbDataReader, 0, int.MaxValue);
    }
    }这个方法还是需要部分.net framework core的技巧的,流程是根据SQL和参数创建原生的SQLCommand,执行ExecuteReader方法返回DataReader,再把DataReader填充到MicroDataTable中。注意的是,IConcurrencyDetector在.net core的描述是这样的:This API supports the Entity Framework Core infrastructure and is not intended to be used directly from your code. This API may change or be removed in future releases。我们只能先这样实现,以后看是否ef.core能否改变或者给出更好的方式。
    上面程序中,最后有一句话MicroDataTableHelper.FillDataTable,这个方法的主要功能是从DataReader填充到MicroDataTable的。

    public static MicroDataTable FillDataTable(DbDataReader reader, int pageIndex, int pageSize)
    {
    bool defined = false;
    MicroDataTable table = new MicroDataTable();
    int index = 0;
    int beginIndex = pageSize * pageIndex;
    int endIndex = pageSize * (pageIndex + 1) - 1;
    while (reader.Read())
    {
    object[] values = new object[reader.FieldCount];
    if (!defined)
    {
    for (int i = 0; i < reader.FieldCount; i++)
    {
    MicroDataColumn column = new MicroDataColumn()
    {
    ColumnName = reader.GetName(i),
    ColumnType = reader.GetFieldType(i)
    };
    table.Columns.Add(column);
    }
    defined = true;
    }
    if (index >= beginIndex && index <= endIndex)
    {
    reader.GetValues(values);
    table.Rows.Add(new MicroDataRow(table.Columns, values));
    }
    index++;
    }
    table.TotalCount = index;
    return table;
    }

    上面这个程序,是按部就班的写法,效率应该不太高。最近时间紧,没有分析原先的Datatable装载方式,以后有时间优化吧。
    下面给出一个当时用.net framework从datareader获取分页数据到datatable的程序,仅作参考。当时这段程序使用了table.beginloaddata/endloaddata方式,效率明显有提升。

    using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
    int fieldCount = reader.FieldCount;
    for (int i = 0; i < fieldCount; i++)
    {
    table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
    }
    object[] values = new object[fieldCount];
    int currentIndex = 0;
    int startIndex = pageSize * pageIndex;
    try
    {
    table.BeginLoadData();
    while (reader.Read())
    {
    if (startIndex > currentIndex++)
    continue;
    if (pageSize > 0 && (currentIndex - startIndex) > pageSize)
    break;
    reader.GetValues(values);
    table.LoadDataRow(values, true);
    }
    }
    finally
    {
    table.EndLoadData();
    try //lgy:由于连接阿里云ADS数据库cmd.Cancel()会报错,所以把错误忽略了。
    {
    cmd.Cancel();
    }
    catch
    {
    }
    reader.Close();
    }
    }以上所述是小编给大家介绍的.Net core下直接执行SQL语句并生成DataTable,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对咔叽网单www.2nzz.com网站的支持!

    QQ|免责声明|小黑屋|手机版|Archiver|咔叽游戏

    GMT+8, 2024-3-29 08:47

    Powered by Discuz! X3.4

    © 2001-2023 Discuz! Team.

    快速回复 返回顶部 返回列表