Inserting SYSDATE using .GetInsertCommand() and UpdateDataset()

Topics: Data Access Application Block
Mar 15, 2012 at 11:55 AM


I'm inserting one row using mydb.UpdateDataset().

For the row that is inserted I want to ensure that the LAST_UPDATED_TIME column has the current Database Server Time (SYSDATE) value.

I'm currently getting a reference to the new row just before sending for insert and setting the LAST_UPDATED_TIME column value manually.

Is there a smarter way of doing this when using the UpdateDataset() method? 

The insert statements are auto-generated using myDataAdapter.GetInsertCommand()

Thanks in advance for any suggestions.

Mar 16, 2012 at 3:28 AM

You could also set a custom insert command that uses SYSDATE.  This would avoid a round trip to the database to fetch SYSDATE (if the date is fetched from the DB) as well as potential date synchronization issues (if the date used is the date from the application server).

I've used the fetch the date from the database approach before where I wanted multiple tables to all have the same update date since they were part of a single atomic transaction.

Randy Levy
Enterprise Library support engineer 

Mar 16, 2012 at 9:25 AM

Is it possible to give me an example of the custom insert command that uses SYSDATE please?

Will this work with my .GetInsertCommand() method?

Mar 17, 2012 at 6:20 AM

See Walkthrough: Using a DataSet to Update a Database for an example.  Based on that article the general approach would look like this:

    Database db = DatabaseFactory.CreateDatabase();

    DataSet productsDataSet = new DataSet();

    string sqlCommand = "Select ProductID, ProductName, CategoryID, UnitPrice, LastUpdate From Products";
    DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);

    string productsTable = "Products";

    // Retrieve the initial data.
    db.LoadDataSet(dbCommand, productsDataSet, productsTable);

    // Get the table that will be modified.
    DataTable table = productsDataSet.Tables[productsTable];

    // Add a new product to existing DataSet.
    DataRow addedRow = table.Rows.Add(new object[] { DBNull.Value, "New product", 11, 25 });

    // Modify an existing product.
    table.Rows[0]["ProductName"] = "Modified product";

    DbCommand insertCommand = db.GetSqlStringCommand("insert into Products (ProductName, CategoryID, UnitPrice, LastUpdate) values (:productName, :categoryID, :UnitPrice, SYSDATE)");
    db.AddInParameter(insertCommand, ":ProductName", DbType.String, "ProductName", DataRowVersion.Current);
    db.AddInParameter(insertCommand, ":CategoryID", DbType.Int32, "CategoryID", DataRowVersion.Current);
    db.AddInParameter(insertCommand, ":UnitPrice", DbType.Currency, "UnitPrice", DataRowVersion.Current);

    // Submit the DataSet, capturing the number of rows that were affected.
    int rowsAffected = db.UpdateDataSet(productsDataSet, 

Randy Levy
Enterprise Library support engineer