/// <summary>
/// 分页获取我的粉丝
/// </summary>
/// <param name="clientId"></param>
/// <param name="pageInfo"></param>
/// <returns></returns>
public override PageResult GetFansListByPage(string clientId, PageInfo pageInfo)
{
StringBuilder sSql = new StringBuilder();
DbParameters Parameters = new DbParameters();
Parameters.Add("sClientId", clientId);
sSql.Append(@"select a.sClientId,
a.bIsNotSee iIsSee,
a.dConcernTime,
b.sNickName,
b.bIsVip,
b.dExpireTime,
b.sHeadImg
from CCY_Concern a
left join CCY_Client b on a.sClientId=b.ID
where a.sConcernId=@sClientId order by a.dConcernTime desc");
if (!string.IsNullOrEmpty(pageInfo.keyword))
{
sSql.Append(" and b.sNickName=@keyword");
Parameters.Add("keyword", pageInfo.keyword);
}
var pageResult = query.PaginationQuery(sSql.ToString(), pageInfo, Parameters);
return(pageResult);
}
/// <summary>
/// 获取用户列表
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public override PageResult GetClientList(PageInfo info, int iType, int bIsVip)
{
StringBuilder sSql = new StringBuilder();
DbParameters Parameters = new DbParameters();
sSql.Append("SELECT c.*,v.sVipName FROM dbo.CCY_Client c LEFT JOIN dbo.CCY_VipPackage v ON c.sVipTypeId=v.ID where 1=1");
if (!string.IsNullOrEmpty(info.keyword))
{
sSql.Append(@" and (c.sNickName =@keyword or c.sPhone =@keyword)");
Parameters.Add("keyword", info.keyword);
}
if (iType >= 0)
{
sSql.Append(@" and c.iType=@iType");
Parameters.Add("iType", iType);
}
if (bIsVip >= 0)
{
sSql.Append(@" and c.bIsVip=@bIsVip");
Parameters.Add("bIsVip", bIsVip);
}
var result = query.PaginationQuery(sSql.ToString(), info, Parameters);
return(result);
}
public void RunExecuateNonQueryBatch(int sampleSize, int repeat, Action <IDbParameters, int> action)
{
var mock = _mockery.StrictMock <IAdoOperations>();
Expect.Call(mock.ExecuteNonQuery(CommandType.Text, _sql, (ICommandSetter)null)).Return(3).Repeat.Times(repeat)
.Callback(new Func <CommandType, string, ICommandSetter, bool>(ExecuteNonQueryCallback));
_testee.DbProvider = _dbProvider;
_testee.BatchSize = _batchSize;
var sampleData = new Dictionary <string, int>();
for (int i = 0; i < sampleSize; i++)
{
string key = "string " + i;
IDbParameters parameters = new DbParameters(_dbProvider);
parameters.Add("string_field", DbType.String).Value = key;
parameters.Add("int_field", DbType.Int32).Value = i;
if (action != null)
{
action(parameters, i);
}
Expect.Call(_converter(key)).Return(parameters);
sampleData.Add(key, i);
}
_mockery.ReplayAll();
int result = _testee.GetExecutor().ExecuteNonQuery(mock, _cmdType, _sql, sampleData.Keys, _converter);
Assert.That(result, Is.EqualTo(3 * repeat));
CollectionAssert.AreEqual(sampleData, _dataSaved);
_mockery.VerifyAll();
}
public static DataTable GetEmpDT(string userName, string UserPwd)
{
string sqlStr = @"SELECT top 1 * FROM dbo.e_Emp
WHERE FUserName=@FUserName AND FPwd=@FPwd";
var pars = new DbParameters();
pars.Add("FUserName", userName);
pars.Add("FPwd", UserPwd);
DataTable dt = DbHelper.SqlObj.CreateSqlDataTable(sqlStr, pars);
return(dt);
}
public bool RoleUpdate(NcManagerRole model, List <NcManagerRoleValue> modelRoleValue)
{
StringBuilder strSql3 = new StringBuilder();
//添加权限
if (modelRoleValue != null)
{
foreach (NcManagerRoleValue modelt in modelRoleValue)
{
strSql3.Append("insert into nc_manager_role_value(");
strSql3.Append("role_id,nav_name,action_type)");
strSql3.Append(" values (");
strSql3.Append("" + model.Id + ",'" + modelt.NavName + "','" + modelt.ActionType + "');");
}
strSql3.Append(@"if @@error<>0 begin rollback tran
select N'fail' as result
return
end"
);
}
//1.先删除该角色所有权限
//2.添加权限
string sql = @"
Begin Tran
update nc_manager_role set role_name=@role_name,role_type=@role_type,is_sys=@is_sys where id=@id
if @@error<>0 begin rollback tran
select N'fail' as result
return
end
delete from nc_manager_role_value where role_id=@role_id
if @@error<>0 begin rollback tran
select N'fail' as result
return
end
" + strSql3.ToString() + @"
select N'success' as result
Commit Tran
";
DbParameters p = new DbParameters();
p.Add("@role_name", model.RoleName);
p.Add("@role_type", model.RoleType);
p.Add("@is_sys", model.IsSys);
p.Add("@id", model.Id);
p.Add("@role_id", model.Id);
string result = Utils.ObjectToStr(Dbl.ZZCMS.CreateSqlScalar(sql, p));
return(result == "success");
}
/// <summary>
/// 添加一条记录
///</summary>
public int Edit(SysUserEntity model)
{
int result = 0;
DbParameters.Clear();
ProcedureName = "up_Tb_SysUser_Add";
DataAccessChannelProtection = true;
DbParameters.Add(new DatabaseParameter("result", result, DatabaseType.SQL_Int, 4, ParameterDirection.Output));
DbParameters.Add(new DatabaseParameter("SysID", model.SysID, DatabaseType.SQL_NVarChar, 40));
DbParameters.Add(new DatabaseParameter("SysUserName", model.SysUserName, DatabaseType.SQL_NVarChar, 30));
DbParameters.Add(new DatabaseParameter("RealName", model.RealName, DatabaseType.SQL_NVarChar, 30));
DbParameters.Add(new DatabaseParameter("SysPwd", model.SysPwd, DatabaseType.SQL_NVarChar, 100));
DbParameters.Add(new DatabaseParameter("CellPhone", model.CellPhone, DatabaseType.SQL_NVarChar, 50));
DbParameters.Add(new DatabaseParameter("WeiXin", model.WeiXin, DatabaseType.SQL_NVarChar, 100));
DbParameters.Add(new DatabaseParameter("CreateUser", model.CreateUser, DatabaseType.SQL_NVarChar, 40));
DbParameters.Add(new DatabaseParameter("UserState", model.UserState, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("Isdel", model.Isdel, DatabaseType.SQL_Int, 4));
STMessage stmessage = ExecuteStoredProcedure(DataOperationValue.IDU_OPERATION).DataReturn;
if (stmessage.SqlCode != 0)
{
throw new Exception(DataReturn.SqlMessage);
}
DataAccessChannel.CommitRelease();
DataAccessChannelProtection = false;
result = Convert.ToInt32(DbParameters[0].Value);
return(result);
}
/// <summary>
/// 添加一条记录
///</summary>
public int Edit(DepartmentEntity model)
{
int result = 0;
DbParameters.Clear();
ProcedureName = "up_Tb_Department_Add";
DataAccessChannelProtection = true;
DbParameters.Add(new DatabaseParameter("result", result, DatabaseType.SQL_Int, 4, ParameterDirection.Output));
DbParameters.Add(new DatabaseParameter("DID", model.DID, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("DepName", model.DepName, DatabaseType.SQL_NVarChar, 50));
DbParameters.Add(new DatabaseParameter("Master", model.Master, DatabaseType.SQL_NVarChar, 40));
DbParameters.Add(new DatabaseParameter("PID", model.PID, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("DepOrder", model.DepOrder, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("DepType", model.DepType, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("DepMark", model.DepMark, DatabaseType.SQL_NVarChar, 500));
DbParameters.Add(new DatabaseParameter("Isdel", model.Isdel, DatabaseType.SQL_Int, 4));
STMessage stmessage = ExecuteStoredProcedure(DataOperationValue.IDU_OPERATION).DataReturn;
if (stmessage.SqlCode != 0)
{
throw new Exception(DataReturn.SqlMessage);
}
DataAccessChannel.CommitRelease();
DataAccessChannelProtection = false;
result = Convert.ToInt32(DbParameters[0].Value);
return(result);
}
/// <summary>
/// Creates the a db parameters collection, adding to the collection a parameter created from
/// the method parameters.
/// </summary>
/// <param name="name">The name of the parameter</param>
/// <param name="dbType">The type of the parameter.</param>
/// <param name="size">The size of the parameter, for use in defining lengths of string values. Use
/// 0 if not applicable.</param>
/// <param name="parameterValue">The parameter value.</param>
/// <returns>A collection of db parameters with a single parameter in the collection based
/// on the method parameters</returns>
protected IDbParameters CreateDbParameters(string name, Enum dbType, int size, object parameterValue)
{
IDbParameters parameters = new DbParameters(DbProvider);
parameters.Add(name, dbType, size).Value = parameterValue;
return(parameters);
}
public ActionResult RoleDel(string ids)
{
bool res = true;
if (string.IsNullOrEmpty(ids))
{
res = false;
strMsg = "删除参数异常！";//
}
else
{
string[] arrId = ids.Split(',');
string result = string.Empty;
try
{
for (int i = 0; i < arrId.Length; i++)
{
string id = arrId[i];
//删除管理角色权限
string sql = @"
Begin Tran
--declare @role_id as int
--declare @id as int
delete from nc_manager_role_value where role_id=@role_id
if @@error<>0 begin rollback tran
select N'fail' as result
return
end
delete from nc_manager_role where id=@role_id
if @@error<>0 begin rollback tran
select N'fail' as result
return
end
select N'success' as result
Commit Tran
";
DbParameters p = new DbParameters();
p.Add("@role_id", id);
result = Utils.ObjectToStr(Dbl.ZZCMS.CreateSqlScalar(sql, p));
}
if (result == "success")
{
res = true;
strMsg = "删除成功！";
}
else
{
res = false;
strMsg = "删除失败！";
}
}
catch (Exception ex)//循环删除，异常才报删除错误
{
res = false;
strMsg = "删除过程中出现异常！";//调试过程中+ex.ToString();
}
}
return(Json(new { status = (res ? 1 : 0), message = strMsg }));
}
public virtual IDataParameter CreateDbParameter(string name, Enum dbType, int size, object value)
{
var dbParameters = new DbParameters(DbProvider);
IDbDataParameter dbDataParameter = dbParameters.Add(name, dbType, size);
dbDataParameter.Value = value;
return(ParameterUtils.CloneParameter(dbDataParameter));
}
public virtual IDbDataParameter AddParameter(string parameterName, object value, DbType?dbType, ParameterDirection?direction)
{
var parameter = GenerateParameter(parameterName, value, dbType, direction);
DbParameters.Add(parameter);
return(parameter);
}
public string DictCache_Bind()
{
int pageSize = Utils.StrToInt(Request.Query["page_size"]);
int pageIndex = Utils.StrToInt(Request.Query["page_index"], 0) + 1;
string keyword = Request.Query["keyword"];//搜索关键字
string strWhere = string.Empty;
if (!string.IsNullOrEmpty(keyword))
{
strWhere += " and (Title like '%" + keyword + "%' or Depend like '%" + keyword + "%' or Cache_Key like '%" + keyword + "%')";
}
string filedOrder = " sort_id asc,cache_id asc";
string fields = " Cache_ID, Cache_Desc, Created_Name,convert(nvarchar(20),Created_Time,120) as Created_Time, Updated_Name, Updated_Time, OStatus, Parent_ID, Class_List, Class_Layer, Sort_ID, Title, Depend, Cache_Key, Cache_Exp ";
DbParameters p = new DbParameters();
p.Add("@Tables", "nc_dictcache");
p.Add("@PrimaryKey", "cache_id");
p.Add("@Fields", fields);
p.Add("@Filter", strWhere);
p.Add("@Sort", filedOrder);
p.Add("@PageSize", pageSize);
p.Add("@CurrentPage", pageIndex);
p.AddOut("@TotalCount", "int", 4);
DataTable dt = Dbl.ZZCMS.CreateDataTable("Pr_PageView", p);
int recordCount = Utils.StrToInt(p[p.Length - 1].Value.ToString(), 0);
//string result = JsonHelper.ObjectToJSON(new { rows = dt, total = recordCount });
string result = "{\"rows\":" + JsonHelper.DataTableToJSON(dt) + ",\"total\":" + recordCount + "}";
return(result);
}
public string DictBase_Bind()
{
int pageSize = Utils.StrToInt(Request.Query["page_size"]);
int pageIndex = Utils.StrToInt(Request.Query["page_index"], 0) + 1;
string keyword = Request.Query["keyword"];//搜索关键字
string strWhere = string.Empty;
if (!string.IsNullOrEmpty(keyword))
{
strWhere += " and (Key_Type like '%" + keyword + "%' or Key_Value like '%" + keyword + "%')";
}
string filedOrder = " id Desc,Sort_ID asc";
string fields = " id , Key_Type , Key_Code , Key_Value , Code_Value , Sort_ID , OStatus ";
DbParameters p = new DbParameters();
p.Add("@Tables", "nc_dictbase");
p.Add("@PrimaryKey", "id");
p.Add("@Fields", fields);
p.Add("@Filter", strWhere);
p.Add("@Sort", filedOrder);
p.Add("@PageSize", pageSize);
p.Add("@CurrentPage", pageIndex);
p.AddOut("@TotalCount", "int", 4);
DataTable dt = Dbl.ZZCMS.CreateDataTable("Pr_PageView", p);
int recordCount = Utils.StrToInt(p[p.Length - 1].Value.ToString(), 0);
//objecttojson转，不可以直接rows=dt会转成xml格式
//JsonHelper.ObjectToJSON(new { rows = JsonHelper.DataTableToJSON(dt), total = recordCount });
string result = "{\"rows\":" + JsonHelper.DataTableToJSON(dt) + ",\"total\":" + recordCount + "}";
return(result);
}
public int RoleAdd(NcManagerRole model, List <NcManagerRoleValue> modelRoleValue)
{
StringBuilder strSql2 = new StringBuilder();
if (modelRoleValue != null)
{
foreach (NcManagerRoleValue modelt in modelRoleValue)
{
strSql2.Append("insert into nc_manager_role_value(");
strSql2.Append("role_id,nav_name,action_type)");
strSql2.Append(" values (");
strSql2.Append("@ReturnValue,'" + modelt.NavName + "','" + modelt.ActionType + "');");
}
strSql2.Append(@"if @@error<>0 begin rollback tran
select N'fail' as result
return
end"
);
}
//1.添加角色
//2.添加对应的权限
string sql = @"
Begin Tran
declare @ReturnValue as int
insert into nc_manager_role(role_name,role_type,is_sys) values(@role_name,@role_type,@is_sys);set @ReturnValue= @@IDENTITY
if @@error<>0 begin rollback tran
select N'fail' as result
return
end
" + strSql2.ToString() + @"
select N'success' as result
Commit Tran
";
DbParameters p = new DbParameters();
p.Add("@role_name", model.RoleName);
p.Add("@role_type", model.RoleType);
p.Add("@is_sys", model.IsSys);
string result = Utils.ObjectToStr(Dbl.ZZCMS.CreateSqlScalar(sql, p));
return(result == "success" ? 1 : 0);
}
public int Update(BaseModel model)
{
Type T = model.GetType();
StringBuilder sql = new StringBuilder("update " + tableName + " set ");
string strIdentityName = "";
PropertyInfo[] propertys = T.GetProperties();
StringBuilder sbColumns = new StringBuilder();
foreach (PropertyInfo pro in propertys)
{
//取得是否有自动增长的特性
IdentityAttribute att = Attribute.GetCustomAttribute(pro, typeof(IdentityAttribute)) as IdentityAttribute;
if (att == null || !att.IsIdentity)
{
if (sbColumns.Length > 0)
{
sbColumns.Append(",");
}
if (pro.Name == "DataBase")
{
sbColumns.Append(string.Format("[{0}] = @{0}", pro.Name));
}
else
{
sbColumns.Append(string.Format("{0} = @{0}", pro.Name));
}
}
else
{
strIdentityName = pro.Name;
}
}
sql.Append(sbColumns.ToString());
sql.Append(" where " + strIdentityName + "=" + model.SN.ToString());
DbParameters paras = new DbParameters();
foreach (PropertyInfo pro in propertys)
{
object value = pro.GetValue(model);
if (value == null)
{
value = getDefault(pro);
}
paras.Add(pro.Name, value);
}
this.db.Open();
int iResult = db.ExecuteSql(sql.ToString(), paras);
this.db.Close();
return(iResult);
}
/// <summary>
/// 根据编号（主键）获取项:返回实体对象
/// </summary>
/// <returns></returns>
public DepartmentEntity GetObjByID(int id)
{
DbParameters.Clear();
ProcedureName = "up_Tb_Department_Get";
DbParameters.Add(new DatabaseParameter("DID", id, DatabaseType.SQL_Int, 4));
if (ExecuteStoredProcedure(DataOperationValue.SEL_OPERATION).DataReturn.SqlCode != 0)
{
throw new Exception(DataReturn.SqlMessage);
}
return(First());
}
public EmployeeEntity GetObj(string id)
{
DbParameters.Clear();
ProcedureName = "up_Tb_Employee_Get";
DbParameters.Add(new DatabaseParameter("EID", id, DatabaseType.SQL_NVarChar, 40));
if (ExecuteStoredProcedure(DataOperationValue.SEL_OPERATION).DataReturn.SqlCode != 0)
{
throw new Exception(DataReturn.SqlMessage);
}
return(First());
}
public void TooFewParameters()
{
TestAdoOperation operation = new TestAdoOperation();
operation.DbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
operation.Sql = "select * from table";
IDbParameters parameters = new DbParameters(operation.DbProvider);
parameters.Add("name");
operation.DeclaredParameters = parameters;
operation.Compile();
operation.ValidateParams(null);
}
///// <summary>
///// 执行查询，将结果集返回到一个DataTable
///// </summary>
///// <param name="cmdText">sql语句</param>
///// <param name="sqlDA">DbDataAdapter对象</param>
///// <returns>结果集</returns>
//public DataTable QueryDataTable(string cmdText, out DbDataAdapter sqlDA)
//{
// if (this.DbConnection == null)
// {
// this.DbConnection = IDbProvider.CreateConnection() as DbConnection;
// this.DbConnection.ConnectionString = ConnnectString;
// }
// this.DbConnection.Open();
// try
// {
// DbDataAdapter _DbDataAdapter = IDbProvider.CreateDataAdapter() as DbDataAdapter;
// _DbDataAdapter.SelectCommand = IDbProvider.CreateCommand() as DbCommand;
// _DbDataAdapter.SelectCommand.CommandText = cmdText;
// _DbDataAdapter.SelectCommand.Connection = this.DbConnection;
// sqlDA = _DbDataAdapter;
// DataTable dt = new DataTable();
// sqlDA.Fill(dt);
// return dt;
// }
// catch (Exception e)
// {
// dsExceptionHandler.HandleException(e);
// sqlDA = null;
// throw e;// new Goodway.CommonModel.Exception.DbException(DbResult.SqlError, e.Message ?? "" + Environment.NewLine + e.StackTrace ?? "", cmdText);
// }
// finally
// {
// this.DbConnection.Dispose();
// }
//}
void PrepareIDbParametersByWhereString(out IDbParameters cmdParms, string whereString, params object[] parms)
{
cmdParms = new DbParameters(IDbProvider);
System.Text.RegularExpressions.Regex r = null;
if (IDbProvider.DbMetadata.ProductName.ToLower().IndexOf("oracle") != -1)
{
r = new System.Text.RegularExpressions.Regex(":([^ ]*)", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled);
System.Text.RegularExpressions.MatchCollection matches = r.Matches(whereString);
IDataParameter param;
for (int i = 0; i < matches.Count; i++)
{
cmdParms.Add(":" + matches[i].Groups[1].Value, DbType.String);
param = cmdParms[":" + matches[i].Groups[1].Value];
param.Direction = ParameterDirection.Input;
if (parms == null || parms.Length < (i + 1))
{
throw new Exception("对不起， @" + matches[i].Groups[1].Value + " 参数的值未提供！");
}
param.Value = parms[i];
//cmdParms.AddParameter(param);
}
}
else
{
r = new System.Text.RegularExpressions.Regex("@([^ ]*)", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Compiled);
System.Text.RegularExpressions.MatchCollection matches = r.Matches(whereString);
IDataParameter param;
for (int i = 0; i < matches.Count; i++)
{
cmdParms.Add("@" + matches[i].Groups[1].Value, DbType.String);
param = cmdParms["@" + matches[i].Groups[1].Value];
param.Direction = ParameterDirection.Input;
if (parms == null || parms.Length < (i + 1))
{
throw new Exception("对不起， @" + matches[i].Groups[1].Value + " 参数的值未提供！");
}
param.Value = parms[i];
//cmdParms.AddParameter(param);
}
}
}
/// <summary>
/// 获取展示权限，根据角色ID，navName
/// </summary>
public string GetActionTypeByNavName(int roleID, string navName)
{
string sql = @"
Begin Tran
declare @s as varchar(max)
select @s=isnull(@s +',','')+dmrv.action_type from nc_manager_role_value dmrv left join nc_manager_role dmr on dmrv.role_id=dmr.id where dmr.id=@ID and dmrv.nav_name=@navName
if @@error<>0 begin rollback tran
select N'fail' as result
return
end
select @s as result
Commit Tran
";
DbParameters p = new DbParameters();
p.Add("@ID", roleID);
p.Add("@navName", navName);
string result = Utils.ObjectToStr(Dbl.ZZCMS.CreateSqlScalar(sql, p));
return(result);
}
public int SaveCmdText(int SN, string CommandType, string CommandText, string DataBase, string ServiceName)
{
if (CommandText == null)
{
CommandText = "";
}
string strSql = "update " + this.tableName + " set CommandType = @CommandType, CommandText =@CommandText, [DataBase] = @DataBase, ServiceName= @ServiceName where SN = @SN";
DbParameters p = new DbParameters();
p.Add("SN", SN);
p.Add("CommandType", CommandType);
p.Add("CommandText", CommandText);
p.Add("DataBase", DataBase);
p.Add("ServiceName", ServiceName);
int iResult = db.ExecuteSql(strSql, p);
return(iResult);
}
/// <summary>
/// 登录
/// </summary>
/// <returns></returns>
public SysUserEntity UserLogin(string uname, string pwd)
{
DbParameters.Clear();
ProcedureName = "up_Tb_SysUser_Login";
DbParameters.Add(new DatabaseParameter("SysUserName", uname, DatabaseType.SQL_NVarChar, 100));
DbParameters.Add(new DatabaseParameter("SysPwd", pwd, DatabaseType.SQL_NVarChar, 100));
if (ExecuteStoredProcedure(DataOperationValue.SEL_OPERATION).DataReturn.SqlCode != 0)
{
throw new Exception(DataReturn.SqlMessage);
}
return(First());
}
public int Dept_Add(Model.Models.NcManagerDept model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into nc_manager_dept(");
strSql.Append("Parent_ID,Class_List,Class_Layer,Sort_ID,Dept_Name,Dept_Desc,Created_ID,OStatus)");
strSql.Append(" values (");
strSql.Append("@Parent_ID,@Class_List,@Class_Layer,@Sort_ID,@Dept_Name,@Dept_Desc,@Created_ID,@OStatus)");
strSql.Append(";select @ReturnValue= @@IDENTITY ");
strSql.Append(@"if @@error<>0 begin rollback tran
select N'fail' as result
return
end");
string sql = @"
Begin Tran
declare @ReturnValue as int,@pClass_list as nvarchar(500),@pClass_layer as int
" + strSql.ToString() + @"
if @parent_id>0 begin
select @pClass_list=class_list,@pClass_layer=class_layer from nc_manager_dept where dept_id=@parent_id
set @pClass_list=@pClass_list+cast(@ReturnValue as nvarchar(max))+','
set @pClass_layer=@pClass_layer+1
end
else begin
set @pClass_list=','+cast(@ReturnValue as nvarchar(max))+','
set @pClass_layer=1
end
update nc_manager_dept set class_list=@pClass_list, class_layer=@pClass_layer where dept_id=@ReturnValue
if @@error<>0 begin rollback tran
select N'fail' as result
return
end
select N'success' as result
--select N'parent_id_error'+cast(@pClass_layer as nvarchar(max))+cast(@ReturnValue as nvarchar(max)) as result
Commit Tran
";
DbParameters p = new DbParameters();
p.Add("@parent_id", model.ParentId);
p.Add("@class_list", model.ClassList);
p.Add("@class_layer", model.ClassLayer);
p.Add("@Dept_Name", model.DeptName);
p.Add("@Dept_Desc", model.DeptDesc);
p.Add("@sort_id", model.SortId);
p.Add("@Created_ID", siteAdminInfo.Id);
p.Add("@OStatus", model.Ostatus);
string result = Utils.ObjectToStr(Dbl.ZZCMS.CreateSqlScalar(sql, p));
return(result == "success" ? 1 : 0);
}
public DataTable GetTable(int isdel, int pid, int deptype)
{
DbParameters.Clear();
ProcedureName = "up_Tb_Department_GetList";
DbParameters.Add(new DatabaseParameter("DepType", deptype, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("Isdel", isdel, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("PID", pid, DatabaseType.SQL_Int, 4));
if (ExecuteStoredProcedure(DataOperationValue.SEL_OPERATION).DataReturn.SqlCode != 0)
{
throw new Exception(DataReturn.SqlMessage);
}
return(DataReflectionContainer);
}
public void TooFewParameters()
{
TestAdoOperation operation = new TestAdoOperation();
operation.DbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
operation.Sql = "select * from table";
IDbParameters parameters = new DbParameters(operation.DbProvider);
parameters.Add("name");
operation.DeclaredParameters = parameters;
operation.Compile();
Assert.Throws <InvalidDataAccessApiUsageException>(() => operation.ValidateParams(null));
}
public int SaveCmdText(int SN, string CommandType, string CommandText)
{
try
{
if (CommandText == null)
{
CommandText = "";
}
string strSql = "update api_flow_step set CommandType = @CommandType, CommandText =@CommandText where SN = @SN";
DbParameters p = new DbParameters();
p.Add("SN", SN);
p.Add("CommandType", CommandType);
p.Add("CommandText", CommandText);
int iResult = db.ExecuteSql(strSql, p);
return(iResult);
}
catch (Exception ex)
{
_logger.LogError("DbCommand->api_interface=>Insert(s) 出错\r\n" + ex.Message);
throw ex;
}
}
void InitIDbParameters(out IDbParameters cmdParms, string sql, IList <KeyValuePair <string, object> > Parameters)
{
cmdParms = new DbParameters(IDbProvider);
IDataParameter param;
foreach (var parameter in Parameters)
{
cmdParms.Add(parameter.Key, DbType.String);
param = cmdParms[parameter.Key];
param.Direction = ParameterDirection.Input;
param.Value = parameter.Value;
}
}
/// <summary>
/// 根据实体条件分页获取数据集，返回DataSet
/// </summary>
/// <param name="pagesize">每页显示条数</param>
/// <param name="pageindex">当前页码,从1开始</param>
/// <param name="recordCount">为-1时统计满足条件的记录总数</param>
/// <param name="model">条件实体</param>
public DataTable GetPaged(int pagesize, int pageindex, ref int recordCount, DepartmentEntity model)
{
DbParameters.Clear();
ProcedureName = "up_Tb_Department_Paged";
DbParameters.Add(new DatabaseParameter("pagesize", pagesize, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("pageindex", pageindex, DatabaseType.SQL_Int, 4));
DbParameters.Add(new DatabaseParameter("recordCount", recordCount, DatabaseType.SQL_Int, 4, ParameterDirection.Output));
if (ExecuteStoredProcedure(DataOperationValue.SEL_OPERATION).DataReturn.SqlCode != 0)
{
throw new Exception(DataReturn.SqlMessage);
}
recordCount = Convert.ToInt32(DbParameters[2].Value);
return(DataReflectionContainer);
}
/// <summary>
/// 根据主键编号集合删除记录
///</summary>
public int DeleteBat(string ids, int isdel)
{
DbParameters.Clear();
ProcedureName = "up_Tb_Department_DelBat";
DataAccessChannelProtection = true;
DbParameters.Add(new DatabaseParameter("Ids", ids, DatabaseType.SQL_NVarChar, 2000));
DbParameters.Add(new DatabaseParameter("Isdel", isdel, DatabaseType.SQL_Int, 4));
STMessage stmessage = ExecuteStoredProcedure(DataOperationValue.IDU_OPERATION).DataReturn;
if (stmessage.SqlCode != 0)
{
throw new Exception(DataReturn.SqlMessage);
}
DataAccessChannel.CommitRelease();
DataAccessChannelProtection = false;
return(stmessage.AffectRows);
}
/// <summary>
/// 判断是否有权限
/// </summary>
private DataTable chkPermission(int?role_id)
{
DataTable dt = CacheHelper.Get("permisson" + role_id) as DataTable;
if (dt == null)
{
string strSql = "SELECT mrv.nav_name,mrv.action_type FROM nc_manager_role mr LEFT JOIN nc_manager_role_value mrv ON mr.id=mrv.role_id WHERE mr.id=@role_id";
DbParameters p = new DbParameters();
p.Add("@role_id", role_id);
dt = Dbl.ZZCMS.CreateSqlDataTable(strSql, p);
CacheHelper.Set("permisson" + role_id, dt);
}
//string strSql = "SELECT mr.role_type,mrv.role_id,mrv.nav_name,mrv.action_type FROM nc_manager_role mr LEFT JOIN nc_manager_role_value mrv ON mr.id=mrv.role_id WHERE mr.id=@role_id";
//DbParameters p = new DbParameters();
//p.Add("@role_id", role_id);
//DataTable dt = Dbl.ZZCMS.CreateSqlDataTable(strSql, p);
return(dt);
}