DataConnector
ADO.NET provider for OData / CRUD Operations
In This Topic
    CRUD Operations
    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 C1ODataDataAdapter classes. The tabs below describe the interfaces and code implementations.

    Querying With DbDataReader

    DbDataReader class can be used to fetch data in subset increments as required.

    The DbDataReader can fetch data quicker than the C1ODataDataAdapter as it retrieves data in pages. When you read data from 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 DbDataReader.

     

    Create Data

    The example adds new records by executing the Insert command.

    C#
    Copy Code
    using (C1ODataConnection conn = new C1ODataConnection(ODataConnectionString))
    {
       conn.Open();
       //Create Insert command
       C1ODataCommand 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 examples retrieves data by executing the Select command.

    C#
    Copy Code
    using (C1ODataConnection 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 Update command.

    C#
    Copy Code
    using (C1ODataConnection conn = new C1ODataConnection(ODataConnectionString))
    {
        conn.Open();
        //Create Update command
        C1ODataCommand 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 Delete command.

    C#
    Copy Code
    using (C1ODataConnection conn = new C1ODataConnection(ODataConnectionString))
    {
         conn.Open();
         //Create Delete command
         C1ODataCommand 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 C1ODataDataAdapter

    C1ODataDataAdapter class can be used to retrieve a single result set of all the data that matches a query.

    DataAdapter uses its Fill method to fetch data from the data source. An empty DataTable instance is passed as an argument to the Fill method. When the method returns, the DataTable instance is populated with the queried data.

    The Fill method needs to retrieve all the data from data source before returning, and hence C1ODataDataAdapter is slower than the DbDataReader.

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

     

    Create Data

    The example adds new records by executing the Insert command.

    C#
    Copy Code
    using (C1ODataConnection conn = new C1ODataConnection(ODataConnectionString))
    {
        //Populate DataTable
        C1ODataDataAdapter adapter = new C1ODataDataAdapter(conn, "Select * from Books");
        DataTable 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 (C1ODataConnection con = new C1ODataConnection(ODataConnectionString))
    {
       //Populate DataTable
       C1ODataConnection conn = new C1ODataConnection(GCODataServerConnectionString);
       C1ODataDataAdapter adapter = new C1ODataDataAdapter(conn, "Select * from Books limit 10");
       DataTable 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 Update command.

    C#
    Copy Code
    using (C1ODataConnection conn = new C1ODataConnection(ODataConnectionString))
    {
       //Populate DataTable
       C1ODataDataAdapter adapter = new C1ODataDataAdapter(conn, "Select * from Books");
       DataTable 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 Delete command.

    C#
    Copy Code
    using (C1ODataConnection conn = new C1ODataConnection(ODataConnectionString))
    {
        //Populate Datatable
        C1ODataDataAdapter adapter = new C1ODataDataAdapter(conn, "Select * from Books");
        DataTable 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);
    }