Querying Data

The ADO.NET Provider for ServiceNow implements two classes you can use to perform CRUD (Create, Read, Update, and Delete) operations: DbDataReader and DbDataAdapter classes. The tabs below describe the interfaces and code implementations.

Data Reader

Querying With DbDataReader

The DbDataReader class can be used to fetch data in subset increments as required. It can retrieve data quicker than the DbDataAdapter as it retrieves data in pages. When you read data from the 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 read, create, update, and delete operations from the data source using the DbDataReader.

Create Data

The example adds new records by executing the Insert command.

static void InsertDataReader()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        conn.Open();
        string insertSql = @"Insert into incident(caller_id, category, description, close_code) 
    values('781ccaf9c0a8016400b98a06818d57d8','inquiry','I am unable to connect to the data server. It appears to be down.','Solved (Permanently)')";
        //Create Insert command;
        C1ServiceNowCommand command = new C1ServiceNowCommand(conn, insertSql);
        // Execute Insert command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Insert operation successful !!! \n \n");
        }
    }
}

Read Data

The example retrieves data from ServiceNow by executing the Select command.

static void ReadDataReader()
{
    using (C1ServiceNowConnection con = new C1ServiceNowConnection(connectionString))
    {
        con.Open();
        //Create Read command
        var cmd = con.CreateCommand();
        cmd.CommandText = "Select caller_id, category, description, close_code from incident";
        //Execute Read command and display fetched data
        var rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Console.WriteLine("CallerId:{0}", rdr["caller_id"]);
            Console.WriteLine("Category:{0}", rdr["category"]);
            Console.WriteLine("Description:{0}", rdr["description"]);
            Console.WriteLine("CloseCode:{0}", rdr["close_code"]);
            Console.WriteLine("\n");
        }
        Console.WriteLine("Read operation successful !!! \n \n");
    }
}

Update Data

This example modifies data by executing the Update command.

static void UpdateDataReader()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        conn.Open();
        //Create Update command               
        C1ServiceNowCommand command = new C1ServiceNowCommand(conn, "UPDATE incident SET close_code=@close_code where caller_id=@caller_id");
        command.Parameters.AddWithValue("@caller_id", "681ccaf9c0a8016400b98a06818d57c7");
        command.Parameters.AddWithValue("@close_code", "Solved Remotely (Permanently)");
        //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 the Delete command.

static void DeleteDataReader()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        conn.Open();
        //Create Delete command
        C1ServiceNowCommand command = new C1ServiceNowCommand(conn, "Delete from incident where caller_id = @caller_id");
        command.Parameters.AddWithValue("@caller_id", "781ccaf9c0a8016400b98a06818d57d8");
        //Execute Delete command
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
}

Data Adapter

Querying With DbDataAdapter

The DbDataAdapter class can be used to retrieve a single result set containing all the data that matches a given query. The DbDataAdapter uses its Fill method to fetch data from the data source. An empty DataTable instance is passed as an argument to the Fill method. Once the method returns, the DataTable instance is populated with the queried data. Since the Fill method must retrieve all the data from the data source before returning, the DbDataAdapter is slower compared to the DbDataReader.

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

Create Data

The example adds new records by executing the Insert command.

static void InsertDataAdapter()
{
    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
        var i = adapter.Update(dataTable);
        if (i != -1)
        {
            Console.WriteLine("Insert operation successful !!! \n \n");
        }
    }
}

Read Data

This example retrieves data by executing the Select command.

static void ReadDataAdapter()
{
    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();
        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");
        }
    }
}

Update Data

This example modifies data by executing the Update command.

static void UpdateDataAdapter()
{
    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
        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.

static void DeleteDataAdapter()
{
    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
        var i = adapter.Update(dataTable);
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
}