扩展Entity Framework通过EF执行SQL获取DataTable

一、背景

近来做一些简单的小项目，用EF通过实体类操作操作数据库，不如直接写SQL操作简单方便快捷，用EF通过实体类操作会经常用到反射有损性能。直接用SQL操作数据库有很多DBHelper实现了，EF也提供了一些直接写SQL操作数据库的方法，但是没找到获取DataTable的方法。于是自己写了一个EF扩展，通过EF获取DataTable，这样做小项目基本上也可以不用DBHelper，统一都用EF。代码如下：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Data;
 4 using System.Data.Entity;
 5 using System.Data.SqlClient;
 6 using System.Linq;
 7 using System.Text;
 8 using System.Threading.Tasks;
 9 
10 namespace CodeTreker.Utils.DB
11 {
12     public static class EFDataTableExtensions
13     {
14         public static DataTable GetDataTable(this Database database,string sql, params SqlParameter[] parameters)
15         {
16             try
17             {
18                 using (SqlDataAdapter adapter = new SqlDataAdapter(sql, database.Connection.ConnectionString))
19                 {
20                     DataTable dt = new DataTable();
21                     adapter.SelectCommand.Parameters.AddRange(parameters);
22                     adapter.Fill(dt);
23                     return dt;
24                 }
25             }
26             catch (Exception ex)
27             {
28 
29                 throw ex;
30             }
31             
32         }
33     }
34 }

使用方式如下：

1、在Web.config或者App.config中配置好EF相关配置，例如下面Web.config

 1 <?xml version="1.0"?>
 2 <!--
 3   有关如何配置 ASP.NET 应用程序的详细信息，请访问
 4   https://go.microsoft.com/fwlink/?LinkId=169433
 5 -->
 6 <configuration>
 7 
 8 
 9   <configSections>
10     <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
11   </configSections>
12   <!--
13     有关 web.config 更改的说明，请参见 http://go.microsoft.com/fwlink/?LinkId=235367。
14 
15     可在 <httpRuntime> 标记上设置以下特性。
16       <system.Web>
17         <httpRuntime targetFramework="4.5.2" />
18       </system.Web>
19   -->
20   <system.web>
21     <compilation debug="true" targetFramework="4.5.2"/>
22     <httpRuntime targetFramework="4.5.2"/>
23   </system.web>
24 
25 
26 
27   <appSettings>
28     <add key="webpages:Version" value="3.0.0.0"/>
29     <add key="webpages:Enabled" value="false"/>
30     <add key="ClientValidationEnabled" value="true"/>
31     <add key="UnobtrusiveJavaScriptEnabled" value="true"/>
32   </appSettings>
33   
34   
35   <entityFramework>
36     <providers>
37       <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
38     </providers>
39     <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
40       <parameters>
41         <parameter value="mssqllocaldb"/>
42       </parameters>
43     </defaultConnectionFactory>
44   </entityFramework>
45   <connectionStrings>
46     <add name="GeneralDbContext" connectionString="Data Source=.; Database=CodeTrekerTest; User ID=sa; Password=CodeTreker1.;" providerName="System.Data.SqlClient"/>
47   </connectionStrings>
48   
49   <system.codedom>
50     <compilers>
51       <compiler language="c#;cs;csharp" extension=".cs" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701"/>
52       <compiler language="vb;vbs;visualbasic;vbscript" extension=".vb" type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\&quot;Web\&quot; /optionInfer+"/>
53     </compilers>
54   </system.codedom>
55 </configuration>

2.继承DbContext简单定义一个操作数据库的DbContext

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Web;

namespace CodeTreker.Utils.DB
{
    public class GeneralDbContext : DbContext
    {

        public GeneralDbContext()
                : base("name=GeneralDbContext")
        {
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            return;
        }
    }
}

3.执行SQL查询出需要的DataTable：

        public static DataTable GetOrderDataTable(string orderChecker)
        {
            string orderSQL = "select * from StockBill where OrderChecker = @OrderChecker";
            using (GeneralDbContext dbContext = new GeneralDbContext())
            {
                return    dbContext.Database.GetDataTable(orderSQL, new System.Data.SqlClient.SqlParameter("@OrderChecker", orderChecker));            
            }
        }

 

posted on 2020-11-19 11:21  CodeTreker  阅读(917)  评论(0)  编辑  收藏  举报

导航