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;
}
}
}Option 2.
To use the SqlClient class in ASP.NET Core, you will need to install the System.Data.SqlClient NuGet package in your project. You can install the package using the following command in the Package Manager Console:
Install-Package System.Data.SqlClient
Once the package is installed, you can use the SqlConnection class to establish a connection to a SQL Server database. Here is an example of how you can use the SqlConnection class in ASP.NET Core:
using (var connection = new SqlConnection("ConnectionString"))
{
connection.Open();
// Execute a SQL command
using (var command = new SqlCommand("SELECT * FROM Customers", connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
// Process the data
}
}
}
}
In this example, the SqlConnection object is created using a connection string, which specifies the server, database, and authentication details for the connection. The Open method is then called to establish the connection.
Once the connection is established, you can use the SqlCommand class to execute a SQL command and the SqlDataReader class to read the results. You can then process the data using the reader object.
I hope this helps! Let me know if you have any other questions about using the SqlClient class in ASP.NET Core.
Comments