Implement ADO connection in ASP.Net Core

Question

I need to get a stored procedure from my database in my .Net Core project. Usually I run this stored Procedure by doing this:

Preferred Code

readonly private SqlConnection _dbConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString);

public int Insert(Employee employee)
{
    var result = 0;
    using (var cmd = new SqlCommand("Sp_Insert", _dbConnection) { CommandType = CommandType.StoredProcedure })
    {
        try
        {
            cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
            cmd.Parameters.AddWithValue("@LastName", employee.LastName);
            cmd.Parameters.AddWithValue("@EmployeeCode", employee.EmployeeCode);
            cmd.Parameters.AddWithValue("@Position", employee.Position);
            cmd.Parameters.AddWithValue("@Office", employee.Office);

            _dbConnection.Open();
            result = cmd.ExecuteNonQuery();
        }
        catch
        {
            // ignore
        }
        finally
        {
            _dbConnection.Close();
        }
    }
    return result;
}

and my connection string is in the Web.config But with the .net Core my connection string is in the appsettings.json as such:

.Net Entity Framework Code

{
  "ConnectionStrings": {
    "Default": "server=DESKTOP-98TG6JE\\SERVER_2014;database=vega;user=sa;password=ComplexPassword!123;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  }
}

then I create a DbContext like so:

public class VegaDbContext : DbContext
{
     public VegaDbContext(DbContextOptions<VegaDbContext> options) : base(options)
     {}

     public DbSet<Make> Makes { get; set; }
}

Then call this in my Startup.cs like so:

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

public IConfiguration Configuration { get; }

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<VegaDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("Default")));

    services.AddMvc();
}

This is good if I am using entity framework to CRUD, however, there are multiple times where I need to create complex queries and so I would need the SQL Stored Procedure for that. Can you please show me how to integrate my "Preferred code" with the ".net Entity Framework Code"? Thank you very much.

P.S. if possible, can you please use the codes I have above as an example.

Have you looked at this? https://stackoverflow.com/a/35444592/2638872 — mrtig, Apr 07 '18 at 04:20
So I need to import something first but on the ` new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString);`, where will I get the "DbConnection" since there is no in the web.config anymore? — stack questions, Apr 07 '18 at 04:24
Have you looked at this? https://learn.microsoft.com/en-us/aspnet/core/fundamentals/configuration/?tabs=basicconfiguration — mrtig, Apr 07 '18 at 04:26
I see that now. The configuration is in the Configure(..., ...) {var config = Configuration["DbConnection"]} but my InsertEmployee is in another class. How do I use it in `new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString);` — stack questions, Apr 07 '18 at 04:30
It should be available via DI: https://stackoverflow.com/a/39232929/2638872 — mrtig, Apr 07 '18 at 04:36
I don't get it. Should I create a var cString = SqlConnection(ConfigurationManager.ConnectionStrings[Configuration.GetConnectionString("Default")].ConnectionString); in my ConfigureServices and in my Controller create a constructor with HomeController(IConfiguration configuration) then I can get my sqlConnection? Is that it? — stack questions, Apr 07 '18 at 04:51

score 5 · Accepted Answer · answered Apr 07 '18 at 05:01

Make the following changes:

In your ConfigureServices method add the following line: services.AddSingleton<IConfiguration>(Configuration);
In your class other with InsertEmployee, add IConfiguration constructor parameter, call it configuration and set it to a private field.

Here is what InsertEmployee should look like:

public int InsertEmployee(Employee employee)
{
    var sql = new SqlConnection(
        this.configuration.GetConnectionString("Default"));

   //... rest of your ADO code.
}

Thank you thank you thank you thank you thaaaannnkkk yyooouuu! — stack questions, Apr 07 '18 at 05:03

Implement ADO connection in ASP.Net Core

1 Answers1