dotConnect for QuickBooks Online enhances SQL handling capabilities with usage of parameters in SQL queries. You can make execution of a query or stored procedure very flexible using several simple techniques. This article describes some basics you must be acquainted with when working with parameters in dotConnect for QuickBooks Online, as well as parameters synchronization and some nuances related to usage of stored procedures.

The article consists of following sections:

• Parameters Basics
• Using Automatic Parameters Synchronization
• Performance Issues
• Example

Parameters Basics

In general, parameter is a placeholder for a variable that contains some value of some type when executing a general-purpose query, or arguments and return values when a function is executed. Parameter is represented by the QuickBooksParameter class. All parameters that take part in query execution constitute a collection that can be accessed through QuickBooksCommand.Parameters property.

Parameters must correspond to the names of the QuickBooksParameter instances in the collection. Named parameters are declared using ':' or '@' prefix followed by name of the parameter. The name of the QuickBooksParameter object in the command's collection should be used without any prefix. You can use these prefixes at any combinations to specify parameters. There are two main advantages of named parameters. First, you do not have to care about the order in which parameters are created. Second, named parameter can appear more than once in query text, but you have to create only one instance of it in Parameters collection.

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

UPDATE Customer SET Title = :title WHERE DisplayName = :name

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

    using (var connection = new QuickBooksConnection("Company Id=1234567890;Access " +
        "Token=asdqerTYUJcom5DSKL0djSSBEEFcJohjCGXixTTOM4Z8UpiX;Access Token " +
        "Secret=ojVArHdmHagedPRIIVtP8DCQNUbGkloK7Rgct59x;Consumer " +
        "Key=vbnrdKcCHaccybn1fbwje3UJLOJm1e;Consumer Secret=649QYsbe76LfeFbhajG6ccaeG8pobxbkiL1De0DS;")) {
        connection.Open();

        QuickBooksCommand cmd = connection.CreateCommand();
        cmd.CommandText = "UPDATE Customer SET Title = :title WHERE DisplayName = :name";
        cmd.Parameters.Add("title", DbType.String).Value = "sample title 1";
        cmd.Parameters.Add("name", DbType.String).Value = "sample name 1";

        cmd.ExecuteNonQuery();
    }

    Using connection As New QuickBooksConnection("Company Id=1234567890;Access " & _
        "Token=asdqerTYUJcom5DSKL0djSSBEEFcJohjCGXixTTOM4Z8UpiX;Access Token " & _
        "Secret=ojVArHdmHagedPRIIVtP8DCQNUbGkloK7Rgct59x;Consumer " & _
        "Key=vbnrdKcCHaccybn1fbwje3UJLOJm1e;Consumer Secret=649QYsbe76LfeFbhajG6ccaeG8pobxbkiL1De0DS;")
        connection.Open()

        Dim cmd As QuickBooksCommand = connection.CreateCommand()
        cmd.CommandText = "UPDATE Customer SET Title = :title WHERE DisplayName = :name"
        cmd.Parameters.Add("title", DbType.String).Value = "sample title 1"
        cmd.Parameters.Add("name", DbType.String).Value = "sample name 1"

        cmd.ExecuteNonQuery()
    End Using

Using Automatic Parameters Synchronization

The behavior described above assumes that QuickBooksCommand.ParameterCheck is false (by default). By turning it on you enable automatic synchronization of query text and QuickBooksCommand.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 do not have to take care about quantity of items in QuickBooksCommand.Parameters collection, you can specify only the ones you really need. The synchronization is done as follows:

• If quantity of input parameters in the query text is more than quantity of input parameters in QuickBooksCommand.Parameters collection, new parameters are created and added to it, with QuickBooksParameter.Value assigned to DBNull. QuickBooksParameter.QuickBooksType is set to QuickBooksType.String. This property is updated to a correct type when a value is assigned to the parameter.
• If quantity of parameters in the query text is less than in QuickBooksCommand.Parameters collection, redundant parameters are deleted from it.
• If named parameters are involved in the query, newly created parameters in the collection are named so that they correspond to ones in the text.
• Values of parameters are not altered, except for the ones created during the synchronization process. But if QuickBooksCommand.CommandText is altered, the parameter collection will be regenerated entirely.

The synchronization takes place when:

• QuickBooksCommand.ParameterCheck is set to true
• QuickBooksCommand.CommandType is set to "Text"
• QuickBooksCommand.CommandText is altered
• Query is executed

When QuickBooksCommand.ParameterCheck is true, every change of QuickBooksCommand.CommandText or QuickBooksCommand.CommandType leads to deletion of all ReturnValue parameters. You have to add them manually each time you change one of these properties.