How Can i excute SQL queries at Access 2007 file from C# Web Service

Answer 1

May I suggest a few improvements to your design and code structure?

1. Re-evaluate the reason for making this a web service. Can you compile this into a class library assembly (.dll) and reference it from other projects? The overhead, or time cost, of making the web service call is much larger than a reference to another library. Yes, there are configuration issues with Access that you'll have to deal with.

2. Reconsider the reason that you want a DataSet returned on each call. Here's an excellent article on the drawbacks of ADO.NET DataSets: www.4guysfromrolla.com/articles/050405-1.aspx

3. Do not execute any string given to you without evaluating or parameterizing it first. I understand the desire to create a layer that will handle all the database querying for you, and you should be applauded for that idea. The code, as presented, will create a more dangerous situation however, as it doesn't check for any malformed statements, etc. So the suggestion is to NOT execute any string that is passed to you. Perhaps this web service is INSIDE your LAN only, and perhaps you trust that all calls will be honest and non-destructive. We might think that all internal activity is good, but as soon as an employee turns bad, you are opening the possibility for inside sabotage.

What happens when someone calls your webservice like this:

ExecuteQuery("DELETE FROM Customers")

or

ExecuteQuery("UPDATE Employee SET Salary = 250000 WHERE ID= 9")

or

ExecuteQuery("SELECT Salary WHERE EmployeePosition = 'CEO'")

4. Create one new webmethod for each function that you want the layer to expose. For example, instead of the client calling

ExecuteQuery("SELECT ID, CustomerName FROM Customers ORDER BY CustomerName")

do this:

public List<Customer> ListAllCustomers()

Consider creating methods like this:

public void UpdateEmployee(Employee emp)

or

public void UpdateEmployeeSalary(string id, double salary)

5. Put your Access connection string into an app.config file. Reference System.Configuration and use ConfigurationManager. load it everytime when the query is called. It's a bit of a performance hit, but really the maintenance workload is zero when changing the directory path or name of the .mdb file.

  private string GetConnectionString()
        {
           //do some more checking on whether the value exists as well, instead of just returning it!
            return ConfigurationManager.AppSettings["MyAccessDB"].ToString();
        }

Answer 2

Also:

using (OleDbConnection conn = new OleDbConnection(
         @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
         @"C:\Folder\Database2.accdb;Persist Security Info=False;"))
{
    using (OleDbDataAdapter da = new OleDbDataAdapter(sqlQuery, conn))
    {
        DataSet ds = new DataSet();
        da.Fill(ds);            
        return ds;
    }
}

Do this unless you like resource leaks.