在 MVC 6 下使用 ADO.NET 的 DataTable 的替代方案是什么? [英] What's the alternative to DataTable with ADO.NET under MVC 6?

问题描述

我正在创建一个 MVC 6 项目，我宁愿在 Entity Framework 7 上使用 Classic ADO.net.但是，据说 DataTable 和 SqlDataAdapter 都找不到名称空间.我有一个 using System.Data 和 System.Data.SqlClient 语句.在我尝试构建项目之前，它不会显示错误.

I am creating a MVC 6 project and I would rather use Classic ADO.net over Entity Framework 7. However It is saying that name space can not be found for both DataTable and SqlDataAdapter. I have a using System.Data and System.Data.SqlClient statement. It dose not show an error until I try to build the project.

我想我在某处读到这两个名称空间未在新版本中实现.如果是这样，是否有其他方法可以做到这一点，或者我是否缺少依赖项或 using 语句?

I think I read somewhere that those two names spaces are not implemented in the new version. If so is there an alternative way of doing it or am I missing a dependency or using statement?

代码:

public static DataTable GetLog(string appName)
{
    DataTable table = new DataTable("ApplicationLog");
    SqlDataAdapter da = null;

    using (SqlConnection conn = DB.GetSqlConnection())
    {
        SqlCommand cmd = new SqlCommand("select * from ApplicationLog where application_name = @appname", conn);
        cmd.Parameters.Add(new SqlParameter("appname", System.Data.SqlDbType.NVarChar, 100));
        cmd.Parameters["appname"].Value = appName;


        da = new SqlDataAdapter(cmd);

        int res = da.Fill(table);
    }

    return table;
}

我的项目.json

{
    "userSecretsId": "aspnet5-ASPDemo-b25bb1cc-00e6-401e-9f49-5b59c08a030f",
    "version": "1.0.0-*",
    "compilationOptions": {
        "emitEntryPoint": true
    },

    "dependencies": {
        "Bestro": "1.0.0-*",
        "EntityFramework.Core": "7.0.0-rc1-final",
        "EntityFramework.Commands": "7.0.0-rc1-final",
        "EntityFramework.MicrosoftSqlServer": "7.0.0-rc1-final",
        "Microsoft.ApplicationInsights.AspNet": "1.0.0-rc1",
        "Microsoft.AspNet.Authentication.Cookies": "1.0.0-rc1-final",
        "Microsoft.AspNet.Diagnostics.Entity": "7.0.0-rc1-final",
        "Microsoft.AspNet.Identity.EntityFramework": "3.0.0-rc1-final",
        "Microsoft.AspNet.IISPlatformHandler": "1.0.0-rc1-final",
        "Microsoft.AspNet.Mvc": "6.0.0-rc1-final",
        "Microsoft.AspNet.Mvc.TagHelpers": "6.0.0-rc1-final",
        "Microsoft.AspNet.Server.Kestrel": "1.0.0-rc1-final",
        "Microsoft.AspNet.StaticFiles": "1.0.0-rc1-final",
        "Microsoft.AspNet.Tooling.Razor": "1.0.0-rc1-final",
        "Microsoft.Extensions.CodeGenerators.Mvc": "1.0.0-rc1-final",
        "Microsoft.Extensions.Configuration.FileProviderExtensions": "1.0.0-rc1-final",
        "Microsoft.Extensions.Configuration.Json": "1.0.0-rc1-final",
        "Microsoft.Extensions.Configuration.UserSecrets": "1.0.0-rc1-final",
        "Microsoft.Extensions.Logging": "1.0.0-rc1-final",
        "Microsoft.Extensions.Logging.Console": "1.0.0-rc1-final",
        "Microsoft.Extensions.Logging.Debug": "1.0.0-rc1-final",
        "Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.0.0-rc1-final",
        "DataTables.AspNet.AspNet5": "2.0.0-beta2"
    },

    "commands": {
        "web": "Microsoft.AspNet.Server.Kestrel",
        "ef": "EntityFramework.Commands"
    },

    "frameworks": {
        "dnx451": {
            "frameworkAssemblies": {
                "System.configuration": "4.0.0.0",
                "System.Data": "4.0.0.0"
            }
        } 
    },

    "exclude": [
        "wwwroot",
        "node_modules"
    ],
    "publishExclude": [
        "**.user",
        "**.vspscc"
    ],
    "scripts": {
        "prepublish": [ "npm install", "bower install", "gulp clean", "gulp min" ]
    }
}

