.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中文網的支持!