Connect to Oracle using ASP.Net and C# or VB.Net

Connect to Oracle using ASP.Net and C# or VB.Net

• Create a new project in Visual Studio using eight C# or VB.Net

• Add reference to Oracle.DataAccess.dll file. Typically this file can be found in C:\oracle\product\10.2.0\client_1\BIN directory. This directory may be different based on your oracle configuration.
• Once library is referenced, go to your class file where you want to create oracle connection.
• Add following statements based on language you selected for project.

1.Imports Oracle.DataAccess.Client ' VB.NET

1.using Oracle.DataAccess.Client;  // C#

• An Oracle connection string is inseparable from Oracle names resolution. Suppose we had a database alias of OraDb defined in a tnsnames.ora file as follows:

OraDb=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=OTNSRVR)(PORT=1521))
)
(CONNECT_DATA=
(SERVER=DEDICATED)
(SERVICE_NAME=ORCL)
)
)

• The OraDb alias defines the database address connection information for the client. To use the OraDb alias defined in the tnsnames.ora file shown above, you would use the following syntax:

1.Dim oradb As String = "Data Source=OraDb;User Id=scott;Password=tiger;"  ' VB.NET

2. 

3.string oradb = "Data Source=OraDb;User Id=scott;Password=tiger;";   // C#

You can modify the connection string to obviate the need for the tnsnames.ora file, however. Simply replace the name of the alias with how it would be defined in a tnsnames.ora file.

01.' VB.NET

02.</li>

03.</ul>

04.Dim oradb As String = "Data Source=(DESCRIPTION=" _

05.+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=OTNSRVR)(PORT=1521)))" _

06.+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));" _

07.+ "User Id=scott;Password=tiger;"

08. 

09.// C#

10.string oradb = "Data Source=(DESCRIPTION="

11.+ "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASRVR)(PORT=1521)))"

12.+ "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"

13.+ "User Id=scott;Password=tiger;";

• Now you can create connection object from the connection string above. The connection string must be associated with the connection object.

1.Dim conn As New OracleConnection(oradb) ' VB.NET

2. 

3.OracleConnection conn = new OracleConnection(oradb); // C#

• Now you can use this connection like any other connection and do various database tasks.
• To open a connection, use following statements,

1.conn.Open() ' VB.NET

2. 

3.conn.Open(); // C#

• To create a command object, use following statements,

1.Dim sql As String = "select dname from dept where deptno = 10" ' VB.NET

2.Dim cmd As New OracleCommand(sql, conn)

3.cmd.CommandType = CommandType.Text

4. 

5.string sql = "select dname from dept where deptno = 10"; // C#

6.OracleCommand cmd = new OracleCommand(sql, conn);

7.cmd.CommandType = CommandType.Text;

• You can retrieve values from command object using following statement,

01.Dim dr As OracleDataReader = cmd.ExecuteReader() ' Visual Basic

02.dr.Read()

03. 

04.Label1.Text = dr.Item("dname") ' retrieve by column name

05.Label1.Text = dr.Item(0) ' retrieve the first column in the select list

06.Label1.Text = dr.GetString(0) ' return a .NET data type

07.Label1.Text = dr.GetOracleString(0) ' return an Oracle data type

08.OracleDataReader dr = cmd.ExecuteReader(); // C#

09.dr.Read();

10. 

11.label1.Text = dr["dname"].ToString(); // C# retrieve by column name

12.label1.Text = dr.GetString(0).ToString();  // return a .NET data type

13.label1.Text = dr.GetOracleString(0).ToString();  // return an Oracle data type

• All open connection objects should be closed once you are done using them.

view sourceprint?

1.conn.Close()   ' Visual Basic

2.conn.Dispose() ' Visual Basic

3. 

4.conn.Close();   // C#

5.conn.Dispose(); // C#