Code, photos, books, and anything else

Subscribe to my RSS feed

Simple .NET data abstraction

Introduction

In an ASP.NET project I’ve been working on recently, there was a need to support different database systems, such as Access, SQL Server, Oracle, and MySQL. This is because a single report will be based off a particular database system; there are numerous reports available, which may not all be based on the same database platform.

It would normally take quite a bit of repeated code to be able to handle different database systems, depending on which report needed to be queried. This is because each type of database has a different programming interface, with different object names and such. I say normally because this isn’t the case here.

When I started the project, I knew the software needed to support multiple database platforms, so I spent some time developing a simple database abstraction system. What this means is, I can create a custom object in my program, tell it what database system I want to use, and provide the connection details. The custom object then handles all the nitty gritty details so far as the different interfaces and objects for each database system. I can then give it a query to run, and the generic database class runs the query against the appropriate database, on the appropriate database system. This saves a LOT of repeated code.

The Code

Database.vb

The generic database class to be used from program code. Hides away the details involved in using a specific database system.

Namespace DataAccess

    '---------------------------------------------------------------------
    ' A generic database access class; wraps connections and queries for
    ' SQL Server, Access, MySQL, and Oracle.
    '---------------------------------------------------------------------
    Public Class Database

        '=====================================================================
        ' PRIVATE MEMBERS
        '=====================================================================

        'Information about the connection
        Private _DatabaseInterface As DatabaseInterfaceEnum
        Private _ConnectionString As String

        'The internal database factory
        Private _Factory As DatabaseFactory

        'The internal connection object - don't yet know what type it is to be
        Private _Connection As IDbConnection

        '=====================================================================
        ' CONSTRUCTOR
        '=====================================================================

        '---------------------------------------------------------------------
        ' New
        '---------------------------------------------------------------------
        ' Creates a generic database connection object for the specified
        '   database type and connection string.  Used when the full
        '   connection string is available.
        '
        ' DatabaseType - type of database to create a connection for
        ' ConnectionString - full connection string for the database
        '---------------------------------------------------------------------
        Sub New(ByVal DatabaseInterface As DatabaseInterfaceEnum, ByVal ConnectionString As String)
            _DatabaseInterface = DatabaseInterface
            _ConnectionString = New String(ConnectionString)

            _Factory = New DatabaseFactory(DatabaseInterface)
            _Connection = _Factory.CreateConnection(_ConnectionString)
        End Sub 'New

        '=====================================================================
        ' PROPERTIES
        '=====================================================================

        Public ReadOnly Property ConnectionString() As String
            Get
                ConnectionString = _ConnectionString
            End Get
        End Property 'ConnectionString

        Public ReadOnly Property DatabaseInterface() As DatabaseInterfaceEnum
            Get
                DatabaseInterface = DatabaseInterface
            End Get
        End Property 'DatabaseInterface

        '=====================================================================
        ' PUBLIC FUNCTIONS
        '=====================================================================

        '---------------------------------------------------------------------
        ' Open
        '---------------------------------------------------------------------
        ' Opens the internal connection object - has already been created for
        '   specified interface.
        '---------------------------------------------------------------------
        Public Sub Open()
            _Connection.Open()
        End Sub 'Open

        '---------------------------------------------------------------------
        ' Close
        '---------------------------------------------------------------------
        ' Closes the internal connection object.
        '---------------------------------------------------------------------
        Public Sub Close()
            _Connection.Close()
        End Sub 'Close

        '---------------------------------------------------------------------
        ' RunQuery
        '---------------------------------------------------------------------
        ' Runs a given query using the internal connection object.
        '   Automatically opens and closes the internal connection.
        '
        ' Query - the query to run
        '---------------------------------------------------------------------
        Public Function RunQuery(ByVal Query As String) As DataTable
            'To receive the query results
            Dim Results As DataTable = New DataTable()

            'Try to run a query; if exception occurs, let it bubble up the stack
            Try
                Open()

                'Need a DataAdapter to retrieve results from data source into a DataTable
                Dim DataAdapter = _Factory.CreateDataAdapter()

                'Assign a command for the adapter - in this case, a query
                DataAdapter.SelectCommand = _Factory.CreateCommand(Query, _Connection)

                'Fill the DataTable with results from the query
                DataAdapter.Fill(Results)
            Finally
                'Always close the connection
                Close()
            End Try

            RunQuery = Results
        End Function 'RunQuery

    End Class

