dataprovider.cs

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

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;

namespace isqlweb
{
    #region 委托

    #region DeleteDatabaseObjectHandle 删除数据库对象委托
    /// <summary>
    /// 删除数据库对象委托
    /// </summary>
    public delegate void DeleteDatabaseObjectHandle(System.Data.SqlClient.SqlConnection conn, string dbname, string name);
    #endregion

    #region AsyncExecutePostBackHandle 异步执行返回后的处理委托
    /// <summary>
    /// 异步执行返回后的处理委托
    /// </summary>
    /// <param name="result"></param>
    /// <param name="ex"></param>
    public delegate void AsyncExecutePostBackHandle(object result,Exception ex);
    #endregion

    #region InvokeHandle 调用的委托，仅指定为 Form的Invoke方法。
    /// <summary>
    /// 调用的委托，仅指定为 Form的Invoke方法。
    /// </summary>
    /// <param name="method"></param>
    /// <param name="args"></param>
    /// <returns></returns>
    public delegate object InvokeHandle(Delegate method, params object[] args); 
    #endregion

    #endregion

    #region DatabaseServer 数据库服务器信息
    /// <summary>
    /// 数据库服务器信息
    /// </summary>
    public class DatabaseServer
    {
        /// <summary>
        /// 服务器名
        /// </summary>
        public string ServerName = string.Empty;

        /// <summary>
        /// 验证方式 0.Windows 身份验证 1.SQL Server 身份验证(默认)
        /// </summary>
        public byte ValidType = 1;

        /// <summary>
        /// 用户名
        /// </summary>
        public string UserName = string.Empty;

        /// <summary>
        /// 密码
        /// </summary>
        public string Password = string.Empty;
    } 
    #endregion

    /// <summary>
    /// 数据库处理类
    /// </summary>
    public static class DataProvider
    {
        #region 连接相关

        #region CreateConn 创建一个数据库连接
        /// <summary>
        /// 创建一个数据库连接
        /// </summary>
        public static SqlConnection CreateConn(DatabaseServer server)
        {
            return CreateConn(server, server.Password);
        }

        /// <summary>
        /// 创建一个数据库连接
        /// </summary>
        public static SqlConnection CreateConn(DatabaseServer server, string pwd)
        {
            return CreateConn(server, pwd, false);
        }
        /// <summary>
        /// 创建一个数据库连接
        /// </summary>
        /// <param name="server"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public static SqlConnection CreateConn(DatabaseServer server, string pwd, bool trySql2005)
        {
            if (server.ValidType == 0)
            {
                SqlConnection conn = new SqlConnection(
                    string.Format("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source={0};" //Asynchronous Processing=true; 
                        , server.ServerName.Replace(";", "")));
                if (trySql2005 && GetServerVersion(conn) == ServerVersion.SqlServer2005)
                {
                    conn = new SqlConnection(
                    string.Format("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source={0};Asynchronous Processing=true;"
                        , server.ServerName.Replace(";", "")));
                }
                return conn;
            }
            else
            {
                SqlConnection conn = new SqlConnection(string.Format("server={0};uid={1};pwd={2};database=master;" //Asynchronous Processing=true;
                    , server.ServerName.Replace(";", ""), server.UserName.Replace(";", ""), pwd.Replace(";", "")));
                if (trySql2005 && GetServerVersion(conn) == ServerVersion.SqlServer2005)
                {
                    conn = new SqlConnection(string.Format("server={0};uid={1};pwd={2};database=master;Asynchronous Processing=true;"
                    , server.ServerName.Replace(";", ""), server.UserName.Replace(";", ""), pwd.Replace(";", "")));
                }
                return conn;
            }
        }
        #endregion

        #region OpenConnection 打开连接
        /// <summary>
        /// 打开连接
        /// </summary>
        /// <param name="conn"></param>
        public static void OpenConnection(SqlConnection conn)
        {
            if (conn == null) return;
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
        } 
        #endregion

        #region CloseConnection 关闭连接
        /// <summary>
        /// 关闭连接
        /// </summary>
        /// <param name="conn"></param>
        public static void CloseConnection(SqlConnection conn)
        {
            if (conn == null) return;

            try
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
            }
            catch { }
        }
        #endregion

