方法一：
1.如果附加數(shù)據(jù)庫(kù)時(shí),，沒(méi)有指定邏輯名，則在SQL2005上顯示為“全路徑,，且為大寫(xiě)”,。比如，數(shù)據(jù)庫(kù)文件D:MyTest.mdf和D:MyTest.ldf,，附加到SQL服務(wù)器上時(shí),，如果沒(méi)有指定邏輯名，則在SQL2005上顯示為“D:MYTEST.MDF”,。此時(shí),，C#的連接字符串為“DataSource=dsName;AttatchDBFilename=”D:\MyTest.mdf”;User ID=id;Password=pw”；
2.如果附加數(shù)據(jù)庫(kù)時(shí),，指定了邏輯名，則在SQL2005上顯示為“邏輯名”,，此時(shí),，C#的連接字符串為“DataSource=dsName;AttatchDBFilename=”D:\MyTest.mdf”;Initial Catalog=aa_LogicName;User ID=id;Password=pw”,；
3.被附加的數(shù)據(jù)庫(kù)名稱不可隨意更改；否則容易出現(xiàn)錯(cuò)誤,。
方法二：
string DbPath=System.Environment.CurrentDirectory +@"\Demo_Data.MDF";
string LogPath=System.Environment.CurrentDirectory +@"\Demo_Log.LDF";
string StrSql="exec sp_attach_db @dbname='supmark',@filename1='"+DbPath+"',@filename2='"+LogPath+"'";
string strcon="Server=(local);Integrated Security=SSPI;Database=master";
SqlConnection cn=new SqlConnection(strcon);
SqlCommand cmd =new SqlCommand (StrSql,cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
方法三詳細(xì)代碼：
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();
}
