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);
}
public static List <UserEntity> GetUsers(string env, int?tzOffsetMinutes, int?userId, string userName, bool?active)
{
var connectionString = ConfigurationManager.ConnectionStrings[env].ConnectionString;
var resultEntity = new List <UserEntity>();
using (var connection = new SqlConnection(connectionString))
{
using (var command = new SqlCommand("[GetUsers]")
{
CommandType = CommandType.StoredProcedure, Connection = connection
})
{
// =====================================================================================================
// Add Parameters
// =====================================================================================================
//SqlParameter[] paramCollection = new SqlParameter[0];
if (userId != null)
{
var param_UserId = new SqlParameter("@UserId", userId)
{
Direction = ParameterDirection.Input,
};
command.Parameters.Add(param_UserId);
}
if (userName != String.Empty)
{
var param_userName = new SqlParameter("@UserName", userName)
{
Direction = ParameterDirection.Input,
SqlDbType = System.Data.SqlDbType.VarChar,
Size = 50
};
command.Parameters.Add(param_userName);
}
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) // does this always evaluate to true? Why is this line here?
{
while (reader.Read())
{
var entity = new UserEntity();
entity.UserId = NullHelper.GetInt32FromReader(reader, "UserId");
entity.UserName = NullHelper.GetStringFromReader(reader, "UserName");
entity.FirstName = NullHelper.GetStringFromReader(reader, "FirstName");
entity.LastName = NullHelper.GetStringFromReader(reader, "LastName");
entity.ActiveCode = NullHelper.GetBooleanFromReader(reader, "ActiveCode");
entity.ActiveStatus = NullHelper.GetStringFromReader(reader, "ActiveStatus");
entity.Email = NullHelper.GetStringFromReader(reader, "Email");
entity.CreatedDateTime = NullHelper.GetDateFromReader(reader, "CreatedDateTime", tzOffsetMinutes);
entity.CreatedBy = NullHelper.GetInt32FromReader(reader, "CreatedBy");
entity.CreatedByUserName = NullHelper.GetStringFromReader(reader, "CreatedByUserName");
entity.UpdatedDateTime = NullHelper.GetDateFromReader(reader, "UpdatedDateTime", tzOffsetMinutes);
entity.UpdatedBy = NullHelper.GetInt32FromReader(reader, "UpdatedBy");
entity.UpdatedByUserName = NullHelper.GetStringFromReader(reader, "UpdatedByUserName");
entity.FullName = NullHelper.GetStringFromReader(reader, "FullName");
resultEntity.Add(entity);
}
}
}
}
}
return(resultEntity);
}