Get the information and share it: Why a web service

ODBC is probably the most widely used interface to access relational databases. However, being a C programming language interface, it is not exactly easy for average developers. The Microsoft Foundation Class Library (MFC) wraps ODBC functionality in a few easy to use classes. I used MFC in almost all of my old applications and never had the need to call ODBC APIs directly.

Here is how ODBC works. The database vendor (or a third party) provides software (driver) to access their database system. In order to access database via ODBC, you need to install the necessary ODBC driver on your machine and configure an ODBC data source first. An ODBC data source typically defines where the server is, how to connect to it, and what database (on the server) to use, etc. Then you need to write code in your client program to call the ODBC APIs directly or use a library like MFC.

I wrote a COM component, an ActiveX control, based on MFC database classes that can be reused in almost all Windows programs. In this article, I will introduce a .NET web service that uses my ActiveX control to access various ODBC data sources.

Here is a summary of the advantages of accessing ODBC data sources through a web service.

Multiple client programs can use a single instance of the web service to access data. There is no need to write database related code in each individual application.
Users need to install ODBC drivers and configure ODBC data sources only on the server machine that hosts the web service. Client programs can be running on other machines over the network.
Client programs are not limited to .NET or Windows platform. All they have to do to access database is call a web service.
Database connections can be shared among different client programs.
Access to databases can be controlled and monitored from a central location (the web service).

Of course, there are also disadvantages and limitations of this method which I will talk about later in the article.

The web service interface

Before invoking my web service from .NET code, you need to add a web reference for the web service to your project first, a proxy class ODBCService will be generated for you automatically. You can use XYSoapClient (which requires Microsoft Soap Toolkit 3.0) to invoke my web service from non .NET Windows programs.

To do a simple select query, call the GetXmlStringEx method. This method takes two string parameters. The first one is a connection string, the second a SQL SELECT statement. Here is an example of the connection string:

dsn=MyDataSourceName;uid=MyUserID;pwd=MyPassword

Note that dsn is the name of the ODBC data source you have previously configured on the server, uid is the database login ID, and pwd is the login password.

The GetXmlStringEx method returns data in an XML string, like the following:

<XYODBC>
   <r0>
       <c0>...</c0>
       <c1>...</c1>
       <c2>...</c2>
       ...
   </r0>
   <r1>
       <c0>...</c0>
       <c1>...</c1>
       <c2>...</c2>
       ...

   </r1>
   <r2>
       <c0>...</c0>
       <c1>...</c1>
       <c2>...</c2>
       ...
   <r2>
   ...
   <RecCount>...</RecCount>
</XYODBC>

Here the <r0>, <r1>, and <r2> elements are data rows, the <c0>, <c1>, and <c2> elements are data columns, and the <RecCount> element is the total number of data rows retrieved by the current query.

The method returns empty string in case of error. All error messages will be logged on the server. The following C# sample code executes a SQL SELECT statement to retrieve all employee records from a database table.

StringsConnect="dsn=TestData;uid=Tester;pwd=Test123";
String sSQL = "select * from Employees";
ODBCService oService = new ODBCService();
oService.Url = "http://MyServer.com/ODBCService/ODBCService.asmx";
String sDataXML = oService.GetXMLStringEx(sConnect, sSQL);
if(sDataXML!="")
{
   // process the data

   XmlDocument oDoc = new XmlDocument();
   oDoc.LoadXml(sDataXML);
   // ...

}
else throw new Exception("Query failed: " + sConnect + ", " + sSQL);

The ExecuteEx method is used to execute a SQL INSERT/UPDATE/DELETE statement or a stored procedure that modifies data in the database. It returns a boolean value to indicate success or failure. Here is the C# sample code to call a SQL Server stored procedure named UpdateEmployee that takes first name, last name, and status as string parameters.

String sConnect = "dsn=TestSQLData;uid=Tester;pwd=Test123";
String sSQL = "exec UpdateEmployee 'john', 'doe', 'active'";
ODBCService oService = new ODBCService();
oService.Url = "http://MyServer.com/ODBCService/ODBCService.asmx";
if(oService.ExecuteEx(sConnect, sSQL))
{
   // ...

}
else throw new Exception("Update failed: " + sConnect + ", " + sSQL);

The above two methods perform database operations in a single web service call. It is also possible to create a database connection on the server and use it multiple times from the client. For example, the following code executes three SQL statements in a database transaction.

Collapse

String sConnect = "dsn=TestSQLData;uid=Tester;pwd=Test123";
String sSQL1 =
 "delete from Employees where firstname = 'john1' and lastname = 'doe1')";
String sSQL2 =
 "insert into Employees(firstname, lastname) values('john2', 'doe2')";
