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); }