Archivo de la etiqueta: T-SQL

GENERANDO UNA API REST (JSON) [2]


Consideremos otro requisito, para evaluar como puede aprovecharse mejor las características de Entity Framework y emular esa funcionalidad en los casos en que no se pueda utilizar, o sea más conveniente otra forma de realizar la tarea.

En este ejemplo, estamos utilizando la misma base de datos explicada en Datos-para-demos

El requisito

Se necesita obtener la información estadística de casos, vacunaciones, etc. Por país, entre determinadas fechas, con las siguientes condiciones:

  • Si no se consigna fecha de inicio, se usa la primera disponible.
  • Si no se consigna la fecha de fin, se usa la última disponible.
  • Se debe retornar la información en lotes de a 100 entradas, con lo cual, se deberá recibir el número de página solicitado.

En este caso, se implementará en el controlador “Country

Entity Framework.

El código aprovecha las funcionalidades Fluent de EF para anidar las condiciones. Igualmente, por debajo, Entity Framework genera una sentencia acorde al motor de datos en uso, en este caso, SQL Server.

public async Task<ActionResult<IEnumerable<OwidCovidDatum>>> GetCountryData(
   int CountryId,
   DateTime? fromDate=null, 
   DateTime? toDate=null,
   int Page=1)
{
   fromDate=fromDate??
      (from el in _context.OwidCovidData orderby el.Date select el.Date).FirstOrDefault();
   toDate=toDate??
      (from el in _context.OwidCovidData orderby el.Date descending select el.Date).FirstOrDefault();
 
   return (from OwidCovidDatum el in 
              _context.OwidCovidData
              .Where(x=> x.Date>=fromDate && x.Date<=toDate && x.CountriesId==CountryId)
              .Skip((Page-1)*100)
              .Take(100select el).ToList();
}

Dapper

Utilizando los DapperRow de retorno, implementamos la llamada con una sentencia SQL que es casi igual a la generada automáticamente por EF.

[HttpGet]
public async Task<stringGetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
   int skip = ((Page - 1* 100);
   fromDate = fromDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MIN([o].[date]) FROM [Owid Covid Data] AS [o];");
   toDate = toDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MAX([o].[date]) FROM [Owid Covid Data] AS [o];");
   string sql = $@"SELECT 
       [o].[ContinentId]
     , [o].[CountriesId]
     , [o].[date]
     , [o].[hosp_patients]
     , [o].[hosp_patients_per_million]
     , [o].[icu_patients]
     , [o].[icu_patients_per_million]
     , [o].[new_cases]
     , [o].[new_cases_per_million]
     , [o].[new_cases_smoothed]
     , [o].[new_cases_smoothed_per_million]
     , [o].[new_deaths]
     , [o].[new_deaths_per_million]
     , [o].[new_deaths_smoothed]
     , [o].[new_deaths_smoothed_per_million]
     , [o].[new_tests]
     , [o].[new_tests_per_thousand]
     , [o].[new_tests_smoothed]
     , [o].[new_tests_smoothed_per_thousand]
     , [o].[new_vaccinations]
     , [o].[new_vaccinations_smoothed]
     , [o].[new_vaccinations_smoothed_per_million]
     , [o].[people_fully_vaccinated]
     , [o].[people_fully_vaccinated_per_hundred]
     , [o].[people_vaccinated]
     , [o].[people_vaccinated_per_hundred]
     , [o].[positive_rate]
     , [o].[tests_per_case]
     , [o].[tests_units]
     , [o].[total_cases]
     , [o].[total_cases_per_million]
     , [o].[total_deaths]
     , [o].[total_deaths_per_million]
     , [o].[total_tests]
     , [o].[total_tests_per_thousand]
     , [o].[total_vaccinations]
     , [o].[total_vaccinations_per_hundred]
     , [o].[weekly_hosp_admissions]
     , [o].[weekly_hosp_admissions_per_million]
     , [o].[weekly_icu_admissions]
     , [o].[weekly_icu_admissions_per_million]
   FROM
      [Owid Covid Data] AS[o]
   WHERE(([o].[date] >= '{fromDate.Value.ToString("u").Substring(0,10)}')
         AND([o].[date] <= '{ toDate.Value.ToString("u").Substring(010)}'))
        AND([o].[CountriesId] = {CountryId})
   ORDER BY
       date
   OFFSET {skip} ROWS FETCH NEXT 100 ROWS ONLY; ";
   var result =await _dal.GetDataAsync(sql);
   string json = JsonSerializer.Serialize(result, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Código

Al igual que en el ejemplo previo, creamos un objeto Command con parámetros que retorne una cadena de caracteres con el JSON resultante, implementado en la sentencia SQL.

         [HttpGet]
   public async Task<stringGetCountryData(
      int CountryId, 
      DateTime? fromDate = null, 
      DateTime? toDate = null, 
      int Page = 1)
   {
      int skip = ((Page - 1* 100);
      fromDate = fromDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MIN([o].[date]) FROM [Owid Covid Data] AS [o];");
      toDate = toDate ??
      await _dal.GetValueAsync<DateTime>("SELECT MAX([o].[date]) FROM [Owid Covid Data] AS [o];");
      string sql = $@"SELECT 
       [o].[ContinentId]
     , [o].[CountriesId]
     , [o].[date]
     , [o].[hosp_patients]
     , [o].[hosp_patients_per_million]
     , [o].[icu_patients]
     , [o].[icu_patients_per_million]
     , [o].[new_cases]
     , [o].[new_cases_per_million]
     , [o].[new_cases_smoothed]
     , [o].[new_cases_smoothed_per_million]
     , [o].[new_deaths]
     , [o].[new_deaths_per_million]
     , [o].[new_deaths_smoothed]
     , [o].[new_deaths_smoothed_per_million]
     , [o].[new_tests]
     , [o].[new_tests_per_thousand]
     , [o].[new_tests_smoothed]
     , [o].[new_tests_smoothed_per_thousand]
     , [o].[new_vaccinations]
     , [o].[new_vaccinations_smoothed]
     , [o].[new_vaccinations_smoothed_per_million]
     , [o].[people_fully_vaccinated]
     , [o].[people_fully_vaccinated_per_hundred]
     , [o].[people_vaccinated]
     , [o].[people_vaccinated_per_hundred]
     , [o].[positive_rate]
     , [o].[tests_per_case]
     , [o].[tests_units]
     , [o].[total_cases]
     , [o].[total_cases_per_million]
     , [o].[total_deaths]
     , [o].[total_deaths_per_million]
     , [o].[total_tests]
     , [o].[total_tests_per_thousand]
     , [o].[total_vaccinations]
     , [o].[total_vaccinations_per_hundred]
     , [o].[weekly_hosp_admissions]
     , [o].[weekly_hosp_admissions_per_million]
     , [o].[weekly_icu_admissions]
     , [o].[weekly_icu_admissions_per_million]
   FROM
      [Owid Covid Data] AS[o]
   WHERE(([o].[date] >= @fromDate)
         AND([o].[date] <= @toDate))
        AND([o].[CountriesId] = @CountryId)
   ORDER BY
       date
   OFFSET @skip ROWS FETCH NEXT 100 ROWS ONLY FOR JSON PATH; ";
      SqlCommand com = _dal.CreateCommand(sql);
      com.Parameters.AddWithValue("@CountryId", CountryId);
      com.Parameters.AddWithValue("@fromDate", fromDate);
      com.Parameters.AddWithValue("@toDate", toDate);
      com.Parameters.AddWithValue("@skip", skip);
      return await _dal.GetJSONDataAsync(com);
   }
}

Las sentencias SQL

Para facilitar la comparativa, aquí están, juntas, las tres sentencias SQL utilizadas.

Entity Framework

EXEC [sp_executesql] 
     N'SELECT 
    [o].[ContinentId]
  , [o].[CountriesId]
  , [o].[date]
  , [o].[hosp_patients]
  , [o].[hosp_patients_per_million]
  , [o].[icu_patients]
  , [o].[icu_patients_per_million]
  , [o].[new_cases]
  , [o].[new_cases_per_million]
  , [o].[new_cases_smoothed]
  , [o].[new_cases_smoothed_per_million]
  , [o].[new_deaths]
  , [o].[new_deaths_per_million]
  , [o].[new_deaths_smoothed]
  , [o].[new_deaths_smoothed_per_million]
  , [o].[new_tests]
  , [o].[new_tests_per_thousand]
  , [o].[new_tests_smoothed]
  , [o].[new_tests_smoothed_per_thousand]
  , [o].[new_vaccinations]
  , [o].[new_vaccinations_smoothed]
  , [o].[new_vaccinations_smoothed_per_million]
  , [o].[people_fully_vaccinated]
  , [o].[people_fully_vaccinated_per_hundred]
  , [o].[people_vaccinated]
  , [o].[people_vaccinated_per_hundred]
  , [o].[positive_rate]
  , [o].[tests_per_case]
  , [o].[tests_units]
  , [o].[total_cases]
  , [o].[total_cases_per_million]
  , [o].[total_deaths]
  , [o].[total_deaths_per_million]
  , [o].[total_tests]
  , [o].[total_tests_per_thousand]
  , [o].[total_vaccinations]
  , [o].[total_vaccinations_per_hundred]
  , [o].[weekly_hosp_admissions]
  , [o].[weekly_hosp_admissions_per_million]
  , [o].[weekly_icu_admissions]
  , [o].[weekly_icu_admissions_per_million]
FROM 
   [Owid Covid Data] AS [o]
WHERE(([o].[date] >= @__fromDate_0)
      AND ([o].[date] <= @__toDate_1))
     AND ([o].[CountriesId] = @__CountryId_2)
ORDER BY
(
    SELECT 
        1
)
OFFSET @__p_3 ROWS FETCH NEXT @__p_4 ROWS ONLY;'
   , N'@__fromDate_0 datetime,@__toDate_1 datetime,@__CountryId_2 int,@__p_3 int,@__p_4 int'
   , @__fromDate_0 = '2020-01-01 00:00:00'
   , @__toDate_1 = '2021-06-11 00:00:00'
   , @__CountryId_2 = 4
   , @__p_3 = 300
   , @__p_4 = 100;


Dapper

SELECT 
    [o].[ContinentId]
  , [o].[CountriesId]
  , [o].[date]
  , [o].[hosp_patients]
  , [o].[hosp_patients_per_million]
  , [o].[icu_patients]
  , [o].[icu_patients_per_million]
  , [o].[new_cases]
  , [o].[new_cases_per_million]
  , [o].[new_cases_smoothed]
  , [o].[new_cases_smoothed_per_million]
  , [o].[new_deaths]
  , [o].[new_deaths_per_million]
  , [o].[new_deaths_smoothed]
  , [o].[new_deaths_smoothed_per_million]
  , [o].[new_tests]
  , [o].[new_tests_per_thousand]
  , [o].[new_tests_smoothed]
  , [o].[new_tests_smoothed_per_thousand]
  , [o].[new_vaccinations]
  , [o].[new_vaccinations_smoothed]
  , [o].[new_vaccinations_smoothed_per_million]
  , [o].[people_fully_vaccinated]
  , [o].[people_fully_vaccinated_per_hundred]
  , [o].[people_vaccinated]
  , [o].[people_vaccinated_per_hundred]
  , [o].[positive_rate]
  , [o].[tests_per_case]
  , [o].[tests_units]
  , [o].[total_cases]
  , [o].[total_cases_per_million]
  , [o].[total_deaths]
  , [o].[total_deaths_per_million]
  , [o].[total_tests]
  , [o].[total_tests_per_thousand]
  , [o].[total_vaccinations]
  , [o].[total_vaccinations_per_hundred]
  , [o].[weekly_hosp_admissions]
  , [o].[weekly_hosp_admissions_per_million]
  , [o].[weekly_icu_admissions]
  , [o].[weekly_icu_admissions_per_million]
FROM 
   [Owid Covid Data] AS [o]
WHERE(([o].[date] >= '01/01/2020 00:00:00')
      AND ([o].[date] <= '06/11/2021 00:00:00'))
     AND ([o].[CountriesId] = 4)
ORDER BY 
    date
OFFSET 300 ROWS FETCH NEXT 100 ROWS ONLY;

Código

EXEC [sp_executesql] 
     N'SELECT 
             [o].[ContinentId]
           , [o].[CountriesId]
           , [o].[date]
           , [o].[hosp_patients]
           , [o].[hosp_patients_per_million]
           , [o].[icu_patients]
           , [o].[icu_patients_per_million]
           , [o].[new_cases]
           , [o].[new_cases_per_million]
           , [o].[new_cases_smoothed]
           , [o].[new_cases_smoothed_per_million]
           , [o].[new_deaths]
           , [o].[new_deaths_per_million]
           , [o].[new_deaths_smoothed]
           , [o].[new_deaths_smoothed_per_million]
           , [o].[new_tests]
           , [o].[new_tests_per_thousand]
           , [o].[new_tests_smoothed]
           , [o].[new_tests_smoothed_per_thousand]
           , [o].[new_vaccinations]
           , [o].[new_vaccinations_smoothed]
           , [o].[new_vaccinations_smoothed_per_million]
           , [o].[people_fully_vaccinated]
           , [o].[people_fully_vaccinated_per_hundred]
           , [o].[people_vaccinated]
           , [o].[people_vaccinated_per_hundred]
           , [o].[positive_rate]
           , [o].[tests_per_case]
           , [o].[tests_units]
           , [o].[total_cases]
           , [o].[total_cases_per_million]
           , [o].[total_deaths]
           , [o].[total_deaths_per_million]
           , [o].[total_tests]
           , [o].[total_tests_per_thousand]
           , [o].[total_vaccinations]
           , [o].[total_vaccinations_per_hundred]
           , [o].[weekly_hosp_admissions]
           , [o].[weekly_hosp_admissions_per_million]
           , [o].[weekly_icu_admissions]
           , [o].[weekly_icu_admissions_per_million]
         FROM
            [Owid Covid Data] AS[o]
         WHERE(([o].[date] >= @fromDate)
               AND([o].[date] <= @toDate))
              AND([o].[CountriesId] = @CountryId)
         ORDER BY
             date
         OFFSET @skip ROWS FETCH NEXT 100 ROWS ONLY FOR JSON PATH; '
   , N'@CountryId int,@fromDate datetime,@toDate datetime,@skip int'
   , @CountryId = 4
   , @fromDate = '2020-01-01 00:00:00'
   , @toDate = '2021-06-11 00:00:00'
   , @skip = 200;

Es llamativo que en el caso de código, utiliza sp_executesql, al igual que EF, lo cual demora un poco la ejecución.

Parece que esto habrá que mejorarlo en la siguiente publicación.

Rendimiento

Se utilizó el mismo procedimiento que en la publicación anterior, para evaluar los resultados.

Como se ve, la cosa ha mejorado bastante para EF, aunque en este caso, Dapper parece ser quien da mejor resultado.

La diferencia está, precisamente, el sp_executesql.

GENERATING A REST API (JSON)


I’m not going to go into detail here describing what is REST (Representational State Transfer) an API (Application Programming Interface) or JSON (JavaScript Object Notation). I start from the base that is already known to which we refer.

In this case, using the database published in the previous entry Data for demos, I will present how to expose the information from that database, in a read-only API.

At the same time, I will try to show advantages and disadvantages of different methods to achieve the same objective.

Entity Framework

Although in general, I dislike its use, I will try to include it in each demonstration, to see what advantages it brings, and the disadvantages that arise.

The project within the solution is ApiRestEF

Dapper

As a data access package/library, it allows you to easily obtain information from databases.

The project, within the solution is ApiRestDapper

Coding directly

In this case, I’ll be showing how to do, the same thing, but step by step, without libraries.

The project, within the solution is ApiRestCode

First requirement

A method is needed to obtain the information that is displayed, for a continent indicated as a parameter.

ContinentCountryTotal CasesTotal DeathsTotal Cases per MillionTotal Deaths per MillionPopulationPopulation DensityGDP per Capita
AfricaSeychelles12466461267644689834020826382
AfricaCape Verde31433271565354875559881366223
AfricaTunisia36265813305306851126118186187410849
AfricaSouth Africa17220865741029036968593086904712295
AfricaLibya1883863155274164596871287417882
AfricaBotswana59480896252933812351625415807
AfricaNamibia6137496824154381254091639542
AfricaEswatini18705676161235831160164797739
AfricaMorocco52276591921416324936910558807485
AfricaDjibouti1157015411711156988002412705
AfricaGabon2469615611096702225728816562

Entity Framework

The code used obtains the continent and its constituent countries in a single query but required to go through the countries, to obtain from each one, the demographic data.

As I said, I don’t like EF and maybe that’s why my research into methods to do it, may not have found another way.

Of course, if someone offers another proposal in the comments, I’ll add it here, and I’ll also proceed to evaluate the corresponding metric.

public async Task<ActionResult<string>> GetContinent(int id)
{
   var continent = await _context.Continents.FindAsync(id);
   continent.Countries = await _context.Countries
     .Where(x => x.ContinentId == id)
     .Select(x => new Country
     {
        Country1 = x.Country1,
        Id = x.Id,
        ContinentId = x.ContinentId
     }
     )
     .ToListAsync();
   foreach (var item in continent.Countries)
   {
      item.OwidCountriesData = await _context.OwidCountriesData
         .Where(x => x.CountriesId == item.Id)
         .Select(x => new OwidCountriesDatum
         {
            CountriesId = item.Id,
            TotalCases = x.TotalCases,
            TotalDeaths = x.TotalDeaths,
            TotalCasesPerMillion = x.TotalCasesPerMillion,
            TotalDeathsPerMillion = x.TotalDeathsPerMillion,
            Population = x.Population,
            PopulationDensity = x.PopulationDensity,
            GdpPerCapita = x.GdpPerCapita
         }).ToListAsync();
   }
 
   if (continent == null)
   {
      return NotFound();
   }
   string json = JsonSerializer.Serialize(continent, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Dapper

For the case of implementation with Dapper, we directly use the “DapperRow” types as the query return, thus decreasing the mapping between columns and properties. If defined classes were used, the response time would surely be longer.

public async Task<ActionResult<string>> GetContinent(int id)
{
   string sql = @"
                  SELECT 
                       [C].[Continent]
                     , [CO].[Country]
                     , [D].[total_cases]
                     , [D].[total_deaths]
                     , [D].[total_cases_per_million]
                     , [D].[total_deaths_per_million]
                     , [D].[population]
                     , [D].[population_density]
                     , [D].[gdp_per_capita]
                   FROM
                      [OwidCountriesData] AS[D]
                      INNER JOIN
                        [Continents] AS[C]
                      ON[D].[ContinentId] = [C].[Id]
                        INNER JOIN
                          [Countries] AS[CO]
                        ON[D].[CountriesId] = [CO].[Id]
                   WHERE([C].[Id] = @continent)
                   ORDER BY
                       [D].[total_cases_per_million] DESC".Replace("@continent", id.ToString()); ;
   var continent = await _dal.GetDataAsync(sql);
   if (continent == null)
   {
      return NotFound();
   }
   string json = JsonSerializer.Serialize(continent, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Only Code

Finally, for direct query by code, we optimize using the FOR JOSN modifier, and then directly obtaining the resulting JSON string.

public async Task<ActionResult<string>> GetContinent(int id)
{
   string sql = @"    SELECT 
                          [C].[Continent]
                        , [CO].[Country]
                        , [D].[total_cases]
                        , [D].[total_deaths]
                        , [D].[total_cases_per_million]
                        , [D].[total_deaths_per_million]
                        , [D].[population]
                        , [D].[population_density]
                        , [D].[gdp_per_capita]
                      FROM 
                         [OwidCountriesData] AS [D]
                         INNER JOIN
                           [Continents] AS [C]
                         ON [D].[ContinentId] = [C].[Id]
                           INNER JOIN
                             [Countries] AS [CO]
                           ON [D].[CountriesId] = [CO].[Id]
                      WHERE([C].[Id] = @continent)
                      ORDER BY 
                          [D].[total_cases_per_million] DESC FOR JSON AUTO, INCLUDE_NULL_VALUES, ROOT('CountriesInfo');";
   SqlCommand com = _dal.CreateCommand(sql);
   com.Parameters.AddWithValue("@continent", id);
   return await _dal.GetJSONDataAsync(com);
 
}

Note

In both the Dapper and code projects, a minimal data access layer was built to emulate functionality similar to that provided by EF-generated code.

Performance

The graph below shows the comparison in CPU utilization, lifetime, and disk reads, in each of the cases.
A picture is worth a thousand words. 🙂

In the graph, values are evaluated only from the point of view of the database, not the .Net code, nor its runtime. I will add this in the next installment.

Generando una API REST (JSON)


No voy a entrar aquí en detalles de descripción de lo que es REST (Representational State Transfer) una API (Application Programming Interface) o JSON (JavaScript Object Notation). Parto de la base que ya se conoce a que nos referimos.

En este caso, utilizando la base de datos publicada en la entrada anterior Datos para demos, presentaré como exponer la información de esa base, en una API de sólo lectura.

Al mismo tiempo, intentaré mostrar ventajas y desventajas de distintos métodos para lograr el mismo objetivo.

Entity Framework

Aunque en líneas generales, me disgusta bastante su utilización, intentaré incluirlo en cada demostración, por ver que ventajas aporta, y los inconvenientes que surjan.

El proyecto dentro de la solución es ApiRestEF

Dapper

Como paquete/biblioteca de acceso a datos, permite fácilmente obtener información de bases de datos.

El proyecto, dentro de la solución es ApiRestDapper

Código directo

En este caso, estaré mostrando como hacer, lo mismo, pero paso a paso, sin bibliotecas.

El proyecto, dentro de la solución es ApiRestCode

Primer requisito.

Se necesita un mecanismo por el cual obtener la información que se muestra, para un continente indicado como parámetro.

ContinentCountryTotal CasesTotal DeathsTotal Cases per MillionTotal Deaths per MillionPopulationPopulation DensityGDP per Capita
AfricaSeychelles12466461267644689834020826382
AfricaCape Verde31433271565354875559881366223
AfricaTunisia36265813305306851126118186187410849
AfricaSouth Africa17220865741029036968593086904712295
AfricaLibya1883863155274164596871287417882
AfricaBotswana59480896252933812351625415807
AfricaNamibia6137496824154381254091639542
AfricaEswatini18705676161235831160164797739
AfricaMorocco52276591921416324936910558807485
AfricaDjibouti1157015411711156988002412705
AfricaGabon2469615611096702225728816562

Entity Framework

El código utilizado obtiene el continente y sus países constitutivos en una sola consulta pero requirió recorrer los países, para obtener de cada uno, los datos demográficos.

Como ya dije, no me gusta EF y quizás por ello, mi investigación de métodos para hacerlo, puede no haber encontrado otra forma.

Por supuesto, si alguien ofrece otra propuesta en los comentarios, la agregaré aquí, y también procederé a evaluar la métrica correspondiente.

public async Task<ActionResult<string>> GetContinent(int id)
{
   var continent = await _context.Continents.FindAsync(id);
   continent.Countries = await _context.Countries
     .Where(x => x.ContinentId == id)
     .Select(x => new Country
     {
        Country1 = x.Country1,
        Id = x.Id,
        ContinentId = x.ContinentId
     }
     )
     .ToListAsync();
   foreach (var item in continent.Countries)
   {
      item.OwidCountriesData = await _context.OwidCountriesData
         .Where(x => x.CountriesId == item.Id)
         .Select(x => new OwidCountriesDatum
         {
            CountriesId = item.Id,
            TotalCases = x.TotalCases,
            TotalDeaths = x.TotalDeaths,
            TotalCasesPerMillion = x.TotalCasesPerMillion,
            TotalDeathsPerMillion = x.TotalDeathsPerMillion,
            Population = x.Population,
            PopulationDensity = x.PopulationDensity,
            GdpPerCapita = x.GdpPerCapita
         }).ToListAsync();
   }
 
   if (continent == null)
   {
      return NotFound();
   }
   string json = JsonSerializer.Serialize(continent, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Dapper

para el caso de la implementación con Dapper, utilizamos directamente los tipos “DapperRow” como retorno de la consulta, disminuyendo así el mapeo entre columnas y propiedades. De usarse clases definidas, seguramente el tiempo de respuesta sería mayor.

public async Task<ActionResult<string>> GetContinent(int id)
{
   string sql = @"
                  SELECT 
                       [C].[Continent]
                     , [CO].[Country]
                     , [D].[total_cases]
                     , [D].[total_deaths]
                     , [D].[total_cases_per_million]
                     , [D].[total_deaths_per_million]
                     , [D].[population]
                     , [D].[population_density]
                     , [D].[gdp_per_capita]
                   FROM
                      [OwidCountriesData] AS[D]
                      INNER JOIN
                        [Continents] AS[C]
                      ON[D].[ContinentId] = [C].[Id]
                        INNER JOIN
                          [Countries] AS[CO]
                        ON[D].[CountriesId] = [CO].[Id]
                   WHERE([C].[Id] = @continent)
                   ORDER BY
                       [D].[total_cases_per_million] DESC".Replace("@continent", id.ToString()); ;
   var continent = await _dal.GetDataAsync(sql);
   if (continent == null)
   {
      return NotFound();
   }
   string json = JsonSerializer.Serialize(continent, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Código

Finalmente, para la consulta directa por código, optimizamos utilizando el modificador FOR JOSN, y obteniendo entonces directamente la cadena JSON resultante.

public async Task<ActionResult<string>> GetContinent(int id)
{
   string sql = @"    SELECT 
                          [C].[Continent]
                        , [CO].[Country]
                        , [D].[total_cases]
                        , [D].[total_deaths]
                        , [D].[total_cases_per_million]
                        , [D].[total_deaths_per_million]
                        , [D].[population]
                        , [D].[population_density]
                        , [D].[gdp_per_capita]
                      FROM 
                         [OwidCountriesData] AS [D]
                         INNER JOIN
                           [Continents] AS [C]
                         ON [D].[ContinentId] = [C].[Id]
                           INNER JOIN
                             [Countries] AS [CO]
                           ON [D].[CountriesId] = [CO].[Id]
                      WHERE([C].[Id] = @continent)
                      ORDER BY 
                          [D].[total_cases_per_million] DESC FOR JSON AUTO, INCLUDE_NULL_VALUES, ROOT('CountriesInfo');";
   SqlCommand com = _dal.CreateCommand(sql);
   com.Parameters.AddWithValue("@continent", id);
   return await _dal.GetJSONDataAsync(com);
 
}

Aclaración

Tanto en el proyecto Dapper como en el de código, se construyó una capa de acceso a datos mínima, para emular similar funcionalidad a la brindada por el código generado por EF.

Rendimiento

El gráfico inferior muestra la comparativa en utilización de CPU, tiempo de duración, y lecturas a disco, en cada uno de los casos.
Una imagen, vale más que mil palabras. 🙂

En el gráfico, solo se evalúan valores desde el punto de vista de la base de datos, no del código .Net, ni su tiempo de ejecución. Agregaré esto en la próxima entrega.

PAGING DATA, NOT CLIENT SIDE (2) (Blazor client)


In the previous post,Paging data, NOT client side. | Universidad Net, I described how to use OFFSET-FETCH pair to paginate data in the server side. In this one, I will describe an example of how to use it in a Web Assembly App.

Note: To test this code, you must create a new Blazor app in Visual Studio.

The Model.

The data model will be a class with two properties, one for the list of items and other with the page’s information.

This classes will be added in the Shared project of the Application.

The first class vary depending on the data you want to display, but the second one will be always the same, enhanced by some code.

Since the pager class will be a standardized and enhanced version of the data retrieved by using the stored procedure, and could be used in different projects, let’s define an interface for it.

IPagesInfo interface.

This is the code for the Interface.

public interface IPagesInfo
{
   #region Properties
 
   /// <summary>
   /// Gets or sets the current page.....
   /// </summary>
   System.Int32 CurrentPage { getset; }
 
   /// <summary>
   /// Gets the current first page number..
   /// </summary>
   Int32 FirstPageNumber { get; }
 
   /// <summary>
   /// Gets the HasNextGroup
   /// Gets a value indicating whether this instance has next page..
   /// </summary>
   Boolean HasNextGroup { get; }
 
   /// <summary>
   /// Gets the HasPreviousGroup
   /// Gets a value indicating whether this instance has previous page..
   /// </summary>
   Boolean HasPreviousGroup { get; }
 
   /// <summary>
   /// Gets the last page number..
   /// </summary>
   Int32 LastPageNumber { get; }
 
   /// <summary>
   /// Gets or sets the number of links to show..
   /// </summary>
   Int32 NumberOfLinks { getset; }
 
   /// <summary>
   /// Gets or sets the size of the page.....
   /// </summary>
   System.Int32 PageSize { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows skipped from the top of the select.....
   /// </summary>
   System.Int32 Skip { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows taken.....
   /// </summary>
   System.Int32 Take { getset; }
 
   /// <summary>
   /// Gets or sets the amount of items to display.
   /// </summary>
   System.Int32 TotalItems { getset; }
 
   /// <summary>
   /// Gets the total pages available to display..
   /// </summary>
   Int32 TotalPages { get; }
   /// <summary>
   /// Gets the page number for the previous group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   Int32 PreviousGroupStart { get; }
   /// <summary>
   /// Gets the  page number for the next group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   Int32 NextGroupStart { get; }
   #endregion
}

PagerInfo class.

Here, you have the code for the class implementing the IPagesInfo interface.

Notice the class is responsible of the calculations about page numbers displayed, if there are next or previous groups of pages, etc.

public class PagerInfo : IPagesInfo
{
   #region Fields
 
   /// <summary>
   /// Defines the lastPageNumber.
   /// </summary>
   internal Int32 lastPageNumber = 0;
 
   #endregion
 
   #region Properties
   public Int32 TotalPages
   {
      get => (Int32)Math.Ceiling(TotalItems / (Double)PageSize);
   }
   /// <summary>
   /// Gets the page number for the previous group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   public Int32 PreviousGroupStart => LastPageNumber - NumberOfLinks;
   /// <summary>
   /// Gets the  page number for the next group start.
   /// </summary>
   /// <value>
   /// The page number.
   /// </value>
   public Int32 NextGroupStart => LastPageNumber + 1;
 
   /// <summary>
   /// Gets or sets the current page.
   /// </summary>
   public System.Int32 CurrentPage { getset; }
 
   /// <summary>
   /// Gets the current first page number..
   /// </summary>
   public Int32 FirstPageNumber
   {
      get => LastPageNumber - (NumberOfLinks - 1);
   }
 
   /// <summary>
   /// Gets a value indicating whether this instance has next page.
   /// </summary>
   public Boolean HasNextGroup
   {
      get => CurrentPage + NumberOfLinks < TotalPages;
   }
 
   /// <summary>
   /// Gets a value indicating whether this instance has previous page.
   /// </summary>
   public Boolean HasPreviousGroup
   {
      get => CurrentPage > NumberOfLinks;
   }
 
   /// <summary>
   /// Gets the last page number..
   /// </summary>
   public Int32 LastPageNumber
   {
      get
      {
         lastPageNumber = (Int32)Math.Ceiling((Double)CurrentPage / NumberOfLinks) * NumberOfLinks;
         if (lastPageNumber > TotalPages)
         {
            lastPageNumber = TotalPages;
         }
         return lastPageNumber;
      }
   }
 
   /// <summary>
   /// Gets or sets the number of links to show..
   /// </summary>
   public Int32 NumberOfLinks { getset; } = 10;
 
   /// <summary>
   /// Gets or sets the size of the page.....
   /// </summary>
   public System.Int32 PageSize { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows skipped from the top of the select.....
   /// </summary>
   public System.Int32 Skip { getset; }
 
   /// <summary>
   /// Gets or sets the Qty of rows taken.....
   /// </summary>
   public System.Int32 Take { getset; }
 
   /// <summary>
   /// Gets or sets the amount of items to display.
   /// </summary>
   public System.Int32 TotalItems { getset; }
 
   /// <summary>
   /// Gets the total pages available to display..
   /// </summary>
 
   #endregion
 
}

The Data Class.

This class will contain your data and the IPagesInfo properties. In this sample, it will be called PersonsPager.

Note: You can easily create it by executing the stored procedure, copying the result and in a new code window (for example, an empty one created for PersonsPager), paste it by the Edit – Paste Special-Past JSON as Classes.

Then replace the names autogenerated by your own and change the type for the second one for the PagesInfo class.

The pasted code names the main class as Rootobject, which has been renamed to PersonsPager.

The Pager class will be changed by PagesInfo, and the Pager class defined could be removed.

This is the final code for the PersonsPager class.

public class PersonsPager
{
 
   public List[] List { getset; }
   public PagerInfo Pager { getset; }
}
public class List
{
   public int BusinessEntityID { getset; }
   public string FirstName { getset; }
   public string MiddleName { getset; }
   public string LastName { getset; }
}

The Pager Component.

You must add a Razor Component. In this sample, it is called Pager.razor.

In the UI code, I use an UL tag, adding a button for previous group of pages (when the user moves beyond the first set of page numbers), buttons for the different page numbers, and a button for the next group as well.

For the buttons, the pagination, page-item, and page-link classes are used.

<nav class="text-center">
   <ul class="pagination">
      @{//Previous page group button.
 
         string className = "page-item " + (!PagesInfo.HasPreviousGroup ? " disabled" : ""); //If there is in previous group, the button will be disabled
         <li class="@className">
            <button class="page-link "
                    @onclick="@(()=>
                                   {
                                      if (PagesInfo.HasPreviousGroup)
                                        ChangePage(PagesInfo.PreviousGroupStart);
                                   }
               )">
               ⏪
            </button>
         </li>
      }
      @*Buttons for page numbers*@
      @for (Int32 i = PagesInfo.FirstPageNumber; i <= PagesInfo.LastPageNumber; i++)
      {
         int pageSelector = i;
 
         className = "page-item " + (i == PagesInfo.CurrentPage ? " active" : "");
         <li class="@className">
            <button class="page-link" @onclick="@(()=>ChangePage(pageSelector))">@string.Format("{0:00}"pageSelector)</button>
         </li>
      }
      @{//Next page group button.
         className = "page-item  " + (!PagesInfo.HasNextGroup ? " disabled" : "");
         <li class="@className">
            <button class="page-link " @onclick="@(()=>
                                                      { if (PagesInfo.HasNextGroup)
                                                            ChangePage(PagesInfo.NextGroupStart);
                                                      }
               )">
               ⏩
            </button>
 
         </li>
      }
   </ul>
</nav>

In the code section, parameters are defined for:

  • an instance of a class implementing the IPagerInfo
  • a value to persist the selected page
  • an EventCallback to notify the client page about the changes in the selection by the user.

Finally, the ChangePage function called every time the user clicks ant of the buttons is defined to change the selected page and notify the client page.

@code {
   [Parameter]
   public IPagesInfo PagesInfo { getset; }
   [Parameter]
   public int SelectedPage { get => PagesInfo.CurrentPage; set => PagesInfo.CurrentPage = value; }
   [Parameter]
   public EventCallback OnPageChange { getset; }
   void ChangePage(int newPage)
   {
      PagesInfo.CurrentPage = newPage;
      SelectedPage = newPage;
      OnPageChange.InvokeAsync(SelectedPage);
   }
}

The controller.

To get the information from the database, you will need an API REST controller which returns the JSON string from the stored procedure.

The method must be decorated with the HttpGet attribute to react when the client page code calls it.

[Route("/[controller]")]
[ApiController]
public class PersonDataController : ControllerBase
{
   public PersonDataController(IConfiguration configuration)
   {
      Configuration = configuration;
   }
 
   public IConfiguration Configuration { get; }
 
   [HttpGet]
   public async Task<stringGetPersons(int selectedPageint pageSize = 10)
   {
      using SqlConnection con = new SqlConnection(Configuration.GetConnectionString("aw"));
      try
      {
         SqlCommand com = new SqlCommand("[Person].[Person_GetforPager]"con);
         com.CommandType = System.Data.CommandType.StoredProcedure;
         com.Parameters.AddWithValue("@skip", (selectedPage == 0 ? 0 : selectedPage - 1* pageSize);
         com.Parameters.AddWithValue("@take"pageSize);
         con.Open();
         string values = (await com.ExecuteScalarAsync()).ToString();
         return values;
      }
      catch (System.Exception ex)
      {
 
         throw;
      }
   }
}

The client page.

A new component will be defined to display the data.

It will contain any type of display for your data (in the sample, it is just a UL list), and an instance of the Pager component.

As usual in a Web assembly app, you must check if you have data to display before performing the UI generation.

Moreover, you can decide if you need display the pager component in case of no more than one page is needed to display the information.

@inject HttpClient httpClient
<h3>Persons</h3>
@if (result != null)
{
   <div>@result.StatusCode</div>
   <div>@result.Content.ReadAsStringAsync().Result</div>
   <div>@httpClient.BaseAddress</div>
}
@if (personsPager != null && personsPager.List.Count() > 0)
{
   <ul>
      @foreach (var item in personsPager.List)
      {
         <li>@item.LastName</li>
 
      }
   </ul>
   @if (personsPager.Pager != null && personsPager.Pager.TotalPages > 1)
   {
      <Pager PagesInfo="PagesInfo" OnPageChange="ChangePage" />
      @**@
   }
}
else SelectedPage = 1;

The code of this page will call the controller get method to retrieve the information.

@code {
   PersonsPager personsPager;
   int SelectedPage;
   public IPagesInfo PagesInfo { get => personsPager.Pager; }
   HttpResponseMessage result;
   protected async override Task OnInitializedAsync()
   {
      await GetDataAsync();
   }
   async void ChangePage()
   {
      SelectedPage = personsPager.Pager.CurrentPage;
      //SelectedPage = PagesInfo.CurrentPage;
      await GetDataAsync();
      this.StateHasChanged();
 
   }
   async Task GetDataAsync()
   {
      //result = await httpClient.GetAsync($"PersonData?Selectedpage={SelectedPage}");
      personsPager = await httpClient.GetFromJsonAsync<PersonsPager>($"PersonData?Selectedpage={SelectedPage}");
 
   }
 
}

And this is the result:

Persons page with Pager sample
Persons page with Pager sample

Note: The “aw” connection string points to the AdventureWorks2017 database where the stored procedure from the previous post has been created.

BTW here is the sample

Paging data, NOT client side.


It is frequent to make web sites, mobile, UWP or Windows Applications, which need to present information of several rows which must be presented in pages of data.

That could be accomplished by 3 basic methods:

  1. Send the content to the client and manage the pagination in it, for example, using some of the JScript frameworks available.
  2. Using some sort of LinQ sentence, against EF, which will get the group of data after retrieving the entire set from the database.
  3. Get the exact set of rows needed each time, by using T-SQL from SQL Server

The third method is the most responsive, since it processes the selection in the server side, and reduce the amount of traffic between server, application level and client UI.

This is how I get the information and paging support by using T-SQL.

The OFFSET – FETCH statements.

The basic tool for getting pages of data is the combination of OFFSET <n> FETCH <c>.

The n value is the number of rows excluded from the beginning of the result set, and the c value, the quantity to return.

Any query you create, could be modified by this pair of statements.

Note: This example, uses the AdventureWorks2017 sample database, which you can get here: https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure

For example, let us get the employees form the database:

           SELECT
                  [BusinessEntityID],
                  [Title],
                  [FirstName],
                  [MiddleName],
                  [LastName]
              FROM
                   [Person].[Person]
              WHERE [PersonType] = N'EM'
              ORDER BY
                       [LastName],
                       [FirstName]
              OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY 

It is just a standard select, but with the addition of the OFFSET and FETCH NEXT statements.

Of course, the numeric values could be replaced by parameters, like in:

           SELECT
                  [BusinessEntityID],
                  [Title],
                  [FirstName],
                  [MiddleName],
                  [LastName]
              FROM
                   [Person].[Person]
              WHERE [PersonType] = N'EM'
              ORDER BY
                       [LastName],
                       [FirstName]
              OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY 

In case you want to get the first set of rows, the offset value could be 0, and it will work perfectly for your need.

Getting information about the entire set of data.

If you want to display to the user, precise information about the total rows in the set of data etc. you just need to perform another query after this one, by using the same filter in WHERE statement, to count the rows.

At the same time, you can get the current page based in the offset and fetch values, like in this query:

SELECT
   COUNT(*) AS [TotalItems],
   @Skip / @Take + 1 AS [CurrentPage],
   @Take AS [PageSize]
     FROM
      [Person].[Person]
     WHERE [PersonType] = N'EM'

Combining both sentences in the same query, you get two result sets to obtain, for example, using a data wrapper like Dapper:

SELECT
       [BusinessEntityID],
       [Title],
       [FirstName],
       [MiddleName],
       [LastName]
   FROM
        [Person].[Person]
   WHERE [PersonType] = N'EM'
   ORDER BY
            [LastName],
            [FirstName]
   OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY;

SELECT
       COUNT(*) AS [TotalItems],
       @Skip / @Take + 1 AS [CurrentPage],
       @Take AS [PageSize]
   FROM
        [Person].[Person]
   WHERE [PersonType] = N'EM';

A JSON for an API.

This sentence could be modified to retrieve a JSON structure containing the entire set, to transfer it by an API to any client, following these rules:

  • The entire set of information must be returned as a well-formed JSON.
  • Each part of the information must be property identified.
  • All the information must be returned as a single string value, which enables to get the information as a scalar value.
  • The first part must be identified as a list/array of data. The second one, just as a unique entry.

For the list of rows, adding FOR JSON PATH, will transform the result in a single JSON content.

For the information about the paging process, it will be modified to avoid create an array of JSON elements, by issuing FOR JSON PATH,WITHOUT_ARRAY_WRAPPER modifier.

Finally, concatenating both sets as different attributes of a single JSON structure, will do the trick.

To have it easy to use, we will make a stored procedure with the entire query, with the corresponding parameters, as follows:

CREATE PROCEDURE [Person].[Person_GetforPager]
 ( @skip INT = 0,
   @take INT = 20
 )
AS
      BEGIN
        SELECT
               '{"List":' +
            (
               SELECT
                      [BusinessEntityID],
                      [Title],
                      [FirstName],
                      [MiddleName],
                      [LastName]
                  FROM
                       [Person].[Person]
                  WHERE [PersonType] = N'EM'
                  ORDER BY
                           [LastName],
                           [FirstName]
                  OFFSET @Skip ROWS FETCH NEXT @Take ROWS ONLY FOR JSON PATH
            )
            + ',"Pager":' +
            (
               SELECT
                      COUNT(*) AS [TotalItems],
                      @Skip / @Take + 1 AS [CurrentPage],
                      @Take AS [PageSize]
                  FROM
                       [Person].[Person]
                  WHERE [PersonType] = N'EM' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
            )
            + '}';  
    END; 

Executing the procedure, this is the result:

{
   {
      "List": [
         {
            "BusinessEntityID": 38,
            "FirstName": "Kim",
            "MiddleName": "B",
            "LastName": "Abercrombie"
         },
         {
            "BusinessEntityID": 211,
            "FirstName": "Hazem",
            "MiddleName": "E",
            "LastName": "Abolrous"
         },
         {
            "BusinessEntityID": 121,
            "FirstName": "Pilar",
            "MiddleName": "G",
            "LastName": "Ackerman"
         },
      ],
      "Pager": {
         "TotalItems": 273,
         "CurrentPage": 1,
         "PageSize": 10
      }
   }

In the next post, we will see how to use it form a Web Assembly application.