WEBINAR: On-demand webcast
How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017 REGISTER >
This article is a sample chapter (Chapter 8) from Beginning ASP.NET 1.1 with VB.NET, written Chris Ullman, et al., and published by Wiley.
So far in this book you've learnt a lot about programming, and seen those techniques in use in a variety of Web pages. Now it's time to turn our attention to one of the most important topics of building Web sites—data. Whatever the type of site you aim to build, data plays an important part. From a personal site (perhaps a vacation diary or a photo album), to a corporate e-commerce site, data is key.
There are many ways in which data can be stored, but most sites will probably use a database. So in this chapter we're going to look at data stored in databases, and show how easily it can be used on Web pages. For this we are going to use ADO.NET, which is the data access technology that comes as part of the .NET Framework.
If the thought of databases sounds complex and scary, don't worry. We're going to show you just how easy this can be. In particular, we'll be looking at:
- Basics of databases and how they work
- Creating simple data pages using Web Matrix
- Different ADO.NET classes used for fetching data
- Basics of ADO.NET and how it fetches data
- Using Web Matrix to simplify developing data access pages
Before we can head into these topics though, we need a little theory.
Understanding some basics about databases is crucial to using data in your pages. You don't need to be a database expert, but there are certain things you will need to know in order to work with data in .NET. For a start, you need to understand how data is stored. All types of data on a computer are stored in files of some sort. Text files, for example, are simple files and just contain the plain text. Spreadsheets, on the other hand, are complex files containing not only the entered text and numbers, but also details about the data, such as what the columns contain, how they are formatted, and so on.
Databases also fall into the category of complex files. When using Microsoft Access, you have an MDB file—this is a database file, but from the file itself you can't tell anything about the data inside. You need some way to get to the data, either using something such as Microsoft Access itself, or as we are going to do, using the .NET data classes.
Before you can access the data, you need to know how it is stored internally.
Within a database, data is stored in tables—these are the key to all databases. A table is like a spreadsheet, with rows and columns. You generally have multiple tables for multiple things—each distinct type of data is stored separately, and tables are often linked together.
Let's look at an example to make it easier to visualize. Consider an ordering system, for example, where you store details of customers and the goods they've ordered. The following table shows rows of customer orders, with columns (or fields as they are sometimes called in database terms) for each piece of order information:
|Customer||Address||Order Date||Order Item||Quantity||Item Cost|
|John||15 High Street
|John||15 High Street
|John||15 High Street
|Chris||25 Easterly Way
|Dave||2 Middle Lane
|Dave||3 Middle Lane
This is the sort of thing you'd see in a spreadsheet, but there are a couple of big problems with this. For a start, we have repeated information. John, for example, has his address shown thrice. What happens if he moves house? You'd have to change the address everywhere it occurs. Dave has two addresses, but notice they are slightly different. Which one is correct? Are neither correct?
To get around these problems we use a process called Normalization.
Normalization is the process of separating repeated information into separate tables. There are whole books dedicated to database design, but we only need to look at the simplest case.
A good beginner book on database design is Database Design for Mere Mortals: A Hands On Guide to Relational Database Design, 2nd Edition (ISBN: 0-201-75284-0).
What we need to do is split the previous table into three tables, one for each unique piece of information—Customers, Orders, and OrderDetails. To link the three new tables together, we create ID columns that uniquely identify each row. For example, we could create a column called CustomerID in the Customers table. To link the Customers table to the Orders table we also add this CustomerID to the Orders table. Let's take a look at our tables now.
The Customers table is as follows:
|1||John||15 High Street|
|2||Chris||25 Easterly Way|
|3||Dave||2 Middle Lane|
The Orders table is as follows:
The OrderDetails table is as follows:
|OrderDetailsID||OrderID||Order Item||Quantity||Item Cost|
We now have three tables that can be linked together by their ID fields as shown in Figure 8-1:
We now have links between the tables. The CustomerID field in the Orders table is used to identify which customer the order is for. Similarly, the OrderID field in the OrderDetails table identifies which order a particular order line belongs to.
The unique key in a table is defined as its Primary Key—it's what uniquely defines a row. When used in another table it is called the Foreign Key, so called because it's a key, but one to a foreign table. The 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 to note 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.
If you remember, 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.
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.
During the rest 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 main focus is 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
- 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:
- Select Access Database from the window that appears and press OK.
- Enter the following into the Data File text area (we'll use a central location for the database, so that we can reuse it later in the book):
- Press OK to connect to the database. This is the Northwind database, one of the sample databases that ships with Microsoft Access.
- Figure 8-3 shows the tables contained in this database:
You can double-click on these to open the table, and see and change the data. One thing you might notice is that you don't see any queries—that's because Web Matrix doesn't support queries in Access. When connecting to SQL Server, you'll see the stored procedures—you can even create and edit them—but for Access, you are limited to tables only.
How It Works
There's nothing really to explain about how it works. What we are doing is simply creating a connection to a database that Web Matrix can use. This isn't required for ASP.NET to fetch data from databases, but Web Matrix has some great ways to generate code for you, so you don't have to do as much coding.