ASP.NET Core application with ADO.NET example

Question

I want to create ASP.NET Core 2.0 MVC application with ADO.NET, I searched googled / Microsoft where I found examples with Entity framework, but not with ADO.NET.

I need,

Connection string read from config file from json or config file (I know there is no Web.config comes by default but required to access config entry from configuration file).
Creating DAL for 3 tier architecture. (I know that asp.net core does not support ADO.NET, but) my requirement is to use Sqlconnection, Sqlcommand same as a normal MVC application in ADO.NET

Can anybody have an example for this or link where complete understand? I think this will help to every body who have knowledge of ASP.NET MVC, but not experience in ASP.NET Core.

Did you read this? learn.microsoft.com/en-us/ef/core/miscellaneous/… — mm8
– mm8, Commented Jul 16, 2018 at 11:50
@mm8, yes and the c sharp code in entity base, the same I want in ado side for asp.net core. Thanks for the help — Ajay2707
– Ajay2707, Commented Jul 16, 2018 at 11:52
I provided an example. Using .NET Standard, the DAL itself could easily be shared between an ASP.NET and an ASP.NET Core app. — mm8
– mm8, Commented Jul 16, 2018 at 12:40

Ajay2707 · Accepted Answer · 2018-07-20 06:21:53Z

I figured out the following solution. Maybe it is useful for you.

In .NET Core, SqlClient is not there by default. You need to add it from NuGet Package Manager. So follow the following steps to achieve this.

Install System.Data.Common using NuGet Package Manager in your DAL project
Install System.Data.SqlClient using NuGet Package Manager in your DAL project

Add config.json in your project. like...

{
        "name": "asp.net",
        "private": true,
        "dependencies": {
},
"connectionString": "data source=*************;initial catalog=****;user id=***;password=****;MultipleActiveResultSets=True;Connection Timeout=300;"
}

Create one class in your DAL project which is getting connection string. In SetBasePath() you need to set the directory path of your DAL project.

using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace MyProject.DAL
{
public class SQLDataAccess
{

    protected string ConnectionString { get; set; }

    public SQLDataAccess()
    {
        var configuration = new ConfigurationBuilder()
            .SetBasePath(Directory.GetParent(Directory.GetCurrentDirectory()) + "/MyProject.DAL").AddJsonFile("config.json", false)
            .Build();

        this.ConnectionString = configuration.GetSection("connectionString").Value;
    }

    private SqlConnection GetConnection()
    {
        SqlConnection connection = new SqlConnection(this.ConnectionString);
        if (connection.State != ConnectionState.Open)
            connection.Open();
        return connection;
    }

    public DbDataReader GetDataReader(string procedureName, List<SqlParameter> parameters, CommandType commandType = CommandType.StoredProcedure)
    {
        DbDataReader dr;

        try
        {
            DbConnection connection = this.GetConnection();
            {
                DbCommand cmd = this.GetCommand(connection, procedureName, commandType);
                if (parameters != null && parameters.Count > 0)
                {
                    cmd.Parameters.AddRange(parameters.ToArray());
                }

                dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }
        catch (Exception ex)
        {
            throw;
        }

        return dr;
    }
  }
 }

mm8 · Accepted Answer · 2018-07-16 12:17:54Z

If you define your DAL in a .NET Standard 2.0 class library, you could use both SqlConnection and SqlCommand, e.g.:

public class MyDal
{
    private readonly string _connectionString;

    public MyDal(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IEnumerable<string> GetSomeData()
    {
        using (SqlConnection conn = new SqlConnection())
        {
            using (SqlCommand command = new SqlCommand(_connectionString, conn))
            {
        ...
            }
        }
    }
}

You then add a reference to your library from your ASP.NET Core app.

Using the built-in support for dependency injection, you can inject the controllers with an IConfiguration:

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public IConfiguration Configuration { get; }

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc();
        services.AddSingleton(Configuration);
    }

    public void Configure(IApplicationBuilder app, IHostingEnvironment env)
    {
        if (env.IsDevelopment())
            app.UseDeveloperExceptionPage();

        app.UseMvc();
    }
}

...that you use like this to extract the connection string:

public class ValuesController : Controller
{
    private readonly IConfiguration _config;

    public ValuesController(IConfiguration config)
    {
        _config = config;
    }

    [HttpGet]
    public IEnumerable<string> Get()
    {
        MyDal dal = new MyDal(_config.GetConnectionString("conn"));
        return dal.GetSomeData();
    }
}

The connection string itself is defined in appsettings.json:

{
"ConnectionStrings": {
  "conn": "Server=localhost;Database=database;Trusted_Connection=True;"
},
"Logging": {
  "IncludeScopes": false,
  "Debug": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "Console": {
    "LogLevel": {
      "Default": "Warning"
    }
  }
}

Ajay2707 · Accepted Answer · 2018-08-08 06:58:01Z

I got an answer that how to use connection string from config json. Suppose I have appsetting.json as below

{   
    "connectionString": "data source=servername;initial catalog=yourdb;user id=sa;password=pwd;MultipleActiveResultSets=True;"
}

Now I want to access this connection string, so the code will be

using Microsoft.Extensions.Configuration;
using System.IO;

protected string ConnectionString { get; set; }

public GetDBConnString()
    {
        var configuration = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory() + "/").AddJsonFile("config.json", false)
            .Build();

        this.ConnectionString = configuration.GetSection("connectionString").Value;
    }

Collectives™ on Stack Overflow

ASP.NET Core application with ADO.NET example

3 Answers 3

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Related