One of the things I find that a lot of people have issues with are database Connection Strings.
Connection Strings aren't very intuitive, and when a database connection or query doesn't work, it can be quite a pain to figure out what the issue is. I was hunting around for a tool that would let me do what I wanted. Insert a connection string, select the data provider I want to us (eg. SQL or Oracle etc) and run a simple query. If it fails, let me know what the error is. If it doesn't fail, I should see some data returned.
I searched online and a few popped up but they seemed to crash, or have these annoying popups with ads and requesting you buy their product. I have nothing against selling or buying products, but the idea I had was super simple so I thought I'd just whip up a tool for myself. Then I thought....I might as well share it as I'm sure others would find it useful.
Thanks to .Net, computers have certain data providers installed. SQL Server Client, OLE and even Oracle. My need was to be able to create a connection string and store it for later. I can then try it, add a query and see if it will work.
Fairly straightforward purpose for this app right?
As you can see, it's a easy app with not too many options to choose from.
The application will start with no default connection strings. What you need to do, is first give it a name. Use something that will make sense to you. From the provider drop down, select which provider you want to use. The put in your connection string.
Now that you have that, click on the Update button and that will save your connection string information to the application configuration file. Next time you run the application, your connection string information will still be available to you.
Assuming you believe this information has been entered correctly, come up with a simple SQL Query. You can click on the Run and it should connect to your database and run the query. If there is an error of any kind, you'll see a popup message box. If it works successfully, the data will be shown in the grid (dark grey box).
Delete button - this will delete the selected Saved Connection String. New button - this will clear out the Name, Provider and Connection String. Update button - if it's a new connection string, it will be added to the Saved Connection Strings. It's an existing connection string, it will update it (as long as the Name remains the same).
What if it fails?
If your connection string is incorrect (eg. wrong server name), then you'll get a popup that looks like this:
On the other hand, if the connection string is correct, but your Query is not (eg. quering a table that doesn't exist), you'll get something like this:
Can I use this to help with any Nintex Workflow issues?
The Execute SQL action is a great Nintex Workflow action. It allows you to query databases of different types, and lets you do SELECT, INSERT, UPDATE and DELETE queries. It also lets you run Stored Procedures.
Sometimes you will configure a Connection String and a query, but when you run it, it fails for some reason. One example of this, is if you are connecting to an Oracle database. You can select Oracle as the data provider, put in a perfect connection string and query, but it fails. Why?
Turns out you need to have the Oracle client installed. Not on one SharePoint farm server, but every server a workflow can potentially run.
So in this situation, you could take this tool, put it on one of your farm servers and try different connection strings and different queries until you get what you need.
There is a great site called Connection Strings which has a number of connection string examples for different databases and different database versions. This site has saved me a bunch of time.
Most of the code was put together from parts I found on MSDN. That's why I decided to make the source code also available. Feel free to download it and make changes for your own benefits. This is a tool to help everyone. There's nothing special in the source code.
Files to Download
Connection String Tester Executable v1.0 - Download
Connection String Tester Source Code v1.0 - Download