DataConnector | ComponentOne
ADO.NET provider for OData / Querying Data
In This Topic
    Querying Data
    In This Topic

    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#
    Copy Code 
    using (var conn = new C1ODataConnection(ODataConnectionString))
{
    conn.Open();

    // Create Insert command
    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')");

    // Execute Insert command
    int i = command.ExecuteNonQuery();
    if (i != -1)
    {
        Console.WriteLine("Insert operation successful! \n\n");
    }
}

    Read Data

    This example retrieves data by executing the Select command.

    C#
    Copy Code 
    using (var con = new C1ODataConnection(ODataConnectionString))
{
    con.Open();

    // Create Read command
    var cmd = con.CreateCommand();
    cmd.CommandText = "SELECT * FROM Books LIMIT 10";

    // Execute Read command and display fetched data
    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 the Update command.

    C#
    Copy Code 
    using (var conn = new C1ODataConnection(ODataConnectionString))
{
    conn.Open();

    // Create Update command
    var command = new C1ODataCommand(conn, "UPDATE Books SET Title = @Title WHERE Id = @Id");
    command.Parameters.AddWithValue("@Id", 4);
    command.Parameters.AddWithValue("@Title", "Patience");

    // 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.

    C#
    Copy Code 
    using (var conn = new C1ODataConnection(ODataConnectionString))
{
    conn.Open();

    // Create Delete command
    var command = new C1ODataCommand(conn, "DELETE FROM books WHERE ID = @ID");
    command.Parameters.AddWithValue("@ID", 5);

    // Execute Delete command
    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#
    Copy Code 
    using (var conn = new C1ODataConnection(ODataConnectionString))
{
    // Populate DataTable
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

    // Create Insert command
    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;

    // Insert new row
    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);

    // Update database
    adapter.Update(dataTable);
}

    Read Data

    This examples retrieves data by executing the Select command.

    C#
    Copy Code 
    using (var con = new C1ODataConnection(ODataConnectionString))
{
    // Populate DataTable
    var conn = new C1ODataConnection(GCODataServerConnectionString);
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books LIMIT 10");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

    // Display fetched data
    foreach (DataRow row in dataTable.Rows)
    {
        Console.WriteLine("{0}\t{1}", row["Id"], row["Title"]);
    }
}

    Update Data

    This example modifies data by executing the Update command.

    C#
    Copy Code 
    using (var conn = new C1ODataConnection(ODataConnectionString))
{
    // Populate DataTable
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

    // Create Update command
    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;

    // Update existing row
    DataRow bookRow1 = dataTable.Rows[0];
    bookRow1["Id"] = 4;
    bookRow1["Title"] = "Update 2007";

    // Update database
    adapter.Update(dataTable);
}

    Delete Data

    This example removes data by executing the Delete command.

    C#
    Copy Code 
    using (var conn = new C1ODataConnection(ODataConnectionString))
{
    // Populate Datatable
    var adapter = new C1ODataDataAdapter(conn, "SELECT * FROM Books");
    var dataTable = new DataTable();
    adapter.Fill(dataTable);

    // Create Delete command
    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;

    // Delete a row
    DataRow bookRow1 = dataTable.Rows[3];
    bookRow1["Id"] = 6;
    bookRow1.Delete();

    // Update Database
    adapter.Update(dataTable);
}