Updating one column only in a row with multiple columns changes

Topics: Data Access Application Block
Jul 2, 2012 at 10:46 AM
Edited Jul 2, 2012 at 10:46 AM


I have a typed datatable containing one datarow.

This datarow contains user changes for 5 of the columns.

I want to update only one column in the corresponding oracle database and in the datarow without affecting the other user changes.

Enterprise library seems to accept the one column change, but when I try to update the remaining row changes at a later time, I get a concurrency error.

Here is my sample code:


public void UpdateLastUpdatedTime(MyDataSet myDS, int mytableId, DateTime lastUpdatedTime)
            StringBuilder sbSQL = new StringBuilder();
            int rowsAffected = 0;
            DbCommand cmd = null;

                sbSQL.Append("UPDATE MYORACLETABLE ");
                sbSQL.Append("SET LAST_UPDATED_TIME         =   :lastUpdatedTime ");
                sbSQL.Append("WHERE MYORACLETABLE_ID        =   :mytableId ");

                //Get the row that has user changes - but only need to update one column for now:
                myDS.MYTYPEDTABLE[0].LAST_UPDATED_TIME = lastUpdatedTime;                

                //Update the dataset:
                cmd = SPSDB.GetSqlStringCommand(sbSQL.ToString());
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = CommonApplication.APP_DEFAULT_QUERY_TIMEOUT;

                SPSDB.AddInParameter(cmd, ":lastUpdatedTime", DbType.DateTime, lastUpdatedTime);
                SPSDB.AddInParameter(cmd, ":mytableId", DbType.Int32, mytableId);

                DbConnection cxn = SPSDB.CreateConnection();
                cmd.Connection = cxn;

                DbCommandBuilder cb = SPSDB.DbProviderFactory.CreateCommandBuilder();
                cb.DataAdapter = SPSDB.GetDataAdapter();
                cb.DataAdapter.UpdateCommand = cmd;

                rowsAffected = SPSDB.UpdateDataSet(myDS, myDS.MYTYPEDTABLE.TableName, null, cb.DataAdapter.UpdateCommand, null, UpdateBehavior.Transactional);
                if (rowsAffected == 0)
                    throw new Exception(string.Format("Update on datatable {0} did not complete.", "MYTYPEDTABLE"));
            catch (Exception ex)
                throw ex;
Jul 3, 2012 at 6:17 AM

When you perform the first update of the single column try getting the changes for the DataSet and passing that in instead of the actual DataSet.  It looks like some internal state is perhaps getting set when the update is performed:

rowsAffected = SPSDB.UpdateDataSet(myDS.GetChanges(), myDS.MYTYPEDTABLE.TableName, null, cb.DataAdapter.UpdateCommand, null, UpdateBehavior.Transactional);

Alternately, you could just perform the first, single update without a DataSet and using ExecuteNonQuery -- your code is almost 100% there.

Randy Levy
Enterprise Library support engineer