SQLManager类

这个东西是我学C#和SQL2000时写来玩的，纯粹个人作品。

using System;
using System.Collections.Specialized;
using System.Data;
using System.Text;
using DataAccess;

//名称：SQLManager
//作者：firstrose
//说明：取得SQL数据库的一些信息

namespace sqlcmp.Module
{
 /// <summary>
 /// SQLMngr 的摘要说明。
 /// </summary>
 public class SQLManager
 {
  public SQLManager()
  {
   //
   // TODO: 在此处添加构造函数逻辑
   //
   ConnSQL();
  }

  private StringBuilder sql = new StringBuilder(50);
  private DataAccessSQL DASQL = new DataAccessSQL();
  private bool Connected=false;
  private DataTable dtCache;
  public StringCollection Result=new StringCollection();
  private static SQLManager _Instance;
  public static SQLManager GetInstance
  {
   get
   {
    if (_Instance==null)
    {
     _Instance=new SQLManager();
    }

    if (!_Instance.Connected)
    {
     throw(new ApplicationException("SQL Server未连接"));
    }

    return _Instance;
   }
  }

  public bool ConnSQL()
  {
   Connected=DASQL.Conn();

   return Connected;
  }

  public void DisConnSQL()
  {
   DASQL.DisConn();

   Connected=false;
  }

  /// <summary>
  /// 穷举SQL实例的数据库
  /// </summary>
  public bool EnumSQLDB()
  {
   bool flag=false;

   if (Connected)
   {
    sql.Length=0;
    sql.Append("USE MASTER;");
    sql.Append("Select Name From sysdatabases");
    DASQL.SQLSelect(sql.ToString());
    dtCache=DASQL.GetDataSet.Tables["Main"].Copy();
    Result.Clear();
    if (dtCache.Rows.Count>0)
    {
     //有数据
     foreach (DataRow temp in dtCache.Rows)
     {
      Result.Add((string)temp["Name"]);
     }
     flag=true;
    }
    else
    {}
   }
   else
   {}

   return flag;
  }

  /// <summary>
  /// 改变默认的数据库
  /// </summary>
  /// <param name="DBName"></param>
  public void ChangeSQLDB(string DBName)
  {
   if (Connected&&(DBName!=null)&&(DBName.Length!=0))
   {
    sql.Length=0;
    sql.AppendFormat("USE {0};",DBName);
    DASQL.SQLSelectNoResult(sql.ToString());
   }
   else
   {}
  }

  /// <summary>
  /// 取得一个数据库里所有表名
  /// </summary>
  public bool GetTables(string DBName, bool UserOnly)
  {
   bool flag=false;

   if (Connected&&(DBName!=null)&&(DBName.Length!=0))
   {
    sql.Length=0;
    sql.Append("Use MASTER;");
    sql.AppendFormat("Select Count(1) From SysDatabases Where Name='{0}'",DBName);
    Result.Clear();
    if ((int)DASQL.SQLSelectSingleValue(sql.ToString())==1)
    {
     //数据库存在
     sql.Length=0;
     sql.AppendFormat("Use {0};",DBName);
     sql.Append("Select Name,ID From SysObjects");
     if (UserOnly)
     {
      sql.Append(" Where XType='U' AND Status>0");
     }
     else
     {
      sql.Append(" Where XType='U' OR XType='S'");
     }

     DASQL.SQLSelect(sql.ToString());
     dtCache=DASQL.GetDataSet.Tables["Main"].Copy();
     
     Result.Clear();
     if (dtCache.Rows.Count>0)
     {
      foreach (DataRow temp in dtCache.Rows)
      {
       Result.Add((string)temp["Name"]);
      }
     }
     else
     {}

     flag=true;
    }
    else
    {}
   }
   else
   {}

   return flag;
  }

  /// <summary>
  /// 取得数据库里所有存储过程的名称
  /// </summary>
  /// DBName为null或空，使用当前数据库
  public bool GetSPName(string DBName,bool UserOnly)
  {
   bool flag=false;

   if (Connected)
   {
    sql.Length=0;
    if ((DBName!=null)&&(DBName.Length!=0))
    {
     sql.AppendFormat("Use {0};",DBName);
    }
    else
    {}
    sql.Append("Select Name From SysObjects Where XType='P'");
    if (UserOnly)
    {
     sql.Append(" AND Status>=0");
    }
    else
    {}
    DASQL.SQLSelect(sql.ToString());
    dtCache=DASQL.GetDataSet.Tables["Main"].Copy();

    Result.Clear();
    if (dtCache.Rows.Count>0)
    {
     //存在存储过程
     foreach (DataRow temp in DASQL.GetDataSet.Tables["Main"].Rows)
     {
      Result.Add((string)temp["Name"]);
     }
     flag=true;
    }
    else
    {}
   }
   else
   {}

   return flag;
  }

  /// <summary>
  /// 取得当前数据库里指定存储过程的内容
  /// </summary>
  public bool GetSPText(string SPName)
  {
   bool flag=false;

   if (Connected&&(SPName!=null)&&(SPName.Length!=0))
   {
    sql.Length=0;
    sql.AppendFormat("Select SysComments.Text From SysComments Left Join SysObjects On SysComments.ID=SysObjects.ID Where SysObjects.Name='{0}'",SPName);
    object temp=DASQL.SQLSelectSingleValue(sql.ToString());

    if (temp!=null)
    {
     Result.Clear();
     Result.Add((string)temp);
     flag=true;
    }
    else
    {}
   }
   else
   {}

   return flag;
  }

  /// <summary>
  /// 取得表中所有列名
  /// </summary>
  public bool EnumTableColumns(string TableName)
  {
   bool flag=false;

   if ((Connected)&&(TableName!=null)&&(TableName.Length!=0))
   {
    sql.Length=0;
    sql.AppendFormat("Select SysColumns.Name From SysColumns Left Outer Join SysObjects On SysColumns.ID=SysObjects.ID Where SysObjects.Name='{0}'",TableName);
    DASQL.SQLSelect(sql.ToString());
    dtCache=DASQL.GetDataSet.Tables["Main"].Copy();

    Result.Clear();
    if (dtCache.Rows.Count>0)//例行检查
    {
     foreach (DataRow temp in DASQL.GetDataSet.Tables["Main"].Rows)
     {
      Result.Add((string)temp["Name"]);
     }
     flag=true;
    }
    else
    {}
   }
   else
   {}

   return flag;
  }

 }
}