Hints

This is a feature that would allow you to optimize the command execution towards the database. It is a keyword that is usually being added into the SQL statements (DML) to further optimize the execution of your commands. Please see the Microsoft document here.

Raw-SQL

For raw-SQL, the process of adding hints is controlled by you as you are the one who is providing the SQL text.

Below is the code that query all the dirty data from the [dbo].[Order] table.

using (var connection = new SqlConnection(connectionString))
{
    var orders = connection.ExecuteQuery<Order>("SELECT * FROM [dbo].[Order] WITH (NOLOCK);");
}

And below is the code that locks the table [dbo].[Person] when you insert a record.

var person = new
{
    Name = "John Doe",
    BirthDay = DateTime.Parse("1970-01-01"),
    IsActive = true
};
using (var connection = new SqlConnection(connectionString))
{
    var affectedRows = connection.ExecuteNonQuery("INSERT INTO [dbo].[Person] WITH (TABLOCK) ([Name], [DateOfBirth], [IsActive], [CreatedDateUtc]) VALUES (@Name, @BirthDay, @IsActive, GETUTCDATE());");
}

Fluent-Methods

Most operations within the library accepts the hints argument. It allows you to pass the hints as a literal string, those giving you the full control when optimizing the calls to the operation.

Below are the equivalent operations of the raw-SQLs execution defined above.

using (var connection = new SqlConnection(connectionString))
{
    var orders = connection.QueryAll<Order>(hints: "WITH (NOLOCK)");
}

And below is the code that locks the table when you insert a record.

var person = new
{
    Name = "John Doe",
    BirthDay = DateTime.Parse("1970-01-01"),
    IsActive = true
};
using (var connection = new SqlConnection(connectionString))
{
    var id = connection.Insert<Person>(person, hints: "WITH (TABLOCK)");
}

You can also use the SqlServerTableHints class to pass the hints.

var person = new
{
    Name = "John Doe",
    BirthDay = DateTime.Parse("1970-01-01"),
    IsActive = true
};
using (var connection = new SqlConnection(connectionString))
{
    var id = connection.Insert<Person>(person, hints: SqlServerTableHints.TabLock);
}

The hints argument is only supporting the SQL Server. When being called with other RDBMS data providers, an exception will be thrown.