虽然.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;
}
}
}