WEB API with Dapper

Udai Mathur

7y2.3k.NET

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.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Mario Scheeren7y ago
Accepted 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 Mathur7y ago
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 Scheeren7y ago
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 Mathur7y ago
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 Scheeren7y ago
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 ...