public UsersEntity GetUserByUserName(string username) { string strSql = @"SELECT u.*,c.CompanyName FROM [Users] u left join Companys c on u.CompanyID=c.ComID WHERE UserName =@UserName AND [IsDelete]=0"; Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { db.AddInParameter(dbCommand, "UserName", DbType.String, username.FilterSqlString()); UsersEntity model = null; using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { try { if (dataReader.Read()) { model = UsersEntity.ReaderBind(dataReader); } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(null); } } return(model); } }
/// <summary> /// Get an object entity /// </summary> public UsersEntity Get(int UserID) { StringBuilder strSql = new StringBuilder(); strSql.Append("select u.*,c.CompanyName as CompanyName from Users u left join Companys c on u.CompanyID=c.ComID "); strSql.Append(" where UserID=@UserID "); Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "UserID", DbType.Int32, UserID); UsersEntity model = null; using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { if (dataReader.Read()) { model = UsersEntity.ReaderBind(dataReader); } } return(model); } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); return(null); } } }
public SearchUserResponse SearchUsers(SearchUsersRequest request) { int start = request.CurrentPage * request.PageCount + 1 - request.PageCount; int end = request.CurrentPage * request.PageCount; string strSelCount = " SELECT COUNT(1) FROM [Users] u "; if (request.OrderExpression.ToLower().Equals("status")) { request.OrderExpression = " u.Status "; } string strOrderby = string.Format(" {0} {1} ", request.OrderExpression, request.OrderDirection); string strSelAttrs = " SELECT u.*,c.CompanyName FROM [Users] u left join Companys c on u.CompanyID=c.ComID "; string strSelAttrsOrderBy = string.Format(@" Order BY {0} ", strOrderby); string strSelPageModel = string.Format(@"SELECT * FROM( SELECT ROW_NUMBER() OVER( Order BY {0}) as INDEX_ID,u.*,c.CompanyName FROM [Users] u left join Companys c on u.CompanyID=c.ComID ", strOrderby); string strWherePageModel = @") NEW_TB WHERE INDEX_ID BETWEEN @Strat AND @End;"; StringBuilder strWhere = new StringBuilder(); strWhere.Append(" WHERE IsDelete=0 "); switch (request.SearchType) { case SearchUsersType.All: if (request.IsSunnet) { strWhere.Append(" AND [UserType] = 'SUNNET'"); } if (request.IsClient) { strWhere.Append(" AND [UserType] = 'CLIENT'"); } //if (!string.IsNullOrEmpty(request.Status) && request.Status.ToUpper() != "ALL") //{ // strWhere.Append(" AND u.Status=@Status "); //} strWhere.Append(" AND u.Status='ACTIVE'"); break; case SearchUsersType.List: strWhere.Append(" AND ( UserName like @Keywords OR FirstName like @Keywords OR LastName like @Keywords )"); if (request.CompanyID != 0) { strWhere.AppendFormat(" AND (CompanyID={0})", request.CompanyID); } if (!string.IsNullOrEmpty(request.Status) && request.Status.ToUpper() != "All".ToUpper()) { strWhere.Append(" AND u.Status=@Status "); } break; case SearchUsersType.Company: strWhere.Append(" AND u.Status='ACTIVE'"); strWhere.Append(" AND u.CompanyID =@CompanyID"); break; case SearchUsersType.CompanyByProject: strWhere.Append(" AND u.Status='ACTIVE'"); strWhere.Append(" AND u.CompanyID IN (SELECT CompanyID FROM Projects WHERE ProjectID = @ProjectID )"); break; case SearchUsersType.Project: strWhere.Append(" AND u.Status='ACTIVE'"); strWhere.Append(" AND u.UserID IN (SELECT UserID FROM ProjectUsers WHERE ProjectID = @ProjectID)"); break; case SearchUsersType.Role: strWhere.Append(" AND u.RoleID =@RoleID "); strWhere.Append(" AND u.Status='ACTIVE'"); break; case SearchUsersType.Ticket: strWhere.Append(" AND u.UserID in ( SELECT [UserID] FROM [TicketUsers] WHERE [TicketID]=@TicketID)"); break; default: break; } StringBuilder strSql = new StringBuilder(); if (request.IsPageModel) { strSql.Append(strSelCount); strSql.Append(strWhere); strSql.Append(";"); strSql.Append(strSelPageModel); strSql.Append(strWhere); strSql.Append(strWherePageModel); } else { strSql.Append(strSelAttrs); strSql.Append(strWhere); strSql.Append(strSelAttrsOrderBy); strSql.Append(";"); } SearchUserResponse response = new SearchUserResponse(); List <UsersEntity> list; Database db = DatabaseFactory.CreateDatabase(); using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString())) { try { db.AddInParameter(dbCommand, "UserID", DbType.Int32, request.UserID); db.AddInParameter(dbCommand, "Keywords", DbType.String, string.Format("%{0}%", request.Keywords.FilterSqlString())); db.AddInParameter(dbCommand, "Status", DbType.String, request.Status); db.AddInParameter(dbCommand, "CompanyID", DbType.Int32, request.CompanyID); db.AddInParameter(dbCommand, "ProjectID", DbType.Int32, request.ProjectID); db.AddInParameter(dbCommand, "RoleID", DbType.Int32, (int)request.Role); db.AddInParameter(dbCommand, "TicketID", DbType.Int32, request.TicketID); db.AddInParameter(dbCommand, "Strat", DbType.Int32, start); db.AddInParameter(dbCommand, "End", DbType.Int32, end); using (IDataReader dataReader = db.ExecuteReader(dbCommand)) { list = new List <UsersEntity>(); if (request.IsPageModel) { if (dataReader.Read()) { response.ResultCount = dataReader.GetInt32(0); dataReader.NextResult(); } } while (dataReader.Read()) { list.Add(UsersEntity.ReaderBind(dataReader)); } response.ResultList = list; } } catch (Exception ex) { WebLogAgent.Write(string.Format("[SQLText:{0},{1}Messages:\r\n{2}]", strSql.ToString(), base.FormatParameters(dbCommand.Parameters), ex.Message)); } } return(response); }