Data Access Component

• Download source code - 38.7 KB

Introduction

Data access is always an unavoidable part of any software development process. There are different types of DBMS systems available in the market. And software architects consider data access as a separate layer while designing multi-tired applications. Here I am presenting a unique data access component which encapsulates the data access functionalities of ADO.NET, and some other useful utilities to use with any type of application and DB backend. One of the major advantages of this component is that, it provides a transparent interface for accessing SQL, OLEDB, ODBC, and Oracle backend. It visualizes a new concept called Parameter Engine, using which parameters for Stored Procedures etc., can be constructed easily and passed to any type of backend.

This library is simple and light weight. Anyone who has experience in .NET data access can easily understand this library. Using this component, you can very easily switch your DBMS systems without any change in your code. You will be writing your data access logic targeting the definitions in this library. During deployment, simply change the connection string in your settings, initialize the appropriate class based on another setting, and the rest will be managed by the data access component.

Structure

MS.Components.Data

• IBackendSource: Defines some base operations that can be performed on any type of backend. Connection management etc., goes here.
• IBackend: Inherited from IBackendSource and defines common data access operations supported by ADO.NET, like ExecuteNonQuery, BeginTransaction etc.
• BackendType: Enumeration specifying the type of backend like SQL Oracle etc.
• ParameterEngine: A unique class that helps the easy manipulation of DB parameters for DB commands without knowing the type of the backend.

MS.Components.Data.Base

• DataAccess: The abstract class inherited from IBackend. This class forms the base class for any specific back end type implementation, and also provides implementation of some common operations like connection management, transaction control, command generation etc.

MS.Components.Data.SQLServer

• SQLServerDB: Derived from DataAccess, and provides a concrete implementation of data access operations for a SQL Server data source.

MS.Components.Data.Oracle

• OracleDB: Derived from DataAccess, and provides a concrete implementation of data access operations for an Oracle data source.

MS.Components.Data.ODBC

• OdbcAccess: Derived from DataAccess, and provides a concrete implementation of data access operations for an ODBC data source.

MS.Components.Data.OLEDB

• OleDbAccess: Derived from DataAccess, and provides a concrete implementation of data access operations for an OLEDB data source.

MS.Components.Data.Utilities

• SQLInstancesFinder: A static utility class that can be used to find SQL Server instances available over a network, and retrieves the information databases in it.
• ODBCDataSourcesFinder: A static utility class that can be used to retrieve information about ODBC data sources set in the Control Panel.

MS.Components.Data.Controls

• SQLConnectionSetupControl: A Windows user control using which you can browse SQL Server instances and their DBs in the network and generate connection strings for them. Similar to Add Connection in the Server Explorer.

Inside the Data Access Component

A. Data Access Interfaces and Classes

The DAL is built upon two interfaces and an abstract class: IBackendSource, IBackend, and DataAccess. The main purpose of these interfaces and the abstract class is to define a standard for further extension and addition of new DBMS access libraries to the component. Let's dig inside.

The purpose of these interfaces is to define a standard language for data access. Similar to ADO.NET's IDbConnection, IDbCommand etc. These interfaces will allow the assembly to be exposed towards COM and used with applications like VB6 etc. Also, they will help in implementing new data layer components for new types of data sources. There are two interfaces which separate the DBMS operations as connectivity and data access operations.

IBackendSource

This interface mainly defines connection management operations like specifying a connection string, specifying the backend type, opening/closing a connection etc. See the comments on each definition.

public interface IBackendSource : IDisposable
{
    // Get and set the connection string
    string ConnectionString

    // Represents the current ADO.NET DBConnection
    DbConnection Connection

    // An enumeration specifying the type of the backend DBMS
    BackendType BackendType

    // Specifies whether connection is established
    bool IsConnected

    // Retrives the last exception occurred inside the data layer
    Exception LastException

    // Connects to the backed using the connection string.
    // The reconnect indicates to close any open connection and reconnect.
    // If connection is already opened then specifying false
    // may generate an exception
    bool Connect(bool reconnect);

    // Connects use the specified connection string
    bool Connect(string conString, bool reconnect);

    // Disconnect
    bool Disconnect();
}

IBackend

This inherits from IBackendSource and defines data manipulation operations like accessing data, executing queries etc.

public interface IBackend : IBackendSource
{
    // Retrieves data by specifying and SQL statement
    DataTable GetData(string sql);

    // Retrieves data using a db command
    DataTable GetData(DbCommand command);

    // Retrieves data using the sql statement
    // and appends with the specified data table
    void GetData(string sql, ref DataTable dt);

    // Retrieves data using the dbcommand
    // and appends with the specified data table
    void GetData(DbCommand command, ref DataTable dt);

    // Executes the specified query with specified command type
    int ExecuteNonQuery(string sqlCommand, CommandType cmdType);

