find and solve || findandsolve.com
Please wait.....

Welcome back.






Before Read Terms of use

How to use SqlClient in ASP.NET Core (.NET 5)


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


Mahira  khanna

Mahira khanna

I have the skills you need for you company blog, website, or other content materials

Comments



Report Response