CODEWITHSUNDEEP
c#
stack questions
stack questions

Reputation: 982

Implement ADO connection in ASP.Net Core

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.

Upvotes: 5

Views: 4042

Answers (1)

mrtig
mrtig

Reputation: 2267

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

Upvotes: 6

Related Questions