Home
    Shop
    Advertise
    Write For Us
    Affiliate
    Newsletter
    Contact

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

Till now, you have come to know How Data into DataSets can be read from databases (by using DataAdapters). We have also discussed how changes in the dataset can be transferred back to database.

 

In this article, you would learn:

  • An example to update the database using Datasets
  • How the Dataset track modified rows
  • How to handle errors while reading and changing data with dataset
  • How to read data in Dataset from multiple tables.
  • How to resolve referential integrity issue.

Changing department of an employee in Employee table

In this example, user would be prompted for the Employee ID and the new department name of Employee. If the employee exists, it would modify the department ID against the Employee.

Listing 1.2

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

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

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

4.      <html>

5.      <body>

6.       

7.      <table border=1>

8.      <tr>

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

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

11.  <td><b> Employee department </b></td>

12.  </tr>

13.   

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

15.  =Microsoft.JET.OLEDB.4.0;" + "Data Source=C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb");

16.   

17.   <%

18.  DataSet ds = new DataSet();

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

20.  employee");

21.    

22.  OleDbAdapter dp = new OleDbAdapter (CObject, con);

23.  OleDbCommandBuilder mybuilder = new OleDbCommandBuilder (dp);

24.   

25.   

26.  dp.Fill (ds, "emp");

27.   

28.   foreach (DataRow dr in ds.Tables["emp"].Rows)

