Bala's Blog

Now you have added the connection string in the web.config file. Let's see some code that we can use to access the database. We want to load some data from the database when the page is loaded for the first time.

private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
string connectionString = (string) ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM Categories",myConnection);
DataSet ds = new DataSet();
ad.Fill(ds,"Categories");
myDataGrid.DataSource = ds;
myDataGrid.DataBind();
}
}

First, we check that if it's not a postback we load data. For this example to work you need to add the namespace:
using System.Configuration;

Let's see what is going on here:
1) string connectionString receives the string from the web.config file which is referred by the "ConnectionString" key we set in the web.config file.
2) We make the object of the SqlConnection class which deals with SQL Server databases.
3) We made the data adapter object. Data adapter is a kind of connection to the database. In the data adapter object we specify that what we want from the database. In this case we want all the rows of the Categories table.

NOTE:
Always use stored procedures for accessing the data. Accessing or running Ad-hoc queries are dangerous as they are open for SQL Injections.
4) Next we made an instance of the DataSet class. DataSet will contain the result of the SqlDataAdapter even if the database connection is not made.
5) Later, we filled the dataset with the data using dataadapter.
6) And finally we assigns the datagrid to the dataset and binds it on the screen.
Pretty simple right?

Lets see if we can improve the code above:

private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
string connectionString = (string) ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection myConnection = new SqlConnection(connectionString);
SqlDataReader reader = null;
SqlCommand myCommand = new SqlCommand("GetData",myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
try
{
myConnection.Open();
reader = myCommand.ExecuteReader();
myDataGrid.DataSource = reader;
myDataGrid.DataBind();
}
catch(Exception ex)
{
// Catches and logs the exception
}
finally
{
reader.Close();
myConnection.Close();
}
} 

In the code above we are using the SqlCommand object and stored procedure. You might have noted SqlDataReader, if you want to merely iterate through the database rows and select them its best to use SqlDataReader since its much faster than DataSet.

Finally we catch exceptions if any of them are generated and closes the connection.