• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 539
  • Last Modified:

ODBC tables and fields

How can i get list of tables and fields from generic ODBC connection / adpter ? (not to Mssql DB)
0
asi
Asked:
asi
  • 3
  • 3
1 Solution
 
Bob LearnedCommented:
1) What version of .NET do you have (2005 or 2003)?

2) Are you working with a DSN?

3) Does your DSN require authentication (user, password)?

Bob

0
 
asiAuthor Commented:
2005
DSN
we config the authentication in the DSN / connection string.
0
 
Bob LearnedCommented:
I haven't used this class in a while, so let me know if you can use it (it was converted from VB.NET code):

using System;
using System.Data.Odbc;
using System.Runtime.InteropServices;
using System.Text;
using System.Collections.Generic;
using System.Windows.Forms;

public class OdbcSchema
{
    private const int MAX_BUFFER = 0x1000;
    private static IntPtr m_hEnvironment;
    private static IntPtr m_hDatabaseConnection;
    private static IntPtr m_hStatement;
    private static int m_intRetCode;
    private const int SQL_ERROR = -1;
    private const int SQL_INVALID_HANDLE = -2;
    private const int SQL_NO_DATA_FOUND = 100;
    private const int SQL_SUCCESS = 0;
    private const int SQL_SUCCESS_WITH_INFO = 1;
    private const int SQL_NTS = -3;
    private const int SQL_SQLSTATE_SIZE = 5;
    private const int SQL_MAX_MESSAGE_LENGTH = 512;
    private const int SQL_MAX_DSN_LENGTH = 32;
    private const int SQL_CHAR = 1;
    private const int SQL_NUMERIC = 2;
    private const int SQL_DECIMAL = 3;
    private const int SQL_INTEGER = 4;
    private const int SQL_SMALLINT = 5;
    private const int SQL_FLOAT = 6;
    private const int SQL_REAL = 7;
    private const int SQL_DOUBLE = 8;
    private const int SQL_VARCHAR = 12;
    private const int SQL_TYPE_MIN = 1;
    private const int SQL_TYPE_NULL = 0;
    private const int SQL_TYPE_MAX = 12;
    private const int SQL_C_CHAR = SQL_CHAR;
    private const int SQL_C_LONG = SQL_INTEGER;
    private const int SQL_C_int = SQL_SMALLINT;
    private const int SQL_C_FLOAT = SQL_REAL;
    private const int SQL_C_DOUBLE = SQL_DOUBLE;
    private const int SQL_CLOSE = 0;
    private const int SQL_DROP = 1;
    private const int SQL_UNBIND = 2;
    private const int SQL_RESET_PARAMS = 3;
    private const int SQL_DRIVER_NOPROMPT = 0;
    private const int SQL_DRIVER_COMPLETE = 1;
    private const int SQL_DRIVER_PROMPT = 2;
    private const int SQL_DRIVER_COMPLETE_REQUIRED = 3;

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLError(IntPtr EnvironmentHandle, IntPtr ConnectionHandle, IntPtr StatementHandle, StringBuilder Sqlstate, ref int NativeError, StringBuilder MessageText, int BufferLength, ref int TextLength);

