using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Configuration;
namespace 数据库小助手
{
public class DataBaseControl
{
/// <summary>
/// 数据库连接字符串
/// </summary>
string ConnectionString = ConfigurationManager.ConnectionStrings["connstr"].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();
}
}
/// <summary>
/// 获取所以的数据库
/// </summary>
/// <returns>数据库</returns>
public List<string> getDatabase()
{
List<string> datas = new List<string>();
string sql = "select name from sysdatabases ";
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand(sql, Conn);
try
{
SqlDataReader reader = Comm.ExecuteReader();
while (reader.Read())
{
datas.Add(reader[0].ToString());
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
Conn.Close();
}
return datas;
}
}
}
【主窗体调用代码】
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
namespace 数据库小助手
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
addDataBase();
}
/// <summary>
/// 显示数据文件
/// </summary>
private void addDataBase()
{
DataBaseControl dbc = new DataBaseControl();
List<string> datas = dbc.getDatabase();
this.comboBox1.Items.Clear();
this.comboBox2.Items.Clear();
this.comboBox3.Items.Clear();
for (int i = 0; i < datas.Count; i++)
{
this.comboBox1.Items.Add(datas[i]);
this.comboBox2.Items.Add(datas[i]);
this.comboBox3.Items.Add(datas[i]);
}
comboBox1.SelectedIndex = 0;
comboBox2.SelectedIndex = 0;
comboBox3.SelectedIndex = 0;
}
//备份数据库
private void button1_Click(object sender, EventArgs e)
{
saveFileDialog1.Filter = "数据库备份文件*.bak|*.bak";
saveFileDialog1.Title = string.Format("将数据库{0}备份到...",comboBox1.Text);
saveFileDialog1.FileName = string.Format("{0}_{1}{2:00}{3:00}",comboBox1.Text,DateTime.Now.Year,DateTime.Now.Month,DateTime.Now.Day);
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
this.toolStripStatusLabel1.Text = string.Format("正在对数据库{0}进行备份操作,请不要做其它操作.....",comboBox1.Text);
string fileName = saveFileDialog1.FileName;
string backupPath = fileName.Substring(0, fileName.LastIndexOf("//"));
// MessageBox.Show(backupPath);
string backupName = fileName.Substring(fileName.LastIndexOf("//") + 1);
DataBaseControl DBC = new DataBaseControl();
DBC.DataBaseName = comboBox1.Text;
DBC.DataBaseOfBackupName = backupName;
DBC.DataBaseOfBackupPath = backupPath + "/";
DBC.BackupDataBase();
this.toolStripStatusLabel1.Text = "完成";
}
}
//还原
private void button2_Click(object sender, EventArgs e)
{
openFileDialog1.Filter = "数据库备份文件*.bak|*.bak";
openFileDialog1.Title = string.Format("从备份文件中还原数据库{0}", comboBox2.Text);
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
string fileName = openFileDialog1.FileName;
string backupPath = fileName.Substring(0, fileName.LastIndexOf("//")) + "//";
DataBaseControl DBC = new DataBaseControl();
DBC.DataBaseName = comboBox2.Text;
DBC.DataBaseOfBackupName = openFileDialog1.SafeFileName;
DBC.DataBaseOfBackupPath = backupPath;
DBC.ReplaceDataBase();
}
}
//分离数据
private void button4_Click(object sender, EventArgs e)
{
DataBaseControl DBC = new DataBaseControl();
DBC.DataBaseName = comboBox3.Text;
DBC.DeleteDataBase();
addDataBase();
}
string dataName="";//数据库名称
//附加
private void button3_Click(object sender, EventArgs e)
{
if (textBox1.Text.Trim() != "")
{
if (textBox2.Text.Trim() != "")
{
DataBaseControl DBC = new DataBaseControl();
DBC.DataBaseName = dataName;
DBC.DataBase_MDF = textBox1.Text;
DBC.DataBase_LDF = textBox2.Text;
DBC.AddDataBase();
addDataBase();
}
else
{
MessageBox.Show("请选择ldf数据库文件");
}
}
else
{
MessageBox.Show("请选择mdf数据库文件");
}
}
//打开主数据文件
private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
openFileDialog1.Filter = "主数据库文件*.MDF|*.MDF";
openFileDialog1.Title = "打开主数据库文件";
openFileDialog1.FileName = "";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
this.textBox1.Text = openFileDialog1.FileName;
dataName = openFileDialog1.SafeFileName;
dataName = dataName.Substring(0, dataName.LastIndexOf("."));
}
}
//打开事务日志文件
private void linkLabel2_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
openFileDialog1.Filter = "事务日志文件*.LDF|*.LDF";
openFileDialog1.Title = "打开事务日志文件";
openFileDialog1.FileName = "";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
this.textBox2.Text = openFileDialog1.FileName;
}
}
private void linkLabel3_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
panel1.Visible = true;
}
private void button5_Click(object sender, EventArgs e)
{
panel1.Visible = false;
}
//鼠标悬停事件
private void btn_MouseLeave(object sender, EventArgs e)
{
this.toolStripStatusLabel1.Text = "完成";
}
private void btn_MouseEnter(object sender, EventArgs e)
{
Button btn = (Button)sender;
switch (btn.Text)
{
case "备份":
this.toolStripStatusLabel1.Text = string.Format("对数据库{0}进行备份操作", comboBox1.Text);
break;
case "还原":
this.toolStripStatusLabel1.Text = string.Format("对数据库{0}进行还原操作", comboBox2.Text);
break;
case "分离":
this.toolStripStatusLabel1.Text = string.Format("对数据库{0}进行分离操作", comboBox3.Text);
break;
case "附加":
this.toolStripStatusLabel1.Text = string.Format("附加现有的数据库文件到数据库中");
break;
}
}
}
}
【程序效果图】