Parameters

Overview

Use parameters to pass the data to the queries. Parameters are sort of variables in a query. However, you can’t get the data from the query using parameters.

Basic Concept

Usage of parameters is trivial; you can create a parameter and simply associate the name of the parameter with the number, string, an array or an object.

Parameters param = db.CreateParameters();

param["name"] = "John"

In this database, there are some overloaded functions that take parameters as a argument:

var res = db.ExecuteQuery("SELECT BasicUser WHERE Name = @name", param);

or

var res = db.ExecuteScalar("SELECT BasicUser WHERE Name = @name", param);

Simple type parameters

Parameters could be of an arbitrary value of a string type or any simple types, e.g. int, long

Parameters param = db.CreateParameters();

param["name"] = "John"

var res = db.ExecuteQuery("SELECT BasicUser WHERE Name = @name", param);

or

Parameters param = db.CreateParameters();

param["name"] = ((BasicUser)db.ExecuteScalar("SELECT BasicUser")).Name;

var res = db.ExecuteQuery("SELECT BasicUser WHERE Name = @name", param);

Object parameters

All object parameters (except for the DateTime) should come from the database.

One of the reasons for this is that there is no standard way to compare arbitrary objects.

Parameters param = db.CreateParameters();

param["school"] = db.ExecuteScalar("SELECT UserSchool WHERE Name CONTAINS 'Thornleigh'");

var res = db.ExecuteQuery("SELECT BasicUser WHERE UserSchool = @school", param);

With DateTime, you can supply any object:

Parameters param = db.CreateParameters();

param["dt1"] = new DateTime(2009, 05, 02);

param["dt2"] = new DateTime(2009, 05, 04);

var res = db.ExecuteQuery("SELECT BasicUser WHERE BirthDate BETWEEN @dt1 AND @dt2");

Array parameters

Array parameters allow you to use one- or multi dimensional array of simple types and strings.

Please note that for the arbitrary arrays, you should use AddList (or SetList), This allows us to pass list of valuess instead of an object.

Parameters param = db.CreateParameters();

param.AddList("emails", new string[]{"john@gmail.com", "david@hotmail.com"});

var res = db.ExecuteQuery("SELECT BasicUser WHERE Emails CONTAINS @emails", param);

Assigning the parameter value is treated as object parameter, not a list parameter.

If you want to find object that references a given list, use “=” operator.

Parameters param = db.CreateParameters();

param["emails"] = ((BasicUser)db.ExecuteScalar("SELECT BasicUser")).Emails;

var res = db.ExecuteQuery("SELECT BasicUser WHERE Emails contains @emails", param);

In queries, array parameters could be used as an ordinary array, for example:

Parameters param = db.CreateParameters();

param.AddList("emails", new string[]{"john@gmail.com", "david@hotmail.com"});

var res = db.ExecuteQuery("SELECT BasicUser WHERE ALL @emails in Emails", param);

Parameters in TOP, SKIP and in ORDER BY

Also, you can use parameters in TOP or in ORDER BY

Parameters param = db.CreateParameters();

param["top"] = 5;

var res = db.ExecuteQuery("SELECT TOP @top BasicUser WHERE Name = 'John'")

Parameters param = db.CreateParameters();

param.AddList("interest", new string[]{"reading", "running"});

var res = db.ExecuteQuery("SELECT BasicUser WHERE Name = 'John' ORDER BY Interests in @interest")

NULL parameters

Parameters can carry a null value:

Parameters param = db.CreateParameters();

param["null"] = null;

param["name"] = "John"

var res = db.ExecuteQuery("SELECT BasicUser WHERE Name = @name AND Friends != @null");