Chapter 9: ASP.NET and SQL Server

bootmanInternet and Web Development

Jul 5, 2012 (4 years and 11 months ago)

217 views

page 1
Chapter 9: ASP.NET and SQL Server
This chapter will look at the process of connecting to and interacting with databases from ASP.NET
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, 4.x and newer technology like Rails
or LINQ
(Language Integrated Query). We are doing this
to make it clearer what is atually going on - though in practice you would obviously use the newer and more
convenient technologies that are becoming increasingly available.
The material in this chapter and the next is covered in more detail, and with some alternative viewpoints, in
Spaanjaars chapters 11 and 12.
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. So unless you explictly 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 2008.
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 ASP.NET applications. There are potential support/compatability issues
if you choose something else.
9.1.1. Administrator Access
Although it is no longer necessary to be an administrator to create ASP.NET 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 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 obvous choice since you proably 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
intearcting 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
page 2
All the interesting stuff is at the top:
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:
page 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.
page 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:
page 5
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).
Once you've created a table, right click on it an choose 'Show Table Data' to enter data:
Fig.6. Table Data Entry Dialogue
Creating databases like this is OK if (a) they're not too big and (b) you only have to do it once, which is certainly
OK for us. The advantages of the other methods are that you can put everything in a reusable script.
9.2.2. Sqlcmd
The command line tool sqlcmd is straightforward for the kind of things we will need to do - just create a script
containing the SQL statements required to create your database, and run the script. The only thing to really
remember is that any commands you enter will not be immediately executed, but will instead be cached - to
execute them, you need to type go:
C:\Documents and Settings\User sqlcmd -S .\SQLExpress
1> create database fruit
2> go
1>
The '.\SQLEexpress' connects to a SQLExpress database on the current host ('.' - you can put the hostname
or 'localhost' instead if you want). To read in a file, you can use the command:
:r script.sql
(:help is useful too - as is exit). It's also possible to specify a scriptfile as a command line argument - use -i
filename.
Note that Vista users will find that this does not work by default because of User Access Control. They will need
to explicitly add the user(s) they wish to interact with SQL Express (or SQL Server) as sysadmins.
9.2.3. Graphical Utilities
If you don't want to use a command line tool, or manually create databases stage-by-stage, then consider a
graphical utility. There are a few around - some have a free 'lite' version (or a time-limited free trial). If you
install the full version of SQL Server 2005 you will get one. Otherwise the obvious choice is the free SQL Server
Management Studio Express (SSMSE).
The only slight hitch is that can be a bit touchy in conjunction with Visual Studio - best to install it after a reboot
and with VS not running.
To create a database start up the tool and connect to the server:
page 6
Fig.7. Connect to Server
Obviously, your login details will be different. Once started, there are lots of things you can do - but for now,
click on 'new query':
Fig.8. New Query
Now you can enter and execute queries:
page 7
Fig.9. New Query
However remember to select the database you have just created before making tables etc. (or put a 'use'
statement at the top) or you might be putting them in the wrong place! Lots of people do this to start with so be
warned.
9.3. ASP.NET Database Access - the 'Old' Way
ASP.NET 2.0 introduced a much more declarative mechanism for interacting with databases, that generally
requires very little code on the part of the programmer - most of the complexity is hidden. For many, basic,
operations, you can get away without writing any code at all. This is convenient, but it does mean that you don't
really get to see and understand how things work.
The 'old' way in ASP.NET 1.1 (and the usual way in most other systems) makes the actual operation(s) much
clearer, though it requires more work.
9.3.1. Connecting
The first step is establising a connection with a database - this generally involves creating a connection object
that will act as a 'proxy' for database operations - that is, the programmer invokes methods on the object that
actually interact with the database. The first stage - establishing the connection - usually involves some kind of
connection string that identifies the appropriate database, and perhaps contains other information:
using System.Data.SqlClient;
protected SqlConnection VisitConnection;
VisitConnection = new SqlConnection();
VisitConnection.ConnectionString = "workstation id=DELLDEVELOP;packet
size=4096;integrated security=SSPI;data source=" +
"DELLDEVELOP;persist security info=False;initial catalog=VisitDays";
The code above creates a new connection object VisitConnection and sets its ConnectionString
property. The string indicates: where the database is, what it's called, how access is authenticated, etc. Note that
there are alternatives to SqlConnection if you are using a different database to SQL Server (normally, you
would use OleDbConnection instead).
We could actually simplify this a bit (for example, there's no critical need to include packet
size=4096), and you can replace integrated security=SSPI with the somewhat more readable
Trusted_Connection=yes.) In fact a minimal string, in this case might look like:
server=DELLDEVELOP;database=VisitDays
Why do we need more complex strings? Well two reasons.

Configuration Options. You may have to, say, authenticate before accessing the database; or wish to (or
be required to) encrypt data; or deal with a mirrored database; or one of a number of other possibilites.
page 8

