This is the first article in a series that I have written for anyone who wants to learn how to use ADO.NET technology, with an ASP.NET 2.0 page, to retrieve and update data. I assume that you have knowledge of C#, ASP.NET and Visual Studio.NET 2005.
Contributed by Michael Youssef Rating: / 8 September 19, 2007
In this article, we introduce the basics of ADO.NET. In subsequent articles we will talk more about how you can do advanced database operations from web pages. I will also show you code examples as I always present them in my articles.
ADO.NET is a set of classes that are organized in a number of namespaces. These namespaces provide developers access to databases to retrieve and update data. The classes of ADO.NET provide us with the means to access databases and execute T-SQL code that retrieves, inserts, updates and deletes data. It's critical to understand that ADO.NET depends on the concepts of data providers. ADO.NET doesn't have a general connection class that would access any database. ADO.NET has separated its functionality through the ADO.NET Data Providers.
A Data Provider is a set of classes that provide access to a specific data source for retrieving, updating and deleting operations between your application and the data source. Those classes are optimized to access and deal with that specific data source. This is the point of creating Data Providers and not using general classes to access any data source.
An ADO.NET Data Provider has the following classes:
Connection class: The connection class is used to connect to the data source. Note that different ADO.NET Data Providers have different connection classes. For example, the SQL Server Data Provider has the SqlConnection class, the Oracle Data Provider has the OracleConnection class and the OleDb Data Provider has the OleDbConnection class. So we don't have a class called connection because ADO.NET doesn't give us the ability to use general classes to access different data sources.
Command class: The command class is used to execute SQL statements on the data source. As you might have guessed there is nothing called a command class in ADO.NET and different ADO.NET Data Providers have different command classes. The SQL Server Data Provider has a SqlCommand class and the same applies to the other Data Providers; each one has its own command class.
Data reader class: This class provides access to a database table in a read-only forward-only behavior. The SQL Server Data Provider has the SqlDataReader class; other Data Providers also have their own implementations of the data reader. For example, the OleDbDataReader is the class for the OleDb Data Provider and so on.
Data adapter class: The data adapter class submits changes made to the data in your application. This data is in the form of a DataSet object to the data source. The SQL Server Data Provider has the SqlDataAdapter class; other Data Providers also have their own implementations of the data adapter. For example, the OleDbDataAdapter is the class for the OleDb Data Provider and so on.
ADO.NET is shipped with four Data Providers, but you can create your own provider if you need to access another data source that has no Data Provider. The four Data Providers are as follows:
The SQL Server Data Provider is used, and optimized, to access SQL Server 2005, 2000 and version 7.0 databases. Its classes lives in the System.Data.SqlClient namespace.
The Oracle Data Provider is used to access Oracle databases and its classes lives in the System.Data.OracleClient namespace.
The OLE DB Data Provider is used to access any data source that has an OLE DB Driver. Its classes lives in the System.Data.OleDb namespace.
The ODBC Data Provider is used to access any data source that has an ODBC Driver. Its classes lives in the System.Data.Odbs namespace.
Throughout the series I will be using the SQL Server Data Provider because I'm using SQL Server as the database server for all of my code examples. In one of the next articles I will show you how you can write generic data access code that you can use to access any data source as long as it has an ADO.NET Data Provider. So enough theory and let's get to the code examples.
For this example we will populate a ListBox control with data from the Employees table of the Northwind database. We will select only the FirstName and the LastName columns from the Employees table, and add the rows as list items to the ListBox control. Let's do it. Start by creating a new website: File --> New --> Website, and place the website folder on the file system. Drag a ListBox control on the page and set its width to 157 and its height to 143 (pixels). That's all that you need in the Default.aspx file.
The following is the code of the Default.aspx.cs file (you need to replace the auto-generated code template with the following code or write it by hand step by step):
using System; using System.Data; using System.Configuration; 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;
// use the namespace of the ADO.NET SQL Server Data Provider using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // create the connection and set the connection string // through the connection object's ConnectionString property SqlConnection connection = new SqlConnection(); string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"; connection.ConnectionString = connectionString;
// create the command object and assign the connection object to it // through the Connection property SqlCommand command = new SqlCommand(); command.Connection = connection; // creating the T-SQL SELECT statement and assign it to the command // through the CommandText property string commandText = "SELECT LastName, FirstName FROM Employees"; command.CommandText = commandText; // assigning the value CommandType.Text to the CommandType property // because we are using a T-SQL statement not a stored procedure command.CommandType = CommandType.Text;
// we have initialized the connection and the command so it's time // to open the connection and execute the command connection.Open(); // get a SqlDataReader object from the Command's ExecuteReader() method SqlDataReader dataReader = command.ExecuteReader();
// adding the returned rows to the listbox control while(dataReader.Read()) { ListBox1.Items.Add(dataReader[0] + ", " + dataReader[1]); } dataReader.Close(); connection.Close(); } }
Now run the page and you will get the first names and last names of the employees added to the list box control as shown in the following screenshot.
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.
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.
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.
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
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.
While connecting to data sources it's best to wrap the code with a try/catch/finally block to make sure that the connection object is closed and the error is handled. I have added a Label control to the form which is assigned the Exception.Message in case an Exception has been thrown.
using System; using System.Data; using System.Configuration; 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;
// use the namespace of the ADO.NET SQL Server Data Provider using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { // create the connection and set the connection string // through the connection object's ConnectionString property SqlConnection connection = new SqlConnection(); string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True"; connection.ConnectionString = connectionString;
// create the command object and assign the connection object to it // through the Connection property SqlCommand command = new SqlCommand(); command.Connection = connection; // creating the T-SQL SELECT statement and assign it to the command // through the CommandText property string commandText = "SELECT LastName, FirstName FROM Employees"; command.CommandText = commandText; // assigning the value CommandType.Text to the CommandType property // because we are using a T-SQL statement not a stored procedure command.CommandType = CommandType.Text;
// we have initialized the connection and the command so it's time // to open the connection and execute the command // using a try/catch block to handle exceptions SqlDataReader dataReader; try { connection.Open(); // get a SqlDataReader object from the Command's ExecuteReader() method dataReader = command.ExecuteReader();
// adding the returned rows to the listbox control while (dataReader.Read()) { ListBox1.Items.Add(dataReader["LastName"] + ", " + dataReader ["FirstName"]); } } catch (Exception ex) { Label1.Text = ex.Message; } finally { connection.Close(); } } }
Remove the last key/value pair from the connection string and run the page. You will now get the exception's message in the label control.
The finally block is called whether or not an exception has been thrown. There is a better technique that we will look at in the next few articles which implies using the statement to implicitly close objects that implement the IDisposable interface. In other words the objects that have the Dispose() method defined.
ASP.NET Export to Excel and Word using VB.NET and C#
