The implementation method of directly executing SQL statement and generating DataTable under Net core

. net core can execute SQL statements, but only produce strongly typed return results. For example, var blogs = context. Blogs. FromSql ("SELECT * FROM dbo. Blogs"). ToList (). Weak types such as DataSet and DataTable are not allowed to be returned. DataTable may not be implemented in. net core for this reason, but DataTable may still be used. Here we have a data warehouse requirement that allows users to write their own SQL-like statements and then execute them in tabular form. Because statements are ever-changing, I don't know what the user's statement outputs, and I can't define it by type, so I can only use DataTable.

Previously, under net framework, datatable can be conveniently filled through dataadpater, and then the data of datatable can be pushed to the client for display. However, under net core, there are no DataTable and DataSet, so we can only realize MicroDataTable by ourselves.

Here, we also follow the way of DataTable, where the columns of MicroDataTable are defined as MicroDataColumn and the rows are defined as MicroDataRow. The code is as follows:

public class MicroDataTable
{ /// <summary>
///  The total number of results of the whole query statement, not the original DataTable Number of pieces of 
/// </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;
}
}
}

It should be noted that the TotalCount attribute, in the case of paging, refers to the number of records found by the query statement in the database, while the data of MicroDataTable is the record of the current page.

For obtaining DataTable from database, the ExecuteDataTable extension method of DbContext is written in the same way as SqlHelper, and the parameters of SQL statement and SQL statement are passed in to generate 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);
}
}

This method still needs some skills. net framework core. The flow is to create a native SQLCommand according to SQL and parameters, execute ExecuteReader method to return DataReader, and then fill DataReader into MicroDataTable. Note that the description of IConcurrencyDetector in. net core is as follows: This API supports the and and and and and is This This This This This API change or core core core core core core core core core core We can only do this first, and then see if ef. core can be changed or given a better way.

In the above program, there is a last sentence MicroDataTableHelper. FillDataTable. The main function of this method is to fill MicroDataTable from DataReader.

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;
}

The above procedure is written step by step, and the efficiency should not be too high. Recently, time is tight, and there is no analysis of the original Datatable loading mode, so there is time to optimize it in the future.

The following is a program for obtaining paging data from datareader to datatable with. net framework for reference only. At that time, table. beginloaddata/endloaddata was used in this program, and the efficiency was obviously improved.

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: Due to the connection to Alibaba Cloud, ADS Database cmd.Cancel() Errors will be reported, so errors are ignored. 
{
cmd.Cancel();
}
catch 
{ 
}
reader.Close();
}
}