Visual Studio Hacks [Electronic resources]

Hack 53. Create Connection Strings Quickly

Database connection strings can be tricky and confusing. Use a macro to automatically generate your connection strings.

Connection strings are one of those things that you usually create only when you are starting a new project or if you have to change databases, so it is easy to forget how to write a connection string. Instead of hunting around for an example or documentation, there is a quick macro you can use to automatically generate your connection string using the Data Link object.

To create the macro:

Open the Macro IDE.

Create a new module and name it ConnectionStringMacro.

Copy the following code into the module:

Public Sub InsertConnectionString( )
Dim links As Object = CreateObject("DataLinks")
Dim cn As Object = CreateObject("ADODB.Connection")
links.PromptEdit(cn)
If cn.connectionstring = " Then Exit Sub
Dim sel As TextSelection = ActiveDocument( ).Selection
sel.Text = cn.connectionstring
End Sub

Close the Macro IDE.

You can then run this macro from the Macro Explorer in your project. First, select the place in your document where you want to insert the connection string, then double-click on the macro; you will see the Data Link Properties page. You will then need to select the provider for your connection by clicking on it in the Provider tab shown in Figure 6-16.

From this tab, select the type of data that you want to connect tothe OLEDB provider for SQL Server is selected in Figure 6-16. Next, you will need to specify the details of the connection on the Connection tab, which is shown in Figure 6-17.

From this tab, you will need to choose the server name, specify the username and password, and then select the database that you want to connect to. You will also need to check the Allow Saving Password checkbox; otherwise, the password will not be included in the connection string. Don't worry, your password won't be saved anywhere other than in the generated connection string. When you click OK, the following string will be inserted into your document:

Provider=SQLOLEDB.1;Password=apppassword;
Persist Security Info=True;User ID=applogin;
Initial Catalog=AdventureWorks2000;Data Source=JAMESA-TABLET

Using this macro, you can quickly create connection strings without needing to research the correct syntax. Thanks to Roy Osherove who published this macro on his blog, which can be found at http://weblogs.asp.net/rosherove.