Tabular array of Contents

  • Introduction
  • Common operation
  • TIPS
  • Building the solution
  • Run across besides
  • Summary
  • Source lawmaking

Introduction

This article presents an efficient method to synchronize database tables using the MERGE command rather than write carve up SQL statements east.k. INSERT, UPDATE and DELETE against a target table by matching the records from a source tabular array.

This code sample shows the very basics for doing majority operations from information presented in a DataGridView that does not have its data source set past reading data from a delimited text file which has columns which lucifer those in a SQL-Server database table. As well demonstrated is how to do a bulk operation on the same data where in a unmarried performance the original information is upset (meaning if in the new data source, a record was modified or deleted it'south reflected in the source/original table) and additions are added to the source tabular array.

Common operation

The job is to read from a text file into a DataGridView for visual inspection prior to working with the information displayed into our SQL-Server table where most developers would use a connection and command objects from SqlClient data provider coupled with parameters for the command object and apply a for each to execute the method ExecuteNonQuery for each row of data which is time-consuming and prone to errors. Instead in this code sample a method from SqlClient data provider (same as for connections and commands) to do bulk operations via SqlBulkCopy.WriteToServer method which is an overloaded method.

In the SqlBulkCopy setup a transaction and allow yous to control how many rows are written via BatchSize holding of SqlBulkCopy. If there are bug/exceptions they can be presented to the caller via a special class which indicates there was an exception and also provides the exception message.

At present let's pause for a second, a seasoned developer 99 per centum of the time will load a DataGridView from a DataTable or a list of a concrete course. Well if you look closely at the code although in this case don't use the data source of the DataGridView when exporting nosotros convert the data in the DataGridView to a list of a concrete class which in plow is pushed to a DataTable. So, if you lot loaded data via a DataTable into a DataGridView you would skip the concrete grade birthday equally all we need is the DataTable.

The second part of the lawmaking sample reads data that was just exported into a DataTable, presented to the user in another DataGridView which is editable. Make changes, add, delete, edit and they are sent to a SQL statement which will be pushed to a temp table then merge the changes back to the original tabular array just imported from the DataGridView at the outset of the project.

Notation, the SqlBulkCopy pushes v,000 plus records to the back end database while the merge operation uses only 8 records (you can change that in DataOperations course by removal of Summit 8 in the SELECT statement). If you lot exit as is, Acme 8 the merge operation will leave viii records because the merge operation will not find a friction match for the other records and remove them every bit one of the rules for the merge operation.

TIPS

Don't simply write the merge operation in code, first write the query either in SQL-Server Management Studio or past creating a text file in your project and giving it an extension of .sql to write the query.

Here the person table was created, added data. So made a copy into a table named Person1. Write the merge query and worked through the syntax until information technology was correct.

Here is my working copy in SQL-Server Management Studio.

Then had 2 SELECT statements, one for Person and one for Person1 in the same query window using a semi-colon to split up them thus when executing them I could run across the results superlative to bottom.

Since we don't want to take a secondary table hanging around the code was modified for the code sample to use a temp table which is created, used and so dropped e.g.


public void UpdateData(DataTable pDataTable)

{

using (SqlConnection cn = new SqlConnection() { ConnectionString = ConnectionString })

{

using (SqlCommand cmd = new SqlCommand( "" , cn))

{

try

{

cn.Open();

//Creating temp table on database which is used them removed after the merge

cmd.CommandText = @"CREATE Tabular array #TmpPersonTable(

[Id] [INT],

[FirstName] [TEXT] Zippo,

[LastName] [TEXT] NULL,

[Gender] [INT] Zero,[Altogether] [DATETIME2](7) Nothing)";

cmd.ExecuteNonQuery();

//Bulk insert into temp table

using (SqlBulkCopy bulkcopy = new SqlBulkCopy(cn))

{

// y'all should tinker with this in your projection

bulkcopy.BulkCopyTimeout = 660;

bulkcopy.DestinationTableName = "#TmpPersonTable" ;

bulkcopy.WriteToServer(pDataTable);

bulkcopy.Close();

}

// Experiment with this in your project

cmd.CommandTimeout = 300;

cmd.CommandText = @"

MERGE INTO dbo.Person AS P

USING dbo.#TmpPersonTable Every bit Due south

ON P.Id = S.Id

WHEN MATCHED THEN

UPDATE Set up P.FirstName = S.FirstName ,

P.LastName = S.LastName ,

P.Gender = S.Gender ,

P.BirthDay = S.BirthDay

WHEN NOT MATCHED THEN

INSERT ( FirstName ,

LastName ,

Gender ,

BirthDay

)

VALUES ( Southward.FirstName ,

S.LastName ,

Southward.Gender ,

S.BirthDay

)

WHEN Not MATCHED BY SOURCE THEN

DELETE;

Driblet TABLE #TmpPersonTable";

cmd.ExecuteNonQuery();

}

catch (Exception ex)

{

Exception.HasError = true ;

Exception.Bulletin = ex.Message;

}

finally

{

cn.Close();

}

}

}

}


Edifice the solution

  • In the database script GenerateDatabase.sql, cheque to ensure the path to your install of SQL-Server matches the path in this script.
  • In BaseSqlServerConnections alter the DatabaseServer to your server proper name or if using SQL-Server Express using .\SQLEXPRESS.
  • Build/run.

Meet also

Summary

In this commodity, the basics have been introduced for an efficient way to import data into SQL server using the MERGE statement from SQL-Server. From hither take time to read the documentation on the MERGE statement to learn the various options available.

Source code

Github repository