c# 通过配置自动附加数据库
系统
2505 0
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();
}
c# 通过配置自动附加数据库
更多文章、技术交流、商务合作、联系博主
微信扫码或搜索：z360901061
微信扫一扫加我为好友
QQ号联系： 360901061
您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。
【本文对您有帮助就好】元
