Products
Database Search Solution (New Version) Search Control SEO Pager Highlighter Shortcut Controls Crypt Package Free ASP.NET Controls
Geotargeting Component ASP.NET Media Player Control Flash Video Player Control Services
ASP.NET Telecommute Jobs Free IP Location Lookup Test .Net Regular Expressions CSS/Table/DIV Page Layouts Custom Programming Article Sites Master List |
DataSet and DataAdapter in ASP.NET 2.0 - Part 2 of 2Till 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:
Changing department of an employee in Employee tableIn 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:
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:
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:
Tutorial toolbar: Tell A Friend | Add to favorites | Feedback | |