Microsoft Visual C# 2005 Express Edition: Build a Program Now! (34 page)

Listing

Table

ColorID

MakeID

CarTypeID

DateSeen

Year

Price

Cylinder

HP

URL

EPGCity

EPGHighway

Notes

This is my

http://www.

dream

1

1

1

08/11/2005

2005

42500

6

240

20

28

litwareinc.com/

car, follow

regularly.

http://www.cpandl.

Too much

4

3

2

07/30/2005

2003

39775

8

340

10

15

com/

gas

You’ll now verify that one of your foreign key constraints is working correctly. Open the Make table by 6

right-clicking on the
Make
table and selecting
Show Table Data
.
You can navigate through the

table by using the navigation

Let’s try to delete the first row by clicking on the left-most field where the pencil usually appears. The 7

controls at the bottom of the grid.

row should be selected and all fields should be blue. Right-click and select
Delete
.
These controls will allow you to

do things such as move to the first

and last row, move to the previ-

8 A dialog box should appear on your screen inquiring whether you really want to delete the row. Click
Yes
.
ous and next entry, move to a new

record, or directly type in the row

You should receive a dialog box error message stating that the row was not deleted because of the
number.

9 foreign key constraint that reads as follows:
Error Message: The DELETE statement conflicted with
the REFERENCE constraint “FK_Listing_Make.”
This statement affirms why the foreign key constraint was created, which was to avoid orphaned rows. Figure 8-15 depicts what the error dialog box looks like and what kind of information is provided to help you debug the problem, if necessary. In this case, it’s not a problem but a feature of your creation!

10 Click
OK
to exit this dialog box.

11 Test your other constraints related to the Listing table by trying to delete the first row of the CarType table. You should

receive a similar error

message.

Figure 8-15

Error dialog box showing the

foreign key relationship pre-

venting the deletion of a row

from the Make table

Chapter 8: Managing the Data

147

C08622299.indd 147

C08622299.indd 147

10/24/05 4:02:41 PM

10/24/05 4:02:41 PM

Now that you have all of your domain tables loaded with some data, you’ll now learn to use the database in a Windows Forms application. You’ll learn about ADO.NET and about databindings with Windows Form controls.

What Are ADO.NET and Databinding?

You rarely enter all data manually using Visual Studio. You typically let the user do it or you do it through an application. You can also either import data from another source or create
If you want more informa-

the new data using SQL scripts, but this is a more advanced concept that will not be covered
tion about SQL and Transact-

in this book.

SQL, you should download

This section will focus on how to build Windows applications that can connect to and
the SQL Server 2005 Express

receive data from a SQL Server 2005 Express Edition using ADO.NET. The following is a
documentation. You can find

this information at the follow-

formal, official definition of ADO.NET from the MSDN online library:

ing link: http://go.microsoft.

ADO.NET provides consistent access to data sources, such as Microsoft SQL Server, as well
com/fwlink/?LinkId=51842.

as data sources exposed through
OLE DB
and
XML
. Data-sharing consumer applications can
SQL Server 2005 Express

use ADO.NET to connect to these data sources and retrieve, manipulate, and update data.
documentation is designed to

ADO.NET cleanly factors data access from data manipulation into discrete components
help you answer most ques-

that can be used separately or in tandem. ADO.NET includes .NET Framework data providers
tions you might have, but it

might also refer you to the

for connecting to a database, executing commands, and retrieving results. Those results are
SQL Server 2005 documenta-

either processed directly or placed in an ADO.NET DataSet object in order to be exposed to
tion. You can download it at

the user in an ad-hoc manner, combined with data from multiple sources, or remoted between
http://go.microsoft.com/fwlink/

tiers. The ADO.NET DataSet object can also be used independently of a .NET Framework data

?LinkId=51843.

provider to manage data local to the application or sourced from XML.
The ADO.NET classes are found in System.Data.dll and are integrated with the XML

classes found in System.Xml.dll. When compiling code that uses the System.Data namespace,
reference both System.Data.dll and System.Xml.dll.

I wanted to present the long and formal definition of ADO.NET because it contains elements that you’ll learn about while working with the Car Tracker application. I also chose it because I would like you to refer back to it whenever you’re working with ADO.NET. Here is a less formal definition that I think summarizes what ADO.NET is all about.
148

Microsoft Visual C# 2005 Express Edition: Build a Program Now!

C08622299.indd 148

C08622299.indd 148

10/24/05 4:02:42 PM

10/24/05 4:02:42 PM

You can say that ADO.NET is the .NET Framework way of accessing and programmatically manipulating databases. With ADO.NET you can also manipulate other sources of data like XML

Visual C# Express allows

sources.

you to work with Microsoft

New to ADO.NET 2.0 are new ways of accessing data from different sources. In Visual C#

Access databases, but working

2005 Express Edition, you are limited to the following data sources: databases (SQL Server
with SQL Server 2005 Express

Express and Microsoft Access databases), Web services, and custom objects. It is much easier
Edition gives you all the ben-

