获得目标服务器中所有数据库名、表名、列名
1 /// <summary> 2 /// 获得目标服务器所有数据库名 3 /// </summary> 4 /// <param name="serverName"></param> 5 /// <param name="userName"></param> 6 /// <param name="password"></param> 7 public void getDataBaseNameList(string serverName, string userName, string password) 8 { 9 SQLDMO.Application sqlApplication = new SQLDMO.ApplicationClass(); 10 SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass(); 11 12 sqlServer.Connect(serverName, userName, password); // 连接服务器 13 14 foreach (SQLDMO.Database databBase in sqlServer.Databases) 15 { 16 if (databBase.Name != null) 17 { 18 this.DataBaseTreeView.Nodes.Add(databBase.Name); 19 20 getDataBaseTableList(serverName, userName, password, databBase.Name); 21 } 22 } 23 } 24 25 26 /// <summary> 27 /// 加载数据库中表 28 /// </summary> 29 /// <param name="serverName">服务器名</param> 30 /// <param name="userName">用户名</param> 31 /// <param name="password">密码</param> 32 /// <param name="dataBaseName">数据库名</param> 33 private void getDataBaseTableList(string serverName, string userName, string password, string dataBaseName) 34 { 35 SQLDMO.SQLServer Server = new SQLDMO.SQLServerClass(); 36 37 //连接到服务器 38 Server.Connect(serverName, userName, password); 39 40 //对所有的数据库遍历，获得指定数据库 41 for (int i = 0; i < Server.Databases.Count; i++) 42 { 43 //判断当前数据库是否是指定数据库 44 if (Server.Databases.Item(i + 1, "dbo").Name == dataBaseName) 45 { 46 //获得指定数据库 47 SQLDMO._Database db = Server.Databases.Item(i + 1, "dbo"); 48 49 //获得指定数据库中的所有表 50 for (int j = 0; j < db.Tables.Count; j++) 51 { 52 this.DataBaseTreeView.Nodes[i].Nodes.Add(db.Tables.Item(j + 1, "dbo").Name); 53 } 54 } 55 } 56 } 57 58 59 /// <summary> 60 /// 获得表中所有列名 61 /// </summary> 62 /// <param name="serverName">服务器名</param> 63 /// <param name="userName">用户名</param> 64 /// <param name="password">密码</param> 65 /// <param name="tableName">表名</param> 66 /// <param name="dataBaseName">数据库名</param> 67 /// <returns></returns> 68 public string getRowListFromTable(string serverName, string userName, string password, string tableName, string dataBaseName) 69 { 70 string result = string.Empty; 71 72 string connectionString = string.Empty; 73 connectionString += "server=" + serverName; 74 connectionString += ";Pwd=" + password; 75 connectionString += ";UID=" + userName; 76 connectionString += ";Database=" + dataBaseName; 77 78 string commandString = string.Empty; 79 commandString += "select name from syscolumns where id=object_id('"; 80 commandString += tableName; 81 commandString += "')"; 82 83 84 SqlConnection sqlConnection = new SqlConnection(connectionString); 85 SqlCommand sqlCommand = new SqlCommand(commandString, sqlConnection); 86 87 SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, sqlConnection); 88 89 DataSet dataSet = new DataSet(); 90 dataAdapter.Fill(dataSet); 91 92 DataTable dataTable = dataSet.Tables[0]; 93 94 // DataTable dataTable = sqlConnection.GetSchema("Tables"); 95 foreach (DataRow row in dataTable.Rows) 96 { 97 result += row[0].ToString() + "-"; 98 } 99 100 if (result != null) 101 { 102 return result; 103 } 104 else 105 { 106 return "0"; 107 } 108 }
下面是转载的代码：
using System.Data.SqlClient;
/// <summary>
/// 获取局域网内的所有数据库服务器名称
/// </summary>
/// <returns>服务器名称数组</returns>
public List<string> GetSqlServerNames()
{
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;
}
/// <summary>
/// 查询sql中的非系统库
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
public List<string> databaseList(string connection)
{
List<string> getCataList = new List<string>();
string cmdStirng = "select name from sys.databases where database_id > 4";
SqlConnection connect = new SqlConnection(connection);
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;
}
/// <summary>
/// 获取列名
/// </summary>
/// <param name="connection"></param>
/// <returns></returns>
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;
}
/// <summary>
/// 获取字段
/// </summary>
/// <param name="connection"></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;
}
2：
region 得到所有本地网络中可使用的SQL服务器列表
/// <summary>
/// 得到所有本地网络中可使用的SQL服务器列表
/// </summary>
/// <param name= "p_strServerList "> 服务器列表 </param>
/// <returns> </returns>
public static bool GetServers(ref string [] p_strServerList)
{
try
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
if(sqlServers.Count > 0)
{
p_strServerList = new string[sqlServers.Count];
for(int i=0;i <sqlServers.Count;i++)
{
string srv = sqlServers.Item(i + 1);
if(srv != null)
{
p_strServerList[i] = srv;
}
}
}
return true;
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
#region 得到指定SQL服务器所有数据库的列表
/// <summary>
/// 得到指定SQL服务器所有数据库的列表
/// </summary>
/// <param name= "p_strDataBaseList "> 数据库列表 </param>
/// <param name= "p_strServer "> 服务器名 </param>
/// <param name= "p_strUser "> 用户名 </param>
/// <param name= "p_strPWD "> 密码 </param>
/// <returns> </returns>
public static bool GetDataBases(ref string [] p_strDataBaseList, string p_strServer, string p_strUser, string p_strPWD)
{
try
{
int i = 0;
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(p_strServer,p_strUser,p_strPWD);
if(srv.Databases.Count > 0)
{
p_strDataBaseList = new string[srv.Databases.Count];
foreach(SQLDMO.Database db in srv.Databases)
{
if(db.Name!=null)
{
p_strDataBaseList[i] = db.Name;
}
i = i + 1;
}
}
return true;
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
#region 得到所有的存储过程
/// <summary>
/// 得到所有的存储过程
/// </summary>
/// <param name= "p_strProcedureList "> 存储过程列表 </param>
/// <param name= "p_strServer "> 服务器名 </param>
/// <param name= "p_strUser "> 用户名 </param>
/// <param name= "p_strPWD "> 密码 </param>
/// <param name= "p_strDataBase "> 数据库名 </param>
/// <returns> </returns>
public static bool GetProcedures(ref string [] p_strProcedureList, string p_strServer, string p_strUser, string p_strPWD, string p_strDataBase)
{
try
{
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(p_strServer,p_strUser,p_strPWD);
for(int i=0;i <srv.Databases.Count;i++)
{
if(srv.Databases.Item(i+1, "dbo ").Name == p_strDataBase)
{
SQLDMO._Database db= srv.Databases.Item(i+1, "dbo ");
if (db.StoredProcedures.Count > 0)
{
p_strProcedureList = new string[db.StoredProcedures.Count];
for(int j=0;j <db.StoredProcedures.Count;j++)
{
p_strProcedureList[j] = db.StoredProcedures.Item(j+1, "dbo ").Name;
}
break;
}
}
}
return true;
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
#region 得到所有的Tables集合
/// <summary>
/// 得到所有的Tables集合
/// </summary>
/// <param name= "p_strProcedureList "> Tables集合 </param>
/// <param name= "p_strServer "> 服务器名 </param>
/// <param name= "p_strUser "> 用户名 </param>
/// <param name= "p_strPWD "> 密码 </param>
/// <param name= "p_strDataBase "> 数据库名 </param>
/// <returns> </returns>
public static bool GetTables(ref string [] p_strTableList, string p_strServer, string p_strUser, string p_strPWD, string p_strDataBase)
{
try
{
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(p_strServer,p_strUser,p_strPWD);
for(int i=0;i <srv.Databases.Count;i++)
{
if(srv.Databases.Item(i+1, "dbo ").Name == p_strDataBase)
{
SQLDMO._Database db= srv.Databases.Item(i+1, "dbo ");
if (db.Tables.Count > 0)
{
p_strTableList = new string[db.Tables.Count];
for(int j=0;j <db.Tables.Count;j++)
{
p_strTableList[j] = db.Tables.Item(j+1, "dbo ").Name;
}
break;
}
}
}
return true;
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
#region 得到所有的Views集合
/// <summary>
/// 得到所有的Views集合
/// </summary>
/// <param name= "p_strProcedureList "> Views集合 </param>
/// <param name= "p_strServer "> 服务器名 </param>
/// <param name= "p_strUser "> 用户名 </param>
/// <param name= "p_strPWD "> 密码 </param>
/// <param name= "p_strDataBase "> 数据库名 </param>
/// <returns> </returns>
public static bool GetViews(ref string [] p_strViewList, string p_strServer, string p_strUser, string p_strPWD, string p_strDataBase)
{
try
{
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(p_strServer,p_strUser,p_strPWD);
for(int i=0;i <srv.Databases.Count;i++)
{
if(srv.Databases.Item(i+1, "dbo ").Name == p_strDataBase)
{
SQLDMO._Database db= srv.Databases.Item(i+1, "dbo ");
if (db.Views.Count > 0)
{
p_strViewList = new string[db.Views.Count];
for(int j=0;j <db.Views.Count;j++)
{
p_strViewList[j] = db.Views.Item(j+1, "dbo ").Name;
}
break;
}
}
}
return true;
}
catch(Exception ex)
{
throw ex;
}
}
#endregion
