DataConnector
ADO.NET provider for OData / Batch Processing
In This Topic
    Batch Processing
    In This Topic

    The ADO.NET Provider for OData allows the user to perform multiple operations with bulk data through C1ODataDataAdapter. You can improve the process by executing many smaller batch requests. To control the size of each batch, you can set the C1ODataDataAdapter's UpdateBatchSize property to a positive integer.

    Steps to perform batch processing:

    The provider translates all SQL queries in the batch into a single request. Below are the examples of different operations with bulk data.

    Bulk Insert

    The below code example creates a batch that inserts data in bulk and retrieves the new data.

    using (C1ODataConnection con = new C1ODataConnection(ODataServerConnectionString))
    {
       //Populate Datatable
       C1ODataDataAdapter adapter = new C1ODataDataAdapter(conn, "Select * from Books");
       DataTable dataTable = new DataTable();
       adapter.Fill(dataTable);
    
       //Create Insert Query
       adapter.InsertCommand = new C1ODataCommand(conn);
       adapter.InsertCommand.CommandText = "Insert into books (ISBN,Title,Author,Price) values (@ISBN,@Title,@Author,@Price)";
       adapter.InsertCommand.Parameters.Add("@Title", "Title");//Query Parametres
       adapter.InsertCommand.Parameters.Add("@ISBN", "ISBN");
       adapter.InsertCommand.Parameters.Add("@Author", "Author");
       adapter.InsertCommand.Parameters.Add("@Price", "Price");
       adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;
    
       //Perform insert operation
       DataRow bookRow1 = dataTable.NewRow();
       bookRow1["Title"] = "Tama 2007";
       bookRow1["ISBN"] = "306-0-355-48016-0";
       bookRow1["Author"] = "CK";
       bookRow1["Price"] = 200.20;
       dataTable.Rows.Add(bookRow1);
       DataRow bookRow2 = dataTable.NewRow();
       bookRow2["Title"] = "Teachers Guide 2007";
       bookRow2["ISBN"] = "306-0-355-48016-0";
       bookRow2["Author"] = "MR Mk";
       bookRow2["Price"] = 260.20;
       dataTable.Rows.Add(bookRow2);
    
       //Set batch size to be updated
       adapter.UpdateBatchSize = 2;
       //Update database
       adapter.Update(dataTable);
       Console.WriteLine("Bulk insert successful !!! \n \n");
    }
    

     

    Bulk Update

    A batch update additionally requires the primary key of each row to update. The below code example prepares a batch that updates data in bulk.

    using (C1ODataConnection conn = new C1ODataConnection(ODataServerConnectionString))
    {
       //Populate Datatable               
       C1ODataDataAdapter adapter = new C1ODataDataAdapter(conn, "Select * from Books");
       DataTable dataTable = new DataTable();
       adapter.Fill(dataTable);
       //Create Update Query
       adapter.UpdateCommand = new C1ODataCommand(conn);
       adapter.UpdateCommand.CommandText = "UPDATE Books SET Title=@Title where id=@Id";//Update Query
       adapter.UpdateCommand.Parameters.Add("@Title", "Title");
       adapter.UpdateCommand.Parameters.Add("@Id", "Id");
       adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
       //Perform Update operation
       DataRow bookRow1 = dataTable.Rows[0];               
       bookRow1["Title"] = "XCatq 2007";
       DataRow bookRow2 = dataTable.Rows[4];               
       bookRow2["Title"] = "XMatqa 2020";
       //Set batch size to be updated
       adapter.UpdateBatchSize = 2;
       //Update database
       adapter.Update(dataTable);
       Console.WriteLine("Bulk update successful !!! \n \n");
    }
    

     

    Bulk Delete

    The below code example creates a batch that deletes data in bulk. The primary key for each row is required.

    using (C1ODataConnection conn = new C1ODataConnection(ODataServerConnectionString))
    {
       //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;
       //Perform delete operation
       DataRow bookRow1 = dataTable.Rows[13];
       bookRow1.Delete();
                   
       DataRow bookRow2 = dataTable.Rows[14];             
       bookRow2.Delete();
       //Set batch size to be updated
       adapter.UpdateBatchSize = 2;
       //Update database
       adapter.Update(dataTable);
       Console.WriteLine("Bulk delete successful !!! \n \n");
    }