郑德才博客

//读取sql数据库里面的所有数据库，并将这些数据库绑定到DropDownList里面，主要是绑定数据库名
public SqlDataReader GetServerList(DropDownList ddlDatabaseList)
{
ArrayList arr = new ArrayList();
SqlConnection connection = null;
SqlCommand command = null;
SqlDataReader reader = null;
try
{
//在List中绑定所有数据库
connection = new SqlConnection(ConnHelper.getConnectionString()); //读取数据库
command = new SqlCommand("sp_helpdb", connection);
command.CommandType = CommandType.StoredProcedure;
connection.Open();
reader = command.ExecuteReader();
ddlDatabaseList.DataSource = reader;
ddlDatabaseList.DataTextField = "Name";
ddlDatabaseList.DataBind();
}
catch
{
return null;
}
finally
{
reader.Close();
connection.Close();
}
return reader;
}
//备份数据库按钮，点击备份调用BakData方法看是否备份成功
protected void btnOK_Click(object sender, EventArgs e)
{
try
{
string dbFileName = txtDbFileName.Text.Trim();
if (!dbFileName.EndsWith(".bak"))
{
dbFileName += ".bak";
}
string Path = Server.MapPath("~//DbFile") + "\\" + dbFileName;

string dbName = ddlDatabaseList.SelectedValue;
bakServer bak = new bakServer();
if (bak.BakData(dbName, Path))
{
MessageBox.Show(this, "备份数据成功！");
}
else
{
MessageBox.Show(this, "备份数据失败！");
}
}
catch
{
MessageBox.Show(this, "备份数据出错！");
}
}
//调用备份数据库的方法，返回备份是否成功
public bool BakData(string DbName, string Path)
{
bool bl = false;
SqlConnection connection = null;
try
{
if (BakData())
{
connection = new SqlConnection(ConnHelper.getConnectionString());
connection.Open();
string sql = "backup database " + DbName + " to disk='" + Path + "'";
SqlCommand command = new SqlCommand(sql, connection);
if (command.ExecuteNonQuery() != 0)
bl = true;
else
bl = false;
}
else
{
bl = false;
}
}
catch
{
bl = false;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return bl;
}
//备份数据库恢复数据前建立存储过程，已经存在存储过程则删除存储过程，从新建立
public bool BakData()
{
string DqStr = "if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[killspid ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[killspid ];";

string Str = "create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status<>-1 begin exec('kill '+@spid) fetch next from getspid into @spid end close getspid deallocate getspid end";
bool bl = false;
SqlConnection connection = null;
try
{
connection = new SqlConnection(ConnHelper.getConnectionString());
connection.Open();
SqlCommand command = new SqlCommand(DqStr, connection);
if (command.ExecuteNonQuery() != 0)
{
command = new SqlCommand(Str, connection);
if (command.ExecuteNonQuery() != 0)
{
bl = true;
}
else
{
bl = false;
}
}
else
{
bl = false;
}

}
catch
{
bl = false;
}
finally
{
if (connection != null)
{
connection.Close();
}
}
return bl;
}