Have you ever wanted to remotely access your website's Access Database from an internal app, or another website? It's possible...
Don't be fooled by the title of this article, there is no new product on the market (other than .Net if you want to look at it that way). Many a times has it been asked on messageboards and newsgroups across the web, and I've got to say that even I've wanted to do it a couple of times.
MS Access is the preferred database for lots of projects that are starting up, or developers that are working on a budget, and it steps up to the job well. But most projects just don't have the ability to use a database like MSSQL server that will allow other websites and applications to access it mainly because of price, or the fact that they're on a shared server. With the advent of SOAP Webservices, remote access of an Access database is possible.
For this example, I will make a webservice in C# that takes a sql statement, along with a username and password for security. The service will have two public methods:
- getDataSet(string username, string password, string sql, **string **tableName)
This method returns a dataset with the results of whatever sql statement you pass in. The tableName property is simply passed on to the dataset as the name of the table.
- ExecuteSql(string username, string password, string sql)
This method simply execute the sql statement, and returns an int telling you how many rows where affected
For the sake of brevity, I did not include the database connection code in the example. the reason for this, is that I used a Data Access Layer (which I will publish someday) to connect, so the code would not have made much sense to you.
<%@ WebService Language="C#" Class="CodeCubeDal" %>
using System;
using System.Web.Services;
using System.Xml;
using System.Data;
using System.Configuration;
[WebService(Namespace="http://www.codecube.net/services/")]
public class CodeCubeDal : WebService {
[ WebMethod(EnableSession=false) ]
public **DataSet** getDataSet(**string** username, **string **password,
**string** sql, **string **tableName)
{
** DataSet** ds;
if (auth(username, password)) {
// connect to your
database, execute the sql statement, and return the Dataset
return ds;
} else {
return new DataSet(tableName); //returns
an empty dataset if the username or passord is wrong
}
}
[ WebMethod(EnableSession=false) ]
public **int** ExecuteSql(**string** username, **string** password,
**string** sql) {
if (auth(username, password)) {
// connect to
your database, execute the sql statement using "Execute Non Query"
// return the number of rows affected
} else {
return 0;
}
}
private bool auth(**string** username, **string** password) {
if (username != "myusername" && password
!= "mypassword")
{return false;}
else
{return true;}
}
}