创建的参数化查询未在数据源 Visual Studio 10 中显示并访问 [英] Created Parameterized query not showing in datasource visual studio 10 and access

问题描述

我正在尝试复制 northwind 数据库，但是当我创建参数查询 vw_CustomerOrderYear 时，查询未显示在我的数据源中.
我不知道我错过了什么..

I'm trying to replicate northwind database but when I created parameter query vw_CustomerOrderYear, the query is not showing in my datasource.
I do not know what I'm missing..

我的参数查询vw_CustomerOrderYear是:

PARAMETERS OrderYear Long;

SELECT DISTINCT Customer.ID, Customer.Name, Customer.City, Customer.Country,
Year([Order].OrderDate) AS OrderYear
FROM Customer INNER JOIN [Order] ON Customer.ID=Order.CustomerId
WHERE (((Year(Order.OrderDate))=[OrderYear])) Or ((([OrderYear]) Is Null));

如何在我的 SomeNameDataSet 下获取此查询?

How can I get this query under my SomeNameDataSet ?

推荐答案

当您在 Microsoft Access 应用程序本身 (MSACCESS.EXE) 中创建参数查询"时，即其 SQL 代码看起来像......

When you create a "parameter query" in the Microsoft Access application itself (MSACCESS.EXE), i.e., one whose SQL code looks like ...

PARAMETERS ... ; SELECT ... FROM ...

...它被 ACE/Jet DAO(数据访问对象)视为查询"，这是 MSACCESS 用于与 Access 数据库引擎(ACE 或 Jet)进行通信的技术.您可以在 Access 中运行查询并提示输入参数值，也可以在 VBA 中使用 DAO QueryDef 对象并将参数值传递给该 QueryDef 对象的 .Parameters 集合.

... it is considered a "Query" by ACE/Jet DAO (Data Access Objects), which is the technology that MSACCESS uses to communicate with the Access Database Engine (ACE or Jet). You can run the query in Access and get prompted for the parameter values, or you can use a DAO QueryDef object in VBA and pass the parameter values to the .Parameters collection of that QueryDef object.

.NET 项目中的数据集"数据源行为不同，因为它们使用 System.Data.OleDb，而 ACE/Jet OLEDB 提供程序不会将这些查询归类为视图".相反，它将它们归类为[存储的]过程".所以，如果你要打开一个 OleDbConnection 并做

"Dataset" Data Sources in .NET projects behave differently because they use System.Data.OleDb, and the ACE/Jet OLEDB provider does not classify those queries as "Views". Instead, it classifies them as "[Stored] Procedures". So, if you were to open an OleDbConnection and do

DataTable dtViews = con.GetSchema("Views")

那么查询将不会显示在结果列表中.这就是为什么在 Visual Studio 的数据源向导的视图"下看不到它的原因.

then the query would not show up in the resulting list. That's why you cannot see it under "Views" in the Data Sources wizard in Visual Studio.

但是，如果你这样做，它确实会显示出来

However, it does show up if you do

DataTable dtProcedures = con.GetSchema("Procedures")

因此，您可以使用现有的 Access参数查询"，将其视为 .NET 代码中的存储过程，例如，

So, you can use the existing Access "parameter query" by treating it like a Stored Procedure in your .NET code, e.g.,

using (var cmd = new OleDbCommand())
{
    cmd.Connection = con;  // an open OleDbConnection to the database
    cmd.CommandText = "vw_CustomerOrderYear";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("OrderYear", OleDbType.Integer).Value = 2014;
    using (OleDbDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            Console.WriteLine(rdr[0]);
        }
    }
}

这篇关于创建的参数化查询未在数据源 Visual Studio 10 中显示并访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！