DataConnector
データのクエリ
ADO.NET provider for ServiceNow > データのクエリ

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.

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.

C#
コードのコピー 
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)')";

        // Insert コマンドを作成します。
        C1ServiceNowCommand command = new C1ServiceNowCommand(conn, insertSql);

        // Insert コマンドを実行します。
        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.

C#
コードのコピー 
static void ReadDataReader()
{
    using (C1ServiceNowConnection con = new C1ServiceNowConnection(connectionString))
    {
        con.Open();

        //Read コマンドを作成します。
        var cmd = con.CreateCommand();
        cmd.CommandText = "Select caller_id, category, description, close_code from incident";

        //Readコマンドを実行し、取得したデータを表示します。
        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 Update command.

C#
コードのコピー 
static void UpdateDataReader()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        conn.Open();

        //Update コマンドを作成します。
        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)");

        //Update コマンドを実行します。
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Update operation successful !!! \n \n");
        }
    }
}

Delete Data

This example removes data by executing Delete command.

C#
コードのコピー 
static void DeleteDataReader()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        conn.Open();

        //Delete コマンドを作成します。
        C1ServiceNowCommand command = new C1ServiceNowCommand(conn, "Delete from incident where caller_id = @caller_id");
        command.Parameters.AddWithValue("@caller_id", "781ccaf9c0a8016400b98a06818d57d8");

        //Delete コマンドを実行します。
        int i = command.ExecuteNonQuery();
        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
}

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.

C#
コードのコピー 
static void InsertDataAdapter()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //DataTable を設定します。
        C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, category, description, close_code from incident");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Insert コマンドを作成します。
        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;
        //新しい行を挿入します。
        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);

        // データベースを更新します。
        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.

C#
コードのコピー 
static void ReadDataAdapter()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //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)
        {
            //取得したデータを表示します。
            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 Update command.

C#
コードのコピー 
static void UpdateDataAdapter()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //DataTable を設定します。
        C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Update コマンドを作成します。
        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;

        //既存の行を更新します。

        DataRow incidentRow = dataTable.Rows[0];
        incidentRow["close_code"] = "Solved Remotely (Permanently)";

        // データベースを更新します。
        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.

C#
コードのコピー 
static void DeleteDataAdapter()
{
    using (C1ServiceNowConnection conn = new C1ServiceNowConnection(connectionString))
    {
        //DataTable を設定します。
        C1ServiceNowDataAdapter adapter = new C1ServiceNowDataAdapter(conn, "Select caller_id, close_code from incident");
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);

        //Delete コマンドを作成します。
        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;

        //行を削除します。

        DataRow productRow = dataTable.Rows[10];
        productRow.Delete();

        // データベースを更新します。
        var i = adapter.Update(dataTable);

        if (i != -1)
        {
            Console.WriteLine("Delete operation successful !!! \n \n");
        }
    }
}

 

 

Copyright © MESCIUS, Inc. All rights reserved.