You might not have to. If you are using tools to generate strings, then you tend to get more complex
ones than strictly necessary - which can then make the whole concept a bit more complex than strictly
necessary.
9.3.2. Reading
The next thing we probably want to do is read from the database. To do this, we need to create and invoke a
query, and collect the results. Here is an example that checks a username and a password, and then attempts to
retrieve information based on that.
string user = Username.Text; //Username is a web form control
string pass = Password.Value; //So is Password
try
{
DataConnection.Open();
SqlCommand cmd =
new SqlCommand("select * from Applicant where
user=@user",DataConnection);
cmd.Parameters.Add("@user",SqlDbType.VarChar);
cmd.Parameters["@user"].Value = user;
SqlDataReader result = cmd.ExecuteReader();
if ((result.Read()) && pass.Equals("lemon"))
{
Session["user"] = result["user"];
Session["LastName"] = result["LastName"];
Session["FirstName"] = result["FirstName"];
Response.Redirect("AnotherPage.aspx");
}
else
{
ErrorLabel.Text = "Sorry, user not recognized - please try
again";
}
}
finally
{
DataConnection.Close();
}
Before we proceed, notice a few things:

Appalling Security. A very simple password that appears in plaintext is very poor.

Protected from SQL Injection. Those of you who know what a SQL injection attack is will see we are
using parameters to protect against it - if you don't know what it is, we'll be coming back to it later.

Response.Redirect Quite often we need to direct a user to another page - in this case, if the login
succeeds. We can do that with Response.Redirect which in this cases will result in another page
(AnotherPage.aspx) being displayed..
Concentrating on the database code, we first open the database connection, and then create a SqlCommand
object with a query string. We also add a new parameter. Then, we invoke the query, creating a result
object. If any results are returned (result.Read) we can access them from the result object
- e.g. result["LastName"]. We copy them into Session variables and jump to another page
(AnotherPage.aspx) - putting them in session variables means that their values will persist on the new page.
Whatever happens, we close the connection.
9.3.3. Non Queries
Executing commands that change the database is if anything even simpler - because we don't (necessarily) need
to deal with any returned data. Here is an example that inserts data into database, using two parameters:
command =
page 9
new SqlCommand("insert into SomeTable (Id,user) values
(@Id,@user)",DataConnection);
command.Parameters.Add("@user",SqlDbType.VarChar);
command.Parameters["@user"].Value = Session["user"];
command.Parameters.Add("@Id",SqlDbType.VarChar);
command.Parameters["@Id"].Value = e.Item.Cells[0].Text;
command.ExecuteNonQuery();
Notice the command ExecuteNonQuery() - previously, with the select operation, we used
ExecuteReader(). ExecuteNonQuery does actually return an int, which we can as here choose to
ignore. However, it does include useful information (usually the number of rows affected, or -1 in the event
of errors). Also, it can throw SqlException - though unlike Java, in C# we are not obliged to write code to
catch it.
9.4. The New Approach - SqlDataSource
The old way of accessing databases using SqlConnection is pretty simple really but it does require a fair
amount of code - code that is substantially similar between different applications. You can imagine yourself
writing quite a lot of lines starting with command.Parameters.Add for example, which gets a bit tedious
and is arguably pointless because such routine code can be generated automatically.. (Repetative code like this is
often called 'boilerplate' - the term comes from the use of hard steel plates (also used to make steam boilers) used
to print things like adverts in newspapers. The steel plates were hard and lasted a long time - unlike the much
softer (but recyclable) metal used to print the rest of the paper which only had to last one edition. The term is
also often used - a bit confusingly - for engineering test articles in aerospace. It's a lot cheaper to mock up a new
satelite or spacecraft in cheap steel than the expensive exotic and very light material you're going to have to use
for the real ones.)
The new approach hides most of the details and most of the code dissappears - although at the cost of making it
much less clear what is going on. The key concept is a control called SqlDataSource, which you can find in
the toobox under the data section.
9.4.1. The Events Database
For this example, create a database, or database file with two columns - year and event. The idea is that we can
store events and the corresponding year they occured. As a database, it's a bit poor - no primary key for example
- but never mind for now. We're also going to cheat a bit and use a wizard...
9.4.2. Configure Data Source
Start a new web project, and add a SqlDataSource object. In design view, you should see something like
this:
Fig.10. SqlDataSource
Click on 'Configure Data Source' and you'll get a configuration wizard - select your database, and follow the
steps:
page 10
Fig.11. Configuration Wizard
A few points to note:

Lost Wizard? If you don't see the Data Configuration link, then select the SqlDataSource object and click
the small triangle in the top right corner.

Store Connection String in Config File? Yes, you want to do this - it's good practice and means that you
can edit it independently of the code.

Select Statement. The wizard assumes - not unreasonably - that you will want to select some data from
your database. So you have to add a select statement. There are various ways to do this - I just chose to
display all the data in the table (see picture below).

Don't like wizards? If you don't want to interact with the wizard, then you can use the Properties Manager
as with every control - or directly edit the source code if you want.
page 11
Fig.12. Select Statement
9.4.3. Displaying the Data
Now we have actually selected data from our database, we are going to want to see it. There are various controls
for displaying organized data - and we want a GridView control. Drag one to the design and you can choose
the datasource to populate it:
Fig.13. GridView
And... that's it. Build and debug:
page 12
Fig.14. Running Example
Notice we have done this while writing no code.
9.5. Changing Data in the Database
Suppose we now want to modify our data? Take a look at the properties window for the SqlDataSource
object:
page 13
Fig.15. SqlDataSource Properties
page 14
Notice that as well as the highlighted property for Select, there are also properties for insert, update and delete.
We are going to modify our example to allow data to be updated. First, we set the update query string. Click on
the UpdateQuery property, and enter the following SQL:
UPDATE Table1 SET event =@event WHERE year=@year
You can either just type it in or use the Query Editor. You can also, if you've specified primary keys (which we
haven't) get the setup wizard to do this for you automatically - or at least have a good attempt at it. Note that
what we are doing here is simple but very insecure - see the Security
chapter for more information.
Now, select the GridView control, click the small triangle to bring the dialogue back up, and click 'Add New
Column:
page 15
Fig.16. Add New Column
Make sure the field type is 'ButtonField' and the Command Name is 'Edit'. The other things don't really matter
- Button type just controls the appearance of the button, but you should probably change the Text to something
like 'Edit'. but not doing so won't stop it working.
Now do the same again, but this time add a button column with a command name of 'Update'. Then build and
debug:
Fig.17. Edit Example
Again, we've done all of this with no code. Fairly obviously, we could do the same kind of thing for insert and
delete operations.