我最终尝试了很多不同的组件以尝试使其符合要求.如果有使用参考请告诉我.

I ended up trying a lot of different components in order to try to get it complied. If there are use references please let me know.

推荐答案

我理解这个问题，因为我喜欢在我的 C# 代码中使用现有的存储过程和动态 SQL 查询.为使用具有可为空属性的某些现有实体的每个查询创建新实体以保存新查询结果并不是一个好主意.此外，我喜欢使用 Web API 提供 JSON 格式的数据，以供在 JavaScript 编写的前端使用.

I understand the problem, because I like myself to use existing STORED PROCEDUREs and dynamic SQL queries in my C# code. Creating new entity for every query of the usage of some existing entity with nullable properties for saving new query results isn't a good idea. Moreover I like to use Web API to provide the data in JSON format for the usage in the frontend written in JavaScript.

另一方面，实体框架包含执行行 SQL 查询并将结果保存在没有使用实体的对象中的方法.我在答案中发布了代码片段，大家可以使用.我很快提醒了这个想法.

On the other side Entity Framework contains the way to execute row SQL Query and to save the results in object without usage entities. I posted in the answer code fragments, which one could use. I remind shortly the idea.

需要在应用程序的配置文件中包含连接字符串.例如，您可以使用

One need to include connection string to the configuration file of your application. For example you can create appsettings.json with

{
  "Data": {
    "MyDbConnectionString": "Server=(localdb)\mssqllocaldb;Database=ApplicationDb;Trusted_Connection=True;MultipleActiveResultSets=true",
  }
}

然后你创建虚拟上下文类

Then you create dummy context class

public class MyDbContext : DbContext
{
}

没有任何实体.您将使用 DbContext 仅用于保持数据库连接.

without any entity. You will use the DbContext only for holding the database connection.

然后你编写Startup.cs，它根据"appsettings.json"的内容构造Configuration属性.

Then you write Startup.cs which construct Configuration property based on the content of "appsettings.json".

public class Startup
{
    // property for holding configuration
    public IConfigurationRoot Configuration { get; set; }

    public Startup(IHostingEnvironment env)
    {
        // Set up configuration sources.
        var builder = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json")
            .AddJsonFile($"appsettings.{env.EnvironmentName}.json", true)
            .AddEnvironmentVariables();
        // save the configuration in Configuration property
        Configuration = builder.Build();
    }
    public void ConfigureServices(IServiceCollection services)
    {
        // Add framework services.
        services.AddMvc()
            .AddJsonOptions(options => {
                options.SerializerSettings.ContractResolver =
                    new CamelCasePropertyNamesContractResolver();
            });
        services.AddEntityFramework()
            .AddSqlServer()
            .AddDbContext<MyDbContext>(options => {
                options.UseSqlServer(Configuration["Data:MyDbConnectionString"]);
            });
    }
    ...
}

现在你已经注入了 MyDbContext 并且可以在上下文中使用它，你可以使用

Now you have MyDbContext injected and one can use it in context and you can use

using (var cmd = MyDbContext.Database.GetDbConnection().CreateCommand()) {
    cmd.CommandText = "select * from ApplicationLog where application_name = @appname";
    if (cmd.Connection.State != ConnectionState.Open)
        cmd.Connection.Open();

    cmd.Parameters.Add(new SqlParameter("@appname", SqlDbType.NVarChar, 100) {
        Value = appName
    });

    da = new SqlDataAdapter(cmd);

    int res = da.Fill(table);
}

我个人更喜欢使用 List<dynamic> 和 ExecuteReader/ExecuteReaderAsync 而不是 DataTable(参见答案的代码)，但它并不那么重要.

I personally prefer to use List<dynamic> and ExecuteReader/ExecuteReaderAsync instead of DataTable (see the code from the answer), but it's not so important.

这篇关于在 MVC 6 下使用 ADO.NET 的 DataTable 的替代方案是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！