DataConnector | ComponentOne
ADO.NET provider for Kintone / CRUD Operations
In This Topic
    CRUD Operations
    In This Topic

    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

    DbDataReader class can be used to fetch data in subset increments as required.

    The DbDataReader can fetch data quicker than the C1KintoneDataAdapter as it retrieves data in pages. When you read data from 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 DbDataReader.

     

    Create Data

    The example adds new records by executing the Insert command.

    C#
    Copy Code 
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
    conn.Open();
    //Create Insert command
    C1KintoneCommand command = new C1KintoneCommand(conn, $@"Insert into Products(Name) values('Test Incremental Cache by Demen')");
    //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#
    Copy Code 
    using (C1KintoneConnection con = new C1KintoneConnection(kintoneConnection))
{
    con.Open();
    //Create Read command
    var cmd = con.CreateCommand();
    cmd.CommandText = "Select  *  FROM Products'";
    //Execute Read command and display fetched data
    DbDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Console.WriteLine(String.Format("\t{0} --> \t\t{1}", rdr["Updated_By"], rdr["Name"]));
    }
    Console.WriteLine("Read operation successful !!! \n \n");

    Update Data

    This example modifies data by executing Update command.

    C#
    Copy Code 
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
    conn.Open();
    //Create Update command
    C1KintoneCommand command = new C1KintoneCommand(conn, "UPDATE Products SET Updated_By = @Updated_By WHERE Name = @Name");
    
    command.Parameters.AddWithValue("@Updated_By", "Guest1");
    command.Parameters.AddWithValue("@Name", "Test Product");
    //Execute Update command
    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#
    Copy Code 
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
    conn.Open();
    //Create Delete command
    C1KintoneCommand command = new C1KintoneCommand(conn, "Delete from Products where Name = @Name");
    command.Parameters.AddWithValue("@Name", "'Test Incremental Cache by Demen'");

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

    Querying With C1KintoneDataAdapter

    C1KintoneDataAdapter class can be used to retrieve a single result set of all the data that matches a query.

    DataAdapter uses its Fill method to fetch data from the data source. An empty DataTable instance is passed as an argument to the Fill method. When the method returns, the DataTable instance is populated with the queried data.

    The Fill method needs to retrieve all the data from data source before returning, and hence C1KintoneDataAdapter is slower than the DbDataReader.

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

     

    Create Data

    The example adds new records by executing the Insert command.

    C#
    Copy Code 
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
    //Populate DataTable
    C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Products");
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);

    //Create Insert command
    adapter.InsertCommand = new C1KintoneCommand(conn);
    adapter.InsertCommand.CommandText = "Insert into Products (Name, Quantity) values (@Name, @Quantity)";
    adapter.InsertCommand.Parameters.Add("@Quantity", "Quantity");
    adapter.InsertCommand.Parameters.Add("@Name", "Name");
    adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
    //Insert new row
    DataRow productRow1 = dataTable.NewRow();
    productRow1["Quantity"] = 100;
    productRow1["Name"] = "Product2";
    dataTable.Rows.Add(productRow1);

    //Update database
    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#
    Copy Code 
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
    //Populate 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");
        //Display fetched data
        foreach (DataRow row in dataTable.Rows)
        {
            Console.WriteLine("{0}\t{1}", row["Updated_By"], row["Name"]);
        }
    }

    Update Data

    This example modifies data by executing Update command.

    C#
    Copy Code 
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
    //Populate DataTable
    C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Products");
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);
    //Create Update command
    adapter.UpdateCommand = new C1KintoneCommand(conn);
    adapter.UpdateCommand.CommandText = "UPDATE Products SET Updated_By = @Updated_By WHERE Name = @Name";
    adapter.UpdateCommand.Parameters.Add("@Updated_By", "Updated_By");
    adapter.UpdateCommand.Parameters.Add("@Name", "Name");
    adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

    //Update existing row
    DataRow productRow1 = dataTable.Rows[0];
    productRow1["Updated_By"] = "Administrator";
    productRow1["Name"] = "Test Insert by Demen";

    //Update database
    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#
    Copy Code 
    using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
    //Populate Datatable
    C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Products");
    DataTable dataTable = new DataTable();
    adapter.Fill(dataTable);
    //Create Delete command
    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;

    //Delete a row
    DataRow bookRow1 = dataTable.Rows[3];
    bookRow1["Name"] = "'Test Incremental Cache by Demen'";
    bookRow1.Delete();
    
    //Update Database
    var i = adapter.Update(dataTable);

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

     

     

    Copyright © GrapeCity, Inc. All rights reserved.

    Product Support Forum | Print this page