.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();
}
}

以上所述是小編給大家介紹的.Net core下直接執行SQL語句並生成DataTable，希望對大家有所幫助，如果大家有任何疑問請給我留言，小編會及時回復大家的。在此也非常感謝大家對腳本之家網站的支持！