C#創建數據庫 附加數據庫等操作

[csharp] 
/// <summary>  
/// 附加數據庫方法  
/// </summary>  
/// <param name="strSql">連接數據庫字符串，連接master系統數據庫</param>  
/// <param name="DataName">數據庫名字</param>  
/// <param name="strMdf">數據庫文件MDF的路徑</param>  
/// <param name="strLdf">數據庫文件LDF的路徑</param>  
/// <param name="path">安裝目錄</param>  
private   void  CreateDataBase( string  strSql, string  DataName,  string  strMdf,  string  strLdf, string  path) 
{ 
   SqlConnection myConn = new SqlConnection(strSql); 
   String str = null ; 
   try 
   { 
      str = " EXEC sp_attach_db @dbname='"+DataName+"',@filename1='"+strMdf+"',@filename2='"+strLdf+"'"; 
      SqlCommand myCommand = new SqlCommand(str, myConn); 
      myConn.Open(); 
      myCommand.ExecuteNonQuery(); 
      MessageBox.Show("數據庫安裝成功！點擊確定繼續");//需Using System.Windows.Forms  
   } 
   catch(Exception e) 
   { 
      MessageBox.Show("數據庫安裝失敗！" + e.Message+"\n\n"+"您可以手動附加數據"); 
      System.Diagnostics.Process.Start(path);//打開安裝目錄  
   } 
   finally 
   { 
      myConn.Close(); 
   } 
} 
 