End Namespace

This class is not very complex, but it does do a lot of work. You create it by providing the appropriate database interface to use, along with the connection string to connect to a given database. Then you just need to use the RunQuery function with a complete query string to retrieve data from the actual database. Note the function calls on the internal DatabaseFactory object. Read on to find out more about that.

DatabaseFactory.vb

No need to use this one directly; it is used behind the scenes in Database.vb. It handles creation of the correct objects to use depending on which database platform is being targeted.

Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports MySql.Data.MySqlClient

Namespace DataAccess

    'Interfaces to be used for particular databases
    Public Enum DatabaseInterfaceEnum
        OleDb = 1
        SqlClient
        OracleClient
        MySqlClient
    End Enum

    '---------------------------------------------------------------------
    ' Class for providing the objects specific to a database system for
    '   running queries.
    '---------------------------------------------------------------------
    Public Class DatabaseFactory

        '=====================================================================
        ' PRIVATE MEMBERS
        '=====================================================================
        Private _DatabaseInterface As DatabaseInterfaceEnum

        '=====================================================================
        ' CONSTRUCTORS
        '=====================================================================
        Public Sub New(ByVal DatabaseInterface As DatabaseInterfaceEnum)
            _DatabaseInterface = DatabaseInterface
        End Sub

        '=====================================================================
        ' PUBLIC FUNCTIONS
        '=====================================================================

        '---------------------------------------------------------------------
        ' CreateConnection
        '---------------------------------------------------------------------
        ' Creates and returns a connection object of the appropriate type.
        '
        ' Connect - the connection string for the database
        '---------------------------------------------------------------------
        Public Function CreateConnection(ByVal Connect As String) As IDbConnection
            Select Case _DatabaseInterface
                Case DatabaseInterfaceEnum.SqlClient
                    CreateConnection = New SqlConnection(Connect)

                Case DatabaseInterfaceEnum.MySqlClient
                    CreateConnection = New MySqlConnection(Connect)

                Case DatabaseInterfaceEnum.OracleClient
                    '

                Case Else 'OleDb
                    CreateConnection = New OleDbConnection(Connect)

            End Select
        End Function 'CreateConnection

        '---------------------------------------------------------------------
        ' CreateCommand
        '---------------------------------------------------------------------
        ' Creates and returns an appropriately typed command object.
        '
        ' Query - the query the command object is made to run
        ' Connection - the connection that the command will run on
        '---------------------------------------------------------------------
        Public Function CreateCommand(ByVal Query As String, ByVal Connection As IDbConnection) As IDbCommand
            Select Case _DatabaseInterface
                Case DatabaseInterfaceEnum.SqlClient
                    CreateCommand = New SqlCommand(Query, Connection)

                Case DatabaseInterfaceEnum.MySqlClient
                    CreateCommand = New MySqlCommand(Query, Connection)

                Case DatabaseInterfaceEnum.OracleClient
                    '

                Case Else 'OleDb
                    CreateCommand = New OleDbCommand(Query, Connection)

            End Select
        End Function 'CreateCommand

        '---------------------------------------------------------------------
        ' CreateDataAdapter
        '---------------------------------------------------------------------
        ' Creates and returns a data adapter of the correct type.
        '---------------------------------------------------------------------
        Public Function CreateDataAdapter() As IDataAdapter
            Select Case _DatabaseInterface
                Case DatabaseInterfaceEnum.SqlClient
                    CreateDataAdapter = New SqlDataAdapter()

                Case DatabaseInterfaceEnum.MySqlClient
                    CreateDataAdapter = New MySqlDataAdapter()

                Case DatabaseInterfaceEnum.OracleClient
                    '

                Case Else 'OleDb
                    CreateDataAdapter = New OleDbDataAdapter()

            End Select
        End Function 'CreateDataAdapter

    End Class 'DatabaseFactory

End Namespace