    [DllImport("odbc32.dll")]
    private static extern int SQLAllocEnv(ref IntPtr EnvironmentHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLFreeEnv(IntPtr EnvironmentHandle);

    [DllImport("odbc32.dll")]
    private static extern int SQLAllocConnect(IntPtr EnvironmentHandle, ref IntPtr ConnectionHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    public static extern int SQLDriverConnect(IntPtr hdbc, IntPtr hwnd, string szConnStrIn, int cbConnStrIn, StringBuilder szConnStrOut, int cbConnStrOutMax, ref int pcbConnStrOut, [MarshalAs(UnmanagedType.U2)]
            int fDriverCompletion);

    [DllImport("odbc32.dll")]
    private static extern int SQLAllocStmt(IntPtr ConnectionHandle, ref IntPtr StatementHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLFreeStmt(IntPtr StatementHandle, [MarshalAs(UnmanagedType.U2)]
            int Option);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLDisconnect(IntPtr ConnectionHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLFreeConnect(IntPtr ConnectionHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLTables(IntPtr StatementHandle, string CatalogName,
        int NameLength1, string SchemaName, int NameLength2, string TableName, int NameLength3, string TableType, int NameLength4);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLNumResultCols(IntPtr hstmt, ref int pccol);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLDescribeCol(IntPtr hstmt, int icol, ref string szColName,
        int cbColNameMax, ref int pcbOutLen, ref int pfSqlType, ref int pcbColDef,
        ref int pibScale, ref int pfNullable);

    [DllImport("odbc32.dll")]
    private static extern int SQLFetch(IntPtr StatementHandle);

    [DllImport("odbc32.dll")]
    private static extern int SQLGetData(IntPtr StatementHandle, [MarshalAs(UnmanagedType.U4)]
            int ColumnNumber, int TargetType, StringBuilder TargetValue, int BufferLength, ref int StrLen_or_Ind);

    public static List<string> GetDSNTables(string dsnName)
    {
        return GetDSNTables(dsnName, string.Empty, string.Empty);
    }

    public static List<string> GetDSNTables(string dsnName, string userName, string userPassword)
    {
        List<string> listTables = new List<string>();
        try
        {
            if (InitializeEnvironment())
            {
                string connectString = string.Format("DSN={0};user={1};pwd={2}", dsnName, userName, userPassword);
                if (BeginConnection(connectString) == 0)
                {
                    listTables = GetTables();
                    EndConnection();
                }
                ExitEnvironment();
            }
            return listTables;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        return null;
    }

    private static string GetSQLErrorDesc(IntPtr hEnv, IntPtr hDbc, IntPtr hStmt, int intRetCode)
    {
        int Outlen = 0;
        int Native = 0;
        string strReturn = "";
        const string NO_INFO = "Error - <No error information available>";
        StringBuilder rgbValue1 = new StringBuilder(16);
        StringBuilder rgbValue3 = new StringBuilder(MAX_BUFFER);
        do
        {
            m_intRetCode = SQLError(hEnv, hDbc, hStmt, rgbValue1, ref Native, rgbValue3, MAX_BUFFER, ref Outlen);
            if (m_intRetCode == SQL_SUCCESS | m_intRetCode == SQL_SUCCESS_WITH_INFO)
            {
                if (Outlen == 0)
                {
                    strReturn = NO_INFO;
                }
                else
                {
                    if (m_intRetCode == SQL_ERROR)
                    {
                        strReturn = rgbValue3.ToString().Substring(0, Outlen);
                    }
                    else
                    {
                        strReturn = rgbValue3.ToString().Substring(0, Outlen);
                    }
                }
            }
        } while (!(m_intRetCode != SQL_SUCCESS));

        if (strReturn == "")
        {
            if (intRetCode == SQL_NO_DATA_FOUND)
            {
                strReturn = "No data found";
            }
            else if (intRetCode == SQL_INVALID_HANDLE)
            {
                strReturn = "Invalid handle";
            }
            else
            {
                strReturn = NO_INFO;
            }
        }
        return strReturn;
    }

    private static bool InitializeEnvironment()
    {
        if (!m_hEnvironment.Equals(IntPtr.Zero))
            return true;

        m_intRetCode = SQLAllocEnv(ref m_hEnvironment);
        if ((m_intRetCode == SQL_ERROR | m_intRetCode == SQL_INVALID_HANDLE))
        {
            MessageBox.Show("Unable to allocate ODBC environment handle");
            m_hEnvironment = IntPtr.Zero;
            return false;
        }
        else
        {
            return true;
        }
    }

    private static bool ExitEnvironment()
    {
        m_intRetCode = SQLFreeEnv(m_hEnvironment);
        if (m_intRetCode != SQL_SUCCESS)
        {
            MessageBox.Show("Unable to free ODBC environment handle");
        }
        else
        {
            m_hEnvironment = IntPtr.Zero;
            return true;
        }
        return false;
    }

    private static int BeginConnection(string sConnectString)
    {
        try
        {
            if (sConnectString.Trim().Length == 0)
            {
                throw new ArgumentException("Connection string cannot be blank.");
            }
            m_intRetCode = SQLAllocConnect(m_hEnvironment, ref m_hDatabaseConnection);
            if (m_intRetCode != SQL_SUCCESS)
            {
                throw new ApplicationException("Cannot allocate connection handle");
            }
            string strConStrIn = sConnectString;
            string strConStrOut = new string(' ', 1024);
            int length = 0;
            StringBuilder builder = new StringBuilder(1024);
            m_intRetCode = SQLDriverConnect(m_hDatabaseConnection, IntPtr.Zero, strConStrIn, strConStrIn.Length, builder, builder.Length, ref length, SQL_DRIVER_NOPROMPT);
            if ((m_intRetCode == SQL_ERROR) | (m_intRetCode == SQL_INVALID_HANDLE))
                throw new ApplicationException(GetSQLErrorDesc(m_hEnvironment, m_hDatabaseConnection, m_hStatement, 0));

            if (m_intRetCode == SQL_SUCCESS)
                m_intRetCode = SQLAllocStmt(m_hDatabaseConnection, ref m_hStatement);
        }
        catch
        {
            m_intRetCode = SQL_ERROR;
        }
        return m_intRetCode;
    }

    private static bool EndConnection()
    {
        m_intRetCode = SQLFreeStmt(m_hStatement, SQL_CLOSE);
        if (m_intRetCode != SQL_SUCCESS)
        {
            MessageBox.Show("Unable to free statment handle");
        }
        m_hStatement = IntPtr.Zero;
        m_intRetCode = SQLDisconnect(m_hDatabaseConnection);
        if (m_intRetCode != SQL_SUCCESS)
        {
            MessageBox.Show("Unable to disconnect");
        }
        m_intRetCode = SQLFreeConnect(m_hDatabaseConnection);
        if (m_intRetCode != SQL_SUCCESS)
        {
            MessageBox.Show("Unable to free connection handle");
        }
        m_hDatabaseConnection = IntPtr.Zero;
        return true;
    }

    private static List<string> GetTables()
    {
        return GetTables(true, "'TABLE', 'VIEW', 'SYSTEM TABLE'");
    }

    private static List<string> GetTables(bool Owner, string TableType)
    {
        List<string> listTables = new List<string>();
        int lngLenRead = 0;

        m_intRetCode = SQLTables(m_hStatement, "\0", SQL_NTS, "\0", SQL_NTS, "\0", SQL_NTS, "TABLE,VIEW,SYNONYM", SQL_NTS);
        if (m_intRetCode != SQL_SUCCESS)
        {
            MessageBox.Show(GetSQLErrorDesc(m_hEnvironment, m_hDatabaseConnection, m_hStatement, (int)m_intRetCode));
        }
        else
        {
            StringBuilder strOwner = new StringBuilder(255);
            StringBuilder strName = new StringBuilder(255);
            m_intRetCode = SQLFetch(m_hStatement);
            if (m_intRetCode != SQL_SUCCESS)
            {
                MessageBox.Show(GetSQLErrorDesc(m_hEnvironment, m_hDatabaseConnection, m_hStatement, m_intRetCode));
            }
            else
            {
                while (m_intRetCode != SQL_NO_DATA_FOUND)
                {
                    m_intRetCode = SQLGetData(m_hStatement, 2, SQL_C_CHAR, strOwner, strOwner.Capacity, ref lngLenRead);
                    m_intRetCode = SQLGetData(m_hStatement, 3, SQL_C_CHAR, strName, strName.Capacity, ref lngLenRead);
                    if (strOwner.Length > 0 && strName.Length > 0)
                    {
                        listTables.Add(strOwner.ToString() + "." + strName.ToString());
                        strOwner = new StringBuilder(255);
                        strName = new StringBuilder(255);
                        m_intRetCode = SQLFetch(m_hStatement);
                    }
                }
            }
        }
        return listTables;
    }
}

Bob
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
asiAuthor Commented:
How do i receive  fields for table ?
0
 
Bob LearnedCommented:
We can use the OdbcReader.GetSchemaTable to get a DataTable that describes the columns for a table (look at GetDSNTableFields):

using System;
using System.Data.Odbc;
using System.Runtime.InteropServices;
using System.Text;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Data;

public class OdbcSchema
{
    private const int MAX_BUFFER = 0x1000;
    private static IntPtr _hEnvironment;
    private static IntPtr _hDatabaseConnection;
    private static IntPtr _hStatement;
    private static SqlError _returnCode;

    private enum SqlError
    {
        SQL_ERROR = -1,
        SQL_INVALID_HANDLE = -2,
        SQL_NO_DATA_FOUND = 100,
        SQL_SUCCESS = 0,
        SQL_SUCCESS_WITH_INFO = 1,
        SQL_NTS = -3,
        SQL_SQLSTATE_SIZE = 5,
        SQL_MAX_MESSAGE_LENGTH = 512,
        SQL_MAX_DSN_LENGTH = 32,
        SQL_CHAR = 1,
        SQL_NUMERIC = 2,
        SQL_DECIMAL = 3,
        SQL_INTEGER = 4,
        SQL_SMALLINT = 5,
        SQL_FLOAT = 6,
        SQL_REAL = 7,
        SQL_DOUBLE = 8,
        SQL_VARCHAR = 12,
        SQL_TYPE_MIN = 1,
        SQL_TYPE_NULL = 0,
        SQL_TYPE_MAX = 12,
        SQL_C_CHAR = SQL_CHAR,
        SQL_C_LONG = SQL_INTEGER,
        SQL_C_int = SQL_SMALLINT,
        SQL_C_FLOAT = SQL_REAL,
        SQL_C_DOUBLE = SQL_DOUBLE,
        SQL_CLOSE = 0,
        SQL_DROP = 1,
        SQL_UNBIND = 2,
        SQL_RESET_PARAMS = 3,
        SQL_DRIVER_NOPROMPT = 0,
        SQL_DRIVER_COMPLETE = 1,
        SQL_DRIVER_PROMPT = 2,
        SQL_DRIVER_COMPLETE_REQUIRED = 3,
    }

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLError(IntPtr EnvironmentHandle, IntPtr ConnectionHandle, IntPtr StatementHandle, StringBuilder Sqlstate, ref int NativeError, StringBuilder MessageText, int BufferLength, ref int TextLength);

    [DllImport("odbc32.dll")]
    private static extern int SQLAllocEnv(ref IntPtr EnvironmentHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLFreeEnv(IntPtr EnvironmentHandle);

    [DllImport("odbc32.dll")]
    private static extern int SQLAllocConnect(IntPtr EnvironmentHandle, ref IntPtr ConnectionHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLDriverConnect(IntPtr hdbc, IntPtr hwnd, string szConnStrIn, int cbConnStrIn, StringBuilder szConnStrOut, int cbConnStrOutMax, ref int pcbConnStrOut, [MarshalAs(UnmanagedType.U2)]
            int fDriverCompletion);

    [DllImport("odbc32.dll")]
    private static extern int SQLAllocStmt(IntPtr ConnectionHandle, ref IntPtr StatementHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLFreeStmt(IntPtr StatementHandle, [MarshalAs(UnmanagedType.U2)]
            int Option);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLDisconnect(IntPtr ConnectionHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLFreeConnect(IntPtr ConnectionHandle);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLTables(IntPtr StatementHandle, string CatalogName,
        int NameLength1, string SchemaName, int NameLength2, string TableName, int NameLength3, string TableType, int NameLength4);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLNumResultCols(IntPtr hstmt, ref int pccol);

    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    private static extern int SQLDescribeCol(IntPtr hstmt, int icol, ref string szColName,
        int cbColNameMax, ref int pcbOutLen, ref int pfSqlType, ref int pcbColDef,
        ref int pibScale, ref int pfNullable);

    [DllImport("odbc32.dll")]
    private static extern int SQLFetch(IntPtr StatementHandle);

    [DllImport("odbc32.dll")]
    private static extern int SQLGetData(IntPtr StatementHandle, [MarshalAs(UnmanagedType.U4)]
            int ColumnNumber, int TargetType, StringBuilder TargetValue, int BufferLength, ref int StrLen_or_Ind);

    public static List<string> GetDSNTables(string dsnName)
    {
        return GetDSNTables(dsnName, string.Empty, string.Empty);
    }

    public static List<string> GetDSNTables(string dsnName, string userName, string password)
    {
        List<string> listTables = new List<string>();
        try
        {
            if (InitializeEnvironment())
            {
                string connectionString = MakeConnectionString(dsnName, userName, password);
                if (BeginConnection(connectionString) == 0)
                {
                    listTables = GetTables();
                    EndConnection();
                }
                ExitEnvironment();
            }
            return listTables;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        return null;
    }

    public static DataTable GetDSNTableFields(string dsnName, string tableName)
    {
        return GetDSNTableFields(dsnName, tableName, "", "");
    }

    public static DataTable GetDSNTableFields(string dsnName, string tableName, string userName, string password)
    {
        string connectionString = MakeConnectionString(dsnName, userName, password);

        using (OdbcConnection connection = new OdbcConnection(connectionString))
        {
            connection.Open();

            string commandText = string.Format("Select * From {0}", tableName);
            using (OdbcCommand command = new OdbcCommand(commandText, connection))
            {
                using (OdbcDataReader reader = command.ExecuteReader())
                {
                    return reader.GetSchemaTable();
                }
            }

        }
    }

    private static string MakeConnectionString(string dsnName, string userName, string userPassword)
    {
        return (userName.Trim().Length == 0) ?
            string.Format("DSN={0};user={1};pwd={2}", dsnName, userName, userPassword) :
            string.Format("DSN={0}", dsnName);
    }

    private static string GetSQLErrorDesc(IntPtr hEnv, IntPtr hDbc, IntPtr hStmt, SqlError returnCode)
    {
        int Outlen = 0;
        int Native = 0;
        string strReturn = "";

        const string NO_INFO = "Error - <No error information available>";

        StringBuilder rgbValue1 = new StringBuilder(16);
        StringBuilder rgbValue3 = new StringBuilder(MAX_BUFFER);
        do
        {
            _returnCode = (SqlError)SQLError(hEnv, hDbc, hStmt, rgbValue1, ref Native, rgbValue3, MAX_BUFFER, ref Outlen);
            if (_returnCode == SqlError.SQL_SUCCESS | _returnCode == SqlError.SQL_SUCCESS_WITH_INFO)
            {
                if (Outlen == 0)
                {
                    strReturn = NO_INFO;
                }
                else
                {
                    if (_returnCode == SqlError.SQL_ERROR)
                    {
                        strReturn = rgbValue3.ToString().Substring(0, Outlen);
                    }
                    else
                    {
                        strReturn = rgbValue3.ToString().Substring(0, Outlen);
                    }
                }
            }
        } while (_returnCode != SqlError.SQL_SUCCESS);

        if (strReturn == "")
        {
            if (returnCode == SqlError.SQL_NO_DATA_FOUND)
            {
                strReturn = "No data found";
            }
            else if (returnCode == SqlError.SQL_INVALID_HANDLE)
            {
                strReturn = "Invalid handle";
            }
            else
            {
                strReturn = NO_INFO;
            }
        }
        return strReturn;
    }

    private static bool InitializeEnvironment()
    {
        if (!_hEnvironment.Equals(IntPtr.Zero))
            return true;

        _returnCode = (SqlError)SQLAllocEnv(ref _hEnvironment);
        if ((_returnCode == SqlError.SQL_ERROR | _returnCode == SqlError.SQL_INVALID_HANDLE))
        {
            MessageBox.Show("Unable to allocate ODBC environment handle");
            _hEnvironment = IntPtr.Zero;
            return false;
        }
        else
        {
            return true;
        }
    }

    private static bool ExitEnvironment()
    {
        _returnCode = (SqlError)SQLFreeEnv(_hEnvironment);
        if (_returnCode != SqlError.SQL_SUCCESS)
        {
            MessageBox.Show("Unable to free ODBC environment handle");
        }
        else
        {
            _hEnvironment = IntPtr.Zero;
            return true;
        }
        return false;
    }

    private static SqlError BeginConnection(string sConnectString)
    {
        try
        {
            if (sConnectString.Trim().Length == 0)
            {
                throw new ArgumentException("Connection string cannot be blank.");
            }
            _returnCode = (SqlError)SQLAllocConnect(_hEnvironment, ref _hDatabaseConnection);
            if (_returnCode != SqlError.SQL_SUCCESS)
            {
                throw new ApplicationException("Cannot allocate connection handle");
            }
            string strConStrIn = sConnectString;
            string strConStrOut = new string(' ', 1024);
            int length = 0;
            StringBuilder builder = new StringBuilder(1024);
            _returnCode = (SqlError)SQLDriverConnect(_hDatabaseConnection, IntPtr.Zero, strConStrIn, strConStrIn.Length, builder, builder.Length, ref length, (int)SqlError.SQL_DRIVER_NOPROMPT);
            if ((_returnCode == SqlError.SQL_ERROR) | (_returnCode == SqlError.SQL_INVALID_HANDLE))
                throw new ApplicationException(GetSQLErrorDesc(_hEnvironment, _hDatabaseConnection, _hStatement, 0));

            if (_returnCode == SqlError.SQL_SUCCESS)
                _returnCode = (SqlError)SQLAllocStmt(_hDatabaseConnection, ref _hStatement);
        }
        catch
        {
            _returnCode = SqlError.SQL_ERROR;
        }
        return _returnCode;
    }

    private static bool EndConnection()
    {
        _returnCode = (SqlError)SQLFreeStmt(_hStatement, (int)SqlError.SQL_CLOSE);
        if (_returnCode != SqlError.SQL_SUCCESS)
        {
            MessageBox.Show("Unable to free statment handle");
        }
        _hStatement = IntPtr.Zero;
        _returnCode = (SqlError)SQLDisconnect(_hDatabaseConnection);
        if (_returnCode != SqlError.SQL_SUCCESS)
        {
            MessageBox.Show("Unable to disconnect");
        }
        _returnCode = (SqlError)SQLFreeConnect(_hDatabaseConnection);
        if (_returnCode != SqlError.SQL_SUCCESS)
        {
            MessageBox.Show("Unable to free connection handle");
        }
        _hDatabaseConnection = IntPtr.Zero;
        return true;
    }

    private static List<string> GetTables()
    {
        return GetTables(true, "'TABLE', 'VIEW', 'SYSTEM TABLE'");
    }

    private static List<string> GetTables(bool Owner, string TableType)
    {
        List<string> listTables = new List<string>();
        int lngLenRead = 0;

        int nts = (int)SqlError.SQL_NTS;
        _returnCode = (SqlError)SQLTables(_hStatement, "\0", nts, "\0", nts, "\0", nts, "TABLE,VIEW,SYNONYM", nts);
        if (_returnCode != SqlError.SQL_SUCCESS)
        {
            MessageBox.Show(GetSQLErrorDesc(_hEnvironment, _hDatabaseConnection, _hStatement, _returnCode));
        }
        else
        {
            StringBuilder owner = new StringBuilder(255);
            StringBuilder name = new StringBuilder(255);

            _returnCode = (SqlError)SQLFetch(_hStatement);
            if (_returnCode != SqlError.SQL_SUCCESS)
            {
                MessageBox.Show(GetSQLErrorDesc(_hEnvironment, _hDatabaseConnection, _hStatement, _returnCode));
            }
            else
            {
                while (_returnCode != SqlError.SQL_NO_DATA_FOUND)
                {
                    _returnCode = (SqlError)SQLGetData(_hStatement, 2, (int)SqlError.SQL_C_CHAR, owner, owner.Capacity, ref lngLenRead);
                    _returnCode = (SqlError)SQLGetData(_hStatement, 3, (int)SqlError.SQL_C_CHAR, name, name.Capacity, ref lngLenRead);
                    if (owner.Length > 0 && name.Length > 0)
                    {
                        listTables.Add(owner.ToString() + "." + name.ToString());
                        owner = new StringBuilder(255);
                        name = new StringBuilder(255);
                        _returnCode = (SqlError)SQLFetch(_hStatement);
                    }
                }
            }
        }
        return listTables;
    }
}

Bob
0
 
asiAuthor Commented:
For some  reason when i try to use it:

List <string> lsttable= OdbcSchema.GetDSNTables( "MySql_Northwind");

 i receive an error "Cannot allocate connection handle" , why ?
Thank you
Asi
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now