Archivo de la etiqueta: T-SQL

Languages and countries in your Apps


In our current globalized world, at this time, any web site must be multilingual, enabling the user to select the language to use.

Moreover, a global company needs to know from which country the user connected.

A lot of sites, more of the streaming services as examples, identifies the country based on the IP address. Which could be a mistake, since a lot of people are in a different country of which belongs to.  

Anyway, your database must need a language list and a countries list.

This post shows you a sample application which, using the standardized languages and countries from .Net Framework, and adding some information from external sources, set up some tables to manage this in your databases.

System.Globalization Namespace

The tool uses information from the System.Globalization namespace, which appears with .Net Framework from it very beginning.

The CultureInfo type exposes each of the languages in use around the world thru the GetCultures member, based in the ISO 639-1: Codes for the Representation of Names of Languages  and in the ISO – ISO 3166 — Country Codes.

Each CultureInfo class instance exposes these properties among others:

Name The identifier of the culture in the culturecode2-country/regioncode2 format
EnglishName The culture name in Enlgish
DisplayName The culture name in the culture of your current configuration
NativeName The culture name in it own culture
IetfcultureTag The culture part of the name
LCID The Windows Language code
ThreeLetterISOcultureName ISO 639-2 code
TextInfo Information about text management
Calendar Calendars used by the culture
DateTimeFormat How the dates and times are managed
NumberFormat How the numbers, currencies etc. Are used
IsNeutralCulture Indicates if it is just a language or a language and country specification

Finally, I get information from two external sources. I got the GPS coordinates of each country from here meanwhile the flag’s pictures are from here. You can found the urls inside the code as well.

Storage’s schema.

Languages/Countries database schema

The tool create 3 tables as you can see in the Diagram.

It is necessary this way, because some countries use more than one language, and the relationship must be preserved.

The tables have a InUse column, to enable/disable each row for your application. So, you can query the Languages table for all the rows with the InUse value in 1, to display only those languages you desire use, or have enabled.

Note: It is important using nvarchar/nchar data types, since several Native names are in UTF-8 chars.

Using the tool.

The tool expects at least the connection string to your database. It accepts a second parameter for the schema name under the tables will be created. If this value is not provided, the tool assumes “Masters” as schema name.

In any case, the DDL scripts manage the creation of the schema if it does not exist.

The source code of the DataGen solution is in my GitHub.

If you prefer just use a T-SQL script to add the tables, here is the script.

In future posts, I will show some faqncy methods for site AND CONTENT localization.

Auditing Data Changes


Sometimes, it is not only important to know when and who perform the last change but trace all the changes to the data in certain important tables.

Big applications, require security behaviors and traceability. And better more important, they must be maintained independently of the applications, since more than one application could modify the same database.

And here is where triggers come to rescue.

Of course, I am not saying you must implement business rules in triggers (or even in Stored Procedures).

Business rules are rules, not data manipulation, even when in some SPECIAL cases, you must implement some in database objects, but this is not the generic case. (We will discuss this in several other posts in the future).

How it works

To store any change, we need a COPY of the data each time it is about changed. So, we need another table with the same definition than the original. Moreover, it is good to store who and when the change was performed.

The stored procedure creates a new table, based in the source table definition, adding the following columns.

ColumnData TypeUsage
DateChangeddatetime2(7)When
UserChangednvarchar(150)Who
Actionnvarchar(15)How

The stored procedure receives an optional parameter for the destination schema. If it is provided, the table denomination will be the destination schema and the same table name than the original. If not, then the history table will be named like the source one, plus the “_Hist” suffix.

Once the table exists, the procedure must create Triggers for Insert, update and delete changes over the source table.

How the triggers work?

A trigger is a T-SQL code which is executed automatically when something happens with one or more rows in a table.
The code could be linked to one or more of the three major events: Insertion, update or deletion.
Meanwhile the trigger is running, a special table, called inserted, contains the new values in change, and another table, called delete, contains the old values. Notice that this tables could contains more than one row, in case the action that start the process, manipulates one or more rows, like in one massive update.
From the trigger point of view, there is no update, but a delete and insert combined.
So, inside the trigger, depending on the action, you can have one or both “virtual” tables, as follows.

