Intro to SQL Server with ASP.NET - Page 6
       by kirupa  |  25 December 2006

In the previous page 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.

Code Explained
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.

Conclusion
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. If you have a question and/or want to be part of a friendly, collaborative community of over 220k other developers like yourself, post on the forums for a quick response!

Need help?

Ask on the forums!

• Previous Article
• Next Article

 

1 | 2 | 3 | 4 | 5 | 6