29.  {

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

31.   for (int  i = 0 ; i <3 ; i++) 

32.   

33.  // dr[0] represents emp_ID , dr[1] represents emp_Name, dr[2]

34.  represents emp_Dept

35.   

36.   {

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

38.   }

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

40.   

41.  %>

42.   <br>

43.   <form>

44.   Enter Employee ID:

45.  <asp:TextBox id="emp_id" runat= "server" />

46.  <br>

47.   

48.  Enter new department:

49.  <asp:TextBox id="emp_dept" runat= "server" />

50.   

51.  Submit Request <asp:button OnClick="NewDept" runat="server" />

52.   </form>

53.   </table>

54.   

55.  <script runat = "server" >

56.   void NewDept (Object Sender, EventArgs e)

57.   {

58.    int row_no = Int32.Parse (emp_id.Text)

59.    int i=0;

60.    int flag = 0;

61.    foreach (DataRow dr1 in ds.Tables["emp"].Rows)

62.    {

63.         if (dr1[0] = = row_no) // dr1[0] represents employee ID

64.          {

65.            flag =1;

66.             break;

67.           }

68.   

69.         else

70.           {

71.            i++;

72.            }

73.    }

74.   

75.    if ( flag = =1)

76.    {

77.      DataTable dt =ds.Tables["emp"];

78.      dt[row_no][2] = emp_dept.Text;

79.     // Dept Column is the third column in Employee Table

80.      

81.      DataSet new_ds = ds.GetChanges (DataRow.Modified);

82.      dp.Update (new_ds, "emp");

83.      ds.AcceptChanges();

84.     }

85.   

86.  }

87.   

88.   </body>

89.   </html>

Let me elaborate this example. The user enters the employee Id and NewDept () parses this input to integer value. We are assuming that employee is stored on IDth row no in Employee Table. We would modify this row only. In the end, we have applied AcceptChanges () function. The purpose of this function is to change the property of RowStates to Unmodified. This function is available in DataSet, DataTable and DataRow class in order to achieve results correctly. When this function is called on DataSet, the DataSet object calls this function on all the DataTables it has. The DataTable object in turn applies this function to all DataRows. This function should be applied when you call Update function of DataAdapter class. A  row only deletes from DataTable when AcceptChanges() is called on it.

At this stage, you must have an idea how rows with modified values are brought in new DataSet by applying some property of DataRow. Whenever a row is changed, its status is set from UnChanged to Modified. Whenever, a new row is added, its status would be depicted by DataRow.Added and same is case for row deletion. When the GetChanges () function request for changed rows with certain criteria, all the rows whose RowState value matches the required criteria would be selected.

Handling Errors while reading and writing Database:

Let's imagine there is an error in reading data from the database or the user incurred exception while writing data back to database. What would be your strategy as programmer? Either you can allow exceptions to occur or you would want to handle these exceptions. Obviously, the thought would be Let the errors occur but devise a strategy to recover. One possible solution can be ignoring and Logging the error.

As we know the Update command of DataAdapter updates the database with modified row values. Similarly, Fill command reads the results of DataAdapter query into DataTable of the dataset. The DataAdapter classes namely OleDbAdapter and SqlDataAdapter fires certain events when there is error executing the Fill and Update commands.

For the Update command, there are two events: RowUpdating and RowUpdated.  I am writing a sample custom Event Handler. Obviously, you would need to change it according to your strategy.

Listing 1.3

1.      void UpdatedHandler (Object Sender, SqlUpdatedEventArgs e)

2.      {

3.      if (e.Errors.Count = = 0)

4.      {

5.       // do nothing , there wasn't any error

6.      }

7.       else

8.       {

9.        e.Status = SkipCurrentRow;

10.    

11.  }

Line # 9 would skip modifying the current row. You can change the status to other values. e.g.

e.Status = SkipAllRemainingRows;

This line would prevent any further updates in the table. Note that you should do this step when the errors can not be recovered.

To do error handling in Listing 1.2, add this line to DataAdapter object dp.

dp.Updating += new SqlRowUpdatedEventHandler (UpdatedHandler)

Where UpdatedHandler is defined in Listing 1.3

We can define an Event Handler for Fill command in a similar fashion.

Listing 1.4

1.      void FillHandler (Object Sender, SqlFillErrorEventHandler e)

2.      {

3.      if (e.Errors.Count = = 0)

4.      {

5.       // do nothing , there wasn't any error

6.      }

7.       else

8.       {

9.        // Checking the error

10.   ...

11.   // Error can not recovered

12.   e.Continue = False;

13.    

14.  }

Line # 12 would stop reading into the DataTable anymore.

Working with Multiple Tables:

Generally, you have to read from more than one database table. With datasets, you can do this task easily. You can have more than one DataTables in order to store data from different tables. Listing 1.5 gives one such example.

Listing 1.5

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

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

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

4.       

5.      <%

6.      DataSet ds = new DataSet ();

7.      OleDbCommand CObject1 = new OleDbCommand ("Select * from

8.      Employee");

9.      OleDbCommand CObject2 = new OleDbCommand ("Select * from

10.  Department");

11.   

12.  OleDbAdapter dp1 = new OleDbAdapter (CObject1, con);

13.  OleDbAdapter dp2 = new OleDbAdapter (CObject2, con);

14.   

15.  dp1.Fill (ds, "emp");

16.  dp2.Fill (ds, "dept");

Now, we have an issue to resolve. We know from our knowledge of Databases that:

  • Every Table has a primary key which is unique for every row.
  • Two tables can be joined through Primary and Foreign Keys. The later refers to the primary key of the parent table.
  • If a foreign key refers a non-existent primary key, Referential Integrity constraint is violated.

In Listing 1.5, Employee and Department tables which we read into emp and dept DataTables respectively are related. An employee can work in a department and the employee table keeps a foreign key for department ID of the employee. Since we have loaded the DB tables in two different Data Tables, there is quite possibility that referential integrity constraint would be violated. There can be a case when the user deletes a department from dept Data Table. In this case, all those rows in emp whose foreign key value is the deleted department ID would be unstable; there would exist no department for these employees to work. To resolve such issue, there is DataRelations collection in every Dataset. These relations would define the primary and foreign attributes of different Data Tables. You can add it to Listing 1.5 in the following way:

Ds.Relationships.Add ("EmpDept", ds.Tables ["dept"].Columns ["dept_id"],                ds.Tables ["emp"].Columns ["emp_id"] )

Where:

  • EmpDept is the name of relationship.
  • ds.Tables ["dept"].Columns ["id"] is the primary key to which values from other Data Table should refer.
  • ds.Tables ["emp"].Columns ["id"] represents the foreign key

You can use this relationship defined for retrieving child values of every parent unique value. Or the parent's unique value for every child value. For example,

Listing 1.6

1.      foreach (DataRow dr in ds.Tables["dept"] )

2.      {

3.      foreach (DataRow dr1 in dr.getChildRows["dept_id"] )

4.      {

5.      Response.Write ( dr1["emp_name"].ToString() );

6.      }

7.      } 

This example prints all the employees against every department.

To summarize:

  • The purpose of AcceptChanges() function is to change the property of RowStates to Unmodified.
  • Whenever a row is changed, its status is set from UnChanged to Modified. Whenever, a new row is added, its status would be depicted by DataRow.Added.
  • The DataAdapter classes namely OleDbAdapter and SqlDataAdapter fires certain events when there is error executing the Fill and Update commands.
  • For the Update command, there are two events: RowUpdating and RowUpdated.
  • You can write your custom event handlers.
  • To resolve referential integrity issue, there is DataRelations collection in every Dataset.

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