本文是针对在C#中如何用安装包将数据库一起附加进去，代替了以往需要用户手工附加的方式的讲解.C#实战系列教程之分析Dialog控件讲解，C#实战系列教程合集正在整理中，后续会编成电子书在论坛供大家下载，请关注。
///
/// 数据库操作控制类
///
public class DataBaseControl
{
///
/// 数据库连接字符串
///
public string ConnectionString;
///
///
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);
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();
}
}
///
/// 附加数据库
///
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"].＆#118alue = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"filename1", SqlDbType.NVarChar));
Comm.Parameters[@"filename1"].＆#118alue = DataBase_MDF;
Comm.Parameters.Add(new SqlParameter(@"filename2", SqlDbType.NVarChar));
Comm.Parameters[@"filename2"].＆#118alue = DataBase_LDF;
Comm.CommandType = CommandType.StoredProcedure; //此处一定要用存储过程，还有我也认识到存储过程的参数的写法不同与SQL语句。在查询分析器中写附加数据库的语法为exec sp_attach_db @dbname=N'数据库名',@filename1=N'MDF文件路径',@filename2=N'LDF文件路径',按照我的推测，存储过程方式添加参数后它的值直接跟在后面了。
Comm.ExecuteNonQuery();
MessageBox.Show("附加数据库成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
}
///
/// 分离数据库
///
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"].＆#118alue = 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();
}
}
///
/// 备份数据库
///
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"].＆#118alue = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"backupname", SqlDbType.NVarChar));
Comm.Parameters[@"backupname"].＆#118alue = @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();
}
}
///
/// 还原数据库
///
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"].＆#118alue = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"BackupFile", SqlDbType.NVarChar));
Comm.Parameters[@"BackupFile"].＆#118alue = 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 btnAttach_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBC.ConnectionString = "Data Source=(local);User id=用户名;Password=密码; Initial Catalog=master";
DBC.DataBaseName = "LabelLive";
DBC.DataBase_MDF = @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LabelLive.MDF";
DBC.DataBase_LDF = @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LabelLive_Log.LDF";
DBC.AddDataBase();
}
private void btnDetach_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBC.ConnectionString = "Data Source=(local);User id=用户名;Password=密码; Initial Catalog=master";
DBC.DataBaseName = "LabelLive";
DBC.DeleteDataBase();
}
private void btnBackup_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBC.ConnectionString = "Data Source=(local);User id=用户名;Password=密码; Initial Catalog=master";
DBC.DataBaseName = "LabelLive";
DBC.DataBaseOfBackupName = @"LabelLive.bak";
DBC.DataBaseOfBackupPath = @"D:\";
DBC.BackupDataBase();
}
private void btnRestore_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBC.ConnectionString = "Data Source=(local);User id=用户名;Password=密码; Initial Catalog=master";
DBC.DataBaseName = "LabelLive";
DBC.DataBaseOfBackupName = @"LabelLive.bak";
DBC.DataBaseOfBackupPath = @"D:\";
DBC.ReplaceDataBase();
}
原文地址：https://www.xin3721.com/Article/xin3721_Article_13780.html