.Net core下直接執行SQL語句並產生DataTable的實作方法

.net core可以執行SQL語句，但只能產生強型別的回傳結果。例如var blogs = context.Blogs.FromSql("SELECT * FROM dbo.Blogs").ToList()。而不允許傳回DataSet、DataTable等弱類型。可能因為這個原因沒有實作在.net coreDataTable，然而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，希望對大家有幫助。在此也非常感謝大家對PHP中文網的支持！