This article demonstrates in a step by step fashion the easiest, and frankly fastest way to connect to an Oracle database using C#. The goal is to not have to install the huge Oracle Client either on the development machine nor the target machine the code will run on. This example creates a winform and inserts the content into a DataGridView for quick viewing. The code below is base on .Net 3.5.

Steps

1. Oracle has an Oracle Database Instant Client which is a set of Dlls which can be XCopy installed onto the development and target PC to allow Oracle database access without installing the full Oracle client. In future steps we will include those target Dlls to be copied to the target output folder with the executable. Download the appropriate package and add Dlls to a folder of your choice on the PC.
2. In Visual Studio create a Winform Project. From the Solution Explorer and within the project create a subfolder named Oracle Dlls. Update: This step should not be done, for the dlls will end up being copied into a subfolder of the same name and when running the client an error may come up stating “System.Data.OracleClient requires Oracle client software
   version 8.1.7 or greater”.
3. Add the reference to the project of System.Data.OracleClient to the project..
4. In Studio again select the folder created in step 2 (highlight the project root) and from the right click menu select Add->Existing Item, and insert all the top level Oracle Dlls from step one into the directory into the folder project. Note the below picture shows into a folder, they do not go into a folder but at the root of the project.
   
5. Highlight all the inserted DLLs and select Properties to bring up the properties window. Change the Build Action to be Content and the Copy To Output Directory to be Copy If Newer. This allows the dlls to reside with the created executable program. By doing this it allows the program to run on another computer, as well as this one, that does not have the Oracle Client installed because all the Oracle specific dlls reside with the output executable.
6. On the design view of the form add a button, label, binding source and a DataGridView. The names used for each in the example (Label as lbState, Binding Source as bsOracle and DataGridView as gvOracle).
7. Create in the forms code a method to handle the connection string and add the target Oracle db/instance items (Note: Replace { xxx } including the curly braces the specifics to your db) :

   private string GenerateConnectionString()
   {
      return "Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = ( PROTOCOL = TCP )( HOST = {Insert Host Here} )( PORT = {Insert Port Here} ) ) )( CONNECT_DATA = ( SERVER = DEDICATED )( SERVICE_NAME = {Service Name Here } ) ) ); User Id= {DB ID Here}; Password = {Password Here};";
   }

8. In the button’s onclick event wire up the controls and access the Oracle database as such:

   try
   {
       using ( OracleConnection connection = new OracleConnection( GenerateConnectionString() ) )
       {
           connection.Open();
           lblState.Text = connection.State.ToString();
   
           OracleCommand oc = connection.CreateCommand();
           oc.CommandText = "SELECT * FROM {Your Table Here}";
   
           OracleDataReader reader = oc.ExecuteReader();
   
           bsOracle.DataSource = reader;
           gvOracle.DataSource = bsOracle;
   
           gvOracle.BorderStyle = BorderStyle.Fixed3D;
           gvOracle.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
   
       }
   }
   catch ( Exception ex )
   {
   //  MessageBox.Show( "Exception: " + ex.Message );
       lblState.Text = ex.Message;
   }

9. Run and compile the program and if all goes well it should attach…but there are other possible failure points see the next section as to a couple of them.
   

Note for a more involved explanation of this process check out the article Instant Oracle Using C# which has a console example and does not provide the advice of using the build options in Studio for the Oracle Dlls. That will be our little secret. HTH

Possible Errors Encountered

System.Data.OracleClient requires Oracle client software version 8.1.7 or greater

If this is encountered it could be a permission problem accessing the location where the oracle dlls are…but most likely the dlls are not in the same directory as the executable or found within the environment path of the system.

ORA-12541: TNS:no listener

This one could mean that one of the connection settings is incorrect and the database could not be connected to and this generic error comes back. Try tweaking the settings, port, instance

Tags: BindingSource, DataGridView, Oracle