Actioninserteddeleted
InsertX
UpdateXX
DeleteX

In this example, the trigger will store in the historical table a new entry with the row (or rows) are inserted. And entries with one or more deleted rows in updates or deletes, to persist the older version of the rows.

CREATE TRIGGER [dbo].[EmployeeTerritories_TInsert] 
	ON [dbo].[EmployeeTerritories]
	AFTER INSERT
AS
  BEGIN
    SET NOCOUNT ON;
	--Insert a new ow in historical table
    INSERT INTO [dbo].[EmployeeTerritories_Hist]
      (
       [EmployeeID]
     , [TerritoryID]
     , [DateChanged]
     , [UserChanged]
     , [Action]
      )
           SELECT 
              [O].[EmployeeID]
            , [O].[TerritoryID]
            , SYSUTCDATETIME()-- The exact moment of the insert
            , USER_NAME()-- The user performing the Insert
            , 'Insert'
           FROM 
              [inserted] [O];
  END;
CREATE TRIGGER [dbo].[EmployeeTerritories_TUD] 
	ON [dbo].[EmployeeTerritories]
	AFTER UPDATE, DELETE
AS
  BEGIN
    DECLARE 
       @Action NVARCHAR(15) = 'Update';
	   /*If there is no rows in inserted table,
	   then it is not an update*/
 
    IF NOT EXISTS
     (
       SELECT 
          *
       FROM 
          [inserted]
     )
      BEGIN
        SET @Action = 'Delete';
      END;
    SET NOCOUNT ON;
    INSERT INTO [dbo].[EmployeeTerritories_Hist]
      (
       [EmployeeID]
     , [TerritoryID]
     , [DateChanged]
     , [UserChanged]
     , [Action]
      )
           SELECT 
              [O].[EmployeeID]
            , [O].[TerritoryID]
            , SYSUTCDATETIME()
            , USER_NAME()
            , @Action
           FROM 
              [deleted] [O];
  END;

So, using the same methodology than in the SP Builder post, with a Table Type variable to collect the columns of the original table, the procedure builds the triggers’ scripts to create them with sp_executesql .

You can found the script fot the SP Create Triggers here.

HTH

Tracking data changes


Sooner or later, it happens. Someone asks “When this data has been modified? Who changed this information?”

A reliable system must ensure this information could be obtained any time.

In this post I offer you a simple way to add columns to any table you want to preserve the metadata about the last changes.

In another post, I’ll talk about tracking all the changes.

Like in the last posts, I prepare it as stored procedure, which receives two parameters:

  • The Table Name
  • The Schema Name (optional) in which case, “dbo” is assumed

The procedure will add the following columns:

Column  NameInformation StoredDefault Value
Created byusername in the insert actionCURRENT_USER
Modified Byusername in the update action 
Creation Datedate & time of the creationSYSUTCDATETIME
Modification Datedate & time of the update 
Note: An “Active” column is added as well, to manage “soft deletes” in case you need it.

Notice there is no default value for update actions, since they are not supported by the database. The values must be provided by application procedures. In fact, they could be provided for the creation process as well, if desired.

Some code highlights

Using a select from INFORMATION_SCHEMA.COLUMNS the procedure detects if the column to add already exists.

DECLARE 
   @Isthere INT;
DECLARE 
   @CheckForColumn NVARCHAR(300) =
   'select @Isthere =count(*) from INFORMATION_SCHEMA.COLUMNS T where TABLE_SCHEMA=@schema and TABLE_NAME=@TableName and T.COLUMN_NAME=@ColumnName'

Like in other examples I already post, using sp_executesql stored procedure, can execute the T-SQL sentence passing parameters.

DECLARE 
   @ColumnName SYSNAME = 'Created by';
EXEC [sp_executesql] 
       @CheckForColumn
 ,
 N'@Schema sysname,@TableName sysname,@ColumnName sysname,@Isthere int OUTPUT'
 , @schema
 , @TableName
 , @ColumnName
 , @Isthere OUTPUT;

If the column doesn’t exist, two other dynamic T-SQL sentences are executed, adding the column and later configuring the default value.

DECLARE 
   @SqlToExecute NVARCHAR(MAX);
