Add ServiceNow to .NET Apps with Data Connectors & ADO.NET
The ComponentOne 2022v1 release enhances the number of datasources supported by .NET Standard service library DataConnectors, which provides a set of connectors that can connect to different types of data sources. Now, you can connect to eight data sources, namely OData, Microsoft Dynamics 365 Sales, Salesforce Data, Kintone, QuickBooks Online, Google Analytics, JSON, and ServiceNow, 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 the 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 datasource, 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 ServiceNow to connect to the ServiceNow data and even explain the basic features, including authentication, querying, and caching. To get started, you would need to install the C1.AdoNet.ServiceNow package from NuGet:
You can explore all types of DataConnector and their features using Desktop DataConnector Explorer and Web-basedDataConnector Explorer. In addition, you can 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'
Connection
The first step toward working with the provider is establishing a connection to the ServiceNow data. The DataConnector service library provides you with the C1ServiceNowConnection class, just like the ADO.NET DataConnection class. It is used to establish 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 C1ServiceNowConnectionStringBuilder class.
Here is a sample code snippet depicting how to generate a connection string and create a connection object:
//Create connection string using C1ServiceNowConnectionStringBuilder
C1ServiceNowConnectionStringBuilder connBuilder = new C1ServiceNowConnectionStringBuilder();
connBuilder.Username = username;
connBuilder.Password = password;
connBuilder.OAuthClientId = oAuthCientId;
connBuilder.OAuthClientSecret = oAuthClientSecret;
connBuilder.OAuthTokenEndpoint = "https://dev63961.service-now.com/oauth_token.do";
connBuilder.Url = "https://dev63961.service-now.com/";
//Create and establish connection
C1ServiceNowConnection conn = new C1ServiceNowConnection(connBuilder);
conn.Open();
Console.WriteLine("Connection Estabished and opened !!!");
Data Operations
The ADO.NET provider for ServiceNow 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 mode or disconnected mode by using the C1ServiceNowCommand and C1ServiceNowDataAdapter objects. The sample code below depicts the basic CRUD operations using the C1ServiceNowDataAdapter object:
//READ QUERY
using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
{
//Populate DataTable
C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, category, description, close_code from incident");
//Fill DataTable
DataTable dataTable = new DataTable();
var i = adapter.Fill(dataTable);
if (i != -1)
{
//Display fetched data
foreach (DataRow row in dataTable.Rows)
{
Console.WriteLine("CallerId:{0}", row["caller_id"]);
Console.WriteLine("Category:{0}", row["category"]);
Console.WriteLine("Description:{0}", row["description"]);
Console.WriteLine("CloseCode:{0}", row["close_code"]);
Console.WriteLine("\n");
}
Console.WriteLine("Read operation successful !!! \n \n");
}
}
//INSERT QUERY
using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
{
//Populate DataTable
C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, category, description, close_code from incident");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Insert command
adapter.InsertCommand = new C1ServiceNowCommand(conn);
adapter.InsertCommand.CommandText = "Insert into incident(caller_id, category, description, close_code) Values(@caller_id,@category,@description,@close_code)";
adapter.InsertCommand.Parameters.Add("@caller_id", "caller_id");
adapter.InsertCommand.Parameters.Add("@category", "category");
adapter.InsertCommand.Parameters.Add("@description", "description");
adapter.InsertCommand.Parameters.Add("@close_code", "close_code");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
//Insert new row
DataRow productRow = dataTable.NewRow();
productRow["caller_id"] = "781ccaf9c0a8016400b98a06818d57d8";
productRow["category"] = "inquiry";
productRow["description"] = "I am unable to connect to the data server. It appears to be down.";
productRow["close_code"] = "Solved (Permanently)";
dataTable.Rows.Add(productRow);
//Update database
adapter.Update(dataTable);
}
//UPDATE QUERY
using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
{
//Populate DataTable
C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Update command
adapter.UpdateCommand = new C1ServiceNowCommand(conn);
adapter.UpdateCommand.CommandText = "UPDATE incident SET close_code=@close_code where caller_id=@caller_id";
adapter.UpdateCommand.Parameters.Add("@caller_id", "caller_id");
adapter.UpdateCommand.Parameters.Add("@close_code", "close_code");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
//Update existing row
DataRow incidentRow = dataTable.Rows[0];
incidentRow["close_code"] = "Solved Remotely (Permanently)";
//Update database
adapter.Update(dataTable);
}
//DELETE QUERY
using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
{
//Populate Datatable
C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Delete command
adapter.DeleteCommand = new C1ServiceNowCommand(conn);
adapter.DeleteCommand.CommandText = "Delete from incident where caller_id = @caller_id";
adapter.DeleteCommand.Parameters.Add("@caller_id", "caller_id");
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;
//Delete a row
DataRow productRow = dataTable.Rows[10];
productRow.Delete();
//Update Database
adapter.Update(dataTable);
}
Here is a quick example for fetching the data from ServiceNow using C1 ServiceNow ADO.NET Provider and binding it to FlexGrid in a Winforms application.
Here is the sample code snippet implementing the same, assuming FlexGrid control has already been added to the Windows Form using designer:
private void Form1_Load(object sender, EventArgs e)
{
using (C1ServiceNowConnection conn = new C1ServiceNowConnection(GCServiceNowConnectionString))
{
//Populate DataTable
C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, category, description, close_code from incident");
//Fill DataTable
DataTable dataTable = new DataTable();
var i = adapter.Fill(dataTable);
if (i != -1)
{
//Bind fetched data to FlexGrid
c1FlexGrid1.DataSource = dataTable;
//AutoSize columns to display data
c1FlexGrid1.AutoSizeCols();
//Set backcolor for the alternate row
c1FlexGrid1.Styles["Alternate"].BackColor = Color.LightGray;
}
}
}
Here is a glimpse of FlexGrid after loading the fetched data:
Batch Update
The ADO.NET provider for ServiceNow lets you group INSERT, UPDATE, and DELETE operations from a DataSet or DataTable and hence supports batch processing. The batch updates enhance the overall performance of data access and data update operation 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 batches of the specified size and sent to the server for processing.
Here is a sample code snippet depicting how to set the UpdateBatchSize property and perform the batch update:
using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
{
//Populate DataTable
C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident");
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
//Create Update command
adapter.UpdateCommand = new C1ServiceNowCommand(conn);
adapter.UpdateCommand.CommandText = "UPDATE incident SET close_code=@close_code where caller_id=@caller_id";
adapter.UpdateCommand.Parameters.Add("@caller_id", "caller_id");
adapter.UpdateCommand.Parameters.Add("@close_code", "close_code");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
//Update existing row
DataRow incidentRow1 = dataTable.Rows[0];
incidentRow1["close_code"] = "Solved Remotely (Permanently)";
//Update existing row
DataRow incidentRow2 = dataTable.Rows[1];
incidentRow2["close_code"] = "Solved Remotely (Permanently)";
//Set batch size to be updated
adapter.UpdateBatchSize = 2;
//Update database
adapter.Update(dataTable);
}
Caching
The performance of the ADO.NET provider for ServiceNow 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 in-built caching support. We would need to configure the connection object to set the cache properties and enable cache for a connection. The UseCache property must be set to true, while 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 that depicts how to enable caching by setting all the cache properties in the connection object:
//Create connection string using C1ServiceNowConnectionStringBuilder
C1ServiceNowConnectionStringBuilder connBuilder = new C1ServiceNowConnectionStringBuilder();
connBuilder.Username = username;
connBuilder.Password = password;
connBuilder.OAuthClientId = oAuthClientId;
connBuilder.OAuthClientSecret = oAuthClientSecret;
connBuilder.OAuthTokenEndpoint = "https://dev63961.service-now.com/oauth_token.do";
connBuilder.Url = "https://dev63961.service-now.com/";
connBuilder.UseCache = true;
connBuilder.CacheTolerance = 500;
connBuilder.CacheLocation = @"C:\temp\sn_cache.db";
Console.WriteLine("Start Time " + DateTime.Now);
using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connBuilder.ConnectionString))
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * from incident";
var rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine(String.Format("\t{0} --> \t\t{1}-->\t\t\t{2}", rdr["caller_id"], rdr["category"], rdr["description"]));
}
Console.WriteLine("End Time " + DateTime.Now);
}
Incremental Caching
The ADO.NET provider for ServiceNow 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 that 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; ServiceNow has a default timestamp column "sys_updated_on." The "IncrementalUpdate" and "IncrementalUpdateColumn" properties of C1ServiceNowConnectionStringBuilder class must be set to enable Incremental caching. For more details, refer to the documentation topic for Incremental caching.
The steps above have demonstrated how easy it is to connect to a new data source if you have a common interface based on a 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.
Related Blogs
Try Our .NET UI Components & Datagrid
Getting Started With ComponentOne
Manpreet Kaur