.Net core下直接执行SQL语句并生成DataTable的实现方法
.net core可以执行sql语句,但是只能生成强类型的返回结果。例如var blogs = context.blogs.fromsql("select * from dbo.blogs").tolist()。而不允许返回dataset、datatable等弱类型。可能由于这个原因没有实现在.net core中datatable,然而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,希望对大家有所帮助