SQLUpserter Class

Class to manage building a statement for a SQL upsert (update or insert) using a MERGE statement. Tested only for SQL-Server which requires SQL-Server 2008/2008R2 or later for MERGE support. An upsert operation allows you to perform an INSERT if a matching record (or records) is not found and an UPDATE if a match is found. This is more efficient than performing a SELECT to determine existence followed by an INSERT or UPDATE as appropriate because it uses the SQL MERGE statement. For MERGE statement syntax, see MERGE (Transact-SQL).

Inheritance Hierarchy

SystemObject
NetQuarrySQLUpserter

Namespace: NetQuarry
Assembly: EAP.Core (in EAP.Core.dll) Version: 2.0.0.0 (4.6.8.0)

Syntax

Copy

public class SQLUpserter

Public Class SQLUpserter

The SQLUpserter type exposes the following members.

Constructors

	Name	Description
	SQLUpserter	Constructor.

Top

Properties

	Name	Description
	ColumnCount	The number of columns that have been added to be inserted, updated, and/or matched.
	Hints	Gets/sets the SQL hints applied to the MERGE statement. If no Hints are explicitly specified then ROWLOCK is used. However, the ROWLOCK hint can be suppressed using NoRowLock when generating/executing.
	Schema	Retrieves the schema for the specified Table. Requires that the object was constructed with the SQLUpserter(IDatabase, String) constructor.
	Table	Gets/Sets the table that this class should using when building the MERGE statement. This is normally set in the constructor.

Top

Methods

	Name	Description
	AddColumn(String, Object, OleDbType, UpsertRoles)	Adds a column to the class
	AddColumn(String, Object, OleDbType, UpsertRoles, Int32)	Adds a column to the class, truncating if necessary.
	AddColumn(String, Object, OleDbType, UpsertRoles, Int32, SQLColumnFlags)	Adds a column to the class, truncating if necessary.
	AddColumnPerSchema	Adds a column to the class if the column is found in the table's schema. The Schema is interrogated from the IDatabase object and for the Table provided in the object's SQLInserter(IDatabase, String) constructor which must have been used in order to use this method.
	AddExpression	Adds a SQL expression string to the clause inserting or updating the specified column.
	AddFilter	Add a filter clause. Typically a filter is added to the Match clause (and this is assumed if no role is specified), but filters CAN be added to the other clauses.
	Equals	Determines whether the specified Object is equal to the current Object. (Inherited from Object.)
	Execute(String, SQLHelperFlags)	Execute the SQL MERGE statement. Note that embedded functions are always resolved.
	Execute(String, SQLHelperFlags, Int32)	Execute the SQL MERGE statement. Note that embedded functions are always resolved.
	Finalize	Allows an Object to attempt to free resources and perform other cleanup operations before the Object is reclaimed by garbage collection. (Inherited from Object.)
	GetHashCode	Serves as a hash function for a particular type. (Inherited from Object.)
	GetType	Gets the Type of the current instance. (Inherited from Object.)
	MemberwiseClone	Creates a shallow copy of the current Object. (Inherited from Object.)
	ToString	Returns a String that represents the current Object. (Inherited from Object.)
	ToString(DBMSType, SQLHelperFlags)	Returns the MERGE statement.

Top

Extension Methods

	Name	Description
	EqualValue	Determines if the object value is equal to another object. If the two objects are null, then this returns true. There is special handling for guid comparisons (since a guid could be a string formatted in up to 3 different ways). If the special guid handling is not performed, then the object.Equals method is used. (Defined by EAPUtil.)

Top

Examples

The basic order of events when using a SQLUpserter is as follows:

Instantiate a SQLUpsert object.
Add columns, expressions, and filters using one of the Add methods.
Call Execute(String, SQLHelperFlags) to execute the statement.

When adding columns it is common to add a column with multiple UpsertRoles as well as to add the same column more than once, but with different roles and corresponding values.

Example #1 - Adding columns with multiple roles.

Copy

upserter.AddColumn("owner_id", this.AppContext.UserContext.ID, System.Data.OleDb.OleDbType.VarWChar, UpsertRoles.Insert | UpsertRoles.Match, 100);
upserter.AddColumn("updated_dt", DateTime.UtcNow, System.Data.OleDb.OleDbType.DBTimeStamp, UpsertRoles.Insert | UpsertRoles.Update);

Example #2 - Adding the same column with different roles and values.

Copy

upserter.AddColumn("visits", 1, System.Data.OleDb.OleDbType.Integer, UpsertRoles.Insert);
upserter.AddExpression("visits", "visits + 1", UpsertRoles.Update);

Reference

NetQuarrySQLSelectInto