using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Windows.Forms;
namespace LtraSrchSrvic
{
class DataSql
{
#region 获取数据库服务器
[System.Runtime.InteropServices.DllImport("odbc32.dll")]
private static extern short SQLAllocHandle(short hType, IntPtr inputHandle, out IntPtr outputHandle);
[System.Runtime.InteropServices.DllImport("odbc32.dll")]
private static extern short SQLSetEnvAttr(IntPtr henv, int attribute, IntPtr valuePtr, int strLength);
[System.Runtime.InteropServices.DllImport("odbc32.dll")]
private static extern short SQLFreeHandle(short hType, IntPtr handle);
[System.Runtime.InteropServices.DllImport("odbc32.dll", CharSet = System.Runtime.InteropServices.CharSet.Ansi)]
private static extern short SQLBrowseConnect(IntPtr hconn, System.Text.StringBuilder inString,
short inStringLength, System.Text.StringBuilder outString, short outStringLength,
out short outLengthNeeded);
private const short SQL_HANDLE_ENV = 1;
private const short SQL_HANDLE_DBC = 2;
private const int SQL_ATTR_ODBC_VERSION = 200;
private const int SQL_OV_ODBC3 = 3;
private const short SQL_SUCCESS = 0;
private const short SQL_NEED_DATA = 99;
private const short DEFAULT_RESULT_SIZE = 1024;
private const string SQL_DRIVER_STR = "DRIVER=SQL SERVER";
/// <summary>
/// 获取网内的数据库服务器名称
/// </summary>
/// <returns>服务器名称数组</returns>
public List<string> GetLocalSqlServerNamesWithAPI()
{
string list = string.Empty;
IntPtr henv = IntPtr.Zero;
IntPtr hconn = IntPtr.Zero;
System.Text.StringBuilder inString = new System.Text.StringBuilder(SQL_DRIVER_STR);
System.Text.StringBuilder outString = new System.Text.StringBuilder(DEFAULT_RESULT_SIZE);
short inStringLength = (short)inString.Length;
short lenNeeded = 0;
try
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_ENV, henv, out henv))
{
if (SQL_SUCCESS == SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (IntPtr)SQL_OV_ODBC3, 0))
{
if (SQL_SUCCESS == SQLAllocHandle(SQL_HANDLE_DBC, henv, out hconn))
{
if (SQL_NEED_DATA == SQLBrowseConnect(hconn, inString, inStringLength, outString,
DEFAULT_RESULT_SIZE, out lenNeeded))
{
if (DEFAULT_RESULT_SIZE < lenNeeded)
{
outString.Capacity = lenNeeded;
if (SQL_NEED_DATA != SQLBrowseConnect(hconn, inString, inStringLength, outString,
lenNeeded, out lenNeeded))
{
throw new ApplicationException("Unabled to aquire SQL Servers from ODBC driver.");
}
}
list = outString.ToString();
int start = list.IndexOf("{") + 1;
int len = list.IndexOf("}") - start;
if ((start > 0) && (len > 0))
{
list = list.Substring(start, len);
}
else
{
list = string.Empty;
}
}
}
}
}
}
catch
{
list = string.Empty;
}
finally
{
if (hconn != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_DBC, hconn);
}
if (henv != IntPtr.Zero)
{
SQLFreeHandle(SQL_HANDLE_ENV, hconn);
}
}
List<string> array = null;
if (list.Length > 0)
{
string[] ba = list.Split(',');
foreach (string item in ba)
{
array.Add(item);
}
}
return array;
}
/// <summary>
/// 获取局域网内的所有数据库服务器名称
/// </summary>
/// <returns>服务器名称数组</returns>
public List<string> SqlServerNames()
{
DataTable dataSources = SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();
DataColumn column = dataSources.Columns["InstanceName"];
DataColumn column2 = dataSources.Columns["ServerName"];
DataRowCollection rows = dataSources.Rows;
List<string> Serverlist = new List<string>();
string array = string.Empty;
for (int i = 0; i < rows.Count; i++)
{
string str2 = rows[i][column2] as string;
string str = rows[i][column] as string;
if (((str == null) || (str.Length == 0)) || ("MSSQLSERVER" == str))
{
array = str2;
}
else
{
array= str2 + @"/" + str;
}
Serverlist.Add(array);
}
Serverlist.Sort();
return Serverlist;
}
#endregion
#region 获取数据库
/// <summary>
/// 查询sql中的非系统库
/// </summary>
/// <param name="Server"></param>
/// <param name="UserName"></param>
/// <param name="UserPassword"></param>
/// <returns></returns>
public List<string> databaseList(string connection)
{
List<string> getCataList = new List<string>();
//string cmdStirng = "select name from sys.databases";
string cmdStirng = "select name from sys.databases where database_id > 4";
SqlConnection connect = new SqlConnection(connection);
//SqlConnection connect = new SqlConnection("server=LOVEWJC;uid=sa;pwd =lovewjc");
SqlCommand cmd = new SqlCommand(cmdStirng, connect);
try
{
if(connect.State==ConnectionState.Closed)
{
connect.Open();
IDataReader dr = cmd.ExecuteReader();
getCataList.Clear();
while (dr.Read())
{
getCataList.Add(dr["name"].ToString());
}
dr.Close();
}
}
catch (SqlException e)
{
MessageBox.Show(e.Message);
}
finally
{
if (connect != null && connect.State == ConnectionState.Open)
{
connect.Dispose();
}
}
return getCataList;
}
#endregion
#region 获取表名
public List<string> GetTables(string connection)
{
List<string> tablelist = new List<string>();
SqlConnection objConnetion = new SqlConnection(connection);
try
{
if (objConnetion.State == ConnectionState.Closed)
{
objConnetion.Open();
DataTable objTable = objConnetion.GetSchema("Tables");
foreach (DataRow row in objTable.Rows)
{
tablelist.Add(row[2].ToString());
}
}
}
catch
{
}
finally
{
if(objConnetion!=null&&objConnetion.State==ConnectionState.Closed)
{
objConnetion.Dispose();
}
}
return tablelist;
}
#endregion
#region 获取字段
/// <summary>
/// 获取字段
/// </summary>
/// <param name="Server"></param>
/// <param name="Database"></param>
/// <param name="UserName"></param>
/// <param name="UserPassword"></param>
/// <param name="TableName"></param>
/// <returns></returns>
public List<string> GetColumnField(string connection, string TableName)
{
List<string> Columnlist = new List<string>();
SqlConnection objConnetion = new SqlConnection(connection);
try
{
if (objConnetion.State == ConnectionState.Closed)
{
objConnetion.Open();
}
SqlCommand cmd = new SqlCommand("Select Name FROM SysColumns Where id=Object_Id('" + TableName + "')", objConnetion);
SqlDataReader objReader = cmd.ExecuteReader();
while(objReader.Read())
{
Columnlist.Add(objReader[0].ToString());
}
}
catch
{
}
objConnetion.Close();
return Columnlist;
}
#endregion
}
}