using?System;
using?System.Collections.Generic;
using?System.Windows.Forms;
using?System.Data.SqlClient;
using?System.Data;
using?System.ServiceProcess;

namespace?AdminZJC.DataBaseControl
{
?///?<summary>
?///?數據庫操作控制類
?///?</summary>
?public?class?DataBaseControl
?{
?///?<summary>
?///?數據庫連接字符串
?///?</summary>
?public?string?ConnectionString;

?///?<summary>
?///?SQL操作語句/存儲過程
?///?</summary>
?public?string?StrSQL;

?///?<summary>
?///?實例化一個數據庫連接對象
?///?</summary>
?private?SqlConnection?Conn;

?///?<summary>
?///?實例化一個新的數據庫操作對象Comm
?///?</summary>
?private?SqlCommand?Comm;

?///?<summary>
?///?要操作的數據庫名稱
?///?</summary>
?public?string?DataBaseName;

?///?<summary>
?///?數據庫文件完整地址
?///?</summary>
?public?string?DataBase_MDF;

?///?<summary>
?///?數據庫日志文件完整地址
?///?</summary>
?public?string?DataBase_LDF;

?///?<summary>
?///?備份文件名
?///?</summary>
?public?string?DataBaseOfBackupName;

?///?<summary>
?///?備份文件路徑
?///?</summary>
?public?string?DataBaseOfBackupPath;

?///?<summary>
?///?執行創建/修改數據庫和表的操作
?///?</summary>
?public?void?DataBaseAndTableControl()
?{
?try
?{
?Conn?=?new?SqlConnection(ConnectionString);
?Conn.Open();

?Comm?=?new?SqlCommand();
?Comm.Connection?=?Conn;
?Comm.CommandText?=?StrSQL;
?Comm.CommandType?=?CommandType.Text;
?Comm.ExecuteNonQuery();

?MessageBox.Show("數據庫操作成功！",?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?catch?(Exception?ex)
?{
?MessageBox.Show(ex.Message,?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?finally
?{
?Conn.Close();
?}
?}

?///?<summary>
?///?附加數據庫
?///?</summary>
?public?void?AddDataBase()
?{
?try
?{
?Conn?=?new?SqlConnection(ConnectionString);
?Conn.Open();

?Comm?=?new?SqlCommand();
?Comm.Connection?=?Conn;
?Comm.CommandText?=?"sp_attach_db";

?Comm.Parameters.Add(new?SqlParameter(@"dbname",?SqlDbType.NVarChar));
?Comm.Parameters[@"dbname"].Value?=?DataBaseName;
?Comm.Parameters.Add(new?SqlParameter(@"filename1",?SqlDbType.NVarChar));
?Comm.Parameters[@"filename1"].Value?=?DataBase_MDF;
?Comm.Parameters.Add(new?SqlParameter(@"filename2",?SqlDbType.NVarChar));
?Comm.Parameters[@"filename2"].Value?=?DataBase_LDF;

?Comm.CommandType?=?CommandType.StoredProcedure;
?Comm.ExecuteNonQuery();

?MessageBox.Show("附加數據庫成功",?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?catch?(Exception?ex)
?{
?MessageBox.Show(ex.Message,?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?finally
?{
?Conn.Close();
?}
?}

?///?<summary>
?///?分離數據庫
?///?</summary>
?public?void?DeleteDataBase()
?{
?try
?{
?Conn?=?new?SqlConnection(ConnectionString);
?Conn.Open();

?Comm?=?new?SqlCommand();
?Comm.Connection?=?Conn;
?Comm.CommandText?=?@"sp_detach_db";

?Comm.Parameters.Add(new?SqlParameter(@"dbname",?SqlDbType.NVarChar));

?Comm.Parameters[@"dbname"].Value?=?DataBaseName;

?Comm.CommandType?=?CommandType.StoredProcedure;
?Comm.ExecuteNonQuery();

?MessageBox.Show("分離數據庫成功",?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?catch?(Exception?ex)
?{
?MessageBox.Show(ex.Message,?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?finally
?{
?Conn.Close();
?}
?}

?///?<summary>
?///?備份數據庫
?///?</summary>
?public?void?BackupDataBase()
?{
?try
?{
?Conn?=?new?SqlConnection(ConnectionString);
?Conn.Open();

?Comm?=?new?SqlCommand();
?Comm.Connection?=?Conn;
?Comm.CommandText?=?"use?master;backup?database?@dbname?to?disk?=?@backupname;";

?Comm.Parameters.Add(new?SqlParameter(@"dbname",?SqlDbType.NVarChar));
?Comm.Parameters[@"dbname"].Value?=?DataBaseName;
?Comm.Parameters.Add(new?SqlParameter(@"backupname",?SqlDbType.NVarChar));
?Comm.Parameters[@"backupname"].Value?=?@DataBaseOfBackupPath?+?@DataBaseOfBackupName;

?Comm.CommandType?=?CommandType.Text;
?Comm.ExecuteNonQuery();

?MessageBox.Show("備份數據庫成功",?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?catch?(Exception?ex)
?{
?MessageBox.Show(ex.Message,?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?finally
?{
?Conn.Close();
?}
?}

?///?<summary>
?///?還原數據庫
?///?</summary>
?public?void?ReplaceDataBase()
?{
?try
?{
?string?BackupFile?=?@DataBaseOfBackupPath?+?@DataBaseOfBackupName;
?Conn?=?new?SqlConnection(ConnectionString);
?Conn.Open();

?Comm?=?new?SqlCommand();
?Comm.Connection?=?Conn;
?Comm.CommandText?=?"use?master;restore?database?@DataBaseName?From?disk?=?@BackupFile?with?replace;";

?Comm.Parameters.Add(new?SqlParameter(@"DataBaseName",?SqlDbType.NVarChar));
?Comm.Parameters[@"DataBaseName"].Value?=?DataBaseName;
?Comm.Parameters.Add(new?SqlParameter(@"BackupFile",?SqlDbType.NVarChar));
?Comm.Parameters[@"BackupFile"].Value?=?BackupFile;

?Comm.CommandType?=?CommandType.Text;
?Comm.ExecuteNonQuery();

?MessageBox.Show("還原數據庫成功",?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?catch?(Exception?ex)
?{
?MessageBox.Show(ex.Message,?"信息提示",?MessageBoxButtons.OK,?MessageBoxIcon.Information);
?}
?finally
?{
?Conn.Close();
?}
?}
?}
}

/*
?///調用事例：
?
?　　　還原數據庫
?private?void?button0_Click(object?sender,?EventArgs?e)
?{
?DataBaseControl?DBC?=?new?DataBaseControl();
?DBC.ConnectionString?=?"Data?Source=(local);User?id=sa;Password=123456;?Initial?Catalog=master";
?DBC.DataBaseName?=?"MyDatabase";
?DBC.DataBaseOfBackupName?=?@"back.bak";
?DBC.DataBaseOfBackupPath?=?@"D:/Program?Files/Microsoft?SQL?Server/MSSQL/Data/";
?DBC.ReplaceDataBase();
?}
?
　　　　附加數據庫
?private?void?button1_Click_1(object?sender,?EventArgs?e)
?{
?DataBaseControl?DBC?=?new?DataBaseControl();
?DBC.ConnectionString?=?"Data?Source=(local);User?id=sa;Password=123456;?Initial?Catalog=master";
?DBC.DataBaseName?=?"MyDatabase";
?DBC.DataBase_MDF?=?@"D:/Program?Files/Microsoft?SQL?Server/MSSQL/Data/MyDatabase_Data.MDF";
?DBC.DataBase_LDF?=?@"D:/Program?Files/Microsoft?SQL?Server/MSSQL/Data/MyDatabase_Log.LDF";
?DBC.AddDataBase();
?}
?
　　　　備份數據庫
?private?void?button2_Click(object?sender,?EventArgs?e)
?{
?DataBaseControl?DBC?=?new?DataBaseControl();
?DBC.ConnectionString?=?"Data?Source=(local);User?id=sa;Password=123456;?Initial?Catalog=master";
?DBC.DataBaseName?=?"MyDatabase";
?DBC.DataBaseOfBackupName?=?@"back.bak";
?DBC.DataBaseOfBackupPath?=?@"D:/Program?Files/Microsoft?SQL?Server/MSSQL/Data/";
?DBC.BackupDataBase();
?}
?
　　　　分離數據庫
?private?void?button3_Click(object?sender,?EventArgs?e)
?{
?DataBaseControl?DBC?=?new?DataBaseControl();
?DBC.ConnectionString?=?"Data?Source=(local);User?id=sa;Password=123456;?Initial?Catalog=master";
?DBC.DataBaseName?=?"MyDatabase";
?DBC.DeleteDataBase();
?}