热门：网页模板.net视频教程JQueryMVCjsonExtJs源码示例三级联动JQuery菜单
您现在的位置：.Net中文社区>> .Net编程>>正文内容

C#附加数据库的思路跟算法

发布时间：2009年05月09日点击数： 次未知

DataBaseHelper V1.0 包括附加、还原、备份、分离、压缩、创建、修改等常见操作

using System;  
using System.Collections.Generic;  
using System.Data.SqlClient;  
using System.Data;  
namespace AdminZJC.DataBaseControl  
{  
/// <summary>  
/// 对数据库操作的类，包括附加、还原、备份、分离、压缩、创建、修改等常见操作  
/// 版本1.0  
/// 最新版本主要在命名规范上做了完善  
/// 同时增加了还原时重命名数据库功能  
/// modify by adminzjc 2009.5.4  
/// E_mail:adminzjc@126.com  
/// </summary>  
public class DataBaseHelper  
{  
/// <summary>  
/// 数据库连接字符串  
/// </summary>  
public string ConnectionString;  
/// <summary>  
/// SQL操作语句  
/// </summary>  
public string StrSQL;  
/// <summary>  
/// 数据库连接对象  
/// </summary>  
private SqlConnection Conn;  
/// <summary>  
/// 数据库操作对象Comm  
/// </summary>  
private SqlCommand Comm;  
/// <summary>  
/// 要操作的数据库名称  
/// </summary>  
public string DataBaseName;  
/// <summary>  
/// 数据库逻辑名  
/// 逻辑名为数据库创建后的初始文件名，以后不会随着数据库的操作(如备份还原)而改变  
/// 此处要区别于DataBaseName  
/// </summary>  
public string LogicalDataBaseName;  
/// <summary>  
/// 数据库文件完整地址  
/// </summary>  
public string DataBase_MDF_Address;  
/// <summary>  
/// 数据库日志文件完整地址  
/// </summary>  
public string DataBase_LDF_Address;  
/// <summary>  
/// 备份文件名  
/// </summary>  
public string DataBaseOfBackupName;  
/// <summary>  
/// 备份文件路径  
/// </summary>  
public string DataBaseOfBackupPath;  
/// <summary>  
/// 数据库压缩比，1%－99%  
/// </summary>  
public string Percent;  
/// <summary>  
/// 执行创建/修改数据库和表的通用操作  
/// </summary>  
public void DataBaseAndTableOperate()  
{  
try  
{  
Conn = new SqlConnection(ConnectionString);  
Conn.Open();  
Comm = new SqlCommand();  
Comm.Connection = Conn;  
Comm.CommandText = StrSQL;  
Comm.CommandType = CommandType.Text;  
Comm.ExecuteNonQuery();  
}  
catch (Exception ex)  
{  
throw (ex);  
}  
finally  
{  
Conn.Close();  
}  
}  
/// <summary>  
/// 附加数据库  
/// </summary>  
public void AppendDataBase()  
{  
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_Address;  
Comm.Parameters.Add(new SqlParameter(@"filename2", SqlDbType.NVarChar));  
Comm.Parameters[@"filename2"].Value = DataBase_LDF_Address;  
Comm.CommandType = CommandType.StoredProcedure;  
Comm.ExecuteNonQuery();  
}  
catch (Exception ex)  
{  
throw (ex);  
}  
finally  
{  
Conn.Close();  
}  
}  
/// <summary>  
/// 分离数据库  
/// </summary>  
public void DetachDataBase()  
{  
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();  
}  
catch (Exception ex)  
{  
throw (ex);  
}  
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();  
}  
catch (Exception ex)  
{  
throw (ex);  
}  
finally  
{  
Conn.Close();  
}  
}  
/// <summary>  
/// 还原数据库  
/// </summary>  
public void RestoreDataBase()  
{  
try  
{  
string BackupFile = @DataBaseOfBackupPath + @DataBaseOfBackupName;  
Conn = new SqlConnection(ConnectionString);  
Conn.Open();  
Comm = new SqlCommand();  
Comm.Connection = Conn;  
Comm.CommandText = "use master;restore filelistonly from disk = @BackupFile;restore database @DataBaseName from disk = @BackupFile with move '" + LogicalDataBaseName + "' to '" + DataBase_MDF_Address + "',move 'ajgl_baseunit_Log' to '" + DataBase_LDF_Address + "',stats = 10, 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();  
}  
catch (Exception ex)  
{  
throw (ex);  
}  
finally  
{  
Conn.Close();  
}  
}  
/// <summary>  
/// 压缩数据库  
/// </summary>  
public void CompressDatabase()  
{  
try  
{  
Conn = new SqlConnection(ConnectionString);  
Conn.Open();  
Comm = new SqlCommand();  
Comm.Connection = Conn;  
Comm.CommandText = "DBCC SHRINKDATABASE (" + DataBaseName + "," + Percent + ")";  
Comm.CommandType = CommandType.Text;  
Comm.ExecuteNonQuery();  
}  
catch (Exception ex)  
{  
throw (ex);  
}  
finally  
{  
Conn.Close();  
}  
}  
/// <summary>  
/// 设置数据库为只读  
/// </summary>  
/// <param name=""></param>  
public void ReadOnlyDatabase()  
{  
try  
{  
Conn = new SqlConnection(ConnectionString);  
Conn.Open();  
Comm = new SqlCommand();  
Comm.Connection = Conn;  
Comm.CommandText = "USE master; EXEC sp_dboption '" + DataBaseName + "', 'read only', 'TRUE'";  

Comm.CommandType = CommandType.Text;  
Comm.ExecuteNonQuery();  
}  
catch (Exception ex)  
{  
throw (ex);  
}  
finally  
{  
Conn.Close();  
}  
}  
/// <summary>  
/// 设置数据库为脱机状态  
/// </summary>  
public void OfflineDatabase()  
{  
try  
{  
Conn = new SqlConnection(ConnectionString);  
Conn.Open();  
Comm = new SqlCommand();  
Comm.Connection = Conn;  
Comm.CommandText = "USE master; EXEC sp_dboption '" + DataBaseName + "', 'offline', 'TRUE'";  

Comm.CommandType = CommandType.Text;  
Comm.ExecuteNonQuery();  
}  
catch (Exception ex)  
{  
throw (ex);  
}  
finally  
{  
Conn.Close();  
}  
}  
}  
}  
调用示例

随机文章推荐

本站热点业务

更多模板/案例展示

热门推荐

最近更新

关于我们 | 联系我们 | 团队日志 | 网站地图 | 网站合作
本站部份资源来于互联网,只供学习之用,不得用于商业,如有侵犯版权请联系告知,本站将第一时间删除!
copyright © 2008-2010 .Net中文社区 ASPXCS.NET™.All Rights Reserved 滇ICP备08102132号