Working with datasets - ADO.NET Part 3

Naimishforu
Posted by in ADO.NET category on for Beginner level | Points: 250 | Views : 5415 red flag
Rating: 5 out of 5  
 2 vote(s)

Overview of working with datasets in ADO.NET

Introduction


Let's use the following simple program to demonstrate how C# uses datasets to examine the information in a database. 

In this program, a dataset is created and then populated from a Microsoft Access database called EasyNomad. The information in the dataset is then displayed in a list box.

Because we are using an OLE database, we must use the System.Data.OleDb namespace.

using System.Data.OleDb;

The following line creates the connection string that specifies the provider as Microsoft.JET.OLEDB.4.0 and the data source as EasyNomad.mdb.

string connection = 
  "provider=Microsoft.JET.OLEDB.4.0; "
  + "data source = c:\\EasyNomad.mdb";

We create the Select statement, which determines which records will be pulled from the database to the dataset.

string command = 
  "Select FirstName, LastName from Customers";

The following line of code creates a new OleDbDataAdapter called DataAdapter.

OleDbDataAdapter DataAdapter = 
  new OleDbDataAdapter(command, connection);

The connection and command strings must be passed in to the new data adapter.

(command, connection);

The data extracted by the DataAdapter object will be fed into a new DataSet object called CustomerDataSet, which is created in this line.

// connect to EasyNomad Access Database string connection = "Provider=Microsoft.JET.OLEDB.4.0; " + "data source = c:\\EasyNomad.mdb"; // get records from the customers table string command = "Select FirstName, LastName from Customers"; // create the data set command object // and the DataSet OleDbDataAdapter DataAdapter = new OleDbDataAdapter(command, connection); DataSet CustomerDataSet = new DataSet();


The Fill method of the DataAdapter class fills the CustomerDataSet object with the data from the Customers table in the EasyNomad database.

Let's use the list box to display the information that has been copied to the CustomerDataSet object. 

This code creates a new DataTable object, which contains the information in the first table of the dataset.

You use the Tables property of the DataSet object to get the collection of tables contained in the dataset. Because we added only one table, we know it is at index 0.

The data is now transferred to the list box, by iterating through each data row in the data tables Rows collection and adding a new item to the list box.

DataSet CustomerDataSet = new DataSet (); // fill the data set object DataAdapter.Fill(CustomerDataSet,"Customers"); // Get the one table from the DataSet DataTable dataTable = CustomerDataSet.Tables[0]; // for each row in the table, display the info foreach (DataRow dataRow in dataTable.Rows) { ListOfCustomers.Items.Add(dataRow["FirstName"] + " " + dataRow["LastName"]); }
You now know how to connect to an OLE DB managed provider. To connect to a SQL managed provider, you need to make a few small changes to the code. 

First you use the System.Data.SqlClient namespace.

Then you need to change the connection string to specify the SQL database.

And change the data adapter to a SqlDataAdapter object.

From then on, for every class member whose name begins with "OleDb," there is a corresponding one that begins with "Sql."
using System.Data.SqlClient string connection = "server =(local) \\ EasyNomad;" + "Trusted_Connection=yes; database=\\EasyNomad"; SqlDataAdapter DataAdapter = new SqlDataAdapter (command, connection);

Thanks and Have Fun!!!!!

Page copy protected against web site content infringement by Copyscape

About the Author

Naimishforu
Full Name: Naimish Pandya
Member Level: Bronze
Member Status: Member,MVP
Member Since: 1/22/2011 7:38:35 AM
Country: India
Thanks, Naimish http://dotnetinterview.zxq.net/ http://naimishpandya.blogspot.in/
http://naimishpandya.blogspot.in/

Login to vote for this post.

Comments or Responses

Posted by: Madhu.b.rokkam on: 2/25/2011 | Points: 25
Nice article..
Posted by: Tripati_tutu on: 2/25/2011 | Points: 25
Good article...
Posted by: Naimishforu on: 2/25/2011 | Points: 25
Thanks Madhu and Tripati :)
Posted by: Devendra0257 on: 5/20/2011 | Points: 25
This is one of Best & easy step article..

Login to post response

Comment using Facebook(Author doesn't get notification)