.net core db connection

[Born2Achieve]

Hello,

I am working in asp.net core 2.1 API and I am following 3-tier architecture. API – Business layer – Data Access Layer

Currently I am facing problem with connecting to database.i am not sure how to pass the connection string from API to DAL. Below the sample code I tried and not working..

public class CustomerDB : DbContext
{

public CustomerDB (DbContextOptions<CustomerDB> options)
: base(options)
{

}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer(//ConnectionString);
}
}

}

startup.cs

{
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}

public IConfiguration Configuration { get; }
services.AddDbContext<CustomerDB>(options => options.UseSqlServer(Configuration.GetConnectionString("ConnectionString")));
}

sampel db call method:

Datatable dtCustomers = new Datatable();
using (CustomerDB dbConnect = new CustomerDB ())
{
dbConnect.Database.OpenConnection();
DbCommand cmd = dbConnect.Database.GetDbConnection().CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.Getcustomers";
using (var reader = cmd.ExecuteReader())
{
//parse and bind the reader data to datatable
}
return dtCustomers
}
}

Not sure who to get the connection string the pass it to onconfiguring method. i tried to create set the connection string this DAL level (hard coded) and it worked. like below

public class CustomerDB : DbContext
{

public CustomerDB (DbContextOptions<CustomerDB> options)
: base(options)
{

}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
optionsBuilder.UseSqlServer("Server=178.40.132.28;Initial Catalog=customers;User ID=XXXXXX;Password=XXXX;Trusted_Connection=False");
}
}

}

but i don't want to hard code in this DAL. it has to come from API. please help me on how to achieve this. any sample code would be highly appreciated





loving dotnet

Continue reading...