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

Question

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.

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?

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" ]
    }
}

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.

Answer 1

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.

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

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

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"]);
            });
    }
    ...
}

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

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.

Answer 2

You should post your project.json.

Referencing System.Data and removing dnxcore50 in project.json lets me compile the above code:

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