Chapter 9: Show Me the Data

In this chapter, you ll learn the basics of data access in Microsoft ASP.NET and Web Matrix. To learn how to use Web Matrix tools to work with a database, you ll connect to MSDE, the database engine for Web Matrix. You ll also create a database, and within that database, you ll create a simple data table and put some data into it. To learn about data display in a Web page, you ll create a page in Web Matrix and then use drag- and-drop tools to create a grid that displays your data.

This chapter will be similar to Chapter 4 in that the purpose of the chapter is primarily orientation. You ll practice how to connect to MSDE, the database engine you installed in Chapter 3, and how to create a database and tables. To provide you with an overview of how to use data in a Web page, I ll show you how to enter and display some sample data. The database and the Web page will be simple, because our focus is more on learning various techniques than in creating something cool for your site, such as a guestbook, a calendar, and more. You ll get to the cool stuff very soon, though!

Using Databases with Your Web Applications

Up to now you ve been working with Web pages that either contained all their own data (the slideshow in Chapter 5) or that used a simple text file for data (the quotations page in Chapter 7 and the hit counter in Chapter 8). Using hard-coded data or text files are fine techniques for the modest data requirements of those pages. But many of the applications you ll want to create will require more powerful and flexible data access and for that requirement, you ll need to have your Web pages talk to a database.

Note 

I m going to assume in this chapter that you have a general grasp of databases tables, columns, rows, and so on. In this chapter, you won t get very far into database structures. In later chapters, though, you ll work with slightly more advanced database concepts, such as the syntax of SQL statements and related tables (master-detail tables). If you re feeling a little shaky in database knowledge, you might want to brush up by reading Designing Relational Database Systems by Rebecca M. Riordan (Microsoft Press, 1999).

As I explained in Chapter 3, Web Matrix is designed to work with MSDE, which is a free, redistributable version of SQL Server. The data tools in Web Matrix interact with MSDE or SQL Server and allow you to use visual tools to connect to, create, and query databases. Web Matrix includes a special control, SqlDataSourceControl, that encapsulates database access into a component that you can drop onto a page. And as we ll see eventually, Web Matrix can automatically generate data-access code in your pages that will talk to an MSDE or SQL Server database.

I Want My Access!

MSDE and SQL Server are great database services, but lots of people like using Microsoft Access. More specifically, they re used to working with the .mdb files that you can create in Access. (I make that distinction because you can use Access to create tables in MSDE.) One reason Access is popular is that many people already have Access installed and are used to working with its various tools to create and manage database tables. Access .mdb files are also easier to deploy (copy) than MSDE and SQL Server databases when you need to move your data to another computer.

Microsoft decided for this release of Web Matrix to provide support for only SQL Server and MSDE. Supporting only SQL Server and MSDE, and not Access, is a decision that you might find odd, but it makes sense. Although our requirements for Web Matrix and databases are relatively modest, ASP.NET itself is, as I mentioned in Chapter 2, a platform designed for professional-grade Web applications. Similarly, although this book introduces you to Web Matrix as a tool that helps you easily create small-scale applications, Web Matrix was designed as a WYSIWYG tool for ASP.NET development. When the ASP.NET team created Web Matrix, therefore, they favored the database that would support the broadest range of Web applications, namely SQL Server. Then there is the question of how much effort Microsoft was going to put into a free tool it s one thing to include database support, but did the ASP.NET team really want to add support to Web Matrix for multiple types of databases? Given these factors, it s not surprising that the ASP.NET team designed Web Matrix to work with only one database, and that the database is SQL Server.

When I say that Web Matrix supports only SQL Server and MSDE, I mean specifically that the nice WYSIWYG tools in Web Matrix are designed for SQL Server databases. Web developers can link to Access, Oracle, and other types of databases if they re willing to write the code required to do so. In this book, I m not going to show you how to write the code for working in Access because I want to take advantage of the tools in Web Matrix. If you re interested in learning about working with Access in Web Matrix, I suggest that you visit the ASP.NET forums on http://www.asp.net/, where these issues have been discussed a number of times.