Tutorials » AspNet tutorials »

Retrieving data from database


This article demonstrates the usage of OleDbDataAdapter Object and DataSet to retrieve data from databbase.


In the previous chater, you learned how to use OleDbConnection and OldDbCommand classes to execute an sql statement that returns no data.

In this chapter, you will learn how to use the ADO.NET classes to execute sql statements and retrieve data from database.

See the following sample code:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";

OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill ( employeeData );


Here we are creating a OleDbConnection object and it to the OleDbDataAdapter object. Also, we pass the select ... query to the OleDbDataAdapter. Next, we call the .Fill() method of the OleDbDataAdapter and pass a dataset object to this method. This step will populate the dataset (called employeeData ) with the data retrieved for the sql statement select * from EMPLOYEE.

As you already know, a DataSet can contain a collection of tables. But in our case, our sql statement will retrieve data from only one table. So, our DataSet will have only one table.

In our sample code, the dataset contains one table and the table contains one or more records. We can iterate through the table in the dataset and retrieve all the records. See the following code demonstrating this:

string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Samples\\Employee.mdb";
OleDbConnection myConnection = new OleDbConnection( connectionString );

string query = "select * from EMPLOYEE_TABLE";

OleDbDataAdapter myAdapter = new OleDbDataAdapter( query, myConnection );
DataSet employeeData = new DataSet();
myAdapter.Fill( employeeData );

// Repeat for each table in the DataSet collection.
foreach ( DataTable table in employeeData.Tables )
{
// Repeat for each row in the table.
foreach ( DataRow row in table.Rows )
{
Response.Write( "Employee Number : " + row["EmployeeNumber"].ToString() );
Response.Write( "Name : " + row["Name"].ToString() );
Response.Write( "Address : " + row["Address"].ToString() );
}
}


The above code retrieves all the records from the employee table and displays the value of the following fields:

  • EmployeeNumber
  • Name
  • Address





  • Previous Chapter: Database operations - Learn more
    More Chapters: ASP.NET Tutorials
    More Tutorials: Tutorial Index



    Top Contributors
    Today
      Last 7 Daysmore...

      Awards & Gifts

      Online Members

      Manigandan
      More...
       
      Copyright © SpiderWorks Technologies Pvt Ltd., Kochi, India