Component to access databases in the .NET environment

Article

Component to access databases in the .NET environment

By Xiangyang Liu | Published: 23 June 2005 | Reader Level: Intermediate

An extremely flexible and easy-to-use library that retrieves data in XML format, supports simple parameterized queries, stored procedures, and transactions. It can also be used as a web service.

Download source files - 48.3 Kb

Introduction

Recently I wrote a C# library XMLDB.dll for the .NET environment. This dll contains a single class XMLDBComponent which greatly simplified my database programming work. It can, for example, execute a SQL query and convert the retrieved data into an XML document object with a single method call.

There are three projects included in the source code package.

XMLDBLib builds the library, XMLDB.dll.
XMLDBTest is an ASP.NET test application.
XMLDBService is a web service making it possible for clients running on various machines and platforms to access XMLDB.dll.

Unless otherwise stated, the sample code in this article is in C#.

The class XMLDBComponent is implemented using classes from the System.Data.OleDb name space. Instead of using more than five classes, now you can use only one class for all of your basic database work in .NET. The trade-off is, you won't have all the power and control provided by the .NET Framework classes. The reason I did not build my class on classes from the System.Data.SqlClient name space is that I don't want to restrict my databases to just SQL server. I would like to include MS Access, for example. Too bad the System.Data.OleDb classes do not support ODBC data sources.

The code has only been tested on Windows 2000 against SQL 7, SQL 2000, and MS Access databases.

Simple database access

First, we have to create an instance of the XMLDBComponent object. This is done by calling the static method CreateObject(), like the following:

XMLDBLib.XMLDBComponent obj = XMLDBLib.XMLDBComponent.CreateObject(
    "Provider=myProvider;Database=myDB;Server=myServer;" + 
    "User ID=me;Password=");

The parameter in the above code is the connection string. For details about the connection string, please read the documentation for the .NET Framework class System.Data.OleDb.OleDbConnection. After the object is created, the following code will make a SQL query and save the returned data in a file.

System.Xml.XmlDocument doc = obj.GetXmlDocument(
    "select ID, firstname, lastname, hiringdate " +
    "from employee");
obj.Close();
// or call Release instead of Close:
// obj.Release();

if(doc==null)
{
    String strError = obj.GetLastError();
    // more error handling code
}
else
{
    doc.Save("c:\temp\employee.xml");
}

The Close() method will close the database connection and release all internal resources. You don't have to call it if the same XMLDBComponent object needs to be reused later. Even if you called Close(), the object knows how to automatically reconnect to the same database the next time it is used. In fact, the Close() method is called internally each time a database error occurs to refresh the database connection. We will talk about the Release() method in the comment later.

Here is the XML file representing the returned data in the above code.

<XMLData>
    <Record>
        <ID>334</ID>            
        <firstname>Albert</firstname>
        <lastname>Einstein</lastname>
        <hiringdate>3/12/1999</hiringdate>
    </Record>
    <Record>           
        <ID>404</ID>
        <firstname>Clint</firstname>
        <lastname>Eastwood</lastname>
        <hiringdate>12/21/2000</hiringdate>
    </Record>
    <Record>
        <ID>536</ID>
        <firstname>James</firstname>
        <lastname>Bond</lastname>
        <hiringdate>10/23/2002</hiringdate>
    </Record>
    <RecordCount>3</RecordCount>
    <FieldCount>4</FieldCount>
</XMLData>

GetXmlDocument() is the method that retrieves data from the database and builds the output document object. It returns null on error. In the output XML document, each record is contained in a <Record> element and each field of the record is a subelement. If a field value is null, then the corresponding subelement will be missing from the output document. The column names in the SQL query will be used as the names of the field elements. If the columns are not named (such as select * from ...), the corresponding database field names will be used as the element names. For a calculated column you must give it a name in the SQL query, for example, select count(*) as total from employee. At the end of the XML document, there are two elements <RecordCount> and <FieldCount> indicating the size of the output record set.

The GetXmlString() method is the same as the GetXmlDocument() method except that the output is a string instead of a document object. This method returns the empty string on error. By the way, there is no exception thrown by XMLDBComponent. You can check error by the return value and the GetLastError() method returns a descriptive error message for the most recent error.

Thread safety and object pooling

All static methods in the class XMLDBComponent are thread safe, but the class is not thread safe at the object level meaning you cannot have multiple threads accessing the same object simultaneously. In a multi-threaded application such as the web server, there may be many threads running simultaneously. We could create a separate XMLDBComponent object in each new thread. However, this is not always the most efficient way. Because not all threads are doing database related work all the time, we should reuse existing objects as much as possible.

XMLDBComponent maintains a global pool of objects that makes sharing objects an easy thing to do. When you call the CreateObject method, the global object pool is checked to see if there is already a unused object with exactly the same connection string. If such an object is found, it will be returned to you without creating a new one. If an object is no longer needed, you should call the Release() method so that other threads can reuse it. Note that you must not use an object after its Release() method is called.

Another thing to remember is that you do not have to call the Close() method before releasing an object to the pool, because the same object can be reused later. At the end of your program, you should call the CloseAll() static method, which will call Close on all unused objects in the global object pool. Therefore, as long as you remember to release an object after using it, the CloseAll() method will clean up everything.

You don't have to keep a reference to the XMLDBComponent object you created. Instead you can obtain its ID string by calling the GetID() method. Later in your program, you can call the GetObject method using the ID string as parameter to retrieve the exact object you used before. However, you cannot do this if you already called Release() on this object. This feature is very helpful in implementing the web service for XMLDBComponent, as you will see later.

