4.1. Working with parameters

Working with parameters

All available extensions that can execute SQL commands (Execute<>, Read<> and Multiple<>) - have four overloads that can receive query parameters in multiple different ways:

Positional parameters

Parameters Signature

(string command, params object[] parameters);

Mapping by position will add parameters by the position they appear in the query.

Example

connection.Execute("update table set id = @id and value = @value", 1, "test");

Value 1 is assigned to parameter p because they appear in the first position from left to right, and so on.

Using Native Parameters

If the parameter object value is derived from System.Data.Common.DbParameter - it will be used as a native parameter.

Example

connection.Execute("update table set id = @id and value = @value", new SqlParameter("id", 1), new SqlParameter("value", "test"));

You can even mix native and positional parameters. For example:

connection.Execute("update table set id = @id and value = @value", new SqlParameter("id", 1), "test");

Named Parameters

Parameters Signature

(string command, params (string name, object value)[] parameters);

Mapping by position receives name and value tuple as parameters that are matched by their names respectively.

Example

connection.Execute("update table set id = @id and value = @value", ("id", 1), ("value", "test"));

In this example, parameters values are matched by the name provided.

Parameter position doesn't make a diffrence, this is the same as:

connection.Execute("update table set id = @id and value = @value", ("value", "test"), ("id", 1));

Named parameters of specific database type

Parameters Signature

(string command, params (string name, object value, DbType type)[] parameters);

This allows passing named parameters with specific database type:

connection.Execute("update table set id = @id and value = @value", ("id", 1, DbType.Int32), ("value", "test", DbType.String));

Named parameters of custom database type

Some database providers have their own custom database types. You can use those types also.

Parameters Signature

(string command, params (string name, object value, object type)[] parameters);

Example

This example uses types from the PostgreSQL database provider:

connection.Execute("update table set id = @id and value = @value", ("id", 1, NpgsqlDbType.Integer), ("value", "test", NpgsqlDbType.Varchar));

You can also mix common database types with custom database types:

connection.Execute("update table set id = @id and value = @value", ("id", 1, DbType.Int32), ("value", "test", NpgsqlDbType.Varchar));

See also

• 2.1. Execute(command, params) connection extension
• 2.2. Read(command, params) connection extensions
• 2.3. Multiple(command, params) connection extension
• 3.1. Working with basic type parameters
• 3.2. Working with class and record type parameters
• 3.3. Working with tuple type parameters
• 4.2. Working with array types
• 5. Asynchronous programming
• 6. Utility extensions