Link Search Menu Expand Document
RepoDB

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

The library can be installed via Nuget. In your Package Manager Console, type the command below.

> Install-Package RepoDb.SqlServer

Once installed, call the globalized setup method to initialize all the dependencies for SQL Server.

GlobalConfiguration
	.Setup()
	.UseSqlServer();

For the users prior the version 1.13.0, use the bootstrapper code below.

RepoDb.SqlServerBootstrap.Initialize();

If you are to work with bulk operations (BulkDelete, BulkInsert, BulkMerge and BulkUpdate), then install the RepoDb.SqlServer.BulkOperations package.

> Install-Package RepoDb.SqlServer.BulkOperations

Or, visit our installation page for more information.

Create a Table

Let us say you have this table on your 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

And you have this model on your 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 create 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);
    /* Do the stuffs for person here */
}

To query all the rows, use the QueryAll method.

using (var connection = new SqlConnection(ConnectionString))
{
    var people = connection.QueryAll<Person>();
    /* Do the stuffs for the people 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, but you can customize the qualifiers with other columns.

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

By default, it uses the primary column as a qualifier, but you can also use the other columns like below.

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

To delete all the rows, use the DeleteAll method.

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

You can also pass the list of primary keys or models to be deleted.

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

You can also dynamically update by targeting certain columns.

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

To update all the 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, but you can also specify your custom qualifiers.

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 query, 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 while expecting a result of class object, 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);
    /* Do the stuffs for the people here */
}

To execute a query while expecting a single result, 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 while expecting a result of DbDataReader object, use the ExecuteReader method.

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

Table-Valued Parameters

To execute a Table-Valued Parameter (TVP), create a DataTable and set its name equals 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

Then, pass it as a value to your argument.

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, you can use any of the execute methods mentioned above, but you have to pass the CommandType.StoredProcedure enumeration value on 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.

Or, you can direct call using the EXEC command. With this, you do not need to pass the value of 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

You can infer the single-column resultsets to any .NET CLR type via ExecuteQuery operation.

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

And even with the enumerations.

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](https://learn.microsoft.com/en-us/dotnet/api/system.collections.generic.ienumerable-1?view=net-7.0) object.