What is the alternative to DataTable with ADO.NET under MVC 6?

Question

I am creating an MVC 6 project and I would prefer to use Classic ADO.net over Entity Framework 7. However, it says that the namespace cannot be found for both the DataTable and the SqlDataAdapter . I have a System.Data and System.Data.SqlClient statement. The dose does not show an error until I try to build a project.

I think I read somewhere that these two namespaces are not implemented in the new version. If so, is there an alternative way to do this, or am I missing a dependency or using an operator?

code:

 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; } 

my project.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" ] } } 

In the end, I tried to find many different components to try to execute it. If there are links to use, please let me know.

Answer 1

I understand the problem because I like to use existing STORED PROCEDURE and dynamic SQL queries in my C # code. Creating a new object for each query using any existing object with zero properties to save new query results is not a good idea. Also, I like to use the Web API to provide data in JSON format for use in an interface written in JavaScript.

On the other hand, the Entity Framework provides a way to execute a SQL query on a string and store the results in an object without using objects. I sent back pieces of code that could be used. Let me remind you of a short idea.

You need to include the connection string in 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 a dummy context class

 public class MyDbContext : DbContext { } 

without any object. You will use DbContext only to connect to the database.

Then you write Startup.cs that build the Configuration property based on the contents 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"]); }); } ... } 

Now you enter MyDbContext and you 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); } 

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

Answer 2

You must submit your project.json .

The System.Data link and removing dnxcore50 in project.json allows me to compile the above code:

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

Answer 3

Oleg's post was very helpful. It looks like with the latest version of DbContext, you need a constructor that uses DbContextOptions if you use the built-in DI. There are several different flavors, but you cannot just have an empty DbContext class "dummy".

https://docs.efproject.net/en/latest/miscellaneous/configuring-dbcontext.html