In one of my new assignments, I had to import a large volume of data into a SQLServer database.

Each imported data must either be updated if it already exists or created if it does not exist.

The first solution I came up with was to make three imported data requests:

• the first by sending the dIdId list that they want to import (the server responding to the existing ones)

• the second for inserts

• the third for updates

This method has the disadvantage of dividing imports into 3 different SQL requests Which means we have the possibility that the SQL server fails in 3 places instead of 1 and we also have 3 back and forth with that server...

The ideal is to send the full list of imports to the base in a single SQL query data and it inserts or updates them directly.

Since SQLServer can handle complex types, one can therefore create a stored procedure that allows processing to be done in one go.

Implementation:

This requires three steps.

You need to create a TYPE SQLServer:

CREATE TYPE TalbetType AS TABLE 
( 
  Id uniqueidentifier NOT NULL,
  Name[nvarchar](MAX)NOT NULL,  
  ExternalId varchar(MAX) NOT NULL, 
  Type int NULL
);

The storage procedure must be established:

CREATE PROCEDURE [dbo].[SaveTablets]
(
    @Tablets [dbo].[TalbetType] READONLY
)
AS
BEGIN
    MERGE [dbo].[Tablets ] as target
    USING @Tablets AS source
    ON target.[ExternalId ] = source.[ExternalId]
    WHEN MATCHED THEN
    UPDATE SET target.[Id] = source.[Id],
    target.[ExternalId] = source.[ExternalId],
    target.[Name] = source.[Name],
    target.[Type] = source.[Type]
    WHEN NOT MATCHED THEN
    INSERT ([Id],[Name],[ExternalId],[Type])
    VALUES (source.[Id],source.[Name], source.[ExternalId], source.[Type]);
END

And then to call the stored procedure , you have to do the following:

            //Creation du datatable correspondant au type TabletType
            var dataTable = new DataTable("dt");
            dataTable.Columns.Add("Id", typeof(Guid));
            dataTable.Columns.Add("Name", typeof(string));
            dataTable.Columns.Add("ExternalId", typeof(string));
            dataTable.Columns.Add(new DataColumn("Type", typeof(int)) { AllowDBNull = true });        
            foreach (var tablet in tablets)
            {
                DataRow dataRow = dataTable.NewRow();
                dataRow["Id"] = tablet.Id;
                dataRow["Name"] = tablet.Name;
                dataRow["Email "] = tablet.Email;
                dataRow["ExternalId"] = tablet .ExternalId;
                if (tablet.Type.HasValue)
                    dataRow["Type"] = attendee.Type.Value;
                else
                    dataRow["Type"] = DBNull.Value;
                dataTable.Rows.Add(dataRow);
                dataRow.AcceptChanges();
            }
            //Création du parametre SQL
            var tableParameter = new SqlParameter("tablets", dataTable);
            tableParameter.TypeName = "TalbetType";
            tableParameter.SqlDbType = SqlDbType.Structured;
            //On execute la requête
            return await _context.Database.ExecuteSqlCommandAsync("SaveTablets @tablets", tableParameter);

Benefits:

The advantages of this method are:

• The data you send to SQLServer is very typed so you don't have to go through the SQL procedure to retrieve the information.

• All l’import is carried out in a single SQL query (so a single go/get).