Creating and Managing Database Connections


The Server/Database[8] Explorer is the gateway to the data sources (databases) your application needs to access. This means you'll need to create a "Database Connection" to the SQL Server instances and specific databases or other databases. Each Database Connection refers to a specific database and login credentials. Behind the scenes, it's persisted as a unique connection string. You won't be able to create duplicate Database Connections, but you can connect to the same database using different credentials.

[8] As I progress through this chapter, I'll refer to the "Server" Explorer instead of Server/Database Explorer. I'll try to point out features not supported in the Database Explorer.

I've already shown how you can create a local file-based (private) Database Connection by adding a "SQL Database" item to your project, so let's walk through the process of creating a new Database Connection that addresses a remote or shared instance of SQL Server on your local system. No, you can't do this with the Express edition.

1.

Expose the Server Explorer by clicking View | Server Explorer. Initially, there won't be much there, as shown in Figure 4.31. I need to point to a specific data source so the explorer can drill down into its contents. Each "Data Connection" points to one (and only one) database located on the specified data provider (data source). You'll be able to create as many data connections as you needeach pointing to a different database.

Figure 4.31. The unconfigured Server Explorer (Professional and Team editions).


Each Data Connection contains a unique connection string that can be altered later (if necessary) to address another database, use different credentials, or set additional connection options. Depending on the rights granted to this connection, you might be able to alter the database structure (add/change/remove tables, stored procedures, views, etc.) using the Server Explorer. I'll show you how later in this section.

The Data Connection plays an important role in all of the Visual Studio 2005 wizards. Each time you're asked to provide a ConnectionString, Visual Studio returns a list of the current Data Connections to choose fromthis can save you a lot of time.


The "Servers" icon is not exposed in the Standard or Express editions. I discuss this feature later in this chapter.


2.

Since you need to point to a data source to activate the Server Explorer, right-click on the "Data Connections" icon and choose "Add Connection". You should now see the "Choose Data Source" dialog, shown in Figure 4.32.

Figure 4.32. Choose a Data Source provider.


This list is reduced to two items in the Express edition: SQL Server and Access database providers. You won't be able to set up connections to specific local or remote SQL Server instancesjust to the SQLEXPRESS instance via a privately attached (User Instance) .MDF file or a JET (.MDB) file.


Choose the data provider[9] that makes sense for this connection. If you plan to attach an existing .MDF SQL Server database file, choose "Microsoft SQL Server Database File". I discuss the implications of this approach in Chapter 9. For an existing server instance (and database), choose "Microsoft SQL Server".

[9] The Express editions expose only two providers: one for SQL Server database files and another for Access/JET database files.

3.

The next dialog you see (see Figure 4.33) should be familiar by nowyou worked with it when you created the ConnectionString for your application Template. Fill in the machine and instance name ("Server name") and authentication credentials ("Log on to the server"), and choose an initial catalog ("Select or enter a database name:") from the drop-down list. If the dialog can't connect to the selected server instance, you'll get an exception. While you can enter a default database, if the credentials or other issues prevent you from connecting, you're wasting your time. As I've said before, connecting can be one of your toughest hurdles, so you might find it necessary to review Chapter 9 before going on.

Figure 4.33. Using the Add Connection dialog to build a ConnectionString.


Before you can choose SQL Server Authentication, your SQL Server instance must be configured to accept SQL Server credentials. By default, SQL Server instances are configured to accept only Windows authentication (SSPI) credentials. This means the credentials you used when logging on to Windows are used to gain access to the SQL Server instance. I also discuss how to configure SQL Server instance options in Chapter 9.

4.

Click on the "Advanced" button. This exposes the Advanced Properties property page dialog (see Figure 4.34). A number of important properties should be set here. Again, I discuss each of these properties in Chapter 9, so let's set some of these to give you an idea of how to set them:

  • Application Name: I set the Application name to make it easier to find references to this connection in the SQL Server Profiler. It's an essential tool to determine what SQL Server is being asked to execute and to debug all kinds of SQL Server performance and functionality issues. I expect to write an EBook on use of the Profiler at a later date. Check the support Web site for details.

  • ConnectionTimeout: I set this to 5 seconds, as most LAN-based connections can connect in less than 2 seconds. Setting this to a higher number just wastes your timeof course, if you're being paid by the hour....

  • Enlist: I set this to False because only a few of our connections used in Visual Studio require the use of transactions.

  • Max Pool Size: I set this to 10 to trigger pool overflow much earlier as I test prototype applications.

    Before you click "OK" on the Advanced Properties dialog, note that your configured ConnectionString is shown in the (editable) TextBox at the bottom of the dialog. You can try to copy from this TextBox if you want to use the ConnectionString in your code, but (by default) if you are using SQL Server credentials and did not specify "Save Password" in the Add Connection dialog (see Figure 4.33), the password is not exposed here.

    Click the Test Connection button in the Add Connection dialog when you're ready to verify that your ConnectionString is correct. Actually, if the drop-down of known databases works, it's not really necessary to test the connectionbut go ahead if it makes you feel more secure.

