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

2017-02-15 21:20:10 .net
.net core可以执行SQL语句,但是只能生成强类型的返回结果。这篇文章主要介绍了.Net core下直接执行SQL语句并生成DataTable的相关资料,需要的朋友可以参考下

.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();
}
}
原文链接:http://www.cnblogs.com/BenDan2002/archive/2016/11/03/6018973.html