none
Question about ADO.NET (OracleDataAdapter) RRS feed

  • Question

  • Hi

    Periodically we recieve data files from the client which are updates to existing data. We are using Oracle 11g and are using the Oracle Client library by Oracle (ODP.NET).

    I have a solution that works. however what that involves is - using an oracle data adapter fill the oracle data set with the original data from the database. Set the fields that have changed. Create an update command using OracleCommandBuilder and use data adapter Update method to post the changes.

    However there is an overhead involved i getting the original data which can sometimes be a lot . The only other option being update using parametrized queries.

    So i tried doing something like this.

    Create a data Adapter using the connection and the Sql Statement "SELECT * FROM CUSTOMER WHERE CUSTOMER_ID = -1"and then did a dataAdapter.Fill(dataset). This bought me my schema but no data.

    For every client record i created a new row in the datatable with only the PK. I then called Datarow.AcceptChanged to change the row state from Added to Unchanged. I Followed this by setting all the fields in the row that had changed. This made the row state as Modifed. I then used the OracleCommandBuilder to create an UpdateCommand for my DataAdapter and use the DataAdapter.Update method.

    This however gives an concurency violation.

    Is this approach or something similar possible ?

    Please let me know

    Thank you

    Regards

    Siddharth.

    • Moved by SamAgain Thursday, March 25, 2010 3:04 AM better fit (From:.NET Base Class Library)
    Wednesday, March 24, 2010 4:09 PM

Answers

  • Siddarth,

     

    As you pointed out, fetching the current contents of the database rows in order to handle the scenario is needless overhead.  Your best bet is to build a parameterized OracleCommand for each row in the client file.  The query you're building will look something like:

     

    UPDATE Customer
        SET FieldA = :ValueA, FieldB = :ValueB, ...
        WHERE ClientID = :ClientID
    

    Based on what you've described, the WHERE clause will be static and will contain just the primary key field(s).  The challenge will be to evaluate the contents of the row in the client file to determine which fields have changed.  I'm assuming that if a field does not appear that the value for that column in the corresponding database row should remain unchanged.  If that's the case, the SET clause will change on a row by row basis.

     

    I put together some rough code as an approach you could follow.  Obviously, you'll want to replace the code with the code you use to check the contents of the data file and you may want to use StringBuilder classes and other ways to handle building the contents of the SET clause, but it the basic approach should serve as a decent starting point.  

    1. Walk through the contents of the row.
    2. For each modified field, add an entry to the query's SET clause and a parameter object.
    3. Add a parameter for the key field.
    4. Execute the query, determine success/failure, handle failure if appropriate.

    Note: I used System.Data.OracleClient because I don't have ODP.NET on this machine right now, but going with ODP.NET is definitely the right call.

     

    OracleConnection connection = new OracleConnection();
    OracleCommand command;
    
    int fieldsToModify = 0;
    string setClause = "";
    object fieldValue = null;
    
    //for each row in the data file
    object customerId = GetFieldValue("CustomerId");
    
    //Initialize the command
    command = connection.CreateCommand();
    
    //Track the number of fields to modify
    fieldsToModify = 0;
    
    
    //If there's a value for Field1, add it to the SET clause
    //and generate a parameter
    fieldValue = GetFieldValue("Field1");
    if (fieldValue != null)
    {
        fieldsToModify++;
        setClause += "Field1 = :Field1";
        command.Parameters.Add(":Field1", OracleType.NVarChar).Value = fieldValue;
    }
    
    //If there's a value for Field2, add it to the SET clause
    //and generate a parameter
    fieldValue = GetFieldValue("Field2");
    if (fieldValue != null)
    {
        fieldsToModify++;
        if (fieldsToModify == 1)
            setClause += "Field2 = :Field2";
        else
            setClause += ", Field2 = :Field2";
        command.Parameters.Add(":Field2", OracleType.NVarChar).Value = fieldValue;
    }
    
    //If there's a value for Field3, add it to the SET clause
    //and generate a parameter
    fieldValue = GetFieldValue("Field3");
    if (fieldValue != null)
    {
        fieldsToModify++;
        if (fieldsToModify == 1)
            setClause += "Field3 = :Field3";
        else
            setClause += ", Field3 = :Field3";
        command.Parameters.Add(":Field3", OracleType.NVarChar).Value = fieldValue;
    }
    
    //If there are fields to modify
    //  Set the Command's CommandText property
    //  Add the CustomerID parameter
    //  Execute the query
    //  Check the number of rows modified
    //  Handle failed update attempt if necessary
    if (fieldsToModify > 0)
    {
        string commandTextTemplate = "UPDATE Customers SET {0} WHERE Customer_ID = :CustomerID";
        command.CommandText = string.Format(commandTextTemplate, setClause);
        command.Parameters.Add(":CustomerID", OracleType.NVarChar).Value = customerId;
    
        int rowsUpdated = command.ExecuteNonQuery();
        if (rowsUpdated != 1)
        {
            //Handle failed update
        }
    }

    I hope this information proves helpful.


    David Sceppa
    Thursday, March 25, 2010 10:35 PM
    Moderator

