dotConnect for QuickBooks Online Documentation
On this page
    Using Parameters
    On this page

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

    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, or for arguments and return values when a function is executed. A parameter is represented by the QuickBooksParameter class. All parameters that take part in query execution constitute a collection that can be accessed through the QuickBooksCommand.Parameters property.

    Parameters in the query text must correspond to the names of the QuickBooksParameter instances in the collection. Named parameters are declared using the ':' or '@' prefix followed by the parameter name. Use the name of the QuickBooksParameter 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 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 with 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 following 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 Parameter Synchronization

    The behavior described above assumes that QuickBooksCommand.ParameterCheck is false (by default). If you set it to true, you enable automatic synchronization of the query text and the 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 care about the number of items in the QuickBooksCommand.Parameters collection and can specify only the ones you really need. The synchronization is performed as follows:

    The synchronization takes place when:

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