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

public class MicroDataTable
{ /// 

/// 整个查询语句结果的总条数，而非本DataTable的条数
/// 
public int TotalCount { get; set; }
public List Columns { get; set; } = new List();
public List Rows { get; set; } = new List();
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 Columns { get; private set; }
public MicroDataRow(List 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;
}
}
}

public static MicroDataTable ExecuteDataTable(this DbContext context, string sql, params object[] parameters)
{
var cOncurrencyDetector= context.Database.GetService();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = context.Database.GetService().Build(sql, parameters);
RelationalDataReader query = rawSqlCommand.RelationalCommand.ExecuteReader(context.Database.GetService(), 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();
using (concurrencyDetector.EnterCriticalSection())
{
var rawSqlCommand = context.Database.GetService().Build(sql, parameters);
RelationalDataReader query = rawSqlCommand.RelationalCommand.ExecuteReader(context.Database.GetService(), parameterValues: rawSqlCommand.ParameterValues);
return MicroDataTableHelper.FillDataTable(query.DbDataReader, 0, int.MaxValue);
}
}

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 = beginIndex && index <= endIndex)
{
reader.GetValues(values);
table.Rows.Add(new MicroDataRow(table.Columns, values));
}
index++;
}
table.TotalCount = index;
return table;
}

using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
int fieldCount = reader.FieldCount;
for (int i = 0; i  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();
}
}