Creating an ASP.NET Database using MS SQL 2008 in Visual Web Developer 2008

A web database is the heart of any dynamic website operation, because it contains the content that will be displayed to web browsers. Without a database, a website is completely inoperable. For example, what would happen to Amazon if their products databases were deleted or gone? Of course the website would be damaged, and online shoppers would not be able to see their products.

It is therefore important to have a database. Adding it is not only trendy (almost all websites operate with a database), but it is the right solution for increasingly big and complex websites.

Basic Requirements for Development and Production Servers

If you are a developer and use ASP.NET to develop websites, planning is important to make the development stage match with the production stage of the website. For example, if you are using Microsoft SQL Server 2008 as the database for your ASP.NET website, you should also be deploying it in a hosting environment that supports both ASP.NET and MS SQL databases.

Even this is not enough; you should also note the versions. In this tutorial, we will use ASP.NET 3.5 in an MS SQL 2008 database. If you deploy it in your production hosting environment to launch the website on the Internet, make sure it supports ASP.NET 3.5 and the MS SQL 2008 database.

There have been hosting environments that support ASP.NET on Linux/Unix operating systems -- for example, the Mono project. However, as of February 2010, most hosting can only support ASP.NET 2.0. A lot of incompatibility issues can arise from the use of different operating systems to host ASP.NET websites. To avoid these complexities, I recommend that you stick with a Microsoft OS and IIS (Internet Information Server).

In the development phase, you must ensure that your local computer meets the following requirements:

1. Windows operating system (Windows XP Home Service Pack 3/Windows 7/XP professional is recommended)

2. Fully installed Visual Web Developer Express 2008. This full installation should be included with Microsoft SQL Server 2008. With a full installation, the database software is already bundled with Visual Web Developer Express.

You can read this tutorial for the installation instructions of Visual Web Developer Express 2008: http://www.aspfree.com/c/a/ASP.NET/Develop-Your-First-ASPNET-Website-with-Visual-Web-Developer-Express/2/

To maximize the use of this tutorial, it is helpful if you already have some basic understanding of the development of basic ASP.NET websites. You'll find it worthwhile to read the following articles:

http://www.aspfree.com/c/a/ASP.NET/Develop-Your-First-ASPNET-Website-with-Visual-Web-Developer-Express/

http://www.aspfree.com/c/a/ASP.NET/Building-a-Static-ASPNET-Website-in-a-Basic-Hosting-Environment/

http://www.aspfree.com/c/a/ASP.NET/Adding-Content-to-a-Static-ASPNET-Website/

Creating your first SQL Server 2008 database

The following are the steps you need to take to create the database in ASP.NET:

Step 1: Launch Visual Web Developer Express 2008.

Step 2: Go to File -> New Website -> under “Visual Studio Installed Templates,” click “ASP.NET Web Site”

Step 3: Configure location -> “File system,” Language -> “Visual Basic”

Step 4: Specify a path where you would like to save your website files; for example:

E:firstdatabasetest

The above tells Visual Web Developer to save the created .NET files in Drive E of the Windows computer using the folder name “firstdatabasetest.”

Step 5: Website files are automatically created after pressing OK. Next, look for “App_Data” under Solution Explorer. Right click on it, and then “Add New item.”

Step 6: Under Visual Studio installed templates, select “SQL Server Database.” Change the name to “Firstdatabase.mdf” and then set the language to “Visual Basic.” Finally, click “Add.”

See the screen shot below showing these processes:

Creating tables in your Firstdatabase.mdf

Now that we have created a database, we need to create tables under it.  For example, we would like to create the following database tables:

Table name: movies

Fields1: movieid

Fields2: movietitle

Fields3: moviegenre

Fields4: runningtime

Fields5: director

Fields6: datereleased

We want to set this up such that movieid is used to uniquely identify each record in the database and will be set to auto-increment each time a record is added (e.g. 1 at first, then 2 for the second record, so on and so forth).

After creating the database, you can see that the Solution Explorer will be replaced with Database Explorer, and you should see the following items: Database diagrams, Tables, Views, Stored Procedures, functions, etc.

To create a table in your firstdatabase.mdf, right click on “Tables” and click “Add New Table.” You should be able to see the “Column Name,” “Data Type” and “Allow Nulls”.

In the column name is where you will type your database table fields. Under data type you will have the three most common options:

1.) nvarchar(number of characters)

2.) int

3.) datetime

There are still other data types, which you could learn about from the following page: http://www.teratrax.com/sql_guide/data_types/sql_server_data_types.html

The following are the recommended data types for the movie table field names (of course in your own application, you can choose your own data types):

movieid = int

movietitle= nvarchar(175)

moviegenre = nvarchar(100)

runningtime = int

director = nvarchar(150)

datereleased = datetime

If you uncheck “Allow Nulls,” it means that you will not allow null entries for that specific field in the database records; in other words, that field should be filled. 

Enter the column name (field names) as well as their respective data types and Allow null conditions. For this database, say that we are not allowing null entries, so the completed table structure for the “movie” database looks like the screen shot below:

Finally, to save the database, click the “Save” icon in the toolbar, and then Visual Web Developer will ask you for the name of the table. Delete the default name shown, like tables or tables1, tables2, and replace it with movies, and then click OK.

However, since you are going to use movieid fields to uniquely identify each record in the database, click the movieid row (until you highlight the entire row). Next,  click “set primary key” (using key icon in the toolbar). See the screen shot below:

Finally, to automatically increment the movieid for every record entered in the database, see “Column Properties,” which is below the Table. Scroll down until you can see “Identity Specification.” Expand it, and click “Is identity.” Change from No to Yes (see screen shot below).

Adding Data to the Movie Table

The database table is still empty. To fill it up with records, you need to go to Database Explorer -> Tables (then expand it) -> movies; then right click on it and choose “Show Table data.” See screen shot below for the guide:

As you can see, it is still empty. To enter data, just click on the cell, just as you do  in MS Excel. But to move from cell to cell, you can use the TAB key.

Note: Since Movieid is the unique identifier, you do not need to enter values or data for movieid; the database will automatically populate it with data.

For example, if you want to populate the database with the following records:

Movie Title: Alien

Movie Genre: Horror

Running Time (in minutes): 117

Director: Ridley Scott

Date Released: May 25, 1979

as well other sample movies you like, the database table will look like the screen shot below:

It is highly recommended that you read the next tutorial: Retrieving Data from Microsoft SQL Server 2008 using ASP.NET, which will discuss data retrieval from an MS SQL database and displaying it on the browser.