If you are having trouble connecting to a database using a program that uses an ADO.NET connector (which is pretty much every C# and .NET app), this program is a great way to test it step by step. There is no license for this code - do anything you want with it.
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:
- 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.
- Enter the connection string.
- Click Connect.
- 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:
- You do not have the ADO.NET connector installed on your system.
- In the case of Oracle, it can also be that the Oracle client software is not properly installed on your computer.
- You do not have rights to access that database.
- 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 kailua.dll is included in the above downloads.
- Under "List Tables", select which mode (User Owned has meaning only with Oracle).
- Click Make It So (yes, I was in a Star Trek frame of mind when creating the program).
- The table to the right should list all tables, Views, and Stored Procedures.
If you are not getting the metadata, it is usually because:
- 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.
- Your login does not have rights to access that database.
Finally comes pulling data from the database. To run a select:
- Click the Select tab.
- Enter your Select below "Enter Select".
- Click Engage.
- 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:
- Your Select is not valid. In that case, read the documentation on the Select syntax for your vendor.
- Your Select is not allowed. This comes back to your credentials again.
No Code Listed
I didn't show the code in this article because it is, in my opinion, uninteresting. This is a useful tool (it has helped us a lot), but the value is in using the tool, not the code itself.
Now, the code in the Kailua Project is very interesting (or more to the point, the Selects in it are). And I will write articles about that in the future and post a link here when I do.