快捷導(dǎo)航

.Net core下直接執(zhí)行SQL語句并生成DataTable的實(shí)現(xiàn)方法

更新時(shí)間：2016年11月04日 09:11:16 作者：BenDan2002

.net core可以執(zhí)行SQL語句，但是只能生成強(qiáng)類型的返回結(jié)果。這篇文章主要介紹了.Net core下直接執(zhí)行SQL語句并生成DataTable的相關(guān)資料,需要的朋友可以參考下

.net core可以執(zhí)行SQL語句，但是只能生成強(qiáng)類型的返回結(jié)果。例如var blogs = context.Blogs.FromSql("SELECT * FROM dbo.Blogs").ToList()。而不允許返回DataSet、DataTable等弱類型?？赡苡捎谶@個(gè)原因沒有實(shí)現(xiàn)在.net core中DataTable，然而DataTable還是可能會(huì)用到的。我們這里就有一個(gè)數(shù)據(jù)倉庫的需求，允許用戶自行編寫類似SQL語句，然后執(zhí)行，以表格展示。因?yàn)檎Z句是千變?nèi)f化的，因此我也不知道用戶的語句輸出的是啥，更無法以類型來定義，因此只能采用DataTable方式。

之前.net framework下，可以通過dataadpater很方便的填充datatable，然后將datatable的數(shù)據(jù)推送到客戶端展示。但是.net core下，已經(jīng)沒有DataTable和DataSet，我們只能自行實(shí)現(xiàn)MicroDataTable。

這里我們也按照DataTable的方式，MicroDataTable的列定義為MicroDataColumn，行定義為MicroDataRow。代碼如下：

public class MicroDataTable
{ /// <summary>
/// 整個(gè)查詢語句結(jié)果的總條數(shù)，而非本DataTable的條數(shù)
/// </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屬性，在分頁情況下，是指查詢語句在數(shù)據(jù)庫中查詢出的所有記錄條數(shù)，而MicroDataTable的數(shù)據(jù)是當(dāng)前頁面的記錄。

對(duì)于從數(shù)據(jù)庫中獲取DataTable的做法，采用類似SqlHelper的方式編寫DbContext的ExecuteDataTable擴(kuò)展方法，傳入SQL語句和SQL語句的參數(shù)，生成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);
}
}

這個(gè)方法還是需要部分.net framework core的技巧的，流程是根據(jù)SQL和參數(shù)創(chuàng)建原生的SQLCommand，執(zhí)行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。我們只能先這樣實(shí)現(xiàn)，以后看是否ef.core能否改變或者給出更好的方式。

上面程序中，最后有一句話MicroDataTableHelper.FillDataTable，這個(gè)方法的主要功能是從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;
}

上面這個(gè)程序，是按部就班的寫法，效率應(yīng)該不太高。最近時(shí)間緊，沒有分析原先的Datatable裝載方式，以后有時(shí)間優(yōu)化吧。

下面給出一個(gè)當(dāng)時(shí)用.net framework從datareader獲取分頁數(shù)據(jù)到datatable的程序，僅作參考。當(dāng)時(shí)這段程序使用了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數(shù)據(jù)庫cmd.Cancel()會(huì)報(bào)錯(cuò)，所以把錯(cuò)誤忽略了。
{
cmd.Cancel();
}
catch 
{ 
}
reader.Close();
}
}

以上所述是小編給大家介紹的.Net core下直接執(zhí)行SQL語句并生成DataTable，希望對(duì)大家有所幫助，如果大家有任何疑問請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！

您可能感興趣的文章: