3 Overly Complicated Processes Easy Steps to Oracle Database connectivity

You’re a .Net developer. You’ve worked with MS SQL Server, PostgreSQL and MySQL databases on more than a few projects.  You feel pretty good about your abilities, right? Now, enter an opportunity to work with Oracle.

Oracle shouldn’t be too bad, right?  How different could it be? To the inexperienced, one might think that there really is no difference at all.  However, depending on the client and the level of security desired it could be vastly different.

Before you begin reevaluating your life choices, remember it’s only technology and it can be conquered.

In this example we have a .Net MVC4 project that uses a Microsoft SQL Server database (running on MS SQL Server 2012) as the base db for the project and the Oracle db is only necessary to pull specific data from, and push specific data to. The Oracle db is where all the reporting data resides and most of the data needed in this project does not need to be stored in Oracle, therefore we only send upstream (or push) data to Oracle as needed. On the flip side, we don’t want duplicate “Customers” or “Products” in our application, so we will just query (or pull) data we need and sync it to the customer and product records we have locally.  So now we need two connection strings, and two data providers.

Despite the complexities (perceived or not), the basics of connectivity to an Oracle database are relatively simple.

Once you’ve gone through the following 3 easy 🙂 steps:

1). Obtain the Connection String:

The connection string you use for Oracle can vary depending on which type of Oracle connection you are supposed to have (or choose) to use. In this example, the client manages access to the Oracle db server and the different db instances. Because of this, they provided a standard file called “tnsnames.ora”.  In this file has a list of Servers and Data Sources on those servers.  Having been given the specific instance name and credentials for project we add this to the web.cofig file in our .Net project

<connectionStrings>

<add name=”AppContext” connectionString=”Data Source=MyAppDbIpAddress;Initial Catalog=uniqueDbName;User Id=sa;Password=SuperSecretPassword;” providerName=”System.Data.SqlClient” />

<add name=”OracleContext” connectionString=”Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MyOracleDbIpAdress)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DbServiceName)(INSTANCE_NAME=DbInstanceName)));User ID=myUserId;Password=superSecretPassword;” providerName=”Oracle.ManagedDataAccess.Client” />

</connectionStrings>

These two entries into our config file will allow us to access these connection strings from anywhere within the project.

For a complete list of connection string options look here : http://www.connectionstrings.com/oracle/#microsoft-ole-db-provider-for-oracle-msdaora

2). Get the proper DataProvider Library:

Once you have the proper connection strings, we need a data provider library that will help us to communicate with the database from our application.  As seen above the “AppContext” is using “System.Data.SqlClient version 4.0”, this is Visual Studio’s default data provider (to be used with mssql database of course).  As mentioned above, when it comes to the “OracleContext” we are in a managed database environment. Therefore we have installed the “Oracle.ManagedDataAccess version 4.12.10” data provider library.  This library can be found, downloaded, and installed using the “Manage NuGet Packages…” option when you ‘right-click’ on the References folder in your Solution Explorer.  Once you have data provider library installed you will have to add a few parameters to your web.config file in order for you application to recognize the provider.

<system.data>

<DbProviderFactories>

<remove invariant=”Oracle.ManagedDataAccess.Client”></remove>

<add name=”Oracle Data Provider for .NET”

invariant=”Oracle.ManagedDataAccess.Client”

description=”Oracle Data Provider for .NET”

type=”Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess,

Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342″ />

</DbProviderFactories>

</system.data>

Now we should be set. We have the information we need to connect to the db, and the code libraries to communicate with the db.  Now we just need to communicate.

3). Query the database:

You should be able to query the database using standard sql commands.  You can select, insert, update and delete (although not recommended) from any table you have access to.  You can even call stored procedures.  How do I do this? I’m glad you asked.  In this simple example we’ll just get a list of tables we have access to (if any).

Depending how you have your project framework set up you will can use different methods to call to the oracle database.  Here we will just call from the controller.  Atop of the controller we need to make sure we have this line – “using Oracle.ManageDataAccess.Client;”

This will give us the accessibility to the provider libraries that we just downloaded and configured.

Now within our method of choice we will create a list to store our results (for more complicated queries we would create a custom model to hold the data we want).

List<string> results = new List<string>();

Next we will establish the connection and query for table names:

using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings[“OracleContext”].ConnectionString)){

Connection.Open();

String query = “SELECT table_name FROM user_tables”;

using (OracleCommand cmd = connection.CreateCommand()){

cmd.CommandText = query;

cmd.Prepare();

using (OracleDataReader reader = cmd.ExecuteReader()){

while(reader.Read()){

results.Add(reader.GetString(0));

}

}

}

Now we can take those results and use them however we choose.  Like I mentioned before, for more complex queries we would have a model to store the row data.  In that case we would also take a repository approach utilizing the System.Linq library with lamba expressions.  The call might look something like this:

First declare the Repository being used:

private readonly IReposity<Customer> _orcCustomerRepo;

The Repository interface contains basic actions you will use to call to the database, and it is bound to a specific table set. In this case, the repository is specific to the Customer table and will bind the data it receives to instances of the Customer Model we’ve created.

If we want to find all customers that are based in Texas we can easily gather a list of customers using a simple LINQ (Language-Integrated Query) to SQL command.

Our call to the db will look something like this:

var results = _orcCustomerRepo.Table.Where(c => c.CustState.Equals(“TX”)).OrderBy(c => c.CustName);

This will return the set of results where the Customer State is equal to Texas and will order the results by customer name.  Now as before we can return this list to the view and use it however we wish.

There you have it! 3 overcomplicated processes easy steps to oracle database connectivity.

Ayoka is a Made in USA enterprise application services company with one clear objective: delivering the best customer service to all of our clients.  Ayoka’s commitment to Made in USA custom software development ensures that our client’s culture is understood, objectives are clearly communicated and allows us to provide tangible advice to our clients that are building custom enterprise applications that are essential to operating their modern business.