Skip to content

Instantly share code, notes, and snippets.

@senjacob
Created January 21, 2014 12:08
Show Gist options
  • Save senjacob/8538857 to your computer and use it in GitHub Desktop.
Save senjacob/8538857 to your computer and use it in GitHub Desktop.
Simple utility functions for connecting to database
using System.Data;
using System.Configuration;
using System.Data.Common;
using System.Data.Linq;
namespace Dapper
{
/// <summary>
/// Utility functions for connecting to database
/// </summary>
public static class DbProvider
{
/// <summary>
/// AppSetting key specifying ConnectionString name used as Default Connection
/// </summary>
private static string DefaultConnection { get { return ConfigurationManager.AppSettings["DefaultConnection"]; } }
/// <summary>
/// Creates a generic connection to the DefaultConnection
/// </summary>
/// <param name="Open">To open the connection by default</param>
/// <returns>System.Data.IDbConnection to DB specified in DefaultConnection</returns>
public static IDbConnection GetConnection(bool Open = false)
{
return GetConnection(DefaultConnection, Open);
}
/// <summary>
/// Creates a generic connection to the specified ConnectionString Name
/// </summary>
/// <param name="ConnectionStringName">Name of the ConnectionString</param>
/// <param name="Open">To open the connection by default</param>
/// <returns>System.Data.IDbConnection to DB specified in specified ConnectionString name</returns>
public static IDbConnection GetConnection(string ConnectionStringName, bool Open = false)
{
IDbConnection con = GetConnection(
ConfigurationManager.ConnectionStrings[ConnectionStringName].ProviderName,
ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString
);
if(Open)
con.Open();
return con;
}
/// <summary>
/// Creates a specified provider connection for given ConnectionString
/// </summary>
/// <param name="ProviderName">Provider name of database specified in the connection string</param>
/// <param name="ConnectionString">ConnectionString to database</param>
/// <returns>System.Data.IDbConnection to DB specified in specified ConnectionString</returns>
private static IDbConnection GetConnection(string ProviderName, string ConnectionString)
{
var provider = DbProviderFactories.GetFactory(ProviderName);
IDbConnection con = provider.CreateConnection();
if (!string.IsNullOrEmpty(ConnectionString))
{
con.ConnectionString = ConnectionString;
}
return con;
}
/// <summary>
/// Creates a generic command to the specified ConnectionString name
/// </summary>
/// <param name="SetConnection">To set default connection string</param>
/// <param name="Open">To open the connection by default</param>
/// <returns>System.Data.IDbCommand for DefaultConnection</returns>
public static IDbCommand GetCommand(bool SetConnection = true, bool Open = false)
{
return GetCommand(DefaultConnection, SetConnection, Open);
}
/// <summary>
/// Get a generic command to the specified ConnectionString Name
/// </summary>
/// <param name="ConnectionStringName">Name of the ConnectionString</param>
/// <param name="SetConnection">To set default connection string</param>
/// <param name="Open">To open the connection by default</param>
/// <returns>System.Data.IDbCommand for DB specified in given ConnectionString Name</returns>
public static IDbCommand GetCommand(string ConnectionStringName, bool SetConnection = true, bool Open = false)
{
var connectionStr = ConfigurationManager.ConnectionStrings;
IDbCommand cmd = DbProviderFactories.GetFactory(connectionStr[ConnectionStringName].ProviderName).CreateCommand();
if (SetConnection)
{
cmd.Connection = GetConnection(
ConfigurationManager.ConnectionStrings[ConnectionStringName].ProviderName,
ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString
);
if(Open)
cmd.Connection.Open();
}
return cmd;
}
/// <summary>
/// Creates a Linq DataContext to default database
/// </summary>
/// <returns>System.Data.Linq.DataContext to default database</returns>
public static DataContext GetDataContext()
{
return GetDataContext(DefaultConnection);
}
/// <summary>
/// Creates a Linq DataContext to database specified in given ConnectionString name
/// </summary>
/// <param name="ConnectionStringName">Name of the ConnectionString</param>
/// <returns>System.Data.Linq.DataContext to the DB in given ConnectionString Name</returns>
public static DataContext GetDataContext(string ConnectionStringName)
{
return new DataContext(GetConnection(ConnectionStringName));
}
}
}
@senjacob
Copy link
Author

Define your ConnectionStrings in web.config and specify default connection to be used in 'DefaultConnection' AppSetting.

<configuration>
 <connectionStrings>
    <clear/>
    <add name="MsSql" connectionString="Server=.;Database=testdb;User Id=sa;Password=****;" 
         providerName="System.Data.SqlClient" />
  </connectionStrings>
    <add name="MySql" connectionString="Database=MyDb;Server=localhost;Username=root;Password=****;" 
         providerName="MySql.Data.MySqlClient" />
  </connectionStrings>
  <appSettings>
    <add key="DefaultConnection" value="MsSql" />

Then connect to default database as

using(var con = DbProvider.GetConnection())
{
  return con.Query<MyModel>(strQuery, new { d="dynamicParams" });
}

or to another database as

using(var con = DbProvider.GetConnection("MySql"))
{
  return con.Query<MyModel>(strQuery, new { d="dynamicParams" });
}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment