Datatables and dataAdapter are designed to manipulate one database table at a time. The idea is that dataTable is like an asynchronous part of the database. So, when you make a change on one row of the dataTable you are suppose to change one and only one row in the database's table when synchronizing. Of course, sometimes it is not what you would like to do.

Today I would like to demonstrate how to update multiple rows in an Oracle table without any Stored Procedure but with oracle specific SQL.

A few days ago, I worked on a little multi-language tool. In a financial application, some labels where needed to be translate from French to English.

Therefore, I decided to make a little tool for the users in order to administrate their label’s translations.

There were three tables:

REF_LABEL:

IDT_LABEL PK

COM_LABEL unique

....

REF_LANGUAGE :

IDT_LNG PK

COM_LNG

REF_TRANSLATION:

IDT_LABEL FK PK

IDT_LNG PK

COM_TRANS

The requirement was to "provide
a grid with COM_LABEL, the French translation and the English one for each
label. The French and English translation available for modification"

First, I defined the main query:

string strSelect= " select COM_LABEL, FR.COM_TRANS, EN.COM_TRANS from
REF_TRANSLATION EN, REF_TRANSLATION FR, REF_LABEL L"
+

" where L.IDT_LABEL = EN.IDT_LABEL " +

" and L.IDT_LABEL = FR.IDT_LABEL" +

" and EN.IDT_LNG = 2 -- the English id " +

" and FR.IDT_LNG = 1 -- the French one " ;"

Then, I set the preceding as the CommandText property of an oledbCommand:

System.Data.OleDb.OleDbCommand cmdS = new System.Data.OleDb.OleDbCommand();

cmdS.CommandText =
strSelect ;

cmdS.Connection = MyValidConnection;

Next step was the update SQL command,
pay attention to the decode function:

System.Data.OleDb.OleDbCommand cmdU = new System.Data.OleDb.OleDbCommand();

cmdU.CommandText = "update ref_translation set com_trans = decode
(idt_lng,1, ?,2,?) where idt_label = ?"

cmdU.Connection =
MyValidConnection;

"?" are parameters, that I
defined by:

cmdU.Parameters.Add(new System.Data.OleDb.OleDbParameter("COM_TRANS_FR",
System.Data.OleDb.OleDbType.Variant, 80, "LBL_VAL_FR"));

cmdU.Parameters.Add(new System.Data.OleDb.OleDbParameter("COM_TRANS_EN",
System.Data.OleDb.OleDbType.Variant, 80, "LBL_VAL_EN"));

cmdU.Parameters.Add(new System.Data.OleDb.OleDbParameter("IDT_LABEL",
System.Data.OleDb.OleDbType.Decimal, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(4)), ((System.Byte)(0)), "IDT_LABEL",
System.Data.DataRowVersion.Original, null));

The "decode" function is Oracle specific. If you do not know this function, let us says that it is something like a C/C++/C# switch clause or a VB select case, but as a function. It takes
at least three arguments and return the third argument if the first is equal to the second, the fifth if the first is equal to the fourth etc... For further
info : http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/functions33a.htm#1017439

Then, I defined my dataAdapter by adding the following:

System.Data.OleDb.OleDbDataAdapter daLib = new System.Data.OleDb.OleDbDataAdapter();

daLib.SelectCommand = cmdS;

daLib.UpdateCommand = cmdU;

Hoops! Two pages, that is too long for one post.

That’s all folks!

Comments are welcome!