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