public static List <RoleEntity> GetRoles(string env, int?tzOffsetMinutes, bool?active, int?userId) { var resultEntity = new List <RoleEntity>(); var connectionString = ConfigurationManager.ConnectionStrings[env].ConnectionString; using (var connection = new SqlConnection(connectionString)) { using (var command = new SqlCommand("[GetRoles]") { CommandType = CommandType.StoredProcedure, Connection = connection }) { // ===================================================================================================== // Add Parameters // ===================================================================================================== if (userId != null) { var param_UserId = new SqlParameter("@UserId", userId) { Direction = ParameterDirection.Input, SqlDbType = System.Data.SqlDbType.Int, }; command.Parameters.Add(param_UserId); } if (active != null) { var param_Active = new SqlParameter("@Active", active) { Direction = ParameterDirection.Input, SqlDbType = System.Data.SqlDbType.Bit, }; command.Parameters.Add(param_Active); } connection.Open(); using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) { if (reader != null) { while (reader.Read()) { var entity = new RoleEntity(); entity.RoleId = NullHelper.GetInt32FromReader(reader, "RoleId"); entity.RoleName = NullHelper.GetStringFromReader(reader, "RoleName"); entity.Active = NullHelper.GetBooleanFromReader(reader, "Active"); entity.ActiveStatus = NullHelper.GetStringFromReader(reader, "ActiveStatus"); entity.CreatedDateTime = NullHelper.GetDateFromReader(reader, "CreatedDateTime", tzOffsetMinutes); entity.CreatedBy = NullHelper.GetInt32FromReaderNullable(reader, "CreatedBy"); resultEntity.Add(entity); } } } } } return(resultEntity); }