| Note that Visual Studio .NET 2005 includes a new "Data Source" paradigm that can build a Visual Studio Data Source to point to any source of data, including databases, data "objects", and Web Services. I covered the Data Source Configuration Wizard in detail in Chapter 6, "Building Data Sources, DataSets, and TableAdapters," but in case you simply want to open a SqlConnection object against a specific database and aren't sure how to build the ConnectionString, you can simply create a new Data Connection in Visual Studio using the Server Explorer[18] and leverage the ConnectionString built by Visual Studio. The list of Data Connections exposed by the Server|Database Explorer are referenced throughout Visual Studio when referring to specific Data Sources. Note that creating a new Data Connection does not add any code to your project, but the Data Connections are persisted, so each time you start Visual Studio .NET, the Data Connections list is repopulated. Yes, referencing a Data Connection can add code to your application, as the ConnectionString it contains is leveraged to create SqlConnection objects behind the scenes. [18] The "Server Explorer" is supported only in the "Professional" or better editions. In the "Standard" or "Academic" versions, some of the described functionality is exposed in the "Database Explorer."
The following steps walk you through the process of creating a new Visual Studio Data Connection. 1. | Click on the View | Server Explorer. This exposes the dockable Server Explorer tab, shown in Figure 9.25. Click on the icon shown to add a new Data Connection.
Figure 9.25. The Server Explorer tab. 
Tip Remember, we discussed the Server|Database explorers to manage T-SQL executables in more detail in Chapter 5, "Managing Executables with the Server Explorer." | | | 2. | Click on the "Change..." button in the Add Connection dialog, as shown in Figure 9.26. Once you choose where the data is to be sourced, you can choose the appropriate .NET Data Provider from the drop-down list shown at the bottom of Figure 9.26. If you check the box "Always use this selection", Visual Studio defaults to the chosen Data source when new Data Connections are created. When you choose ".NET Framework Data Provider for SQL Server", you're choosing the SqlClient .NET Data Provider.
Figure 9.26. Change Data Source for new Data Connection. 
| | | 3. | Once you choose a Data source, the Add Connection dialog morphs to the property requirements of the chosen provider. When working with SQL Server, the dialog captures the Windows server name that's hosting the instance of SQL Server you wish to access. You can also choose a server/instance name from the drop-down list (assuming the server and instance you need to access are visible on the Net). Figure 9.27 shows the list of visible servers and instances as populated. If the server is not visible, try clicking on the "Refresh" button. Be patient when clicking the down arrow or clicking "Refresh"the routines to locate SQL Server instances are not only slow, but they're lazy and don't always see available SQL Server instances.
Figure 9.27. Choosing a server and SQL Server instance name. 
| 4. | Next, choose the type of authentication your connection expects to use. If you choose "Windows Authentication", the currently logged-on Windows user's (usually yours) credentials are passed to SQL Server to connect. If you have mixed mode authentication enabled and choose "Use SQL Server Authentication", you'll need to provide a valid Login ID (User name) and password. In the latter case, you can choose to expose these Login credentials in the ConnectionString by checking the "Save my password" check box.
| | | 5. | Once the authentication options are set, you can click the down arrow to choose a default database. If you don't choose a database name here, the default database is set to the default database set by the DBA when your Login ID was created. If your account is a member of the administrator's group, you'll likely be addressing the master database (not good). If the list does not populate or you get an exception when you click on the drop-down list down arrow, there is a problem with your credentials or the configuration of the server visibility (as discussed earlier in this chapter).
An alternative to choosing a database that's already managed by SQL Server is to point to an attachable .MDF database file. You can browse for that file using the "Browse..." button, as shown in Figure 9.27. The "logical name" is the name of the database contained in the .MDF file.
| | | 6. | Click the "Advanced..." button to open the Advanced Properties dialog, shown in Figure 9.28. This dialog can be used to view or change any of the ConnectionString properties. I recommend that you set the "Application Name" property to uniquely identify your application so it will be easier to find when using the Profiler. Note the entire ConnectionString is shown at the bottom of this dialog. If you right-click on this string and Select All, you can copy the ConnectionString to the clipboard and paste it into your application. Click "OK" to save your changes and return to the Add Connection dialog.
Figure 9.28. Inspecting and setting the ConnectionString properties with the Advanced Properties dialog. 
| 7. | Click "OK" on the Add Connection dialog to create the new Data Connectionit should appear in the list of Data Connections in the Server Explorer. If the ConnectionString is the same as an existing Data Connection, Visual Studio ignores the "new" Data Connection.
| 8. | Once the Data Connection is created, you can right-click on the icon and click "Properties". This exposes the simple property page for the Data Connectionincluding the ConnectionString. This can be selected with Ctrl+A and copied to the clipboard with Ctrl+C.
|
The SqlConnectionStringBuilder is currently configured to set the MARS keyword to true. While Microsoft promised to fix this (the default is supposed to be false), you might want to double-check this feature before committing the ConnectionString.
|
