asked on

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

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

pivar

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

jerryleeclark

ASKER

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

pivar

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.

jerryleeclark

ASKER

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?

pivar

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.

ASKER CERTIFIED SOLUTION

pivar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial