4.5 .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。代码如下:

 

 1     public class MicroDataTable
 2     {
 3         /// <summary>
 4         /// 整个查询语句结果的总条数,而非本DataTable的条数
 5         /// </summary>
 6         public int TotalCount { get; set; }
 7 
 8         public List<MicroDataColumn> Columns { get; set; } = new List<MicroDataColumn>();
 9 
10         public List<MicroDataRow> Rows { get; set; } = new List<MicroDataRow>();
11 
12         public MicroDataColumn[] PrimaryKey { get; set; }
13 
14         public MicroDataRow NewRow()
15         {
16             return new MicroDataRow(this.Columns, new object[Columns.Count]);
17         }
18     }
19 
20     public class MicroDataColumn
21     {
22         public string ColumnName { get; set; }
23         public Type ColumnType { get; set; }
24     }
25 
26     public class MicroDataRow
27     {
28         private object[] _ItemArray;
29         public List<MicroDataColumn> Columns { get; private set; }
30 
31         public MicroDataRow(List<MicroDataColumn> columns, object[] itemArray)
32         {
33             this.Columns = columns;
34             this._ItemArray = itemArray;
35         }
36 
37         public object this[int index]
38         {
39             get { return _ItemArray[index]; }
40             set { _ItemArray[index] = value; }
41         }
42         public object this[string columnName]
43         {
44             get
45             {
46                 int i = 0;
47                 foreach (MicroDataColumn column in Columns)
48                 {
49                     if (column.ColumnName == columnName)
50                         break;
51                     i++;
52                 }
53                 return _ItemArray[i];
54             }
55             set
56             {
57                 int i = 0;
58                 foreach (MicroDataColumn column in Columns)
59                 {
60                     if (column.ColumnName == columnName)
61                         break;
62                     i++;
63                 }
64                 _ItemArray[i] = value;
65             }
66         }
67 }
View Code

 

需要注意的是TotalCount属性,在分页情况下,是指查询语句在数据库中查询出的所有记录条数,而MicroDataTable的数据是当前页面的记录。

 

对于从数据库中获取DataTable的做法,采用类似SqlHelper的方式编写DbContext的ExecuteDataTable扩展方法,传入SQL语句和SQL语句的参数,生成MicroDataTable:

 

 1         public static MicroDataTable ExecuteDataTable(this DbContext context, string sql, params object[] parameters)
 2         {
 3             var concurrencyDetector = context.Database.GetService<IConcurrencyDetector>();
 4 
 5             using (concurrencyDetector.EnterCriticalSection())
 6             {
 7                 var rawSqlCommand = context.Database.GetService<IRawSqlCommandBuilder>().Build(sql, parameters);
 8 
 9                 RelationalDataReader query = rawSqlCommand.RelationalCommand.ExecuteReader(context.Database.GetService<IRelationalConnection>(), parameterValues: rawSqlCommand.ParameterValues);
10 
11                 return MicroDataTableHelper.FillDataTable(query.DbDataReader, 0, int.MaxValue);
12             }
13         }
14 
15         public static MicroDataTable ExecuteDataTable(this DbContext context, string sql, int pageIndex, int pageSize, params object[] parameters)
16         {
17             var concurrencyDetector = context.Database.GetService<IConcurrencyDetector>();
18 
19             using (concurrencyDetector.EnterCriticalSection())
20             {
21                 var rawSqlCommand = context.Database.GetService<IRawSqlCommandBuilder>().Build(sql, parameters);
22 
23                 RelationalDataReader query = rawSqlCommand.RelationalCommand.ExecuteReader(context.Database.GetService<IRelationalConnection>(), parameterValues: rawSqlCommand.ParameterValues);
24 
25                 return MicroDataTableHelper.FillDataTable(query.DbDataReader, 0, int.MaxValue);
26             }
27         }

 

这个方法还是需要部分.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的。

 

 1         public static MicroDataTable FillDataTable(DbDataReader reader, int pageIndex, int pageSize)
 2         {
 3             bool defined = false;
 4 
 5             MicroDataTable table = new MicroDataTable();
 6 
 7             int index = 0;
 8             int beginIndex = pageSize * pageIndex;
 9             int endIndex = pageSize * (pageIndex + 1) - 1;
10 
11             while (reader.Read())
12             {
13                 object[] values = new object[reader.FieldCount];
14 
15                 if (!defined)
16                 {
17                     for (int i = 0; i < reader.FieldCount; i++)
18                     {
19                         MicroDataColumn column = new MicroDataColumn()
20                         {
21                             ColumnName = reader.GetName(i),
22                             ColumnType = reader.GetFieldType(i)
23                         };
24 
25                         table.Columns.Add(column);
26                     }
27 
28                     defined = true;
29                 }
30 
31                 if (index >= beginIndex && index <= endIndex)
32                 {
33                     reader.GetValues(values);
34 
35                     table.Rows.Add(new MicroDataRow(table.Columns, values));
36                 }
37 
38                 index++;
39             }
40 
41             table.TotalCount = index;
42 
43             return table;
44         }

 

上面这个程序,是按部就班的写法,效率应该不太高。最近时间紧,没有分析原先的Datatable装载方式,以后有时间优化吧。下面给出一个当时用.net framework从datareader获取分页数据到datatable的程序,仅作参考。当时这段程序使用了table.beginloaddata/endloaddata方式,效率明显有提升。

 1             using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
 2             {
 3                 int fieldCount = reader.FieldCount;
 4                 for (int i = 0; i < fieldCount; i++)
 5                 {
 6                     table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
 7                 }
 8 
 9                 object[] values = new object[fieldCount];
10                 int currentIndex = 0;
11                 int startIndex = pageSize * pageIndex;
12                 try
13                 {
14                     table.BeginLoadData();
15                     while (reader.Read())
16                     {
17                         if (startIndex > currentIndex++)
18                             continue;
19 
20                         if (pageSize > 0 && (currentIndex - startIndex) > pageSize)
21                             break;
22 
23                         reader.GetValues(values);
24                         table.LoadDataRow(values, true);
25                     }
26                 }
27                 finally
28                 {
29                     table.EndLoadData();
30                     try  //lgy:由于连接阿里云ADS数据库cmd.Cancel()会报错,所以把错误忽略了。
31                     {
32                         cmd.Cancel();
33                     }
34                     catch                 
35                     {                       
36                     }
37                     reader.Close();
38                 }
39             }

 

面向云的.net core开发框架

posted @ 2016-11-03 17:03  BenDan2002  阅读(7769)  评论(7编辑  收藏  举报