近段時間學習了如何用安裝包將數據庫一起附加進去代替了以往需要用戶手工附加的方式
/// <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);
ConnOpen();
Comm = new SqlCommand();
CommConnection = Conn;
CommCommandText = StrSQL;
CommCommandType = CommandTypeText;
CommExecuteNonQuery();
MessageBoxShow(數據庫操作成功！ 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
catch (Exception ex)
{
MessageBoxShow(exMessage 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
finally
{
ConnClose();
}
}
/// <summary>
/// 附加數據庫
/// </summary>
public void AddDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
ConnOpen();
Comm = new SqlCommand();
CommConnection = Conn;
CommCommandText = @sp_attach_db;
CommParametersAdd(new SqlParameter(@dbname SqlDbTypeNVarChar));
CommParameters[@dbname]Value = DataBaseName;
CommParametersAdd(new SqlParameter(@filename SqlDbTypeNVarChar));
CommParameters[@filename]Value = DataBase_MDF;
CommParametersAdd(new SqlParameter(@filename SqlDbTypeNVarChar));
CommParameters[@filename]Value = DataBase_LDF;
CommCommandType = CommandTypeStoredProcedure; //此處一定要用存儲過程還有我也認識到存儲過程的參數的寫法不同與SQL語句在查詢分析器中寫附加數據庫的語法為exec sp_attach_db @dbname=N數據庫名@filename=NMDF文件路徑@filename=NLDF文件路徑按照我的推測存儲過程方式添加參數後它的值直接跟在後面了
CommExecuteNonQuery();
MessageBoxShow(附加數據庫成功 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
catch (Exception ex)
{
MessageBoxShow(exMessage 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
finally
{
ConnClose();
}
}
/// <summary>
/// 分離數據庫
/// </summary>
public void DeleteDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
ConnOpen();
Comm = new SqlCommand();
CommConnection = Conn;
CommCommandText = sp_detach_db;
CommParametersAdd(new SqlParameter(@dbname SqlDbTypeNVarChar));
CommParameters[@dbname]Value = DataBaseName;
CommCommandType = CommandTypeStoredProcedure;
CommExecuteNonQuery();
MessageBoxShow(分離數據庫成功 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
catch (Exception ex)
{
MessageBoxShow(exMessage 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
finally
{
ConnClose();
}
}
/// <summary>
/// 備份數據庫
/// </summary>
public void BackupDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
ConnOpen();
Comm = new SqlCommand();
CommConnection = Conn;
CommCommandText = use master;backup database @dbname to disk = @backupname;;
CommParametersAdd(new SqlParameter(@dbname SqlDbTypeNVarChar));
CommParameters[@dbname]Value = DataBaseName;
CommParametersAdd(new SqlParameter(@backupname SqlDbTypeNVarChar));
CommParameters[@backupname]Value = @DataBaseOfBackupPath + @DataBaseOfBackupName;
//這裡添加參數的方式就跟我以前的做法一樣只不過他加了@我認識加@是個好習慣防止特殊字符被轉義我以後也采用這種方式
CommCommandType = CommandTypeText;
CommExecuteNonQuery();
MessageBoxShow(備份數據庫成功 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
catch (Exception ex)
{
MessageBoxShow(exMessage 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
finally
{
ConnClose();
}
}
/// <summary>
/// 還原數據庫
/// </summary>
public void ReplaceDataBase()
{
try
{
string BackupFile = @DataBaseOfBackupPath + @DataBaseOfBackupName;
Conn = new SqlConnection(ConnectionString);
ConnOpen();
Comm = new SqlCommand();
CommConnection = Conn;
CommCommandText = use master;restore database @DataBaseName From disk = @BackupFile with replace;;
CommParametersAdd(new SqlParameter(@DataBaseName SqlDbTypeNVarChar));
CommParameters[@DataBaseName]Value = DataBaseName;
CommParametersAdd(new SqlParameter(@BackupFile SqlDbTypeNVarChar));
CommParameters[@BackupFile]Value = BackupFile;
CommCommandType = CommandTypeText;
CommExecuteNonQuery();
MessageBoxShow(還原數據庫成功 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
catch (Exception ex)
{
MessageBoxShow(exMessage 信息提示 MessageBoxButtonsOK MessageBoxIconInformation);
}
finally
{
ConnClose();
}
}
}
有了這些方法我就可以在我的安裝類中附加數據庫了接下來我要學習一下如何讓用戶安裝時有下拉框選擇
測試代碼如下
private void btnAttach_Click(object sender EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBCConnectionString = Data Source=(local);User id=用戶名;Password=密碼; Initial Catalog=master;
DBCDataBaseName = LabelLive;
DBCDataBase_MDF = @C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\Data\LabelLiveMDF;
DBCDataBase_LDF = @C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL\Data\LabelLive_LogLDF;
DBCAddDataBase();
}
private void btnDetach_Click(object sender EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBCConnectionString = Data Source=(local);User id=用戶名;Password=密碼; Initial Catalog=master;
DBCDataBaseName = LabelLive;
DBCDeleteDataBase();
}
private void btnBackup_Click(object sender EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBCConnectionString = Data Source=(local);User id=用戶名;Password=密碼; Initial Catalog=master;
DBCDataBaseName = LabelLive;
DBCDataBaseOfBackupName = @LabelLivebak;
DBCDataBaseOfBackupPath = @D:\;
DBCBackupDataBase();
}
private void btnRestore_Click(object sender EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBCConnectionString = Data Source=(local);User id=用戶名;Password=密碼; Initial Catalog=master;
DBCDataBaseName = LabelLive;
DBCDataBaseOfBackupName = @LabelLivebak;
DBCDataBaseOfBackupPath = @D:\;
DBCReplaceDataBase();
}
From:http://tw.wingwit.com/Article/program/net/201311/12075.html
