Integrate Kintone Data Using ADO.NET
The ComponentOne 2020v2 release enhances the number of data sources supported by .NET Standard service library DataConnectors, which provides a set of connectors that can connect to different types of data sources. You can now connect to four data sources, namely OData, Microsoft Dynamics 365 Sales, Salesforce Data and Kintone using an ADO. NET provider for each type. These providers have the same ADO. NET architecture as the native .NET data providers for SQL Server and OLEDB. Hence, they ease the complexity of accessing data by letting you follow a similar set of classes available in the .NET architecture. These providers also have advantageous features such as authentication, caching, and SQL/LINQ query support. To add up to these features, the DataConnector also includes an Entity Framework (EF) Core provider for each type of data source, which makes this library useful even when working with Entity Framework Core.
This blog will help you understand how to use the ADO. NET provider for Kintone to connect to the Kintone data and explain the basic features, including authentication, querying, and caching.
To get started, you would need to install the C1.DataConnector and C1.AdoNet.Kintone packages from NuGet:
You can also download and install the ComponentOne DataConnectors service component from here and find the installed samples at the following location: 'C:\Users\xxxx\Documents\ComponentOne Samples\ServiceComponents\DataConnector.'
Establishing the Connection to the Kintone Data
The first step towards working with the provider is establishing a connection to the Kintone data. The DataConnector service library provides you with the C1KintoneConnection class, just like the ADO. NET DataConnection class. It creates a connection to the data by creating the connection object and passing the connection string as a parameter to the class constructor. The connection string can either be predefined, or it can be generated using the C1KintoneConnectionStringBuilder class.
Here is a sample code snippet depicting how to generate a connection string and create a connection object:
//Configure Connection string
C1KintoneConnectionStringBuilder builder = new C1KintoneConnectionStringBuilder();
builder.Url = Url;
builder.Username = Username;
builder.Password = Password;
//Setup Connection
C1KintoneConnection conn = new C1KintoneConnection(builder.ConnectionString);
How to Query Against the Data Source
The ADO. NET provider for Kintone Data lets you query against the data source using standard SQL syntax. The provider supports most SQL functionalities, including join queries, functions, and summaries. The basic CRUD operations, such as insertion, updating, and deleting data, can be performed either in the connected or disconnected mode by using the C1KintoneCommand and C1KintoneDataAdapter objects.
The sample code below depicts the basic CRUD operations using the C1KintoneDataAdapter object:
-------------------------------------------------------------------------------------------------------------------
//READ QUERY
using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
//Populate DataTable
C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Customers");
DataTable dataTable = new DataTable();
var i = adapter.Fill(dataTable);
if (i != -1)
{
//Display fetched data
foreach (DataRow row in dataTable.Rows)
{
Console.WriteLine("{0}\t{1}", row["CustomerID"], row["CompanyName"]);
}
}
} -------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
//INSERT QUERY
using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
//Populate DataTable
C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Customers");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Insert command
adapter.InsertCommand = new C1KintoneCommand(conn);
adapter.InsertCommand.CommandText = "Insert into Customers (CustomerID, CompanyName) values (@CustomerID @CompanyName)";
adapter.InsertCommand.Parameters.Add("@CustomerID", "CustomerID");
adapter.InsertCommand.Parameters.Add("@CompanyName", "CompanyName");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
//Insert new row
DataRow customerRow = dataTable.NewRow();
customerRow["CustomerID"] = "YZBA";
customerRow["CompanyName"] = "Yuvzebcas";
dataTable.Rows.Add(customerRow);
//Update database
adapter.Update(dataTable);
}
-------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
//UPDATE QUERY
using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
//Populate DataTable
C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Customers");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Update command
adapter.UpdateCommand = new C1KintoneCommand(conn);
adapter.UpdateCommand.CommandText = "UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @CustomerID";
adapter.UpdateCommand.Parameters.Add("@CompanyName", "CompanyName");
adapter.UpdateCommand.Parameters.Add("@CustomerID", "CustomerID");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
//Update existing row
DataRow customerRow = dataTable.Rows[0];
customerRow["CompanyName"] = "Basdfguyt";
//Update database
adapter.Update(dataTable);
}
-------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------ //DELETE QUERY
using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
//Populate Datatable
C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Customers");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Delete command
adapter.DeleteCommand = new C1KintoneCommand(conn);
adapter.DeleteCommand.CommandText = "Delete from Customers where CompanyName = @CompanyName";
adapter.DeleteCommand.Parameters.Add("@CompanyName", "CompanyName");
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
//Delete a row
DataRow customerRow = dataTable.Rows[0];
customerRow.Delete();
//Update Database
adapter.Update(dataTable);
}
-------------------------------------------------------------------------------------------------------------------
Enhancing Data Access and More with Batch Update
The ADO. NET provider for Kintone lets you group INSERT, UPDATE, and DELETE operations from a DataSet or DataTable, supporting batch processing. The batch updates enhance data access and data update operation's overall performance by preventing multiple requests to the server. The UpdateBatchSize property must be set to an appropriate value to ensure that the commands are grouped in the specified size batches and sent to the server for processing.
Here is a sample code snippet depicting how to set the UpdateBatchSize property and perform a batch update:
//Delete Query
using (C1KintoneConnection conn = new C1KintoneConnection(kintoneConnection))
{
//Populate Datatable
C1KintoneDataAdapter adapter = new C1KintoneDataAdapter(conn, "Select * from Customers");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Update command
adapter.UpdateCommand = new C1KintoneCommand(conn);
adapter.UpdateCommand.CommandText = "UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @CustomerID";
adapter.UpdateCommand.Parameters.Add("@CompanyName", "CompanyName");
adapter.UpdateCommand.Parameters.Add("@CustomerID", "CustomerID");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
//Update existing row
DataRow customerRow1 = dataTable.Rows[0];
customerRow1["CompanyName"] = "Wolski Zajazd";
DataRow customerRow2 = dataTable.Rows[1];
customerRow2["CompanyName"] = "Wilman Kala";
//Set batch size to be updated
adapter.UpdateBatchSize = 2;
//Update database
adapter.Update(dataTable);
}
Further Performance Enhancements from the Cache
The performance of the ADO. NET provider for Kintone can further be enhanced by accessing the data from the cache when the user performs similar operations repeatedly. To support this type of functionality, the DataConnector library provides built-in caching support. We would need to configure the connection object to set the cache properties and enable the cache for a connection. The UseCache property must be set to true, but you can customize the default cache location by setting the CacheLocation property. The CacheTolerance property is used to set the cache tolerance time in seconds, with the default value set to 600 seconds.
Here is a sample code snippet which depicts how to enable caching by setting all the cache properties in the connection object:
//Configure Connection string
C1KintoneConnectionStringBuilder builder = new C1KintoneConnectionStringBuilder();
builder.Url = Url;
builder.Username = Username;
builder.Password = Password;
builder.UseCache = true;
builder.CacheTolerance = 500;
builder.CacheLocation = @"C:\temp\c1cache.db";
using (C1KintoneConnection conn = new C1KintoneConnection(builder.ConnectionString))
{
Console.WriteLine("Start Time" + DateTime.Now);
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "Select * from Customers";
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(String.Format("{0}\t{1}\t{2}", rdr["CustomerID"], rdr["CompanyName"], rdr["Phone"]));
}
Console.WriteLine("End Time " + DateTime.Now);
}
Getting Started with Incremental Caching
The ADO. NET provider for Kintone provides another caching strategy known as Incremental Caching, which updates the cache data periodically to add new records from the source entities without refreshing the complete cache data. This technique relies on a unique field and a time-stamp field, which records the last time the record was updated or a new record was inserted. Hence, any table which needs to implement incremental caching must have a Timestamp column (Kintone has a default timestamp column "_Updated_datetime_"). The "IncrementalUpdate" and "IncrementalUpdateColumn" properties of C1KintoneConnectionStringBuilder class must be set to enable Incremental caching. For more details, refer to the documentation topic for incremental caching.
Conclusion: Moving Forward with ADO. NET
The steps above have demonstrated how easy it is to connect to a new type of data source if you have a standard interface based on known data access technology such as ADO. NET. So, let's get our hands on something new by using something old. For more detailed information, kindly refer to the DataConnector documentation.
Manpreet Kaur