The Gyrum.Data Database Access Layer is a .NET 3.5 module that abstracts away most of the specifics of the various SQL data sources comonly used today. This allows for a single code base to run on top of different data sources with no more than a configuration file change. For example, you can maintain a single business object layer that runs on a SQL Server database for in-house users, and runs on an Access database for remote, disconnected users.

As a quick introduction, consider the following code snippet:

string sql = "SELECT ProductId, Code, Description FROM Products WHERE Code = @Code@ ";
Command cmd = new Command(CommandType.Text, sql, new Parameter("Code", productCode));
using(Connection cn = DataAccess.Providers["CFO"].GetConnection())
{
    using(GyrumDataReader dr = cn.ExecuteReader(cmd))
    {
        if(dr.Read())
        {
            // populate object's values...
            object.Id = dr.GetGuid("ProductId");
            object.Code = dr.GetString("Code");
            object.Description = dr.GetString("Description');
        }
    }
}

This code sets up the SQL statement, creates a Gyrum.Data.Command object, gets a Gyrum.Data.Connection object (based on a provider name), and finally gets a GyrumDataReader to read the data retrieved. Practically all of your SQL database access code can be reduced to something this simple. The benefits to using the Gyrum.Data module include:

• one code base can target multiple database types
• it's simple and easy to use
• it's free!

This is a short overview; see the help file for more specific information on these classes.

DataAccess class - The DataAccess class is the entry point for accessing the different data providers.

Connection class - The Connection class is the database agnostic version of the provider specific connection classes. This is the connection object that you will write your code for. The Connection object is responsible for accepting a generic Command object and creating a database specific command object and executing it.

Command class - The Command class is the intermediary between the SQL statement and the Connection class. A Command object is passed into a Connection object for execution.

Provider class - The Provider class is a wrapper for the connection string(s) in the configuration file. (The connection string can be provided in code as well as the config file.) The Provider class is used to determine which specific connection type to instantiate.

Parameter class - The Parameter class works in conjunction with the Command class to manage the parameters passed to/from a database.

The following database connection classes are used internally. Any database that uses one of the following connection types will be supported.

• SqlServerConnection
• SqlServerCe35Connection
• OracleConnection (needs more testing...) :-)
• OleDbConnection
• OdbcConnection

These connection classes correspond to the following ProviderName types in the config file.

• System.Data.SqlClient
• Microsoft.SqlServerCe.Client.3.5
• System.Data.OracleClient
• System.Data.OleDb
• System.Data.Odbc

In order to support different database providers, we had to come up with a way to structure the SQL so that it could easily be converted to a provider specific syntax. We chose to only mess with the way that parameters are declared. Parameters are declared by name with a "@" character at both the beginning and the end of the parameter name. This is so that the parameter is easily found and replaced in the SQL string if needed. (And so that @Codes is not confused with @Code when doing any parameter name manipulation.)

The SQL used must be generic. Since this module was developed to support the standard CRUD operations, we opted to not support provider specific language constructs. If you need to make use of provider specific language, you may opt to put that SQL in a "switch" or "if" block of code.

using(Connection cn = DataAccess.Providers["CFO"].GetConnection())
{
    string sql;
    Command cmd;
    if(cn.ProviderTypeName.ToUpperInvariant() == "ORACLE")
    {
        sql = "provider specific sql here";
        cmd = new Command(CommandType.Text, sql, new Parameter(...));
    }
    else
    {
        sql = "generic sql here"
        cmd = new Command(CommandType.Text, sql, new Parameter(...));
    }
    using(GyrumDataReader dr = cn.ExecuteReader(cmd))
    {
        if(dr.Read())
        {
            // do something with the data...
        }
    }
}

Parameters can be specified when the Command is created, or appended later. This allows for the SQL to be constructed in parts. For Example:

string sql = "SELECT * FROM Products ";
if (code.Length > 0)
{ 
    sql  = "WHERE Code = "@Code@";
}
else if(id != null)
{
    sql  = "WHERE ProductId = "@Id@";
}
Command cmd = new Command(CommandType.Text, sql);
if (code.Length > 0)
{
    cmd.Parameters.Add(new Parameter("Code", code));
}
else if(id != null)
{
    cmd.Parameters.Add(new Parameter("Id", id));
}

The ExecuteReader line could be moved into the if/else block if addition code was needed to make the generic results conform to whatever the provider specific SQL does.

 To install the Gyrum.Data module:

• unzip the downloaded zip file to the folder of your choice
• add a reference to Gyrum.Data.dll to your project
• in your code file, add a "using Gyrum.Data" statement