Visual Studio Hacks

208

|

Chapter 6, Speed Hacks

#53 Create Connection Strings Quickly

HACK

Sub Macro1( )

Dim var As String = InputBox("Enter enum name")

DTE.ActiveDocument.Selection.NewLine( )

DTE.ActiveDocument.Selection.Text = _

"private void Record" + var + "( int value )"

DTE.ActiveDocument.Selection.NewLine( )

DTE.ActiveDocument.Selection.Text = "{"

DTE.ActiveDocument.Selection.NewLine( )

DTE.ActiveDocument.Selection.Text = _

"BusinessLogicCall.RecordEnum( value, OurEnum." + var + " ) ;"

DTE.ActiveDocument.Selection.NewLine( )

DTE.ActiveDocument.Selection.Text = "}"

End Sub

Resulting code. In this example, you now have a macro that is useful for gen-

erating new versions of the Record function when new items are added to

the

OurEnum enumeration. This can be useful when providing a Business

Layer interface in which you wish to provide a distinct function for each

value of the enumeration.

If you type

NewValue into the InputBox prompt, the macro will create the fol-

lowing function:

private void RecordNewValue( int value )

{

BusinessLogicCall.RecordEnum( value, Enum.NewValue ) ;

}

—Ben Von Handorf

HACK

#53

Create Connection Strings Quickly Hack #53

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:

1. Open the Macro IDE.

2. Create a new module and name it ConnectionStringMacro.

3. 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)

Create Connection Strings Quickly #53

Chapter 6, Speed Hacks

|

209

HACK

If cn.connectionstring = "" Then Exit Sub

Dim sel As TextSelection = ActiveDocument( ).Selection

sel.Text = cn.connectionstring

End Sub

4. 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 Proper-

ties 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 to—the

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 user-

name and password, and then select the database that you want to connect

Figure 6-16. Data Link Properties dialog—Provider tab

210

|

Chapter 6, Speed Hacks

#53 Create Connection Strings Quickly

HACK

to. You will also need to check the Allow Saving Password checkbox; other-

wise, 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 need-

ing 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.

Figure 6-17. Data Link Properties dialog—Connection tab