在.net平台中 获取ODBC数据对象的列表

虽然.net平台又专门针对ODBC的驱动System.Data.Odbc,但是却没有像System.Data.Oledb中提供的

GetOledbSchemaTable的函数，为此，利用ODBC32.DLL,本人给大家写了一个专门的类.

using System;
using System.Runtime.InteropServices;
namespace SQLDialog.Data
{
 /// <summary>
 /// ODBCDriver 获取ODBC数据对象的列表
 /// 作者:徐侠君
 /// 创建时间:2004-08-02
 /// 完成时间:2004-08-02 18:40
 /// </summary>
 public class ODBCDriver
 {
  public ODBCDriver()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
  }

  private const short MAX_DATA_BUFFER = 255;
  private const short SQL_SUCCESS = 0;
  private const short SQL_SUCCESS_WITH_INFO = 1;
  private const short SQL_ERROR = -1;
  private const short SQL_NO_DATA_FOUND = 100;
  private const short SQL_CLOSE = 0;
  private const short SQL_DROP = 1;
  private const short SQL_CHAR = 1;
  private const short SQL_NUMERIC = 2;
  private const short SQL_DECIMAL = 3;
  private const short SQL_INTEGER = 4;
  private const short SQL_SMALLINT = 5;
  private const short SQL_FLOAT = 6;
  private const short SQL_REAL = 7;
  private const short SQL_DOUBLE = 8;
  private const short SQL_VARCHAR = 12;
  private const short SQL_DATA_SOURCE_NAME = 6;
  private const short SQL_USER_NAME = 8;
  private const short SQL_HANDLE_ENV = 1;
  private const short SQL_HANDLE_DBC = 2;   

  /* 由于暂时没有使用，注释
  [DllImport("odbc32.dll", EntryPoint="SQLAllocConnect")]
  private static extern short SQLAllocConnect (
   int env,
   ref int ldbc
  );
  [DllImport("odbc32.dll", EntryPoint="SQLCloseCursor")]
  private static extern short SQLCloseCursor (
   int lStmt
  );
  [DllImport("odbc32.dll", EntryPoint="SQLAllocEnv")]
  private static extern short SQLAllocEnv (
   ref int env
  );
  [DllImport("odbc32.dll", EntryPoint="SQLDescribeCol")]
  private static extern short SQLDescribeCol (
   int lStmt,
   int colnum,
   int colname,
   int Buflen,
   ref int colnamelen,
   ref int dtype,
   ref int dl,
   ref int ds,
   ref int n
  );
  [DllImport("odbc32.dll", EntryPoint="SQLDrivers")]
  private static extern short SQLDrivers (
   int env,
   int dir,
   int descrip,
   int bflen,
   ref int descriplen,
   int attrib,
   int bfattrlen,
   ref int attriblen
  );
  [DllImport("odbc32.dll", EntryPoint="SQLError")]
  private static extern short SQLError (
   int env,
   int ldbc,
   int lStmt,
   int SQLState,
   ref int NativeError,
   int Buffer,
   int Buflen,
   ref int Outlen
  );
  [DllImport("odbc32.dll", EntryPoint="SQLExecDirect")]
  private static extern short SQLExecDirect (
   int lStmt,
   long sqlString,
   int sqlstrlen
  );
  [DllImport("odbc32.dll", EntryPoint="SQLFreeConnect")]
  private static extern short SQLFreeConnect (
   int ldbc
  );

  [DllImport("odbc32.dll", EntryPoint="SQLFreeEnv")]
  private static extern short SQLFreeEnv (
   int env
  );
  [DllImport("odbc32.dll", EntryPoint="SQLGetInfo")]
  private static extern short SQLGetInfo (
   int ldbc,
   int hWnd,
   string szInfo,
   int cbInfoMax,
   ref int cbInfoOut
  );
  [DllImport("odbc32.dll", EntryPoint="SQLNumResultCols")]
  private static extern short SQLNumResultCols (
   int lStmt,
   ref int NumCols
  );
  */

