Oracle Database Update using OracleCommandBuilder

Oracle Database Update using OracleCommandBuilder

                                                                          Xiaolin (Colin) Peng

                                                                   WitStream Technologies Inc.

 

1. Introduction  

I have a table with 40 columns, I need to insert new records into this table. I don't want to explicitly use "INSERT" SQL statement in my C# code, and the database update is through a dataset, how can I achieve that?

In this little article, I like to share my experience with you on using command builder in ADO.NET.  My database server is Orable 10g, and I use Oracle Data Provider for .NET as SQL client.  Since my project is under copyright protection, I can not post my whole source code here. I hope some code segments with some comments can make sense to you guys.

2. Open Database Connection

Before any database operation, we need to have an open database connection.  A database connection in .NET can be achieved through a connection string:  

   OracleConnection dbConn = new OracleConnection();

    dbConn.ConnectionString = ConnectString;

   dbConn.Open();

An example Oracle connection string can be like:

   "Data Source=TNS-Name;User ID=xxxxxx;Password=******;"

TNS-Name is configured in the oracle tnsnames.ora file, and it looks like this:

   TNS-SAMPLE =
     (DESCRIPTION =
       (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1500))
       )
       (CONNECT_DATA =
         (SID = SAMPLE-SID)
       )
     )

3. Data table schema

After the database connection is open, we can get the schema of the data table to update.

   //create a data adapter

   OracleDataAdapter odaAdapter = new OracleDataAdapter();

   OracleCommand selectCommand = new OracleCommand("SELECT * FROM MY_TABLE", this.dbConn);

   odaAdapter.SelectCommand = selectCommand;

   //create a DataSet

   DataSet ds = new DataSet();

   //get the schema

   odaAdapter.FillSchema(ds, SchemaType.Source, "MY_TABLE");

My goal here is to insert data into the table "MY_TABLE" using a dataset, which means I will insert data into dataset first, then I will ask the oracle data adapter to do the update.

Please note that the SelectCommand is used here to obtain the schema of "MY_TABLE". FillSchema() is called here for this purpose.

After the FillSchema() call, the dataset should have one DataTable with all the columns defined. Of course, dataset has no data in it. If you need to get the existing data from the table, you know data adapter's Fill() method is for that.

4. Insert Data into DataSet

Now we are ready to insert data into the dataset, in real application, usually threre are more than one records to insert. So we have some kind of loop:

   foreach(MyItem item in Items)

   {

      //create a data row

      //we only have one table

      DataRow newRow = ds.Tables["MY_TABLE"].NewRow();;

      //fill data row with journal entry line item data

      this.FillDataRow(newRow, item);

      //add data row to table

      ds.Tables["MY_TABLE"].Rows.Add(newRow);

   }

To add a new row into a DataTable, we have to call its NewRow() method. The actual data insertion is in the FillDataRow() method, this method is a private and based on the actual business rule, it is not shown here.

After the loop, there should be multiple rows of data in the DataSet object.

5. Flush Data to Database

When DataSet update is complete, we are ready to get the new records to the database. What will happen if we just simple make the following call?

    odaAdapter.Update(ds, "MY_TABLE");

You will get an exception saying that there is no InsertCommand in the data adapter. What this means is that, when you try to update the database using dataset, ADO.NET detects new records in the dataset, so it knows insertion will occur in the database update. In order to do the insertion, the data adpater has to have an InsertCommand defined (This same is true for deletion).

One approach is to define an InsertCommand to the data adapter using "INSERT" statement, but my table has 40 columns, and I definitely try not to do it that way. Thanks to ADO.NET commad builder, we really don't need to hard code an insert command. Here is the simple but working solution:

   //fill the data set

   OracleCommandBuilder commandBuilder = new OracleCommandBuilder(odaAdapter);

   odaAdapter.Update(ds, "MY_TABLE");

6. Conclusion

By using some code segments, I showed you how to update a database without explicitly defining InsertComment, DeleteComment, UpdateCommand in a data adapter. The command builder can take care of this.  If you want to know a little bit about what this command builder does, here is an excerpt from the Oracle document on OracleCommandBuilder:

"OracleCommandBuilder automatically generates SQL statements for single-table updates when the SelectCommand property of the OracleDataAdapter is set. An exception is thrown if the DataSet contains multiple tables. The OracleCommandBuilder registers itself as a listener for RowUpdating events whenever its DataAdapter property is set. Only one OracleDataAdapter object and one OracleCommandBuilder object can be associated with each other at one time."