Simplified .NET Data Access API

• Download source files - 72.1 KB
• Download demo project - 36.9 KB

Introduction

Even before .NET, when ADO was still an ActiveX API, connection strings have always been a point of confusion for a lot of developers. This mostly stems from the fact that a connection string is structured information with a specific format that developers have always been expected to be able to produce from memory or have to look up in documentation. To me, it only made sense to add the structure they've always needed.

At the same time, for most purposes, I've found the ADO.NET API to be a little more granular than necessary. Which prompted me to look at creating a structure that would simplify most uses of the API. I've seen many people use the API rather recklessly, not taking care to properly dispose of connections and or readers, or fail to properly encapsulate data related functionality in a central location, passing readers from data access classes to business classes, and the like. I found that simplifying the use of ADO.NET with a simpler structure helps promote better encapsulation and more elegant design in data use.

My answer to these issues is a data access structure that uses connection manager classes and connection string structs. This API only makes use of the classes provided by the .NET framework, using OLEDB for access to non-Microsoft databases such as MySQL and Oracle. This decision was made to avoid the need for finding and supporting different versions of third party providers that users may or may not have, so that it is usable by any developer that has the Framework installed. Through the use of this API, I've come to believe that this is the final data abstraction that Microsoft, for reasons unknown, decided not to provide.

IConnectionManager

IConnectionManager is our specification for a connection manager. It lays out all the methods you can expect a connection manager to support. It provides a central location for managing all aspects and associated types related to working with a database connection. It also provides a central location for resource clean-up through its extension of the IDisposable interface.

AbstractConnectionManager : IConnectionManager

The AbstractConnectionManager is the base class for all implementations of connection managers in this API. It implements the generic functionality needed by all concrete implementations.

Internals

AbstractConnectionManager encapsulates an IDbString, IDbConnection, IDbTransaction, IDataReader, and an IDbCommand; which it exposes as protected properties to allow access to them by implementations of this class. These are the objects that the manager classes manage, through a simple interface. All the manager needs from you is a connection string to work with; from this, it can setup a connection and build transactions and commands from the connection.

ResetCommand()

When reusing a manager instance, it's necessary to use the ResetCommand method to set up a fresh command object, especially if a Stored Procedure with parameters has been used previously. It's always a good practice to use this method before starting a new command.

Parameters

The manager classes provide a couple of simple methods for attaching parameters to your queries, for Stored Procedures as well as parameterized queries. The AddParameter(string name, object value) and AddOutputParameter(string name, object value) methods are provided for this purpose. The example code at the bottom of this article provides an example of how to use these methods.

Transactions

By default, the connection managers don't use transactions. But when needed, all you have to do is call the BeginTransaction() method, and Commit() or Rollback() as needed.

BeginTransaction()

This method tells the manager to create a transaction from its internal connection.

Commit() & Rollback()

As expected, these methods finalize your transaction.

Abstracts

AbstractConnectionManager has four abstract methods that all concrete inheritors must implement. Most of these methods are specific to the data provider used, and therefore cannot be implemented by this class.

GetParameter(name, value)

This abstract method is also protected and internal, as it's used internally by the AbstractConnectionManager.AddParameter() method. It returns an instance of IDbDataParameter specific to the implementation's provider, based on the values provided.

GetAdapter(sql, IDbConneciton | CommandType)

This method has two protected overloads, both of which take a SQL string. One also requires an IDbConnection, the other requires a CommandType. This method is used internally to fill a DataSet. It returns an IDataAdapter instance specific to the provider used.

FillSchema(table)

This method is used to retrieve schema information in the form of a DataTable.

Clone()

This method basically creates a new instance of the current implementation with a copy of its internal connection string.

Other Methods

The remaining methods of this abstract class generically perform various functions through the use of the abstract methods described above.

ExecuteNonQuery(sql, [type])

ExecuteNonQuery() has two overloads, one taking a SQL string and a CommandType, the other only requiring a SQL string assuming CommandType.Text. It behaves as IDbConnection.ExecuteNonQuery(), returning an integer representing the number of rows affected.

ExecuteReader(sql, [type])

ExecuteReader() has two overloads, one taking a SQL string and a CommandType, the other only requiring a SQL string and assuming CommandType.Text. It behaves as IDbConnection.ExecuteReader(), returning an IDataReader instance.

Fill(sql, [type])

Like the ExecuteNonQuery() and ExecuteReader() methods, Fill() has a SQL string overload, and a SQL string and CommandType overload. And, as would be expected, it returns a DataSet instance. Unlike the IDbConnection, it does not take a DataSet instance to fill, but creates a new one when called.

Close() & Dispose()

The Close() method closes all internal objects. The IConnectionManager interface extends the IDisposable interface, and therefore requires a Dispose() method, which is similar to the Close() method in function. Because of this, C# users can use the using (...) {...} construct when working with connection managers, and be confident that cleanup will occur for all internal resources.

Implementations

