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

The ADO.NET Provider for Kintone implements two classes you can use to perform CRUD (Create, Read, Update, and Delete) operations: DbDataReader and C1KintoneDataAdapter 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 C1KintoneDataAdapter 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 examples demonstrate create, read, update, and delete operations from the data source using the DbDataReader.

 

Create Data

The example adds new records by executing the Insert command.

C#
コードのコピー 
static void InsertDataReader()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);

    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        conn.Open();
        //Insert コマンドを作成します。
        C1KintoneCommand command = new C1KintoneCommand(conn, $"INSERT INTO Products(ProductName) VALUES('Lorem Ipsum')");
        //Execute Insert command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Insert operation successful !!! \n \n");
        }
    }
}

Read Data

This examples retrieves data by executing the Select command.

C#
コードのコピー 
static void ReadDataReader()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);

    using (C1KintoneConnection con = new C1KintoneConnection(kintoneConnection))
    {
        con.Open();
        //Read コマンドを作成します。
        var cmd = con.CreateCommand();
        cmd.CommandText = "SELECT  *  FROM Products";
        //Readコマンドを実行し、取得したデータを表示します。
        DbDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine(String.Format("\t{0} --> \t\t{1}", rdr["Updated By"], rdr["ProductName"]));
        }
        Console.WriteLine("Read operation successful !!! \n \n");
    }
}

Update Data

This example modifies data by executing Update command.

C#
コードのコピー 
static void UpdateDataReader()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);

    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        conn.Open();
        //Update コマンドを作成します。
        C1KintoneCommand command = new C1KintoneCommand(conn, "UPDATE Products SET [ProductName]='Lorem Ipsum updated' WHERE ProductName = 'Lorem Ipsum'");

        //Update コマンドを実行します。
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Update operation successful !!! \n \n");
        }
    }
}

Delete Data

This example removes data by executing Delete command.

C#
コードのコピー 
static void DeleteDataReader()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);

    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        conn.Open();
        //Delete コマンドを作成します。
        C1KintoneCommand command = new C1KintoneCommand(conn, "DELETE FROM Products WHERE ProductName = 'Lorem Ipsum updated'");

        //Delete コマンドを実行します。
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
}

Querying With C1KintoneDataAdapter

The C1KintoneDataAdapter class can be used to retrieve a single result set containing all the data that matches a given query. The C1KintoneDataAdapter 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 C1KintoneDataAdapter is slower compared to the DbDataReader.

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

 

Create Data

The example adds new records by executing the Insert command.

C#
コードのコピー 
static void InsertDataAdapter()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);

    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //DataTable を設定します。
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "SELECT * FROM Products");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Insert コマンドを作成します。
        adapter.InsertCommand = new C1KintoneCommand(conn);
        adapter.InsertCommand.CommandText = "INSERT INTO Products(ProductName, UnitPrice) VALUES(@ProductName, @UnitPrice)";
        adapter.InsertCommand.Parameters.Add("@UnitPrice", "UnitPrice");
        adapter.InsertCommand.Parameters.Add("@ProductName", "ProductName");
        adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
        //新しい行を挿入します。
        DataRow productRow = dataTable.NewRow();
        productRow["UnitPrice"] = 100;
        productRow["ProductName"] = "Lorem Ipsum";
        dataTable.Rows.Add(productRow);

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

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

Read Data

This examples retrieves data by executing the Select command.

C#
コードのコピー 
static void ReadDataAdapter()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);

    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //DataTable を設定します。                
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "SELECT * FROM Products");
        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{1}", row["Updated By"], row["ProductName"]);
            }
        }
    }
}

Update Data

This example modifies data by executing Update command.

C#
コードのコピー 
static void UpdateDataAdapter()
{
    string kintoneConnection = string.Format("Username={0};Password={1};Url={2}", Username, Password, Url);

    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        //DataTable を設定します。
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "SELECT * FROM Products");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Update コマンドを作成します。
        adapter.UpdateCommand = new C1KintoneCommand(conn);
        adapter.UpdateCommand.CommandText = "UPDATE Products SET UnitPrice = @UnitPrice WHERE ProductName = @ProductName";
        adapter.UpdateCommand.Parameters.Add("@UnitPrice", "UnitPrice");
        adapter.UpdateCommand.Parameters.Add("@ProductName", "ProductName");
        adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

        // 既存の行を更新します。
        DataRow productRow = dataTable.Rows[0];
        productRow["UnitPrice"] = 550;
        productRow["ProductName"] = "Lorem Ipsum";

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

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

Delete Data

This example removes data by executing Delete command.

C#
コードのコピー 
static void DeleteDataAdapter()
{
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
    {
        // データテーブルにデータを入力します。
        C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Products");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        //Delete コマンドを作成します。
        adapter.DeleteCommand = new C1KintoneCommand(conn);
        adapter.DeleteCommand.CommandText = "Delete from Products where Name = @Name";
        adapter.DeleteCommand.Parameters.Add("@Name", "Name");
        adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

        //行を削除します。
        DataRow bookRow1 = dataTable.Rows[3];
        bookRow1["Name"] = "'Test Incremental Cache by Demen'";
        bookRow1.Delete();
        
        //データベースを更新します。
        var i = adapter.Update(dataTable);

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

 

 

Copyright © MESCIUS, Inc. All rights reserved.