1. Understanding Databases
Almost every piece of
software ever written works with data. In fact, a typical web
application is often just a thin user interface shell on top of
sophisticated data-driven code that reads and writes information from a
database. Often, website users aren't aware (or don't care) that the
displayed information originates from a database. They just want to be
able to search your product catalog, place an order, or check their
payment records.
The most common way to manage
data is to use a database. Database technology is particularly useful
for business software, which typically requires sets of related
information. For example, a typical database for a sales program
consists of a list of customers, a list of products, and a list of sales
that draws on information from the other two tables. This type of
information is best described using a relational model, which is the philosophy that underlies all modern database products, including SQL Server, Oracle, and even Microsoft Access.
As you probably know, a
relational model breaks information down to its smallest and most
concise units. For example, a sales record doesn't store all the
information about the products that were sold. Instead, it stores just a
product ID that refers to a full record in a product table, as shown in
Figure 1.
Although it's technically
possible to organize data into tables and store it on the hard drive in
one or more files (perhaps using a standard like XML), this approach
wouldn't be very flexible. Instead, a web application needs a full relational database management system
(RDBMS), such as SQL Server. The RDBMS handles the data infrastructure,
ensuring optimum performance and reliability. For example, the RDBMS
takes the responsibility of providing data to multiple users
simultaneously, disallowing invalid data, and committing groups of
actions at once using transactions.
In most ASP.NET
applications, you'll need to use a database for some tasks. Here are
some basic examples of data at work in a web application:
E-commerce sites
(like Amazon) use detailed databases to store product catalogs. They
also track orders, customers, shipment records, and inventory
information in a huge arrangement of related tables.
Search engines (like Google) use databases to store indexes of page URLs, links, and keywords.
Knowledge
bases (like Microsoft Support) use less structured databases that store
vast quantities of information or links to various documents and
resources.
Media sites (like The New York Times) store their articles in databases.
2. Configuring Your Database
Before you can run any data
access code, you need a database server to take your command. Although
there are dozens of good options, all of which work equally well with
ADO.NET (and require essentially the same code), a significant majority
of ASP.NET applications use Microsoft SQL Server.
2.1. SQL Server Express
If you don't have a test
database server handy, you may want to use SQL Server 2005 Express
Edition, the free data engine included with some versions of Visual
Studio and downloadable separately.
SQL Server Express is a
scaled-down version of SQL Server that's free to distribute. SQL Server
Express has certain limitations—for example, it can use only one CPU and
a maximum of 1GB of RAM, and databases can't be larger than 10GB.
However, it's still remarkably powerful and suitable for many midscale
websites. Even better, you can easily upgrade from SQL Server Express to
a paid version of SQL Server if you need more features later.
2.2. Browsing and Modifying Databases in Visual Studio
As an ASP.NET developer, you
may have the responsibility of creating the database required for a web
application. Alternatively, it may already exist, or it may be the
responsibility of a dedicated database administrator. If you're using a
full version of SQL Server, you'll probably use a graphical tool such as
SQL Server Management Studio to create and manage your databases.
If you don't have a
suitable tool for managing your database, or you don't want to leave the
comfort of Visual Studio, you can perform many of the same tasks using
Visual Studio's Server Explorer window. (Confusingly enough, the Server
Explorer window is called the Database Explorer window in Visual Studio
Web Developer Express.)
You may see a tab for the
Server Explorer on the right side of the Visual Studio window, grouped
with the Toolbox and collapsed. If you do, click the tab to expand it.
If not, choose View => Server Explorer to show it (or View => Database Explorer in Visual Studio Web Developer Express).
Using the Data Connections node
in the Server Explorer, you can connect to existing databases or create
new ones. Assuming you've installed the pubs database (see the
readme.txt file for instructions), you can create a connection to it by
following these steps:
Right-click
the Data Connections node, and choose Add Connection. If the Choose
Data Source window appears, select Microsoft SQL Server and then click
Continue.
If you're using a full version of SQL Server, enter localhost
as your server name. This indicates the database server is the default
instance on the local computer. (Replace this with the name of a remote
computer if needed.) If you're using SQL Server Express, you'll need to
use the server name localhost\SQLEXPRESS instead, as shown in Figure 2. The SQLEXPRESS part indicates that you're connecting to a named instance of SQL Server. By default, this is the way that SQL Server Express configures itself when you first install it.
Click
Test Connection to verify that this is the location of your database.
If you haven't installed a database product yet, this step will fail.
Otherwise, you'll know that your database server is installed and
running.
In
the Select or Enter a Database Name list, choose the pubs database. (In
order for this to work, the pubs database must already be installed.
You can install it using the database script that's included with the
sample code, as explained in the following section.) If you want to see
more than one database in Visual Studio, you'll need to add more than
one data connection.
|
Alternatively, you can choose
to create a new database by right-clicking the Data Connections node
and choosing Create New SQL Server Database.
|
|
Click
OK. The database connection will appear in the Server Explorer window.
You can now explore its groups to see and edit tables, stored
procedures, and more. For example, if you right-click a table and choose
Show Table Data, you'll see a grid of records that you can browse and
edit, as shown in Figure 3.
|
The Server Explorer
window is particularly handy if you're using SQL Server Express, which
gives you the ability to place databases directly in the App_Data folder
of your web application (instead of placing all your databases in a
separate, dedicated location). If Visual Studio finds a database in the
App_Data folder, it automatically adds a connection for it to the Data
Connections group.
|
|
2.3. The sqlcmd Command-Line Tool
SQL Server includes a handy
command-line tool named sqlcmd.exe that you can use to perform database
tasks from a Windows command prompt. Compared to a management tool like
SQL Server Management Studio, sqlcmd doesn't offer many frills. It's
just a quick-and-dirty way to perform a database task. Often, sqlcmd is
used in a batch file—for example, to create database tables as part of
an automated setup process.
The sqlcmd tool is found in
a directory like c:\Program Files\Microsoft SQL Server\100\Tools\Binn.
The easiest way to run sqlcmd is to launch the Visual Studio command
prompt (open the Start menu and choose All Programs => Microsoft Visual Studio 2010 => Visual Studio Tools =>
Visual Studio Command Prompt). This opens a command window that has the
SQL Server directory set in the path variable. As a result, you can use
sqlcmd anywhere you want, without typing its full directory path.
When running sqlcmd, it's up to you to supply the right parameters. To see all the possible parameters, type this command:
sqlcmd -?
Two commonly used sqlcmd
parameters are –S (which specifies the location of your database server)
and –i (which supplies a script file with SQL commands that you want to
run). For example, the downloadable code samples include a file named
InstPubs.sql that contains the commands you need to create the pubs
database and fill it with sample data. If you're using SQL Server
Express, you can run the InstPubs.sql script using this command:
sqlcmd -S localhost\SQLEXPRESS -i InstPubs.sql
If you're using a full version of SQL Server on the local computer, you don't need to supply the server name at all:
sqlcmd -i InstPubs.sql
And if your database is on
another computer, you need to supply that computer's name with the –S
parameter (or just run sqlcmd on that computer).
NOTE
The parameters you use
with sqlcmd are case sensitive. For example, if you use –s instead of
–S, you'll receive an obscure error message informing you that sqlcmd
couldn't log in.
Figure 4 shows the feedback you'll get when you run InstPubs.sql with sqlcmd.
You'll occasionally see instructions about using sqlcmd to
perform some sort of database configuration. However, you can usually
achieve the same result (with a bit more clicking) using the graphical
interface in a tool like SQL Server Management Studio. For example, to
install a database by running a SQL script, you simply need to start SQL
Server Management Studio, open the SQL file (using the File => Open => File command), and then run it (using the Query => Execute command).
