Simple static helper for opening database connections, wraps the provider agnostic features of ADO.NET. Useful to target multiple databases without the hassle; SQL Server, SQL CE, MySQL, Postgress, Firebird, etc. (ADO.NET supports connectors to SQL Server, Oracle, OLE DB and ODBC interfaces.) Open a connection to any server and don't worry about what database was configured in app.config, web.config, settings.setting or xml store. I like to use it with a micro ORM like Dapper to query databases and map the results.


Example 1. Create connection object and retrieve version info.

    using Databases;

    using (var database1 = Database.Connection("database1"))
        string version = database1.ServerVersion;

Example 2. Create a connection and open it immediately for queries. Run a couple of queries, maybe with Dapper (nuget dapper,

    using Databases;
    using Dapper;

    using (var database2 = Database.Open("database2"))
        List<Dog> dogs = database2.Query<Dog>("SELECT * FROM dogs").ToList();
        Console.WriteLine("Total dogs found: " + dogs.Count());

        Dog dog = database2.Query<Dog>("SELECT * FROM dogs WHERE name = @Name", new { Name = "Pluto" }).First();
        Console.WriteLine("Pluto is years old: " + dog.Age);

Example 3. Opening a connection from encrypted connection string is done for you:

    // <add name="database7" connectionString="encrypted:0jx0NNG6POnEZ4/5VKXfeUj0u5WhEa9AEdPx7mYrIiFGmPNPJw8dVZvrcc8gjuy35mz/lt8M2s4e9dQFXHZzgQ##" providerName="System.Data.SqlClient" />

    DbConnection database7 = Database.Open("database7");
    // do something ...

Example 4. Writing a tool to encrypt connection strings for new configurations is easy:

    using Databases;

    Console.WriteLine("Enter the connection string to encrypt and press enter: ");
    string connectionString = Console.ReadLine();
    string encryped = Database.Sources.EncryptConnectionString(connectionString);
    Console.WriteLine("Put in web.config: " + encryped);

Config 1. Connection settings in app.config, web.config or settings.settings:

    <?xml version="1.0" encoding="utf-8" ?>
            <add name="database1" connectionString="Server=localhost;Integrated Security=SSPI;Database=Northwind;" providerName="System.Data.SqlClient" />
            <add name="database2" connectionString="Data Source=|DataDirectory|\Database.sdf" providerName="System.Data.SqlServerCe.3.5" />
            <add name="database3" connectionString="Data Source=|DataDirectory|\Database.sqlite;Version=3;" providerName="System.Data.SQLite" />
            <add name="database4" connectionString="server=localhost;user id=myusername;password=mypassword;persist security info=True;database=mydatabasename;CharSet='utf8';" providerName="MySql.Data.MySqlClient" />
            <add name="database5" connectionString="SERVER=localhost;Database=mydatabasename;User name=myusername;Password=mypassword" providerName="Npgsql2" />
            <add name="database6" connectionString="Server=localhost;User=myusername;Password=mypassword;Charser=NONE;Database=C:\Database.fdb" providerName="FirebirdSql.Data.FirebirdClient" />
            <add name="database7" connectionString="encrypted:0jx0NNG6POnEZ4/5VKXfeUj0u5WhEa9AEdPx7mYrIiFGmPNPJw8dVZvrcc8gjuy35mz/lt8M2s4e9dQFXHZzgQ##" providerName="System.Data.SqlClient" />

Config 2. Connection settings in any xml store, like databases.xml:

    <?xml version="1.0" encoding="utf-8"?>
        <connectionString>Server=localhost;Integrated Security=SSPI;Database=Northwind;</connectionString>

Last edited Dec 3, 2011 at 4:50 AM by patricknl, version 6