.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，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！