C#: Working with T-SQL MERGE statement
Introduction
This article presents an efficient method to synchronize database tables using the MERGE command rather than write separate SQL statements e.g. INSERT, UPDATE and DELETE against a target table by matching the records from a source table.
This code sample shows the very basics for doing bulk operations from data presented in a DataGridView that does not have its data source set by reading data from a delimited text file which has columns which match those in a SQL-Server database table. Also demonstrated is how to do a bulk operation on the same data where in a single operation the original data is upset (meaning if in the new data source, a record was modified or deleted it’s reflected in the source/original table) and additions are added to the source table.
12/2022 added a new code sample written with .NET Core 7
Common operation
The task is to read from a text file into a DataGridView for visual inspection prior to working with the data 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 use 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 you to control how many rows are written via BatchSize property of SqlBulkCopy. If there are issues/exceptions they can be presented to the caller via a special class which indicates there was an exception and also provides the exception message.
Now let’s pause for a second, a seasoned developer 99 percent of the time will load a DataGridView from a DataTable or a list of a concrete class. Well if you look closely at the code although in this case don’t use the data source of the DataGridView when exporting we convert the data in the DataGridView to a list of a concrete class which in turn is pushed to a DataTable. So, if you loaded data via a DataTable into a DataGridView you would skip the concrete class altogether as all we need is the DataTable.
The second part of the code 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 table just imported from the DataGridView at the start of the project.
Note, the SqlBulkCopy pushes 5,000 plus records to the back end database while the merge operation uses only eight records (you can change that in DataOperations class by removal of TOP 8 in the SELECT statement). If you leave as is, TOP 8 the merge operation will leave 8 records because the merge operation will not find a match for the other records and remove them as 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 by 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. Then made a copy into a table named Person1. Write the merge query and worked through the syntax until it was correct.
Here is my working copy in SQL-Server Management Studio.
Then had two SELECT statements, one for Person and one for Person1 in the same query window using a semi-colon to separate them thus when executing them I could see the results top to bottom.
Since we don't want to have a secondary table hanging around the code was modified for the code sample to use a temp table which is created, used then 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 TABLE #TmpPersonTable(
[Id] [INT],
[FirstName] [TEXT] NULL,
[LastName] [TEXT] NULL,
[Gender] [INT] NULL,[BirthDay] [DATETIME2](7) NULL)";
cmd.ExecuteNonQuery();
//Bulk insert into temp table
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(cn))
{
// you should tinker with this in your project
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 AS S
ON P.Id = S.Id
WHEN MATCHED THEN
UPDATE SET 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 ( S.FirstName ,
S.LastName ,
S.Gender ,
S.BirthDay
)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
DROP TABLE #TmpPersonTable";
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Exception.HasError = true;
Exception.Message = ex.Message;
}
finally
{
cn.Close();
}
}
}
}
Building the solution
- In the database script GenerateDatabase.sql, check to ensure the path to your install of SQL-Server matches the path in this script.
- In BaseSqlServerConnections change the DatabaseServer to your server name or if using SQL-Server Express using .\SQLEXPRESS.
- Build/run.
See also
SQL Server 2008+: Merge (Unity is Strength - Strengthen your Performance by Merge)
Requires
Summary
In this article, the basics have been introduced for an efficient way to import data into SQL server using the MERGE statement from SQL-Server. From here take time to read the documentation on the MERGE statement to learn the various options available.