IF @Isthere = 0
  BEGIN
    SET @SqlToExecute =
                        'ALTER TABLE ['
                        + @schema
                        + '].['
                        + @TableName
                        + '] ADD ['
                        + @columnName
                        + '] nvarchar(150) NULL';
    EXEC [sp_executesql] 
           @SqlToExecute;
    SET @SqlToExecute =
                        'ALTER TABLE ['
                        + @schema
                        + '].['
                        + @TableName
                        + '] ADD CONSTRAINT [DF_'
                        + @TableName
                        + '_'
                        + @columnName
                        + '] DEFAULT CURRENT_USER FOR ['
                        + @columnName
                        + ']';
    EXEC [sp_executesql] 
           @SqlToExecute;
  END;

The same steps are repeated for the other columns.

In similar way with other previous posts, this stored procedure could be executed over all the tables in a database, using a select from INFORMATION_SCHEMA.TABLES.

You can find the procedure in my repository here.

HTH

T-sql SP Builder


A Developer tool

When things are complex, and it is preferable to not use any ORM like EF, or the database needs to be secured, probably you prefer to use SQL Server Stored procedures.

However, it happens that in those cases, the database uses to have hundreds of tables, and it takes a lot of time to write the standard procedures for CRUD operations for them. This code helps you to build the Insert, Update and Delete procedure for each table. In all cases, the task is performed in just one row, using the Primary key to identify it for update and delete.

How it works.

By getting information from the INFORMATION_SCHEMA view, and using some special system tables like sys.columns and sys.computed_columns, the code retrieves the information for the required table.

The information is stored in a table variable which contains columns to make easier to build the different parts of the sentence, like adding parenthesis, default values, etc. which contains data as you can see in the next image

Table variable columns

Then, several queries against the table variable constructs the parameters, sentence, and where segments of the different procedures.

In this case, I will not explain each part.  Most of it use STRING_AGG to concatenate the different parts.

However, some comments about the different “issues” I found making this:

  • The parameters for the Insert procedures, include an OUTPUT statement, in case a column has identity specification to get the value for the inserted row. To do so, the procedure uses IDENT_CURRENT function. But the parameters for Update and Delete do not need the OUTPUT.
  • The computed columns must not be inserted or updated, so must be excluded of the procedure. This happens in case a column is of timestamp datatype as well.
  • Just to be sure what the script generates, the results are printed so you can review them.

You can download this script here.

The script as Stored Procedure.

If you want to use this several times during your development, you can use this second script, which add this as a Stored Procedure, so you can call it just passing the table name, and, when needed, the schema name.

Moreover, it accepts a third parameter, to perform the procedures’ creation directly.

Finally, if you want to use the procedure for all the tables of a database at once, just use this sentence for creating a complete script.

SELECT     
   'EXECUTE @RC = [Development].[SPBuilder]    @TableName'
   + QUOTENAME([table_schema])
   + ',@SchemaName ='
   + QUOTENAME([table_name])
   + ' ,@Create=1'
FROM     
   [INFORMATION_SCHEMA].[TABLES]
WHERE
       [table_type]
       = 'BASE TABLE'
       AND [table_name] NOT LIKE 'sys%';

Custom Identity splitted by other column


Last week, I saw an entry in a social network in Spanish, asking for this specific case:

I need to use a sequential numbering for an Orders table, but it must start in one for each customer. How can I accomplish this?

Of course, it can be done by selecting the max value of the column, filtered by customer, and add 1 to that value.

However, it is a good example to implement a SEQUENCE in T-SQL, as follows:

Consider these two tables:

You must create a new sequence for each new customer added to the database, as follows:

EGIN
    BEGIN
      INSERT INTO [OnLineSales].[WebCustomers]
       (
          [LastName]
        , [FirstName]
        , [Address]
        , [City]
        , [Region]
        , [PostalCode]
        , [CountryId]
        , [Phone]
        , [Fax]
        , [email]
        , [LanguageId]
       )
      VALUES
       (
          @LastName
        , @FirstName
        , @Address
        , @City
        , @Region
        , @PostalCode
        , @CountryId
        , @Phone
        , @Fax
        , @email
        , @LanguageId
       );
    END;
    DECLARE 
           @SeqCreator  NVARCHAR(MAX) = replace(
           'CREATE SEQUENCE [OnLineSales].[WebCustomers_@@] START WITH 1 INCREMENT BY 1',
           '@@',
           IDENT_CURRENT('[OnLineSales].[WebCustomers]'));
    EXEC [sp_executesql] 
         @stmt = @SeqCreator;
  END;

