Saturday, February 28, 2015

Easy-Peasy Connection Strings

I frequently see questions on the Forums about what a developer’s database connection string should look like. The main site that everyone recommends for that is http://www.connectionstrings.com/ … this is a good site and usually very helpful. The only problem is that sometimes people still can’t figure out what their connection string ought to be. So, here’s another suggestion that’s pretty easy to do.

  • Create a new file text file (the easiest place to put that is on your desktop), and change the extension from .txt to .udl
  • Double-click the file and a “Data Link Properties” window opens. By default it opens to the second tab, the “Connection” tab. If you click on the Provider tab, you will see that it defaults to using the Provider for SQL Server. Change that if you’re not using SQL Server.
  • Back on the Connection tab:
    1. Select a server name from the dropdown list. If you don’t see your server there, click the Refresh button. If it’s still not there, then you’re not getting to it via your network and you won’t be able to test the connection. Optimally, it’s best to do this on a machine that has access to the machine that has the database server on it.
    2. Enter your login info, either Integrated security or username/password (check the “Allow saving password” box to save the password in the connection string … you’ll get a warning about saving the password to a file, but that’s OK) .
    3. Select the database.
    4. You can click the “Test Connection” button if you want to, but if you can see a list of databases in Step 3, then you’ve already got a valid connection.
  • You can still do the steps above (except for #4) without actual access to the database server by typing in the server name and database name without picking from a list, and the connection string will be valid (provided that you’ve hand-typed those values in correctly).
  • There are two other tabs on this window, usually you won’t need them.
    • The Advanced tab has a few more options that you usually won’t need to bother with, but take a look if you wish.
    • The All tab will show all the properties that can go into the connection string, the ones you just entered plus others. Make changes here if you wish (double-click an item to edit it), but it’s usually not necessary.
  • Now that you’re done, click OK to save it.
  • To get the actual connection string, right-click on the file, and “Open with” Notepad. Use the string shown (except that you don’t need the “Provider=XXX” part of it).

That’s it! It’s pretty painless …

Happy coding!  =0)

2 comments:

  1. My company has a dev team for every client and, obviously, these teams would have a lot of overlapping business processes they have to manage. So we have been modularizing our processes into WCF services. The service clients get their configuration from the DB used by the app creating the client. Add to that the transition from several small clusters to a single cluster of IIS and SQL servers behind a secure load balancer, and you can image the growth rate of our connection strings . This tip will save me tons of time updating connection strings as code and date get moved around. Thanks!!!!

    ReplyDelete
    Replies
    1. You're welcome! Glad I could help! =0)

      Delete