Introducing ADO.NET with ASP.NET 2.0 - Explaining the code
(Page 3 of 4 )
Before we discuss the code that we have written in the Page_Load() method, note that we have added a namespace reference statement to use the SQL Server Data Provider. The first line of code in the Page_Load() method creates a connection object of type SqlConnection; because we are connecting to SQL Server we have to use its specific Data Provider's classes.
At this point the connection object has not established a connection to the database. We need to tell it where the database server is, what database to use and how to access the server. This is possible through the use of connection strings. A connection string is a string value that contains a group of key/value pairs separated by a semicolon which gives the connection object information about how to reach the database. You create the connection string by supplying values for the keys as we did in the example.
string connectionString = "Data Source=(local);Initial
Catalog=Northwind;Integrated Security=True";
The first key is Data Source, the server to which you are connecting. In our case (local) refers to the local server on the machine. Initial Catalog is the database you need to access, and it is the second key you will find in the connection strings, although the order of the keys is not important. The third key, Integrated Security, tells the connection object to access the database through the use of the current windows login. We could have used SQL Authentication instead through the user id and password keys. The capitalization of the keys is also not important.
Next we assign the string variable connectionString to the SqlConnection.ConnectionString property. This must happen before opening the connection; this makes sense because we use the information provided by the ConnectionString property to open a connection to that database.
connection.ConnectionString = connectionString;
After that we need to create and initialize a command object and assign the connection object to it.
SqlCommand command = new SqlCommand();
command.Connection = connection;
string commandText = "SELECT LastName, FirstName FROM
Employees";
command.CommandText = commandText;
command.CommandType = CommandType.Text;
The first statement in the above code creates a SqlCommand object, which represents a T-SQL statement that will be executed on the server. The SqlCommand.Connection is set to the SqlConnection object that will be used to execute the T-SQL statement. The T-SQL statement itself is set through the SqlCommand.CommandText property; also you need to tell the SqlCommand object if you are executing a T-SQL text or a stored procedure. You do that through assigning the SqlCommand.CommandType property a value from the CommandType enumeration. In our case we are assigning the CommandType.Text value, which is the default value for the SqlCommand.CommandType property; it can be omitted from the above code. If you are executing a stored procedure, you assign the CommandType.StoredProcedure value to the SqlCommand.CommandType property.
Now everything is ready and we can open the connection. Execute the T-SQL code and retrieve the data.
connection.Open();
SqlDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
ListBox1.Items.Add(dataReader[0] + ", " + dataReader[1]);
}
You make the connection to the database by calling the SqlConnection.Open() method. Note that if you didn't assign the connection string to the connection object before calling the Open() method you will get an exception. The second line in the above code uses the SqlCommand.ExecuteReader() method to create a SqlDataReader object. You can't use the new operator to create an instance of the SqlDataReader class because it's defined with a private constructor.
As we said, the SqlDataReader object accesses the data in a read-only forward-only manner. This means that after it reads a row you go to the next available row and you can't get back to the previous row. That's why it's a fast way to retrieve data.
You need to store this data somewhere. In our example we use a ListBox control to store the rows retrieved by the SqlDataReader Object. The SqlDataReader.Read() method is used to move the reader to the next available row; it returns true if there is a row to read and false otherwise. The SqlDataReader.Read() method is placed before the first row and not on the first row because it wouldn't read that first row. We have used the while statement to make sure that we read all the available rows. Inside each iteration the fields of the row are available through the SqlDataReader indexer's syntax. You can access the fields through their column names too, so we can write the code as
while (dataReader.Read())
{
ListBox1.Items.Add(dataReader["LastName"] + ", " + dataReader
["FirstName"]);
}
We have used the Add() method of the Items property to add a new list item object to the ListBox1 control. The Add() method accepts a string and adds it as a list item to its Items collection. Finally we need to close both SqlDataReader and the SqlConnection objects by calling their Close() methods.
dataReader.Close();
connection.Close();
For now just understand that closing both the SqlDataReader and the SqlConnection object is very important; I will cover more on this in another article. In our example, if something went wrong and an exception has been raised the connection object might not be closed. So we need a mechanism to make sure that the connection object's Close() method is called even if an error has occurred.
Next: Using Exception Handling code >>
More ASP.NET Articles
More By Michael Youssef
