public ClassCGScopeEntity GetClassCGScopeByName(string name) { string sql = @"SELECT top 1 [Id],[ClassId],[ScopeClassName],[IsActive],[Sort],ParentId,IsRoot,ScopeType FROM dbo.[ClassCGScope] WITH(NOLOCK) WHERE [ScopeClassName]=@ScopeClassName Order By Sort Desc" ; DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@ScopeClassName", DbType.String, name); ClassCGScopeEntity entity = new ClassCGScopeEntity(); using (IDataReader reader = db.ExecuteReader(cmd)) { if (reader.Read()) { entity.Id = StringUtils.GetDbInt(reader["Id"]); entity.ClassId = StringUtils.GetDbInt(reader["ClassId"]); entity.ScopeClassName = StringUtils.GetDbString(reader["ScopeClassName"]); entity.IsActive = StringUtils.GetDbInt(reader["IsActive"]); entity.Sort = StringUtils.GetDbInt(reader["Sort"]); entity.ParentId = StringUtils.GetDbInt(reader["ParentId"]); entity.IsRoot = StringUtils.GetDbInt(reader["IsRoot"]); entity.ScopeType = StringUtils.GetDbInt(reader["ScopeType"]); } } return(entity); }
/// <summary> /// 插入一条记录到表MemCGScope,如果表中存在自增字段,则返回值为新记录的自增字段值,否则返回0。 /// 该方法提供给界面等UI层调用 /// </summary> /// <param name="memCGScope">要添加的MemCGScope数据实体对象</param> public int AddMemCGScope(MemCGScopeEntity memCGScope) { if (memCGScope.ScopeType == (int)ScopeTypeEnum.Car) { CarTypeBrandEntity cartype = CarTypeBrandBLL.Instance.GetParentByName(memCGScope.BrandName); if (cartype != null && cartype.Id > 0) { if (cartype.BrandName == memCGScope.BrandName) { memCGScope.BrandId = cartype.Id; } else { MemCGScopeEntity parentcgscope = new MemCGScopeEntity(); parentcgscope.BrandName = cartype.BrandName; parentcgscope.CGMemId = memCGScope.CGMemId; parentcgscope.ClassName = memCGScope.ClassName; if (MemCGScopeBLL.Instance.IsExist(parentcgscope)) { return((int)CommonStatus.ADD_Fail_Exist); } } } } else if (memCGScope.ScopeType == (int)ScopeTypeEnum.Normal) { ClassCGScopeEntity classscope = ClassCGScopeBLL.Instance.GetParentByScopeName(memCGScope.BrandName); if (classscope != null && classscope.Id > 0) { if (classscope.ScopeClassName == memCGScope.BrandName) { memCGScope.BrandId = classscope.Id; } else { MemCGScopeEntity parentcgscope = new MemCGScopeEntity(); parentcgscope.BrandName = classscope.ScopeClassName; parentcgscope.CGMemId = memCGScope.CGMemId; parentcgscope.ClassName = memCGScope.ClassName; if (MemCGScopeBLL.Instance.IsExist(parentcgscope)) { return((int)CommonStatus.ADD_Fail_Exist); } } } } if (MemCGScopeBLL.Instance.IsExist(memCGScope)) { return((int)CommonStatus.ADD_Fail_Exist); } else { return(MemCGScopeDA.Instance.AddMemCGScope(memCGScope)); } }
/// <summary> /// 插入一条记录到表ClassCGScope,如果表中存在自增字段,则返回值为新记录的自增字段值,否则返回0。 /// 该方法提供给界面等UI层调用 /// </summary> /// <param name="classCGScope">要添加的ClassCGScope数据实体对象</param> public int AddClassCGScope(ClassCGScopeEntity classCGScope) { if (ClassCGScopeBLL.Instance.IsExist(classCGScope)) { return((int)CommonStatus.ADD_Fail_Exist); } else { return(ClassCGScopeDA.Instance.AddClassCGScope(classCGScope)); } }
/// <summary> /// 根据主键值更新记录的全部字段(注意:该方法不会对自增字段、timestamp类型字段以及主键字段更新!如果要更新主键字段,请使用Update方法)。 /// 如果数据库有数据被更新了则返回True,否则返回False /// </summary> /// <param name="db">数据库操作对象</param> /// <param name="classCGScope">待更新的实体对象</param> public int UpdateClassCGScope(ClassCGScopeEntity entity) { string sql = @" UPDATE dbo.[ClassCGScope] SET [ClassId]=@ClassId,[ScopeClassName]=@ScopeClassName,[IsActive]=@IsActive,[Sort]=@Sort WHERE [Id]=@id"; DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@Id", DbType.Int32, entity.Id); db.AddInParameter(cmd, "@ClassId", DbType.Int32, entity.ClassId); db.AddInParameter(cmd, "@ScopeClassName", DbType.String, entity.ScopeClassName); db.AddInParameter(cmd, "@IsActive", DbType.Int32, entity.IsActive); db.AddInParameter(cmd, "@Sort", DbType.Int32, entity.Sort); return(db.ExecuteNonQuery(cmd)); }
/// <summary> /// 读取记录列表。 /// </summary> /// <param name="db">数据库操作对象</param> /// <param name="columns">需要返回的列,不提供任何列名时默认将返回所有列</param> public IList <ClassCGScopeEntity> GetClassCGScopeList(int pagesize, int pageindex, ref int recordCount) { string sql = @"SELECT [Id],[ClassId],[ScopeClassName],[IsActive],[Sort] FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id desc) AS ROWNUMBER, [Id],[ClassId],[ScopeClassName],[IsActive],[Sort] from dbo.[ClassCGScope] WITH(NOLOCK) WHERE 1=1 ) as temp where rownumber BETWEEN ((@PageIndex - 1) * @PageSize + 1) AND @PageIndex * @PageSize" ; string sql2 = @"Select count(1) from dbo.[ClassCGScope] with (nolock) "; IList <ClassCGScopeEntity> entityList = new List <ClassCGScopeEntity>(); DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@PageIndex", DbType.Int32, pageindex); db.AddInParameter(cmd, "@PageSize", DbType.Int32, pagesize); using (IDataReader reader = db.ExecuteReader(cmd)) { while (reader.Read()) { ClassCGScopeEntity entity = new ClassCGScopeEntity(); entity.Id = StringUtils.GetDbInt(reader["Id"]); entity.ClassId = StringUtils.GetDbInt(reader["ClassId"]); entity.ScopeClassName = StringUtils.GetDbString(reader["ScopeClassName"]); entity.IsActive = StringUtils.GetDbInt(reader["IsActive"]); entity.Sort = StringUtils.GetDbInt(reader["Sort"]); entityList.Add(entity); } } cmd = db.GetSqlStringCommand(sql2); using (IDataReader reader = db.ExecuteReader(cmd)) { if (reader.Read()) { recordCount = StringUtils.GetDbInt(reader[0]); } else { recordCount = 0; } } return(entityList); }
/// <summary> /// 判断当前节点是否已存在相同的 /// </summary> /// <param name="entity"></param> /// <returns></returns> public int ExistNum(ClassCGScopeEntity entity) { ///id=0,判断总数,ID>0判断除自己之外的总数 string sql = @"Select count(1) from dbo.[ClassCGScope] WITH(NOLOCK) "; string where = "where ScopeClassName=@ScopeClassName AND ParentId=@ParentId AND ScopeType=@ScopeType "; sql = sql + where; DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@ScopeClassName", DbType.String, entity.ScopeClassName); db.AddInParameter(cmd, "@ParentId", DbType.String, entity.ParentId); db.AddInParameter(cmd, "@ScopeType", DbType.String, entity.ScopeType); object identity = db.ExecuteScalar(cmd); if (identity == null || identity == DBNull.Value) { return(0); } return(Convert.ToInt32(identity)); }
/// <summary> /// 插入一条记录到表ClassCGScope,如果表中存在自增字段,则返回值为新记录的自增字段值,否则返回0 /// </summary> /// <param name="db">数据库操作对象</param> /// <param name="classCGScope">待插入的实体对象</param> public int AddClassCGScope(ClassCGScopeEntity entity) { string sql = @"insert into ClassCGScope( [ClassId],[ScopeClassName],[IsActive],[Sort],ParentId,IsRoot,ScopeType)VALUES ( @ClassId,@ScopeClassName,@IsActive,@Sort,@ParentId,@IsRoot,@ScopeType); SELECT SCOPE_IDENTITY();" ; DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@ClassId", DbType.Int32, entity.ClassId); db.AddInParameter(cmd, "@ScopeClassName", DbType.String, entity.ScopeClassName); db.AddInParameter(cmd, "@IsActive", DbType.Int32, entity.IsActive); db.AddInParameter(cmd, "@Sort", DbType.Int32, entity.Sort); db.AddInParameter(cmd, "@ParentId", DbType.Int32, entity.ParentId); db.AddInParameter(cmd, "@IsRoot", DbType.Int32, entity.IsRoot); db.AddInParameter(cmd, "@ScopeType", DbType.Int32, entity.ScopeType); object identity = db.ExecuteScalar(cmd); if (identity == null || identity == DBNull.Value) { return(0); } return(Convert.ToInt32(identity)); }
public string GetCGMemIdsByCarBrand(int scopetype, string carbrandname, string oldmemids) { if (scopetype == (int)ScopeTypeEnum.Normal) { ClassCGScopeEntity scopeparent = ClassCGScopeBLL.Instance.GetParentByScopeName(carbrandname); if (scopeparent != null && scopeparent.Id > 0 && !string.IsNullOrEmpty(scopeparent.ScopeClassName)) { carbrandname += "|" + scopeparent.ScopeClassName; } } else { CarTypeBrandEntity carbrandparent = CarTypeBrandBLL.Instance.GetParentByName(carbrandname); if (carbrandparent != null && carbrandparent.Id > 0 && !string.IsNullOrEmpty(carbrandparent.BrandName)) { carbrandname += "|" + carbrandparent.BrandName; } } string memids = MemCGScopeDA.Instance.GetCGMemIdsByCarBrand(carbrandname, oldmemids); return(memids.Trim(',')); }
public ClassCGScopeEntity GetParentByScopeName(string name) { string sql = @"SELECT [Id],[ClassId],[ScopeClassName],[IsActive],[Sort],ParentId,IsRoot FROM dbo.[ClassCGScope] WITH(NOLOCK) WHERE [Id]=(Select top 1 ParentId from dbo.[ClassCGScope] WITH(NOLOCK) where ScopeClassName=@ScopeClassName and ParentId>0 )" ; DbCommand cmd = db.GetSqlStringCommand(sql); db.AddInParameter(cmd, "@ScopeClassName", DbType.String, name); ClassCGScopeEntity entity = new ClassCGScopeEntity(); using (IDataReader reader = db.ExecuteReader(cmd)) { if (reader.Read()) { entity.Id = StringUtils.GetDbInt(reader["Id"]); entity.ClassId = StringUtils.GetDbInt(reader["ClassId"]); entity.ScopeClassName = StringUtils.GetDbString(reader["ScopeClassName"]); entity.IsActive = StringUtils.GetDbInt(reader["IsActive"]); entity.Sort = StringUtils.GetDbInt(reader["Sort"]); } } return(entity); }
/// <summary> /// 更新一条ClassCGScope记录。 /// 该方法提供给界面等UI层调用 /// </summary> /// <param name="classCGScope">待更新的实体对象</param> /// <param name="columns">要更新的列名,不提供任何列名时默认将更新主键之外的所有列</param> public int UpdateClassCGScope(ClassCGScopeEntity classCGScope) { return(ClassCGScopeDA.Instance.UpdateClassCGScope(classCGScope)); }
/// <summary> /// 判断对象是否存在 /// </summary> /// <param name="dicEnum"></param> /// <returns></returns> public bool IsExist(ClassCGScopeEntity classCGScope) { return(ClassCGScopeDA.Instance.ExistNum(classCGScope) > 0); }