Quickstart · 7 min read

SQL Server

Learn on how to work with SQL Server databases using RepoDB library.

RepoDB is a hybrid .NET ORM library for SQL Server RDBMS. The project is hosted at Github and is licensed with Apache 2.0.

Support ships as two packages:

RepoDb.SqlServer — the core provider, built on ADO.NET’s SqlClient.
RepoDb.SqlServer.BulkOperations — adds BulkInsert, BulkMerge, BulkUpdate and BulkDelete.

Installation

Install the library via NuGet using the Package Manager Console.

> Install-Package RepoDb.SqlServer

After installation, call the globalized setup method to initialize all dependencies for SQL Server.

GlobalConfiguration
    .Setup()
    .UseSqlServer();

To use bulk operations (BulkDelete, BulkInsert, BulkMerge and BulkUpdate), install the RepoDb.SqlServer.BulkOperations package.

> Install-Package RepoDb.SqlServer.BulkOperations

Create a DB Table

The examples below assume the following table exists in the database.

CREATE TABLE [dbo].[Person]
(
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](128) NOT NULL,
    [Age] [int] NOT NULL,
    [CreatedDateUtc] [datetime2](5) NOT NULL,
    CONSTRAINT [CRIX_Person_Id] PRIMARY KEY CLUSTERED ([Id] ASC) ON [PRIMARY]
)
ON [PRIMARY];
GO

Create a .NET Model

The examples below assume the following model exists in the application.

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public DateTime CreatedDateUtc { get; set; }
}

Creating a Record

To insert a row, use the Insert method.

var person = new Person
{
    Name = "John Doe",
    Age = 54,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new SqlConnection(ConnectionString))
{
    var id = connection.Insert(person);
}

To insert multiple rows, use the InsertAll operation.

var people = GetPeople(100);
using (var connection = new SqlConnection(ConnectionString))
{
    var rowsInserted = connection.InsertAll(people);
}

Insert returns the identity/primary column value, InsertAll returns the number of rows inserted, and both set the identity/primary property back onto the entity model (if present).

Querying a Record

To query a row, use the Query method.

using (var connection = new SqlConnection(ConnectionString))
{
    var person = connection.Query<Person>(e => e.Id == 10045);
    /* Process the result here */
}

To query all rows, use the QueryAll method.

using (var connection = new SqlConnection(ConnectionString))
{
    var people = connection.QueryAll<Person>();
    /* Process the results here */
}

Merging a Record

To merge a row, use the Merge method.

var person = new Person
{
    Id = 1,
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new SqlConnection(ConnectionString))
{
    var id = connection.Merge(person);
}

By default, the primary column is used as a qualifier. Custom qualifiers can also be specified.

var person = new Person
{
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new SqlConnection(ConnectionString))
{
    var id = connection.Merge(person, qualifiers: (p => new { p.Name }));
}

To merge multiple rows, use the MergeAll method.

var people = GetPeople(100).AsList();
people
    .ForEach(p => p.Name = $"{p.Name} (Merged)");
using (var connection = new SqlConnection(ConnectionString))
{
    var affectedRecords = connection.MergeAll<Person>(people);
}

Merge returns the identity/primary column value, MergeAll returns the number of rows affected, and both set the identity/primary property back onto the entity model (if present).

Deleting a Record

To delete a row, use the Delete method.

using (var connection = new SqlConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(10045);
}

Other columns can also be used as qualifiers.

using (var connection = new SqlConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(p => p.Name == "John Doe");
}

To delete all rows, use the DeleteAll method.

using (var connection = new SqlConnection(ConnectionString))
{
    var deletedRows = connection.DeleteAll<Person>();
}

A list of primary keys or models can also be passed for targeted deletion.

using (var connection = new SqlConnection(ConnectionString))
{
    var primaryKeys = new [] { 10045, 11001, ..., 12011 };
    var deletedRows = connection.DeleteAll<Person>(primaryKeys);
}

Delete and DeleteAll both return the number of rows affected.

Updating a Record

To update a row, use the Update method.

var person = new Person
{
    Id = 1,
    Name = "James Doe",
    Age = 55,
    DateInsertedUtc = DateTime.UtcNow
};
using (var connection = new SqlConnection(ConnectionString))
{
    var updatedRows = connection.Update<Person>(person);
}

Specific columns can also be targeted using a dynamic update.

using (var connection = new SqlConnection(ConnectionString))
{
    var updatedRows = connection.Update("Person", new { Id = 1, Name = "James Doe" });
}

To update multiple rows, use the UpdateAll method.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new SqlConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people);
}

