Implementation method of directly executing SQL statements and generating DataTable under .Net core

.net core can execute SQL statements, but it can only generate strongly typed return results. For example var blogs = context.Blogs.FromSql("SELECT * FROM dbo.Blogs").ToList(). It is not allowed to return weak types such as DataSet and DataTable. It may be that DataTable is not implemented in .net core for this reason, but DataTable may still be used. We have a data warehouse requirement here that allows users to write SQL-like statements by themselves, then execute them and display them in tables. Because the 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 the DataTable method.

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

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

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

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

To obtain the DataTable from the database, use a method similar to SqlHelper to write the ExecuteDataTable extension method of DbContext, pass in the SQL statement and the parameters of the SQL statement, and generate the 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 requires some .net framework core skills Yes, the process is to create a native SQLCommand based on SQL and parameters, execute the ExecuteReader method to return the DataReader, and then fill the DataReader into the MicroDataTable. Note that the description of IConcurrencyDetector in .net core is as follows: 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. We can only implement it this way first, and see if ef.core can be changed or a better way can be given later.

In the above program, there is a sentence at the end MicroDataTableHelper.FillDataTable. The main function of this method is to fill in the MicroDataTable from the 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 program is written in a step-by-step manner and should not be very efficient. I have been short of time recently and have not analyzed the original Datatable loading method. I will have time to optimize it in the future.

The following is a program that uses .net framework to obtain paging data from datareader to datatable, for reference only. At that time, this program used the table.beginloaddata/endloaddata method, and the efficiency was significantly 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:由于连接阿里云ADS数据库cmd.Cancel()会报错，所以把错误忽略了。
{
cmd.Cancel();
}
catch
{ 
}
reader.Close();
}
}

The above is what the editor introduced to you to directly execute SQL statements and generate DataTable under .Net core. I hope it will be helpful to everyone. I would also like to thank you all for your support of the PHP Chinese website!