  /// <summary>
  /// 分配一个运行环境的一个句柄，在与ODBC连接之前必须要做的
  /// </summary>
  /// <param name="hType"></param>
  /// <param name="inputHandle"></param>
  /// <param name="outputHandle"></param>
  /// <returns></returns>
  [DllImport("odbc32.dll")]
  private static extern short SQLAllocHandle(short hType,
   IntPtr inputHandle, out IntPtr outputHandle);
  /// <summary>
  /// 释放一个运行环境的一个句柄
  /// </summary>
  /// <param name="hType"></param>
  /// <param name="handle"></param>
  /// <returns></returns>
  [DllImport("odbc32.dll")]
  private static extern short SQLFreeHandle(short hType, IntPtr handle); 
  
  /// <summary>
  /// 设置环境属性
  /// </summary>
  /// <param name="henv"></param>
  /// <param name="attribute"></param>
  /// <param name="valuePtr"></param>
  /// <param name="strLength"></param>
  /// <returns></returns>
  [DllImport("odbc32.dll")]
  private static extern short SQLSetEnvAttr(IntPtr henv,
   int attribute, IntPtr valuePtr, int strLength);
  
  /// <summary>
  /// 分配一个执行语句的句柄
  /// </summary>
  /// <param name="ldbc"></param>
  /// <param name="lStmt"></param>
  /// <returns></returns>
  [DllImport("odbc32.dll", EntryPoint="SQLAllocStmt")]
  private static extern short SQLAllocStmt (
   System.IntPtr ldbc,
   out System.IntPtr lStmt
  );
  /// <summary>
  /// 取得与数据库的连接
  /// </summary>
  /// <param name="ldbc"></param>
  /// <param name="Server"></param>
  /// <param name="serverlen"></param>
  /// <param name="uid"></param>
  /// <param name="uidlen"></param>
  /// <param name="pwd"></param>
  /// <param name="pwdlen"></param>
  /// <returns></returns>
  [DllImport("odbc32.dll", EntryPoint="SQLConnect")]
  private static extern short SQLConnect (
   System.IntPtr ldbc,
   string Server,
   int serverlen,
   string uid,
   int uidlen,
   string pwd,
   int pwdlen
  );  
  
  /// <summary>
  ///  断开与数据库的连接
  /// </summary>
  /// <param name="ldbc"></param>
  /// <returns></returns>
  [DllImport("odbc32.dll", EntryPoint="SQLDisconnect")]
  private static extern short SQLDisconnect (
   System.IntPtr ldbc
  );
  
  /// <summary>
  /// 根据ODBC的连接字符串，取得与ODBC的连接
  /// </summary>
  /// <param name="hconn"></param>
  /// <param name="WindowHandle"></param>
  /// <param name="inString"></param>
  /// <param name="inStringLength"></param>
  /// <param name="outString"></param>
  /// <param name="outStringLength"></param>
  /// <param name="outLengthNeeded"></param>
  /// <param name="f"></param>
  /// <returns></returns>
  [DllImport("odbc32.dll", EntryPoint="SQLDriverConnect")]
  private static extern short SQLDriverConnect (
   IntPtr hconn, IntPtr WindowHandle,string inString, 
   int inStringLength, System.Text.StringBuilder outString, int outStringLength,
   out short outLengthNeeded,
   int f
  );

  
  /// <summary>
  /// 滚动游标
  /// </summary>
  /// <param name="lStmt">语句句柄</param>
  /// <returns></returns>
  [DllImport("odbc32.dll", EntryPoint="SQLFetch")]
  private static extern int SQLFetch (
   System.IntPtr lStmt
  );
  