Then, when a new order is inserted, you can get the next value from the sequence, using this code:

BEGIN
  DECLARE 
         @GetSeq  NVARCHAR(MAX) = 
         replace('SELECT NEXT VALUE FOR OnLineSales.WebCustomers_@@ as OrderNo', 
         '@@', @IdCustomer);
  DECLARE 
         @OrderNo  INT;
  EXEC [sp_executeSQL] 
         @GetSeq
     , N'@OrderNo INT OUTPUT'
     , @OrderNo OUTPUT;
  INSERT INTO [OnLineSales].[Orders]
     (
      [IdCustomer]
    , [OrderNo]
    , [Date]
    , [Total]
   )
  VALUES
     (
      @IdCustomer
    , @OrderNo
    , @Date
    , @Total
   );
END;

This is a simple way to keep separated numbering by other column value.

This is a simple way to keep separated numbering by other column value.

Note
As you can see, both statements samples use variables. In fact, they are parameters, since are implemented as stored procedures.
This is important, since a normal user, not and admin, dbo, or server admin, must not have DDL permissions, and the CREATE SEQUENCE statement is a DDL one.
However, been the Stored Procedure created by an admin, when it is used by another user with EXECUTE permissions, the DDL statement works without raise any error.
It is like the normal user “is impersonated” by the stored procedure’s creator, and the SP runs under the security permissions environment of the SP creator.

Below I include both procedures

/****** Object:  StoredProcedure [dbo].[WebCustomers_Add]    Script Date: 7/28/2021 12:10:15 PM ******/
 
SET ANSI_NULLS ON;
GO
 
SET QUOTED_IDENTIFIER ON;
GO
 
CREATE PROCEDURE [dbo].[WebCustomers_Add]
 (
   @LastName    NVARCHAR(40)
 , @FirstName   NVARCHAR(30)
 , @Address     NVARCHAR(60)  = NULL
 , @City        NVARCHAR(15)  = NULL
 , @Region      NVARCHAR(15)  = NULL
 , @PostalCode  NVARCHAR(10)  = NULL
 , @CountryId   INT           = 1
 , @Phone       NVARCHAR(24)  = NULL
 , @Fax         NVARCHAR(24)  = NULL
 , @email       NVARCHAR(100) = NULL
 , @LanguageId  INT           = 1
)
AS
  BEGIN
    BEGIN
      INSERT INTO [OnLineSales].[WebCustomers]
       (
          [LastName]
        , [FirstName]
        , [Address]
        , [City]
        , [Region]
        , [PostalCode]
        , [CountryId]
        , [Phone]
        , [Fax]
        , [email]
        , [LanguageId]
       )
      VALUES
       (
          @LastName
        , @FirstName
        , @Address
        , @City
        , @Region
        , @PostalCode
        , @CountryId
        , @Phone
        , @Fax
        , @email
        , @LanguageId
       );
    END;
    DECLARE 
           @SeqCreator  NVARCHAR(MAX) =
           replace('CREATE SEQUENCE [OnLineSales].[WebCustomers_@@] START WITH 1 INCREMENT BY 1',
           '@@',
           IDENT_CURRENT('[OnLineSales].[WebCustomers]'));
    EXEC [sp_executesql] 
         @stmt = @SeqCreator;
  END;
GO
 
/****** Object:  StoredProcedure [OnLineSales].[Orders_Add]    Script Date: 7/28/2021 12:10:15 PM ******/
 
SET ANSI_NULLS ON;
GO
 
SET QUOTED_IDENTIFIER ON;
GO
 
