How do I handle Database Connections with Dapper in NET

Question

I ve been playing with Dapper  but I m not sure of the best way to handle the database connection     Most examples show the connection object being created in the example class  or even in each method   But it feels wrong to me to reference a connection string in every clss  even if it s pulling from the web config     My experience has been with using a DbDataContext or DbContext with Linq to SQL or Entity Framework  so this is new to me     How do I structure my web apps when using Dapper as my Data Access strategy

User · Answer

Try this   public class ConnectionProvider               DbConnection conn          string connectionString          DbProviderFactory factory              Constructor that retrieves the connectionString from the config file         public ConnectionProvider                         this connectionString   ConfigurationManager ConnectionStrings 0  ConnectionString ToString                factory   DbProviderFactories GetFactory ConfigurationManager ConnectionStrings 0  ProviderName ToString                           Constructor that accepts the connectionString and Database ProviderName i e SQL or Oracle         public ConnectionProvider string connectionString  string connectionProviderName                        this connectionString   connectionString              factory   DbProviderFactories GetFactory connectionProviderName                         Only inherited classes can call this          public DbConnection GetOpenConnection                         conn   factory CreateConnection                conn ConnectionString   this connectionString              conn Open                 return conn

User · Answer

I wrap connection with the helper class   public class ConnectionFactory       private readonly string  connectionName       public ConnectionFactory string connectionName                 connectionName   connectionName             public IDbConnection NewConnection     gt  new SqlConnection  connectionName         region Connection Scopes      public TResult Scope lt TResult gt  Func lt IDbConnection  TResult gt  func                using  var connection   NewConnection                          connection Open                return func connection                        public async Task lt TResult gt  ScopeAsync lt TResult gt  Func lt IDbConnection  Task lt TResult gt  gt  funcAsync                using  var connection   NewConnection                          connection Open                return await funcAsync connection                        public void Scope Action lt IDbConnection gt  func                using  var connection   NewConnection                          connection Open                func connection                        public async Task ScopeAsync lt TResult gt  Func lt IDbConnection  Task gt  funcAsync                using  var connection   NewConnection                          connection Open                await funcAsync connection                         endregion Connection Scopes     Examples of usage   public class PostsService       protected IConnectionFactory Connection          Initialization here         public async Task TestPosts Async                    Normal way           var posts   Connection Scope cnn   gt                        var state   PostState Active              return cnn Query lt Post gt   SELECT   FROM  Posts  WHERE  State     state    new   state                             Async way           posts   await Connection ScopeAsync cnn   gt                        var state   PostState Active              return cnn QueryAsync lt Post gt   SELECT   FROM  Posts  WHERE  State     state    new   state                           So I don t have to explicitly open the connection every time  Additionally  you can use it this way for the convenience  sake of the future refactoring   var posts   Connection Scope cnn   gt        var state   PostState Active      return cnn Query lt Post gt    SELECT   FROM   TableName lt Post gt      WHERE   nameof Post State        nameof state      new   state           What is TableName lt T gt    can be found in this answer

User · Answer

I do it like this   internal class Repository   IRepository        private readonly Func lt IDbConnection gt   connectionFactory       public Repository Func lt IDbConnection gt  connectionFactory                  connectionFactory   connectionFactory             public IWidget Get string key            using var conn    connectionFactory                           return conn Query lt Widget gt                   select   from widgets with nolock  where widgetkey  WidgetKey   new   WidgetKey key                         Then  wherever I wire-up my dependencies  ex  Global asax cs or Startup cs   I do something like   var connectionFactory   new Func lt IDbConnection gt       gt        var conn   new SqlConnection          ConfigurationManager ConnectionStrings  connectionString-name        conn Open        return conn

User · Answer

