/// <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="sqlHelper">SqlServerHelper</param>
/// 时间：2016-02-24 16:00
/// 备注：
private void DropBackupDevice(SqlServerDataOperator sqlHelper)
{
try
{
SqlParameter[] _paramters = new SqlParameter[1];
_paramters[0] = new SqlParameter("@logicalname", BackUpDataBase);
sqlHelper.StoreExecuteNonQuery("sp_dropdevice", _paramters);//删除备份设备
}
catch (Exception)
{
}
}
/// <summary>
/// 创建数据库的备份逻辑设备
/// </summary>
/// <param name="sqlHelper">SqlServerHelper</param>
/// <param name="filePath">备份路径</param>
/// 时间：2016-02-24 16:05
/// 备注：
private void CreateBackupDevice(SqlServerDataOperator sqlHelper, string filePath)
{
try
{
SqlParameter[] _paramters = new SqlParameter[3];
_paramters[0] = new SqlParameter("@devtype", "disk");
_paramters[1] = new SqlParameter("@logicalname", BackUpDataBase);
_paramters[2] = new SqlParameter("@physicalname", filePath);
sqlHelper.StoreExecuteNonQuery("sp_addumpdevice", _paramters);
}
catch (Exception)
{
}
}
public void ToDataTableTest()
{
SqlServerDataOperator _sqlHelper = new SqlServerDataOperator(@"server=YANZHIWEI-PC\SQLEXPRESS;database=AdventureWorks2014;uid=sa;pwd=sasa;");
string _sql = @"SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[rowguid]
,[ModifiedDate]
FROM [Person].[Address]";
List <Address> _result = _sqlHelper.ExecuteReader <Address>(_sql, null);
DataTable _actual = GeneralMapper.ToDataTable <Address>(_result);
Assert.IsTrue(_actual.Rows.Count > 0);
}
public void Init()
{
sqlHelper = new SqlServerDataOperator(@"server=YANZHIWEI-IT-PC\SQLEXPRESS;database=Northwind;uid=sa;pwd=sasa;");
}
public void Init()
{
sqlHelper = new SqlServerDataOperator(@"Server=YANZHIWEI-IT-PC\SQLEXPRESS;DataBase=Northwind;User Id=sa;Password=sasa;");
}
public void InitConnection()
{
string _sqlConnectString = @"server=YANZHIWEI-PC\SQLEXPRESS;database=AdventureWorks2014;uid=sa;pwd=sasa;";
SqlHelper = new SqlServerDataOperator(_sqlConnectString);
}
public void ProcessRequest(HttpContext context)
{
string _actionType = context.Request.Params["action"].ToStringOrDefault("UnKown");
if (_actionType.CompareIgnoreCase("getLocationList"))
{
int _pageIndex = context.Request.Params["PageIndex"].ToInt32OrDefault(1),
_pageSize = context.Request.Params["PageSize"].ToInt32OrDefault(10);
SqlServerDataOperator _helper = new SqlServerDataOperator(@"Server=YANZHIWEI-IT-PC\SQLEXPRESS;database=Northwind;user id=sa;Password=sasa");
PagedList <Order> _pageResult = _helper.ExecutePageQuery <Order>("[Orders]", "*", "OrderID", OrderType.Desc, string.Empty, _pageSize, _pageIndex);
AjaxResult _result = new AjaxResult(string.Empty, AjaxResultType.Success, _pageResult);
string _json = SerializeHelper.JsonSerialize(new JsonPagedList <Order>(_pageResult)).ParseJsonDateTime();
context.Response.Write(_json);
}
else if (_actionType.CompareIgnoreCase("exportLocationExcel"))
{
SqlServerDataOperator _helper = new SqlServerDataOperator(@"Server=YANZHIWEI-IT-PC\SQLEXPRESS;database=JooWMS;user id=sa;Password=sasa");
PagedList <Location> _pageResult = _helper.ExecutePageQuery <Location>("[Location]", "*", "ID", OrderType.Desc, string.Empty, 10, 1);
DataTable _result = GeneralMapper.ToDataTable <Location>(_pageResult, new string[4] {
"LocalNum", "LocalBarCode", "LocalName", "StorageNum"
});
string _filePath = context.Server.MapPath("~/UploadFiles/");
if (!Directory.Exists(_filePath))
{
Directory.CreateDirectory(_filePath);
}
string _filename = string.Format("库位管理{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"));
NPOIExcel.ToExcel(_result, "库位管理", "库位", Path.Combine(_filePath, _filename));
context.CreateResponse(("/UploadFiles/" + _filename).Escape(), AjaxResultType.Success, string.Empty);
}
else
{
context.ExecutePageQuery <Person>((pageLength, pageIndex, orderIndex, orderBy) =>
{
var persons = GetPersons();
Func <Person, object> order = p =>
{
if (orderIndex == 0)
{
return(p.Id);
}
return(p.Name);
};
if ("desc" == orderBy)
{
persons = persons.OrderByDescending(order);
}
else
{
persons = persons.OrderBy(order);
}
//错误测试
//DataTablePageResult result = new DataTablePageResult();
//result.ExecuteMessage = "测试错误";
//result.ExecuteState = HttpStatusCode.BadGateway;
//正确测试
DataTablePageResult result = new DataTablePageResult();
result.iTotalDisplayRecords = persons.Count();
List <Person> _personList = new List <Person>();
result.iTotalRecords = persons.Count();
result.aaData = persons.Skip(pageIndex).Take(pageLength);
result.ExecuteState = HttpStatusCode.OK;
return(result);
});
// // Those parameters are sent by the plugin
// var iDisplayLength = int.Parse(context.Request["iDisplayLength"]);
// var iDisplayStart = int.Parse(context.Request["iDisplayStart"]);
// var iSortCol = int.Parse(context.Request["iSortCol_0"]);
// var iSortDir = context.Request["sSortDir_0"];
// // Fetch the data from a repository (in my case in-memory)
// var persons = GetPersons();
// // Define an order function based on the iSortCol parameter
// Func<Person, object> order = p =>
// {
// if (iSortCol == 0)
// {
// return p.Id;
// }
// return p.Name;
// };
// // Define the order direction based on the iSortDir parameter
// if ("desc" == iSortDir)
// {
// persons = persons.OrderByDescending(order);
// }
// else
// {
// persons = persons.OrderBy(order);
// }
// // prepare an anonymous object for JSON serialization
// var result = new
// {
// iTotalRecords = persons.Count(),
// iTotalDisplayRecords = persons.Count(),
// aaData = persons
// .Skip(iDisplayStart)
// .Take(iDisplayLength)
// };
// //var serializer = new JavaScriptSerializer();
//// var json = SerializationHelper.JsonSerialize(result);// serializer.Serialize(result);
// context.CreateResponse(result, System.Net.HttpStatusCode.OK);
// //context.Response.ContentType = "application/json";
// //context.Response.Write(json);
}
}
/// <summary>
/// 构造函数，用于初始化sql Server数据连接
/// </summary>
/// <param name="connectString">Sql Server连接字符串</param>
/// 时间：2016-04-29 10:18
/// 备注：
public SqlServerProvider(string connectString)
{
ValidateOperator.Begin().NotNullOrEmpty(connectString, "Sql Server连接字符串");
ConnectString = connectString;
sqlHelper = new SqlServerDataOperator(connectString);
}
public void InitConnection()
{
string _sqlConnectString = @"server=YANZHIWEI-IT-PC\SQLEXPRESS;database=Sample;uid=sa;pwd=sasa;";
SqlHelper = new SqlServerDataOperator(_sqlConnectString);
}
