DataConnector | ComponentOne
ADO.NET provider for QuickBooks Online / Querying Data
In This Topic
    Querying Data
    In This Topic

    The ADO.NET Provider for QuickBooks Online implements two classes you can use to perform CRUD (Create, Read, Update, and Delete) operations: DbDataReader and C1QuickBooksOnlineDataAdapter classes. The tabs below describe the interfaces and code implementations.

    Querying With DbDataReader

    The DbDataReader class can be used to fetch data in subset increments as required. It can retrieve data quicker than the C1QuickBooksOnlineDataAdapter as it retrieves data in pages. When you read data from the DbDataReader, it requests the succeeding page from the data source to load the result, which makes the data retrieval faster.

    The following code example demonstrates create, read, update, and delete operations from the data source using the DbDataReader.

     

    Create Data

    The following example adds new records by executing the Insert command.

    C#
    Copy Code 
    static void InsertDataReader()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        conn.Open();
        //Create Insert command
        C1QuickBooksOnlineCommand command = new C1QuickBooksOnlineCommand(conn, $@"Insert into Attachables(Note, Category) values('{uniqueNote}', 'Document')");

        Console.WriteLine("Insert operation started !!! \n \n");
        //Execute Insert command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Insert operation successful !!! \n \n");
        }
    }
}

    Read Data

    The following example retrieves data by executing the Select command.

    C#
    Copy Code 
    static void ReadDataReader()
{
    //Setup Connection
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        conn.Open();

        //Create Read command
        var cmd = conn.CreateCommand();
        cmd.CommandText = "Select * FROM Attachables";

        Console.WriteLine("Read operation started !!! \n \n");
        //Execute Read command and display fetched data
        DbDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine(String.Format("{0}\t{1}\t{2}", rdr["Id"], rdr["Category"], rdr["Note"]));
        }
        Console.WriteLine("Read operation successful !!! \n \n");
    }
}

    Update Data

    The following example modifies data by executing the Update command.

    C#
    Copy Code 
    static void UpdateDataReader()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        conn.Open();
        //Create Update command              
        C1QuickBooksOnlineCommand command = new C1QuickBooksOnlineCommand(conn, "UPDATE Attachables SET Category = @Category WHERE Note = @Note");
        command.Parameters.AddWithValue("@Category", "Other");
        command.Parameters.AddWithValue("@Note", uniqueNote);

        Console.WriteLine("Update operation started !!! \n \n");
        //Execute Update command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Update operation successful !!! \n \n");
        }
    }
}

    Delete Data

    The following example removes data by executing the Delete command.

    C#
    Copy Code 
    static void DeleteDataReader()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        conn.Open();
        //Create Delete command
        C1QuickBooksOnlineCommand command = new C1QuickBooksOnlineCommand(conn, "Delete From Attachables where Note = @Note");
        command.Parameters.AddWithValue("@Note", uniqueNote);

        Console.WriteLine("Delete operation started !!! \n \n");
        //Execute Delete command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
}

    Querying With DbDataAdapter

    The C1QuickBooksOnlineDataAdapter class can be used to retrieve a single result set containing all the data that matches a given query. The C1QuickBooksOnlineDataAdapter uses its Fill method to fetch data from the data source. An empty DataTable instance is passed as an argument to the Fill method. Once the method returns, the DataTable instance is populated with the queried data. Since the Fill method must retrieve all the data from the data source before returning, the C1QuickBooksOnlineDataAdapter is slower compared to the DbDataReader.

    The following code examples demonstrate create, read, update, and delete operations from the data source using the DbDataAdapter.

     

    Create Data

    The following example adds new records by executing the Insert command.

    C#
    Copy Code 
    static void CreateDataAdapter()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //Populate DataTable
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Create Insert command
        adapter.InsertCommand = new C1QuickBooksOnlineCommand(conn);
        adapter.InsertCommand.CommandText = "Insert into Attachables(Note, Category) Values(@Note, @Category)";
        adapter.InsertCommand.Parameters.Add("@Note", "Note");
        adapter.InsertCommand.Parameters.Add("@Category", "Category");

        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
        //Insert new row
        DataRow customerRow = dataTable.NewRow();
        customerRow["Note"] = uniqueNote;
        customerRow["Category"] = "Document";
        dataTable.Rows.Add(customerRow);

        //Update database
        var i = adapter.Update(dataTable);

        if (i != -1)
        {
            Console.WriteLine("Insert operation successful !!! \n \n");
        }
    }
}

    Read Data

    The following example retrieves data by executing the Select command.

    C#
    Copy Code 
    static void ReadDataAdapter()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //Populate DataTable                
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables");
        DataTable dataTable = new DataTable();
        var i = adapter.Fill(dataTable);
        if (i != -1)
        {
            Console.WriteLine("Read operation successful !!! \n \n");
            //Display fetched data
            foreach (DataRow row in dataTable.Rows)
            {
                Console.WriteLine("{0}\t\t{1}\t\t\t{2}", row["Id"], row["Category"], row["Note"]);
            }
        }
    }
}

    Update Data

    The following example modifies data by executing the Update command.

    C#
    Copy Code 
    static void UpdateDataAdapter()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //Populate DataTable
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables Where Category = 'Document'");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Create Update command                
        adapter.UpdateCommand = new C1QuickBooksOnlineCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE Attachables SET Category = @Category WHERE Id = @Id";
        adapter.UpdateCommand.Parameters.Add("@Category", "Category");
        adapter.UpdateCommand.Parameters.Add("@Id", "Id");

        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        //Update existing row
        DataRow customerRow = dataTable.Rows[0];
        customerRow["Category"] = "Signature";
        customerRow["Id"] = "xxxxxxxxxxxxxxxxxxx";

        //Update database
        var i = adapter.Update(dataTable);

        if (i != -1)
        {
            Console.WriteLine("Update operation successful !!! \n \n");
        }
    }
}

    Delete Data

    The following example removes data by executing the Delete command.

    C#
    Copy Code 
    static void DeleteDataAdapter()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //Populate Datatable
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, $"Select * from Attachables Where Note = '{uniqueNote}'");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Create Delete command
        adapter.DeleteCommand = new C1QuickBooksOnlineCommand(conn);
        adapter.DeleteCommand.CommandText = "Delete From Attachables Where Id = @Id";
        adapter.DeleteCommand.Parameters.Add("@Id", "Id");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

        //Delete a row
        DataRow customerRow = dataTable.Rows[0];
        customerRow.Delete();

        //Update Database
        var i = adapter.Update(dataTable);

        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
}