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

时间:2021-05-28

.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[i];}set{int i = 0;foreach (MicroDataColumn column in Columns){if (column.ColumnName == columnName)break;i++;}_ItemArray[i] = 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,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对网站的支持!

声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。

相关文章