Datasheet

foreign key is simply a column that is the primary key in another table. Because the values of the
primary key and the foreign key will be the same, we can use them to link the tables together. This
linking of the tables is done in Structured Query Language (SQL), usually as a query or a stored procedure.
SQL and Stored Procedures
Queries are the way in which we deal with data in a database, either to extract data or to manipulate it.
We can use an SQL statement or a stored procedure, which is an SQL statement wrapped to provide a
simple name. It's worth noting that a stored procedure is actually more than just wrapping an SQL
statement in a name, but that's a good enough description for what we need.
In Chapter 5 when we looked at functions, we had a function name encapsulating some code statements.
Think of a stored procedure in a similar way – it wraps a set of SQL statements, allowing us to use the
name of the stored procedure to run those SQL statements. We're not going to focus much on this topic
as it's outside the scope of this book.
To learn more about SQL, read SQL for Dummies (ISBN 0-7645-4075-0) by John Wiley & Sons Inc.
Here are a few reasons why you should always use stored procedures instead of direct SQL:
❑
Security: Using the .NET data classes with stored procedures protects you against certain forms
of hacking.
❑
Speed: Stored procedures are optimised the first time they are called, and then the optimised
code is used in subsequent calls.
❑
Separation: It keeps the SQL separate from your code.
In the remainder of this book, we'll actually be using a mixture of SQL and stored procedures for the
simple reason that sometimes it's easier to use SQL in the context of an example. Remember, our focus is
on ASP.NET. We'll be using Microsoft Access for the samples, and although Access doesn't support
stored procedures, its use of stored queries is equivalent.
Let's get on with some examples.
The Web Matrix Data Explorer
You've already seen how powerful Web Matrix is for creating Web pages, and this power extends to
working with data. Where you've used the
Workspace Explorer in the top right hand corner of Web
Matrix to work with files, you can use the
Data Explorer to work with data. This provides ways of
creating databases, connecting to existing ones, and working with tables and queries. Let's give this a go.
Try It Out Connecting to a Database
1. Select the Data Explorer tab, and click the Add Database Connection button – the one that's second
in from the right, and will be the only one highlighted, as shown in Figure 8-2, if you haven't
already got a database connection open:
251
Reading from Databases
57084_08.qxp 30/01/2004 8:02 PM Page 251