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