I am trying to use SQLClient library in the ASP.net Core but can't seem to get it working.I have create store procedure in database.Now i want to pass my connectionString in my SqlConnection function from appsetting.json.In my case when i have use connectstring in entity framework this is working fine but when I am trying to pass my connectionString in SqlConnection function this is not working.So My question is who to call appsetting.json connectionstring in my SqlConnection?
This is my appsetting.json file
{
"ConnectionStrings": {
"windowsAuthentication": true,
"DefaultConnection": "Data Source=SqlServerName;Database=MyDatabaseName; uid=sqlUserId;pwd=SqlPassword;"
},
"Logging": {
"LogLevel": {
"Default": "Warning"
}
},
"AllowedHosts": "*"
}
This is my DbContext.cs class
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
namespace DemoConnection
{
public class DataContext : IdentityDbContext<IdentityUser, IdentityRole, string, IdentityUserClaim<string>, IdentityUserRole<string>, IdentityUserLogin<string>, IdentityRoleClaim<string>, IdentityUserToken<string>> {
public DataContext(DbContextOptions<DataContext> options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<IdentityUser>();
}
}
}
And this is my function where I have call store procedure by SqlConnection.
using (SqlConnection con = new SqlConnection(/*here I want to pass my connectiontring*/)) { con.Open(); using (SqlCommand cmd = new SqlCommand()) { cmd.CommandText = "[dbo].[Paging.GetList]"; cmd.Connection = con; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("SearchVal",param.SearchVal); cmd.Parameters.AddWithValue("Page", param.Start); cmd.Parameters.AddWithValue("OrderBy", param.SortOrder); cmd.Parameters.AddWithValue("PageSize", param.Length); cmd.CommandTimeout = 120; using (var reader = cmd.ExecuteReader()) { if (reader.HasRows)
while (reader.Read()) {
DataTableViewModel model = new DataTableViewModel();
model.Email = reader["Email"].ToString();
model.FirstName = reader["FirstName"].ToString();
model.LastName = reader["LastName"].ToString();
model.Country = reader["Country"].ToString();
}
}
}
return model;
}
Answer
Option 1.
public class ConnectionExample {
private string _connectionString; private readonly DataContext _dataContext; public ConnectionExample(IConfiguration iconfiguration) { _connectionString =iconfiguration.GetConnectionString("DefaultConnection");
}
public DataTableViewModel GetStoreProcedureData() {
using (SqlConnection con = new SqlConnection(_connectionString))
{
con.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "[dbo].[Paging.GetList]";
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("SearchVal",param.SearchVal);
cmd.Parameters.AddWithValue("Page", param.Start);
cmd.Parameters.AddWithValue("OrderBy", param.SortOrder);
cmd.Parameters.AddWithValue("PageSize", param.Length);
cmd.CommandTimeout = 120;
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
while (reader.Read())
{
DataTableViewModel model = new DataTableViewModel();
model.Email = reader["Email"].ToString();
model.FirstName = reader["FirstName"].ToString();
model.LastName = reader["LastName"].ToString();
model.Country = reader["Country"].ToString();
}
}
}
return model;
} }
}
Comments