WEB API with Dapper

Question

With reference to below given article: https://www.c-sharpcorner.com/article/asp-net-core-web-api-with-dapper-and-vs-2017/ I want connection string to be get at runtime in the above given web api article. I have one common DB in which I have all the client DB details with its connection string in the client table. I have user table in which all the users with its client id. When user logs into my system with its user name and password then I get which DB I have use with its connection string. One solution I have is that in each and every method I have to send the DB name as the parameter. But I want common wrapper class that handle the run time DB. Can we handle multiple database scenario on runtime with this architecture. Please suggest.

Mario Scheeren · Answer

Hi, this should workout fine. You can change the dbName within the Controller anytime you like, and every Methodcall afterwards will go to this db. public class Startup { // This method gets called by the runtime. Use this method to add services to the container. public void ConfigureServices(IServiceCollection services) { // transient lifetime ... services.AddTransient (provider => new BenefitManager()); services.AddTransient (provider => new BenefitRepository()); } } public class BaseRepository : IDisposable { public void SetDataBase( string dbName) { string connectionString = "Data Source=sqltest02\dev01;Initial Catalog=" + dbName + "; Integrated Security=True;Max Pool Size=200" ; con = new SqlConnection(connectionString); } } public class BenefitRepository : BaseRepository, IBenefitRepository { public IList GetElection( int id) { var sql = @ "SELECT * FROM BenefitElection" ; sql += " WHERE Id = @Id" ; sql += " ORDER BY 1 " ; return SqlMapper.Query((SqlConnection)con, sql, new { Id = id }, commandType: Text).ToList(); } } public class BenefitController : ApiController { IBenefitManager _benefitManager; IBenefitRepository _benefitRepository; public BenefitController(IBenefitManager benefitManager, IBenefitRepository benefitRepository) { _benefitManager = benefitManager; _benefitManager.dbName = "TA_JDSU" ; _benefitRepository = benefitRepository; _benefitRepository.SetDataBase( "TA_JDSU" ); } [HttpGet] [Authorize] public IListGet( int id) { return _benefitRepository.GetElection(id); } }

Udai Mathur · Answer

Thanks for your quick responce. I want my business layer and repository layer separate. This is my project structure. Can you please give an example so that I can achieve what I want.

Mario Scheeren · Answer

OK, now I got it ... so I guess IBenefitManager is declared as a transient service, for dependency injection right? If so, why don't you simply extend the BenefitRepository ctor to get those transient reference - that way you got all the properties you need and can use them public BenefitRepository (IBenefitRepository benefitRepository, IBenefitManager benefitManager) .... Or you simply add a Settings object to be passed as transient service ... to be used whereever you need it.

Udai Mathur · Answer

How do I pass DB name from my controller class to businness layer class and then Repository layer class. Currently I am passing this as: A) Controller Class: //[RoutePrefix("Benefit")] public class BenefitController : ApiController { IBenefitManager _benefitManager; public BenefitController(IBenefitManager benefitManager) { _benefitManager = benefitManager; _benefitManager.dbName = "TA_JDSU" ; // When user logs in then as per its client detail DB name will pass. Right now it is hard coded } [HttpGet] //[Route("Elections")] [Authorize] public IListGet( int id) { return _benefitManager.GetElection(id); } } B) Business Class: public class BenefitManager : IBenefitManager { IBenefitRepository _benefitrRepository; public string dbName { get ; set ; } public BenefitManager(IBenefitRepository benefitRepository) { _benefitrRepository = benefitRepository; _benefitrRepository.dbName = benefitRepository.dbName; } public IList GetElection( int id) { _benefitrRepository.dbName = dbName; // Here I have to pass DB name dynamically in each and every Method. I want other solution for the same. return _benefitrRepository.GetElection(id); } } C) Repository Class: public class BenefitRepository : BaseRepository, IBenefitRepository { public string dbName { get ; set ; } public IList GetElection( int id) { GetConnection(dbName); var sql = @ "SELECT * FROM BenefitElection" ; sql += " WHERE Id = @Id" ; sql += " ORDER BY 1 " ; List benefitelections = SqlMapper.Query( (SqlConnection)con, sql, new { Id = id }, commandType: Text).ToList(); return benefitelections; } } public class BaseRepository : IDisposable { protected IDbConnection con; public BaseRepository() { string connectionString = "Data Source=sqltest02\dev01;Initial Catalog=TA_JDSU; Integrated Security=True;Max Pool Size=200" ; con = new SqlConnection(connectionString); } public void GetConnection( string dbName) { string connectionString = "Data Source=sqltest02\dev01;Initial Catalog=" + dbName + "; Integrated Security=True;Max Pool Size=200" ; con = new SqlConnection(connectionString); } public void Dispose() { //throw new NotImplementedException(); con.Dispose(); } } In my scenario I dont want to set DB name from each and every method in business layer class. I want it to set it at controllar class level and access the same at repository class.

Mario Scheeren · Answer

I cannot understand why this is so hard for you ... using System; using System.Data; using System.Data.SqlClient; namespace DataManagement.Repository { public abstract class BaseRepository : IDisposable { protected IDbConnection _Connection; private string _ConnectionString; public BaseRepository( string connectionString = null ) { _ConnectionString = ! string .IsNullOrWhiteSpace(connectionString) ? connectionString : "Data Source=****;Initial Catalog=DataManagement;Integrated Security=True" ; _Connection = new SqlConnection(_ConnectionString); } public void SetConnectionString( string connectionString) { if (! string .IsNullOrWhiteSpace(connectionString)) { if (_Connection.State != ConnectionState.Closed) { try { _Connection.Close(); _Connection.Dispose(); } catch { } } _ConnectionString = connectionString; _Connection = new SqlConnection(_ConnectionString); } } public void Dispose() { try { _Connection.Close(); } catch { } finally { _Connection.Dispose(); } } } } Just as simple as this ...