This API provides implementations for SQL Server, Microsoft Access, OLEDB, ODBC, and MySQL. The SQL Server, OLEDB, and ODBC implementations make use of their corresponding Framework providers. The Access and MySQL implementations extend the OLEDB implementation.

Direct Descendants of AbstractConnectionManager

SqlConnectionManager, OdbcConnectionManager, and OleDbConnectionManager all provide the basic functionality specific to their providers. Currently, the SqlConnectionManager is the only one that implements FillSchema(). I rarely work with the others, and have not had time or need to find a solution to extracting schema information from those providers. In future updates, I would like to have this issue resolved.

Initialization

All implementations have an overloaded constructor that accepts an int timeout parameter that is meant to be a connection timeout in seconds. I have witnessed instances where this did not seem to behave as expected, though I've never found reason or cause for this. In most cases, I believe it does work.

AccessConnectionManager : OleDbConnectionManager

The Access implementation provides Access specific initialization, and overrides Clone().

MySqlConnectionManager : OleDbConnectionManager

The MySQL implementation provides MySQL specific initialization, and overrides FillSchema() and Clone(). Currently, FillSchema() is not really implemented. I have meant for some time to revisit this method and make use of MySQL's proprietary schema queries for providing this functionality. These proprietary schema queries have always been interesting to me, and I've always wished that Microsoft would provide something similar. Though, they probably do not due to security concerns.

Connection Strings

This API provides several different implementations of the IDbString interface, which only requires a ToString() method. Each of these are implemented as structs, and hold the necessary fields required by the connection string in question. They can all be implicitly converted to a string, which produces a connection string based on the fields present. The way that these output connection strings is basic; they may not handle all possible needs, though they handle most I've come across. However, I rarely use MySQL, and avoid Oracle like the plague, so these implementations have very little testing and verification of usability. They are all simple implementations, and should be pretty simple to change, if the need arises.

ConnectionStringUtility

The ConnectionStringUtility class provides three static methods:

• public static IConnectionManager GetManager(IDbString dbs) creates an instance of IConnectionManager based on the type of IDbString supplied.
• public static IConnectionManager GetManager(string dbs) creates an instance of IConnectionManager based on the type of IDbString parsed.
• public static IDbString ParseConnectionString(string dbs) attempts to parse a connection string and return a corresponding IDbString.

AccessDbString

The AccessDbString has three properties: FileName, User, and Password. Its constructors have three overloads depending on your needs; most of the time, you will likely use the simplest, which only requires a file name for your MDB file. It represents an OLEDB connection string. It will output a connection string similar to the following:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\somepath\mydb.mdb;User Id=admin;Password=;

MsSqlDbString

The Microsoft SQL Server connection string has six properties: Host, Database, UserId, Password, Trusted, and Timeout. Trusted is used when a password is not supplied. Timeout is the connection timeout in seconds. It will output a connection string similar to the following:

Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False;

MySqlDbString

The MySqlDbString has four properties: Host, Database, UserId, and Password. It will output a connection string similar to the following:

Provider=MySQLProv;Data Source=mydb;User Id=UserName;Password=asdasd;

OdbcDbString

The OdbcDbString has five properties: Driver, Host, Database, UserId, and Password. Driver is the driver name that would normally be in an ODBC connection string. It will output a connection string similar to the following:

Driver={SQL Native Client};Server=Aron1;Database=pubs;UID=sa;PWD=asdasd;

OleDbString

The OleDbString has five properties: Provider, Host, Database, UserId, and Password. Provider is the name of the provider as you would normally supply to an OleDB connection string. It will output a connection string similar to the following:

Provider=sqloledb;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;

OraDbString

The Oracle connection string has three properties: Host, UserId, and Password. It will output a connection string similar to the following:

Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=Username;Password=asdasd;

Config XML Serialization

This API has evolved to include a config file structure that allows multiple connections to be defined and easily exchanged for different environments. All classes related to configuration are in the BoneSoft.Data.Config namespace. The configuration I've come to use for data connection config files has two main parts: a "Connections" list where connection specifics are defined, and a "NamedConnections" section where a connection's information is tied to a name for easy access through code. This is so that you may define as many connection sets as necessary, and change the connection referenced through code by only changing the "ConnectionName" in the corresponding "DbConnection" node. The class structure discussed below shows how these classes are used by applications to retrieve connection information.

First, let's look at the class that the user code will use to access config information...

SqlConnectionList

The SqlConnectionList is a class that handles loading data config files and retrieving connection information from config based on a connection name. It is initialized with a path to a config file, and has two methods for using config information: IDbString GetConnectionString(string name) can be used to get an instance of a connection string based on the value in DbConnectionList/NamedConnections/DbConnection/@Name (XPath format), IConnectionManager GetConnection(string name) can be used to get an instance of a connection based on the value in the same XML attribute of the DbConnection node. SqlConnectionList also provides access to the root config object through the Connections property of type DbConnectionList.

The config classes are modeled after the XML structure shown in the following section on serialization. This class model was generated using Skeleton Crew 2.0, a code generation tool partly designed to replace XSD.exe for generating XML serializable code from an XML structure.

