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.

Figure 6-16. Data Link Properties dialogProvider tab

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.

Figure 6-17. Data Link Properties dialogConnection tab

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.