        #region TestConnection 测试连接
        /// <summary>
        /// 测试连接
        /// </summary>
        public static bool TestConnection(DatabaseServer server, out string infoString)
        {
            SqlConnection conn = CreateConn(server);
            return TestConnection(conn, out infoString);
        }
        /// <summary>
        /// 测试连接
        /// </summary>
        public static bool TestConnection(SqlConnection conn, out string infoString)
        {
            try
            {
                OpenConnection(conn);
                infoString = "连接成功。";
                return true;
            }
            catch (Exception ex)
            {
                infoString = "连接失败。\n原因:" + ex.Message;
                return false;
            }
        }
        #endregion 

        #endregion

        #region 数据库结构相关

        #region ServerVersion 服务器版本
        /// <summary>
        /// 服务器版本 
        /// </summary>
        public enum ServerVersion
        {
            /// <summary>
            /// 未知数据库
            /// </summary>
            Unknown,
            /// <summary>
            /// SqlServer2000数据库
            /// </summary>
            SqlServer2000,
            /// <summary>
            /// SqlServer2005数据库
            /// </summary>
            SqlServer2005
        }
        #endregion

        #region GetServerVersion 获取服务器版本
        /// <summary>
        /// 获取服务器版本
        /// </summary>
        /// <param name="ver"></param>
        /// <returns></returns>
        public static ServerVersion GetServerVersion(SqlConnection conn)
        {
            ServerVersion ver = ServerVersion.Unknown;

            try
            {
                if (conn != null && conn.State != ConnectionState.Open)
                    conn.Open();

                if (conn != null && conn.State == ConnectionState.Open)
                {
                    if (conn.ServerVersion.Substring(0, 2) == "08")
                    {
                        ver = ServerVersion.SqlServer2000;
                    }
                    else if (conn.ServerVersion.Substring(0, 2) == "09")
                    {
                        ver = ServerVersion.SqlServer2005;
                    }
                }

                //if (conn != null && conn.State == ConnectionState.Open)
                //    conn.Close();
            }
            catch { }

            return ver;
        }
        #endregion

        #region KillConnectionByDatabase 强制关闭连接到指定数据库的用户
        /// <summary>
        /// 强制关闭连接到指定数据库的用户
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="dbname"></param>
        public static void KillConnectionByDatabase(SqlConnection conn, string dbname)
        {
            string sql = @"
              declare @dbname varchar(200)
              set @dbname = '{0}'
              declare @sql nvarchar(500)       
              declare @spid nvarchar(20)   

              declare #tb cursor for
                select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
              open #tb
              fetch next from #tb into @spid
              while @@fetch_status=0
              begin
                exec('kill   '+@spid)
                fetch next from #tb into @spid
              end  close #tb  deallocate #tb";

            sql = string.Format(sql, dbname);
            Execute(conn, sql);
        } 
        #endregion

        #region GetAllDatabase 获取所有数据库
        /// <summary>
        /// 获取所有数据库
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllDatabase(SqlConnection conn)
        {
            string sysdb = "'master','model','msdb','tempdb','distribution'";
            //return ExecuteDataTable(conn
            //    , "select [name] from sysdatabases where cast(owner_sid as int)<>1 order by [name]");
            return ExecuteDataTable(conn
                , string.Format("select TOP 100 PERCENT [name] from sysdatabases where [name] not in({0}) order by [name]", sysdb));
        }
        #endregion

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

            DataTable dt = ExecuteDataTable(conn, 
                //string.Format("select [name] from sysdatabases where [name]='{0}' and cast(owner_sid as int)<>1 order by [name]", dbname) );
                string.Format("select [name] from sysdatabases where [name]='{0}'", dbname));

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

        #region GetDatabaseFiles 获取数据库的文件列表
        /// <summary>
        /// 获取数据库的文件列表
        /// </summary>
        public static DataTable GetDatabaseFiles(SqlConnection conn, string dbname)
        {
            return ExecuteDataTable(conn, "select * from sysfiles", dbname);
        } 
        #endregion