dataprovider.cs

基于vs2.0开发的WEB管理SQL功能该软件是一个用于管理SQL Server的工具

        #endregion

        #region ContainsView 检查是否有此视图
        /// <summary>
        /// 检查是否有此视图
        /// </summary>
        /// <returns></returns>
        public static bool ContainsView(SqlConnection conn, string dbname, string viewname)
        {
            viewname = viewname.Replace("'", "");

            DataTable dt = ExecuteDataTable(conn
                , string.Format("select [name] from sysobjects where [name]='{0}' and type='V' order by [name]", viewname)
                , dbname);

            if (dt == null) return false;
            if (dt.Rows.Count == 0) return false;
            return true;
        }
        #endregion

        #region DeleteView 删除视图
        /// <summary>
        /// 删除视图
        /// </summary>
        public static void DeleteView(SqlConnection conn, string dbname, string name)
        {
            dbname = dbname.TrimStart('[').TrimEnd(']');
            Execute(conn, string.Format("DROP View [{0}]", name), dbname);
        }
        #endregion

        #region GetAllProcedure 获取所有存储过程
        /// <summary>
        /// 获取所有存储过程
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable GetAllProcedure(SqlConnection conn, string dbname)
        {

            return ExecuteDataTable(conn
                , "select [name] from sysobjects where type='P' order by [name]"
                , dbname);
        }
        #endregion

        #region ContainsProcedure 检查是否有此存储过程
        /// <summary>
        /// 检查是否有此存储过程
        /// </summary>
        /// <returns></returns>
        public static bool ContainsProcedure(SqlConnection conn, string dbname, string procname)
        {
            procname = procname.Replace("'", "");

            DataTable dt = ExecuteDataTable(conn
                , string.Format("select [name] from sysobjects where [name]='{0}' and type='P' order by [name]", procname)
                , dbname);

            if (dt == null) return false;
            if (dt.Rows.Count == 0) return false;
            return true;
        }
        #endregion

        #region GetProcedureCreateSql 获取创建存储过程的SQL语句
        /// <summary>
        /// 获取创建存储过程的SQL语句
        /// </summary>
        /// <returns></returns>
        public static string GetProcedureCreateSql(SqlConnection conn, string dbname, string name,string rename)
        {
            string result = string.Empty;
            DataTable dt = null;
            string tempname = string.Empty;
            if (!string.IsNullOrEmpty(rename)) //重命名
            {
                tempname = RenameObject(conn, dbname, name); //改成一个不会和其他对象冲突的名称
                dt = ExecuteDataTable(conn, string.Format("sp_helptext [{0}]", tempname), dbname); //获取创建SQL
                RenameObject(conn, dbname, tempname, name); //名称改回来
            }

            if(dt==null)
                dt = ExecuteDataTable(conn, string.Format("sp_helptext [{0}]", name), dbname); //获取创建SQL
            
            if (dt == null) return string.Empty;

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string row = dt.Rows[i]["text"].ToString();
                if (row.Length == 255)
                    result += row;
                else
                    result += row + "\r\n";
            }

            //
            if (!string.IsNullOrEmpty(rename))
                result.Replace(tempname, rename);

            return result;

            //return ParseObjectCreateSql(dt, rename, new string[] { "proc", "procedure" });
        }
        #endregion

        #region DeleteProcedure 删除存储过程
        /// <summary>
        /// 删除存储过程
        /// </summary>
        public static void DeleteProcedure(SqlConnection conn, string dbname, string name)
        {
            dbname = dbname.TrimStart('[').TrimEnd(']');
            Execute(conn, string.Format("DROP Procedure [{0}]", name), dbname);
        }
        #endregion

        #region GetAllFunction 获取所有函数
        /// <summary>
        /// 获取所有函数
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable GetAllFunction(SqlConnection conn, string dbname)
        {

            return ExecuteDataTable(conn
                , "select [name] from sysobjects where (type='TF' or type='FN' or type='IF') order by [name]"
                , dbname);
        }
        #endregion

        #region ContainsFunction 检查是否有此函数
        /// <summary>
        /// 检查是否有此函数
        /// </summary>
        /// <returns></returns>
        public static bool ContainsFunction(SqlConnection conn, string dbname, string funname)
        {
            funname = funname.Replace("'", "");

            DataTable dt = ExecuteDataTable(conn
                , string.Format("select [name] from sysobjects where [name]='{0}' and (type='TF' or type='FN' or type='IF') order by [name]", funname)
                , dbname);

            if (dt == null) return false;
            if (dt.Rows.Count == 0) return false;
            return true;
        }
        #endregion

        #region DeleteFunction 删除函数
        /// <summary>
        /// 删除函数
        /// </summary>
        public static void DeleteFunction(SqlConnection conn, string dbname, string name)
        {
            dbname = dbname.TrimStart('[').TrimEnd(']');
            Execute(conn, string.Format("DROP Function [{0}]", name), dbname);
        }
        #endregion

        #region GetAllTrigger 获取所有触发器
        /// <summary>
        /// 获取所有触发器
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable GetAllTrigger(SqlConnection conn, string dbname)
        {

            return ExecuteDataTable(conn
                //, "select a.[name],b.[name] 'TableName' from sysobjects a inner join sysobjects b on a.parent_object_id=b.[object_id] and a.type='TR' and a.is_ms_shipped=0 order by a.[name]"
                , "select a.[name],b.[name] 'TableName' from sysobjects a inner join sysobjects b on a.parent_obj=b.[id] and a.type='TR' order by a.[name]"
                , dbname);
        }
        #endregion

        #region ContainsTrigger 检查是否有此触发器
        /// <summary>
        /// 检查是否有此触发器
        /// </summary>
        /// <returns></returns>
        public static bool ContainsTrigger(SqlConnection conn, string dbname, string triname)
        {
            triname = triname.Replace("'", "");

            DataTable dt = ExecuteDataTable(conn
                //, string.Format("select [name] from sysobjects where [name]='{0}' and type='TR' and is_ms_shipped=0 order by [name]", triname)
                , string.Format("select [name] from sysobjects where [name]='{0}' and type='TR' order by [name]", triname)
                , dbname);

            if (dt == null) return false;
            if (dt.Rows.Count == 0) return false;
            return true;
        }
        #endregion

        #region DeleteTrigger 删除触发器
        /// <summary>
        /// 删除触发器
        /// </summary>
        public static void DeleteTrigger(SqlConnection conn, string dbname, string name)
        {
            dbname = dbname.TrimStart('[').TrimEnd(']');
            Execute(conn, string.Format("DROP Trigger [{0}]", name), dbname);
        }
        #endregion

        #region ParseObjectCreateSql 解释创建数据库对象的SQL语句 [未完成][否决的]
        /// <summary>
        /// 解释创建数据库对象的SQL语句 [未完成][否决的]
        /// </summary>
        /// <param name="dt">sp_texthelp查到的结果</param>
        /// <param name="rename">重命名</param>
        /// <param name="create_keys">创建的关键字</param>
        /// <returns></returns>
        public static string ParseObjectCreateSql(DataTable dt, string rename, string[] create_keys)
        {
            string result = string.Empty;
            if (rename == null || rename == string.Empty) //不重命名
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string row = dt.Rows[i]["text"].ToString();
                    if (row.Length == 255)
                        result += row;
                    else
                        result += row + "\r\n";
                }
            }
            else  //重命名
            {
                string row = string.Empty; //一个行的数据
                bool isFindCreate = false; //是否找到create关键字
                bool isFindObjectKey = false; //是否找到创建的对象的关键字
                bool isFindObjectName = false; //是否找到对象的名字
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    //搜集一个行
                    row += dt.Rows[i]["text"].ToString();
                    if (row.Length == 255)
                    {
                        continue;
                    }
                    else
                    {
                        result += row + "\r\n";
                    }

                    ////该行是注释
                    //if (row.Trim().Substring(0, 2) == "--")
                    //{

                    //}

                    //开始解释
                    if (isFindCreate == false || isFindObjectKey == false || isFindObjectName == false)
                    {
                        bool isFindCreate_currRow = false; //是否找到create关键字_在当前行
                        do
                        {
                            string[] rowKeys = row.Split(new char[] { ' ', '\t' }, StringSplitOptions.RemoveEmptyEntries);

                            if (!isFindCreate) //开始寻找create关键字
                            {

                            }
                            else if (!isFindObjectKey) //开始寻找创建对象的关键字
                            {
                            }

                        } while (isFindCreate_currRow == true); //如果在当前行找到create，则继续查找
                    }
                }
            }
            return result;
        } 
        #endregion

        #region RenameDbObject 重命名数据库对象
        /// <summary>
        /// 重命名数据库对象
        /// </summary>
        public static void RenameObject(SqlConnection conn, string dbname,string name, string rename)
        {
            name = name.Replace("'", "").TrimStart('[').TrimEnd(']');
            rename = rename.Replace("'", "").TrimStart('[').TrimEnd(']');
            Execute(conn, string.Format("sp_rename '[{0}]','{1}'", name, rename), dbname);
        }
        /// <summary>
        /// 重命名数据库对象(取个随机名称)
        /// </summary>
        public static string RenameObject(SqlConnection conn, string dbname, string name)
        {
            string newname = string.Empty;
            do
            {
                newname = Guid.NewGuid().ToString();
            } while (ContainsObject(conn, dbname, newname));

            RenameObject(conn, dbname, name, newname);
            return newname;
        }
        #endregion

        #region HelpText 获取对象创建时的文本
        /// <summary>
        /// 获取对象创建时的文本
        /// </summary>
        public static string HelpText(SqlConnection conn, string name, string dbname)
        {
            string result = string.Empty;
            DataTable dt = ExecuteDataTable(conn, string.Format("sp_helptext [{0}]", name), dbname); //获取创建SQL

            if (dt == null) return string.Empty;

            for (int i = 0; i < dt.Rows.Count; i++)
            {