To get started, direct your browser to www.microsoft.com/sqlserver/2005/en/us/express-starter-schemas.aspx. If this URL doesn't work, search the Web for SQL Server 2008 sample schema and find the nearest Microsoft link. It should get you there.

This page offers two sample listings — sample applications and sample schemas. The sample applications are full-blown applications that show complete end-to-end implementation of data-driven software built using .NET. Some are in C#, some are in Visual Basic. The sample schemas are databases only and are designed for database administrators to practice getting experience in handling the system.

Any of the sample schemas will work. If you want exactly the same one as I use in the examples here, choose the Asset Management schema. Other options may be a better fit for the work you're doing. They include

To install, follow these steps:

If you are familiar with SQL Server, you can add a database to your local install and point to it there. In the case that you aren't a DBA, it is also possible to point a data provider directly to a file. That's the angle I take for the rest of this chapter.

There is more to connecting to a database than establishing a simple connection to Microsoft Access these days. Visual Basic developers have to connect to mainframes, text files, unusual databases, Web services, and other programs. All these disparate systems get integrated into windows and Web screens, with update, add, and delete functionality to boot.

Getting to these data sources is mostly dependent on the Adapter classes of the individualized database namespaces. Oracle has its own, as does SQL Server. Databases that are ODBC (Open Database Connectivity) compliant (such as Microsoft Access) have their own Adapter classes; the newer OLEDB (Object Linking and Embedding Database) protocol has one, too.

Fortunately, a wizard handles most of this. The Data Source Configuration Wizard is accessible from the Data Sources panel, where you spend much of your time when working with data. To get started with the Data Source Configuration Wizard, follow these steps:

You're done! If you look at the Data Sources panel, you find that the new data connection was added, as shown in Figure 2-8.

Figure 2-8. New data connections appear in the Data Sources panel to the left.

By following the preceding steps, you create two significant entities in Visual Studio:

Both of them are important, and they provide different functionality. In this chapter, I focus on the project-specific data source displayed in the Data Sources panel.

The Rapid Application Development (RAD) data tools for Visual Basic are a massive improvement over what Microsoft previously provided. The RAD data tools in Visual Studio are usable and do what you need, and they write decent code for you.

If you click a table in the Data Sources panel, a drop-down arrow appears, as shown in Figure 2-9. Click it and you see something interesting: A drop-down list appears, and you can then choose how that table is integrated into Windows Forms.

Change the Parts table to Details View. It's used to create a detail type form — one that easily enables users to view and change data. Then drag the table to the form, and Details View is created for you, as shown in Figure 2-10.

A whole lot of things happened when you dropped the table on your form:

Figure 2-9. Table Options drop-down list.

Figure 2-10. Creating a Parts Detail data form.

Also, a VCR Bar (technically called the BindingNavigator) is added to the top of the page. When you run the application, you can use the VCR Bar to cycle among the records in the table.

Finally, four completely code-based objects are added in the Component Tray at the bottom of the page: the DataSet, the BindingSource, the DataAdapter, and the BindingNavigator objects.

Click the Play button and you can see the VCR Bar work. You can walk through the items in the database with no problems, as shown in Figure 2-11. It's just like working in Access or FoxPro, but with enterprise quality!

Figure 2-11. Running the example.

It gets better. Follow these steps to create a child table interface:

You have a running, easy-to-use parent/child form, with parts and part faults. Creating this form would have required you to write a hundred lines of code in other environments. With the capability to choose an assembly for a data source that C# gives you, the form is even nearly enterprise ready. It's slick stuff.

Figure 2-12. A complete edit form.

In most enterprise development environments, however, you won't be using the visual tools to build data access software. Generally, an infrastructure is already in place.

The reason for this is that often, enterprise software has specific requirements, and the easiest way to manage those specifications is with unique and customized code. In short, some organizations don't want things done the way Microsoft does them.

Output of the visual tools

The reason that the visual tools often aren't used in enterprise environments is that the code the tools put out is rather sophisticated. If you switch to Code View and right-click an instance of an object (such as the PartsTableAdapter object) and select Go to Definition, you see the code behind the designer.

