.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Thursday, April 07, 2005
  Updating two lines with one data adapter.

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!

 
Comments: Post a Comment

Links to this post:

Create a Link



<< Home
You are a developer and work with Oracle and Microsoft technologies? Have a look!
ATOM
How to:
Use updatable views in Access
Get data in Excel from Oracle 1
Get data in Excel from Oracle 2
Draw the Mandelbrot set using C#
Use the "Grouping Sets" SQl Syntax
Use the "Rollup" SQl Syntax
Use the "Rank over" SQl Syntax

oracle functions
link
Quotes & parameters
Oracle sequences and ADO .Net.
Back to work
Code generation and self description
links
Oracle data dictionary
Code generation with .net
Change all querytable in an Excel File
ARCHIVES
October 2004 / November 2004 / December 2004 / January 2005 / February 2005 / March 2005 / April 2005 / June 2005 / July 2005 / August 2005 / September 2005 / December 2005 / February 2006 / December 2006 / March 2009 /


Powered by Blogger

mboizeau.free.fr