SQL Server vs. Access

For our final act in this chapter (and therefore just prior to putting some of the things we've looked at into practice in real ASP.NET applications), we're going to have a quick look at Microsoft's two RDBMS products, with a view to helping you decide which to use in your production applications. As you know, we're going to be using MSDE as we work through the examples in this book, but commercial applications tend to require commercial solutions.

Microsoft Access can be a good database choice when you need a relatively small-footprint, relatively inexpensive database that resides on the client. However, performance begins to breaks down for large databases, and for many simultaneous users. Using Access as a server-side database for even small databases will yield poor performance, since Access does not have a server component. That is, the client application always has to read the entire table from the file server (that is, the computer on which the Access database resides), even if the SELECT is requesting a single record.

Microsoft SQL Server is much more expensive, and may be subject to license fees per user . It has a larger footprint than Access, but it will virtually always reside on a server. It performs well for medium- sized databases, and it has a server component that is designed to operate very efficiently over a network. It also supports stored procedures (see Chapter 8) that can perform significantly better than Access's queries, since the stored procedure execution plan is optimized on the fly. SQL Server has a much richer and more robust security model than Access. Finally, SQL Server allows richer maintenance operations, since it keeps a transaction log that can be used to recover a corrupt database without loss of data.

MSDE

As we described in the previous chapter, to a large extent MSDE is SQL Server, but without client tools like the Query Analyzer and the Enterprise Manager. Without the aid of these tools, routine database maintenance becomes more difficult - but not prohibitively so, as you'll soon see.

Summary

In this chapter, we've scratched the surface of an enormous subject. We have reviewed theory ranging from Codd's rules to database design and normalization. We have introduced SQL, the lingua franca of RDBMSs, and have compared SQL Server and Access, two of the most common RDBMSs for Windows. The chapters that follow will cover SQL in greater depth, but our emphasis will turn away from database design, and toward database programming. The focus shifts from theory to practice as you get hands-on experience and learn techniques to apply the theory to real-world problems.

Chapter 3: Connecting to a Data Source

Overview

As we discussed in Chapter 1, all of the interactions between ASP.NET and data that you'll see in this book will start with an ADO.NET connection . The connection establishes what data source the page will use, the security details necessary to authorize access to the data, and the data provider that will handle the translations between the data source and the ADO.NET objects. However, the syntax you use to create the connection, and the options available to you, vary considerably with different sources of data.

You know that in ASP.NET, getting data from a database to the web page that your users will see in their browsers requires a number of ADO.NET objects: connections, datasets, data readers, data adapters, and so on. In order to provide useful demonstrations , we'll be using quite a few of these different objects in this chapter, but our focus is on the first item in that list - the others will have their time in the sun a little later on. Our aim here is to understand ADO.NET connection objects, and in order to do so we'll look at the following topics:

• The basics of ADO.NET connections

• Connecting to Microsoft SQL Server data

• Connecting to an MS Access database

• Connecting to an MS Excel spreadsheet

• Connecting to an XML data file

• Getting schema information about a data store

• Some common mistakes