DbConnectionList and DbConnectionListSerializer

SqlConnectionList is the config loading class, but the config itself is represented by a DbConnectionList object. DbConnectionList is the class that serializes to the root node of a config file. As you can see from the class diagram below, this class has two properties, one of type NamedConnections, and one of type Connections. As stated earlier, the class model for data config mirrors the XML structure.

The following example XML config defines two named connections, "ErrorLogging" which references the "ErrorLog" connection definition, and "StatsLogging" which references the connection information named "StatsLog". You could have two or three definitions for logging stats to different databases, for development, staging, and production, then just change the DbConnection's "ConnectionName" attribute to the correct ConnectionInfo name to change environments for what you want when asking for "StatsLogging". The Connections node is just a storage area for connection information. SqlConnectionList looks at the NamedConnections node when asked to get a connection by name. The "ConnectionName" attribute on the DbConnection tells it where to get the actual connection specifics.

<DbConnectionList>
    <!--
        Code will ask for connections by "Name" from this list,
        each entries "ConnectionName" determines which definition
        to use from the "Connections" list below.
    -->
    <NamedConnections>
        <DbConnection Name="ErrorLogging" ConnectionName="ErrorLog" />
        <DbConnection Name="StatsLogging" ConnectionName="StatsLog" />
    </NamedConnections>
    
    <!--
        You can specify as many sets of connection information as you want here.
        The only ones that will be loaded are those named above.
    -->
    <Connections>
        <ConnectionInfo Type="SQL" Name="StatsLog">
            <Host>StatsSQL</Host>
            <Database>Logs</Database>
            <UserId Trusted="false">uid</UserId>
            <Password>pwd</Password>
        </ConnectionInfo>
        <ConnectionInfo Type="SQL" Name="ErrorLog">
            <Host>ErrorSQL</Host>
            <Database>Logs</Database>
            <UserId Trusted="true">uid</UserId>
        </ConnectionInfo>
    </Connections>
</DbConnectionList>

Using the config example above, to get the connection information for logging errors, you would do something similar to the following example:

using System;
using System.Data;
using BoneSoft.Data;
using BoneSoft.Data.Config;

namespace Example {
    class ConfigExample {
        public void Test() {
            string configPath = System.IO.Path.GetDirectoryName(
                System.Reflection.Assembly.GetExecutingAssembly()
                .CodeBase.Replace("file:///", "")) + 
                @"\data.config";

            // initialize a SqlConnectionList with a config path..
            SqlConnectionList conns = new SqlConnectionList(configPath);

            // use the SqlConnectionList to get the connection information
            // with the name "ErrorLogging"
            using (IConnectionManager icm = new SqlConnectionManager(
                (MsSqlDbString)conns.GetConnectionString("ErrorLogging"))) {
                icm.ResetCommand();
                IDataReader idr = icm.ExecuteReader("select * from Errors");
                while (idr.Read()) {
                    // process record...
                }
                idr.Close();
            }
        }
    }
}

With this API, it's a simple matter to get connection information from config, and query your database in just a few lines of code.

Sample Application: Config Editor

The sample project for this article is a small application for creating and editing data config files. Since NamedConnections reference Connections, you must create a NamedConnection first. Just right click in either listview to access its context menu. The File menu allows you to open or save config files.

Example

OK, just one more quick example of how to use the API. The following example uses a Stored Procedure to produce an IDataReader to look through. It demonstrates how to initialize a connection string object, setup a connection manager, use it to set some parameters, and execute and process a data reader.

using System;
using System.Data;
using BoneSoft.Data;

namespace Example {
    class DataExample {
        public int ExecuteProc(string val1, int val2) {
            int answer = 0;

            // Setup connection string
            MsSqlDbString dbs = new MsSqlDbString("host", 
              "database", "user", "password");
            //Console.WriteLine(dbs.ToString());

            // Setup a connection manager taking advantage
            // of the fact that it is IDisposable
            using (IConnectionManager icm = new SqlConnectionManager(dbs)) {
                // If it's being reused, it's important to reset the command
                icm.ResetCommand();

                // Add parameters for our stored procedure
                icm.AddParameter("@param1", val1);
                icm.AddParameter("@param2", val2);

                // With a scalar stored proc, we could do this...
                /*
                icm.AddOutputParameter("@outparam", answer);
                icm.ExecuteNonQuery("StoredProc", CommandType.StoredProcedure);
                */

                // or we could use ExecuteReader...
                IDataReader idr = icm.ExecuteReader("StoredProc", 
                                  CommandType.StoredProcedure);
                if (idr.Read()) {
                    answer = Convert.ToInt32(idr[0]);
                }
                // Always close your readers explicitly
                idr.Close();
            }
            return answer;
        }
    }
}

Conclusion

This all seems like a lot of information, but it's really a pretty simple and easy to use API. Like I stated in the introduction, I believe that this API simplifies basic data access greatly, and also promotes better coding practices and aids in the separation of data access logic. I hope you find it useful, or at least find some good ideas in it for creating your own data access components.