    // Executes the specified dbcommand on the data source
    int ExecuteNonQuery(DbCommand command);

    // Executes the specified query over the data source with
    // specified command type and parameter engine
    int ExecuteNonQuery(string sql, CommandType cmdType, 
                        ParameterEngine param);

    // Retrieves a single value using the specified db command
    object GetSingleValue(DbCommand command);

    // Retrieves a single value using
    // the specified query and command type
    object GetSingleValue(string sql, CommandType cmdType);

    // Retrieves a single value using the specified query/procedure 
    // with the command type and parameter engine
    object GetSingleValue(string sql, CommandType cmdType, 
                          ParameterEngine param);

    // Generates a corresponding db command
    // of the backend type for executing stored procedures etc.
    // using specified command type and parameter engine
    DbCommand GenerateCommand(string sql, CommandType cmdType, 
                              ParameterEngine param);

    // Creates a corresponding db parameter for the backend type.
    DbParameter CreateParameter();

    // Starts a transaction
    DbTransaction BeginTransaction();

    // Starts a transaction with specified isolation level
    DbTransaction BeginTransaction(IsolationLevel isolationLevel);

    // Commits the transaction
    void CommitTransaction();

    // Rolls back the transaction
    void RollbackTransaction();
}

DataAccess

This is an abstract class that inherits from the IBackend interface and provides concrete implementation of a certain functionality specified in IBackend. This class acts as a base class for deriving and implementing concrete data access classes for SQL Server, Oracle, ODBC, and OLEDB type of components defined in the ADO.NET library. This class implements and handles functionality like connection management, DB command generation, transaction management etc., so that the deriving classes need not have to worry about implementing these. They have to only override the abstract functionalities (derived from IBackend) from this class and provide the source specific implementation.

This class also defines a constant ERROR_RESULT. Currently, in the derived classes, the exceptions from ExecuteNonQuery operations are caught inside derived classes and update the LastException property. At that time, this ERROR_RESULT constant is returned as the return value of ExecuteNonQuery operations. In this case, you have to check the LastException property of the IBackendSource to identify the generated exception. Otherwise, the driver specific return value is returned back.

public abstract class DataAccess : IBackend
{
    public const int ERROR_RESULT = int.MaxValue;

    // Inherits the interfaces from IBackend and IBackendSource
    // Some of the interface methods are given concrete implementation
    // while some others are defined as abstract
    // for derived class implementation

    // *** Protected ***
    protected abstract void createConnection();
    protected abstract DbCommand getCommand(ref string sqlCommand, 
                                 CommandType cmdType);

    // *** Public ***
    // IBackend Members
    public abstract DataTable GetData(string sql);
    public abstract DataTable GetData(DbCommand command);
    public abstract void GetData(string sql, ref DataTable dt);
    public abstract void GetData(DbCommand command, ref DataTable dt);
    public abstract int ExecuteNonQuery(string sqlCommand, CommandType cmdType);
    public abstract int ExecuteNonQuery(DbCommand command);
    public abstract int ExecuteNonQuery(string sql, 
                        CommandType cmdType, ParameterEngine param);
    public abstract object GetSingleValue(DbCommand command);
    public abstract object GetSingleValue(string sql, CommandType cmdType);
    public abstract object GetSingleValue(string sql, 
                           CommandType cmdType, ParameterEngine param);
    public abstract DbCommand GenerateCommand(string sql, 
                              CommandType cmdType, ParameterEngine param);
    public abstract DbParameter CreateParameter();
}

public class SQLServerDB : DataAccess
{
    // Implements data access functionalities specific to SQL server
}

public class OracleDB : DataAccess
{
    // Implements data access functionalities specific to Oracle
}


public class OdbcAccess : DataAccess
{
    // Implements data access functionalities specific to ODBC
}


public class OleDbAccess : DataAccess
{
    // Implements data access functionalities specific to OLEDB
}

ParameterEngine

ParameterEngine is a new concept introduced in this DAL component which provides transparent and easy manipulation of database parameters. Irrespective of the type of backend, users can manage the input and output parameters that can be passed to Stored Procedures (this parameter engine supports only SQL, Oracle, ODBC, and OLEDB type of backends). It can be used for the retrieval of output parameters after query execution. Once the instance of the parameter engine is created and filled up with the parameters, it can be used to generate database commands and then execute the queries.

public sealed class ParameterEngine
{
    // Adding parameters
    public bool Add(string name, object value)
    public bool Add(string name, DbType dataTypeEnum, int size, object value)
    public bool Add(string name, DbType dataTypeEnum, int size, 
                    string sourceColumn, object value)
    public bool Add(string name, DbType dataTypeEnum, int size, 
                    ParameterDirection direction, string sourceColumn, 
                    DataRowVersion sourceVersion, object value)


