csharp: read system DSN configured get Driver Names on windows

標籤：try   box   desc   using   lin   ons   sum   collect   split   

 

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());                        }               }    }

　　

csharp: read system DSN configured get Driver Names on windows