Implementing methods for directly executing SQL statements and generating a DataTable under. Net Core _ Practical Tips

. NET core can execute SQL statements, but only strongly typed return results can be generated. For example, var blogs = context. Blogs.fromsql ("SELECT * FROM dbo.") Blogs "). ToList (). It is not allowed to return weak types such as datasets, DataTable, and so on. It may not be possible for this reason to implement a DataTable in. NET Core, but the DataTable may still be used. Here we have a requirement for a data warehouse that allows users to write similar SQL statements themselves and then execute them in a tabular presentation. Because the statement is ever-changing, so I do not know what the user's statement output, but also can not be defined by type, so only the use of DataTable method.

Before the. NET Framework, you can easily populate the DataTable with Dataadpater, and then push the DataTable data to the client presentation. But under. NET core, there are no DataTable and dataset, and we can only implement microdatatable ourselves.

Here we also follow the way of the DataTable, the Microdatatable column is defined as Microdatacolumn, and the row is defined as Microdatarow. The code is as follows:

public class Microdatatable {///<summary>///the total number of results for the entire query statement, not the number of bars in this DataTable///</summary> public int Totalc
ount {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 to New Microdatarow (this.
Columns, new Object[columns.count]); The public class Microdatacolumn {public string ColumnName {get; set;} public Type ColumnType {get; set;} ' public cl Ass Microdatarow {private object[] _itemarray public list<microdatacolumn> Columns {get; private set;/Public Mi Crodatarow (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 thi S[string ColumnName] {get {int i = 0; foreACH (microdatacolumn column in Columns) {if (column).
ColumnName = = ColumnName) break;
i++;
return _itemarray[i]; The set {int i = 0; foreach (microdatacolumn column in Columns) {if (column).
ColumnName = = ColumnName) break;
i++;
} _itemarray[i] = value; }
}
}

Note that the TotalCount attribute, in the case of paging, refers to the number of all the record bars queried in the database by the query statement, while the microdatatable data is the record of the current page.

For the practice of getting a DataTable from a database, write the DbContext executedatatable extension method in a similar sqlhelper manner, pass in the parameters of the SQL statement and SQL statement, and generate microdatatable:

public static microdatatable executedatatable (this dbcontext context, string sql, params object[] parameters) {var concu Rrencydetector = 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 of the. NET Framework core techniques, and the process is to create native SqlCommand based on SQL and parameters, and perform the ExecuteReader method to return DataReader. Then fill the DataReader into the microdatatable. Note that Iconcurrencydetector's description of the. NET core is as follows: This API supports the Entity Framework Core infrastructure and are not intended To is used directly from your code. This API may or is removed in future releases. We can only do this first, later to see if Ef.core can change or give a better way.

In the program above, there is a word microdatatablehelper.filldatatable, the main function of this method is to fill from DataReader to microdatatable.

public static microdatatable filldatatable (DbDataReader reader, int pageIndex, int pageSize)
{
bool defined = FAL SE;
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 (table, new Microdatarow. Columns, values));
}
index++;
}
Table. TotalCount = index;
return table;
}

The above procedure, is the step-by-step writing, the efficiency should not be too high. The recent time is tight, did not analyze the original DataTable loading way, later has the time optimization.

The following is a program that uses the. NET Framework to obtain paging data from DataReader to a DataTable, for reference purposes only. At that time, this procedure used the Table.beginloaddata/endloaddata method, the efficiency is 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: Because the connection Aliyun ads database Cmd.cancel () will be an error, so errors ignored.
{
cmd. Cancel ();
}
Catch 
{ 
}
reader. Close ();
}
}

The above is a small set to introduce the. Net core directly Execute SQL statements and generate a DataTable, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!