Notice, near the top, the DatabaseInterfaceEnum. It defines the different database interfaces that can be used. When a Database object is constructed, given a particular value from DatabaseTypeEnum, a DatabaseFactory object is also constructed. When the database object sets up the internal Connection, Command, and DataAdapter objects, the internal DatabaseFactory object is asked to generate the objects using an interface appropriate to the database platform being used.

Note that I’m missing bits for Oracle, but I don’t have access to an Oracle database, so I have no way to test that functionality. Besides, the built-in Oracle objects are not available until .NET 1.1; this code is done using .NET 1.0. I really should upgrade!

It should be a very simple task to add support for other database systems (e.g. PostgreSQL, DB2, etc) that have connector classes for .NET, similar to the MySQL/.NET connector (read a bit more about this in my previous post about databases). Just add the reference to the Imports section at the top, add an entry to the DatabaseInterfaceEnum, and for each function, add a section to the select statement that returns the appropriate object. And that should be all that is necessary.

This behind-the-scenes class is fairly simple, but could be extended further to handle SQL generation, using the correct SQL syntax for the given database system. SQL is a stndard syntax, but some databases use proprietary extensions to the language, so it is important to allow for these. For example, Access has a particular SQL syntax to only retrieve the first 5 rows of a table, whereas SQL Server has it’s own syntax to do the same thing, and so on.

Sample Usage

This is a trivial example, but it should be enough to show how simple data access is when using an abstraction layer. Note how simple it is to use the Database class; all of the database-specific details are completely invisible.

Imports DataAccess

Dim db As Database
Dim results as DataTable

'Access sample
db = New Database(DatabaseInterfaceEnum.OleDb, "connection string")
results = db.RunQuery("SELECT * FROM Books")

'SQL Server sample
db = New Database(DatabaseInterfaceEnum.SqlClient, "connection string")
results = db.RunQuery("SELECT * FROM Books")

'MySql sample
db = New Database(DatabaseInterfaceEnum.MySqlClient, "connection string")
results = db.RunQuery("SELECT * FROM Books")

Conclusion

For related reading, I recently read an article discussing four types of data abstraction layers, ranging from simple to complex, and points out their similarities and differences. For those interested in learning more about different data abstraction layers, this article is a good read. It also has several links to other related articles, so there’s a lot of reading to do on the subject!

The data abstraction layer I have developed would currently fall under Type 2 as described by the article. It could however be extended to become a Type 3; it takes a complete query string to run a query, but it could be further abstracted to provide more insulation from details of SQL syntax, and differences in that syntax between different systems.

Admittedly, my code won’t be very relevant in a lot of situations, as most applications tend to use data from a single database system, so there would be no need to support other systems. But it is relevant in my current project, what with needing to pull data from different database systems. And even though I’m using this code as part of an ASP.NET project, it is general enough to be usable with other .NET languages, such as C#, and not just in web applications.

Author:

Format:

Category:
Programming

Tags:

5 Comments
Comments closed

Comments

#1

bryan

August 23, 2006 @ 7:02 pm

Good stuff. I’m not a .NET developer but you’re right, the concept is the same. Simplify.

#2

Grant

August 23, 2006 @ 7:46 pm

Thanks for the compliment. Hopefully I made sense! I had been thinking of making this post for a while, but having read yours spurred me into getting it done. You found mine awfully quickly!

I’m always trying to improve my code; I did plenty of reading on the web, learning about things like custom classes, design patterns and so on. My data handling code was much messier before, but it’s a lot better now that I have employed some of the mentioned techniques.

#3

bryan

August 24, 2006 @ 1:04 pm

What did you think of mine?

#4

Grant

August 24, 2006 @ 2:10 pm

It was good actually. I’ve worked off and on with PHP for some time, and have been doing a lot of repeated data access code across multiple pages. I’d really like to simplify this process by adding a simple data abstraction/management system to reduce this tedium. The code from your article will be a useful starting point.

Again, having read your article prompted me to do my own article on the same subject, except in .NET.

#5

Bryan

August 25, 2006 @ 2:50 pm

One convenient way to spread data access across pages is using polymorphism and using objects in PHP to pass an object and it’s methods between classes/pages without ever creating duplicate connections to the database over pages. I use such a system and it really saves processing time, big time.

Leave a Comment

Apologies, commenting is now disabled for this post.