Chapter 9: ASP.NET and SQL Server

Chapter 9: and SQL Server



This chapter will look at the process of connecting to and interacting with databases

from applications. Database operations have simplified substantially in .Net

2.0 and 3.x. However, conceptually, it is now arguably harder to understand what is

going on, even though much less code is needed. So after briefly introduced SQL

Server Express (which is the preferred database in this module) we will run over the 'old'

method - which, in principle, is still more or less the way you interact with databases in

environments other than .Net 2.0, 3.x and newer technology like Rails or LINQ

(Language Integrated Query). We are doing this to make it clearer what is actually going

on - though in practice you would obviously use the newer and more convenient

technologies that are becoming increasingly available.

9.1. SQL Server Express

The database used in this example is SQL Server Express, which is installed by default

when you install Visual Studio 2005 Pro. So unless you explicitly chose not to install it,

then you have it and it's probably running - managing to interact with it is another matter

however. You may have chosen to install the full version of SQL Server 2005. If so,

much of what follows still applies, with minor differences in detail. It is possible to use

other databases, but SQL Server is the natural companion of applications.

There are potential support/compatibility issues if you choose something else.

9.1.1. Administrator Access

Although it is no longer necessary to be an administrator to create web

applications in Visual Studio, it is to create SQL Server databases (including express

ones). This is strictly a bit of a simplification - and it is certainly possible to interact with

databases without admin access. However, that's of no help if you cannot create one in

the first place. Fortunately, there is an alternative - you can create a database file which

1

for our purposes acts exactly like a database. You can of course also choose to do this

even if you are an administrator on the machine you are using. This decision to some

extent influences how you interact with SQL Server. More on this in the next section.

9.2. Tools for SQL Server Databases

You have three main choices for interacting with SQL Server 2005 and SQL Server

Express.

?

?

?

Use Visual Studio. This is the most obvious choice since you probably have it already however, it may not be the most convenient for everyone. (Though it's your only

straightforward choice if you are going to use a database file.)

Use the command line tool. You get a tool called sqlcmd which works from the

command line. This may not be to everyone's taste but (a) you will already have it and (b)

you can straightforwardly script the creation of databases.

Use a graphical tool. Other than Visual Studio, there are a number of graphical tools

available for interacting with SQL Server - the most obvious choice being Microsoft's free

SQL Server Management Studio Express This can also conveniently use scripts but you

will have to download and install it.

We will explain the use of all of these tools below, but will concentrate on the first option

(since that seems to be what 90% of people use for the simple databases required in

this module).

9.2.1. Visual Studio

To use Visual Studio to create databases you need to open the server explorer window click the tab next to the one for toolbox in the lower left corner:

Fig.1. Server Explorer

All the interesting stuff is at the top:

2

Fig.2. Server Explorer - the Useful Bit

In the figure, we have expanded data connections to reveal a couple of databases (the

dbo entries) and a couple of database files (the mdf entries). One of the database files

has been further expanded to show table detail.

Right click on data connections and choose add connection.., then select SQL Server.

You will see a window looking like this:

3

Fig.3. New Data Connection

As it stands, this expects a connection to an existing database - and not the creation of

a new database file. So click 'change' and select database file.

4

Fig.4. New Database File

Then type in the name you want to use - it will ask if you want to create the file and

(obviously) you do.

To create a table, expand your new file and right click on table - choose 'Add New Table'

and you'll get something like this:

Fig.5. Table Entry Dialogue

You can create new columns, select the types - there's a list of built-in ones which you

can modify as needed, and set column characteristics - like primary keys (right click to

get a contextual menu).

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download