CREATE PROCEDURE [OnLineSales].[Orders_Add]
 (
   @IdCustomer  INT
 , @Date        SMALLDATETIME
 , @Total       SMALLMONEY
)
AS
  BEGIN
    DECLARE 
           @GetSeq  NVARCHAR(MAX) =
           replace('SELECT NEXT VALUE FOR OnLineSales.WebCustomers_@@ as OrderNo',
           '@@', 
            @IdCustomer);
    DECLARE 
           @OrderNo  INT;
    EXEC [sp_executeSQL] 
         @GetSeq
       , N'@OrderNo INT OUTPUT'
       , @OrderNo OUTPUT;
    INSERT INTO [OnLineSales].[Orders]
     (
        [IdCustomer]
      , [OrderNo]
      , [Date]
      , [Total]
     )
    VALUES
     (
        @IdCustomer
      , @OrderNo
      , @Date
      , @Total
     );
  END;
GO

Hope this help.

using the API REST (JSON)


In this example, we will see how we can test the different versions from a web application.

It is a simple page that calls consecutively the different versions, repeatedly (100), and returns the number of milliseconds involved in the whole process.

To increase the impedance of the evaluation, the option is given in a check box, to perform the same query, 100 times, but for all countries.

In any case, the responses will be in approximately the same proportion.

Performance

As you can see, from the point of view of the client application, the final result is faster with EF than with simple code, exactly the opposite of the performance in the first test.

It should be noted, however, that in that first test, the process requested was not the same at all, since data from different tables were required, while, in this case, the data came from only one.

In any case, the comparison could be established with the second publication of this series, which obtains the same data as the latter.

In any case, this last test has some advantage on the part of EF, compared to the rest.

In other words, let us point out conclusions:

  • The result is not always as expected. You must try. ALWAYS.
  • There is no single way to do things. We must investigate to improve the quality of the applications we generate.
  • An unit test of the final set of the application can lead to false conclusions, since other factors, such as communication, data transformation, etc., also influence performance. That is, unit tests are highly valid for testing functionality, but they are not always valid for evaluating performance.
  • In fact, most likely, in a real-world application environment, performance results can also change.

Therefore, it is important to monitor the application deployed in production, include counters, logs etc. to have permanent information and be able to periodically evaluate it, to anticipate possible problems.

We’ll see more of this in other posts.

Puedes encontrar la solución completa en este repositorio

Usando la API REST (JSON)


En este ejemplo, veremos como podemos probar las distintas versiones desde un aplicativo web.

Se trata de una página sencilla que, llama consecutivamente a las distintas versiones, repetidas veces (100), y nos retorna la cantidad de milisegundos implicados en todo el proceso.

Para incrementar la impedancia de la evaluación, se da la opción en una casilla, de realizar la misma consulta, 100 veces, pero para todos los países.

En cualquier caso, las respuestas serán aproximadamente en la misma proporción.

Rendimiento

Como se puede ver, desde el punto de vista de la aplicación cliente, el resultado final es más rápido con EF que con código plano, exactamente lo contrario a la respuesta en la primera prueba.

Es de notar, sin embargo, que en aquella primera prueba, el proceso solicitado no era el mismo en lo absoluto, dado que se requerían datos de distintas tablas, mientras que, en este caso, los datos provienen de una sola.

En todo caso, la comparativa la podríamos establecer con la segunda publicación de esta serie, que obtiene los mismos datos que ésta última.

En cualquier caso, esta última prueba presenta alguna ventaja de parte de EF, respecto del resto.

O sea, puntualicemos conclusiones:

  • No siempre el resultado es el esperado. Hay que probar. SIEMPRE.
  • No hay una sola forma de hacer las cosas. Hay que investigar para mejorar la calidad de las aplicaciones que generamos.
  • Una prueba aislada del conjunto final de la aplicación, puede llevarnos a falsas conclusiones, ya que otros factores, como comunicación, transformación de datos, etc., también influyen en el rendimiento.
    O sea, las pruebas unitarias son muy validad para comprobar funcionalidad, pero no siempre son válidas para evaluar rendimiento.

De hecho, muy probablemente, en un entorno de una aplicación real, también los resultados de rendimiento pueden cambiar.

Por ello, es importante hacer seguimiento de la aplicación desplegada en producción, incluir contadores, bitácoras etc. para tener información permanente y poder evaluar periódicamente la misma, para adelantarnos a posibles problemas.

