Search
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Articles Author
Fri, 24-Oct-2014 Authors
All Time Authors
Sourav.Kayal
39750
SheoNarayan
38050
Niladri.biswas
33350

Latest members | More ...


(Statistics delayed by 5 minutes)
Ads
 Article

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

Sheonarayan
Posted by under ADO.NET category on for Intermediate level | Points: 250 | Views : 47132 red flag
If you found plagiarised (copied) or inappropriate content,
please let us know the original source along with your correct email id (to communicate) for further action.
In this article, we shall learn how to retrieve data from the database.

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.

Page copy protected against web site content infringement by Copyscape
About the Author

Sheonarayan

Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Microsoft_MVP,Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com

Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan
Login to vote for this post.
Found interesting? Add this to:


Comments or Responses

Posted by: Ashok1992 on: 2/23/2013 | Points: 25

Great Article .. Really helps for freshers like me . Currently doing my own project and this helps me a lot .. Thanks you .. Carry on your Good work

Posted by: Ashok1992 on: 2/23/2013 | Points: 25

I have one more Query

string _connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

when is configured this on my system it shows "A namespace cannot directly contain members such as fields or methods "

after writing this on page_load() . it cleared .. May i know the reason for this ?

Login to post response

Comment using Facebook(Author doesn't get notification)