?
Solved

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

Posted on 2009-02-18
6
Medium Priority
?
2,056 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

0
Comment
Question by:jerryleeclark
  • 4
  • 2
6 Comments
 
LVL 22

Expert Comment

by:pivar
ID: 23676064
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
0
 

Author Comment

by:jerryleeclark
ID: 23676233
can you manage the DSN's using the OldDB? add/delete?
0
 
LVL 22

Expert Comment

by:pivar
ID: 23676324
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.


0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jerryleeclark
ID: 23676500
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?
0
 
LVL 22

Expert Comment

by:pivar
ID: 23679098
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.
0
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 23679241
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);
        }
      }
    }
  }

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question