How to retrieve data from the database in ASP.NET?

Introduction

ADO.NET is a mechanism to communicate with the database. It allows us to work in connected (database connection is alive while the operation is performed) as well as disconnected architecture (database connection is closed and operation can be performed on the data). 

In this article, let us learn how to retrieve data from the database.

Get hundreds of ASP.NET Tips and Tricks and ASP.NET Online training here.

In some scenario, we need to connect to the database and retrieve records, that can be done by following this approach.

Below is my web.config file (Web.config) database connection string setting.

Web.config

<connectionStrings>
              <add name="ConnStr" connectionString="Data Source=xxxx-
PC\SQLEXPRESS;Initial Catalog=DemoDatabase;Persist Security Info=True;User
ID=demo;Password=demo;"/>
       </connectionStrings>
 

The _connStr will have the database connection string value saved into the web.config file underconnectionStrings settings.

CODE BEHIND

using System.Configuration; // to use the ConfigurationManager
using System.Data; // to use DataSet or DataTable
using System.Data.SqlClient; // to communicate with SQL Server database
using System.Text; // for StringBuilder
 
string _connStr =
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
 
    protected void Page_Load(object sender, EventArgs e)
    {

    DataTable table = new DataTable();
    var autoId = 1;
 
    // get the connection
    using (SqlConnection conn = new SqlConnection(_connStr))
    {
         // write the sql statement to execute
         string sql = "SELECT AutoId, FirstName, LastName, Age, Active FROM PersonalDetail WHERE AutoID > @autoId";
            // instantiate the command object to fire
           using (SqlCommand cmd = new SqlCommand(sql, conn))
           {
              // attach the parameter to pass, if no parameter is in the sql no need to attach
              SqlParameter prm = new SqlParameter("@autoId", autoId);
              cmd.Parameters.Add(prm);
              // get the adapter object and attach the command object to it
              using (SqlDataAdapter ad = new SqlDataAdapter(cmd))
              {
                  // fire Fill method to fetch the data and fill into DataTable
                  ad.Fill(table);
             }
            // DataAdapter doesn't need open connection, it takes care of opening and closing the database connection
      }
}
 
// loop through the rows of the table
foreach (DataRow row in table.Rows)
{
     Response.Write("<p>" + row["AutoId"] + " > " + row["FirstName"] + " >
" + row["LastName"] + " > " + row["Age"] + " > " + row["Active"] + "</p>");
        }
}

In the above code snippet, the Page_Load event of the code behind contains an instance of the DataTable object that holds the fetched records from the database. Then we have Instantiated the SqlConnection object by passing the connection string, instantiated the SqlCommand object by passing the sql string and connection object. 

Now we need to see if sql statement needs any parameter; if yes, attache the SqlParameter objects (in this case I have autoid as parameter). Then I have instantiate the SqlDataAdapter object and passed the object of SqlCommand and called the Fill method of the SqlDataAdapter object by passing the DataTable object as parameter. This will fill the fetched records from the database (returned using the SQL statement) to the DataTable object. 

In the above code snippet, I am using using {}  block that ensures that once we are done with the object, that object gets disposed properly.  So there is no need to use try and finally and closing and disposing the connection, command and adapter object in the finally block explicitly.

Now, I am looping through the rows and writing on the page (The DataTable object could have been directly bounded to ASP.NET Data controls like GridView, DetailsView etc.).

OUTPUT

NOTE: The same approach can be used to connect with other database as well (Oracle, MySql, MS Access etc.). Just the provider name changes (For example for the database that supports Oledb connection, OledbConnection, OledbCommand, OledbParameter, OledbDataAdapter classes can be used).

Hope this article will be useful for those who are new to ADO.NET. In real time scenario, we use stored procedure in place of writing SQL statement in the code. I will explain how to use stored procedure in coming up articles.

Thanks for reading, stay tuned for forthcoming articles on ADO.NET. You can help your friends by referring to the articles and tutorials of this website.