Oracle Connection Tester for .Net Applications

I wrote a small utility that will allow you to test connections to an Oracle database server in a .net environment.  Many times you can setup an oracle connection from a client machine and it works when you connect with SQL Plus or tnsping.exe but when you try to utilize from .net Applications issues arise.

*Updated – added support for both 32 and 64 bit data providers.

You can download the utility here:

32 bit – for use with 32 bit Oracle Data Provider Clients

OracleConnectionTester_x86.zip

64 bit – for use with 64 bit Oracle Data Provider Clients

OracleConnectionTester_x64.zip

The zip file includes the executable and Oracle Data Access Library (10.2.0.2).

Instructions for Usage

1. Launch the application
2. Select the data provider for .Net you wish to test (Microsoft or Oracle)
3. Select the TNSNames Entry from the drop down menu
4. Enter a valid username
5. Enter a valid password
6. Click Test to test the connection

A message will appear indicating the success or failure of the test.  If the test fails – it will give the exception details.

Note that the test consists of the following

1. Opens a connection to the server
2. Runs a command (“select * from dual”)
3. Closes the connection

How to install Oracle 10g2 for use with Windows Vista and Server 2008

Installation Oracle on Windows Vista x86 and x64

Note: The following is assuming you are using the Oracle Data Provider for .Net version 10.2.0.2 or 10.2.0.2.21.

The following process takes between 45 – 75 minutes based on machine speed and available memory.

Part 1 – Install 10.2.0.3 Client Software

1. Download the 10.2.0.3.0 Oracle Client installation software

2. Extract the installation

3. In the client folder right click on setup and select “Run as Administrator”. If prompted by UAC allow access.

4. On the welcome screen click Installed Products. There should be no installed products. Click Close on the Inventory form.

5. Click Next to continue

6. Select Runtime (443MB) for the installation type and click Next

7. Change the Destination Details if desired (not required) and click Next

8. All prerequisite checks should pass. Click Next

9. Click Install to begin the installation. If prompted during the installation in regards to a Windows Firewall rule – select unblock to continue.

10. Click through any prompts received to complete the installation. No additional input is required.

11. When the installation is complete the tnsnames.ora file must be configured. This can be copied in from another area or setup using the NetManager application. The default location for this file is C:\oracle\product\10.2.0\client_1\NETWORK\ADMIN

12. To confirm the Oracle Client can reach the Database server perform the following

a. Open a command prompt (Start – Run – CMD)

b. Run the following command tnsping servicename (tnsping orcl11g)

c. A response should return with a time in milliseconds from the Oracle server confirming the connection is valid.

Part 2 – Install the 10.2.0.2 Data Provider

Note : For Windows 2008 Server you must use the 10.2.0.3 installation setup program and point to the 10.2.0.2 directory for the setup xml file to launch. Please refer to the following link for more information.

http://www.oracle.com/technology/software/tech/windows/odpnet/install10202.html

1. Obtain the 10.2.0.2.21 ODAC data provider

2. Extract the installation

3. In the client folder right click on setup and select “Run as Administrator”. If prompted by UAC allow access.

4. Click Next to continue

5. The default setting Oracle Data Access Components 10.2.0.2.21 should be selected. Click Next

6. Under Specify Home Details you MUST choose the existing Oracle home in the Name: drop down menu. Otherwise you will have two separate installations which will not work.

7. If the oracle home selection was performed successfully you should see a screen similar to below with Install Status for some items listed as new install and some as installed.

8. Click Next to continue and then Next again

9. Click Install to complete the installation

Part 3 – Patch to 10.2.0.4 if on a 64 bit (x64) system

If you are running a 64 bit operating system then the following steps must be performed.

1. Download the 10.2.0.4 Patch Set – p6810189 (Oracle MetaLink Account Required)

2. Extract the installation

3. In the Disk1 folder right click on setup and select “Run as Administrator”. If prompted by UAC allow access.

4. Click Next

5. Ensure the same Oracle Home is selected

6. Click Next

7. On the Product-Specific Prerequisites page Click the box to mark the Home incompatibilities as user verified.

8. Click Next and Next again

9. Click Install to complete the installation

Get Identity after SQL Insert Command

