Creating Your VS Database Project

Fire up Visual Studio .NET, create a new Windows application In the C:\BegVCSharp\Chapter25 directory, name it GettingData (also change the Name and Text properties of Form1 to GettingData), and use the following Try It Out to connect to a database!

Try It Out – Connecting to a Database

After VS2005 is open, follow these steps to create your connection:

1. Open Server Explorer by choosing Tools Connect to Database... as shown in Figure 25-1.

   
   Figure 25-1

2. Click Connect to Database to bring up the Choose Data Source window, shown in Figure 25-2.

   
   Figure 25-2

   For this example we want to choose Microsoft SQL Server using the .NET Framework Provider for SQL Server as shown in Figure 25-2. However, if you want to use another database such as Oracle, you would choose the data source appropriate for that database and select the appropriate .NET data provider for your database.

3. Now the Add Connection dialog appears, shown in Figure 25-3. Here you fill out all the information VS2005 needs to connect to your database.

   
   Figure 25-3

   First of all, in the Server name combo box, either type the name of the SQL Server you wish to connect to, or select the server name from the drop-down list. SQL Server names consist of the server (machine) name and an instance name, which is needed in case there is more than one installation of SQL Server on the same machine.

   Assuming that you installed SQL Express with the default options, use the name (local)\ SQLEXPRESS. As described in Chapter 24, the server name (local) is a generic alias for your local desktop machine. SQLEXPRESS is the default instance name used when SQL Express is installed; if you specified a different server or instance name during installation, use that instead.

4. Next, you need to specify the user account you want to use to connect to the database. If you select Use Windows Authentication, VS will attempt to connect to the database with the same user account that you used to log on to Windows.

   Note

   SQL Express is installed with Windows integrated security as the default security option, so you will want to choose this option unless you know you use a different username and password to connect to your SQL Server, in which case you would select the next option (Use a specific user ID and password), and enter them in the appropriate boxes.

5. Finally, select the Northwind database from the "Select or enter a database" drop-down list. If Northwind doesn't appear in the list, see the section "Install SQL Server and the Northwind Sample Data" in Chapter 24 for details on how to install the Northwind sample database.

6. When you've finished entering this information, click on the Test Connection button. Hopefully, you will now see a dialog box like that shown in Figure 25-4, saying that the connection succeeded.

   
   Figure 25-4

   Congratulations, you have just successfully added your first ADO.NET database connection! You will now see the Server Explorer window on your Visual Studio 2005 screen, as shown in Figure 25-5, and the connection just added is now nested under the Data Connections node.

   
   Figure 25-5

You now have access to the database and all the data in it, without even leaving VS! Note that the actual server machine name (roadrunner, in my case) appears instead of (local). Let's have a look at this data to see how it's structured.

Database Objects

In the Server Explorer, click on the node beside the connection you just made and examine what kinds of objects can be viewed here, as shown in Figure 25-6.

Figure 25-6

Relational databases such as SQL Server and Oracle contain a number of different kinds of objects, which the Server Explorer allows you to examine interactively.

• Tables contain the actual database data, stored in rows and columns.

• Views look like tables, but contain alternate views of the data, possibly including combinations of the data from several tables (the view changes when the data in its underlying table changes).

• Stored procedures contain code that is stored within the database. Traditionally, stored procedures have been written in the SQL database language, but with SQL Server 2005 and Visual Studio 2005, you can now write database stored procedures in C# or other .NET Framework languages. However, that is a topic beyond the scope of a beginning book such as this one.

• Functions are like stored procedures but return a data value, like a method in C# that returns a particular data type such as an int or string. In contrast, a stored procedure is like a method with a void return type.

• Synonyms are a new object type found only in SQL Server 2005 (and SQL Express); they are alternate names for database objects such as tables, views, procedures, and functions.

• Types are also new, found only in SQL Server 2005 (and SQL Express); these are user-defined data types that extend the data type system of the SQL database language, making it somewhat object-oriented. User-defined types are implemented in C# or another .NET Framework language, unless they are simply alternate names for an existing SQL data type.

• Assemblies, also new to SQL Server 2005 (and SQL Express), are the .NET binary assemblies (compiled from C# or other .NET language source code), implement a user-defined type, .NET stored procedure, or .NET function. See Chapter 26 for more information on .NET assemblies in general; the ones shown here are assemblies registered with SQL Server 2005 to extend its functionality.

You may see other object types listed also, depending on which edition of Visual Studio 2005 and what database product you have. Let's look now at some of the objects in the Northwind database, starting with the tables.

Browsing Database Tables and Relationships

In the Server Explorer, open up the Tables node underneath the connection you just added. You should now see a list of the tables in the Northwind database, as shown in Figure 25-7.

Figure 25-7

As stated earlier, relational databases store data within a series of related tables. These tables consist of rows and columns; each row represents a record within the database, and the columns represent the individual fields for each record. To see a visual representation of this, right-click on the Customers node in the Server Explorer, and select Show Table Data. VS2005 will now load and display the data from the Customers table in the Northwind database, as shown in Figure 25-8.

Figure 25-8

The Northwind sample database contains the data for a fictional food wholesaler that supplies various restaurants and food shops. the Customers table contains the details about each of these customers. Each row in this table represents a specific company that Northwind supplies, and each column contains a specific piece of data about the company, such as the company's name, its address, and the name of Northwind's contact in the company.

Each row in the table is distinguished by a unique five-character ID code, which is stored in the CustomerID field. This distinguishing field is known as the primary key and is vital for relating the Customers table to the other tables in the database. To see how this works, right-click on the Orders node, and again select Show Table Data, which displays the rows from the Orders table, as shown in Figure 25-9.

Figure 25-9

This table represents the orders received by the Northwind company. Again, each row represents one order. Notice that this table also has a CustomerID field with the same five-character ID codes as in the Customers table. The values in this column serve as a pointer to the row in the Customers table where more information about the customer can be found. This type of column is known as a foreign key.

You will use tables related through their primary and foreign keys in queries that fill the data structures and forms in the example applications. The ability to browse the database is very handy for developing database applications, because you can check your work against the database as you go (now what is the name of that column again?).

However, you didn't buy VS to use it as a front end for your database, so let's see how to use the connection from your C# programs!