Integrating .NET Applications with Kintone Using Entity Framework Core
Kintone is a platform for creating enterprise workflows for data-driven results. The platform exposes REST APIs for its entities for use by external applications. These APIs support the retrieval, update, and deletion of app records. Developers accessing the APIs need to handle the API calls and manually map application contracts with Kintone entities and all coding for CRUD operations.
.NET developers extensively use ADO. NET and Entity Framework for data access. They feel at home when performing data operations using these technologies on any data source. To enable developers to use their existing skills on these internet data sources, ComponentOne has introduced a data connector that allows data access to Kintone based on these .NET technologies.
Data Connector Features
The library supports .NET Standard so that it can be used in .NET Framework 4.7.2 above or any .NET Core application. The data connector has a host of features that make CRUD operations easy to perform in any .NET application:
- ADO. NET
- SQL
- Open Auth
- Entity Framework Core
- LINQ
- Intelligent Caching
- Scaffolding
Getting Started with the Kintone Data Connector
Let's walk through an application that can directly talk to the Kintone database and perform all CRUD functionality using Entity Framework Core.
Authentication Prerequisites for Kintone
Each REST API call to Kintone needs an authentication header. Kintone provides two types of authentication mechanisms:
-
Password: Password authentication allows the use of REST APIs with user-level permission, meaning that the authenticated user will need to have permission to control the app or space for the API to succeed.
-
API Token: Alternatively, each app in Kintone can create API tokens that will work specifically with that app. To know more about how to generate API tokens, please refer to Kintone documentation.
Once we have the relevant authentication details in hand, we can move forward to use them in external applications.
Setting Up the .NET Application
In this tutorial, we will create a WinForms application targeting .NET Framework 4.7.2. After completing the project, we will add the C1.EntityFrameworkCore.Kintone package from NuGet.
Create the Model to Use Entity Framework
To use Entity Framework, we need to create the DbContext class and model class representing the entity in the Kintone database. This action can be done manually in code. Fortunately, the Kintone DataConnector comes with built-in scaffolding support that generates these classes for you using the provided connection information. The command is similar to Entity Framework Core, i.e., using the Scaffold-DbContext command. You can learn more about scaffolding here.
How to Use Scaffolding
- To use scaffolding first add the Microsoft.EntityFrameworkCore.Tools (version 2.1.0) from Nuget.
- Next, go to View -> Other Windows -> Package Manager Console from Visual Studio.
- Run the scaffolding command line with the following syntax to generate a single table from the data source.
Scaffold-DbContext "Url=https://xx888.kintone.com;Username=user;Password=password" C1.EntityFrameworkCore.Kintone -OutputDir "Models" -Context "KintoneContext" -Tables Invoices
Here is the generated code:
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
namespace WindowsFormsApp1.GeneratedCode
{
public partial class KintoneContext : DbContext
{
public KintoneContext()
{
Database.AutoTransactionsEnabled = false;
}
public KintoneContext(DbContextOptions<KintoneContext> options)
: base(options)
{
Database.AutoTransactionsEnabled = false;
}
public virtual DbSet<Invoices> Invoices { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
optionsBuilder.UseKintone("Url=https://xx888.kintone.com;Username=user;Password=password");
}
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Invoices>(entity =>
{
entity.Property(e => e.Id).HasColumnName("$id");
entity.Property(e => e.Assignee).ValueGeneratedOnAddOrUpdate();
entity.Property(e => e.Categories).ValueGeneratedOnAddOrUpdate();
entity.Property(e => e.CreatedBy)
.HasColumnName("Created_by")
.ValueGeneratedOnAddOrUpdate();
entity.Property(e => e.CreatedDatetime)
.HasColumnName("Created_datetime")
.ValueGeneratedOnAddOrUpdate();
entity.Property(e => e.CustomerId).HasColumnName("CustomerID");
entity.Property(e => e.OrderId).HasColumnName("OrderID");
entity.Property(e => e.ProductId).HasColumnName("ProductID");
entity.Property(e => e.RecordNumber)
.HasColumnName("Record_number")
.ValueGeneratedOnAddOrUpdate();
entity.Property(e => e.ShipperName).IsRequired();
entity.Property(e => e.Status).ValueGeneratedOnAddOrUpdate();
entity.Property(e => e.UpdatedBy)
.HasColumnName("Updated_by")
.ValueGeneratedOnAddOrUpdate();
entity.Property(e => e.UpdatedDatetime)
.HasColumnName("Updated_datetime")
.ValueGeneratedOnAddOrUpdate();
});
}
}
}
Best practices suggest that connection information should be moved out of code files and kept in configuration.
The generated Invoice Model Class is below:
public partial class Invoices
{
public int Id { get; set; }
public string ShipName { get; set; }
public double? Discount { get; set; }
public string Address { get; set; }
public string ProductName { get; set; }
public DateTime? ShippedDate { get; set; }
public string PostalCode { get; set; }
public string CustomerId { get; set; }
public double? OrderId { get; set; }
public string UpdatedBy { get; set; }
public DateTime? CreatedDatetime { get; set; }
public double? ExtendedPrice { get; set; }
public int? RecordNumber { get; set; }
public string ShipAddress { get; set; }
public string CreatedBy { get; set; }
public string CustomerName { get; set; }
public double? Freight { get; set; }
public string Status { get; set; }
public string Assignee { get; set; }
public DateTime? RequiredDate { get; set; }
public string ShipperName { get; set; }
public string Categories { get; set; }
public string ShipCity { get; set; }
public double? Quantity { get; set; }
public double? ProductId { get; set; }
public string ShipPostalCode { get; set; }
public string City { get; set; }
public string ShipRegion { get; set; }
public DateTime? OrderDate { get; set; }
public DateTime? UpdatedDatetime { get; set; }
public double? UnitPrice { get; set; }
public string Region { get; set; }
public string Country { get; set; }
public string ShipCountry { get; set; }
public string Salesperson { get; set; }
}
How to Fetch the Data
In the form constructor, initialize an instance of KintoneContext:
KintoneContext context;
public Form1()
{
InitializeComponent();
Load += Form1_Load;
context = new KintoneContext();
}
Next, in the form designer, drop a C1FlexGrid control. You could use a DataGridView; however, for demonstration purposes, this example will show various other features that help analyze data from Kintone. Set the following properties of FlexGrid as shown:
Name
- AllowAddNew:true
- AllowDelete:true
- AllowFiltering:true
- Name:fg
Next, complete the following steps to design the form.
- Add a C1FlexgridSearchPanel above FlexGrid. Set FlexGrid's C1FlexGridSearchPanel property to the name of the previous C1FlexgridSearchPanel control dropped.
- Add a GroupBox on the form and add two radio buttons to the group box. Set the first radio button's text property to "Country" and the second to "City."
- Add a button on the form, set its text property to save.
The designed form is below:
In the Form load event, add the below code to bind FlexGrid to the Invoice's data:
context.Invoices.Load();
this.fg.DataSource = context.Invoices.Local.ToBindingList();
The above code takes care of change tracking. It tracks updated, inserted, and deleted changes performed by the user.
To push the changes back to Kintone, we need to call the SaveChanges function of KintoneContext. We can call it on form closing or in the Save button we just added:
private void btnSave_Click(object sender, EventArgs e)
{
try
{
context.SaveChanges();
}
catch (Exception ex)
{
MessageBox.Show("There was a problem saving the changes");
//log the error
}
}
It would be nice if we could view the data grouped by Country or City. This view is easy to configure with FlexGrid. In the CheckedChange method of Country and City radio buttons, add the following code:
private void radioCountry_CheckedChanged(object sender, EventArgs e)
{
if (fg.GroupDescriptions != null)
fg.GroupDescriptions = null;
this.fg.GroupDescriptions = new C1.Win.C1FlexGrid.GroupDescription[] { new C1.Win.C1FlexGrid.GroupDescription("Country") };
}
private void radioCity_CheckedChanged(object sender, EventArgs e)
{
if(fg.GroupDescriptions!=null)
fg.GroupDescriptions = null;
this.fg.GroupDescriptions = new C1.Win.C1FlexGrid.GroupDescription[] { new C1.Win.C1FlexGrid.GroupDescription("City") };
}
The application is ready, run the app and perform all data specific changes like updating a record, deleting a row, and inserting a row using New Row. Group the data by Country or City. Perform a full-text search using the search panel. You can even filter individual columns using the column filter.
Conclusion: Moving Forward with .NET Apps and Kintone
As you may have noticed, there is no learning curve when using this data connector. Moreover, it has a useful tool that helps the scaffolding code. The data connector comes with an intelligent caching mechanism that can be configured in connection. This caching mechanism improves performance by removing the need for frequent roundtrips to Kintone. You can learn more about caching here and here.
The Kintone DataConnector comes with various samples for different platforms. Check out this online app that integrates the data connector with an MVC pivot table.
To see all available data connectors in action, download the C1DemoExplorer and run the WinForms DataConnectorExplorer sample.
Prabhakar Mishra