The following code will allow you to execute an insert command and retrieve the identity of the inserted record in a single method. The use of (ID = SCOPE_IDENTITY()) will prevent any triggers from providing incorrect ID results that can be obtained when using @@Identity.

public static int ExecuteIdentityInsert(string connection, 
  string commandtext, string tablename, 
  Dictionary<string, string> parameters)       
{
SqlConnection con = new SqlConnection(connection);            
string getIdentityCommandText = (";SELECT ID FROM " + tablename +
  " WHERE (ID = SCOPE_IDENTITY())");
Object result;            
using (con)            
{               
con.Open();                
// Add query to retrieve inserted command id                
SqlCommand com = new SqlCommand(commandtext+getIdentityCommandText, con);                
if (parameters != null)                
{                    
 foreach (KeyValuePair<string, string> pair in parameters)                    
 {                        
 com.Parameters.AddWithValue(pair.Key, pair.Value);
 }                
}                
try                
{                    
 result = com.ExecuteScalar();                                    
}                
catch (Exception ex)                
{                    
 throw;                
}            
}            
return Convert.ToInt32(result);  
}

Data Comparison Tool

In the course of my job I have to validate numerous amounts of data. We have many processes that transfer data between databases to provide flexibility for our clients. In doing so we must support MS Access,MS SQL, and Oracle. Due to the complex nature of this task and the differences in how these database engines perform – it is very difficult to ensure they are all acting the same. So I have identified the problem – and developed a custom solution for it.

The outcome of this is a custom application that uses our existing DAL to compare two databases (regardless of platform). Some of the logic from this tool was based on an existing tool we had to compare single queries.

Setup:

1 Solution, 3 projects, and NUnit.

I wrote the main class as a DLL that can be used with a form, console, or integrated into an existing application. To test this – I used NUnit. This turned out to be the best method. With the console output available in NUnit and this being primarily a testing tool – I left the other options for later and just use NUnit to run my tests. I love it when I see green!!!

How it works

First it gets a list of tables from each database and compares them
Then it compares the rows counts for each table
If that passes, then it compares the row data. This is the tricky part. Since Access, SQL, and Oracle all handle identity inserts differently, it’s hard to just sort on ID. To handle this I sort and attempt a compare on each column (in a data table in memory) for each table. I then walk through each data row and compare the contents. There are some fall back checks to handle other differences such as how numbers and bits are handled between the platforms.

In the end – I can move data from one platform to another, perform the same functions in each database, and then compare the databases very quickly with high confidence.

For a database with 300 plus tables I can run a complete test in under a minute with the databases in the same office.

Time Saved:

Before App – 30 minutes per test with less than 50 percent coverage
With App – 1 to 2 minutes per test with 100 percent coverage.

Horraaayyyy for automation!!!!

Oracle Database Management Tool – New and Improved

FINISHED!!!!

Over the past couple of months I have been working on an Oracle Management tool to help manage Oracle Database Servers in a Windows environment. The tool morphed into a decent sized application with over 10,000 executable lines of code. It is written entirely in C# utilizing the ADO.NET classes for data access.

Upon opening the application a login screen is presented. The database connections are populated automatically from the tnsnames file being used by Oracle.

The application has the concept of roles which are determined based on what system privileges a user has on the Oracle Database server. These roles determine what features are available in the management tool.

Below are the features of the application:

Query Editor

This area lets a user execute standard SQL queries against any user with parameters. The results come back as an output window (number of records affect, returned, updated, etc) and select statements return a data grid.

The ability to add parameters is also available. Parameters are detected automatically using the : or @ designated syntax.

Create User

Here one can create a user very quickly by typing in the user’s name, password, selecting a tablespace and give the user roles. The radio buttons on the left are the most common used in our environment and automatically select the related oracle system privileges. The privileges are loaded from the oracle database dynamically. There is validation for the username and password (existing, length, character usage).

Edit User

Edit user is similar to create user. Here one can change a user’s password, default tablespace, roles, and view the current roles / privileges assigned to a user.

Delete User

Delete user allows one to remove one or more users at a time. Due to the cascading options with oracle users this can be time consuming so the ability to select – click delete – and leave to complete is extremely useful as a time saver.

Import Dump File

