s

Home > c# > c# .net how to read data from oracle database

C# .net how to read data from oracle database edit button Edit

author
Murugan Andezuthu Dharmaratnam | calendar 15 October 2020 | 3597

In this article, we will look at how to read data from the oracle database from an asp .net application. If you are already familiar with writing code on reading data using ADO .net System.Data.SqlClient then code for reading data from the oracle database is very much similar, Here we will be using the Oracle.ManagedDataAccess.dll to read data from oracle database.

Sample Code

public static DataTable getDBTable(string query) {
     DataTable dt = new DataTable();
     int rowsaffected = 0;

     using (OracleConnection connection = new OracleConnection("DATA SOURCE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = "   Constants.SERVER_HOST   ")(PORT = "   Constants.SERVER_PORT   ")))(CONNECT_DATA = (SERVICE_NAME = "   Constants.SERVER_SERVICE_NAME   "))); USER ID = "   Constants.SERVER_USER   "; PASSWORD = "   Constants.SERVER_PSWD   ";")) {
         connection.Open();
         OracleCommand command = new OracleCommand();
         command.Connection = connection;
         command.CommandTimeout = 0;
         command.CommandText = query;
         OracleDataAdapter resultdata = new OracleDataAdapter(command.CommandText, connection);
         resultdata.SelectCommand.CommandTimeout = 0;
         try {
             rowsaffected = resultdata.Fill(dt);
         } catch (OracleException ex) {
             Console.WriteLine(ex.ToString());
         }
         connection.Close();
     }
     return dt;
 }

Oracle.ManagedDataAccess.dll can be downloaded from the oracle website. or from nuget . You can right click on references, select Manage Nuget Packages, and search for Oracle.ManagedDataAccess. Select and click on install. Add reference to your C# code.

The above function returns a data table. rowsaffected will return the numbers of rows returned from the present query. You can call the above function using the code below.

Code

String queryString = "select * from setupcountry";
DataTable  dtCountry = new DataTable();
dtCountry = getDBTable(queryString);