DataSet and DataAdapter in ASP.NET 2.0 - Part 1 of 2

What are DataSets and DataAdapters

Datasets store a copy of data from the database tables. However, Datasets can not directly retrieve data from Databases. DataAdapters are used to link Databases with DataSets. If we see diagrammatically,

DataSets < ----- DataAdapters < ----- DataProviders < ----- Databases

DataSets and DataAdapters are used to display and manipulate data from databases.

Reading Data into a Dataset

To read data into Dataset, you need to:

• Create a database connection and then a dataset object.
• Create a DataAdapter object and refer it to the DB connection already created. Note that every DataAdapter has to refer to a connection object. For example, SqlDataAdapter refers to SqlDataConnection.
• The Fill method of DataAdapter has to be called to populate the Dataset object.

We elaborate the above mentioned steps by giving examples of how each step can be performed:

1)      As we said, our first task is to create a connection to database. We would explore later that there is no need of opening and closing database connection explicitly while you deal with DataAdapter objects. All you have to do is, create a connection to database using the code like this:

SqlConnection con = new SqlConnection ("data source=localhost; uid= sa; pwd= abc; database=Northwind");

We would use Northwind database by using OleDbConnection. The Code would

Look like:

OleDbConnection con= new OleDbConnection ("Provider =Microsoft.JET.OLEDB.4.0;" + "Data Source=C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb");

2)      Now, create a Dataset object which would be used for storing and manipulating data. You would be writing something like

DataSet myDataSet = new DataSet ("Northwind");

Since the name of source database is Northwind, we have passed the same name in the constructor.

3)      The DataSet has been created but as we said before, this DataSet object can not directly interact with Database. We need to create a DataAdapter object which would refer to the connection already created. The following line would declare a DataAdapter object:

OleDbAdapter myDataAdapter = new OleDbAdapter (CommandObject, con);

The above line demonstrates one of many constructors of OleDbAdapter class. This constructor takes a command object and a database connection object. The purpose of command object is to retrieve suitable data needed for populating DataSet. As we know SQL commands directly interacting with database tables, a similar command can be assigned to CommandObject.

OleDbCommand CommandObject = new OleDbCommand ("Select * from employee");

Whatever data you need for your Dataset should be retrieved by using suitable command here. The second argument of OleDbAdapter constructor is connection object con.

Alternative approach for initializing DataAdapter object:

Place a null instead of CommandObject while you initialize the OleDbAdapter object:

OleDbAdapter myDataAdapter = new OleDbAdapter (null, con);

Then you assign your query to the CommandObject and write:

myDataAdapter.SelectCommand = CommandObject;

4)      Now, the bridge between the DataSet and Database has been created. You can populate dataset by using the Fill command:

myDataAdapter.Fill (myDataSet, "EmployeeData");

The first argument to Fill function is the DataSet name which we want to populate. The second argument is the name of DataTable. The results of SQL queries go into DataTable. In this example, we have created a DataTable named EmployeeData and the values in this table would be the results of SQL query: "Select * from employee". In this way, we can use a dataset for storing data from many database tables.

5)      DataTables within a Dataset can be accessed using Tables. To access EmployeeData, we need to write:

myDataSet.Tables["EmployeeData"].

To access rows in each Data Table, you need to write:

