Tutorials
Books
Videos
Forums
This is an archived tutorial from the kirupa.com legacy collection. It covers software that may no longer be available, but it is kept online because the ideas still hold up.
When deciding which database to use with ASP.net, a common choice is to use Microsoft's own SQL Server. I prefer SQL Server because it integrates nicely with Visual Studio, and if you are just starting out learning how to use databases with your web site, you will appreciate many of tasks that are automated for you.
In this tutorial, I will explain how to create a simple data-driven web site that allows you to add data to and view data from a SQL Express 2005 database. There are many great tutorials on the web that outline how to use the Visual Studio wizards to accomplish this really easily, so I will provide a more code-oriented approach so that you have a better idea of how some of these things really work.
As a survey of what you will create, here is a birds-eye view of what our project will look like:
You will create two pages - one for sending data to the database (Games.aspx) and another page for retrieving data (Results.aspx) from the database. In order to get that to work, you will need to setup the database and specify the connection between them. Don't worry, for this tutorial will explain in great detail how to do all of this.
Before we begin, make sure you have the free Visual Web Developer and SQL Server Express 2005 installed. These programs feature most of the features of their full-version counterparts, so these programs are great for exploring .NET and for easily porting to their fully-featured counterparts in the future:
In this tutorial I will divide the main topics into the following sections:
With this said, we'll start the tutorial in the next section.
Now that you have a basic idea of what to expect from the previous section, let's first start by creating our project and setting up the database.
In this section you will create a new ASP.net project configured to use our database. The following steps should help you out:
[ go to File | Open | Web Site, and select Local IIS as your project location ]
[ create a new folder called dbTest ]
[ select the SQL Database template from the Templates view ]
[ give your new, empty SQL database the name Games.mdf ]
[ expand your App_Data folder to see where your database is ]
[ the Server Explorer gives you detailed access to your database's internals ]
[ the Add New Table view where you specify columns ]
[ create a new folder called dbTest ]
[ give your table the name gameTable ]
Getting the database created and the table configured will be your only direct interaction with the database in this tutorial. In the subsequent pages, you will learn how to connect and interact with the database.
In the previous section, you created a database and defined some table columns. Now, we need to figure out how to get our web pages to communicate with the database. In this page, I will explain how to do that by setting up a connection string.
Now that you have your database setup, it's time to create a way to add values to it. We will do that by first setting up a connection string that tells our applications and web pages how to communicate with our database:
[ create a new form called Games.aspx and set the language to Visual C# ]
[ make sure your Toolbox panel is displayed ]
[ find and double-click on the SqlDataSource control ]
[ click on the arrow to display the SqlDataSource Tasks menu ]
[ select the name of your database, Games.mdf ]
[ save this connection as GamesConnection ]
[ when asked to select the columns, check the * checkbox ]
You have now configured your Connection String. My main goal for doing this was to get our Web.Config file to store information about our database and how to access it. If you open your Web.Config file from your Solution Explorer, you will see a block of text corresponding to the steps we performed earlier:
[ how your connectionStrings node in Web.Config looks like ]
The data for the connection string is difficult to discern without using the wizard, especially if your web site is hosted on localhost or on your file system.
In the next section, let's create a simple input form that sends data to our database.
After the behind-the-scenes work you performed in the previous section, you are finally at a point where your users can directly see what you are working on.
We are finally finished with setting up our database and related details. It's all easy coasting and fun from here! In your Games.aspx page, create two textboxes and and a Submit button. It is entirely up to you on how you decide to design your form, but to give you an idea, here is how my form looks like:
[ how my input form for sending data to the database looks like ]
Regardless of how your form looks, make sure that your Name textbox has the ID txtGameName, the Platform textbox has the ID txtGamePlatform, and the Submit button has the name btnSubmit.
The code that I will have you write will refer to the above control names. Speaking of code, double-click on your Submit button to open your Games.aspx.cs file. Fill in the lines of code that are missing from your project (highlighted in yellow):
I will explain in greater detail what the lines of code do later, but for now, I first open a connection to the database, add in the data from our two textboxes, execute the commands to modify the database, and then close the connection.
Anyway, it's time to test our application. Press Ctrl + F5 to launch the browser and run our program. Type in some data into both of your textboxes and press the Submit button. You will receive a Page not Found message because we haven't created our Results.aspx file yet, so don't worry. If you do not receive any errors, proceed to the next section.
Depending on your computer setup, you may be asked via a scary yellow-colored error page to add some extra parameters to your Web.Config file. From earlier, you should be familiar with the ConnectionStrings area of Web.Config, and, for example, in Vista, I was requested to add the Asynchronous Processing=True line to my Web.Config's ConnectionString node.
For reference purposes, here is my full ConnectionString:
<connectionStrings>
<add name="GamesConnection" connectionString="Data
Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Games.mdf;
Integrated Security=True;User Instance=True;
Asynchronous Processing=True" providerName="System.Data.SqlClient"
/>
</connectionStrings>If your errors are more sinister errors, doing a simple web search for the error text should help you solve the problem. I have often found that closing Visual Studio and then manually visiting the localhost URL in the browser solves many problems also - especially "Cannot open user default database" errors.
For everybody else, there is always the next section where we talk about viewing the data you just added.
Quick recap! You created a database, setup the table, created the connection string, and designed your input form in the previous section. Now, it is time to view the data stored in your database.
All that is left is now to view the data you added to the database. If you recall, our code attempts to load the Results.aspx.cs file, but you received an error because that page does not exist. So, your first step is to create the Results.aspx file into your dbTest folder. Your dbTest folder should look like the following image:
[ create a new form called Results.aspx in your dbTest folder ]
Once you have your Results.aspx page created, open it for editing. It should be a blank page. Make sure your Toolbox is displayed, go to to the Data section and double-click on the GridView control. Your page should look like the following screenshot:
[ how your GridView control looks like ]
Once you see a sample GridView control on your page, we need to configure it to use our database. Select the GridView control, and then click on the small arrow that appears on the top-right corner of the control. Once you clicked on the arrow, the GridView Tasks menu will appear:
[ click on the arrow in your GridView control to display the GridView tasks ]
From the Choose Data Source drop-down menu that appears, select New Data Source. The Data Source Configuration window will appear. From this window, select the Database icon press OK:
[ select the Database icon from the Data Source Configuration Wizard ]
The rest of the screens should be familiar to you! From the Configure Data Source window that appears, click on the drop-down menu and select the Connection string you created earlier called GamesConnection:
[ since you already went through this process earlier, select GamesConnection ]
Once you have selected Game Connection, the next screen ask you which columns to display. Select the * selection and press Next:
[ select the * column to view all of the information ]
In the next and final screen, if you press the Test Query button, you should see the data you entered earlier via your Games.aspx form:
[ when you Test Query, you see all of the data currently stored in your database ]
Press the Finish button to close the wizard. If you now preview your Games.aspx file, enter some data into both the text fields, and click the Submit button, you will see that your Results.aspx page loads with the newly added data displayed along the data you added earlier.
Note - Copy/Paste SqlDataSource Control
You could have bypassed all of the above steps by copying the SqlDataSource control from your Games.aspx and pasting it into your Results.aspx. With your SqlDataSource control displayed, your Choose Data Source drop-down menu will contain the name of your SqlDataSource. Selecting that is all you would really need to do.
For example, here is how my Results.aspx form looks like in the browser:
[ how my results page looks like ]
If you are able to see the data you added on Games.aspx in the Results.aspx page, then you have successfully created a simple set of pages that add and display data from a database.
In the next section I will explain the code and briefly review some of the interesting things covered over the last many pages!
In the previous section you learned how to get a GridView control to display data from our database. You have everything you need to have a working input form with a results page, but you will get more out of this tutorial if you learn more about the code.
In your Games.aspx page, you copied and pasted code that I provided. In order to gain the most out of this tutorial, it is good for you to understand what the code you copied and pasted does. Let's start at the top:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;The above few lines are the namespaces to the various classes you used in your code. For this tutorial, all of the namespaces were provided as part of the default .NET Framework, so the above lines were written automatically provided for you.
SqlConnection connection;In this line, I am declaring a new variable of type SqlConnection called connection. The SqlConnection is responsible for setting up the connection with your database.
connection = new SqlConnection(ConfigurationManager.ConnectionStrings
["GamesConnection"].ConnectionString);Inside the Page_Load event handler, I initialized the connection variable I declared earlier. What I am doing is essentially initializing the SqlConnection object with the connection string you already have specified in your Web.Config file.
That is done via the ConnectionStrings["name"].ConnectionString code. Essentially, think of your Web.Config file as storing many connection strings for various database connections. What you are doing is targeting a specific connection string by passing in the connection string's name parameter. In our case, the connection string is called GamesConnection, so that is the name parameter I use.
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlCommand command = new SqlCommand("INSERT INTO gameTable(gameName,
gamePlatform) VALUES (@id_gameName, @id_gamePlatform)", connection);
SqlParameter nameContent = new SqlParameter("@id_gameName", SqlDbType.VarChar);
nameContent.Value = txtGameName.Text;
command.Parameters.Add(nameContent);
SqlParameter platformContent = new SqlParameter("@id_gamePlatform", SqlDbType.VarChar);
platformContent.Value = txtGamePlatform.Text;
command.Parameters.Add(platformContent);
connection.Open();
command.BeginExecuteNonQuery();
connection.Close();
Response.Redirect("Results.aspx");
}The above section of code represents the Click event handler attached to your button, btnSubmit. When your button is clicked, the code contained within it is executed.
SqlCommand command = new SqlCommand("INSERT INTO gameTable(gameName,
gamePlatform) VALUES (@id_gameName, @id_gamePlatform)", connection);The above line of code is, in my opinion, the most interesting of all of the code covered so far. What you are doing is essentially specifying how to send your data to the database, and that is accomplished by using an SqlCommand object.
All SQL commands are nothing more than strings, so you manually specify the SQL commands needed to insert the data from both of your textboxes:
"INSERT INTO gameTable(gameName, gamePlatform) VALUES (@id_gameName,
@id_gamePlatform)"The text INSERT INTO specifies that data will be sent to the database, and the gameTable text refers to the table I will be passing the data into. Our gameTable table, if you remember, contains two columns called gameName and gamePlatform.
The actual data passed in are specified after the VALUES text, and the number of values corresponds to the number of columns your table will have. Essentially this is the format your insert statement takes up:
INSERT INTO tableName(column1, column2) VALUES ("data", "data")If you notice, nowhere in the SQL command do I specify the name of our database (games.mdf) or the connection string. That is because the SqlCommand constructor takes in two arguments - the SQL command string that you just saw and the SqlConnection object that I created earlier..
SqlParameter nameContent = new SqlParameter("@id_gameName",
SqlDbType.VarChar);In this line of code, I am specifying a SQL parameter called nameContent. What I am doing is exchanging a string of text "@id_gameName" with a real value. In order to do that, I need to designate that text as a parameter as seen above, and I set the type of the parameter to be SqlDbType.VarChar.
nameContent.Value = txtGameName.Text;With this line, I set the value of our nameContent SqlParameter to the text entered into our txtGameName textbox. Like I hinted at earlier, this is also the line where @id_gameName text is replaced with the text entered into textGameName textbox.
command.Parameters.Add(nameContent);Now that our nameContent SqlParameter is all set, we add it to our SqlCommand object, command, for sending instructions to the database.
SqlParameter platformContent = new SqlParameter("@id_gamePlatform",
SqlDbType.VarChar);
platformContent.Value = txtGamePlatform.Text;
command.Parameters.Add(platformContent);These three lines accomplish the same thing as the three lines I covered earlier. We designate the @id_gamePlatform text from our SQL command string as a SQL parameter and set its value to txtGamePlatform.Text. In the third line, I add this parameter to my command string.
connection.Open();
command.BeginExecuteNonQuery();
connection.Close();The above lines open our database connection, tell the database to execute our command, and then close our database connection. Pretty simple, but you must perform those operations in that exact order.
You cannot send commands to the database without first opening the connection, and unless you send the commands to the database, your database will not see any of the changes. Finally, you close the connection after you are done.
Response.Redirect("Results.aspx");The above line simply loads the Results.aspx page after all of the lines have finished executing. This is the equivalent of displaying a confirmation page after the user submits a form. Actually, the end result is exactly the same as a confirmation page.
Well, after this many pages, you are finally done. This tutorial covered a lot of ground. It started off by first explaining how our pages will interact with the database, and then we dove right into the implementation of the database and the Games and Results pages.
All of this may seem like a lot of work to do something that seems simple, but don't let the number of pages in this tutorial trick you. It shouldn't take more than a few minutes to setup the table, create the columns, and use Visual Studio's built-in wizards for setting up the connection string. Once your connection string is setup, it takes very little effort to use the built-in controls as you saw with the GridView.
Because I wanted you to get a feel for writing some code, I spent some time explaining what the code does. For the most part, you can get away with simulating the functionality of both our Games.aspx and Results.aspx pages without writing any code at all.
Just a final word before we wrap up. What you've seen here is freshly baked content without added preservatives, artificial intelligence, ads, and algorithm-driven doodads. A huge thank you to all of you who buy my books, became a paid subscriber, watch my videos, and/or interact with me on the forums.
Your support keeps this site going! 😇
:: Copyright KIRUPA 2026 //--