Figure 4.34. Setting ConnectionString properties in the Advanced Properties property page dialog.


5.

Once you feel comfortable with the settings, click "OK". This creates a new Data Connection in the Server Explorer, as shown in Figure 4.35.

Figure 4.35. The Server Explorer with a new Data Connection.


You're now ready to explore the schema of the database addressed by this new Data Connection. Note that when you first start Visual Studio, the Data Connections you have installed are not opened. Unopened Data Connections are shown with a small red X next to the icon, as shown in Figure 4.36. However, once connected, these Data Connections hold a connection open until Visual Studio shuts down. To open the Data Connection, click on the "+" next to your Data Connection.

Figure 4.36. Data Connections do not open when Visual Studio first starts.


After a brief wait, the Data Connection is opened and the Server Explorer is populated with the objects that you can browse and edit (in some cases). As shown in Figure 4.37, the Server Explorer exposes lists of objects, including Tables, Views, Stored Procedures, and other objects.

Figure 4.37. Setting Data Connection options.


A new feature for Visual Studio 2005 (and SQL Server 2005) permits developers to view the objects in the Server (and Database) explorer by object type or by "schema". By default, objects are listed as in previous versionsby their type. If you right-click on a Data Connection (as shown in Figure 4.38), you can choose to view the objects by schema. As I discussed earlier, a schema is not just the way that the tables are laid out in a relational model; it's also (in SQL Server 2005) how the objects are owned. That is, given the Server Explorer options, you can choose to view the objects exposed by the Server Explorer by the user (Schema) or the object that owns them.

Figure 4.38. Choose how the objects are displayed in the Server Explorer (Team Edition).


Once you choose the schema view, the list of objects visible from the connection is a function of the schema chosen. Choose the schema view (as shown in Figure 4.38) and drill into the list of Schemas by clicking on the "+" next to the Schemas icon. Next, choose a schema (I chose "dbo"), and a list of all of the Tables, Views, etc. visible to this selected schema is listed (as in Figure 4.39).

Figure 4.39. Drilling into the objects exposed to a specific schema.


Note that the Data Connection menu also permits you to modify the existing Data Connection, as well as set several options (as shown in Figure 4.38):

  • Refresh: This tells Visual Studio to requery the connection to refresh the object lists. This might be needed when you add, change, or delete the objects (as when adding new stored procedures or tables).

  • Delete: This removes the connection from the list of registered Data Connections. This has no impact on existing databasesattached databases remain registered in the server's master database. Note that Visual Studio won't let you create duplicate Data Connections, but you can have several connections pointing to the same server and databaseit's just that the ConnectionString must be different for each one.

  • Modify Connection: This opens the dialog (similar to Figure 4.33) that permits you to alter the ConnectionString.

  • Close the Data Connection: This might be necessary to release the connection held by the connection pool. Note that closing the Data Connection does not close the server connectionat least, not immediately. You'll have to wait from 4 to 8 minutes for the connection pooler to release it.

  • New Query: This opens the Query Designer, as discussed later in this chapter. This tool is helpful when you need to build a block of T-SQL or more closely inspect the contents of a database object.

  • Detach Database: This detaches the selected SQL Server database from the local SQL Server Express instance (Express edition only).

  • Application Debugging: Permits application debugging using the Team system (Team version only).

  • Allow SQL/CLR Debugging: This option enables applications to set up CLR debug sessions with this connection. I discuss CLR debugging in Chapter 13 (Team version only).

  • Properties: This exposes the Data Connection property page, shown in Figure 4.40. Note that these properties are read-only but can be copied to extract an important property like the ConnectionString. Note that this Data Connection is marked as not "Case Sensitive". This attribute is set by the default case of the server. To change the ConnectionString property, click on "Modify Connection".

    Figure 4.40. The (read-only) Data Connection properties page.




Hitchhiker's Guide to Visual Studio and SQL Server(c) Best Practice Architectures and Examples
Hitchhikers Guide to Visual Studio and SQL Server: Best Practice Architectures and Examples, 7th Edition (Microsoft Windows Server System Series)
ISBN: 0321243625
EAN: 2147483647
Year: 2006
Pages: 227

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net