ODBC连接(C#)上的GetSchema("Databases") [英] GetSchema("Databases") on ODBC connection (C#)

问题描述

我正在测试C#中的各种数据库连接方法.特别是，我正在测试SqlConnection和OdbcConnection类.我的数据库是SQLServer Express(.\SQLEXPRESS).除列出服务器上的可用数据库外，两者都运行良好.

I am testing various DB connection methods in C#. In particular, I am testing SqlConnection and OdbcConnection classes; my DB is SQLServer Express (.\SQLEXPRESS). Both are working reasonably well, except in listing available databases on the server.

在测试代码中，我使用一个"generic" DbConnection对象和一个简单的工厂来创建特定的SqlConnetion和OdbcConnetion子类的实例(它们都从DbConnection派生):

In my test code I use a "generic" DbConnection object and a simple factory to create an instance of specific SqlConnetion and OdbcConnetion subclasses (they both derive from DbConnection):

DbConnection connection;
switch (connection_type)
{
case DbConnectionType.DBCONN_MSSQL:
   connection = new SqlConnection(...sql connection string...);
   break;
case DbConnectionType.DBCONN_ODBC:
  connection = new OdbcConnection(...odbc connection string...);
  break;
}

除非我尝试获取服务器上的数据库列表，否则该技巧似乎效果很好:

The trick seems to work well except when I try to get the list of databases on the server:

DataTable databases = connection.GetSchema("Databases");
foreach (DataRow database in databases.Rows)
{
   String databaseName = database["database_name"] as String;
   Console.WriteLine(databaseName);
}

当"connection"是OdbcConnection时(并且，请注意，数据库是相同的)，我得到一个异常，说"Databases" key was not found.我列出了GetSchema()公开的所有键，而ODBC版本仅返回SQLServer版本公开的项的子集.我找不到有关此特定问题的任何提示.它是有记录的/预期的行为吗?我在做错什么吗?

When "connection" is an OdbcConnection (and, note, the database is the same), I get an exception saying that "Databases" key was not found. I listed all the keys exposed by GetSchema(), and the ODBC version returns only a subset of the items exposed by the SQLServer version. I couldn't find any hint about this specific problem. Is it a documented/expected behaviour? Am I doing something wrong?

注意:这是我如何建立ODBC连接字符串:

NOTE: here how I build the ODBC connection string:

   OdbcConnectionStringBuilder builder;

   builder = new OdbcConnectionStringBuilder();
   builder.Driver = "SQL Server";
   builder.Add("Server", ".\\SQLEXPRESS");
   builder.Add("Uid", "");
   builder.Add("Pwd", ""); // Using current user
   builder.Add("Integrated Security", "SSPI");

   connection = new OdbcConnection(builder.ConnectionString);

推荐答案

这是有据可查/预期的行为吗?

Is it a documented/expected behaviour?

是的.请参阅检索数据库架构信息

我做错什么了吗?

Am I doing something wrong?

如果您的目标是以与提供者无关的方式读取SQL Server元数据，则可以.您应该直接查询SQL Server目录视图. sys.databases，sys.tables等.

If your goal is to read SQL Server metadata in a provider-agnostic way, then yes. You should query the SQL Server catalog views directly. sys.databases, sys.tables, etc.

这篇关于ODBC连接(C#)上的GetSchema("Databases")的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！