C#获得SqlServer服务器名、数据库名、表名、字段名[转]

 

 

       using System.Data.SqlClient;  
  
        ///   
        /// 获取局域网内的所有数据库服务器名称   
        ///   
        /// 服务器名称数组   
        public List GetSqlServerNames()  
        {  
            DataTable dataSources = SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();  
 
           DataColumn column = dataSources.Columns["InstanceName"];  
           DataColumn column2 = dataSources.Columns["ServerName"];  
 
           DataRowCollection rows = dataSources.Rows;  
           List Serverlist = new List();  
           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;  
       }  
 
       ///   
       /// 查询sql中的非系统库   
       ///   
       ///   
       ///   
       public List databaseList(string connection)  
       {  
           List getCataList = new List();  
           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;  
       }  
 
       ///   
       /// 获取列名   
       ///   
       ///   
       ///   
       public List GetTables(string connection)  
       {  
           List tablelist = new List();  
           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;  
       }  
 
       ///   
       /// 获取字段   
       ///   
       ///   
       ///   
       ///   
        public List GetColumnField(string connection, string TableName)  
        {  
            List Columnlist = new List();  
            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;  
        }