在ADO.NET中使用ODBC调用预定义的Access查询 [英] Calling a pre-defined Access query using ODBC in ADO.NET

问题描述

我正在使用c#和OdbcConnection连接到Access数据库.在数据库内部，有一个我要运行的预定义查询(如Sql Server中存储的proc).对于以前的基于COM的ADO，这曾经很容易实现，但在ADO.net中似乎不起作用

I am using c# and OdbcConnection to connect to an Access database. Inside the database there is a pre-defined query that I want to run (like a stored proc in Sql Server). This used to be dead easy with the old COM-based ADO but it doesn't seem to work in ADO.net

OdbcConnection conn = AccessConnect.Connect();
var cmd = conn.CreateCommand();
cmd.CommandText = @"MyAccessQuery;";
cmd.CommandType = CommandType.StoredProcedure;
var da = new OdbcDataAdapter(cmd);
var ds = new DataSet();
da.Fill(ds);

有没有解决的办法，还是我必须用C#代码复制Access查询?

Is there a way round it or am I going to have to duplicate my Access query in C# code?

推荐答案

Access ODBC(和OLEDB)接口将Access中保存的查询公开为视图或存储过程.它们的公开方式决定了外部应用程序可以使用它们的方式.

The Access ODBC (and OLEDB) interfaces expose saved queries in Access as either Views or Stored Procedures. How they are exposed determines the way they can be used by an external application.

在Access中保存的不不使用参数的SELECT查询显示为视图，因此它们可以像表一样使用，例如

Saved SELECT queries in Access that do not use PARAMETERS are exposed as Views, so they can be used like a table, e.g.

string sql = "SELECT * FROM mySavedSelectQuery WHERE id <= 3";
using (var cmd = new OdbcCommand(sql, con))
{
    cmd.CommandType = System.Data.CommandType.Text;
    using (var da = new OdbcDataAdapter(cmd))
    {
        var dt = new System.Data.DataTable();
        da.Fill(dt);
        Console.WriteLine("DataTable contains {0} row(s)", dt.Rows.Count);
    }
}

Access中其他类型的已保存查询作为存储过程公开，因此需要使用ODBC {CALL ...}语法来调用它们，如下所示:

Other types of saved queries in Access are exposed as Stored Procedures, so they need to be called using the ODBC {CALL ...} syntax, like so:

string sql = "{CALL mySavedParameterQuery (?)}";
using (var cmd = new OdbcCommand(sql, con))
{
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    // set parameter values (if any) in the order that they appear 
    //     in the PARAMETERS list of the saved query
    cmd.Parameters.Add("?", OdbcType.Int).Value = 3;
    using (var da = new OdbcDataAdapter(cmd))
    {
        var dt = new System.Data.DataTable();
        da.Fill(dt);
        Console.WriteLine("DataTable contains {0} row(s)", dt.Rows.Count);
    }
}

请注意，在上述两种情况下，与从外部应用程序(C#，VB.NET，Java等)对Access数据库进行的任何查询一样，都存在一些限制.例如，某些Access内置函数可能不可用，并且保存的调用用户定义函数(用VBA编写)的Access查询将无法通过直接的ODBC或OLEDB连接进行工作.

Note that in both of the above cases, as with any query against an Access database from an external application (C#, VB.NET, Java, etc.), there are some restrictions. For example, some Access built-in functions may not be available, and saved Access queries that call user-defined functions (written in VBA) will not work from a direct ODBC or OLEDB connection.

这篇关于在ADO.NET中使用ODBC调用预定义的Access查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！