- 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.
{codecitation class=”brush:vbnet;”}
Imports Oracle.DataAccess.Client ‘ VB.NET
{/codecitation}
{codecitation class=”brush:c-sharp;”}
using Oracle.DataAccess.Client; // C#
{/codecitation}
- 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:
{codecitation class=”brush:vbnet;”}
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#
{/codecitation}
- 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.
{codecitation class=”brush:vbnet;”}
‘ VB.NET
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;”;
{/codecitation}
- Now you can create connection object from the connection string above. The connection string must be associated with the connection object.
{codecitation class=”brush:vbnet;”}
Dim conn As New OracleConnection(oradb) ‘ VB.NET
OracleConnection conn = new OracleConnection(oradb); // C#
{/codecitation}
- Now you can use this connection like any other connection and do various database tasks.
- To open a connection, use following statements,
{codecitation class=”brush:vbnet;”}
conn.Open() ‘ VB.NET
conn.Open(); // C#
{/codecitation}
- To create a command object, use following statements,
{codecitation class=”brush:vbnet;”}
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;
{/codecitation}
- You can retrieve values from command object using following statement,
{codecitation class=”brush:vbnet;”}
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
{/codecitation}
- All open connection objects should be closed once you are done using them.
{codecitation class=”brush:vbnet;”} conn.Close() ‘ Visual Basic
conn.Dispose() ‘ Visual Basic
conn.Close(); // C#
conn.Dispose(); // C# {/codecitation}
{kunena_discuss:15}