如何从C＃Web服务在Access 2007文件中执行SQL查询

we want to have a hosted C# web service in IIS that i can pass to its method SOL Query and it execute the query on a Access 2007 DB file that exist in the same server that contain Web service and return data
We Already the following code

我们希望在IIS中有一个托管的C#Web服务,我可以将其传递给它的方法SOL Query,它在存在于包含Web服务的同一服务器中的Access 2007 DB文件上执行查询并返回数据我们已经是以下代码

<%@ WebService Language="C#" 
  CodeBehind="~/App_Code/Service.cs" 
  Class="Service" %>
using System;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;

using System.Data;
using System.Data.OleDb;
using System.IO;

public class Service : System.Web.Services.WebService
{
    [WebMethod]
    public DataSet ExecuteQuery(string sqlQuery) 
    {
        OleDbConnection conn = new OleDbConnection(
             @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
             @"C:\Folder\Database2.accdb;Persist Security Info=False;");

        OleDbDataAdapter da = new OleDbDataAdapter(sqlQuery, conn);
        DataSet ds = new DataSet();
        da.Fill(ds);            
        return ds;
    }

}

but when we call this method the page is just shown as in Load ( in progress ) but without any return of data or Exception ... Could any help us to find a solution to this problem ?

但是当我们调用这个方法时,页面只显示在Load(正在进行中)但没有任何数据返回或异常......可以帮助我们找到解决这个问题的方法吗?

2 个解决方案

#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.

1.重新评估使其成为Web服务的原因。你可以将它编译成类库程序集(.dll)并从其他项目中引用它吗?进行Web服务调用的开销或时间成本比对另一个库的引用要大得多。是的,您必须处理Access的配置问题。

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

2.重新考虑您希望在每次调用时返回DataSet的原因。这是关于ADO.NET DataSet的缺点的优秀文章: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.

3.如果没有先评估或参数化,请不要执行任何给定的字符串。我理解创建一个层来处理所有数据库查询的愿望,你应该为这个想法鼓掌。如上所述,代码将创建一个更危险的情况,因为它不检查任何格式错误的语句等。所以建议不要执行传递给您的任何字符串。也许这个网络服务只在你的局域网中,也许你相信所有的电话都是诚实的,非破坏性的。我们可能会认为所有内部活动都很好,但是一旦员工变坏,你就会开启内部破坏的可能性。

What happens when someone calls your webservice like this:

当有人像你这样调用你的web服务时会发生什么:

ExecuteQuery("DELETE FROM Customers")

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

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

4.为希望图层公开的每个函数创建一个新的web方法。例如,而不是客户端调用

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)

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.

5.将Access连接字符串放入app.config文件中。参考System.Configuration并使用ConfigurationManager。每次调用查询时加载它。这有点受到性能影响,但在更改.mdb文件的目录路径或名称时,维护工作量确实为零。

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

#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.

除非你喜欢资源泄漏,否则这样做。

#1