.Net core下直接执行SQL语句并生成DataTable的实现方法
.netcore可以执行SQL语句,但是只能生成强类型的返回结果。例如varblogs=context.Blogs.FromSql("SELECT*FROMdbo.Blogs").ToList()。而不允许返回DataSet、DataTable等弱类型。可能由于这个原因没有实现在.netcore中DataTable,然而DataTable还是可能会用到的。我们这里就有一个数据仓库的需求,允许用户自行编写类似SQL语句,然后执行,以表格展示。因为语句是千变万化的,因此我也不知道用户的语句输出的是啥,更无法以类型来定义,因此只能采用DataTable方式。
之前.netframework下,可以通过dataadpater很方便的填充datatable,然后将datatable的数据推送到客户端展示。但是.netcore下,已经没有DataTable和DataSet,我们只能自行实现MicroDataTable。
这里我们也按照DataTable的方式,MicroDataTable的列定义为MicroDataColumn,行定义为MicroDataRow。代码如下:
publicclassMicroDataTable {///<summary> ///整个查询语句结果的总条数,而非本DataTable的条数 ///</summary> publicintTotalCount{get;set;} publicList<MicroDataColumn>Columns{get;set;}=newList<MicroDataColumn>(); publicList<MicroDataRow>Rows{get;set;}=newList<MicroDataRow>(); publicMicroDataColumn[]PrimaryKey{get;set;} publicMicroDataRowNewRow() { returnnewMicroDataRow(this.Columns,newobject[Columns.Count]); } } publicclassMicroDataColumn { publicstringColumnName{get;set;} publicTypeColumnType{get;set;} } publicclassMicroDataRow { privateobject[]_ItemArray; publicList<MicroDataColumn>Columns{get;privateset;} publicMicroDataRow(List<MicroDataColumn>columns,object[]itemArray) { this.Columns=columns; this._ItemArray=itemArray; } publicobjectthis[intindex] { get{return_ItemArray[index];} set{_ItemArray[index]=value;} } publicobjectthis[stringcolumnName] { get { inti=0; foreach(MicroDataColumncolumninColumns) { if(column.ColumnName==columnName) break; i++; } return_ItemArray[i]; } set { inti=0; foreach(MicroDataColumncolumninColumns) { if(column.ColumnName==columnName) break; i++; } _ItemArray[i]=value; } } }
需要注意的是TotalCount属性,在分页情况下,是指查询语句在数据库中查询出的所有记录条数,而MicroDataTable的数据是当前页面的记录。
对于从数据库中获取DataTable的做法,采用类似SqlHelper的方式编写DbContext的ExecuteDataTable扩展方法,传入SQL语句和SQL语句的参数,生成MicroDataTable:
publicstaticMicroDataTableExecuteDataTable(thisDbContextcontext,stringsql,paramsobject[]parameters) { varconcurrencyDetector=context.Database.GetService<IConcurrencyDetector>(); using(concurrencyDetector.EnterCriticalSection()) { varrawSqlCommand=context.Database.GetService<IRawSqlCommandBuilder>().Build(sql,parameters); RelationalDataReaderquery=rawSqlCommand.RelationalCommand.ExecuteReader(context.Database.GetService<IRelationalConnection>(),parameterValues:rawSqlCommand.ParameterValues); returnMicroDataTableHelper.FillDataTable(query.DbDataReader,0,int.MaxValue); } } publicstaticMicroDataTableExecuteDataTable(thisDbContextcontext,stringsql,intpageIndex,intpageSize,paramsobject[]parameters) { varconcurrencyDetector=context.Database.GetService<IConcurrencyDetector>(); using(concurrencyDetector.EnterCriticalSection()) { varrawSqlCommand=context.Database.GetService<IRawSqlCommandBuilder>().Build(sql,parameters); RelationalDataReaderquery=rawSqlCommand.RelationalCommand.ExecuteReader(context.Database.GetService<IRelationalConnection>(),parameterValues:rawSqlCommand.ParameterValues); returnMicroDataTableHelper.FillDataTable(query.DbDataReader,0,int.MaxValue); } }
这个方法还是需要部分.netframeworkcore的技巧的,流程是根据SQL和参数创建原生的SQLCommand,执行ExecuteReader方法返回DataReader,再把DataReader填充到MicroDataTable中。注意的是,IConcurrencyDetector在.netcore的描述是这样的:ThisAPIsupportstheEntityFrameworkCoreinfrastructureandisnotintendedtobeuseddirectlyfromyourcode.ThisAPImaychangeorberemovedinfuturereleases。我们只能先这样实现,以后看是否ef.core能否改变或者给出更好的方式。
上面程序中,最后有一句话MicroDataTableHelper.FillDataTable,这个方法的主要功能是从DataReader填充到MicroDataTable的。
publicstaticMicroDataTableFillDataTable(DbDataReaderreader,intpageIndex,intpageSize) { booldefined=false; MicroDataTabletable=newMicroDataTable(); intindex=0; intbeginIndex=pageSize*pageIndex; intendIndex=pageSize*(pageIndex+1)-1; while(reader.Read()) { object[]values=newobject[reader.FieldCount]; if(!defined) { for(inti=0;i<reader.FieldCount;i++) { MicroDataColumncolumn=newMicroDataColumn() { 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(newMicroDataRow(table.Columns,values)); } index++; } table.TotalCount=index; returntable; }
上面这个程序,是按部就班的写法,效率应该不太高。最近时间紧,没有分析原先的Datatable装载方式,以后有时间优化吧。
下面给出一个当时用.netframework从datareader获取分页数据到datatable的程序,仅作参考。当时这段程序使用了table.beginloaddata/endloaddata方式,效率明显有提升。
using(IDataReaderreader=cmd.ExecuteReader(CommandBehavior.CloseConnection)) { intfieldCount=reader.FieldCount; for(inti=0;i<fieldCount;i++) { table.Columns.Add(reader.GetName(i),reader.GetFieldType(i)); } object[]values=newobject[fieldCount]; intcurrentIndex=0; intstartIndex=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(); } }
以上所述是小编给大家介绍的.Netcore下直接执行SQL语句并生成DataTable,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!