Add ServiceNow to .NET Apps with Data Connectors & ADO.NET
Tags: webdev, dotnet, tutorial
Published: 2022-05-19 21:07:26

---
canonical_url: https://www.grapecity.com/blogs/add-servicenow-to-net-apps-with-data-connectors-and-ado-net
description: Learn how to add ServiceNow to .NET apps with data connectors & ADO.NET. See more from ComponentOne today.
---

The ComponentOne 2022v1 release enhances the number of datasources supported by **.NET Standard** service library **[DataConnectors](https://www.grapecity.com/componentone/net-data-connector-component)**, 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:

![NuGet](https://files.grapecity.com/gc-website-media/qypp4pm5/image1.png?width=500&height=184.26724137931035)

You can explore all types of DataConnector and their features using [Desktop DataConnector Explorer](https://demos.componentone.com/c1demos/C1StudioDemoExplorer.exe) and [Web-basedDataConnector Explorer](https://demos.componentone.com/DataConnectors/DataConnectorExplorer). In addition, you can download and install the ComponentOne DataConnectors service component from [here](https://www.grapecity.com/componentone/net-service-component) 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

TheADO.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:

![Fetched Data](https://files.grapecity.com/gc-website-media/hbujvwqv/image2.png?width=500&height=339.5744680851064)

### 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](https://www.grapecity.com/componentone/docs/services/online-dataconnector/incrementalcaching.html).

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](https://www.grapecity.com/componentone/docs/services/online-dataconnector/overview.html).