The .NET Upsert Nuget package is for combined insert and update operations of strongly typed collections into SQL Server.

Getting Started

To install .NET Upsert, run the following from the Package Manager Console.

Install-Package Upsert -Version 1.0.1

Click here for the .NET Upsert Nuget page.

Important

• Database table column names and class properties should be the same
• Database tables must have primary keys
• The user that executes the Upsert code must have permissions to create objects

Setup database

This example will create and populate a SQL Server database table used in this example.

CREATE SCHEMA Sales;


CREATE TABLE Sales.Customer (
	CustomerID INT NOT NULL PRIMARY KEY,
	Name VARCHAR(20) NOT NULL,
	City VARCHAR(20) NOT NULL,
	State CHAR(2) NOT NULL
);


INSERT INTO Sales.Customer (CustomerID, Name, City, State)
VALUES (1, 'Joe Smith', 'Philadelphia', 'PA'),
	(2, 'Mary Jones', 'New York', 'NY'),
	(3, 'Mike Andersen', 'Raleigh', 'NC');

Customer class in C#

Create a class that maps to the database table.

    public class Customer
    {
        public int CustomerID {get;set;}
        public string Name {get;set;}
        public string City {get;set;}
        public string State {get;set;}
    }

Upsert

The Upsert code will update any records that match on the primary key and attempt to insert any records that do not match on the primary key.

using Spearing.Utilities.Data.Upsert;


    string connStr = @"Data Source=ServerName;Initial Catalog=DatabaseName;User Id=SomeUser; Password=password1;";

    List customers = new List()
    {
        new Customer() { CustomerID = 1, Name = "Joseph Smith", City = "Philadelphia", State = "PA" },
        new Customer() { CustomerID = 4, Name = "Jane West", City = "Denver", State = "CO" }
    };

    customers.Upsert("Sales", "Customer", connStr);

Skip columns

• You can skip specified columns from being updated in the event of a match. Inserts are not affected by these parameters.
• In this example, the first customer will match but will only update the City column even though Name and State were updated also
• The second customer will not match and insert as expected

    string connStr = @"Data Source=ServerName;Initial Catalog=DatabaseName;User Id=SomeUser; Password=password1;";
    
    List customers = new List()
    {
        new Customer() { CustomerID = 1, Name = "J. Smith", City = "Philly", State = "XX" },
        new Customer() { CustomerID = 5, Name = "Terry Jenkins", City = "Los Angeles", State = "CA" }
    };

    customers.Upsert("Sales", "Customer", connStr, "Name", "State");