SQLGetInfo -如何使用此函数

Question

我开发了一个c#应用程序，它连接到许多类型的数据库服务器，如Sql,Oracle,Mysql等，使用ODBC建立连接。

我需要找到建立连接的服务器类型(DBMS类型)，因为用户只输入了DSN名称。

经过几个小时的浏览，我发现我的需求只有通过这个函数SQLGetInfo才能实现。但是我不知道如何在c#中处理这个问题。

甚至检查了这个Link1。

从上面的链接中，我发现ODBC API只给出了数据库名称和数据源名称。但我需要找到数据库类型，如是否它是一个SQL连接或Oracle连接或mySqlConnection。

是否有可能从DSN名称获得DBMS类型..?

注意:我不想从注册表设置中读取它。我试过这个，它工作，但我有许可问题…!

Answer 1

这绝对会成功。这是我的实现挂钩OdbcConnection GetInfoStringUnhandled函数。上帝我们喜欢反思，我知道我是传奇;)

public enum SQL_INFO
{
    DATA_SOURCE_NAME,
    DRIVER_NAME,
    DRIVER_VER,
    ODBC_VER,
    SERVER_NAME,
    SEARCH_PATTERN_ESCAPE,
    DBMS_NAME,
    DBMS_VER,
    IDENTIFIER_CASE,
    IDENTIFIER_QUOTE_CHAR,
    CATALOG_NAME_SEPARATOR,
    DRIVER_ODBC_VER,
    GROUP_BY,
    KEYWORDS,
    ORDER_BY_COLUMNS_IN_SELECT,
    QUOTED_IDENTIFIER_CASE,
    SQL_OJ_CAPABILITIES_30,
    SQL_SQL92_RELATIONAL_JOIN_OPERATORS,
    SQL_OJ_CAPABILITIES_20
}
public static string GetInfoStringUnhandled(OdbcConnection ocn, SQL_INFO info)
{
    MethodInfo GetInfoStringUnhandled = ocn.GetType().GetMethods(BindingFlags.NonPublic | BindingFlags.Instance).First(c => c.Name == "GetInfoStringUnhandled");
    ParameterInfo SQL_INFO =
        GetInfoStringUnhandled.GetParameters()
            .First(c => (c.ParameterType.ToString() == "System.Data.Odbc.ODBC32+SQL_INFO"));
    Array EnumValues = SQL_INFO.ParameterType.GetEnumValues();
    foreach (var enumval in EnumValues) {
        if (enumval.ToString() == info.ToString()) {
            return Convert.ToString(GetInfoStringUnhandled.Invoke(ocn, new object[] { enumval }));
        }
    }
    return string.Empty;
}
private static void Main(string[] args)
{
    OdbcConnection ocn = new OdbcConnection("DSN=GENESIS");
    ocn.Open();
    Console.WriteLine(GetInfoStringUnhandled(ocn, SQL_INFO.DBMS_VER) + " " +
                      GetInfoStringUnhandled(ocn, SQL_INFO.DBMS_NAME));
}

我找到的最好的文档，解释了47种使用SQLGetInfo的可能性在这里https://mariadb.com/kb/en/sql-99/sqlgetinfo/

然而，OdbcConnection只是集成了一个有19种可能性的枚举。下面是System.Data.Odbc.ODBC32的SQL_INFO的反汇编enum:

public enum SQL_INFO : ushort
{
    DATA_SOURCE_NAME = (ushort)2,
    DRIVER_NAME = (ushort)6,
    DRIVER_VER = (ushort)7,
    ODBC_VER = (ushort)10,
    SERVER_NAME = (ushort)13,
    SEARCH_PATTERN_ESCAPE = (ushort)14,
    DBMS_NAME = (ushort)17,
    DBMS_VER = (ushort)18,
    IDENTIFIER_CASE = (ushort)28,
    IDENTIFIER_QUOTE_CHAR = (ushort)29,
    CATALOG_NAME_SEPARATOR = (ushort)41,
    DRIVER_ODBC_VER = (ushort)77,
    GROUP_BY = (ushort)88,
    KEYWORDS = (ushort)89,
    ORDER_BY_COLUMNS_IN_SELECT = (ushort)90,
    QUOTED_IDENTIFIER_CASE = (ushort)93,
    SQL_OJ_CAPABILITIES_30 = (ushort)115,
    SQL_SQL92_RELATIONAL_JOIN_OPERATORS = (ushort)161,
    SQL_OJ_CAPABILITIES_20 = (ushort)65003
}

如您所见，您可以简单地调用GetInfoStringUnhandled方法，并使用您需要的信息的(短)强制转换整数。下面是一个示例:

public static string GetInfoStringUnhandled(OdbcConnection ocn, ushort info)
{
    MethodInfo GetInfoStringUnhandled = ocn.GetType().GetMethods(BindingFlags.NonPublic | BindingFlags.Instance).First(c => c.Name == "GetInfoStringUnhandled");
    return Convert.ToString(GetInfoStringUnhandled.Invoke(ocn, new object[] { (ushort)info }));            
}
public static void Main(string[] args)
{
    OdbcConnection ocn = new OdbcConnection("DSN=GENESIS");
    ocn.Open();
    Console.WriteLine(GetInfoStringUnhandled(ocn, (ushort)10003)); //SQL_CATALOG_NAME returns Y
}

Answer 2

短答:没有。努力寻找托管的对等物。没有文档说明获取此句柄的方法。

长答: SQLGetInfo函数的InfoType参数有47个可能的值。Ref.你可以像下面这样为带引号的标识符检索一个正则表达式模式:

DataTable dt = connection.GetSchema(DbMetaDataCollectionNames.DataSourceInformation); 
string quotedIdentifierPattern = (string)dt.Rows[0][DbMetaDataColumnNames.QuotedIdentifierPattern];

这将允许您识别但不产生带引号的标识符。但是，可以安全地假设引号字符确实是一个字符，因此您可以简单地执行以下操作来获取它:

Regex.Unescape(quotedIdentifierPattern)[0];

(.Unescape()是必需的，因为引号字符可能是特殊的)

SQLInfo()的大多数其他用途也可以类似地用. getschema()解决。如果我建议你一定要使用SQLGetInfo()使用私有方法.GetInfoInt16Unhandled(), .GetInfoInt32Unhandled()..GetInfoStringUnhandled()对OdbcConnection的反射。这是但可能会在没有警告的情况下中断。

你可以通过私有的。connectionhandle获取内部句柄成员，但这同样容易被破坏，而且远不方便(因为你还必须编写所有非托管的互操作代码)。

使用ILSpy或Reflector获得更多关于实现的细节。逆向工程的内部可以在许多案例指向一个完全托管的解决方案。Ref .

---

或构建在这个MSDN示例代码上，通过不同的命令检测版本，例如

MySQL:,,,,"选择版本()";
Oracle:,,,,"SELECT @@version， @@version_comment FROM dual";
:状态"置疑","选择@@version";

MSDN示例代码:

using System;
using System.Data;
namespace IDbConnectionSample {
   class Program {
      static void Main(string[] args) {
         IDbConnection connection;
         // First use a SqlClient connection
         connection = new System.Data.SqlClient.SqlConnection(@"Server=(localdb)V11.0");
         Console.WriteLine("SqlClientrn{0}", GetServerVersion(connection));
         connection = new System.Data.SqlClient.SqlConnection(@"Server=(local);Integrated Security=true");
         Console.WriteLine("SqlClientrn{0}", GetServerVersion(connection));
         // Call the same method using ODBC 
         // NOTE: LocalDB requires the SQL Server 2012 Native Client ODBC driver
         connection = new System.Data.Odbc.OdbcConnection(@"Driver={SQL Server Native Client 11.0};Server=(localdb)v11.0");
         Console.WriteLine("ODBCrn{0}", GetServerVersion(connection));
         connection = new System.Data.Odbc.OdbcConnection(@"Driver={SQL Server Native Client 11.0};Server=(local);Trusted_Connection=yes");
         Console.WriteLine("ODBCrn{0}", GetServerVersion(connection));
         // Call the same method using OLE DB
         connection = new System.Data.OleDb.OleDbConnection(@"Provider=SQLNCLI11;Server=(localdb)v11.0;Trusted_Connection=yes;");
         Console.WriteLine("OLE DBrn{0}", GetServerVersion(connection));
         connection = new System.Data.OleDb.OleDbConnection(@"Provider=SQLNCLI11;Server=(local);Trusted_Connection=yes;");
         Console.WriteLine("OLE DBrn{0}", GetServerVersion(connection));
         }
      public static string GetServerVersion(IDbConnection connection) {
         // Ensure that the connection is opened (otherwise executing the command will fail)
         ConnectionState originalState = connection.State;
         if (originalState != ConnectionState.Open)
            connection.Open();
         try {
            // Create a command to get the server version 
            IDbCommand command = connection.CreateCommand();
            command.CommandText = "SELECT @@version"; //<- HERE  
            //try out the different commands by passing the CommandText as a parameter
            return (string)command.ExecuteScalar();
         }
         finally {
            // Close the connection if that's how we got it 
            if (originalState == ConnectionState.Closed)
               connection.Close();
         }
      }
   }
}

---

或您可以按照其他人的建议做一些更优雅的事情。

注意:这是复制/粘贴@FabianStern回答的工作-归功于作者。我只是让它不那么程序化，更正统，因为我无法忍受层叠的Try-Catch’s):