All replies

  • Can you do it with a single SQL Update command? Then you should use just a OracleCommand with parametrised UPDATE command.
    Miha Markic [MVP C#] http://blog.rthand.com
    Wednesday, March 24, 2010 7:48 PM
  • Siddarth,

     

    Based on the information you've provided, the failure you're seeing in the Add/AcceptChanges/Modify/Update scenario is as expected.  CommandBuilder objects will, by default, include the original values of all comparable fields in the WHERE clause of update and delete logic.  It sounds like you don't have the original values for the row in the data files from the client.

     

    There are a couple options I could suggest that should simplify things, but determining which option(s) will work will depend on the information that's in the data files you're getting from the client.  Do the files contain the entire row or just the columns to modify?  Do they contain the original values for the columns to modify?

     

    As Miha pointed out, submitting updates via a parameterized Command can get the job done.  There may be a simpler solution depending on what information is in the data files.

     


    David Sceppa
    Thursday, March 25, 2010 5:50 AM
    Moderator
  • Hi David

    The scenario we have is that the client file will have only the modified field. We originally define the file layout as a contract with the client as CLIENTID|FIELD1|FIELD2|...... and so on.

    The client sends us only the mdified data fields. So the records look like

    CLIENTID||FIELD2||FIELD4

    CLIENTID|FIELD1|||FIELD4

    Some of our clients being really small vendors cannot invest in a full blown ETL tool - hence the need for us to develop a small dektop solution for them.

    I could always fetch the data depending on the client key and then set the modified values but thats an extra step which could take a toll on the performance if the files get huge going ahead.

    Any help will be really appreciated.

    Regards

    Siddharth

    Thursday, March 25, 2010 2:34 PM
  • Siddarth,

     

    As you pointed out, fetching the current contents of the database rows in order to handle the scenario is needless overhead.  Your best bet is to build a parameterized OracleCommand for each row in the client file.  The query you're building will look something like:

     

    UPDATE Customer
        SET FieldA = :ValueA, FieldB = :ValueB, ...
        WHERE ClientID = :ClientID
    

    Based on what you've described, the WHERE clause will be static and will contain just the primary key field(s).  The challenge will be to evaluate the contents of the row in the client file to determine which fields have changed.  I'm assuming that if a field does not appear that the value for that column in the corresponding database row should remain unchanged.  If that's the case, the SET clause will change on a row by row basis.

     

    I put together some rough code as an approach you could follow.  Obviously, you'll want to replace the code with the code you use to check the contents of the data file and you may want to use StringBuilder classes and other ways to handle building the contents of the SET clause, but it the basic approach should serve as a decent starting point.  

    1. Walk through the contents of the row.
    2. For each modified field, add an entry to the query's SET clause and a parameter object.
    3. Add a parameter for the key field.
    4. Execute the query, determine success/failure, handle failure if appropriate.

    Note: I used System.Data.OracleClient because I don't have ODP.NET on this machine right now, but going with ODP.NET is definitely the right call.

     

    OracleConnection connection = new OracleConnection();
    OracleCommand command;
    
    int fieldsToModify = 0;
    string setClause = "";
    object fieldValue = null;
    
    //for each row in the data file
    object customerId = GetFieldValue("CustomerId");
    
    //Initialize the command
    command = connection.CreateCommand();
    
    //Track the number of fields to modify
    fieldsToModify = 0;
    
    
    //If there's a value for Field1, add it to the SET clause
    //and generate a parameter
    fieldValue = GetFieldValue("Field1");
    if (fieldValue != null)
    {
        fieldsToModify++;
        setClause += "Field1 = :Field1";
        command.Parameters.Add(":Field1", OracleType.NVarChar).Value = fieldValue;
    }
    
    //If there's a value for Field2, add it to the SET clause
    //and generate a parameter
    fieldValue = GetFieldValue("Field2");
    if (fieldValue != null)
    {
        fieldsToModify++;
        if (fieldsToModify == 1)
            setClause += "Field2 = :Field2";
        else
            setClause += ", Field2 = :Field2";
        command.Parameters.Add(":Field2", OracleType.NVarChar).Value = fieldValue;
    }
    
    //If there's a value for Field3, add it to the SET clause
    //and generate a parameter
    fieldValue = GetFieldValue("Field3");
    if (fieldValue != null)
    {
        fieldsToModify++;
        if (fieldsToModify == 1)
            setClause += "Field3 = :Field3";
        else
            setClause += ", Field3 = :Field3";
        command.Parameters.Add(":Field3", OracleType.NVarChar).Value = fieldValue;
    }
    
    //If there are fields to modify
    //  Set the Command's CommandText property
    //  Add the CustomerID parameter
    //  Execute the query
    //  Check the number of rows modified
    //  Handle failed update attempt if necessary
    if (fieldsToModify > 0)
    {
        string commandTextTemplate = "UPDATE Customers SET {0} WHERE Customer_ID = :CustomerID";
        command.CommandText = string.Format(commandTextTemplate, setClause);
        command.Parameters.Add(":CustomerID", OracleType.NVarChar).Value = customerId;
    
        int rowsUpdated = command.ExecuteNonQuery();
        if (rowsUpdated != 1)
        {
            //Handle failed update
        }
    }

    I hope this information proves helpful.


    David Sceppa
    Thursday, March 25, 2010 10:35 PM
    Moderator
  • Hi David

    As per your suggestion, i will try out the parameterized query approach. Thanks for the help.

    Regards

    Siddharth

    Monday, March 29, 2010 3:10 PM