Ya veremos más de esto.

Puedes encontrar la solución completa en este repositorio

GENERATING A REST API (JSON) [3]


Finally, I’ll add projects to use stored procedures instead of statements constructed in code.

Moreover, and based on the recommendation of a greater entity framework expert than me, I added “AsNoTracking()” to the Entity Framework LINQ query set to  Generating a REST API (JSON)[2].

The Stored Procedure.

This is the stored procedure which receives the country ID, the date from and date to, and the page to display.

Is the stored procedure which is responsible for setting values that are appropriate to the date parameters, rather than setting them from the component in C#.

CREATE PROCEDURE [dbo].[Owid Covid Data Get By Country] 
   @fromDate   SMALLDATETIME NULL
 , @toDate     SMALLDATETIME NULL
 , @CountryId  INT
 , @Page       INT           = 1
AS
  BEGIN
    SELECT 
        @froMDate = ISNULL(@fromDate,
    (
        SELECT 
            MIN([o].[date])
        FROM 
           [Owid Covid Data] AS [o]
    ));
    SELECT 
        @toDate = ISNULL(@toDate,
    (
        SELECT 
            MAX([o].[date])
        FROM 
           [Owid Covid Data] AS [o]
    ));
    DECLARE 
           @Skip  INT = ((@Page - 1) * 100);
    BEGIN
      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;
    END;
  END;

Exactly the same, but with “FOR JSON PATH” at the end, is used in the project that uses pure code.

El Cambio en Entity Framework

Based on the proposal and comment, the code is as follows:

public 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).AsNoTracking().ToList();
}

Dapper Using Stored Procedure

We use Dapper’s stored procedure execution capability, which is capable of assign values to parameters by name matching.

public async Task<string> GetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
 
   var result =await _dal.GetDataAsync("[Owid Covid Data Get By Country]",new { fromDate, toDate, CountryId, Page });
   string json = JsonSerializer.Serialize(result, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Code using Stored Procedure

In the case of direct code, we assign the parameters one by one, also specifying the data type, which allows greater specificity.

public async Task<string> GetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
   var command = _dal.CreateCommand("[Owid Covid Data Get By Country JSON]");
   command.Parameters.Add("@fromDate", System.Data.SqlDbType.SmallDateTime).Value = fromDate;
   command.Parameters.Add("@toDate", System.Data.SqlDbType.SmallDateTime).Value = toDate;
   command.Parameters.Add("@CountryId", System.Data.SqlDbType.Int).Value = CountryId;
   command.Parameters.Add("@skip", System.Data.SqlDbType.Int).Value = Page;
   var json =await _dal.GetJSONDataAsync(command);
   return json;
}

Performance

The graph shows that even when you use features that improve effectiveness, the simplicity of the code improves performance.

That is, for better response to the user, more time should be invested by developers in improving their development.

As a detail, stored procedure calls directly make calls to the SP, instead of using, as we saw in the previous post, sp_executesql.

EXEC [Owid Covid Data Get By Country] 
     @fromDate = NULL
   , @toDate = NULL
   , @CountryId = 4
   , @Page = 3;

GENERANDO UNA API REST (JSON) [3]


Aquí, finalmente, agregaré proyectos para utilizar procedimientos almacenados en lugar de sentencias construidas en el código.

De paso, y por recomendación de un mayor experto que yo en Entity Framework, agregué “AsNoTracking()” a la consulta LINQ de Entity Framework establecida en Generando una API REST (JSON) [2].

El procedimiento Almacenado.

Este es el procedimiento almacenado que recibe, el Id de país, la fecha desde y la fecha hasta, y la página a mostrar.

Es el procedimiento almacenado el responsable de establecer valores adecuados a los parámetros de fecha, en lugar de establecerlos desde el componente en C#.

CREATE PROCEDURE [dbo].[Owid Covid Data Get By Country] 
   @fromDate   SMALLDATETIME NULL
 , @toDate     SMALLDATETIME NULL
 , @CountryId  INT
 , @Page       INT           = 1
