dotConnect for MongoDB supports parameters both in SQL statements and in native MongoDB commands. This article describes the basics of working with parameters in dotConnect for MongoDB and automatic parameter synchronization.

Parameter Basics

In general, a parameter is a placeholder for a variable that contains a value of a certain type when a general-purpose query is executed. A parameter is represented by the MongoDBParameter class. All parameters that take part in query execution constitute a collection that can be accessed through the MongoDBCommand.Parameters property.

Parameters in the query text must correspond to the names of the MongoDBParameter instances in the collection. Named parameters are declared using the ':' or '@' prefix followed by the parameter name. Use the name of the MongoDBParameter object in the command's collection without any prefix. You can use these prefixes in any combination. Named parameters have two main advantages. First, you don't 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.

You can also use the '?' positional parameter marker. In this case, parameters are bound in the order in which they're added to the collection. Note that '$name' isn't a parameter marker because MongoDB operators start with '$'.

dotConnect for MongoDB supports only input parameters (ParameterDirection.Input) because MongoDB has no stored procedures.

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

UPDATE customers SET "address.street" = :street WHERE name = :name

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

    using (var connection = new MongoDBConnection("Host=localhost;Database=sample;User Id=user;Password=pwd;License Key=***")) {
        connection.Open();

        MongoDBCommand cmd = connection.CreateCommand();
        cmd.CommandText = "UPDATE customers SET \"address.street\" = :street WHERE name = :name";
        cmd.Parameters.Add("street", DbType.String).Value = "Main St";
        cmd.Parameters.Add("name", DbType.String).Value = "Bob";

        cmd.ExecuteNonQuery();
    }

    Using connection As New MongoDBConnection("Host=localhost;Database=sample;User Id=user;Password=pwd;License Key=***")
        connection.Open()

        Dim cmd As MongoDBCommand = connection.CreateCommand()
        cmd.CommandText = "UPDATE customers SET ""address.street"" = :street WHERE name = :name"
        cmd.Parameters.Add("street", DbType.String).Value = "Main St"
        cmd.Parameters.Add("name", DbType.String).Value = "Bob"

        cmd.ExecuteNonQuery()
    End Using

Parameters in Native Mode

In native mode (the Sql Engine connection string parameter is set to False), the command text is a MongoDB database command in Extended JSON. Parameters are substituted into the JSON text on the client side, and string values are escaped. A parameter of the MongoDBType.Object type is inserted as a JSON document. For example, the following command gets the filter from the filter parameter:

{"find": "customers", "filter": :filter}

Using Automatic Parameter Synchronization

The behavior described above assumes that MongoDBCommand.ParameterCheck is false (default). If you set it to true, you enable automatic synchronization of the query text and the MongoDBCommand.Parameters collection. In this mode, all input parameters are checked for validity, new ones are added if necessary, and redundant parameters are deleted. Thus, you don't have to care about the number of items in the MongoDBCommand.Parameters collection and can specify only the ones you really need. The synchronization is performed as follows:

• If the query text contains more input parameters than the MongoDBCommand.Parameters collection, new parameters are created and added to the collection. Their MongoDBParameter.Value is set to DBNull, and MongoDBParameter.MongoDBType is set to MongoDBType.String. This property is updated to the correct type when a value is assigned to the parameter.
• If the query text contains fewer parameters than the MongoDBCommand.Parameters collection, redundant parameters are deleted from it.
• If the query uses named parameters, newly created parameters in the collection are named to match the ones in the text.
• Parameter values aren't changed, except for the parameters created during synchronization. However, if MongoDBCommand.CommandText is changed, the parameter collection is regenerated entirely.

The synchronization takes place when:

• MongoDBCommand.ParameterCheck is set to true.
• MongoDBCommand.CommandType is set to Text.
• MongoDBCommand.CommandText is changed.
• A query is executed.