    // Adding output parameters
    public bool AddOutputParameter(string name)
    public bool AddOutputParameter(string name, int size)
    public bool AddOutputParameter(string name, DbType dataType, int size)

    // Retrieving the value from output parameter after the query execution
    public object RetrieveOutputParameterValue(string parameterName)
}

B. Utilities

Some utilities are incorporated in the component. Some are still under research. Presently, two static utility classes named SQLInstancesFinder and ODBCDataSourcesFinder are functional.

SQLInstancesFinder

This class provides static functionalities to query SQL Server instances available over a network. Each SQL Server instance is represented using the SQLInstancesFinder.SQLServerInstance class, which contains the name and version information of that instance. Additional functionalities are there which retrieve the name of databases in a particular server using Windows/SQL authentication.

public static class SQLInstancesFinder
{
    // Retrives the list of available server
    public static List<SQLServerInstance> GetNetworkSQLServerInstances()

    // Retrives the list of databases
    // in a particular server using windows authentication
    public static List<string> GetDatabasesFromServer(string server)

    // Retrives the list of databases
    // in a particular server using SQL authentication
    public static List<string> GetDatabasesFromServer(string server, 
                                     string useName, string password)
}

ODBCDataSourcesFinder

This class provides the static functionalities to query the ODBC data sources (System/User/File) configured in a computer. Each source is represented using the ODBCDataSourcesFinder.ODBCDataSource class, which contains the name and driver information of that data source.

public static class ODBCDataSourcesFinder
{
    // Gets the list of all DSNs
    public static List<ODBCDataSource> GetAllDSNs()

    // Gets the list of system DSNs
    public static List<ODBCDataSource> GetSystemDSNs()

    // Gets the list of user DSNs
    public static List<ODBCDataSource> GetUserDSNs()

    // Gets the list of file DSNs
    public static List<ODBCDataSource> GetFileDSNs()
}

C. User Controls

SQLConnectionSetupControl

This is a Windows user control similar to the Add Connection dialog in the Server Explorer. This can be used to connect interactively to a server in the network, and connect to a database, and finally generate the connection string.

public partial class SQLConnectionSetupControl : UserControl
{
    // ********* Data *********

    // Specifies the authentication mode. Windows/SQL
    [DefaultValue(typeof(SQLConnectionSetupControl.LogonMode), 
                         "Windows")]
    public LogonMode AuthenticationMode
    // Gets the connection string
    public string ConnectionString
    // Gets/sets the name of the server
    public string ServerName
    // Gets/sets the name of the database
    public string DatabaseName
    // Gets/sets the user name for connection
    public string UserName
    // Gets/sets the password for connection
    public string Password
    // Gets/sets the timeout for connection
    [DefaultValue(-1)]
    public int TimeOut

    // ********* Behaviour / Appearance *********

    // Setting true will resize the parent form
    // to resize to the size of the control
    [DefaultValue(false)]
    public bool AutoSizeParentForm
    // Gets/sets whether the parent form should
    // be closed when OK or cancel is clicked
    [DefaultValue(false)]
    public bool CloseParentFormOnOKCancel
    // Gets/sets whether the caption of the parent form
    // to be altered when opening a connection
    [DefaultValue(false)]
    public bool UpdateParentFormStatus
    // Gets/sets whether the "Test" button to be visible
    // for testing the connection
    [DefaultValue(true)]
    public bool ShowTestButton
    [DefaultValue("")]
    // Gets/sets the message to be displayed when a connection
    // is opening. Has effect only when
    // UpdateParentFormStatus is set true
    public string ParentFormStatusMessage

    // ********* Buttons *********

    // Gets/sets the caption of OK button
    [DefaultValue("&OK")]
    public string OKButtonText
    // Gets/sets the caption of cancel button
    [DefaultValue("&Cancel")]
    public string CancelButtonText

    // ********* Methods *********

    // Check whether the connection is success with the input data
    public bool CheckConnection()
}

Using the Component

The below code segments illustrates the usage of the component. You can find the same code in FormUsage.cs in the TestApp project. The test project contains another form that demonstrates the usage of SQLConnectionSetupControl.

DataAccess da = null;
string conStr = 
  "Data Source=localhost;Initial Catalog=MyDB;User ID=sa;Password=pwd";

private static DataAccess _CreateDataSource(BackendType type)
{
    DataAccess ret = null;

    switch (type)
    {
        case BackendType.SQL: ret = new SQLServerDB(); break;
        case BackendType.Oracle: ret = new OracleDB(); break;
        case BackendType.Odbc: ret = new OdbcAccess(); break;
        case BackendType.OleDb: ret = new OleDbAccess(); break;
    }

    return ret;
}

