Connect a .Net application to an Oracle Database 1 Hello,
today I would like to talk about something that, I think, is very important when you want to build an Oracle and Microsoft based Application.
I want to talk about the ways you can use to connect a .Net application to an Oracle Database. It is a very wide subject with many aspects. I will dedicate many post to this.

Today, lets concentrate on architectural aspects.

Okay you've got an Oracle Database and a Windows .Net Platform. How to go from here to there?

1 - First of all, Oracle is a relational Database implementing the SQL standard, somewhere in your system you need to build
SQL queries to acces your data(and your stored procedures calls).

2 - Oracle is a secure application. You need some login informations to connect to the database.(database name, login,
password)

3 - Oracle is also a network based application. You need an Oracle Client to access the database.

4 - Oracle and Microsoft are two different corporations. You need a specific - so called - middleware to make those two
technologies talking together.

5 - .Net provide an Object Oriented API. So, you need to manipulate some objects of this API to connect the application to
the Oracle Client and retrieve the data.


Ok lets see that more deeply:

1 - SQL. Ok nothing special about this. If You ever work with a relational database you know what I'm talking about.
If you never, you can survive to this article by knowing that :
In this language three keywords are important "select", "from" and "where" to get your column of data named "A" in a table
named B, just ask: "Select A from B" if you just want the value which are greater than 1 ask "select A from B where A>1".
Just remember that you can retrieves data, but also change it.

2 - Those of you Who ever work with Oracle, Know how to connect to a database. Fo those of you Who never work with this
system lets say that an Oracle server can run many Databases. Each database has its own name. Each database has its own
groups of users. The database's administrator can gives the right to read or to write on a table to a specific user. So when
you connect to a database you provide a"user name" and a "password" and the system authorize you or not to acces ressources
regarding your rights. All applications need a database name, a login and a password.

3 - The client computer needs what Oracle calls "Oracle connectivity" it means the basics software which allow the computer to act as client for Oracle. You may have to install these softwares yourself using an Oracle CD. You may also have to configure the tnsnames.ora file. It is a configuration file you find on every machine connected to Oracle. Common path is :

<oracle installation directory>/network/admin/
TNS is standing for "Transparent Network Substrate" which is a part of Oracle network protocol. To connect to your data base, this file must contains information about the server on which you database is.
A databse entry of your tnsnames.ora is like the following:
MY_DATABASE_NAME.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MY_SERVER_NAME)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = My_SERVICE_NAME)
)
)
Ask your favorite database administrator for the MY_SERVER_NAME, and MY_SERVICE_NAME.
Don't ask me any question about the ".world" I never understand why it is sometimes unnecessary and sometimes mandatory (ask this question to your DBA to)

4 - Middleware. Ok now hard things begin. I call "middleware" any software which allow very differrent software to communicate each others. Whith microsoft You have different way to plug your application on a database.
Here i 'm going to speak about two common methods. ODBC and OLE DB.
ODBC is standing for "Open DataBase Connectivity" (forget the word "Open" lol).
It is a microsoft standard and coms with most of windows operating systems. It is a kind of repository where all connection to databases are stored. An ODBC entry is named a Data Source Name(DSN). To configure a DSN, you choose an ODBC driver dedicate to your Database System. And then give the database, login and password info.
For instance to connect to "My_database" on windows XP:

1 Open the configuration panel
2 Choose "administration tools"
3 Then click on ODBC data sources
4 Choose the "System Data source" (you need administrator rights)
5 click on "ADD.."
6 Choose a database odbc drivers, here you may choose "Microsoft ODBC for Oracle" or something like "Oracle in OraHome92"
7 A driver specific window pops up, put the DSN name(which you will use to acces it later), your database name, login and password. Often a "test" button allows you to check that you 've put the right arguments.
8 validate

That's it. you have configured a DSN for an ODBC connection.

Good news, For OLE DB you don't need any configuration like this. in fact in that case, this layer is nearly transparent.

5 - The objects. The .Net framework provide the System.Data API. The principles are
- You build a "connection" object using a "connection string" (which will contain information related to the database the ODBC or OLEDB layer, and so on)
- Then you build a "DATAadaptater" object which is dedicated to encapsulate the SQL strings.
- Then you build a "dataset" object which will allow you to access the data and maybe put it into graphical object.

for instance, if you connect via oleb your C# code will be close to this one :




using System;
using System.Data;
using System.Data.OleDb;
using System.Xml;

namespace oracle
{
public class oracleData
{
static string strConnect;//Connection String
static OleDbConnection myConnection; //connnection object
static OleDbDataAdapter myAdapter;// see it later
static OleDbCommand mySelect; // select command
static DataSet myDS;
static void Main(string[] args)
{
//The connection string :
strConnect ="Provider=\"OraOLEDB.Oracle.1\";User ID=MY_LOGIN;PASSWORD=My_Password;Data Source=My_database;Extended Properties=;Persist Security Info=False";

//the connection itself
myConnection = new OleDbConnection();
myConnection.ConnectionString= strConnect; //affect the connection string
myConnection.Open();//open the connection

//SQL
mySelect =new OleDbCommand("select * from dual ",myConnection);
myAdapter = new OleDbDataAdapter(mySelect);

//dataset
myDS = new DataSet("MyDataSetName"); //instanciate the data set
myAdapter.Fill(myDS,"MyTable"); //load the data into the dataset

//An easy way to retrieve the content of a dataset : XML export
myDS.WriteXml("c:\\data.xml",XmlWriteMode.WriteSchema);// double "\" because "\" is an escape character
myConnection.Close();
}
}
}


Assuming the oledb provider "OraOLEDB.Oracle.1"is installed, you can use this in a .cs file and compile it with csc.exe (locate in
c:/window/microsoft .net/framework/v1.1.4322/ on my station)

Take close attention to the connection string strConnect and replace the name of the database/login/password by yours.

Ok thats all folks! (and that is enought for today)
Comments are welcome!

A colleague of mine says to me about this blog :"there's a lot of code"( lol). Next time I will try talk in a more "human" way.












  ¶ 6:18 AM