Retrieving Data

Introduction
Requirements
General Information
Retrieving Data using QuickBooksCommand and QuickBooksDataReader
Retrieving Data using QuickBooksDataAdapter
Additional Information

Introduction

This tutorial describes how to use QuickBooksCommand, QuickBooksDataReader, and QuickBooksDataAdapter components.

Requirements

This tutorial supposes that you know how to connect to server.

Note that if you do not use design-time (specifically, if you do not place on a designer QuickBooksConnection component from toolbox), you have to embed license information manually. This is described in the Licensing topic.

General Information

The main function of any database application is establishing a connection to a data source and working with data contained in it. ADO.NET data providers serve as a bridge between an application and a data source, and allow you to execute commands as well as to retrieve data by using a DataReader or a DataAdapter. Updating data involves using the Command and DataAdapter objects.

Retrieving Data using QuickBooksCommand and QuickBooksDataReader

In this sample we will use QuickBooksCommand and QuickBooksDataReader to retrieve data. QuickBooksDataReader allows retrieving data in pages. While you read data from it, it automatically queries next pages from QuickBooks Online. QuickBooksDataReader offers higher performance than QuickBooksDataAdapter, especially when you query a lot of data. For more information, refer to the description of these classes in our documentation.

using Devart.Data.QuickBooks;
...
class Program
{
    static void Main(string[] args) {

        const string connectionString = "Company Id=1234567890;Access " +
            "Token=asdqerTYUJcom5DSKL0djSSBEEFcJohjCGXixTTOM4Z8UpiX;Access Token " +
            "Secret=ojVArHdmHagedPRIIVtP8DCQNUbGkloK7Rgct59x;Consumer " +
            "Key=vbnrdKcCHaccybn1fbwje3UJLOJm1e;Consumer Secret=649QYsbe76LfeFbhajG6ccaeG8pobxbkiL1De0DS;";
        const string sql = "SELECT Title, DisplayName FROM Customer";

        using (QuickBooksConnection connection = new QuickBooksConnection(connectionString)) {

            connection.Open();
            using (QuickBooksCommand command = connection.CreateCommand()) {

                command.CommandText = sql;
                using (QuickBooksDataReader reader = command.ExecuteReader()) {

                    while (reader.Read()) {

                        Console.WriteLine("{0}\t{1}", reader.GetValue(0), reader.GetValue(1));
                    }
                }
            }
        }
        Console.ReadKey();
    }

}

Visual Basic

Imports Devart.Data.QuickBooks
...
Module Module1

    Sub Main()

        Const connectionString As String = "Company Id=1234567890;Access " & _
            "Token=asdqerTYUJcom5DSKL0djSSBEEFcJohjCGXixTTOM4Z8UpiX;Access Token " & _
            "Secret=ojVArHdmHagedPRIIVtP8DCQNUbGkloK7Rgct59x;Consumer " & _
            "Key=vbnrdKcCHaccybn1fbwje3UJLOJm1e;Consumer Secret=649QYsbe76LfeFbhajG6ccaeG8pobxbkiL1De0DS;"
        Const sql As String = "SELECT Title, DisplayName FROM Customer"

        Using connection As New QuickBooksConnection(connectionString)

            connection.Open()
            Using command As QuickBooksCommand = connection.CreateCommand()

                command.CommandText = sql
                Using reader As QuickBooksDataReader = command.ExecuteReader()

                    While reader.Read()

                        Console.WriteLine(reader.GetValue(0) & vbTab & reader.GetValue(1))
                    End While
                End Using
            End Using
        End Using
        Console.ReadKey()
    End Sub

End Module

Retrieving Data using QuickBooksDataAdapter

Applicable only for projects targeting full .NET Framework.

In this sample we will use the QuickBooksDataAdapter component. It retrieves data when you call its Fill method. Note that this method needs to retrieve all the queried data from QuickBooks Online, and it can take some time.

Here is a small sample that demonstrates usage of QuickBooksDataAdapter to retrieve data.

    static void Main(string[] args) {

        const string connectionString = "Company Id=1234567890;Access " +
            "Token=asdqerTYUJcom5DSKL0djSSBEEFcJohjCGXixTTOM4Z8UpiX;Access Token " +
            "Secret=ojVArHdmHagedPRIIVtP8DCQNUbGkloK7Rgct59x;Consumer " +
            "Key=vbnrdKcCHaccybn1fbwje3UJLOJm1e;Consumer Secret=649QYsbe76LfeFbhajG6ccaeG8pobxbkiL1De0DS;";
        const string sql = "SELECT Title, DisplayName FROM Customer";

        using (QuickBooksConnection connection = new QuickBooksConnection(connectionString)) {

            connection.Open();
            DataTable table = new DataTable("Customer");

            using (QuickBooksCommand command = connection.CreateCommand()) {

                command.CommandText = sql;
                using (QuickBooksDataAdapter adapter = new QuickBooksDataAdapter(command)) {
                    adapter.Fill(table);
                }
            }

            foreach (DataRow row in table.Rows) {
                Console.WriteLine("{0}\t{1}", row[0], row[1]);
            }
        }
        Console.ReadKey();
    }

Visual Basic


    Sub Main()

        Const connectionString As String = "Company Id=1234567890;Access " & _
            "Token=asdqerTYUJcom5DSKL0djSSBEEFcJohjCGXixTTOM4Z8UpiX;Access Token " & _
            "Secret=ojVArHdmHagedPRIIVtP8DCQNUbGkloK7Rgct59x;Consumer " & _
            "Key=vbnrdKcCHaccybn1fbwje3UJLOJm1e;Consumer Secret=649QYsbe76LfeFbhajG6ccaeG8pobxbkiL1De0DS;"
        Const sql As String = "SELECT Title, DisplayName FROM Customer"

        Using connection As New QuickBooksConnection(connectionString)

            connection.Open()
            Dim table As New DataTable("Customer")

            Using command As QuickBooksCommand = connection.CreateCommand()

                command.CommandText = sql
                Using adapter As New QuickBooksDataAdapter(command)
                    adapter.Fill(table)
                End Using
            End Using

            For Each row As DataRow In table.Rows
                Console.WriteLine(row(0) & vbTab & row(1))
            Next
        End Using
        Console.ReadKey()
    End Sub

Additional Information

This tutorial describes only basic ways of working with data. For example, you can take advantage of using Entity Framework ORM technology which is intended for converting data between incompatible type systems in different data sources and object-oriented programming languages. This technology allow you to decrease the amount of code and maintenance required for data-oriented applications. For more information, please refer to Entity Framework section.