We help IT Professionals succeed at work.

C# using ODBC32.DLL to retrieve System DSN's

jerryleeclark
on
Medium Priority
2,203 Views
Last Modified: 2012-05-06
I am trying to recreate the code previously talked about in to a simpler more straight forward fashion so I can see what I am doing wrong.  I have created a Form 1 application only adding the minium to retrieve the DSN's. This is what I have come up with:
using System;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
 
namespace dsn_test
{
    public partial class Form1 : Form
    {
        [DllImport("odbc32")]
        public static extern short SQLAllocHandle(short HandleType, IntPtr InputHandle, out IntPtr OutputHandle);
        [DllImport("odbc32", CharSet = CharSet.Unicode)]
        public static extern short SQLSetEnvAttr(IntPtr envHandle, ushort attribute, IntPtr val, int stringLength);
        [DllImport("odbc32", CharSet = CharSet.Ansi)]
        public static extern short SQLDataSources(IntPtr EnvironmentHandle, ushort Direction, StringBuilder ServerName, short BufferLength1, ref short NameLength1Ptr, StringBuilder Description, short BufferLength2, ref short NameLength2Ptr);
         
        public Form1()
        {
            InitializeComponent();
        }        
        private void button1_Click(object sender, EventArgs e)
        {   
            short iResult = 0;
            IntPtr lhEnvIn = (IntPtr)0;
            IntPtr lhEnv = (IntPtr)0;
            StringBuilder sDSNItem = new StringBuilder(1024);
            StringBuilder sDRVItem = new StringBuilder(1024);
            short iDSNLen = 0;
            short iDRVLen = 0;
 
            SQLSetEnvAttr(lhEnv, 200, (IntPtr)3, 0);
            
            iResult = SQLAllocHandle(1, lhEnvIn, out lhEnv);
            MessageBox.Show(iResult.ToString(), "iResult SQLAllocHandle");
 
            if (iResult != 0)
            {
                iResult = SQLSetEnvAttr(lhEnv, 200, lhEnvIn, 100);
                MessageBox.Show(iResult.ToString(), "iResult SQLSetEnvAttr");
 
                if (iResult != 0)
                {
                    while (SQLDataSources(lhEnv, 1, sDSNItem, 1024, ref iDSNLen, sDRVItem, 1024, ref iDRVLen) == 0)
                    {
                        MessageBox.Show(sDSNItem.ToString(), "sDSNItem.ToString()");
                    }                   
                }                
            }
        }
    
    }
}

Open in new window

Comment
Watch Question

Commented:
Hi,

Here's another way to accomplish this without p/invoke.

using System;
using System.Data;
using System.Data.OleDb;

class Program {
  static void Main() {
    OleDbDataReader reader =
      OleDbEnumerator.GetEnumerator(Type.GetTypeFromProgID("MSDASQL Enumerator"));

    while (reader.Read()) {
      for (int i = 0; i < reader.FieldCount; i++) {
        Console.WriteLine("{0} = {1}", reader.GetName(i), reader.GetValue(i));
      }
      Console.WriteLine("==================================");
    }

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();
  }
}

/peter

Author

Commented:
can you manage the DSN's using the OldDB? add/delete?

Commented:
No, I don't think so. I thought you just wanted a list of DSNs,

About your ODBC example, I think the lines  
  if (iResult != 0)
should be  
  if (iResult == 0)
since SQL_SUCCESS is 0.


Author

Commented:
I saw on msdn the items that are returned such as 'SQL_SUCCESS' but no correlations as to what integer those returned items relate to such as SQL_SUCCESS=0 so I was a little lost. OK now. The next method (SQLSetEnvAttr) returns a -1. I take it a -1 is not a success. Can you say what is wrong there?

Commented:
Yes, -1 is SQL_ERROR.

What are you doing in the second SQLSetEnvAttr? As I can see it you trying to set ODBC version to 0? Which is illegal. But you have already set it to 3.
Commented:
Try this:

  public partial class Form1 : Form {
    [DllImport("odbc32")]
    public static extern short SQLAllocHandle(short HandleType, IntPtr InputHandle, out IntPtr OutputHandle);
    [DllImport("odbc32", CharSet = CharSet.Unicode)]
    public static extern short SQLSetEnvAttr(IntPtr envHandle, ushort attribute, IntPtr val, int stringLength);
    [DllImport("odbc32.dll", CharSet = CharSet.Ansi)]
    public static extern short SQLDataSources(IntPtr EnvironmentHandle, ushort Direction, StringBuilder ServerName, short BufferLength1, ref short NameLength1Ptr, StringBuilder Description, short BufferLength2, ref short NameLength2Ptr);

    public const int SQL_SUCCESS = 0;
    public const int SQL_ERROR = -1;
   
    public const int SQL_FETCH_NEXT = 1;
    public const int SQL_FETCH_FIRST = 2;
    public const int SQL_FETCH_FIRST_USER = 31;
    public const int SQL_FETCH_FIRST_SYSTEM = 32;

    public const int SQL_ATTR_ODBC_VERSION = 200;

    public const int SQL_HANDLE_ENV = 1;
    public const int SQL_HANDLE_DBC = 2;
    public const int SQL_HANDLE_STMT = 3;
    public const int SQL_HANDLE_DESC = 4;

    public Form1() {
      InitializeComponent();
     }

    private void button1_Click(object sender, EventArgs e) {
         
      short iResult = 0;
      IntPtr lhEnvIn = (IntPtr)0;
      IntPtr lhEnv = (IntPtr)0;
      StringBuilder sDSNItem = new StringBuilder(1024);
      StringBuilder sDRVItem = new StringBuilder(1024);
      short iDSNLen = 0;
      short iDRVLen = 0;

      iResult = SQLAllocHandle(SQL_HANDLE_ENV, lhEnvIn, out lhEnv);

      SQLSetEnvAttr(lhEnv, SQL_ATTR_ODBC_VERSION, (IntPtr)3, 0);

      if (iResult == SQL_SUCCESS) {
        iResult = SQLDataSources(lhEnv, SQL_FETCH_FIRST, sDSNItem, 1024, ref iDSNLen, sDRVItem, 1024, ref iDRVLen);
        while (iResult == SQL_SUCCESS)
        {
          MessageBox.Show(sDSNItem.ToString(), "sDSNItem.ToString()");
          iResult = SQLDataSources(lhEnv, SQL_FETCH_NEXT, sDSNItem, 1024, ref iDSNLen, sDRVItem, 1024, ref iDRVLen);
        }
      }
    }
  }

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.