.comment-link {margin-left:.6em;}
Marc Boizeau's blog
Thursday, April 28, 2005
  link
just a link today:
Database Forums
 
Tuesday, April 19, 2005
  call an Oracle stored proc with oledb .net
Thanks to Emilio who ask the question on microsoft.public.dotnet.framework.adonet newsgroup.

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
 
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!

 
Friday, April 01, 2005
  oracle functions
Today just a little Oracle trick: the all_arguments view.

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

 
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

Go to knol
When does the next bus pass?
Thanks
googled2cd966929769ab9
two lines in the datagrid header
Context saving with persistent datasets
.net webservice session
Winform, Web Services & credential
back to work
just a link
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