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? stackoverflow.com/a/35444592/2638872 — mrtig
– mrtig, Commented Apr 7, 2018 at 4: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
– stack questions, Commented Apr 7, 2018 at 4:24
Have you looked at this? learn.microsoft.com/en-us/aspnet/core/fundamentals/… — mrtig
– mrtig, Commented Apr 7, 2018 at 4: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
– stack questions, Commented Apr 7, 2018 at 4:30
It should be available via DI: stackoverflow.com/a/39232929/2638872 — mrtig
– mrtig, Commented Apr 7, 2018 at 4:36

mrtig · Accepted Answer · 2018-04-07 05:01:45Z

6

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

answered Apr 7, 2018 at 5:01

mrtig

2,26717 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

stack questions Over a year ago

Thank you thank you thank you thank you thaaaannnkkk yyooouuu!

Collectives™ on Stack Overflow

Implement ADO connection in ASP.Net Core

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related