String sSQL3 = "select * from Employees";
ODBCService oService = new ODBCService();
oService.Url = "http://MyServer.com/ODBCService/ODBCService.asmx";
String sObjID = "";
try
{
   sObjID = oService.CreateObject(sConnect);
   if(sObjID=="") throw new Exception("CreateObject failed");
   if(oService.BeginTransaction(sObjID)==false)
       throw new Exception("Transaction failed");
   if(oService.Execute(sSQL1, sObjID)==false)
       throw new Exception("Update failed");
   if(oService.Execute(sSQL2, sObjID)==false)
       throw new Exception("Update failed");
   String sDataXML = oService.GetXmlString(sSQL3, sObjID);
   if(sDataXML=="")
       throw new Exception("Query failed");
   // more code ...

   if(oService.Commit(sObjID)==false)
       throw new Exception("Commit failed");
}
catch(Exception oBug) { throw oBug; }
finally { if(sObjID!="") oService.ReleaseObject(sObjID); }

The CreateObject method creates a database connection on the server and returns a string ID (a guid string). You need to use this string ID when calling methods like BeginTransaction, GetXMLString, Execute, Commit, etc. The GetXMLString and Execute methods are the same as the GetXMLStringEx and ExecuteEx methods described in the above except that they need to use an ID string returned by CreateObject as input parameter.

Please note that you must call ReleaseObject after calling CreateObject, otherwise the database connection on the server will never be released. When a transaction fails, it will be rolled back automatically, therefore you almost never need to call the Rollback method explicitly.

Whenever possible, you need to code your transactions within stored procedures because multiple web service calls are inefficient and more likely to fail. You may also want to create database views for your data retrieval instead of querying database tables directly.

Implementation, installation, and configuration

The web service keeps a pool of database connections in its memory. When the client program calls GetXMLStringEx, ExecuteEx, and CreateObject, the server will use an existing connection (with the same connection string) if it is available and lock it for the client, otherwise it will create a new one.

At the end of the GetXMLStringEx and ExecuteEx methods or when ReleaseObject is called, the locked database connection will be returned to the connection pool for future use.

Whenever a database failure occurs, the connection will be closed by the server automatically, the server will also try to reconnect to the database whenever needed. It is not necessary to close a database connection explicitly.

The web service will write errors and diagnostic information to log files in a system Log folder. A log file will be generated each day. By modifying the web.config file, you can control how logging is done. The following is part of the web.config file. TraceLevel determines how much information will be logged, the possible values are 0, 10, 20, 30, and 40, where 0 means no logging and 40 means the most detailed logging. TraceCleanup is the number of days to keep the log files, old files will be deleted automatically.

<appSettings>
   <add key="TraceLevel" value="40" />
   <add key="TraceCleanup" value="7" />
</appSettings>

Here is what you need to do to install the web service.

Unzip files into a folder (preserving the folder structure).
Run the VB script file InstallODBCService.vbs.

The script will first ask you where you want to install the web service, you need enter the full path of the folder of your choice.

Then, it will ask you to confirm your website by entering your website name (the app friendly name), this is for servers that host multiple websites, just click OK if this does not apply to you. It will also ask you the port number used by your website. Again click OK if it is the default http port (80).

Finally, it will ask you whether you want to allow access to the web service from all IP addresses. If you enter yes, then there will be no access restriction. Otherwise, only IP addresses from your subgroup can access your web service. For example, if your website is bound to IP address 172.49.21.39, then only clients from IP addresses of the form 172.49.21.* can call your web service.

You can do more detailed configuration for the web service, including setting user name and password, using the Windows Internet Services Manager.

After the web service is installed, you can use existing ODBC data sources on the server or configure new ODBC data sources. It is important to remember that the ODBC data sources used by the web service have to be system data sources, not user data sources, because the web service will be running under the system account (user data sources are not visible to the web service).

Disadvantages and limitations

As you can see, by using this web service, your database will be exposed to all kinds of programs on the network. This is good but it also makes your database less secure in a way. Although it is possible to access the web service across a firewall, it is recommended that access to the web service should be restricted to trusted programs within the firewall if you are concerned with the security of your data.

Here are some more disadvantages and limitations of this web service. They are mainly limitations of my implementation, not of the general method described here.

The data types are restricted to string or types that can be converted easily to string (such as numbers, DateTime, etc.). It is not possible to retrieve general binary data using the web service.
It does not support complicated SQL statements. Simple SELECT/INSERT/UPDATE/DELETE statement is OK.
You cannot do nested transactions even if your database supports it.
Null value of a database field will be represented as empty string.

System.Data.SqlClient and System.Data.OleDb in .NET

These are two namespaces in the .NET framework which contain classes you can use to write database code. I previously wrote a .NET DB library and a web service based on classes in the System.Data.OleDb name space. These .NET classes are much easier to use than the ODBC interface, however it is not possible to use them to access some non Microsoft databases.

On the other hand, ODBC drivers exist for almost all relational databases. I am aware of the ODBC .NET provider from Microsoft, it has almost the same features as the .NET SqlClient and OleDb providers. According to Microsoft documentation, the ODBC .NET provider has not been tested against any non Microsoft databases.

Get the information and share it

The web service interface

Implementation, installation, and configuration

Disadvantages and limitations

System.Data.SqlClient and System.Data.OleDb in .NET

Blog Archive