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