调用本文的数据库操作基类-->http://www.opent.cn/a/2009/2/4/830.shtml

 

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace EC
{
    /// <summary>
    /// 数据库操作常用函数,创建，删除，数据库，创建，删除数据库表,用户必须拥有master权限
    /// </summary>
   public class CommonDB
    {
       private string _sql;
       public DbObject DB;

       public string Sql
       {
           get { return this._sql; }
           set { this._sql = value; }
       }

        #region 构造与析构函数
       public CommonDB()
       {
           DB = new DbObject();
       }
       public CommonDB(string conn)
       {
         
          DB = new DbObject(conn);
       }

       ~CommonDB()
         {
             DB.Dispose();
             Dispose();
         }

         public void Dispose()
         {           
             GC.SuppressFinalize(this);
         }
       #endregion

        #region 创建数据库
         /// <summary>
       /// 创建数据库
        /// </summary>
        /// <param name="DbName">数据库名</param>
        /// <returns></returns>
        public int CreateDB(string DbName)
        {
            string sql = "create database " + DbName;
            if (IsExistDB(DbName))
            {
                this._sql += sql + "{$}创建不成功,数据库存在{|}";
                return 0;
            }
            else
            {
                DbObject db = new DbObject();
                int rn = db.ExecuteSql(sql);

                db.Dispose();
                this._sql += sql + "{$}" + rn + "{|}";
                return rn;
            }
        }
      

        #endregion

        #region 判读数据库名是否已存在
       /// <summary>
        /// 判读数据库名是否已存在
       /// </summary>
       /// <param name="DbName">数据库名</param>
       /// <returns></returns>

        public bool IsExistDB(string DbName)
        {
            bool Rn=false;

          
            string sql = "select 1 from master.dbo.sysdatabases where name = " + DbName + "";

            DataSet ds = DB.GetDataSet(sql);

            if (ds.Tables[0].Rows.Count == 1)
            {
                Rn = true;
            }

            ds.Clear();
            ds.Dispose();
          
            this._sql += sql + "{$}是否存在," + Rn + "{|}";
            return Rn;
        }

        #endregion

        #region 删除数据库
       /// <summary>
        /// 删除数据库
       /// </summary>
       /// <param name="DbName">数据库名</param>
       /// <returns></returns>
        public int DelDB(string DbName)
        {
            string sql = "drop database " + DbName;

            if (!IsExistDB(DbName))
            {
                this._sql += sql + "{$}删除不成功,数据库不存在{|}";
                return 0;
            }
            else
            {

                int Rn = DB.ExecuteSql(sql);
             
                this._sql += sql + "{$}" + Rn + "{|}";
                return Rn;
            }
        }

        #endregion

        #region 查询数据库中已存在的所有数据库

       /// <summary>
        /// 查询数据库中已存在的所有数据库
       /// </summary>
       /// <returns></returns>
        public DataSet SelAllDb()
        {
            string sql = "select * from master.dbo.sysdatabases where name <>master and name <>tempdb and name<>model and name<>msdb and name<>ReportServer and name<>ReportServerTempDB";

            DataSet ds = DB.GetDataSet(sql);
            //ds.Clear();
            //ds.Dispose();
           
            this._sql += sql + "{$}"+ ds.Tables[0].Rows.Count +"{|}";
            return ds;      

        }
        #endregion

        #region 查询数据库某一数据库的详细信息
       /// <summary>
        /// 查询数据库某一数据库的详细信息
       /// </summary>
       /// <param name="DbName">数据库名</param>
       /// <returns></returns>
        public DataSet SelDB(string DbName)
        {
          
            string sql = "select * from master.dbo.sysdatabases where name = " + DbName + "";

            DataSet ds = DB.GetDataSet(sql);

            //ds.Clear();
            //ds.Dispose();
          
            this._sql += sql + "{$}" + ds.Tables[0].Rows.Count + "{|}";
            return ds;
       
        }

        #endregion

        #region 获取数据库中的所有表
       /// <summary>
        /// 获取数据库中的所有表
       /// </summary>
       /// <returns></returns>
        public DataSet SelTbl()
        {
                  
            string sql = "select name from sysobjects where xtype = u order by name asc";

            DataSet ds = DB.GetDataSet(sql);

            this._sql = sql + "{$}" + ds.Tables[0].Rows.Count + "{|}";
            return ds;       
        }

        #endregion

        #region 查询数据库表中的结构详细信息
       /// <summary>
        /// 查询数据库表中的结构详细信息
       /// </summary>
       /// <param name="tblname">数据表名</param>
       /// <returns></returns>
        public DataSet SelTblDetail(string tblname)
        {
        

            string sql = "SELECT"
                        +" TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N END,"
                        +" TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N),"
                        +" Column_id=C.column_id,"
                        +" ColumnName=C.name,"
                        +" PrimaryKey=ISNULL(IDX.PrimaryKey,N),"
                        +" [IDENTITY]=CASE WHEN C.is_identity=1 THEN N√ELSE N END,"
                        +" Computed=CASE WHEN C.is_computed=1 THEN N√ELSE N END,"
                        +" Type=T.name,"
                        +" Length=C.max_length,"
                        +" Precision=C.precision,"
                        +" Scale=C.scale,"
                        +" NullAble=CASE WHEN C.is_nullable=1 THEN N√ELSE N END,"
                        +" [Default]=ISNULL(D.definition,N),"
                        +" ColumnDesc=ISNULL(PFD.[value],N),"
                        +" IndexName=ISNULL(IDX.IndexName,N),"
                        +" IndexSort=ISNULL(IDX.Sort,N),"
                        +" Create_Date=O.Create_Date,"
                        +" Modify_Date=O.Modify_date"
                        +" FROM sys.columns C"
                        +" INNER JOIN sys.objects O"
                        +" ON C.[object_id]=O.[object_id]"
                        +"  AND O.type=U"
                        +"  AND O.is_ms_shipped=0"
                        +"  INNER JOIN sys.types T"
                        +"  ON C.user_type_id=T.user_type_id"
                        +" LEFT JOIN sys.default_constraints D"
                        +"  ON C.[object_id]=D.parent_object_id"
                        +"       AND C.column_id=D.parent_column_id"
                        +"        AND C.default_object_id=D.[object_id]"
                        +" LEFT JOIN sys.extended_properties PFD"
                        +"  ON PFD.class=1 "
                        +"       AND C.[object_id]=PFD.major_id "
                        +"        AND C.column_id=PFD.minor_id"
                     //   +"--             AND PFD.name=Caption  -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)"
                        +"  LEFT JOIN sys.extended_properties PTB"
                        +"      ON PTB.class=1 "
                        +"        AND PTB.minor_id=0 "
                        +"        AND C.[object_id]=PTB.major_id"
                     //   +"--             AND PFD.name=Caption  -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) "

                        +"  LEFT JOIN"                      // -- 索引及主键信息"
                        +"("
                        +"    SELECT "
                        +"      IDXC.[object_id],"
                        +"      IDXC.column_id,"
                        +"      Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,IsDescending)"
                        +"         WHEN 1 THEN DESC WHEN 0 THEN ASC ELSE END,"
                        +"     PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N√ELSE N END,"
                        +"      IndexName=IDX.Name"
                        +"  FROM sys.indexes IDX"
                        +" INNER JOIN sys.index_columns IDXC"
                        +"      ON IDX.[object_id]=IDXC.[object_id]"
                        +"        AND IDX.index_id=IDXC.index_id"
                        +"  LEFT JOIN sys.key_constraints KC"
                        +"     ON IDX.[object_id]=KC.[parent_object_id]"
                        +"           AND IDX.index_id=KC.unique_index_id"
                        +" INNER JOIN  "//-- 对于一个列包含多个索引的情况,只显示第1个索引信息"
                        +"  ("
                        +"    SELECT [object_id], Column_id, index_id=MIN(index_id)"
                        +"    FROM sys.index_columns"
                        +"     GROUP BY [object_id], Column_id"
                        +"  ) IDXCUQ"
                        +"      ON IDXC.[object_id]=IDXCUQ.[object_id]"
                        +"         AND IDXC.Column_id=IDXCUQ.Column_id"
                        +"          AND IDXC.index_id=IDXCUQ.index_id"
                        + " ) IDX"
                        +"  ON C.[object_id]=IDX.[object_id]"
                        +"     AND C.column_id=IDX.column_id "

                        +" WHERE O.name=N"+ tblname +"  "//     -- 如果只查询指定表,加上此条件"
                        +"ORDER BY O.name,C.column_id ";

            //System.Web.HttpContext.Current.Response.Write(sql);
            //System.Web.HttpContext.Current.Response.End();
            DataSet ds = DB.GetDataSet(sql);
            this._sql = sql + "{$}" + ds.Tables[0].Rows.Count + "{|}";
            return ds;
       
     }

        #endregion

        #region 通用读取某一表单数据
       /// <summary>
        /// 通用读取某一表单数据
       /// </summary>
       /// <param name="tbl">数据表名</param>
       /// <param name="fld">读取字段</param>
       /// <param name="where">条件语句不用加where</param>
       /// <returns></returns>
        public DataSet Read(string tbl, string fld, string where)
        {
           
            SqlParameter[] ps ={ new SqlParameter("@tbl", tbl),
                             new SqlParameter("@fld",fld),
                             new SqlParameter("@where", where)
        };
            DataSet ds = DB.RunProcedureGetDataSet("pr_Read", ps);
            return ds;

        }

        #endregion

        #region 判断指定的表是否存在
       /// <summary>
        /// 判断指定的表是否存在
       /// </summary>
       /// <param name="tbl">表名</param>
       /// <returns></returns>
        public bool IsExistTbl(string tbl)
        {
            bool Rn=false;

          
            string sql = "select name from sysobjects where xtype = u and name=" + tbl + "";

            DataSet ds = DB.GetDataSet(sql);

            if (ds.Tables[0].Rows.Count == 1)
            {
                Rn = true;
            }

            ds.Clear();
            ds.Dispose();
          
          
            return Rn;
        }
    
        #endregion
    }
}