OleDb web service
Download source code (93kb)
The purpose of this article is to demonstrate how to control a remote microsoft access (or any other oledb supporting databasae)
over the internet via a web service. This web service provides a generic architecture to access all forms of OleDb supporting databases over the internet via a webservice. This would include basic rights management, such as a username/password and ReadOnly access option. The benifit of
accessing a database over the internet, is (a) for syndicating information to partners, (b) to provide rapid remote updates - without having
to download & upload the entire database to make a non-standard update. This web service also supports:
- Active Directory Service
- Advantage
- AS/400 (from IBM)
- AS/400 and VSAM (from Microsoft)
- Commerce Server
- DB2
- DTS Packages
- Exchange
- Excel
- Internet Publishing
- Index Server
- Microsoft Jet
- Microsoft Project
- MySQL
- ODBC Databases
- OLAP Services
- Oracle (from Microsoft)
- Oracle (from Oracle)
- Pervasive
- Simple Provider
- SQLBase
- SQL Server
- SQL Server via SQLXMLOLEDB
- Sybase Adaptive Server Anywhere
- Sybase Adaptive Server Enterprise
- Text Files
- UniData and UniVerse
- Visual FoxPro
For connection strings for the above databases, a useful website is
Able consulting.
Using the OleDb web service
To install the web service, as included in the DLL, upload it to the /bin folder on your server, and copy config.xml into the bin folder also.
Copy service.asmx to your root folder (you can rename this file to something more meaningful).
1. Open a new project in visual studio.net, and make a new web reference to www.yourserver.com/service.asmx, and call it dbwebservice.
2. Drag a button and a datagrid onto the form, double click on the button.
3. Populate your databaseAccessParams class, thus:
dbwebservice.databaseAccessParameters dbParams = new dbwebservice.databaseAccessParameters();
dbParams.connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\home\\webtropy\\dict.mdb";
dbParams.sql= "select top 50 * from wb1913_a where field5 like '%ing'";
dbParams.username="john";
dbParams.password="pass";
(Modify the connection string according to your own database)
4. Pass this object to your webservice, and bind the result to the database thus:
dbwebservice4.OleDbWebService service = new dbwebservice4.OleDbWebService();
DataSet ds = service.ExecuteDataSet(dbParams);
DataTable dt = ds.Tables["sql"];
DataView dv = new DataView(dt);
this.dataGrid1.DataSource = dv;
The web service can be used from a asp.net page aswell. Please leave a note on the page if you've got an on-line example of this
service running under aspx.
Project overview
The project is developed in Visual Web Developer 2005, - simply because it had a in-built web server, which I don't have on my development
machine. I will then port the project from .NET 2.0 beta to .NET 1.1 so that the DLL is portable to production servers. The four main operations that the application needed to perform was - (a) read a dataset, (b) execute an update or insert, (c) retrieve a single value, and (d) retrieve database schema.
One of the core requirements of the application was to provide a rights management system. Because, when you provide a webservice like this, you may only want a selected few individuals read your database, and even then, you may nor want them to change your data. To provide this, I created a congiguration xml file called config.xml (NOT web.config) in the same /bin folder as the web service DLL. - Containing this:
<security>
<username>john</username>
<password>pass</password>
<readOnly>true</readOnly>
</security>
Meaning that a user "John" with password "Pass" could read from my database, but not write to it.
Unfortunately, the option to retrieve schema was dropped, after encountering some difficulties trying to reflect the schema data as it was returnerd from the webservice. More information on this is written on my blog at msn spaces.
Similarly, I was hoping to have the provided username and passsword as properties, and managed internally using session state management (where you declare EnableSession = true for each method). However, this didn't work from my windows forms client, so I resorted to declaring a class called databaseAccessParameters, and passing this to all the functions.
