Moving On Up
Last time out, I gave you a quick introduction to ADO.NET and how it differs from its predecessor, together with an example of how to get a connection up and running between an ASP.NET page and a SQL Server 2000 RDBMS. I also introduced you to some of the basic objects used to retrieve and iterate over a record set from the database, including the ASP.NET DataGrid server control which lets you quickly display a result set table.
Of course, I’m not done yet - all I’ve shown you so far is how to get data out of the database. But what about putting it in, or changing whatever’s already inside? Well, that’s where the SqlDataAdapter and DataSet objects come in - they work closely together to give developers full-fledged database access, including the ability to write new records or edit/delete existing records. Over the course of this article, I’ll show you how to use these two objects to carry out the entire spectrum of SQL operations, including INSERTs, UPDATEs and DELETEs. So keep reading.
Set-ting Up
In the last segment of this tutorial, I used a SqlDataReader object to retrieve records and display them with a “while” loop. Sadly, that’s about all you can do with this object - if you remember what I said last time, the SqlDataReader object returns a read-only set of records which can only be accessed in the forward direction. Furthermore, as mentioned earlier, the SqlDataReader object is meant for a connected system, wherein a database connection is maintained between the server and the client for the entire duration of the session.
Needless to say, this can affect system resources significantly. Which is why ADO.NET also supports “disconnected data access”, via two other objects: the SqlDataAdapter object and the DataSet object.
First, the SqlDataAdapter object acts a bridge between the data stored in the database and a DataSet object, which is used to display to the data to the end user. This DataSet object is used to the hold the data ferried by the SqlDataAdapter from the database, and it works like a mini-database by caching the data locally on the client and allowing the user to make changes to it. Once the changes are complete, the SqlDataAdapter object updates the live database with your changes.
Since this DataSet is cached locally on the client, this approach allows for speedy access and manipulation of data, without having to keep the connection to the server open at all times. Further, the DataSet object even supports XML and can thus be used to create XML representations of the query result set and send it over HTTP to any location on the network (internal or external). In this serialized XML form, the result set becomes an ordinary string of data hat can easily be transferred across a network without the need for proprietary protocols.
What does this translate to in terms of code? Let’s see.
The XML Files
Here’s an example:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="C#" runat="server">
void Page_Load()
{
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// the SQL query
string strSQL = "SELECT * FROM starwars";
// create an SqlDataAdapter object
SqlDataAdapter objAdapter =new SqlDataAdapter(strSQL,objConn);
// create a new DataSet object
DataSet objDataSet = new DataSet();
// populate the DataSet object
objAdapter.Fill(objDataSet, "starwars");
// bind the DataGrid to the DataSet
// this will populate the DataGrid automatically
starwars.DataSource = objDataSet;
starwars.DataBind();
// clear up memory by closing all objects
objDataSet.Clear();
objConn.Close();
}
</script>
<html>
<head></head>
<body>
<asp:datagrid id="starwars" runat="server" />
</body>
</html>
The output is nothing to write home about - all the records from the “starwars” table are displayed in a neat table - but the details make for interesting reading.
Once the required namespaces have been imported into the script, it’s time to start creating some objects.
<%
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// the SQL query
string strSQL = "SELECT * FROM starwars";
// create an SqlDataAdapter object
SqlDataAdapter objAdapter =new SqlDataAdapter(strSQL,objConn);
%>
The SqlConnection object sets the ball rolling. It’s followed by the new SqlDataAdapter object, which acts as the connection between the database and the user interface; this object requires an SQL SELECT query and the newly-created SqlConnection object as constructor arguments. I’ve provided both in the example above.
Once the SqlDataAdapter is in place, the next step is to associate it with a DataSet object and then bind it to a user interface (in this example, a ASP.NET DataGrid control).
<%
// create a new DataSet object
DataSet objDataSet = new DataSet();
// populate the DataSet object
objAdapter.Fill(objDataSet, "starwars");
%>
Here, I’ve used the Fill() method of the SqlDataAdapter object to populate the DataSet object. The second parameter to Fill() specifies the name for the DataTable which will store the information within the DataSet.
Wanna take a quick peek inside the newly-populated DataSet object? It’s easy - just use the GetXML() method of the DataSet object, like this:
<%
// populate the DataSet object
objAdapter.Fill(objDataSet, "starwars");
// output the data in XML format
Response.Write(objDataSet.GetXml());
%>
and you should see something like this:
<NewDataSet>
<starwars>
<id>1</id>
<name>Darth Maul</name>
<homeworld>Iridonia</homeworld>
<species>Zabrak</species>
<gender>Male</gender>
<affiliation>Sith</affiliation>
</starwars>
<starwars>
<id>2</id>
<name>Obi-Wan Kenobi </name>
<homeworld>NA</homeworld>
<species>Human</species>
<gender>Male</gender>
<affiliation>Jedi</affiliation>
</starwars>
<starwars>
<id>3</id>
<name>Qui-Gon Jinn</name>
<homeworld>NA</homeworld>
<species>Human</species>
<gender>Male</gender>
<affiliation>Jedi</affiliation>
</starwars>
<starwars>
<id>4</id>
<name>C-3PO </name>
<homeworld>Tatooine</homeworld>
<species>Droid</species>
<gender>NA</gender>
<affiliation>Rebel Alliance</affiliation>
</starwars>
<starwars>
<id>5</id>
<name>Luke Skywalker</name>
<homeworld>Tatooine</homeworld>
<species>Human</species>
<gender>Male</gender>
<affiliation>Jedi</affiliation>
</starwars>
<starwars>
<id>6</id>
<name>Darth Vader</name>
<homeworld>Tatooine </homeworld>
<species>Human</species>
<gender>Male</gender>
<affiliation>Empire</affiliation>
</starwars>
</NewDataSet>
OK, back to business. At this point, I have populated the DataSet object, but still have an empty “starwars” DataGrid to populate. How do I do that?
<%
// bind the DataGrid to the DataSet
// this will populate the DataGrid automatically
starwars.DataSource = objDataSet;
starwars.DataBind();
%>
As shown above, all I need to do is set the newly populated DataSet object as the data source for the “starwars” DataGrid. The DataBind() method of the DataSet object takes care of the rest. Here’s the output:
You saw something like this in the previous segment of this tutorial also. But before you say “been there, done that” to the script above, I should tell you that I’ve taken you through the above example again as a prelude to actually manipulating the database. Keep reading, that’s coming up next.
In With The New
Let’s now look at adding a new record to the database.
<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="C#" runat=server>
void Page_Load()
{
if(IsPostBack)
{
if(name.Value == "" )
{
error.Style["color"] = "Red";
error.Text = "Please enter your name.";
return;
}
else
{
// we have all the data,
// let's insert it
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// the SQL query
string strSQL = "SELECT * FROM starwars";
// create an SqlDataAdapter object
SqlDataAdapter objAdapter = new SqlDataAdapter(strSQL, objConn);
// create a new DataSet object
DataSet objDataSet = new DataSet();
// populate the DataSet object
objAdapter.Fill(objDataSet, "starwars");
// create an instance of the DataTable
// in order to add a new record
DataTable objTab = objDataSet.Tables["starwars"];
// add a new row to our local DataTable object
DataRow objNewRecord = objTab.NewRow();
// add the data from the form
// into the new row
objNewRecord["name"] = name.Value;
objNewRecord["homeworld"] = homeworld.Value;
objNewRecord["species"] = species.Value;
objNewRecord["gender"] = gender.Value;
objNewRecord["affiliation"] = affiliation.Value;
// add the new record to our local DataTable object
objTab.Rows.Add(objNewRecord);
// build the required SQL command
// automatically using the CommandBuilder Object
SqlCommandBuilder objCmdBuilder = new SqlCommandBuilder(objAdapter);
objAdapter.InsertCommand = objCmdBuilder.GetInsertCommand();
// update the database server to reflect the changes
objAdapter.Update(objDataSet, "starwars");
// clear up memory by closing all objects
objDataSet.Clear();
objConn.Close();
// clear the Form fields
name.Value = "";
homeworld.Value = "";
species.Value = "";
gender.Value = "";
affiliation.Value = "";
error.Text = "Record added successfully.";
return;
}
}
}
</script>
<html>
<head>
<basefont face="Arial">
</head>
<body>
<form runat="server">
<div align="center">
<table cellpadding="10" cellspacing="5" border="2" >
<tr>
<td align="center" colspan="2"><b>Registration Desk</b></td>
</tr>
<tr>
<td colspan=2>
<asp:label id="error" name="error" runat=server text="* - Indicates compulsory field" />
</td>
</tr>
<tr>
<td>Name<b>*</b></td>
<td><input type="text" id="name" runat=server value=""/></td>
</tr>
<tr>
<td>Home World</td>
<td><input type="text" id="homeworld" runat=server value=""/></td>
</tr>
<tr>
<td>Species</td>
<td><input type="text" id="species" runat=server value=""/></td>
</tr>
<tr>
<td>Gender</td>
<td><input type="text" id="gender" runat=server value=""/></td>
</tr>
<tr>
<td>Affiliation</td>
<td><input type="text" id="affiliation" runat=server value=""/></td>
</tr>
<tr>
<td align="center" colspan="2"><input type="submit" id="submit" value="Submit" runat=server></td>
</tr>
<tr>
<td align="center" colspan="2"><a href="list.aspx">Get complete listing</a></td>
</tr>
</table>
</div>
</form>
</body>
</html>
That’s a lot of code for a simple INSERT statement. But don’t despair - I’ll dissect it with you and it should all make sense shortly.
Basically, the above code listing consist of two parts. The first part displays an HTML form and asks the user for input, while the second part takes that user input and inserts it into the database. Take a look at the skeletal structure of the code, which illustrates this clearly:
<script language="C#" runat=server>
void Page_Load()
{
if(IsPostBack)
{
// all the code for data validation and insertion
}
}
</script>
<!-- form goes here -->
Here, the web server will use the “IsPostBack” variable to check if the form is being displayed for the first time to the user, or whether it is POST-ing data submitted by the user. If the former, then the value of “IsPostBack” variable is false. As a result, the compiler skips the entire block of code responsible for data insertion, and the browser then proceeds to render the HTML form to the user. On the other hand, if the user was indeed submitting data to the server, then the “IsPostBack” variable will be true and the code to insert the record into the database will be triggered.
Let’s begin by taking a look at the HTML form:
<form runat="server">
<div align="center">
<table cellpadding="10" cellspacing="5" border="2" >
<tr>
<td align="center" colspan="2">
<b>Registration Desk</b>
</td>
</tr>
<td colspan=2>
<asp:label id="error" name="error" runat=server text="* - Indicates compulsory field" />
</td>
<tr>
<td>Name<b>*</b></td>
<td><input type="text" id="name" runat=server value=""/></td>
</tr>
<tr>
<td>Home World</td>
<td><input type="text" id="homeworld" runat=server value=""/></td>
</tr>
<tr>
<td>Species</td>
<td><input type="text" id="species" runat=server value=""/></td>
</tr>
<tr>
<td>Gender</td>
<td><input type="text" id="gender" runat=server value=""/></td>
</tr>
<tr>
<td>Affiliation</td>
<td><input type="text" id="affiliation" runat=server value=""/></td>
</tr>
<tr>
<td align="center" colspan="2"><input type="submit" id="submit" value="Submit" runat=server></td>
</tr>
<tr>
<td align="center" colspan="2"><a href="list.aspx">Get complete listing</a></td>
</tr>
</table>
</div>
</form>
If you’ve been following along, you’ll see that this is a plain-vanilla HTML form which merely displays various input fields to the user. Here’s what it looks like:
The user is compulsorily required to enter a name - hence the following lines of code, which verify the presence of this information.
<%
if(IsPostBack)
{
if(name.Value == "" )
{
error.Style["color"] = "Red";
error.Text = "Please enter your name.";
return;
}
else
{
// name exists
// insert the record
}
}
%>
Here’s what happens if the data is absent:
If things are all hunky-dory, I can go ahead and prepare to insert the record. This involves the usual suspects: creating an SqlConnection object by providing database access details and user credentials, and then creating a SqlDataAdapter to connect to the database. Since I plan to update the “starwars” table, a simple SELECT query is needed to fetch the contents of this table from the database; the result set can then be bound to the SqlDataAdapter:
<%
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// the SQL query
string strSQL = "SELECT * FROM starwars";
// create an SqlDataAdapter object
SqlDataAdapter objAdapter =new SqlDataAdapter(strSQL,objConn);
// create a new DataSet object
DataSet objDataSet = new DataSet();
// populate the DataSet object
objAdapter.Fill(objDataSet, "starwars");
%>
The next step is to create a DataTable object and store the result set in it - this is needed to manipulate the information using the different methods of the DataTable object.
<%
// create an instance of the DataTable
// in order to add a new record
DataTable objTab = objDataSet.Tables["starwars"];
%>
Once a DataTable object has been instantiated, a new DataRow object can be created via the NewRow() method of the object. The fields of this row can now be accessed as elements, and the data entered into the form by the user can be inserted into this row simply by assigning them to the appropriate fields.
<%
// add a new row to our local DataTable object
DataRow objNewRecord = objTab.NewRow();
// add the data from the form
// into the new row
objNewRecord["name"] = name.Value;
objNewRecord["homeworld"] = homeworld.Value;
objNewRecord["species"] = species.Value;
objNewRecord["gender"] = gender.Value;
objNewRecord["affiliation"] = affiliation.Value;
// add the new record to our local DataTable object
objTab.Rows.Add(objNewRecord);
%>
At this point, you’ve essentially inserted a new record into the DataTable. But this isn’t enough in itself - the data still needs to be saved to the actual database. In order to do this, it is necessary to instantiate another object, the SqlCommandBuilder object, which takes care of building the SQL commands needed to transfer the changes made in the local copy to the database server.
<%
// build the required SQL command
// automatically using the CommandBuilder Object
SqlCommandBuilder objCmdBuilder = new SqlCommandBuilder(objAdapter);
objAdapter.InsertCommand = objCmdBuilder.GetInsertCommand();
// update the database server to reflect the changes
objAdapter.Update(objDataSet, "starwars");
%>
The first step is to give the SqlCommandBuilder object something to start with - here, the SqlDataAdapter. If you remember, this was created using a simple SELECT query. Now, this object will proceed to automatically create the SQL INSERT, UPDATE or DELETE commands needed after studying the SELECT query.
Since this example is all about inserting data into the table, the GetInsertCommand() method of the SqlCommandBuilder object is all I need, followed by a call to the Update() method of the SqlDataAdapter to execute this command on the server. For the records, you can use the GetUpdateCommand() and GetDeleteCommand() methods as well for updates and deletions.
You can view the newly-inserted record by checking the records on the server, or by using the very first script in this article to display all the records from the database.
Winds of Change
Now, how about updating the database? Well, it’s a little more involved. First, I’ll need some code to list all the available records and allow the user to edit any of them. And before all you ASP.NET purists start screaming about the blatant use of in-line coding (contrary to the best practices described for this platform), I want to emphasize that this has been done only for simplicity in explanation.
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.SqlClient"%>
<html>
</head>
<body>
<script language="C#" runat="server">
void Page_Load()
{
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand("SELECT * FROM starwars;", objConn);
// open the connection
objConn.Open();
// populate an SqlDataReader object
SqlDataReader objReader = objCommand.ExecuteReader();
// output some HTML code
Response.Write("<basefont face=\"Arial\">");
Response.Write("<div align=\"center\">");
Response.Write("<p><b>Star Wars - Registration Desk</b></p>");
Response.Write("<table border=\"1\" cellspacing=\"2\" cellpadding=\"2\">");
Response.Write("<tr>");
// display the fields in the
for(int count = 0; count < objReader.FieldCount; count++)
{
Response.Write("<td><b>" + objReader.GetName(count).ToUpper() + "</b></td>");
}
Response.Write("<td><b>EDIT</b></td></tr>");
// read each record from the result set and display in the table
while(objReader.Read())
{
Response.Write("<tr>");
Response.Write("<td>" + objReader.GetValue(0) + "</td>");
Response.Write("<td>" + objReader.GetValue(1) + "</td>");
Response.Write("<td>" + objReader.GetValue(2) + "</td>");
Response.Write("<td>" + objReader.GetValue(3) + "</td>");
Response.Write("<td>" + objReader.GetValue(4) + "</td>");
Response.Write("<td>" + objReader.GetValue(5) + "</td>");
Response.Write("<td><a href=\"edit.aspx?cid=" + objReader.GetValue(0) + "\">Edit</a></td>");
Response.Write("</tr>");
}
Response.Write("</table>");
Response.Write("</div>");
// clear up memory by closing all objects
objReader.Close();
objConn.Close();
}
</script>
</html>
This is identical to the example I used in the previous segment of this tutorial. One minor addition - an “Edit” button appears next to each row, linking to “edit.aspx” and passing it the record ID.
Here’s what it looks like:
The script “edit.aspx” displays the values for the selected record in a form, and allows the user to make changes to it. Here it is:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="C#" runat=server>
string strName;
string strHomeworld;
string strSpecies;
string strGender;
string strAffiliation;
string strCid;
void Page_Load()
{
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// the SQL query
string strSQL = "SELECT * FROM starwars";
// create an SqlDataAdapter object
SqlDataAdapter objAdapter =new SqlDataAdapter(strSQL,objConn);
// create a new DataSet object
DataSet objDataSet = new DataSet();
// populate the DataSet object
objAdapter.Fill(objDataSet, "starwars");
// create an instance of the DataTable
// in order to add a new record
DataTable objTab = objDataSet.Tables["starwars"];
DataRow[] objRowToChange = objTab.Select("id = " + Request.QueryString["cid"]);
// populate the form fields
strName = objRowToChange[0]["name"].ToString();
strHomeworld = objRowToChange[0]["homeworld"].ToString();
strSpecies = objRowToChange[0]["species"].ToString();
strGender = objRowToChange[0]["gender"].ToString();
strAffiliation = objRowToChange[0]["affiliation"].ToString();
strCid = Request.QueryString["cid"].ToString();
// clear up memory by closing all objects
objDataSet.Clear();
objConn.Close();
}
</script>
<html>
<head>
<basefont face="Arial">
</head>
<body>
<form action="update.aspx" method="POST">
<div align="center">
<table cellpadding="10" cellspacing="5" border="2" >
<tr>
<td align="center" colspan="2"><b>Registration Desk</b></td>
</tr>
<tr>
<td>Name<b>*</b></td>
<td><input type="text" name="name" value="<% Response.Write(strName); %>" /></td>
</tr>
<tr>
<td>Home World</td>
<td><input type="text" name="homeworld" value="<% Response.Write(strHomeworld); %>" /></td>
</tr>
<tr>
<td>Species</td>
<td><input type="text" name="species" value="<% Response.Write(strSpecies); %>" /></td>
</tr>
<tr>
<td>Gender</td>
<td><input type="text" name="gender" value="<% Response.Write(strGender); %>" /></td>
</tr>
<tr>
<td>Affiliation</td>
<td><input type="text" name="affiliation" value="<% Response.Write(strAffiliation); %>" /></td>
</tr>
<tr>
<input type="hidden" name="cid" value="<% Response.Write(strCid); %>" />
<td align="center" colspan="2"><input type="submit" id="submit" value="Submit"></td>
</tr>
</table>
</div>
</form>
</body>
</html>
The code starts by declaring the variables that will be used to store the values from the database and populate the HTML form fields later.
<%
string strName;
string strHomeworld;
string strSpecies;
string strGender;
string strAffiliation;
string strCid;
%>
Next, the SqlConnection, SqlDataAdapter, DataSet and DataTable objects are created as before, and the fields of the selected record are extracted from the database. I’ve not done this in the traditional way - instead, I’ve used the Select() method of the DataTable object. This allows me to specify a simple condition to filter out all but the required row(s). The result set generated by the query is stored in an array called “objRowToChange”; this is an array of DataRow objects, and can contain more than one row (if the query returns multiple records).
<%
// create datatable object
DataTable objTab = objDataSet.Tables["starwars"];
DataRow[] objRowToChange = objTab.Select("id = " + Request.QueryString["cid"]);
// populate the form fields
strName = objRowToChange[0]["name"].ToString();
strHomeworld = objRowToChange[0]["homeworld"].ToString();
strSpecies = objRowToChange[0]["species"].ToString();
strGender = objRowToChange[0]["gender"].ToString();
strAffiliation = objRowToChange[0]["affiliation"].ToString();
strCid = Request.QueryString["cid"].ToString();
%>
Now, all that’s left is to access the appropriate fields of the record, convert them into strings via the ToString() method, and then use Response.Write() to write these values to the relevant HTML form elements. Here’s the result:
Note that this HTML form submits to another ASPX page, aptly called “update.aspx”, which does the actual UPDATE. Let’s take a quick peek at that next, shall we?
Command And Control
The “update.aspx” script takes the data entered into the form and uses it to update the corresponding database record. Here’s how:
<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="C#" runat=server>
void Page_Load()
{
if(Request.Form["name"] == "" )
{
output.Style["color"] = "Red";
output.Text = "<p>Please enter your name.</p>";
url.Text = "<p><a href=\"javascript:history.go(-1)\">Click here to go back.</a></p>";
return;
}
else
{
// we have all the data,
// let's update
// build the connection string
string strConn = "user id=john;password=secret;";
strConn += "initial catalog=pubs;data source=tatooine;";
// create an instance of the SqlConnection object
SqlConnection objConn = new SqlConnection(strConn);
// build the SQL query first
String strSQL = "UPDATE starwars SET name = @name, homeworld = @homeworld, species = @species, gender = @gender, affiliation = @affiliation WHERE id = @id";
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand(strSQL, objConn);
// populate the variables in our UPDATE query above
// the "name" field
objCommand.Parameters.Add(new SqlParameter("@name", SqlDbType.VarChar, 50));
objCommand.Parameters["@name"].Value = Request.Form["name"];
// the "homeworld" field
objCommand.Parameters.Add(new SqlParameter("@homeworld", SqlDbType.VarChar, 50));
objCommand.Parameters["@homeworld"].Value = Request.Form["homeworld"];
// the "species" field
objCommand.Parameters.Add(new SqlParameter("@species", SqlDbType.VarChar, 50));
objCommand.Parameters["@species"].Value = Request.Form["species"];
// the "gender" field
objCommand.Parameters.Add(new SqlParameter("@gender", SqlDbType.VarChar, 50));
objCommand.Parameters["@gender"].Value = Request.Form["gender"];
// the "affiliation" field
objCommand.Parameters.Add(new SqlParameter("@affiliation", SqlDbType.VarChar, 50));
objCommand.Parameters["@affiliation"].Value = Request.Form["affiliation"];
// the "id" field
objCommand.Parameters.Add(new SqlParameter("@id", SqlDbType.Int, 4));
objCommand.Parameters["@id"].Value = Request.Form["cid"];
// uncomment this for debugging
// Response.Write(objCommand.CommandText);
// open the connection
objConn.Open();
// execute the query
objCommand.ExecuteNonQuery();
// display message to user
output.Style["color"] = "Green";
output.Text = "<p>Record updated successfully.</p>";
url.Text = "<p><a href=\"./listing.aspx\">Click here for list</a></p>";
// close all
objConn.Close();
}
}
</script>
<html>
<head>
<basefont face="Arial">
</head>
<body>
<div align="center">
<b> Registration Desk</b>
<asp:label id="output" name="output" runat=server />
<asp:label id="url" name="url" runat=server />
</div>
</body>
</html>
The first part of this script is identical to the one I wrote earlier, so I’ll jump straight to the parts that are different. Here, the primary difference lies in the way the UPDATE query is constructed - since the query uses form variables, it has been created using placeholder variables that will eventually be replaced with the actual data entered by the user.
<%
// build the SQL query first
String strSQL = "UPDATE starwars SET name = @name, homeworld = @homeworld, species = @species, gender = @gender, affiliation = @affiliation WHERE id = @id";
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand(strSQL, objConn);
%>
And here’s the code that actually performs the variable interpolation:
<%
// populate the variables in our UPDATE query above
// the "name" field
objCommand.Parameters.Add(new SqlParameter("@name",SqlDbType.VarChar,50));
objCommand.Parameters["@name"].Value = Request.Form["name"];
// the "homeworld" field
objCommand.Parameters.Add(new SqlParameter("@homeworld",SqlDbType.VarChar,50));
objCommand.Parameters["@homeworld"].Value = Request.Form["homeworld"];
// the "species" field
objCommand.Parameters.Add(new SqlParameter("@species",SqlDbType.VarChar,50));
objCommand.Parameters["@species"].Value = Request.Form["species"];
// the "gender" field
objCommand.Parameters.Add(new SqlParameter("@gender",SqlDbType.VarChar,50));
objCommand.Parameters["@gender"].Value = Request.Form["gender"];
// the "affiliation" field
objCommand.Parameters.Add(new SqlParameter("@affiliation",SqlDbType.VarChar,50));
objCommand.Parameters["@affiliation"].Value = Request.Form["affiliation"];
// the "id" field
objCommand.Parameters.Add(new SqlParameter("@id",SqlDbType.Int,4));
objCommand.Parameters["@id"].Value = Request.Form["cid"];
%>
The SqlCommand object’s “Parameters” property, which is actually a collection of SQL parameters, stores the values for the various placeholders in the UPDATE query. Once they’ve all been set, the data can be committed to the database by calling the SqlCommand object’s ExecuteNonQuery() method. This method is used to execute SQL commands such as INSERT, DELETE, and UPDATE, that do not return any values.
You can use the previous example to delete selected record(s) from the table as well. I’ll leave that to you - just change the UPDATE query to a DELETE query in “update.aspx” and you should be on your way!
Endgame
And that’s about all I have for you today. Continuing where I left off in the first part of this tutorial on database interaction using ASP.NET, I started with a simple example that demonstrated the capabilities of the SqlDataAdapter and DataSet object. Here, I highlighted the differences between the DataReader and SqlDataAdapter objects, and explained why you will find the latter more useful than the former.
This was followed by another example that explained how you can use the same set of objects (SqlDataAdapter and DataSet) along with the DataTable and DataRow objects to add a new record to a database. Since there’s more than one way to skin a cat, the last example demonstrated yet another technique to update database records, using a combination of the SqlDataAdapter, DataSet and SqlCommand objects to fetch and alter the selected records.
If this introductory tutorial has whetted your appetite, you can read more about the ADO.NET classes I’ve used in the last two articles on MSDN, at the following links:
The SqlDataReader class, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlDataReaderClassTopic.asp
The SqlDataAdapter class, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlDataAdapterClassTopic.asp
The SqlDataCommand class, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlCommandClassTopic.asp
The DataSet class, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataSetClassTopic.asp
The DataTable class, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataTableClassTopic.asp
The DataRow class, at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataDataRowClassTopic.asp
Next week, I’ll be showing you how to add exception handling to your ASP.NET scripts, with examples of how to catch errors and exit gracefully when things go wrong. Until then, play with the various objects I introduced today - they should keep you busy for a while!
Note: Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article. YMMV!
This article was first published on 14 Nov 2003.