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();
}
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