Figure 2-13 shows what you see when you first get in there. The box marking the region near the top of the code window is marked as Windows Form Designer generated code, and you can't help but notice that the line number before that section is in the twenties and the number after that is in the four hundreds. That's a lot of generated code.

Nothing is wrong with this code, but it is purposely generic to support anything that anyone might want to do with it. Enterprise customers often want to make sure that everything is done the same way. For this reason, they often define a specific data code format and expect their software developers to use that, rather than the visual tools.

Figure 2-13. Generated code. Huh?

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace Accessing_Data
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void partsBindingNavigatorSaveItem_Click(object sender, EventArgs
    e)
        {
            this.Validate();
            this.partsBindingSource.EndEdit();
            this.tableAdapterManager.UpdateAll(this.assets_MaintenanceDataSet);

        }

private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'assets_
    MaintenanceDataSet.Part_Faults' table. You can move, or remove it, as needed.
            this.part_FaultsTableAdapter.Fill(this.assets_MaintenanceDataSet.
    Part_Faults);
            // TODO: This line of code loads data into the 'assets_
    MaintenanceDataSet.Parts' table. You can move, or remove it, as needed.
            this.partsTableAdapter.Fill(this.assets_MaintenanceDataSet.Parts);

        }
    }
}

1. SqlConnection mainConnection = new SqlConnection();
2. mainConnection.ConnectionString = "server=(local);database=Assets_
    Maintenance;Trusted_Connection=True"
3. SqlDataAdapter partsAdapter = new SqlDataAdapter("SELECT * FROM
    Parts", mainConnection)
4. DataSet partsDataSet = new DataSet();
5. mainConnection.Open();
6. partsAdapter.Fill(partsDataSet);
7. mainConnection.Close();

TextBox1.Text = myDataSet.Tables[0].Rows[0]["part_name"]

I don't want to get into much programming philosophy here, but object models (which I discuss in much of this book) and databases just don't go together. They're two different ways of thinking of the same information.

The problem mostly lies in inheritance, which I discuss in Book II. If you have a class called ScheduledEvent, which has certain properties, and a bunch of classes that inherit from it, like Courses, Conferences, and Parties, there just isn't a good way to show this in a relational type of database.

If you make a big table for ScheduledEvents with all possible types of properties, and just make a Type property so you can tell the Courses from the Parties, and then you will have a lot of empty cells. If you make a table for just the properties that are in ScheduledEvents, and then separate tables for Courses and Parties, you make the database remarkably complex.

To address this problem, Microsoft created the Entity Framework. It's the latest edition of a product that Microsoft and everyone else has tried to create since the popularity of relational databases and object-oriented programming made object role modelers necessary.

Object Role Modelers try and take the whole shootin' match and turn it on its head. The goal is to design the database first (which I recommend anyway), and then make an object model to work with it automatically. Then, keep it up to date.

The Entity Framework does an acceptable job at that process. It generates a context for you that you can use to communicate with your data in a way that looks more like an object model than it does a database.

Generating the entity model

To get started, you need the model itself. Just follow these steps to generate the entity model:

1. Create a new project (I used a Windows Forms project again, called Entity Framework).

2. Right-click the project and click Add New in the context menu. Select ADO.NET Entity Data Model. Name it PartsDatabase.

3. Choose Generate from Database Option in the Choose Model Contents window.

4. Select the Assets_Database.mdf from the Connection drop-down list. If it isn't there, see "Connecting to a data source."

5. If you get a message asking if you would like to copy the database into the project, select No. Copying the database is usually a bad practice, especially if you are on a shared project. If you are building a standalone project, and are working alone, it is an acceptable solution.

6. Select the Asset_Parts, Assets, Part_Faults, and Parts tables, just for fun. (See Figure 2-14.) Keep the default name.

   

   Figure 2-14. Select a few tables.

The next thing you see is the designer canvas for the Class Designer. A sample diagram is shown in Figure 2-15. It's just a class diagram, like the ones in Book II.

Figure 2-15. Looks like a class model, walks like a database.

Notice something missing? The Asset_Parts table is gone; it's been abstracted into the model for you because the table was there only to link the Assets and Parts tables in a many-to-many relationship. Slick, isn't it?