获得目标服务器中所有数据库名、表名、列名

  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         }