如何在C＃中使用OleDB列出MS Access文件中的所有查询？

I have an Access 2003 file that contains 200 queries, and I want to print out their representation in SQL. I can use Design View to look at each query and cut and paste it to a file, but that's tedious. Also, I may have to do this again on other Access files, so I definitely want to write a program to do it.

我有一个包含200个查询的Access 2003文件,我想在SQL中打印出它们的表示形式。我可以使用设计视图来查看每个查询并将其剪切并粘贴到文件中,但这很乏味。另外,我可能不得不在其他Access文件上再次执行此操作,因此我绝对想编写一个程序来执行此操作。

Where are queries stored an Access db? I can't find anything saying how to get at them. I'm unfamiliar with Access, so I'd appreciate any pointers. Thanks!

查询在哪里存储Access数据库?我找不到任何说如何得到他们的东西。我对Access不熟悉,所以我很感激任何指针。谢谢!

4 个解决方案

#1

Procedures are what you're looking for:

程序是你正在寻找的:

OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();

DataTable queries = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, null);

conn.Close();

This will give you a DataTable with the following columns in it (among others):

这将为您提供一个DataTable,其中包含以下列(以及其他列):

PROCEDURE_NAME: Name of the query

PROCEDURE_NAME:查询的名称

PROCEDURE_DEFINITION: SQL definition

PROCEDURE_DEFINITION:SQL定义

So you can loop through the table like so:

所以你可以这样循环遍历表:

foreach(DataRow row in queries.Rows)
{
    // Do what you want with the values here
    queryName = row["PROCEDURE_NAME"].ToString();
    sql = row["PROCEDURE_DEFINITION"].ToString();
}

#2

you can put this together using the OleDbConnection's GetSchema method along with what Remou posted with regards to the ADO Schemas

您可以使用OleDbConnection的GetSchema方法以及Remou针对ADO架构发布的内容将它们放在一起

oops forgot link: MSDN

哎呀忘了链接:MSDN

#3

In case you wanted to do a quick query by hand.

如果您想手动快速查询。

SELECT MSysObjects.Name
FROM MSysObjects
WHERE type = 5

#4

-2

Not in C#, but may be a good place to start:

不是在C#中,但可能是一个好的开始:

http://www.datastrat.com/Code/DocDatabase.txt

#1