(i.e., there is less code) to manipulate data in ADO.NET 2.0, especially when using all of
efits of the Enterprise quality of

the tools included in Visual Studio 2005. There are many new wizards and other tools that
SQL Server 2005, with the only

downside being a reduced set of

make the experience of working with databases a pleasant one. Visual Studio 2005 covers
features.

numerous common scenarios with its tools and wizards, but it’s also very powerful when used programmatically without the use of the visual tools. You will learn the basics in this book, but there’s nothing preventing you from learning more about databinding and ADO.NET and from unleashing powerful applications.

Before proceeding any further, let’s talk about the Car Tracker application. The main goal of the application is to track car ads over the Internet. As you have your database ready to go, you now need to consider what will be included in this application. In reality, what you need is simply a way of displaying the ads, adding new ads, modifying/deleting existing ads, and searching through the ads using a series of drop-down boxes that allow you to narrow your search based on certain criteria. These search criteria will come directly from the domain tables (i.e., separate drop-down controls for the car type, color, make, and so forth). When using drop-down controls or any other controls with data that you know exist in your database, you don’t want to populate the data by hand. You want to use the databinding capabilities of a control.
Databinding
is an easy and transparent way to read/write data and a link between a control on a Windows Form and a data source from your application. ADO.NET takes care of a great deal of activity behind the scenes (it’s even better in .NET

Framework 2.0), as well as managing the connection to the database. Managing the connection doesn’t stop at opening and closing the connection, but also concerns itself with finding the database with which you’re trying to connect. When a connection is opened, it means your application can talk to the database through ADO.NET method calls. All exchanges (send/

receive) of data between your application and the database are managed for you by ADO.NET.

Not all Windows Form controls

The data itself is also managed by ADO.NET through diverse mechanisms: read-only forward
are “databinding aware.” When

navigation, navigation in any direction with read-write, field evaluation, and so forth. And the
they are aware, they have a

DataBindings property.

beauty of it is that you usually don’t have to write a lot of code to enjoy those nice features.
Chapter 8: Managing the Data

149

C08622299.indd 149

C08622299.indd 149

10/24/05 4:02:42 PM

10/24/05 4:02:42 PM

The Car Tracker Application Development

The Data Sources window might

end up somewhere else in your

You’ll now proceed to the development of the Car Tracker application. First, you need to cre
IDE. Because your IDE is entirely
ate a dataset that will provide you with all the databinding you need for the Car Tracker
customizable to your liking, you

can customize your windows and

application. Now that your tables are established, you can configure the dataset with all of
tabs to appear wherever you feel

the elements you’ve just added to your database.

they are most productive for you.

Before creating a dataset, you must learn what a dataset is. A dataset is an in-memory representation of one or more tables and is used to store the rows you retrieve that match the query you sent to the database. You can then add, delete, or update rows in memory. When the user is done, you can submit, save, or commit the changes to the database. The CarTrackerDataSet.xsd is called an XML Schema Definition file. The .xsd file ensures that the data will be structured and respect the schema. This file will be used later in the project when we discuss databinding.

To create a dataset, you’ll learn to use the Data Sources window. This window gives you access to all of the data sources you have configured in your application. See Figure 8-16 to see where the Data Sources window is located. If you don’t see the Data Sources window, you can access it by clicking on the Data menu and selecting Show Data Sources. If Show Data Sources does not appear on the Data menu, be sure you have closed all of the CarTracker table data grids and Form1 is visible.

Figure 8-16

The Data Sources window

TO CREATE A DATASET

1 In the Data Sources window, click the
Add New Data Source
link or click the
Add New Data Source
button in the toolbar. The Data Source Configuration Wizard appears.

The first screen of the Data Source Configuration Wizard allows you to choose the data source type 2 you want to create. You can choose a database, a Web service, or one of your objects. You’ve just built a database for the Car Tracker application, so you’re going to choose that Data Source Type. Select
Database
and then click
Next
.

In the next screen, you will choose your data connection. CarTracker.mdf should already be selected. When you created the CarTracker SQL Server Express database in your project, a data connection was created for you. You can click the
plus sign
(+) in the bottom of the dialog box to see what the connection string looks like. This connection string defines how your application will connect to the database.
150

Microsoft Visual C# 2005 Express Edition: Build a Program Now!

C08622299.indd 150

C08622299.indd 150

10/24/05 4:02:43 PM

10/24/05 4:02:43 PM

3 Click
Next
on the Choose Your Data Connection screen.

The next screen in the wizard inquires whether you want to save this connection string in the application configuration file. As you saw in the previous screen, you know where your database is stored. Yet you might change your mind and deploy the file somewhere else. If you do that, you don’t want to modify the source code and recompile it. Putting the connection string in your application configuration file is actually a best practice. It gives you the advantage of only modifying the file and restarting the application without recompilation so as to automatically pick up the changes in your connection string and connect to that new location.