数据库小助手实现分离数据库/附加数据库等操作

2017-02-20 00:29:12  访问(1351) 赞(0) 踩(0)

using System;
using System.Collections.Generic;

using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Configuration;
using employment;
namespace 数据库小助手
{
public class DataBaseControl
{
///

/// 数据库连接字符串
///

// string ConnectionString = string.Format("Data Source=({0});User id={1};Password={2}; Initial Catalog=master",UserHelper.sql.servername,UserHelper.sql.uid,UserHelper.sql.password);
//string ConnectionString = "Data Source=(local);User id=sa;Password=123; Initial Catalog=master";

string ConnectionString = string.Format("Data Source={0};Initial Catalog=master;Persist Security Info=True;User ID={1};Password={2}", UserHelper.sql.servername, UserHelper.sql.uid, UserHelper.sql.password);
///

/// 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);
Conn.Open();

Comm = new SqlCommand();
Comm.Connection = Conn;
Comm.CommandText = StrSQL;
Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();

MyMessageBox.Show("数据库操作成功！", "信息提示");
}
catch (Exception ex)
{
MyMessageBox.Show(ex.Message, "信息提示");
}
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"].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();

MyMessageBox.Show("附加数据库成功", "信息提示");
}
catch (Exception ex)
{
MyMessageBox.Show(ex.Message, "信息提示");
}
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"].Value = DataBaseName;

Comm.CommandType = CommandType.StoredProcedure;
Comm.ExecuteNonQuery();

MyMessageBox.Show("分离数据库成功", "信息提示");
}
catch (Exception ex)
{
MyMessageBox.Show(ex.Message, "信息提示");
}
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"].Value = DataBaseName;
Comm.Parameters.Add(new SqlParameter("@backupname", SqlDbType.NVarChar));
Comm.Parameters["@backupname"].Value = @DataBaseOfBackupPath + @DataBaseOfBackupName;

Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();

MyMessageBox.Show("备份数据库成功", "信息提示");
}
catch (Exception ex)
{
MyMessageBox.Show(ex.Message, "信息提示");


}
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"].Value = DataBaseName;
Comm.Parameters.Add(new SqlParameter(@"BackupFile", SqlDbType.NVarChar));
Comm.Parameters[@"BackupFile"].Value = BackupFile;

Comm.CommandType = CommandType.Text;
Comm.ExecuteNonQuery();

MyMessageBox.Show("还原数据库成功", "信息提示");
}
catch (Exception ex)
{
MyMessageBox.Show(ex.Message, "信息提示");
}
finally
{
Conn.Close();
}
}

///

/// 获取所以的数据库
///

/// 数据库
public List getDatabase()
{

List datas = new List();
string sql = "select name from sysdatabases ";
try
{
Conn = new SqlConnection(ConnectionString);
Conn.Open();
Comm = new SqlCommand(sql, Conn);
SqlDataReader reader = Comm.ExecuteReader();
while (reader.Read())
{
datas.Add(reader[0].ToString());
}
reader.Close();

Conn.Close();

}
catch (Exception)
{

throw new Exception("数据库连接失败，请检查信息是否正确。");
}
return datas;
}

}
}
标签：数据库小助手实现分离数据库/附加数据库等操作 

上一条：

下一条：

 

相关评论

评论加载中……

 

发表评论

类型：	留言 建议 疑问
内容：
	(Alt+Enter)