Wiki

##DbHug ##Templating

The Templating is the one of the core concepts of DbHug. Templating creates a command with DbParameters or in-line values form strings or objects.

###Parameter templating #####DbParameter

#!csharp
var cmd = "SELECT * FROM TEST WHERE ID=@ID AND NAME=@Name;";
var result = dbHug.Select(cmd, new SqlParameter("ID", 1), new SqlParameter("Name", "John Wayne"));

So it is the simplest way to execute command. Command text will not be changed, two parameters will be added to command by name and then executed. The logical meaning of the command is not important for examples.

#####Argument object

#!csharp
var cmd = "SELECT * FROM TEST WHERE ID=@Id AND NAME=@Name;";
var result = dbHug.Select(cmd, new { Id = 1, Name = "John Wayne" });

Execution of this example remains the same as in previous one, but parameters will be created from argument object.
And then will be added @Id and @Name db parameters to command. So if argument object passed to method, DbHug will try to find names of parameters from command in the argument object and then create a DbParameters.
Argument name can be ExpandoObject as well.

#!csharp
var cmd = "SELECT * FROM TEST WHERE ID=@Id AND NAME=@Name;";
dynamic value = new ExpandoObject();
value .Id = 1;
value.Name = "John Wayne";
var result = dbHug.Select(cmd, value);

#####Scalar In case of passing scalar type (int, string, bool, etc.) as argument object value will be used only for a first parameter. This works for all templating types @x,{x},{x:E}.

#!csharp
var cmd = "SELECT * FROM TEST WHERE NAME=@Name;";
var name = "John Wayne";
var result = dbHug.Select(cmd, name);

#####Combined

#!csharp
var cmd = "SELECT * FROM TEST WHERE ID=@Id AND NAME=@Name;";
var result = dbHug.Select(cmd, new { Id = 1 }, new SqlParameter("Name", "John Wayne"));

Or

#!csharp
var cmd = "SELECT * FROM TEST WHERE ID=@Id AND NAME=@Name;";
var result = dbHug.Select(cmd, 1, new SqlParameter("Name", "John Wayne"));

The @Id will get value of argument object - 1. In case if pass the Name parameter as argument and then add Id as DbParameter, @Name will not be set at all, but @Id will get DbParameter value but not an argument one. Same for anonymous object or ExpandoObject, DbParameters has more priority.

###Templating #####Parameter {x} or {x:P}.

#!csharp
var cmd = "SELECT * FROM TEST WHERE ID={Id} AND NAME={Name};";
var result = dbHug.Select(cmd, new { Id = 1, Name = "John Wayne" });

Sql text will be:

#!sql
SELECT * FROM TEST WHERE ID=@Id AND NAME=@Name;

Using {x} template will change command, all other stuff remains the same. In this case {x} or {x:P} is the same, :P indicates the the template must create parameter for command, but it is default, so it is not mandatory to use :P. Why then use {x} template instead of @x? It is important for many executions and passing enumerable as a field of argument. Furthermore DbParameters will not be added to command with {x} template.

#!csharp
var cmd = "SELECT * FROM TEST WHERE ID={Id} AND NAME={Name};";
var result = dbHug.Select(cmd, new { Id = 1 }, new SqlParameter("Name", "John Wayne"));

Will throw an error because the Name field could not be found in argument.

#####Explicit templating {x:E}, with optional q - {x:Eq}.

#!csharp
var cmd = "SELECT * FROM TEST WHERE ID={Id:E} AND NAME={Name:Eq};";
var result = dbHug.Select(cmd, new { Id = 1 , Name = "John Wayne" });

#!sql
SELECT * FROM TEST WHERE ID=1 AND NAME='John Wayne';

Explicit templating will insert values in the command string without adding parameters. Additional q parameter will add quotes around value.
Why to use it? Sometimes there is a need to do that, for example add a TOP operator in the command dynamically.

#!csharp
var addTop = true; //indeed this value is set somewhere depends on business rules
var cmd = "SELECT {top:E} * FROM TEST;";
var result = dbHug.Select(cmd, new { top: addTop ? "TOP(10)" : string.Empty });

Or any similar case...

##### Internal properties of an argument object You can use internal properties of an argument object if it is needed.

#!csharp
var cmd = "SELECT * FROM TEST WHERE ID={Id} AND NAME={Person.Name};";
var result = dbHug.Select(cmd, new { Id = 1, Person = new { Name = "John Wayne" } });

###Many executions

#!csharp
var phoneIds = new[] { new { Id = 1 }, new { Id = 2 } };
dbHug.ExecuteNonQueries("DELETE FROM PHONES WHERE Id={Id}", phoneIds));

sql:

#!sql
DELETE FROM PHONES WHERE Id=@Id_0;
DELETE FROM PHONES WHERE Id=@Id_1;

For many executions it is mandatory to use {x} templating instead of @x. Because everything are mixing in one command and @x is not changeable in the command string, so the parameter @Id, for example, will be one for all commands and will equal to 2.
The generated sql for:

#!csharp
dbHug.ExecuteNonQueries("DELETE FROM PHONES WHERE Id=@Id",new[] { new { Id = 1 }, new { Id = 2 } });

will be

#!sql
DELETE FROM PHONES WHERE Id=@Id;
DELETE FROM PHONES WHERE Id=@Id;

So there will be one @Id with latest value. But it useful when there is one param that used in all executions:

#!csharp
dbHug.ExecuteNonQueries("EXEC remove_data {Id}, @User",new[] { new { Id = 1 }, new { Id = 2 } }, new SqlParamter("User", CurrentUserInfo.User);

Explicit templating for many executions is same. Values will be inserted for each command:

#!csharp
dbHug.ExecuteNonQueries("DELETE FROM PHONES WHERE Id={Id:E}",new[] { new { Id = 1 }, new { Id = 2 } });

#!sql
DELETE FROM PHONES WHERE Id=1;
DELETE FROM PHONES WHERE Id=2;

###IEnumerable as a parameter Template is the same as for single value - {x},{x:P},{x:E}, but with additional support.
s for parameter, and can only be used with :P, {x:Ps} a single string parameter. All values will be added to command as a single DbParameter.
S(y) is separator value, y - separator, between each of them, by default it is ,. It can be added to parameter template with s parameter {x:Ps.S(y)}.

#!csharp
var cmd = "SELECT * FROM TEST WHERE VALUE IN ({List})";
dbHug.Select<Test>(cmd, new {List = new[]{ "abc", "def" } });

#!sql
SELECT * FROM TEST WHERE VALUE IN (@StrList_0, @StrList_1)

Explicit:

#!csharp
var cmd = "SELECT * FROM TEST WHERE VALUE IN ({List:Eq})";
dbHug.Select<Test>(cmd, new {List = new[]{ "abc", "def" } });

#!sql
SELECT * FROM TEST WHERE VALUE IN ('abc', 'def')

* In addition: {{List:Eq.S(;)}} will become 'abc';'def' or {{List:Ps.S(;)}} will become a new parameter with value 'abc';'def'.

####Mapping ... Commands