/// <summary>
/// 多条sql执行 无事务
/// </summary>
/// <param name="dto"></param>
/// <returns>主体部分执行成功条数</returns>
public int MultipleSQLExecuteReturnRows(MultiSQLExecuteParam dto)
{
SqlConnection connection = new SqlConnection(this.connectString);
var beforeRows = -1;
var afterRows = -1;
int count = 0;
try
{
connection.Open();
if (!string.IsNullOrEmpty(dto.beforeSql))
{
//执行before
if (dto.beforeObj != null)
{
beforeRows = connection.Execute(dto.beforeSql, dto.beforeObj);
}
else if (dto.beforeParam.Count > 0)
{
beforeRows = connection.Execute(dto.beforeSql, dto.beforeParam);
}
else
{
beforeRows = connection.Execute(dto.beforeSql, null);
}
}
//执行的是存储过程
//执行主体
if (!string.IsNullOrEmpty(dto.listSql))
{
if (dto.listSql.IndexOf("EXECUTE") != -1)
{
foreach (object item in dto.objList)
{
JRes res = connection.Query <JRes>(dto.listSql, item).SingleOrDefault();
if (res.res == 1)
{
count++;
}
}
}
else
{
foreach (object item in dto.objList)
{
connection.Execute(dto.listSql, item);
count++;
}
}
}
if (!string.IsNullOrEmpty(dto.afterSql))
{
//执行after
if (dto.afterObj != null)
{
afterRows = connection.Execute(dto.afterSql, dto.afterObj);
}
else if (dto.afterParam.Count > 0)
{
afterRows = connection.Execute(dto.afterSql, dto.afterParam);
}
else
{
afterRows = connection.Execute(dto.afterSql, null);
}
}
connection.Close();
}
catch (Exception ex)
{
connection.Close();
Log.WriteLog(ex.Message);
return(count);
}
return(count);
}
public bool SavePower(int id, string list, int creator)
{
JavaScriptSerializer js = new JavaScriptSerializer();
List <PageTreeDto> ListDto = js.Deserialize <List <PageTreeDto> >(list);
MultiSQLExecuteParam param = new MultiSQLExecuteParam();
//删除权限数据
param.beforeSql = @"DELETE FROM tbPower WHERE roleId = @id";
param.beforeParam.Add("id", id);
param.listSql = @"INSERT tbPower
( roleId,
pageId,
createDate,
creator)
VALUES
( @roleId,
@pageId,
GETDATE(),
@creator)" ;
int opCount = 0;
foreach (PageTreeDto item in ListDto)
{
//判断是否为checked 由于当前设计看不到上层即看不到下层页面 如果上层未选择则视为下层也不选中
if (item.state.@checked == true)
{
//保存该条
item.roleId = id;
item.creator = creator;
param.objList.Add(item);
opCount++;
//循环Nodes
foreach (var subitem in item.nodes)
{
if (subitem.state.@checked == true)
{
//保存该条
subitem.roleId = id;
subitem.creator = creator;
param.objList.Add(subitem);
opCount++;
//循环Nodes
foreach (var thrditem in subitem.nodes)
{
if (thrditem.state.@checked == true)
{
//保存该条
thrditem.roleId = id;
thrditem.creator = creator;
param.objList.Add(thrditem);
opCount++;
}
}
}
}
}
}
if (opCount == DBAgent.MultipleSQLExecuteReturnRows(param))
{
return(true);
}
return(false);
}
