#1
#2
#3
Questions Answered
Overall Points

Fundamentals - Reading from a database - Part 1

One of the most common tasks in any data driven application, and one of the ones that seems to cause most confusion for beginners, is reading data from a database. The confusion is easy to understand given the range of available data sources and the number of available methods of retrieving data.

The aim of this article is to demonstrate a very basic example of how to connect to a database, retrieve some data from a table, and then display the data on screen. The sample will take the form of a Console application and will talk to Microsofts AdventureWorks database running on a local SQL Server (if you want to try this example your self and you don't already have the AdventureWorks database, you can download it here . The reason I am going to use a Console application is because it allows us to quickly create a working application without having to worry about the details of how to work with WinForms or ASP.Net pages and lets us focus purely on the code we’re interested in.

Before we jump to the code, one of the things we need to know about are "connection strings". A connection string tells our code about how to connect to our database. This can include what driver to use, where the database is located and login information. Other options are available and are generally specific to the type of data source you are connecting to.

For our example program we are going to define our connection string as follows:

string connString = "Server=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;";

This connection string says that we will connect to a SQL Server located on the same machine as our program, that we want to connect to the database named "AdventureWorks" and that we want to authenticate to the database using the credentials of the user currently logged on to the computer. Note: We will be using the Sql Provider for .Net which uses the SQL Native Client by default, so we do not need to explicitly state what driver to use.

Now that we have our connection string, we can start writing some code. In order to talk to our SQL database we are going to use the SQL provider for .Net, so we need to add the following line at the top of our code file:

using System.Data.SqlClient;

In order to be able to talk to our database we first need an SqlConnection object. This objects acts as the link between our program and the database itself. Every time we want to interact with the database we need a connection object. In our example we create a connection as follows:

string connString = "Server=(local);Initial Catalog=AdventureWorks;Integrated Security=SSPI;";
SqlConnection conn = new SqlConnection(connString);

It is worth noting that at this point we haven't connected to the database, we have simply defined an object that will connect to our database when we are ready to. The next step is to create an object that defines what statement we want to execute against our database. In our example we want to retrieve the FirstName and LastName columns from the Person.Contact table of the AdventureWorks database. We do this by creating a new SqlCommand object and passing it both the query we want to execute and the SqlConnection object it should use:

SqlCommand cmd = new SqlCommand("SELECT [FirstName], [LastName] FROM [Person.Contact]", conn);

Now that we have prepared our connection, and the command we want to execute, we are ready to send the query to our database and process the data we get back. In order to execute our query we first need to open the connection to the database. The connection will remain open while we are reading from the database, and we must ensure that we always close the connection again when we are done.

In our example we are going to execute our query by calling the ExecuteReader() method of our SqlCommand object. This will return a SqlDataReader object which will allow us to read data from the database one row at a time.

conn.Open();
SqlDataReader reader = cmd.ExecuteReader();

We move through rows of data by calling the Read() method of the SqlDataReader object. The Read() method returns a boolean value indicating if a record was read successfully.

The SqlDataReader object exposes methods for reading different types of data from the database. Since the FirstName and LastName columns contain text data we will be using the GetString() method. The GetString() method requires that we pass it the index number (starting from zero) of the column we are interested in. Putting all of that together we can output the name of each person in the Contact table using a simple loop:

while (reader.Read())
{
	Console.WriteLine(
		string.Format("{0} {1}",
			reader.GetString(0),
			reader.GetString(1)
		);
	);
}

// we have finished reading from the database, so close the connection
conn.Close();

And that's it! We have successfully read and displayed data from a database table. In Part 2 of this article I will expand slightly on the example presented here by adding in exception handling, automatic closing of the connection, and showing how we can access fields by name rather than index number.

Comment on this article
(required)
(required)
(optional)

What other people have said
Dave Rowland Mon 28 Feb 2011 @ 21:14
Excellent article, great for beginners, please post more articles like this. Keep up the good work!