Get Started for SQL Server

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

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();

For versions prior to 1.13.0, use the bootstrapper below.

RepoDb.SqlServerBootstrap.Initialize();

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

> Install-Package RepoDb.SqlServer.BulkOperations

For more information, visit the installation page.

Create a 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 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);
}

The Insert method returns the value of identity/primary column, while the InsertAll method returns the number of rows inserted. Both methods are automatically setting back the value of identity/primary property to 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);
}

The Merge method returns the value of the identity/primary column while the MergeAll method returns the number of rows affected. Both methods are automatically setting back the value of identity/primary property to 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);
}

Both the Delete and DeleteAll methods return the number of rows affected during the deletion.

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 }));
}

Both the Update and UpdateAll methods return the number of rows affected during the execution.

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).

Please follow the Microsoft guidelines on how to create a TVP and UDT and call 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] WHERE Id = @Id;";
    var name = connection.ExecuteQuery<string>(sql, new { Id = 10045 });
}

Enumeration types are also supported.

public enum Gender
{
    Male,
    Female
}

// Code
using (var connection = new SqlConnection(ConnectionString))
{
    var sql = "SELECT Gender FROM [dbo].[Person] WHERE Id = @Id;";
    var name = connection.ExecuteQuery<Gender>(sql, new { Id = 10045 });
}

The result of this operation is an IEnumerable object.