public override async Task <GetAllRegistrationProcessResponse> SendGetAllRegistrationProcess(GetAllRegistrationProcessRequest request, ServerCallContext context)
{
var registrationProcess = new GetAllRegistrationProcessResponse();
using (var sql = new SqlConnection(_connectionString))
{
var query = @"
SELECT
[LT].[Name] AS [LevelTypeName],
[EV].[Name] AS [EnvironmentTypeName],
[RE].[Events],
[RE].[ReportDescription],
[RE].[ReportSource],
[RP].[RegistrationProcessID],
[RP].[CreatedDate]
FROM [dbo].[RegistrationProcess] AS [RP]
INNER JOIN [dbo].[Report] AS [RE] ON [RP].[ReportID] = [RE].[ReportID]
INNER JOIN [dbo].[LevelType] AS [LT] ON [RE].[LevelTypeID] = [LT].[LevelTypeID]
INNER JOIN [dbo].[EnvironmentType] AS [EV] ON [RP].[EnvironmentTypeID] = [EV].[EnvironmentTypeID]
WHERE [RP].[IsActive] = 1
ORDER BY [RP].[CreatedDate] DESC";
using (var command = new SqlCommand(query, sql))
{
await sql.OpenAsync();
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
registrationProcess.List.Add(new GetAllRegistrationProcessObject
{
LevelTypeName = reader.GetString(0),
EnvironmentTypeName = reader.GetString(1),
Events = reader.GetInt32(2),
ReportDescription = reader.GetString(3),
ReportSource = reader.GetString(4),
RegistrationProcessID = reader.GetGuid(5).ToString(),
CreatedDate = reader.GetDateTimeOffset(6).ToString()
});
}
reader.Close();
sql.Close();
}
}
return(registrationProcess);
}
public override async Task <GetAllRegistrationProcessResponse> FilterRegistrationProcess(FilterRegistrationProcessRequest request, ServerCallContext context)
{
var registrationProcess = new GetAllRegistrationProcessResponse();
using (var sql = new SqlConnection(_connectionString))
{
using var command = new SqlCommand(RegistrationProcessRequestSQL.QueryFilterRegistrationProcess(), sql);
await sql.OpenAsync();
command.Parameters.AddWithValue("@EnvFilterValue", request.EnvFilter);
command.Parameters.AddWithValue("@LevelFilterValue", request.LevelFilter);
command.Parameters.AddWithValue("@OrderByValue", request.OrderBy.ToLower().Equals("level") ? "level" : "events");
command.Parameters.AddWithValue("@SortDirectionValue", request.SortDirection.ToLower().Equals("asc") ? "asc" : "desc");
command.Parameters.AddWithValue("@SearchTypeValue", request.SearchType.ToLower().Equals("reportsource") ? "reportSource" : "reportDescription");
command.Parameters.AddWithValue("@SearchValueQ", request.SearchValue);
command.Parameters.AddWithValue("@IsActiveValue", request.IsActive);
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
registrationProcess.List.Add(new GetAllRegistrationProcessObject
{
LevelTypeName = reader.GetString(0),
EnvironmentTypeName = reader.GetString(1),
Events = reader.GetInt32(2),
ReportDescription = reader.GetString(3),
ReportSource = reader.GetString(4),
RegistrationProcessID = reader.GetGuid(5).ToString(),
CreatedDate = reader.GetDateTimeOffset(6).ToString()
});
}
reader.Close();
sql.Close();
}
return(registrationProcess);
}