Importing dump files for Oracle can be time consuming and painful. There are many requirements to importing a dump file and syntax rules for command line options that must be followed. To make this process easy now one must create a user via the Create User feature, browse to a dump file and click import.

This feature is only available when the application is run directly on the Oracle Server as it requires the Import Utility provided from oracle (imp.exe).

There are certain checks in place to ensure this will work without errors occurring. There is also a log file created. It is scanned after the import has completed and prompts a user to open it if errors are detected. It will not let you import if there are tablespace conflicts and will notify you of what needs to be changed.

Export Dump File

Exporting a dump file also must be run directly on the database server. This option exports the dump file with standard options enabled and then opens up the file location as to where the dump file / log file were created.

TableSpace Management

The tablespace management section lets you create tablespaces, view details about the tablespaces, and add datafiles to the tablespaces.

Create Tablespace

This feature makes creating tablespaces a breeze. This isn’t a widely used feature but will be helpful for development groups. Viewing tablespace data and adding datafiles also make tablespace management simple but effective. Note that all naming for data files is automatic and conforms to oracle standards.

System Information

System information provides a basic snapshot of the host operating system as well as the database server OS environmetn information. The parameters sections (Get Buttons) return parameters from the Oracle Database server.

Help

There is a help file in .mht format that describes each feature in detail as well as provide common Oracle best practices, troubleshooting methodology, and Oracle Database logical design.

Retrospective

This was my first major development project. While I bounced some conceptual ideas off some of my colleagues I did not seek any assistance in writing the code. This was a great learning experience for me. I am now extremely comfortable using C# / Winforms / ADO.NET. I gave a demo of my product to a development team (a group of highly experienced developers). They were all pleased with the tool and provided great feedback for me to use moving forward. The hardest part for me during the development of this tool was to find the time to do it. I started the tool on November 22nd and finished it January 28th. Not too bad considering I took two weeks off in December.

My next project:

Create a new tool the facilitates an automatic daily creation of multiple databases across multiple database platforms for testing and development purposes. I already wrote one of these programs a few months ago but it needs additional features. After looking at the code I wrote then – I realized it would be better to start fresh and create an application worthy of upgrading and progressing. This tool is about 30 percent complete and has the following major sections:

• Customized data model (database) created using Visual Studio Database Edition (Data Dude) with 16 separate tables all with strict foreign and unique key constraints
• UI Management tool for setting up database creation plans
• Console Application to execute the database creation plans
• Unit Tests (of course we can’t forget these!!!!)

Once finished – I will post a snapshot of the UI and a retrospective of this process.

Oracle Manager

Over the past few years I have learned quite a bit about Oracle Databases and the management functions that go along with using Oracle as a back end for applications. Creating users, backups, and settings can be time consuming and confusing for most users. To assist those who do not have the time (or desire) to learn the ins and outs of this I am creating a utility in C# that will automate most of this for a user. Keep in mind this is not intended for any production type of environment. This is geared more towards small development shops where Oracle isn’t the main Database Platform of choice but is still required.

Below is a screen shot of the main page along with the Create New User tab. The main roles needed in our environment are listed in the checked list box (I will probably add additional at a later time).

Imports and Exports:

Importing and exporting dump files from Oracle can be time consuming and very hard to do. It requires multiple steps using multiple utilities. With this tool (installed on the Oracle Server) a user can import a dump file in a few simple steps:

1. Browse for dump file
2. Specify From User
3. Specify To User and Password
4. Click on the import button

This is far simpler than creating the user in SQL Plus or Toad and then importing using a command line utility on the server. Plus it doesn’t require in depth knowledge of Oracle Administration that may be needed.

Basically – it takes all of the guess work out of it and takes stress off of me supporting others. )

SQL Query Tester

Well I took some time off from progamming at home to spend time with the family. Life is going great and my development juices are flowing strong. The balance between family and work is delicate so I had to ensure that remained balanced. I did manage to write another tool I have been needing for a long time. This tool will test a query against Access, SQL, or Oracle. It is written in C# using some ADO.Net libraries. It is pretty stable and I have had some positive feedback from some developers in my group using the tool to write some application queries.

Here is an image of the tool to the left.

Below is the Grid View of the results returned.

This is a simple tool but highly effective. I am still searching for my next project. I am leaning towards a some type of scripting (Javascript, VB, or Pearl).