Click here to download the source code for this lesson.

In this lesson I will do an introduction to SQL Server database programming in ASP.NET by explaining how to use the GridView control.  The GridView control can be found in the Toolbox window in the Data group of controls.  If you would like to learn how I setup a SQL Server database with test data for this lesson please click here.  The database that I am using in this example is called “csharpuniversity” and I created a very simple table called “customer” with a few columns.

Let me start by saying that database programming in ASP.NET and .NET in general is very straightforward.  It may seem complicated at first because there are so many .NET Framework database classes that are available.  Once you get the hang of it though, it becomes easier, so don’t be discouraged or overwhelmed when you begin to practice with .NET database programming.  If something doesn’t work the first time, go back and re-read the examples and watch the videos again, just in case you missed a step along the way.

The .NET design for interacting with SQL Server databases is organized into two parts: a generic namespace called System.Data and a SQL Server specific namespace called System.Data.SqlClient.  A namespace is a collection of related .NET classes organized into a single library (.dll file).  Microsoft created a special class for database programming called the DataSet.  The DataSet class is in the System.Data namespace and is probably the most commonly used database related .NET class. 

A DataSet is a set of rows that can hold data in memory.  A DataSet has columns, just like a database table.  The DataSet was purposely designed to be disconnected from the database.  By disconnected I mean that the DataSet does not hold a database connection open in order to be used.  This is very beneficial since it can be passed around in your application without worrying about locking up any resources.  The DataSet is also disconnected in another sense, in that it does not necessarily have to match the exact structure of a database and can even be populated with data by using code (without the need for a database at all).  For example, you could read data from an alternate data source like an XML file and hold the data in a DataSet object.

In order to populate a DataSet object with data from a SQL Server database, you have to use the System.Data.SqlClient namespace.  That namespace contains connection and query oriented classes.  There is a class called SqlConnection that allows you to open a database connection to SQL Server.  This is necessary of course as a prerequisite in order to execute any commands such as queries.  In order to actually perform queries you have to also use another class called SqlDataAdapter.  The SqlDataAdapter class is responsible for executing the queries.  Let’s take a look at some sample source code and break it down line by line.  I created a new web form and dragged a Button control onto the form.  I then dragged a GridView control from the Data group of Toolbox controls onto the form.  I double clicked the Button to create the event handler.  At the top of the source code file I added the following lines.

using System.Data;
using System.Data.SqlClient;

In the click event handler for the Button control I put the following.  For the actual connect string used please download the source code zip file at the top of this post.

SqlDataAdapter dbadapter = null;
DataSet dbdata = new DataSet();

using (SqlConnection dbconn = new SqlConnection("CONNECT STRING GOES HERE"))

{
    dbadapter = new SqlDataAdapter("select * from Customer", dbconn);

    dbadapter.Fill(dbdata);
}

gvCustomers.DataSource = dbdata;
gvCustomers.DataBind();

You can see at the top of the handler that I declared two variables dbadapter and dbdata.  The dbdata variable will hold the results of the query in memory.  The dbadapter object will be responsible for executing the query.  Following those two declarations I created a special block of code called a “using” block with open and closed curly braces.  The using statement is used in situations where you want .NET to automatically close a resource when you are finished using it.  In this example the resource that will automatically close is the database connection because I instantiated an SqlConnection object called dbconn inside the parentheses following the using keyword.  It is important to close database connections in your code because if you don’t, your application will eventually run out of connection objects since they are never being explicitly released.  The using statement block will make sure that the dbconn object is available until the closed curly brace.  After the closed curly brace, the dbconn object isn’t available for use anymore.

Inside the using statement, I first instantiate the SqlDataAdapter object and call the constructor method.  The constructor takes two parameters: a string containing the SQL query you want to execute and an SQLConnection object (dbconn) that tells .NET which database to execute against.  Notice in my query that I retrieve all the records from the Customer table”.  On the next line I call a special method of the SqlDataAdapter object called Fill.  Fill allows you to take the results of the query and store them in a DataSet object which is dbdata in my example.  In about 5 lines of C# code, I was able to open a connection to a SQL Server database and query a table.

Once I have the DataSet object populated with data from the database, there are many things that I can do with it.  In this example, I am displaying the data in an ASP.NET GridView (similar to an HTML table tag) control.  In order to achieve this, I have to perform data binding.  In .NET data bound controls are basically server controls like a GridView that have a DataSet object assigned (bound) to them.  Once a DataSet is bound to a server control, the server control (e.g. GridView) can display the data that is in in the DataSet on the web page.  For a GridView control, this is very easy and only takes two lines of code.  First you have to assign the GridView’s DataSource property to a DataSet object that contains data.  Then you call the GridView’s DataBind method to let ASP.NET know that you want to display the data.  In total, this entire example is about 7 lines of C# code.  That is pretty powerful and you can see how quickly an application can be developed that contains database integration.  Click here to see an example video where I query data in a SQL Server database and display the results in an ASP.NET GridView control.