  /// <summary>
  /// 释放分配的语句句柄
  /// </summary>
  /// <param name="lStmt"></param>
  /// <param name="EndOption"></param>
  /// <returns></returns>
  [DllImport("odbc32.dll", EntryPoint="SQLFreeStmt")]
  private static extern short SQLFreeStmt (
   System.IntPtr lStmt,
   int EndOption
  );
  /// <summary>
  /// 获取行数据
  /// </summary>
  /// <param name="lStmt">语句句柄</param>
  /// <param name="ColumnNumber">列索引</param>
  /// <param name="TargetType">列的数据类型</param>
  /// <param name="TargetValuePtr">目标值存放地址</param>
  /// <param name="dwbuflen">目标值缓冲区大小</param>
  /// <param name="lpcbout">实际取出的数据大小</param>
  /// <returns></returns>
  [DllImport("odbc32.dll", EntryPoint="SQLGetData")]
  private static extern short SQLGetData (
   System.IntPtr lStmt,  
   short ColumnNumber,   
   short TargetType,    
   System.Text.StringBuilder TargetValuePtr,   
   int dwbuflen,    
   out int lpcbout    
  );  

  
  /// <summary>
  ///
  /// </summary>
  /// <param name="lStmt">//语句句柄</param>
  /// <param name="CatalogName">//表的分类</param>
  /// <param name="CatalogNameLength">//长度</param>
  /// <param name="SchemaName">//方案名称</param>
  /// <param name="SchemaNameLength">//方案名称的长度</param>
  /// <param name="TableName">/表名称</param>
  /// <param name="TableNameLen">//表名称的长度</param>
  /// <param name="TableType">表类型</param>
  /// <param name="TableTypeLen">TableType字符串的长度</param>
  /// <returns></returns>
  [DllImport("odbc32.dll", EntryPoint="SQLTables")]
  private static extern short SQLTables (
   System.IntPtr lStmt, 
   string  CatalogName, 
   short CatalogNameLength,
   string SchemaName,  
   short SchemaNameLength,  
   string TableName,  
   short TableNameLen,  
   string     TableType,
   int TableTypeLen   
  );  

  
  /// <summary>
  /// 连接到ODBC,取数据对象的列表
  /// </summary>
  /// <param name="sConn"></param>
  public TStringList GetDBObject(string sConn,SQLDialog.EnumObjectType OT)
  {
   System.IntPtr henv=IntPtr.Zero;
   TStringList list=new TStringList();
   System.IntPtr hdbc=IntPtr.Zero;
   short retcode;
   retcode = SQLAllocHandle(SQL_HANDLE_ENV, System.IntPtr.Zero, out henv);
   #region
   if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
   {
    retcode = SQLSetEnvAttr(henv, 200, (IntPtr)3, 0);
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
    {
     retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, out hdbc);
     if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
     {
      #region 打开数据库连接
      System.Text.StringBuilder OutStr=new System.Text.StringBuilder(255);
      short lenNeeded = 0;
      retcode =SQLDriverConnect(hdbc,System.IntPtr.Zero,sConn,sConn.Length,OutStr,OutStr.Capacity,out lenNeeded,1);
      if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
      {
       System.IntPtr stHandle=IntPtr.Zero;
       #region 分配一个语句句柄
       retcode=SQLAllocStmt(hdbc,out stHandle);
       if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
       {
        //取表
        string TableType;
        if(OT==SQLDialog.EnumObjectType.None)
         TableType="";
        else
         TableType=OT.ToString();
        retcode=SQLTables(stHandle,null,0, null,0, null,0,TableType,TableType.Length);
        if(retcode==SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
        {
         //取记录
         System.Text.StringBuilder TName;
         int sqlstatus;
         
         
         #region 游标取数据
         while(true)
         {
          sqlstatus=SQLFetch(stHandle);
          if(sqlstatus==65535 || sqlstatus==SQL_ERROR ||sqlstatus==SQL_NO_DATA_FOUND )
          {

           break;
          }
          else
          {
           //
           //取数据
           int len;
           TName=new System.Text.StringBuilder(1024);
           SQLGetData(stHandle,3,SQL_CHAR,TName,MAX_DATA_BUFFER,out len);
           
           if(TName.ToString()=="")
            break;
           else
            list.Add(TName.ToString());            
          }
         }
         #endregion 结束游标取数据
        }
        SQLFreeStmt(stHandle,0);
       }
       #endregion
       SQLDisconnect(hdbc);
      }
      #endregion
      SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
     }
    }
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
   }
   #endregion
   return list;
  }
 }
}