Accessing Database Resources for This Book Accessing Database Resources for This Book
Creating a Database Connection String
Browsing Database Content with Visual Studio
As a C# developer, you most likely will want to work with database content in your code at some point early in your career. To help you prepare for data-driven C# development, this chapter focuses on the project setup and environment rather than code. Regardless of your level of experience in working with databases, whether you have lots or none, this chapter offers helpful information on how to use the tools in Visual Studio to access database content.
To support the focus of this book on data-driven content, this book uses a database named FoodStore in all chapters where examples require database content. For readers with little to no experience or those who just don’t have Microsoft SQL Server installed, you can use the database that is contained in the FoodStore.mdf file, which is included in the FoodStoreDatabase directory with the download for this book. For experienced database developers who have Microsoft SQL Server installed, the SQL script is also included in a file named FoodStore.sql in the FoodStore directory within the code download for this book. Also, for experienced developers, the entity relationship diagram that shows all tables in this database is included in the appendix.
To communicate with a database from your C# application, the database location, the name, and the authentication parameters are required. These database parameters are contained in a database connection string.
TIP
Determining connection string parameters is not an overly challenging task, but when the connection string is assembled incorrectly, this issue may be difficult to detect. For this reason, whenever possible, using the wizard in Visual Studio to generate the connection string will help you to ensure it is configured properly.
For all database demonstrations in this book, you may choose to use one of two database types:
A Microsoft SQL Server Database File (also known as an .mdf file). Use of the FoodStore.mdf file is recommended for database beginners.
A separate Microsoft SQL Server install. Use of a separate SQL Server install is only recommended for advanced database users who already have a free Express edition or a paid edition of Microsoft SQL Server installed.
Regardless of your selection from these two options, use of either data source type is perfectly acceptable for all code examples in this book.
Example 13-1 Creating a Connection String for Database Development Beginners
Steps in this example show how to add a reference to a database file. In this case, you are going to create a reference to the FoodStore.mdf database file. This example is recommended for database development beginners or advanced database users who do not have Microsoft SQL Server installed separately.
The FoodStore.mdf file is included with the code download for this book. Also in the same directory as this database file you will find a log file named FoodStore_log.ldf. The FoodStore.mdf file references the FoodStore_log.ldf file, so the two files need to be kept together at all times. These two files were exported from a Microsoft SQL Server database that was created with a script that is contained in the FoodStore.sql file. As a developer, you can consume these FoodStore.mdf and FoodStore.ldf files as a package. The result is that you can run your code as if you are working with a live database, but you don’t need to install a separate database application.
To add a reference to the FoodStore.mdf file, first create a console application. Next, right-click the project name in the Solution Explorer, click Properties, and choose the Settings tab on the left. In the Settings panel that appears on the right, the following message may display as a hyperlink: “This project does not contain a default settings file. Click here to create one.” If so, click the hyperlink. Under the Type column, choose the Connection String option in the drop-down menu. Under the Scope column, select Application if this choice isn’t already selected (refer to Figure 13-1).
Figure 13-1 Setting the connection string from the project Settings panel
Next, click in the Value column to display an ellipsis (…) button. Click this button to launch the Connection Properties dialog. Click the Change button to select Microsoft SQL Server Database File, since you are reading from the FoodStore.mdf file. Then click OK after you have selected the file option. Next, click the Browse button to launch a dialog that lets you navigate to the FoodStore.mdf file. Select the file. Leave the default Use Windows Authentication option selected (see Figure 13-2).
Figure 13-2 Connection Properties settings for a SQL Server Database File
Next, ensure your connection is valid by clicking the Test Connection button. A dialog should appear to report that the “Test Connection Succeeded.” Click OK to exit the Connection Properties dialog.
After these last steps, you will see a connectionStrings element in your App.config file like the one displayed in Figure 13-3. Change the name attribute to MyConnection and save the file by choosing File | Save App.config or pressing CTRL-S. We will use this named reference in future examples.
Figure 13-3 Adjusting connection string reference in App.config file
CAUTION
Remember to save your App.config file after changing the name attribute to MyConnection. Otherwise, the code examples within this book, which rely on this named connection, will fail.
The solution for this example is now complete. While the result may seem uneventful, keep the solution handy. You will use this solution in Example 13-3, and you will need this solution as well to complete the examples in Chapter 18.
Example 13-2 Generating a Connection String to SQL Server
This example explains how to create a database connection string to reference databases that are stored in Microsoft SQL Server.
NOTE
If you already completed Example 13-1, you may skip this example. This example is intended only for advanced database users who want to use their current installation of Microsoft SQL Server instead of a database file.
If you have Microsoft SQL Server installed, you can use it to host the database instead of using the database file. First, create a database named FoodStore and then run the script that is located in FoodStore.sql, which is provided with the download for this book. To begin this example, create a new console application. Then, right-click the project node in the Solution Explorer and choose Properties. In the project window, select the Settings tab. You may be shown a panel with the message “This project does not contain a default settings file. Click here to create one” in a hyperlink. If so, click the hyperlink. Now the Settings view will be visible, as shown in Figure 13-4.
Figure 13-4 Setting the connection string from the project Settings panel
In the Settings panel, select Connection String for the Type and choose Application for the Scope. Next, click in the Value column and click the ellipses (…) button at the right to launch the Connection Properties dialog shown in Figure 13-5.
Figure 13-5 Connection Properties dialog for Microsoft SQL Server
In the Connection Properties dialog, the data source must be Microsoft SQL Server. If it isn’t, click Change, select Microsoft SQL Server from the Change Data Source dialog, and click OK. Now you can enter the server name. If you do not know the server name, one quick way to obtain it is from the Connect To Server dialog that appears when SQL Server Management Console launches (see Figure 13-6).
Figure 13-6 Connect to Server dialog
Back in the Connection Properties dialog, enter the server name. Then select Windows Authentication, provided that you were logged in using Windows authentication when creating the FoodStore database. If instead you created the FoodStore database while using SQL Server Authentication mode, then you also need to enter your database login credentials. Once you have entered the correct server name and authentication data, select the database name from the drop-down list. If you did not enter the correct information, the database will not appear.
To ensure your connection information has been entered properly, click the Test Connection button at the bottom of the Connection Properties dialog. A pop-up message should indicate that your connection was successful, in which case you can click OK to close the Connection Properties dialog.
After successfully setting your data connection from the Connection Properties dialog, you will then find a connectionStrings element inside your App.config file. The add element will contain a connection string like the one shown in Figure 13-7. To prepare the connection string for use in future examples within this chapter, assign the value of MyConnection to the name attribute (refer to Figure 13-7).
Figure 13-7 Adjusting connection string reference in App.config file
Remember to save your App.config file after changing the name attribute to MyConnection. Otherwise, the code examples within this book, which rely on this named connection, will fail.
Example 13-2 is now complete. You will want to use this solution later to start the examples that are presented in Chapter 18.
For data-driven development, it is really important to be able to view the actual database content without relying on your code. For example, if you are executing a query in C# to view data stored in the Product table of your database, for test purposes, you likely will want to compare results with the database values that are retrieved independently of your own application. If you are an experienced database developer, you could use SQL Server Management Studio to view the data directly. If, however, you are new to databases or to SQL Server, Visual Studio provides either a Server Explorer tool or a Database Explorer tool. Other than the differences in names, these tools basically look and work the same. Which tool is included with your Visual Studio application depends on your application edition. If you are using a less advanced edition of Visual Studio, such as an Express edition, you likely will have the Database Explorer tool bundled with your application. Otherwise, you will have access to the Server Explorer tool, which offers the same identical features. Regardless of your database experience level, the Server Explorer/Database Explorer offers a convenient way to view the data.
Example 13-3 Browsing Database Content with Visual Studio
This example shows how to set up and use the Server Explorer/Database Explorer to view data that is contained in a database table.
If you receive any errors while working with the Server Explorer/Database Explorer, you might need to download and install the latest release of Microsoft’s SQL Server Data Tools.
To begin, start with the solution from Example 13-1 or Example 13-2. Once your project is created, you can open the Server Explorer from the View menu or open the Database Explorer from View | Other Windows | Database Explorer.
Right-click the Database Connections node that appears in the Server Explorer/Database Explorer panel. You will then see your existing database connection listed. If you do not see it, you can add it by choosing Add Connection. Then, follow the steps presented either in Example 13-1 or Example 13-2 starting where you select the data source type. Once the FoodStore database appears as a listing in the Server Explorer/Database Explorer, you can expand the FoodStore connection node, which contains folders for different types of database objects (see Figure 13-8). The directory of interest for this discussion is the Tables folder, which contains the database tables that store the actual data. Figure 13-8 shows data that is stored within the Product table in tabular format. To view table data, click the Tables folder, right-click the desired data table, and then select Show Table Data in the drop-down menu. When you do, the column names and corresponding rows of data will display on the right.
Figure 13-8 Examining database objects and content
This exercise ensures that you can connect to a database and view data. To practice, try these steps:
1. Complete either Example 13-1 if you are a database beginner or Example 13-2 if you are a database expert.
2. Complete Example 13-3 to explore the FoodStore database with the Server Explorer/Database Explorer.
3. Browse the data for the Employee table.
If you wish to examine details about each column in the data table, right-click the column name in the Server Explorer/Database Explorer and choose Properties. The Properties window that appears displays information such as the column name, data type, size, and other items that might be of interest to an experienced database developer (refer to Figure 13-9).
Figure 13-9 Examining data table column properties
The data types reported by the Server Explorer/Database Explorer are data types that are provided by the database. These types may differ from existing C# types. For example, the Microsoft SQL Server varchar(n) type defines a string with a length of n. The varchar type is automatically mapped to a C# string type by Visual Studio when you are working with it in code.
This exercise gives you a bit more practice using the Server Explorer/Database Explorer. This time, try using it to inspect the structure of the Employee table instead of the data. Open the Employee table with Server Explorer/Database Explorer and ensure that you can determine the data types of each column within the table.
Chapter 13 Self TestThe following questions are intended to help reinforce your comprehension of the concepts covered in this chapter. The answers can be found in the accompanying online Appendix B, “Answers to the Self Tests.”
1. Using the Server Explorer/Database Explorer, list the data content that is stored in the Supplier table of the FoodStore database.
2. List the database properties of each column of the Supplier table that are presented in the Server Explorer/Database Explorer.