Best practice is a real loaded term  I like a DbDataContext style container like Dapper Rainbow promotes  It allows you to couple the CommandTimeout  transaction and other helpers     For example    using System  using System Collections Generic  using System Linq  using System Text  using System Data SqlClient   using Dapper      to have a play  install Dapper Rainbow from nuget  namespace TestDapper       class Program                  no decorations  base class  attributes  etc          class Product                        public int Id   get  set                public string Name   get  set                public string Description   get  set                public DateTime  LastPurchase   get  set                          container with all the tables          class MyDatabase   Database lt MyDatabase gt                        public Table lt Product gt  Products   get  set                       static void Main string   args                        var cnn   new SqlConnection  Data Source   Initial Catalog tempdb Integrated Security True                cnn Open                 var db   MyDatabase Init cnn  commandTimeout  2                try                               db Execute  waitfor delay  00 00 03                               catch  Exception                                Console WriteLine  yeah     it timed out                                db Execute  if object id  Products   is not null drop table Products                db Execute   create table Products                       Id int identity 1 1  primary key                       Name varchar 20                        Description varchar max                        LastPurchase datetime                  int  productId   db Products Insert new  Name  Hello   Description  Nothing                  var product   db Products Get  int productId                product Description    untracked change                   snapshotter tracks which fields change on the object              var s   Snapshotter Start product               product LastPurchase   DateTime UtcNow              product Name      World                   run  update Products set LastPurchase    utcNow  Name    name where Id    id                note  this does not touch untracked columns              db Products Update product Id  s Diff                     reload             product   db Products Get product Id                 Console WriteLine  id   0  name   1  desc   2  last  3    product Id  product Name  product Description  product LastPurchase                  id  1 name  Hello World desc  Nothing last 12 01 2012 5 49 34 AM              Console WriteLine  deleted   0    db Products Delete product Id                   deleted  True                Console ReadKey

User · Answer

Everyone appears to be opening their connections entirely too early   I had this same question  and after digging through the Source here - https   github com StackExchange dapper-dot-net blob master Dapper SqlMapper cs  You will find that every interaction with the database checks the connection to see if it is closed  and opens it as necessary    Due to this  we simply utilize using statements like above without the conn open     This way the connection is opened as close to the interaction as possible   If you notice  it also immediately closes the connection   This will also be quicker than it closing automatically during disposal     One of the many examples of this from the repo above       private static int ExecuteCommand IDbConnection cnn  ref CommandDefinition command  Action lt IDbCommand  object gt  paramReader                IDbCommand cmd   null          bool wasClosed   cnn State    ConnectionState Closed          try                       cmd   command SetupCommand cnn  paramReader               if  wasClosed  cnn Open                int result   cmd ExecuteNonQuery                command OnCompleted                return result                    finally                       if  wasClosed  cnn Close                cmd  Dispose                      Below is a small example of how we use a Wrapper for Dapper called the DapperWrapper   This allows us to wrap all of the Dapper and Simple Crud methods to manage connections  provide security  logging  etc       public class DapperWrapper   IDapperWrapper         public IEnumerable lt T gt  Query lt T gt  string query  object param   null  IDbTransaction transaction   null  bool buffered   true  int  commandTimeout   null  CommandType  commandType   null              using  var conn   Db NewConnection                      var results   conn Query lt T gt  query  param  transaction  buffered  commandTimeout  commandType                Do whatever you want with the results here              Such as Security  Logging  Etc            return results

User · Answer

