C#_获取 SQL服务器列表

 

#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