ASP.NET Basics (Part 8): Data Overload - Going All the Way
(Page 5 of 6 )
All working? Good. Now, let's use our new SqlConnection object to do something interesting: fire a SELECT query at the database, and display the results in an HTML page.
After the mandatory import of the required ADO.NET assemblies, a connection object is created as explained earlier.
<%
// create an instance of the Command object
SqlCommand objCommand = new SqlCommand("SELECT * FROM starwars;", objConn);
%>
Here, I have created an instance of the SqlCommand object. True to its name, this object allows you to execute commands against the database and possibly return records (in case of a SELECT query). Using this object, you can then navigate through the records in your ASP.NET code.
The constructor (a method having the same name as that of the class and executed each time an instance is created) of this object requires two parameters: the SQL statement to be executed and the connection object to be used.
<%
// open the connection
objConn.Open();
// populate a SqlDataReader object
SqlDataReader objReader = objCommand.ExecuteReader();
%>
After opening the connection to the database, an instance of the SqlDataReader object is created. As the name suggests, this object is used to store a read-only set of records and, therefore, can only be used for display purposes. The ExecuteReader() method of the SqlCommand object is then used to populate the SqlDataReader object with the database's response to the command - in this case, a recordset. Note that when using the SqlDataReader object, records can be read in the forward direction only; you cannot access a record once you have moved past it.
Note also that in this approach, the connection is open throughout the execution of the code and processing of the recordset. This might not seem important at the moment, but remember it because it'll become a big deal soon.
<%
// display the names of the columns of the "starwars" table
for(int count = 0; count < objReader.FieldCount; count++)
{
Response.Write("<td><b>" + objReader.GetName(count).ToUpper() +
"</b></td>");
}
%>
The GetName() method of the SqlDataReader object can be used, with a "for" loop, to obtain a list of field names from the recordset, with the FieldCount property exposing the total number of fields.
<%
// read each record from the resultset 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("</tr>");
}
%>
Next, the Read() method of the SqlDataReader object can be used to iterate over the resultset and process each record in a "while" loop. All you need to do is pass the field index to the GetValue() method to retrieve the corresponding field value. The "while" loop will terminate automatically as soon as the SqlDataReader object runs out of records.
Finally, once the entire set of records has been displayed, it's a good idea to free up used memory resources.
<%
// clear up memory by closing all objects
objReader.Close();
objConn.Close();
%>
Since the SqlDataReader object is read-only and can only be used in one direction, it's fast and ensures minimal load on the server - always a good thing in the Web world!
Next: Grid Lock >>
More ASP.NET Articles
More By Team Melonfire, (c) Melonfire