private void Init()
{
    this.da = _CreateDataSource(BackendType.SQL);
}

// *************** Connecting & Disconnecting ***************

private void Connect()
{
    this.da.ConnectionString = conStr;
    this.da.Connect(false);

    // OR

    this.da.Connect(conStr, true);

    // Note : The reconnect parameter indicates whether
    // any existing connection to be dropped
    // and new connection to be created. 
    // When you specify false and there already an open connection
    // available, then an exception will be generated.
}

private void Disconenct()
{
    this.da.Disconnect();
}

// ********************* Retrieving Data *********************

private void GetData()
{
    // 1. Basic data access with SQL statements
    DataTable dt1 = this.da.GetData("SELECT * FROM Users");

    // 2. Basic data access with SQL statements,
    //    passing an existing data table to be filled up with
    DataTable dt2 = new DataTable();
    this.da.GetData("SELECT * FROM Users", ref dt2);

    // 3. Data access using DB commands.
    DbCommand cmd1 = this._GenCommand();
    DataTable dt3 = this.da.GetData(cmd1);

    // 4. Data access using DB commands, 
    //    passing an existing data table to be filled up with
    DbCommand cmd2 = this._GenCommand();
    DataTable dt4 = new DataTable();
    this.da.GetData(cmd2, ref dt4);
}

private void GetSingleValue()
{
    // 1. Using commands
    DbCommand cmd1 = this._GenCommand();
    object o1 = this.da.GetSingleValue(cmd1);

    // 2. Using direct SQL statement or procedure without parameters
    object o2 = this.da.GetSingleValue("Query", CommandType.Text);
    object o3 = this.da.GetSingleValue("Procedure", 
                                       CommandType.StoredProcedure);

    // 3. Using procedure with parameters
    ParameterEngine pe = this._CreateParamEngine();
    object o4 = this.da.GetSingleValue("Procedure", 
                                       CommandType.StoredProcedure, pe);
}

// ********************* Executing Other Queries *********************

private void ExecuteNonQuery()
{
    int result = 0;

    // 1. Using commands
    DbCommand cmd1 = this._GenCommand();
    result = this.da.ExecuteNonQuery(cmd1);

    // 2. Using direct SQL statement or procedure without parameters
    result = this.da.ExecuteNonQuery("Query", CommandType.Text);
    result = this.da.ExecuteNonQuery("Procedure", 
                     CommandType.StoredProcedure);

    // 3. Using procedure with parameters
    ParameterEngine pe = this._CreateParamEngine();
    result = this.da.ExecuteNonQuery("Procedure", 
                     CommandType.StoredProcedure, pe);

    // Checking error
    if (result == DataAccess.ERROR_RESULT)
    {
        // Represents the last occured exception.
        // LastException is available only for ExecuteNonQuery command.
        // Data retrieval commands throws out the exceptions immediately.
        throw this.da.LastException;
    }
}

// ********************* DBCommand Generation *******************

private DbCommand _GenCommand()
{
    // Direct SQL statements
    DbCommand cmd1 = this.da.GenerateCommand(
      "SELECT * FROM Users", CommandType.Text, null);

    // Stored procedures
    ParameterEngine pe = this._CreateParamEngine();
    DbCommand cmd2 = this.da.GenerateCommand("spMyProcedure", 
                     CommandType.StoredProcedure, pe);

    return null;
}

// ********************* Parameter Engine Creation ****************

private ParameterEngine _CreateParamEngine()
{
    ParameterEngine pe = ParameterEngine.New(this.da);

    // Specifying input parameters
    pe.Add("param1", 1);
    pe.Add("param1", DateTime.Now);
    pe.Add("param1", "Hello");

    // Specifying output parameter
    pe.AddOutputParameter("OutParam1");
    pe.AddOutputParameter("OutParam2", DbType.Int32, 4);

    return pe;
}

// ********* Retrieving Output Values From ParameterEngine *********

private object _RetrieveOutputValue(ParameterEngine pe, 
                                    string paramName)
{
    object ret = pe.RetrieveOutputParameterValue(paramName);
    return ret;
}

// ********** Retrieving Output Values From ParameterEngine ********

private void Transaction()
{
    // Note : Only one transaction
    // can be opened for a particular connection


    // Begins a transaction on the existing connection
    DbTransaction transaction = this.da.BeginTransaction();

    // Commits the begun transaction
    this.da.CommitTransaction();

    // Rollbacks the changes made in the existing transaction
    this.da.RollbackTransaction();
}

Future

I am looking on bringing up new features to this component. You can see some other classes in the code, which are still under construction. The next feature will probably be a schema retrieval class using which the schema of tables, procedures, field names etc., can be retrieved from a data source. I appreciate your suggestions.

History

• 22-Dec-2009: Posted the article.