I am aware that the .NET Framework classes already support database connection pooling.

Using parameters in SQL queries or stored procedures

Besides retrieving data from the database, you can also modify records in the database (insert, update, or delete). Suppose you have a stored procedure UpdateTotal that takes a string parameter ID and a integer parameter total, here is a VB subroutine that calls this procedure.

Public Sub CallProcUpdateTotal( _
    ByVal strConnect as String, ByVal strID as String, _
    ByVal nTotal as Integer)
       
    Dim obj as XMLDBLib.XMLDBComponent = _
        XMLDBLib.XMLDBComponent.CreateObject(strConnect)

    obj.AddParameter(strID)
    obj.AddParameter(nTotal)
       
    If obj.Execute("exec UpdateTotal @ID=?, @total=?") Then          
        ' Success
    Else
        Dim strError as String = obj.GetLastError()
       ' more error handling code
    End If
    
    obj.Release()
End Sub

The parameters you add using the AddParameter() method has to be in the same order as they appear in the SQL command. The Execute() method will run a SQL command that updates the database. You can also use parameters with the GetXmlDocument() or the GetXmlString() methods to retrieve data. After a SQL query or command is executed by an XMLDBComponent object, the added parameters are cleared automatically. You need to add the parameters again the next time you want to make the same call.

Simple transactions

XMLDBComponent supports simple transactions. Call the BeginTransaction() method to, well, begin a new transaction. Then do some database work. And finally call the Commit() method to complete the transaction. If any database error occurs, the transaction will be rolled back automatically so you don't have to remember to rollback your transaction. You can call the Rollback method manually if you change your mind about the work you have done to the database since the start of the transaction.

There is no problem to have multiple transactions in your application (just use multiple XMLDBComponent objects). However, nested transaction is not permitted. If you call BeginTransaction() twice on an object, the previous transaction will be rolled back before the next one is started.

Here is a more complicated "virtual" example. Suppose you have a .NET web application which consists of multiple pages. The user is supposed to go through the pages one by one and saving the data to the database along the way. At the last page, all the data collected from the user should be committed or ralled back. In the first page, your app will call the CreateObject() method and then call BeginTransaction() to start database work. When the user moves to the next page, the ID string of the object will be passed along and the next page will call GetObject() using the ID string to obtain the same object created in the first page. On the last page, your app will call the Commit() method to complete the work and call the Release() method to return the object to the pool. Actually, I have never tried the idea in real applications, there could be many problems with this kind of approach.

The web service

The project XMLDBService is a simple web service written in VB. It exposes the methods of XMLDBComponent to all potential web clients. That means your client program doesn't have to create a new XMLDBComponent object in its own address space and the XMLDB.dll doesn't have to be copied onto the same machine the client program is running. In fact, you don't even need the .NET Framework or the Windows operating system installed on the client machine (theoretically, at least).

The methods in the web service are almost the same as those in the XMLDBComponent class except an extra object ID parameter. The typical way to use the web service is, you first call the CreateObject() method, instead of returning an XMLDBComponent object, the method returns an ID string which identifies a unused object within the object pool located in the remote web service process. Later when you call other web service methods, you need to pass the ID string along so that the SQL queries or stored procedures will be executed on the same object you obtained before. Internally, the web service just uses the ID string you passed to call GetObject(), then executes your SQL statements on the returned object. By the way, the Release() method has to be called for each object accessed, otherwise the global object pool in the web service process will get bigger and bigger.

With such a web service, there can be some very unconventional applications. For example, a program A will create an XMLDBComponent object using the web service, start a database transaction on it, and then pass the ID string of this object to program B. Program B will do some database work on this object using the ID string, either commit the transaction or pass the ID string to a third program, program C. Program C can do the same as program B, and so on. So it seems the transaction has crossed process or machine boundaries (actually it all happens within the object in the remote web service process).

Summary

Here is the complete list of public methods of the XMLDBComponent class and we have covered almost all of them. As mentioned before, the methods for the corresponding web service are almost the same except an extra string parameter for the object ID.

// create an object with given connection string
static public XMLDBComponent CreateObject(
    String strConnect); 

// get the last error message on this object
public String GetLastError(); 

// get the ID string of the object
public String GetID(); 

// (optional) open database connection explicitly
// using the internal connection string
public bool Open(); 

// (optional) open database connection explicitly using the
// connection string parameter
public bool Open(String strConnect); 

// close the database connection and release all resources 
// assocated with the object
public void Close(); 

// release the object to the global object pool so other 
// threads can reuse it
public void Release();

// add a parameter for the next SQL query or command
public void AddParameter(Object par); 

// run a SQL query and  return data as an XML document
public XmlDocument GetXmlDocument(String strSQL); 

// same as above except the return type is string
public String GetXmlString(String strSQL); 

// execute a SQL command to update the database
public bool Execute(String strSQL); 

// begin a new database transaction
public bool BeginTransaction(); 

// commit the current transaction
public bool Commit(); 

// rollback the current transaction
public bool Rollback(); 

// get an object using the ID string
static public XMLDBComponent GetObject(String strID);

// clean up all unused objects in the object pool 
// (close connections, etc.)
static public void CloseAll();

I hope you will find the code interesting or even useful. Thank you.

Finally, a shameless plug

Some ideas in this article are taken directly out of my free software package XYSystem Components. By the way, it can be used easily in the .NET environment.