SQL Reports

All Things Being SQL – SQL Reports and SQL Reporting Resources

How to easily test your ADO.NET connections

If you are having trouble connecting to a database using a program that uses an ADO.NET connection (which is pretty much every C# and .NET app), this program is a great way to test it step by step. First off, you can download the program at DatabaseTester.zip and you can download the complete source at DatabaseTester-src.zip. There is no license for this code – do anything you want with it (although we would appreciate it if in return you place the link Awesome Reporting Software on your blog or website).

.NET connection

Note: We rarely use this ourselves as we have numerous development tools to accomplish the same thing. Where this is invaluable is sending this to our customers who are not developers and do not have any tools close to hand that can do this.

Opening a Communications Channel

 

Ok, step 1 is finding a connection string that works. For this connection strings is your best friend. With the connection string you then:

  1. Select the vendor you are trying to connect to. DB2, MySql, and/or Oracle will not be listed if their ADO.NET connector is not correctly installed on your system.
  2. Enter the connection string.
  3. Click connect.
  4. The status at the bottom will either say Connection successful! Or it will give you the error that occurred when trying to connect.

If the connection fails it is generally due to one of three issues:

  1. You do not have the ADO.NET connector installed on your system.
    1. In the case of Oracle it can also be that the Oracle client software is not properly installed on your computer.
  2. You do not have rights to access that database.
  3. The database is not accepting connections. This can be due to both a firewall as well as the database itself.

Retrieving the Metadata

 

This part makes use of The Kailua Project – the forgotten methods in the ADO.NET API. This is open source and the kailua.dll is included in the above downloads.

  1. Under “List Tables” select which mode (User Owned has meaning only with Oracle).
  2. Click Make It So (yes I was in a Star Trek frame of mind when creating the program).
  3. The table to the right should list all tables, views, & stored procedures.

If you are not getting the metadata, it is usually because:

  1. Your login does not have rights to retrieve metadata (this restriction usually only occurs with DB2 and Oracle). If you think this is the problem, go to “Retrieving Data” below and try a select. If the select succeeds and metadata fails, then it is a restriction on your credentials.
  2. Your login does not have rights to access that database.

Retrieving Data

 

Finally comes pulling data from the database. To run a select:

  1. Click the Select tab.
  2. Enter your select below “Enter Select”.
  3. Click Engage.
  4. You will see the results in the table below the select.

If this does not work read the message in the error box at the bottom. Problems here fall in two categories:

  1. Your select is not valid. In that case, read the documentation on the select syntax for your vendor.
  2. Your select is not allowed. This comes back to your credentials again.

5 comments

  • Nice post about connection test. I agree with you. Before we can get data from database, I think we should test the connection first for anticipating connection error. Thank you for sharing.

  • accurate analysis of database systems, with this software is expected that we can improve the speed of data access and data search is so big. Greetings

  • so for i am not aware to use ado net connections but after reading this blog and the various steps and the easy ways to test our ado net was simply superb….especially the way it explains was simply outstanding

  • If a connection exists to a server that has disappeared, it is possible for this connection to be drawn from the pool even if the connection pooler has not detected the severed connection and marked it as invalid. When this occurs, an exception is generated.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>