public void ExecuteNonQueryTest()
{
string _sql = "insert into [Person](PName,PAge,PAddress) values(@pname,@page,@paddress)";
int _actual = SqlHelper.ExecuteNonQuery(_sql,
new DbParameter[3]
{
new SqlParameter("@pname", "YanZhiwei"),
new SqlParameter("@page", 18),
new SqlParameter("@paddress", "zhuzhou")
});
Assert.IsTrue(_actual >= 1);
}
/// <summary>
/// 还原数据库文件
/// </summary>
/// <param name="filePath">还原备份路径</param>
/// <param name="restoreDbName">还原后数据库名称</param>
/// <returns>还原是否成功</returns>
/// 时间：2016-02-24 17:45
/// 备注：
public bool DataRestore(string filePath, string restoreDbName)
{
ValidateOperator.Begin().IsFilePath(filePath).NotNullOrEmpty(restoreDbName, "还原数据库名称");
bool _result = false;
SqlServerDataOperator _sqlHelper = new SqlServerDataOperator(ConnectString);
string _sql = @" use master ;
declare @s varchar(8000);
select @s=isnull(@s,'')+' kill '+rtrim(spID) from master..sysprocesses where dbid=db_id(@dbid);
select @s;exec(@s) ;RESTORE DATABASE @database FROM DISK = @filepath with replace";
SqlParameter[] _paramters = new SqlParameter[3];
_paramters[0] = new SqlParameter("@dbid", restoreDbName);
_paramters[1] = new SqlParameter("@database", restoreDbName);
_paramters[2] = new SqlParameter("@filepath", filePath);
try
{
_sqlHelper.ExecuteNonQuery(_sql, _paramters);
}
catch (Exception ex)
{
ex.Data.Add("filePath", filePath);
ex.Data.Add("ConnectString", ConnectString);
ex.Data.Add("BackUpDataBase", BackUpDataBase);
throw new FrameworkException(string.Format("还原数据库{0}失败，可能该数据库正在使用。", restoreDbName), ex);
}
return(_result);
}
/// <summary>
/// 备份数据库
/// </summary>
/// <param name="filePath">备份路径，需带后缀;eg:D:\ddd.bakRM_DB20160224151425.bak</param>
/// <returns>备份数据库是否成功</returns>
/// 时间：2016-02-24 16:44
/// 备注：
public bool DataBackup(string filePath)
{
ValidateOperator.Begin().IsFilePath(filePath);
bool _result = false;
//创建连接对象
SqlServerDataOperator _sqlHelper = new SqlServerDataOperator(ConnectString);
DropBackupDevice(_sqlHelper);
CreateBackupDevice(_sqlHelper, filePath);
try
{
SqlParameter[] _paramters = new SqlParameter[2];
_paramters[0] = new SqlParameter("@dataBase", BackUpDataBase);
_paramters[1] = new SqlParameter("@descdataBase", BackUpDataBase);
string _sql = "BACKUP DATABASE @dataBase TO @descdataBase WITH INIT";
_sqlHelper.ExecuteNonQuery(_sql, _paramters);
_result = false;
}
catch (Exception ex)
{
ex.Data.Add("filePath", filePath);
ex.Data.Add("ConnectString", ConnectString);
ex.Data.Add("BackUpDataBase", BackUpDataBase);
throw new FrameworkException(string.Format("备份数据库{0}失败。", BackUpDataBase), ex);
}
return(_result);
}
/// <summary>
/// 创建角色权限
/// </summary>
/// <param name="roleName">角色名称</param>
/// <param name="roleCode">角色代码</param>
/// <param name="modulePermissionId">模块操作标识ID数组</param>
/// <returns>
/// 创建是否成功
/// </returns>
/// 时间：2016-04-29 10:21
/// 备注：
public bool CreateRole(string roleName, string roleCode, string[] modulePermissionId)
{
ValidateOperator.Begin().NotNullOrEmpty(roleName, "角色名称").NotNullOrEmpty(roleCode, "角色代码").NotNull(modulePermissionId, "角色对应模块操作标识ID数组");
bool _result = false;
using (SqlServerTransaction sqlTran = sqlHelper.BeginTranscation())
{
try
{
SqlParameter[] _paramter = new SqlParameter[2];
_paramter[0] = new SqlParameter("@roleCode", roleCode);
_paramter[1] = new SqlParameter("@roleName", roleName);
sqlHelper.ExecuteNonQuery(sqlTran, "insert into Roles(R_Code,R_Name) values(@roleCode,@roleName)", _paramter);
foreach (string s in modulePermissionId)
{
if (!string.IsNullOrEmpty(s))
{
_paramter = new SqlParameter[2];
_paramter[0] = new SqlParameter("@roleCode", roleCode);
_paramter[1] = new SqlParameter("@mpId", s);
sqlHelper.ExecuteNonQuery(sqlTran, "insert into RolePermissions values(@roleCode,@mpId)", _paramter);
}
}
sqlTran.CommitTransaction();
_result = true;
}
catch (Exception)
{
sqlTran.RollbackTransaction();
_result = false;
}
return(_result);
}
}