dotConnect for Google BigQuery enhances SQL handling capabilities through the use of parameters in SQL queries. You can make execution of a query very flexible using several techniques. This article describes some basics you must know when working with parameters in dotConnect for Google BigQuery, as well as parameters synchronization.

Parameters Basics

In general, a parameter is a placeholder for a variable that contains some value of some type when executing a query. A parameter is represented by the BigQueryParameter class. All parameters that take part in query execution constitute a collection that can be accessed through the BigQueryCommand.Parameters property.

dotConnect for Google BigQuery uses BigQuery named query parameters. Named parameters are declared in the query text using the '@' prefix followed by the name of the parameter. The name of the BigQueryParameter object in the command's collection can be specified without the prefix. There are two main advantages of named parameters. First, you do not have to care about the order in which parameters are created. Second, a named parameter can appear more than once in the query text, but you have to create only one instance of it in the Parameters collection.

For example, a simple UPDATE statement that requires named parameters might look like the following:

UPDATE dept SET loc = @loc WHERE deptno = @deptno

To set parameters for this query you can use the following code:

using (var connection = new BigQueryConnection(@"Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********")) {
    connection.Open();

    BigQueryCommand cmd = connection.CreateCommand();
    cmd.CommandText = "UPDATE dept SET loc = @loc WHERE deptno = @deptno";
    cmd.Parameters.Add("loc", DbType.String).Value = "BOSTON";
    cmd.Parameters.Add("deptno", DbType.Int64).Value = 10;

    cmd.ExecuteNonQuery();
}

Using connection As New BigQueryConnection("Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********")
    connection.Open()

    Dim cmd As BigQueryCommand = connection.CreateCommand()
    cmd.CommandText = "UPDATE dept SET loc = @loc WHERE deptno = @deptno"
    cmd.Parameters.Add("loc", DbType.String).Value = "BOSTON"
    cmd.Parameters.Add("deptno", DbType.Int64).Value = 10

    cmd.ExecuteNonQuery()
End Using

The type of a parameter value must match the type of the expression it is compared with or assigned to: BigQuery does not convert, for example, STRING parameters to INT64 implicitly. Set the DbType or BigQueryParameter.Type property, or assign a value of the corresponding .NET type.

BigQuery stored procedures are called with the CALL statement in the command text.

CALL your_dataset.your_procedure(@arg)

The CommandType.StoredProcedure command type is not supported.

Using Automatic Parameters Synchronization

The behavior described above assumes that BigQueryCommand.ParameterCheck is false (by default). By turning it on you enable automatic synchronization of the query text and the BigQueryCommand.Parameters collection. In this mode all parameters are checked for validity, new ones are added if necessary, and redundant parameters are deleted. Thus, you do not have to take care of the quantity of items in the BigQueryCommand.Parameters collection. The synchronization is done as follows:

• If the quantity of parameters in the query text is more than the quantity of parameters in the BigQueryCommand.Parameters collection, new parameters are created and added to it, with BigQueryParameter.Value assigned to DBNull. BigQueryParameter.Type is set to BigQueryType.String. This property is updated to the correct type when a value is assigned to the parameter.
• If the quantity of parameters in the query text is less than in the BigQueryCommand.Parameters collection, redundant parameters are deleted from it.
• Newly created parameters are named exactly as in the query text, including the '@' prefix. For example, after setting the command text to UPDATE dept SET loc = @loc WHERE deptno = @deptno, use the following code to access the parameter:

  cmd.Parameters["@loc"].Value = "BOSTON";

• Values of parameters are not altered, except for the ones created during the synchronization process. But if BigQueryCommand.CommandText is altered, the parameter collection will be regenerated entirely.

The synchronization takes place when:

• BigQueryCommand.ParameterCheck is set to true.
• BigQueryCommand.CommandText is altered.
• The query is executed.