public override void Install(System.Collections.IDictionary stateSaver) 
{ 
   string server = this.Context.Parameters["server"];//服務器名稱  
    string uid = this.Context.Parameters["user"];//SQlServer用戶名  
    string pwd = this.Context.Parameters["pwd"];//密碼  
    string path = this.Context.Parameters["targetdir"];//安裝目錄  
    string strSql = "server=" + server + ";uid=" + uid + ";pwd=" + pwd + ";database=master";//連接數據庫字符串  
    string DataName = "JXC";//數據庫名  
    string strMdf = path + @"JXC.mdf";//MDF文件路徑，這裡需注意文件名要與剛添加的數據庫文件名一樣！  
    string strLdf = path + @"jxc_log.ldf";//LDF文件路徑  
    base.Install(stateSaver); 
   this.CreateDataBase(strSql, DataName, strMdf, strLdf, path);//開始創建數據庫  
}  
 
 
    /// <summary>  
    /// 測試連接  
    /// </summary>  
    /// <param name="serverName"></param>  
    /// <param name="dbName"></param>  
    /// <param name="userName"></param>  
    /// <param name="password"></param>  
    private SqlConnection TestConnection(string serverName, string dbName, string userName, string password) 
    { 
        string connectionString = GetConnectionString(serverName, dbName, userName, password); 
        SqlConnection connection = new SqlConnection(connectionString); 
        try 
        { 
            if (connection.State != ConnectionState.Open) 
            { 
                connection.Open(); 
            } 
            return connection; 
        } 
        catch 
        { 
            CloseConnection(connection); 
            throw new InstallException("安裝失敗!\n數據庫配置有誤,請正確配置信息！"); 
        } 
    } 
 
 
    /// <summary>  
    /// 得到連接字符串  
    /// </summary>  
    /// <param name="serverName"></param>  
    /// <param name="dbName"></param>  
    /// <param name="userName"></param>  
    /// <param name="password"></param>  
    /// <returns></returns>  
    private string GetConnectionString(string serverName, string dbName, string userName, string password) 
    { 
        string connectionString = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}"; 
        connectionString = string.Format(connectionString, serverName, dbName, userName, password); 
        return connectionString; 
    } 
 
    /// <summary>  
    /// 創建數據庫  
    /// </summary>  
    /// <param name="serverName"></param>  
    /// <param name="dbName"></param>  
    /// <param name="userName"></param>  
    /// <param name="password"></param>  
    /// <param name="connection"></param>  
    /// <param name="stateSaver"></param>  
    public int CreateDataBase(SqlConnection connection) 
    { 
        int result = -1; 
        connection.ChangeDatabase("master"); 
        string createDBSql = @" if Exists(select 1 from sysdatabases where [name]=N'{0}') 
                                begin 
                                drop database {0} 
                                end 
                                GO  
                                CREATE DATABASE {0} "; 
        createDBSql = string.Format(createDBSql, _dbName); 
 
        //因為有Go在SQLCommand中不認識，所以以Go為分隔符取sql語句  
 
        char[] split = new char[] { 'G', 'O' }; 
        string[] sqlList = createDBSql.Split(split); 
 
        SqlCommand command = null; 
        try 
        { 
            command = connection.CreateCommand(); 
            command.CommandType = System.Data.CommandType.Text; 
            foreach (string sqlItem in sqlList) 
            { 
                if (sqlItem.Length > 2) 
                { 
                    command.CommandText = sqlItem; 
                    result = command.ExecuteNonQuery(); 
                } 
            } 
            return result; 
        } 
        catch 
        { 
            CloseConnection(connection); 
            command.Dispose(); 
            throw new InstallException("安裝失敗!\n數據庫配置不正確!"); 
        } 
    } 
 
    /// <summary>  
    /// 分隔SQL語句  
    /// </summary>  
    /// <param name="sql"></param>  
    /// <returns></returns>  
    private string[] splitSql(string sql) 
    { 
        Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline); 
        string[] sqlList = regex.Split(sql.ToUpper()); 
        return sqlList; 
    } 

    /// <summary>
    /// 附加數據庫方法
    /// </summary>
    /// <param name="strSql">連接數據庫字符串，連接master系統數據庫</param>
    /// <param name="DataName">數據庫名字</param>
    /// <param name="strMdf">數據庫文件MDF的路徑</param>
    /// <param name="strLdf">數據庫文件LDF的路徑</param>
    /// <param name="path">安裝目錄</param>
    private   void  CreateDataBase( string  strSql, string  DataName,  string  strMdf,  string  strLdf, string  path)
    {
       SqlConnection myConn = new SqlConnection(strSql);
       String str = null ;
       try
       {
          str = " EXEC sp_attach_db @dbname='"+DataName+"',@filename1='"+strMdf+"',@filename2='"+strLdf+"'";
          SqlCommand myCommand = new SqlCommand(str, myConn);
          myConn.Open();
          myCommand.ExecuteNonQuery();
          MessageBox.Show("數據庫安裝成功！點擊確定繼續");//需Using System.Windows.Forms
       }
       catch(Exception e)
       {
          MessageBox.Show("數據庫安裝失敗！" + e.Message+"\n\n"+"您可以手動附加數據");
          System.Diagnostics.Process.Start(path);//打開安裝目錄
       }
       finally
       {
          myConn.Close();
       }
    }
    
    public override void Install(System.Collections.IDictionary stateSaver)
    {
       string server = this.Context.Parameters["server"];//服務器名稱
        string uid = this.Context.Parameters["user"];//SQlServer用戶名
        string pwd = this.Context.Parameters["pwd"];//密碼
        string path = this.Context.Parameters["targetdir"];//安裝目錄
        string strSql = "server=" + server + ";uid=" + uid + ";pwd=" + pwd + ";database=master";//連接數據庫字符串
        string DataName = "JXC";//數據庫名
        string strMdf = path + @"JXC.mdf";//MDF文件路徑，這裡需注意文件名要與剛添加的數據庫文件名一樣！
        string strLdf = path + @"jxc_log.ldf";//LDF文件路徑
        base.Install(stateSaver);
       this.CreateDataBase(strSql, DataName, strMdf, strLdf, path);//開始創建數據庫
    }

     /// <summary>
        /// 測試連接
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="dbName"></param>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        private SqlConnection TestConnection(string serverName, string dbName, string userName, string password)
        {
            string connectionString = GetConnectionString(serverName, dbName, userName, password);
            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                if (connection.State != ConnectionState.Open)
                {
                    connection.Open();
                }
                return connection;
            }
            catch
            {
                CloseConnection(connection);
                throw new InstallException("安裝失敗!\n數據庫配置有誤,請正確配置信息！");
            }
        }

        /// <summary>
        /// 得到連接字符串
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="dbName"></param>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        private string GetConnectionString(string serverName, string dbName, string userName, string password)
        {
            string connectionString = "Data Source={0};Initial Catalog={1};User ID={2};Password={3}";
            connectionString = string.Format(connectionString, serverName, dbName, userName, password);
            return connectionString;
        }

        /// <summary>
        /// 創建數據庫
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="dbName"></param>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <param name="connection"></param>
        /// <param name="stateSaver"></param>
        public int CreateDataBase(SqlConnection connection)
        {
            int result = -1;
            connection.ChangeDatabase("master");
            string createDBSql = @" if Exists(select 1 from sysdatabases where [name]=N'{0}')
                                    begin
                                    drop database {0}
                                    end
                                    GO
                                    CREATE DATABASE {0} ";
            createDBSql = string.Format(createDBSql, _dbName);

            //因為有Go在SQLCommand中不認識，所以以Go為分隔符取sql語句

            char[] split = new char[] { 'G', 'O' };
            string[] sqlList = createDBSql.Split(split);

            SqlCommand command = null;
            try
            {
                command = connection.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                foreach (string sqlItem in sqlList)
                {
                    if (sqlItem.Length > 2)
                    {
                        command.CommandText = sqlItem;
                        result = command.ExecuteNonQuery();
                    }
                }
                return result;
            }
            catch
            {
                CloseConnection(connection);
                command.Dispose();
                throw new InstallException("安裝失敗!\n數據庫配置不正確!");
            }
        }

        /// <summary>
        /// 分隔SQL語句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        private string[] splitSql(string sql)
        {
            Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);
            string[] sqlList = regex.Split(sql.ToUpper());
            return sqlList;
        }