DataConnector
データのクエリ
ADO.NET provider for QuickBooks Online > データのクエリ

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 demonstrate 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#
コードのコピー 
static void InsertDataReader()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        conn.Open();
        //Insert コマンドを作成します。
        C1QuickBooksOnlineCommand command = new C1QuickBooksOnlineCommand(conn, $@"Insert into Attachables(Note, Category) values('{uniqueNote}', 'Document')");

        Console.WriteLine("Insert operation started !!! \n \n");
        //Insert コマンドを実行します。
        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#
コードのコピー 
static void ReadDataReader()
{
    //接続を設定します。
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        conn.Open();

        //Readコマンドを作成します。
        var cmd = conn.CreateCommand();
        cmd.CommandText = "Select * FROM Attachables";

        Console.WriteLine("Read operation started !!! \n \n");
        //Readコマンドを実行し、取得したデータを表示します。
        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 Update command.

C#
コードのコピー 
static void UpdateDataReader()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        conn.Open();
        //Update コマンドを作成します。              
        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");
        //Update コマンドを実行します。
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Update operation successful !!! \n \n");
        }
    }
}

Delete Data

The following example removes data by executing Delete command.

C#
コードのコピー 
static void DeleteDataReader()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        conn.Open();
        //Delete コマンドを作成します。
        C1QuickBooksOnlineCommand command = new C1QuickBooksOnlineCommand(conn, "Delete From Attachables where Note = @Note");
        command.Parameters.AddWithValue("@Note", uniqueNote);

        Console.WriteLine("Delete operation started !!! \n \n");
        //Delete コマンドを実行します。
        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#
コードのコピー 
static void CreateDataAdapter()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //DataTable を設定します。
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Insert コマンドを作成します。
        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;
        //新しい行を挿入します。
        DataRow customerRow = dataTable.NewRow();
        customerRow["Note"] = uniqueNote;
        customerRow["Category"] = "Document";
        dataTable.Rows.Add(customerRow);

        //データベースを更新します。
        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#
コードのコピー 
static void ReadDataAdapter()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //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");
            //取得したデータを表示します。
            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 Update command.

C#
コードのコピー 
static void UpdateDataAdapter()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        //DataTable を設定します。
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, "Select * from Attachables Where Category = 'Document'");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Update コマンドを作成します。                
        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;

        // 既存の行を更新します。
        DataRow customerRow = dataTable.Rows[0];
        customerRow["Category"] = "Signature";
        customerRow["Id"] = "xxxxxxxxxxxxxxxxxxx";

        //データベースを更新します。
        var i = adapter.Update(dataTable);

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

Delete Data

The following example removes data by executing Delete command.

C#
コードのコピー 
static void DeleteDataAdapter()
{
    using (C1QuickBooksOnlineConnection conn = new C1QuickBooksOnlineConnection(connectionString))
    {
        // データテーブルにデータを入力します。
        C1QuickBooksOnlineDataAdapter adapter = new C1QuickBooksOnlineDataAdapter(conn, $"Select * from Attachables Where Note = '{uniqueNote}'");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Delete コマンドを作成します。
        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;

        //行を削除します。
        DataRow customerRow = dataTable.Rows[0];
        customerRow.Delete();

        //データベースを更新します。
        var i = adapter.Update(dataTable);

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

 

 

Copyright © MESCIUS, Inc. All rights reserved.