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."