AS
  BEGIN
    SELECT 
        @froMDate = ISNULL(@fromDate,
    (
        SELECT 
            MIN([o].[date])
        FROM 
           [Owid Covid Data] AS [o]
    ));
    SELECT 
        @toDate = ISNULL(@toDate,
    (
        SELECT 
            MAX([o].[date])
        FROM 
           [Owid Covid Data] AS [o]
    ));
    DECLARE 
           @Skip  INT = ((@Page - 1) * 100);
    BEGIN
      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;
    END;
  END;

Exactamente igual, pero con “FOR JSON PATH” al final, se usa en el proyecto que utiliza código puro.

El Cambio en Entity Framework

Basado en la propuesta y comentario, el código queda como sigue:

public 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).AsNoTracking().ToList();
}

Dapper Usando Procedimientos Almacenados

Utilizamos la capacidad de ejecución de procedimientos almacenados de Dapper, que es capaz de asignar valores a los parámetros por coincidencia de nombres.

public async Task<string> GetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
 
   var result =await _dal.GetDataAsync("[Owid Covid Data Get By Country]",new { fromDate, toDate, CountryId, Page });
   string json = JsonSerializer.Serialize(result, new JsonSerializerOptions()
   {
      WriteIndented = true,
      ReferenceHandler = ReferenceHandler.Preserve
   });
   return json;
}

Código usando Procedimientos Almacenados

En el caso del código directo, asignamos los parámetros uno a uno, especificando además el tipo de dato, que permite una mayor especificidad.

public async Task<string> GetCountryData(
   int CountryId, 
   DateTime? fromDate = null, 
   DateTime? toDate = null, 
   int Page = 1)
{
   var command = _dal.CreateCommand("[Owid Covid Data Get By Country JSON]");
   command.Parameters.Add("@fromDate", System.Data.SqlDbType.SmallDateTime).Value = fromDate;
   command.Parameters.Add("@toDate", System.Data.SqlDbType.SmallDateTime).Value = toDate;
   command.Parameters.Add("@CountryId", System.Data.SqlDbType.Int).Value = CountryId;
   command.Parameters.Add("@skip", System.Data.SqlDbType.Int).Value = Page;
   var json =await _dal.GetJSONDataAsync(command);
   return json;
}

Rendimiento

El gráfico muestra que, aún cuando se utilizan características que mejoran la efectividad, la simpleza del código mejora el rendimiento.

O sea, para mejor respuesta al usuario, se deberá invertir más tiempo de los desarrolladores en mejorar su desarrollo.

Como detalle, las llamadas de procedimiento almacenado, realizan directamente llamadas al mismo, en lugar de utilizar, como vimos en la publicación anterior, sp_executesql.

EXEC [Owid Covid Data Get By Country] 
     @fromDate = NULL
   , @toDate = NULL
   , @CountryId = 4
   , @Page = 3;

GENERATING A REST API (JSON) [2]


Let’s consider another requirement, to evaluate how you can best take advantage of the features of the Entity Framework and emulate that functionality in cases where it cannot be used, or it is more convenient to do something else.

In this example, we are using the same database explained in Data-for-demos

The requirement

You need to get the statistical information of cases, vaccinations, etc. By country, between certain dates, with the following conditions:

  • If no start date is entered, the first available date is used.
  • If the end date is not entered, the last available date is used.
  • The information must be returned in batches of 100 entries, so the requested page number must be received.

In this case, it will be implemented in the “Country” controller

Entity Framework.

The code leverages EF’s Fluent capabilities to nest conditions. Similarly, below, the Entity Framework generates a statement according to the data engine in use, in this case, 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

Using the returned DapperRows, we implement the call with an SQL statement that is almost the same as the one automatically generated by 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;
}

Code

As in the previous example, we create a parameterized Command object that returns a string of characters with the resulting JSON, implemented in the SQL statement.

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

SQL sentences

For ease of comparison, here are, together, the three SQL statements used.

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;

Code

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;

It’s striking that in the case of code, it uses sp_executesql,just like EF, which takes a bit longer to execute.

It seems that this will need to be improved in the next publication.

Performance

The same procedure was used as in the previous publication to evaluate the results.

As you can see, things have improved quite a bit for EF, although in this case, Dapper seems to be the one who works best.

The difference is precisely the sp_executesql.