7 people following this project (follow)

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.

Databases.cs


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, http://code.google.com/p/dapper-dot-net/).

    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 ...
    database7.Close();

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" ?>
    <configuration>
        <connectionStrings>
            <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" />
        </connectionStrings>
    </configuration>

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

    <?xml version="1.0" encoding="utf-8"?>
    <databases>
      <database>
        <name>database8</name>
        <connectionString>Server=localhost;Integrated Security=SSPI;Database=Northwind;</connectionString>
        <providerName>System.Data.SqlClient</providerName>
      </database>
    </databases>

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