myDataSet.Tables["EmployeeData].Rows

Listing 1.1 would combine all the steps we have elaborated so far.

1.      <%@ Page Language= "C#" %>

2.      <%@ Import Namespace= "System.Data" %>

3.      <%@ Import Namespace= "System.Data.OleDb" %>

4.      <html>

5.      <body>

6.       

7.      <table border=2>

8.      <tr>

9.      <td><b> Employee ID </b></td>

10.  <td><b> Employee Name </b></td>

11.  </tr>

12.   

13.  <% OleDbConnection con= new OleDbConnection ("Provider

14.  =Microsoft.JET.OLEDB.4.0;" + "Data Source=C:\\Program Files\\Microsoft

15.  Office\\Office\\Samples\\Northwind.mdb");

16.   

17.  <%

18.  DataSet myDataSet = new DataSet();

19.  OleDbCommand CommandObject = new OleDbCommand ("Select * from

20.  employee");

21. 

22.  OleDbAdapter myDataAdapter = new OleDbAdapter (CommandObject, con);

23. 

24.  myDataAdapter.Fill (myDataSet, "EmployeeData");

25. 

26.  foreach (DataRow dr in myDataSet.Tables["EmployeeData"].Rows)

27.  {

28.  Response.write ("<tr>");

29.  for (int j = 0 ; j <2 ; j++)

30.  {

31.  Response.write ( "<td>" + dr[j].ToString() + "</td"> );

32.  }

33.  Response.write ("</tr>");

34. 

35.  %>

36.  </table>

37.  </body>

38.  </html>

The Code above would iterate in all rows of Employee table and display ID and name of every employee. To Display all columns of Employee Table, Line # 29 would be replaced by:

for (int j = 0 ; j < dr.Table.Columns.Count ; j++)

As we said earlier, there is no need of opening and closing database connection explicitly. DataAdapter class handles both these functions.

Changing Data with a DataSet

Until now, we only focused on reading data by using DataSet and DataAdapter class. As you know, there are frequent modifications in database tables in real life. These may include insertions, updates and deletions. How these functions can be handled using DataSets and DataAdapters?

Deletions in Employee Table

To delete the Employee having id 1001:

1.      int i =0;

2.      foreach (DataRow dr in ds1.Tables["EmployeeData"].Rows)

3.      {

4.      i++;

5.      if (dr["id"] = = 1001 )

6.      break;

7.      }

8. EmployeeData.Rows[i].Delete;

Or to Delete 74^th row, use:

EmployeeData.Rows[74].Delete;

Updating Employee Table

To change the name of Employee having id 1001:

8.      foreach (DataRow dr in ds1.Tables["EmployeeData"].Rows)

9.      if (dr["id"] = = 1001 )

10.  dr["name"] = "new name";

Or to change name of employee in 74^th row of Table,

EmployeeTable.Rows[74]["name"] = "new name";

Insertions in Employee Table

1.      DataRow dr = EmployeeData.NewRow();

2.      dr["id"] = "1003";

3.      dr["name"] = "Ahmed Albaradi";

(or you can move it like dr[0], dr[1],a., dr[n-1])

4.      EmployeeData.Rows.Add(dr);

In this example, we created a new row, set its attributes and then added it to the Data table.

Writing Changes back to database table

We have discussed that DataSets can not directly interact with Database tables. Moreover, all the modifications we performed above apply only to EmployeeData, which is Data Table. Before we discuss how to write back changes, letAs explore DataAdapter class in a bit more detail:

We have discussed SelectCommand property which lets the Adapter selects its query. There are three other properties including UpdateCommand, AddCommand and DeleteCommand. All these commands would make changes in the database. However, by using CommandBuilderObject, you donAt have to create all the available commands. The update, Add and Delete commands are created automatically based on SelectCommand.

OleDbCommandBuilder mybuilder = new OleDbCommandBuilder (myDataAdapter);

And after writing this statement, you are now in position to make changes back to database. All you have to do is create a separate dataset for all the modified rows and then apply the Update command of DataAdapter.

1.      DataSet newSet = myDataSet.GetChanges (DataRowState.Modified);

2.      myDataAdapter.Update(newSet, "EmployeeData");

GetChanges method would return all the modified rows .The parameter in GetChanges method can be different. For example:

DataRowState.Added: would return newly added rows

DataRowState.Deleted: would return deleted rows

To summarize:

• Datasets store a copy of data from the database tables.
• Datasets can not directly retrieve data from Databases.
• DataAdapters are used to link Databases with DataSets.
• To populate dataset, db connection is created which is followed by creating a DataAdapter and calling its Fill method.
• OleDbCommand class can be used for applying desired SQL command on DataAdapter object. Dataset would be populated according to the selection criteria given in this command.
• DataTables contain results of SQL query.
• Modifications in DataTable can be done which would be later written on database by GetChanges method of Dataset.

Tutorial toolbar:  Tell A Friend  |  Add to favorites  |  Feedback  |