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.

Imports Oracle.DataAccess.Client ' VB.NET
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:

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

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.

    ' VB.NET
    </li>
    </ul>
    Dim oradb As String = "Data Source=(DESCRIPTION=" _
     + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=OTNSRVR)(PORT=1521)))" _
     + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));" _
     + "User Id=scott;Password=tiger;"
    
    // C#
    string oradb = "Data Source=(DESCRIPTION="
     + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASRVR)(PORT=1521)))"
     + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
     + "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.

    Dim conn As New OracleConnection(oradb) ' VB.NET
    
    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,

    conn.Open() ' VB.NET
    
    conn.Open(); // C#
    

    • To create a command object, use following statements,

    Dim sql As String = "select dname from dept where deptno = 10" ' VB.NET
    Dim cmd As New OracleCommand(sql, conn)
    cmd.CommandType = CommandType.Text
    
    string sql = "select dname from dept where deptno = 10"; // C#
    OracleCommand cmd = new OracleCommand(sql, conn);
    cmd.CommandType = CommandType.Text;
    

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

    Dim dr As OracleDataReader = cmd.ExecuteReader() ' Visual Basic
    dr.Read()
    
    Label1.Text = dr.Item("dname") ' retrieve by column name
    Label1.Text = dr.Item(0) ' retrieve the first column in the select list
    Label1.Text = dr.GetString(0) ' return a .NET data type
    Label1.Text = dr.GetOracleString(0) ' return an Oracle data type
     OracleDataReader dr = cmd.ExecuteReader(); // C#
    dr.Read();
    
    label1.Text = dr["dname"].ToString(); // C# retrieve by column name
    label1.Text = dr.GetString(0).ToString();  // return a .NET data type
    label1.Text = dr.GetOracleString(0).ToString();  // return an Oracle data type
    

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

     conn.Close()   ' Visual Basic
    conn.Dispose() ' Visual Basic
    
    conn.Close();   // C#
    conn.Dispose(); // C#  



    Posted: 5 years 11 months ago by sriramkukkadapu #135
    sriramkukkadapu's Avatar
    sir..! im getting the following Exception

    System.BadImageFormatException

    i have used the following code

    Imports Oracle.DataAccess.Client
    Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    Try
    Dim oradb As String = "Data Source=oracle;User Id=sriram;Password=sriram;"
    Dim conn As New OracleConnection(oradb)
    conn.Open()
    Catch ex As Exception
    Label1.Text = ex.ToString()
    End Try
    End Sub
    End Class

    please tell me how to connect to oracle 10g correctly sir.. because im facing dis problem from so many days n do not found any solution..
    please help me sir