here is a syntaxe I've just test :
this.cmdInsertLib.CommandText = "MyStoredProc";
this.cmdInsertLib.CommandType = System.Data.CommandType.StoredProcedure;
this.cmdInsertLib.Parameters.Add(
new System.Data.OleDb.OleDbParameter("P1",
System.Data.OleDb.OleDbType.VarWChar, 100));
this.cmdInsertLib.Parameters.Add(
new System.Data.OleDb.OleDbParameter("P2",
System.Data.OleDb.OleDbType.VarWChar, 100));
cmdInsertLib.Parameters[0].Value = textBox1.Text.ToString();
cmdInsertLib.Parameters[1].Value = textBox2.Text.ToString();
cmdInsertLib.ExecuteNonQuery();
that's all folks
hope this helps
comments are welcome ¶ 6:23 AM 0 comments
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!
On your favourite Oracle Client try this :
select distinct package_name from all_arguments
this will list every packages including yours on the current schema.
then try this one:
select distinct object_name
from all_arguments where package_name like 'STANDARD'
this will list every standard functions installed. I've found it useful when try to remember the exact syntax of "NUMTODSINTERVAL " or "NUMTOYMINTERVAL".
that's all folks
hope this helps
comments are welcome
¶ 6:38 AM 0 comments
