using
System;
using
System.Collections.Generic;
using
System.Windows.Forms;
using
System.Data.SqlClient;
using
System.Data;
using
System.ServiceProcess;
namespace
AdminZJC.DataBaseControl
{
/// <summary>
/// 數(shù)據(jù)庫(kù)操作控制類
/// </summary>
public
class
DataBaseControl
{
/// <summary>
/// 數(shù)據(jù)庫(kù)連接字符串
/// </summary>
public
string
ConnectionString;
/// <summary>
/// SQL操作語(yǔ)句/存儲(chǔ)過(guò)程
/// </summary>
public
string
StrSQL;
/// <summary>
/// 實(shí)例化一個(gè)數(shù)據(jù)庫(kù)連接對(duì)象
/// </summary>
private
SqlConnection Conn;
/// <summary>
/// 實(shí)例化一個(gè)新的數(shù)據(jù)庫(kù)操作對(duì)象Comm
/// </summary>
private
SqlCommand Comm;
/// <summary>
/// 要操作的數(shù)據(jù)庫(kù)名稱
/// </summary>
public
string
DataBaseName;
/// <summary>
/// 數(shù)據(jù)庫(kù)文件完整地址
/// </summary>
public
string
DataBase_MDF;
/// <summary>
/// 數(shù)據(jù)庫(kù)日志文件完整地址
/// </summary>
public
string
DataBase_LDF;
/// <summary>
/// 備份文件名
/// </summary>
public
string
DataBaseOfBackupName;
/// <summary>
/// 備份文件路徑
/// </summary>
public
string
DataBaseOfBackupPath;
/// <summary>
/// 執(zhí)行創(chuàng)建/修改數(shù)據(jù)庫(kù)和表的操作
/// </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(
"數(shù)據(jù)庫(kù)操作成功！"
,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
(Exception ex)
{
MessageBox.Show(ex.Message,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary>
/// 附加數(shù)據(jù)庫(kù)
/// </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(
"附加數(shù)據(jù)庫(kù)成功"
,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
(Exception ex)
{
MessageBox.Show(ex.Message,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary>
/// 分離數(shù)據(jù)庫(kù)
/// </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(
"分離數(shù)據(jù)庫(kù)成功"
,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
(Exception ex)
{
MessageBox.Show(ex.Message,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary>
/// 備份數(shù)據(jù)庫(kù)
/// </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(
"備份數(shù)據(jù)庫(kù)成功"
,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
(Exception ex)
{
MessageBox.Show(ex.Message,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
/// <summary>
/// 還原數(shù)據(jù)庫(kù)
/// </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(
"還原數(shù)據(jù)庫(kù)成功"
,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch
(Exception ex)
{
MessageBox.Show(ex.Message,
"信息提示"
, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
}
}
/*
///調(diào)用事例：
還原數(shù)據(jù)庫(kù)
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();
}
附加數(shù)據(jù)庫(kù)
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();
}
備份數(shù)據(jù)庫(kù)
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();
}
分離數(shù)據(jù)庫(kù)
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();
}
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
