欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

csharp: read system DSN configured get Driver Names on windows

程序员文章站 2022-09-12 22:41:55
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.... ......

 

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Win32;
using System.Data.Odbc;

namespace SQLanyWhereDemo
{
    public partial class Form5 : 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);
        [DllImport("ODBCCP32.dll")]
        private static extern bool SQLConfigDataSource(IntPtr parent, int request, string driver, string attributes);

        [DllImport("odbccp32.dll", CharSet = CharSet.Unicode, SetLastError = true)]
        private static extern bool SQLGetInstalledDriversW(char[] lpszBuf, ushort cbufMax, out ushort pcbBufOut);

        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;
        /// <summary>
        /// 
        /// </summary>
        public Form5()
        {
            InitializeComponent();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {

            List<ODBCStr> ls = new List<ODBCStr>();
            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");

            //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)
            {
                ODBCStr sr = null;
                iResult = SQLDataSources(lhEnv, SQL_FETCH_FIRST, sDSNItem, 1024, ref iDSNLen, sDRVItem, 1024, ref iDRVLen);
                while (iResult == SQL_SUCCESS)
                {
                    sr=new ODBCStr();
                    //MessageBox.Show(sDSNItem.ToString(), "sDSNItem.ToString()");
                    iResult = SQLDataSources(lhEnv, SQL_FETCH_NEXT, sDSNItem, 1024, ref iDSNLen, sDRVItem, 1024, ref iDRVLen);
                    sr.DsnName = sDSNItem.ToString();
                    sr.DriveName = sDRVItem.ToString();
                    //string str = "SERVER=HOME\0DSN=MYDSN\0DESCRIPTION=MYDSNDESC\0DATABASE=DBServer\0TRUSTED_CONNECTION=YES";
                    //SQLConfigDataSource((IntPtr)0, 4, "Sybase SQL Anywhere 5.0", str);//SQL Server
                    //MessageBox.Show(str);

                    ls.Add(sr);
                }
            }

            this.dataGridView1.DataSource = ls;

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {

            string[] list = GetOdbcDriverNames();


        }

        /// <summary>
        /// Gets the ODBC driver names from the SQLGetInstalledDrivers function.
        /// </summary>
        /// <returns>a string array containing the ODBC driver names, if the call to SQLGetInstalledDrivers was successfull; null, otherwise.</returns>
        public static string[] GetOdbcDriverNames()
        {
            string[] odbcDriverNames = null;
            char[] driverNamesBuffer = new char[ushort.MaxValue];
            ushort size;

            bool succeeded = SQLGetInstalledDriversW(driverNamesBuffer, ushort.MaxValue, out size);

            if (succeeded == true)
            {
                char[] driverNames = new char[size - 1];
                Array.Copy(driverNamesBuffer, driverNames, size - 1);
                odbcDriverNames = (new string(driverNames)).Split('\0');
            }

            return odbcDriverNames;
        }
    }


    /// <summary>
    /// 涂聚文
    /// 20180223
    /// Geovin Du
    /// </summary>
    public class ODBCStr
    {
        /// <summary>
        /// 
        /// </summary>
        public string DriveName { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string DsnName { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string ServerName { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string DatabaseName { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string DataSrource { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string DataFile { get; set; }


    }


}

  

 

  /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button3_Click(object sender, EventArgs e)
        {

            //string connectionString = "dsn=LocalServer";
            //System.Data.Common.DbConnectionStringBuilder builder = new System.Data.Common.DbConnectionStringBuilder(); 
            //builder.ConnectionString = connectionString;
            //string server = builder["Data Source"] as string;
            //string database = builder["Initial Catalog"] as string;

            //string conString = "SERVER=localhost;DATABASE=tree;UID=root;PASSWORD=branch;Min Pool Size = 0;Max Pool Size=200";
            //SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(conString);
            //string user = builder.UserID;
            //string pass = builder.Password;

            //OdbcConnectionStringBuilder buil =new OdbcConnectionStringBuilder();
            //buil.Driver = "Sybase SQL Anywhere 5.0";
            //buil.Dsn = "achive";

            System.Data.Odbc.OdbcConnectionStringBuilder connBuilder = new System.Data.Odbc.OdbcConnectionStringBuilder();
            connBuilder.Dsn = "achive";
            connBuilder.Driver = "Sybase SQL Anywhere 5.0";           
            //connBuilder.Add("uid", "");
            //connBuilder.Add("pwd", "");

            connBuilder.Add("database", "涂聚文");
            
            string sss = connBuilder.ToString();
            //MessageBox.Show(connBuilder.ToString());
            System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(connBuilder.ToString());
            try
            {
                conn.Open();
                System.Data.Odbc.OdbcCommand comm = new System.Data.Odbc.OdbcCommand("select count(*) from item_description", conn);
                var reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    MessageBox.Show(reader[0].ToString());
                }
                MessageBox.Show("连接成功!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
            finally
            {
                conn.Close();
            }
          
            //设置
            //OdbcConnectionStringBuilder.Dsn=connectionString;


        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="Database"></param>
        /// <param name="Version"></param>
        /// <returns></returns>
        public static string GetODBCDriverName(string Database, string Version)
        {

            string ODBCDriverName = "";

            RegistryKey registryKey = Registry.LocalMachine;

            RegistryKey registrySubKey = registryKey.OpenSubKey(@"SOFTWARE\ODBC\ODBCINST.INI\");

            String[] SubKeyNames = registrySubKey.GetSubKeyNames();

            foreach (String KeyName in SubKeyNames)
            {

                if (KeyName.Contains(Database) && KeyName.Contains(Version))
                {

                    ODBCDriverName = KeyName;

                    break;

                }
            }

            registrySubKey.Close();

            registryKey.Close();

            return ODBCDriverName;

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button4_Click(object sender, EventArgs e)
        {
                OdbcConnectionStringBuilder odbcConnectionStringBuilder = new OdbcConnectionStringBuilder();
                OdbcCommand odbcCommand;
                int RecordFound = 0;
                odbcConnectionStringBuilder.Driver = GetODBCDriverName("Sybase SQL Anywhere", "5.0");
                if (odbcConnectionStringBuilder.Driver == "")
                {

                    MessageBox.Show(" ODBC Driver is not installed");
                    //return -1;
                }
                odbcConnectionStringBuilder.Add("DSN", "achive");
                //odbcConnectionStringBuilder.Add("UID", "no-user");
                //odbcConnectionStringBuilder.Add("PWD", "no-pass");
                odbcConnectionStringBuilder.Add("DB", @"C:\Documents and Settings\geovindu\My Documents\Visual Studio 2010\Projects\SQLanyWhereDemo\SQLanyWhereDemo\bin\Debug\geovindu.db;"); // copy of database SPORTS
                odbcConnectionStringBuilder.Add("HOST", ".");
                //odbcConnectionStringBuilder.Add("PORT", "5162"); // i found this port inn the log file
                using (OdbcConnection connection = new OdbcConnection(odbcConnectionStringBuilder.ConnectionString))

                {

                    connection.Open();
                        try
                        {
                                odbcCommand = new OdbcCommand("SELECT COUNT(*) FROM pub.Invoice WHERE Invoicenum > 0", connection);
                                odbcCommand.CommandTimeout = 1;
                                object executeScalarResult = odbcCommand.ExecuteScalar();
                                RecordFound = Convert.ToInt32(executeScalarResult);
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message.ToString());
                        }
               }


    }