dataprovider.cs

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

        #region GetBakDatabaseFiles 获取备份数据库的文件列表
        /// <summary>
        /// 获取备份数据库的文件列表
        /// </summary>
        public static DataTable GetBakDatabaseFiles(SqlConnection conn, string filename)
        {
            return ExecuteDataTable(conn, string.Format("RESTORE FILELISTONLY From DISK = N'{0}'", filename));
        }
        #endregion

        #region DetachDatabase 分离数据库
        /// <summary>
        /// 分离数据库
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        public static void DetachDatabase(SqlConnection conn, string dbname)
        {
            Execute(conn, string.Format("EXEC master.dbo.sp_detach_db @dbname = N'{0}', @keepfulltextindexfile=N'true'", dbname));
        } 
        #endregion

        #region DeleteDatabase 删除数据库
        /// <summary>
        /// 删除数据库
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="databaseName"></param>
        public static void DeleteDatabase(SqlConnection conn, string databaseName)
        {
            databaseName = databaseName.TrimStart('[').TrimEnd(']');
            KillConnectionByDatabase(conn, databaseName);
            Execute(conn, string.Format("DROP DATABASE [{0}]", databaseName));
        } 
        #endregion

        #region BackupDatabase 备份数据库
        /// <summary>
        /// 同步备份数据库(建议使用异步操作)
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="databaseName">指定要备份的数据库</param>
        /// <param name="filename">备份到</param>
        public static void BackupDatabase(SqlConnection conn, string databaseName, string filename)
        {
            Execute(conn, BackupDatabase_SqlString(databaseName, filename));
        }
        /// <summary>
        /// 备份数据库的SQL语句
        /// </summary>
        /// <param name="databaseName">指定要备份的数据库</param>
        /// <param name="filename">备份到</param>
        /// <returns></returns>
        public static string BackupDatabase_SqlString(string databaseName, string filename)
        {
            databaseName = databaseName.TrimStart('[').TrimEnd(']');
            string sql = "BACKUP DATABASE [{0}] TO  DISK = N'{1}' WITH FORMAT, INIT,  NAME = N'{0}-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10";
            sql = string.Format(sql, databaseName, filename);
            return sql;
        }
        #endregion

        #region RestoreDatabase 还原数据库

        #region 还原数据库(数据文件保存位置由bak文件中的设置决定)
        /// <summary>
        /// 还原数据库(数据文件保存位置由bak文件中的设置决定)
        /// </summary>
        /// <param name="sourcebak">备份文件</param>
        /// <param name="conn">还原到该连接的数据库服务器上</param>
        /// <param name="databaseName">还原后的数据库名称</param>
        public static void RestoreDatabase(string sourcebak, SqlConnection conn, string databaseName)
        {
            RestoreDatabase(sourcebak, conn, databaseName, string.Empty, false);
        } 
        #endregion

        #region 还原数据库(不尝试使用原来的数据文件保存位置)
        /// <summary>
        /// 还原数据库(不尝试使用原来的数据文件保存位置)
        /// </summary>
        /// <param name="sourcebak">备份文件</param>
        /// <param name="conn">还原到该连接的数据库服务器上</param>
        /// <param name="databaseName">还原后的数据库名称</param>
        /// <param name="targetDataPath">还原后的数据文件保存位置</param>
        public static void RestoreDatabase(string sourcebak, SqlConnection conn, string databaseName, string targetDataPath)
        {
            RestoreDatabase(sourcebak, conn, databaseName, targetDataPath, false);
        } 
        #endregion

        #region 同步还原数据库(建议使用异步操作)
        /// <summary>
        /// 还原数据库
        /// </summary>
        /// <param name="sourcebak">备份文件</param>
        /// <param name="conn">还原到该连接的数据库服务器上</param>
        /// <param name="databaseName">还原后的数据库名称</param>
        /// <param name="targetDataPath">还原后的数据文件保存位置</param>
        /// <param name="tryUseOldPath">尝试使用原来的数据文件保存位置</param>
        public static void RestoreDatabase(string sourcebak, SqlConnection conn, string databaseName, string targetDataPath, bool tryUseOldPath)
        {
            string sql = RestoreDatabase_SqlString(sourcebak, conn, databaseName, targetDataPath, tryUseOldPath);

            //包函此数据库
            if (ContainsDatabase(conn, databaseName))
            {
                KillConnectionByDatabase(conn, databaseName); //关闭连接到该数据库的连接用户
                DetachDatabase(conn, databaseName); //分离该数据库
            }

            Execute(conn, sql, int.MaxValue);
        } 
        #endregion

        #region 还原数据库的SQL语句
        /// <summary>
        /// 还原数据库的SQL语句
        /// </summary>
        /// <param name="sourcebak">备份文件</param>
        /// <param name="conn">还原到该连接的数据库服务器上</param>
        /// <param name="databaseName">还原后的数据库名称</param>
        /// <param name="targetDataPath">还原后的数据文件保存位置</param>
        /// <param name="tryUseOldPath">尝试使用原来的数据文件保存位置</param>
        public static string RestoreDatabase_SqlString(string sourcebak, SqlConnection conn, string databaseName, string targetDataPath, bool tryUseOldPath)
        {
            databaseName = databaseName.TrimStart('[').TrimEnd(']');

            //bool targetDatabaseIsContains = false; //目标数据库是否存在

            #region sql_move 组装sql代码_关于文件移动的
            string sql_move = string.Empty;

            //求文件列表
            DataTable dt_sourcefiles = GetBakDatabaseFiles(conn, sourcebak);

            #region 求 targetDataPath
            if (ContainsDatabase(conn, databaseName)) //尝试使用原来的数据文件保存位置
            {
                //targetDatabaseIsContains = true; //目标数据库存在

                DataTable dt001 = GetDatabaseFiles(conn, databaseName);
                if (dt001 != null && dt001.Rows.Count > 0)
                {
                    targetDataPath = Path.GetDirectoryName(dt001.Rows[0]["filename"].ToString());
                }
            }
            else if (targetDataPath == string.Empty) //没有指定数据文件保存位置，则以bak文件中的设置为准
            {
                targetDataPath = Path.GetDirectoryName(dt_sourcefiles.Rows[0]["PhysicalName"].ToString());
            }
            #endregion

            //开始组装
            int datafileLength = 0;
            int logfileLength = 0;
            for (int i = 0; i < dt_sourcefiles.Rows.Count; i++)
            {
                string logicalName = Utils.Parse<string>(dt_sourcefiles.Rows[i]["logicalName"]);
                string __filetype = Utils.Parse<string>(dt_sourcefiles.Rows[i]["type"]).ToUpper();
                string __ext = __filetype == "D" ? ".mdf" : __filetype == "L" ? ".ldf" : "";

                sql_move += ",";
                sql_move += string.Format("MOVE N'{0}'", logicalName);
                #region __filename 求文件名
                string __filename = string.Empty;
                if (__filetype == "D")
                {
                    if (datafileLength == 0)
                        __filename = databaseName;
                    else
                        __filename = databaseName + datafileLength.ToString();
                    datafileLength++;
                }
                else if (__filetype == "L")
                {
                    if (logfileLength == 0)
                        __filename = databaseName + "_log";
                    else
                        __filename = databaseName + "_log" + logfileLength.ToString();
                    logfileLength++;
                }
                #endregion
                sql_move += string.Format("TO N'{0}'", Path.Combine(targetDataPath, __filename + __ext));
            }

            #endregion

            string sql = "RESTORE DATABASE [{0}] FROM DISK = N'{1}' WITH  FILE = 1 {2}  ,NOUNLOAD,  REPLACE,  STATS = 10";
            sql = string.Format(sql, databaseName, sourcebak, sql_move);

            return sql;
        } 
        #endregion

        #endregion

        #region ContainsObject 检查是否有此对象
        /// <summary>
        /// 检查是否有此对象
        /// </summary>
        /// <returns></returns>
        public static bool ContainsObject(SqlConnection conn, string dbname, string name)
        {
            name = name.Replace("'", "");

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

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

        #region GetAllTable 获取所有表
        private const string sql__select_all_table = "select top 100 percent [name] from sysobjects where type='U' order by [name]";
        /// <summary>
        /// 获取所有表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable GetAllTable(SqlConnection conn, string dbname)
        {

            return ExecuteDataTable(conn
                , sql__select_all_table
                , dbname);
        } 
        #endregion

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

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

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

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

        #region DeleteTableData 清除表数据
        /// <summary>
        /// 清除表数据
        /// </summary>
        public static void DeleteTableData(SqlConnection conn, string dbname, string name)
        {
            dbname = dbname.TrimStart('[').TrimEnd(']');
            Execute(conn, string.Format("Delete From [{0}]", name), dbname);
        }
        #endregion

        //列相关

        #region GetAllColumns 获取指定表中的所有列
        private const string sql__select_all_columns = @"select top 100 percent 
case 
when b.variable=1 then
  b.name + '(' + cast(isnull(a.prec,0) as varchar(9)) + ')'
when isnull(a.prec,0)<>isnull(b.prec,0) and a.scale is null then
  b.name + '(' + cast(isnull(a.prec,0) as varchar(9)) + ')'
when isnull(a.prec,0)<>isnull(b.prec,0) or isnull(a.scale,0)<>isnull(b.scale,0) then
  b.name + '(' + cast(isnull(a.prec,0) as varchar(9)) + ',' + cast(isnull(a.scale,0) as varchar(9)) + ')'
else
  b.name 
end 'typestring',
a.*
from syscolumns a
left join systypes b
on a.xtype=b.xtype and a.xusertype=b.xusertype
where a.id=object_id('{0}') order by colid";
        /// <summary>
        /// 获取指定表中的所有列
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        /// <returns></returns>
        public static DataTable GetAllColumns(SqlConnection conn, string dbname,string table)
        {
            return ExecuteDataTable(conn
                , string.Format(sql__select_all_columns,table)
                , dbname);
        }
        #endregion

        //列相关_end

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

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