.Net core下如何實現直接執行SQL語句并生成DataTable

這篇文章主要介紹了.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”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!