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

The ADO.NET Provider for OData 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 create, read, update, and delete operations from the data source using the DbDataReader.

 

Create Data

The example adds new records by executing the Insert command.

C#
コードのコピー 
using (var conn = new C1ODataConnection(ODataConnectionString))
{
    conn.Open();

    // Insert コマンドを作成します。
    var command = new C1ODataCommand(conn,
        "INSERT INTO books (ISBN, Title, Author, Price, Location_City, Location_Street)
        VALUES ('306-0-355-48016-0', 'Php Expert', 'Charles', 500, 'Delhi', 'MT')");

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

Read Data

This examples retrieves data by executing the Select command.

C#
コードのコピー 
using (var con = new C1ODataConnection(ODataConnectionString))
{
    con.Open();

    // Read コマンドを作成します。
    var cmd = con.CreateCommand();
    cmd.CommandText = "SELECT * FROM Books LIMIT 10";

    // Readコマンドを実行し、取得したデータを表示します。
    DbDataReader rdr = cmd.ExecuteReader();
    while (rdr.Read())
    {
        Console.WriteLine(String.Format("\t{0} --> \t\t{1}", rdr["Id"], rdr["Title"]));
    }
    Console.WriteLine("Read operation successful! \n\n");
}

Update Data

This example modifies data by executing Update command.

C#
コードのコピー 
using (var conn = new C1ODataConnection(ODataConnectionString))
{
    conn.Open();

    // Update コマンドを作成します。
    var command = new C1ODataCommand(conn, "UPDATE Books SET Title = @Title WHERE Id = @Id");
    command.Parameters.AddWithValue("@Id", 4);
    command.Parameters.AddWithValue("@Title", "Patience");

    // 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#
コードのコピー 
using (var conn = new C1ODataConnection(ODataConnectionString))
{
    conn.Open();

    // Delete コマンドを作成します。
    var command = new C1ODataCommand(conn, "DELETE FROM books WHERE ID = @ID");
    command.Parameters.AddWithValue("@ID", 5);

    // 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#
コードのコピー 
using (var conn = new C1ODataConnection(ODataConnectionString))
{
    // データテーブルにデータを入力します。
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

    // Insert コマンドを作成します。
    adapter.InsertCommand = new C1ODataCommand(conn);
    adapter.InsertCommand.CommandText = "
        INSERT INTO books (ISBN, Title, Author, Price, Location_City, Location_Street)
        VALUES (@ISBN, @Title, @Author, @Price, @Location_City, @Location_City)";

    adapter.InsertCommand.Parameters.Add("@Title", "Title");
    adapter.InsertCommand.Parameters.Add("@ISBN", "ISBN");
    adapter.InsertCommand.Parameters.Add("@Author", "Author");
    adapter.InsertCommand.Parameters.Add("@Price", "Price");
    adapter.InsertCommand.Parameters.Add("@Location_City", "Location_City");
    adapter.InsertCommand.Parameters.Add("@Location_Street", "Location_City");
    adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

    // 新しい行を挿入します。
    DataRow bookRow1 = dataTable.NewRow();
    bookRow1["Title"] = "Tama 2007";
    bookRow1["ISBN"] = "306-0-355-48016-0";
    bookRow1["Author"] = "CK";
    bookRow1["Price"] = 200.20;
    bookRow1["Location_City"] = "Delhi";
    bookRow1["Location_Street"] = "MT";
    dataTable.Rows.Add(bookRow1);

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

Read Data

This examples retrieves data by executing the Select command.

C#
コードのコピー 
using (var con = new C1ODataConnection(ODataConnectionString))
{
    // データテーブルにデータを入力します。
    var conn = new C1ODataConnection(GCODataServerConnectionString);
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books LIMIT 10");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

    // 取得したデータを表示します。
    foreach (DataRow row in dataTable.Rows)
    {
        Console.WriteLine("{0}\t{1}", row["Id"], row["Title"]);
    }
}

Update Data

This example modifies data by executing Update command.

C#
コードのコピー 
using (var conn = new C1ODataConnection(ODataConnectionString))
{
    // データテーブルにデータを入力します。
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

    // Updateコマンドを作成します。
    adapter.UpdateCommand = new C1ODataCommand(conn);
    adapter.UpdateCommand.CommandText = "UPDATE Books SET Title = @Title WHERE id = @Id";
    adapter.UpdateCommand.Parameters.Add("@Title", "Title");
    adapter.UpdateCommand.Parameters.Add("@Id", "Id");
    adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

    // 既存の行を更新します。
    DataRow bookRow1 = dataTable.Rows[0];
    bookRow1["Id"] = 4;
    bookRow1["Title"] = "Update 2007";

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

Delete Data

This example removes data by executing Delete command.

C#
コードのコピー 
using (var conn = new C1ODataConnection(ODataConnectionString))
{
    // データテーブルにデータを入力します。
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

    // Delete コマンドを作成します。
    adapter.DeleteCommand = new C1ODataCommand(conn);
    adapter.DeleteCommand.CommandText = "DELETE FROM Books WHERE id = @Id";
    adapter.DeleteCommand.Parameters.Add("@Id", "Id");
    adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

    // 行を削除します。
    DataRow bookRow1 = dataTable.Rows[3];
    bookRow1["Id"] = 6;
    bookRow1.Delete();

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

 

 

Copyright © MESCIUS, Inc. All rights reserved.