protected static DBType GetDBType(string odbcConnStr)
{
 var dbType = DBType.UNSUPPORTED;
 try
 {
  using (var cn = new OdbcConnection(odbcConnStr))
  {
    if (cn.State != ConnectionState.Open) cn.Open();
    dbType = GetDbType(cn, dbType)
    if (dbType > 0) return dbType;
    var sqlVersionQuery = "SELECT version()"; 
    dbType = GetDbType(cn, sqlVersionQuery, DBType.MYSQL)
    if (dbType > 0) return dbType;
    sqlVersionQuery = "SELECT @@version, @@version_comment FROM dual"; 
    dbType = GetDbType(cn, sqlVersionQuery, DBType.Oracle)
    if (dbType > 0) return dbType;
    sqlVersionQuery = "SELECT @@version"; 
    dbType = GetDbType(cn, sqlVersionQuery, DBType.MSSQL)
    if (dbType > 0) return dbType;
  }
 }
 catch(Exception connEx) { }
 return dbType;
}
public enum DBType
{
    UNSUPPORTED = 0,
    MYSQL = 1,
    ORACLE = 2,
    MSSQL = 3,
    JET = 4
}
private static DBType GetDBType(OdbcConnection cn, DBType dbType)
{
  try
  {
    if (cn.Driver == "odbcjt32.dll") dbType = DBType.JET;
  }
  catch(Exception ex) { }
  return dbType;
}
private static DBType GetDbType(OdbcConnection cn, string sqlVersionQuery, DBType dbType)
{
  try
  {
  using (var cmd = cn.CreateCommand()) {
  cmd.CommandText = sqlVersionQuery;
    try
    {
       using (var reader = cmd.ExecuteReader()) 
       {
           if (reader.HasRows) return dbType;
       }
    }
    catch (Exception ex) { }
  }}
  catch (Exception cmdEx) { }           
  }
 return dbType;
}

Answer 3

您是否尝试解析OdbcConnection的.Driver属性?它将显示用于连接的数据库包装程序驱动程序。您也可以在注册表HKEY_LOCAL_MACHINESOFTWAREMicrosoftODBC

中找到这些映射。

有时它只是一个。dll名称(例如Microsoft Excel Driver)，但它可以给你一个提示。

您还可以执行数据库特定的命令来从服务器获取数据库版本，如下所示:

   static void Main(string[] args)
    {
        var cn = new OdbcConnection("DSN=mysql1");
        Console.WriteLine("DBType: {0}", GetDbType(cn));
        Console.Read();
    }
    public enum DbType
    {
        UNSUPPORTED = 0,
        MYSQL = 1,
        ORACLE = 2,
        MSSQL = 3,
        POSTGRESQL = 4,
        JET = 5
    }
    public static DbType GetDbType(OdbcConnection cn)
    {
        DbType t = DbType.UNSUPPORTED;
            try
            {
                if (cn.State != ConnectionState.Open) cn.Open();
                if (cn.Driver == "odbcjt32.dll")
                {
                    return DbType.JET;
                }
                var cmd = cn.CreateCommand();
                string outstring = "";
                cmd.CommandText = "SELECT * FROM v$version";
                try
                {
                    var reader = cmd.ExecuteReader();
                    if (reader.HasRows)
                    {
                        reader.Read();
                        outstring = String.Format("{0}", reader.GetString(0));
                    }
                }
                catch (Exception)
                {
                    cmd = cn.CreateCommand();
                    cmd.CommandText = "SELECT @@version, @@version_comment FROM dual";
                    try
                    {
                        var reader = cmd.ExecuteReader();
                        if (reader.HasRows)
                        {
                            reader.Read();
                            outstring = String.Format("{0} {1}", reader.GetString(0), reader.GetString(1));
                        }
                    }
                    catch (Exception)
                    {
                        cmd = cn.CreateCommand();
                        cmd.CommandText = "SELECT @@version";
                        try
                        {
                            var reader = cmd.ExecuteReader();
                            if (reader.HasRows)
                            {
                                reader.Read();
                                outstring = String.Format("{0}", reader.GetString(0));
                            }
                        }
                        catch (Exception)
                        {
                            cmd = cn.CreateCommand();
                            cmd.CommandText = "SELECT version()";
                            try
                            {
                                var reader = cmd.ExecuteReader();
                                if (reader.HasRows)
                                {
                                    reader.Read();
                                    outstring = String.Format("{0}", reader.GetString(0));
                                }
                            }
                            catch (Exception)
                            {
                            }
                        }
                    }
                }
                outstring = outstring.ToUpper();
                if (outstring.Contains("MYSQL"))
                {
                    t = DbType.MYSQL;
                }
                else if (outstring.Contains("ORACLE"))
                {
                    t = DbType.ORACLE;
                }
                else if (outstring.Contains("SQL SERVER"))
                {
                    t = DbType.MSSQL;
                }
                else if (outstring.Contains("POSTGRESQL"))
                {
                    t = DbType.POSTGRESQL;
                }

            }
            catch (Exception E)
            {
            }
        return t;
    }