Microsoft AspNetCore All  v2 0 3   Dapper  v1 50 2  I am not sure if I am using the best practices correctly or not  but I am doing it this way  in order to handle multiple connection strings   It s easy if you have only 1 connection string  Startup cs  using System Data  using System Data SqlClient   namespace DL SO Project Web UI       public class Startup               public IConfiguration Configuration   get  private set                                public void ConfigureServices IServiceCollection services                           Read the connection string from appsettings              string dbConnectionString   this Configuration GetConnectionString  dbConnection1                    Inject IDbConnection  with implementation from SqlConnection class              services AddTransient lt IDbConnection gt   sp    gt  new SqlConnection dbConnectionString                    Register your regular repositories             services AddScoped lt IDiameterRepository  DiameterRepository gt                                                DiameterRepository cs  using Dapper  using System Data   namespace DL SO Project Persistence Dapper Repositories       public class DiameterRepository   IDiameterRepository               private readonly IDbConnection  dbConnection           public DiameterRepository IDbConnection dbConnection                         dbConnection   dbConnection                     public IEnumerable lt Diameter gt  GetAll                         const string sql     SELECT   FROM TABLE                   No need to use using statement  Dapper will automatically                open  close and dispose the connection for you              return  dbConnection Query lt Diameter gt  sql                                          Problems if you have more than 1 connection string  Since Dapper utilizes IDbConnection  you need to think of a way to differentiate different database connections   I tried to create multiple interfaces   inherited  from IDbConnection  corresponding to different database connections  and inject SqlConnection with different database connection strings on Startup    That failed because SqlConnection inherits from DbConnection  and DbConnection inplements not only IDbConnection but also Component class  So your custom interfaces won t be able to use just the SqlConnection implenentation   I also tried to create my own DbConnection class that takes different connection string  That s too complicated because you have to implement all the methods from DbConnection class  You lost the help from SqlConnection   What I end up doing   During Startup  I loaded all connection string values into a dictionary  I also created an enum for all the database connection names to avoid magic strings   I injected the dictionary as Singleton   Instead of injecting IDbConnection  I created IDbConnectionFactory and injected that as Transient for all repositories  Now all repositories take IDbConnectionFactory instead of IDbConnection  When to pick the right connection  In the constructor of all repositories  To make things clean  I created repository base classes and have the repositories inherit from the base classes  The right connection string selection can happen in the base classes    DatabaseConnectionName cs  namespace DL SO Project Domain Repositories       public enum DatabaseConnectionName               Connection1          Connection2           IDbConnectionFactory cs  using System Data   namespace DL SO Project Domain Repositories       public interface IDbConnectionFactory               IDbConnection CreateDbConnection DatabaseConnectionName connectionName             DapperDbConenctionFactory - my own factory implementation   namespace DL SO Project Persistence Dapper       public class DapperDbConnectionFactory   IDbConnectionFactory               private readonly IDictionary lt DatabaseConnectionName  string gt   connectionDict           public DapperDbConnectionFactory IDictionary lt DatabaseConnectionName  string gt  connectionDict                         connectionDict   connectionDict                     public IDbConnection CreateDbConnection DatabaseConnectionName connectionName                        string connectionString   null              if   connectDict TryGetValue connectionName  out connectionString                                 return new SqlConnection connectionString                              throw new ArgumentNullException                        Startup cs  namespace DL SO Project Web UI       public class Startup                                  public void ConfigureServices IServiceCollection services                        var connectionDict   new Dictionary lt DatabaseConnectionName  string gt                                  DatabaseConnectionName Connection1  this Configuration GetConnectionString  dbConnection1                        DatabaseConnectionName Connection2  this Configuration GetConnectionString  dbConnection2                                    Inject this dict             services AddSingleton lt IDictionary lt DatabaseConnectionName  string gt  gt  connectionDict                   Inject the factory             services AddTransient lt IDbConnectionFactory  DapperDbConnectionFactory gt                     Register your regular repositories             services AddScoped lt IDiameterRepository  DiameterRepository gt                                                DiameterRepository cs  using Dapper  using System Data   namespace DL SO Project Persistence Dapper Repositories          Move the responsibility of picking the right connection string          into an abstract base class so that I don t have to duplicate          the right connection selection code in each repository      public class DiameterRepository   DbConnection1RepositoryBase  IDiameterRepository               public DiameterRepository IDbConnectionFactory dbConnectionFactory                base dbConnectionFactory               public IEnumerable lt Diameter gt  GetAll                         const string sql     SELECT   FROM TABLE                   No need to use using statement  Dapper will automatically                open  close and dispose the connection for you              return base DbConnection Query lt Diameter gt  sql                                          DbConnection1RepositoryBase cs  using System Data  using DL SO Project Domain Repositories   namespace DL SO Project Persistence Dapper       public abstract class DbConnection1RepositoryBase               public IDbConnection DbConnection   get  private set             public DbConnection1RepositoryBase IDbConnectionFactory dbConnectionFactory                           Now it s the time to pick the right connection string                 Enum is used  No magic string              this DbConnection   dbConnectionFactory CreateDbConnection DatabaseConnectionName Connection1                       Then for other repositories that need to talk to the other connections  you can create a different repository base class for them   using System Data  using DL SO Project Domain Repositories   namespace DL SO Project Persistence Dapper       public abstract class DbConnection2RepositoryBase               public IDbConnection DbConnection   get  private set             public DbConnection2RepositoryBase IDbConnectionFactory dbConnectionFactory                        this DbConnection   dbConnectionFactory CreateDbConnection DatabaseConnectionName Connection2                      using Dapper  using System Data   namespace DL SO Project Persistence Dapper Repositories       public class ParameterRepository   DbConnection2RepositoryBase  IParameterRepository               public ParameterRepository  IDbConnectionFactory dbConnectionFactory                base dbConnectionFactory               public IEnumerable lt Parameter gt  GetAll                         const string sql     SELECT   FROM TABLE               return base DbConnection Query lt Parameter gt  sql                                          Hope all these help