By default, the primary column is used as a qualifier. Custom qualifiers can also be specified.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new SqlConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people,
        qualifiers: (p => new { p.Name, p.DateOfBirth }));
}

Update and UpdateAll both return the number of rows affected.

Bulk Operations

For 1,000 rows or more, prefer the true bulk operations over their All-suffixed counterparts shown above — BulkInsert, BulkMerge, BulkUpdate, BulkDelete and BulkDeleteByKey. These leverage ADO.NET’s SqlBulkCopy under the hood and require the RepoDb.SqlServer.BulkOperations package installed alongside the core provider.

To bulk-insert rows, use the BulkInsert method.

using (var connection = new SqlConnection(ConnectionString))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert(people);
}

To bulk-merge rows, use the BulkMerge method.

var people = GetPeople(10000).AsList();
people.ForEach(p => p.Name = $"{p.Name} (Merged)");
using (var connection = new SqlConnection(ConnectionString))
{
    var mergedRows = connection.BulkMerge(people);
}

To bulk-update rows, use the BulkUpdate method.

var people = GetPeople(10000).AsList();
people.ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new SqlConnection(ConnectionString))
{
    var updatedRows = connection.BulkUpdate(people);
}

To bulk-delete rows via their data entities, use the BulkDelete method.

using (var connection = new SqlConnection(ConnectionString))
{
    var inactivePeople = connection.Query<Person>(e => e.Age > 90).AsList();
    var deletedRows = connection.BulkDelete(inactivePeople);
}

To bulk-delete rows via a list of primary keys, use the BulkDeleteByKey method.

using (var connection = new SqlConnection(ConnectionString))
{
    var primaryKeys = connection.Query<Person>(e => e.IsActive == false).Select(e => e.Id);
    var deletedRows = connection.BulkDeleteByKey<Person>(primaryKeys);
}

BulkInsert and BulkMerge can also set the generated identity value back onto each entity via the identityBehavior argument (defaults to not returning it).

Executing a SQL Text

To execute a non-query statement, use the ExecuteNonQuery method.

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "DELETE FROM [dbo].[Person] WHERE Id = @Id;";
    var affectedRecords = connection.ExecuteNonQuery(sql, new { Id = 1 });
}

To execute a query and return mapped objects, use the ExecuteQuery method.

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT * FROM [dbo].[Person] ORDER BY Id ASC;";
    var people = connection.ExecuteQuery<Person>(sql);
    /* Process the results here */
}

To execute a query and return a scalar value, use the ExecuteScalar method.

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT MAX(Id) FROM [dbo].[Person];";
    var maxId = connection.ExecuteScalar<long>(sql);
}

To execute a query and return a DbDataReader, use the ExecuteReader method.

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT * FROM [dbo].[Person];";
    using (var reader = connection.ExecuteReader(sql))
    {
        /* Process the data reader here */
    }
}

Table-Valued Parameters

To pass a Table-Valued Parameter (TVP), create a DataTable and set its name to the name of the User-Defined Type (UDT).

Follow Microsoft’s guidelines for creating a TVP and UDT and calling it from C#/ADO.NET.

var table = new DataTable();
table.TableName = "[dbo].[PersonType]"; // Name of the UDT
// Create the 'table' columns/rows

Pass it as a parameter value.

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "EXEC [sp_InsertPerson] @PersonTable = @Table;";
    var tables = connection.ExecuteQuery<Person>(sql, new { Table = table })?.AsList();
}

Executing a Stored Procedure

To execute a stored procedure, use any of the execute methods above and pass CommandType.StoredProcedure to the commandType argument.

using (var connection = new SqlConnection(ConnectionString))
{
    var people = connection.ExecuteQuery<Person>("[dbo].[sp_GetPeople]",
        commandType: CommandType.StoredProcedure);
}

Beware of not putting a semi-colon at the end of the calls.

Alternatively, use the EXEC command directly, which does not require the commandType argument.

using (var connection = new SqlConnection(ConnectionString))
{
    var people = connection.ExecuteQuery<Person>("EXEC [dbo].[sp_GetPeople];");
}

You can also use the types defined at the Passing of Parameters section when passing a parameter.

Typed Result Execution

Single-column result sets can be mapped to any .NET CLR type via ExecuteQuery.

using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT Name FROM [dbo].[Person];";
    var names = connection.ExecuteQuery<string>(sql);
}

Enumeration types are also supported.

public enum Gender
{
    Male,
    Female
}

// Code
using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT Gender FROM [dbo].[Person];";
    var genders = connection.ExecuteQuery<Gender>(sql);
}

Returns an IEnumerable object.