方法一:
1.如果附加数据库时,没有指定逻辑名,则在SQL2005上显示为“全路径,且为大写”。比如,数据库文件D:***est.mdf和D:***est.ldf,附加到SQL服务器上时,如果没有指定逻辑名,则在SQL2005上显示为“D:***EST.MDF”。此时,C#的连接字符串为“DataSource=dsName;AttatchDBFilename=”D:My***t.mdf”;User ID=id;Password=pw”;
2.如果附加数据库时,指定了逻辑名,则在SQL2005上显示为“逻辑名”,此时,C#的连接字符串为“DataSource=dsName;AttatchDBFilename=”D:My***t.mdf”;Initial Catalog=aa_LogicName;User ID=id;Password=pw”;
3.被附加的数据库名称不可随意更改;否则容易出现错误。
方法二:
string Db***h=System.Environment.CurrentDirectory De***Data.MDF">+@"De***Data.MDF";
string Lo***th=System.Environment.CurrentDirectory De***Log.LDF">+@"De***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();
cm***xecuteNonQuery();
cn.Close();
方法三详细代码:
using System;
using Sy***m.Collections.Generic;
using Sy***m.Windows.Forms;
using Sy***m.Data.SqlClient;
using Sy***m.Data;
using Sy***m.ServiceProcess;
namespace Ad***ZJC.DataBaseControl
{
///
/// 数据库操作控制类
///
public class DataBaseControl
{
///
/// 数据库连接字符串
///
public string ConnectionString;
///
/// SQL操作语句/存储过程
///
public string StrSQL;
///
/// 实例化一个数据库连接对象
///
private SqlConnection Conn;
///
/// 实例化一个新的数据库操作对象Comm
///
private SqlCommand Comm;
///
/// 要操作的数据库名称
///
public string DataBaseName;
///
/// 数据库文件完整地址
///
public string DataBase_MDF;
///
/// 数据库日志文件完整地址
///
public string DataBase_LDF;
///
/// 备份文件名
///
public string DataBaseOfBackupName;
///
/// 备份文件路径
///
public string DataBaseOfBackupPath;
///
/// 执行创建/修改数据库和表的操作
///
public void DataBaseAndTableControl()
{
try
{
Conn = new SqlConnection(ConnectionString);
Co***Open();
Comm = new SqlCommand();
Co***Connection = Conn;
Co***CommandText = StrSQL;
Co***CommandType = Co***ndType.Text;
Co***ExecuteNonQuery();
Me***geBox.Show("数据库操作成功!", "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
catch (Exception ex)
{
Me***geBox.Show(ex***ssage, "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
finally
{
Co***Close();
}
}
///
/// 附加数据库
///
public void AddDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
Co***Open();
Comm = new SqlCommand();
Co***Connection = Conn;
Co***CommandText = "sp_attach_db";
Co***Parameters.Add(new SqlParameter(@"dbname", Sq***Type.NVarChar));
Co***Parameters[@"dbname"].Value = DataBaseName;
Co***Parameters.Add(new SqlParameter(@"filename1", Sq***Type.NVarChar));
Co***Parameters[@"filename1"].Value = DataBase_MDF;
Co***Parameters.Add(new SqlParameter(@"filename2", Sq***Type.NVarChar));
Co***Parameters[@"filename2"].Value = DataBase_LDF;
Co***CommandType = Co***ndType.StoredProcedure;
Co***ExecuteNonQuery();
Me***geBox.Show("附加数据库成功", "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
catch (Exception ex)
{
Me***geBox.Show(ex***ssage, "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
finally
{
Co***Close();
}
}
///
/// 分离数据库
///
public void DeleteDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
Co***Open();
Comm = new SqlCommand();
Co***Connection = Conn;
Co***CommandText = @"sp_detach_db";
Co***Parameters.Add(new SqlParameter(@"dbname", Sq***Type.NVarChar));
Co***Parameters[@"dbname"].Value = DataBaseName;
Co***CommandType = Co***ndType.StoredProcedure;
Co***ExecuteNonQuery();
Me***geBox.Show("分离数据库成功", "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
catch (Exception ex)
{
Me***geBox.Show(ex***ssage, "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
finally
{
Co***Close();
}
}
///
/// 备份数据库
///
public void BackupDataBase()
{
try
{
Conn = new SqlConnection(ConnectionString);
Co***Open();
Comm = new SqlCommand();
Co***Connection = Conn;
Co***CommandText = "use master;backup database @dbname to disk = @backupname;";
Co***Parameters.Add(new SqlParameter(@"dbname", Sq***Type.NVarChar));
Co***Parameters[@"dbname"].Value = DataBaseName;
Co***Parameters.Add(new SqlParameter(@"backupname", Sq***Type.NVarChar));
Co***Parameters[@"backupname"].Value = @DataBaseOfBackupPath + @DataBaseOfBackupName;
Co***CommandType = Co***ndType.Text;
Co***ExecuteNonQuery();
Me***geBox.Show("备份数据库成功", "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
catch (Exception ex)
{
Me***geBox.Show(ex***ssage, "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
finally
{
Co***Close();
}
}
///
/// 还原数据库
///
public void ReplaceDataBase()
{
try
{
string BackupFile = @DataBaseOfBackupPath + @DataBaseOfBackupName;
Conn = new SqlConnection(ConnectionString);
Co***Open();
Comm = new SqlCommand();
Co***Connection = Conn;
Co***CommandText = "use master;restore database @DataBaseName From disk = @BackupFile with replace;";
Co***Parameters.Add(new SqlParameter(@"DataBaseName", Sq***Type.NVarChar));
Co***Parameters[@"DataBaseName"].Value = DataBaseName;
Co***Parameters.Add(new SqlParameter(@"BackupFile", Sq***Type.NVarChar));
Co***Parameters[@"BackupFile"].Value = BackupFile;
Co***CommandType = Co***ndType.Text;
Co***ExecuteNonQuery();
Me***geBox.Show("还原数据库成功", "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
catch (Exception ex)
{
Me***geBox.Show(ex***ssage, "信息提示", Me***geBoxButtons.OK, Me***geBoxIcon.Information);
}
finally
{
Co***Close();
}
}
}
}
/*
///调用事例:
#----------------------------------------------------还原数据库---------------------------------------------------------#
private void button0_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DB***onnectionString = "Data Source=(local);User id=sa;Password=123456; Initial Catalog=master";
DB***ataBaseName = "MyDatabase";
DB***ataBaseOfBackupName = @"back.bak";
DB***ataBaseOfBackupPath = @"D:Program FilesMicrosoft SQL ServerMSSQLData";
DB***eplaceDataBase();
}
#--------------------------------------------------附加数据库---------------------------------------------------------#
private void button1_Click_1(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DB***onnectionString = "Data Source=(local);User id=sa;Password=123456; Initial Catalog=master";
DB***ataBaseName = "MyDatabase";
DB***ataBase_MDF = @"D:Program FilesMicrosoft SQL ServerMSSQLDataMy***abase_Data.MDF";
DB***ataBase_LDF = @"D:Program FilesMicrosoft SQL ServerMSSQLDataMy***abase_Log.LDF";
DB***ddDataBase();
}
#--------------------------------------备份数据库--------------------------------------------------------------------#
private void button2_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DB***onnectionString = "Data Source=(local);User id=sa;Password=123456; Initial Catalog=master";
DB***ataBaseName = "MyDatabase";
DB***ataBaseOfBackupName = @"back.bak";
DB***ataBaseOfBackupPath = @"D:Program FilesMicrosoft SQL ServerMSSQLData";
DB***ackupDataBase();
}
#----------------------------------------分离数据库-----------------------------------------------------------------#
private void button3_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DB***onnectionString = "Data Source=(local);User id=sa;Password=123456; Initial Catalog=master";
DB***ataBaseName = "MyDatabase";
DB***eleteDataBase();
上一篇: asp.net网站静态化