User · Answer

It was asked about 4 years ago    but anyway  maybe the answer will be useful to someone here   I do it like this in all the projects  First  I create a base class which contains a few helper methods like this   public class BaseRepository       protected T QueryFirstOrDefault lt T gt  string sql  object parameters   null                using  var connection   CreateConnection                          return connection QueryFirstOrDefault lt T gt  sql  parameters                        protected List lt T gt  Query lt T gt  string sql  object parameters   null                using  var connection   CreateConnection                          return connection Query lt T gt  sql  parameters  ToList                         protected int Execute string sql  object parameters   null                using  var connection   CreateConnection                          return connection Execute sql  parameters                           Other Helpers         private IDbConnection CreateConnection                 var connection   new SqlConnection                  Properly initialize your connection here          return connection            And having such a base class I can easily create real repositories without any boilerplate code   public class AccountsRepository   BaseRepository       public Account GetById int id                return QueryFirstOrDefault lt Account gt   SELECT   FROM Accounts WHERE Id    Id   new   id                public List lt Account gt  GetAll                 return Query lt Account gt   SELECT   FROM Accounts ORDER BY Name                  Other methods        So all the code related to Dapper  SqlConnection-s and other database access stuff is located in one place  BaseRepository   All real repositories are clean and simple 1-line methods   I hope it will help someone

User · Answer

I created extension methods with a property that retrieves the connection string from configuration  This lets the callers not have to know anything about the connection  whether it s open or closed  etc  This method does limit you a bit since you re hiding some of the Dapper functionality  but in our fairly simple app it s worked fine for us  and if we needed more functionality from Dapper we could always add a new extension method that exposes it   internal static string ConnectionString   new Configuration   ConnectionString       internal static IEnumerable lt T gt  Query lt T gt  string sql  object param   null                using  SqlConnection conn   new SqlConnection ConnectionString                         conn Open                return conn Query lt T gt  sql  param                        internal static int Execute string sql  object param   null                using  SqlConnection conn   new SqlConnection ConnectionString                         conn Open                return conn Execute sql  param

User · Answer

Hi  donaldhughes I m new on it too  and I use to do this   1 - Create a class to get my Connection String 2 - Call the  connection string class in a Using  Look   DapperConnection cs  public class DapperConnection        public IDbConnection DapperCon           get                       return new SqlConnection ConfigurationManager ConnectionStrings  Default   ToString                          DapperRepository cs    public class DapperRepository   DapperConnection            public IEnumerable lt TBMobileDetails gt  ListAllMobile                         using  IDbConnection con   DapperCon                                 con Open                    string query    select   from Table                   return con Query lt TableEntity gt  query                                    And it works fine

[c#] How do I handle Database Connections with Dapper in .NET?

Examples related to c#

Examples related to .net

Examples related to dapper