Error Handling | Special Edition Using ASP.Net

I have shown a lot of code examples without any support for trapping errors, which is not an ideal way to program in real life. There are numerous ways a connection or command execution attempts can fail, all of which need to be handled correctly. Listings 9.29 and 9.30 show the DataReader example with the correct error-handling structure applied.

Listing 9.29 Using the DataReader ”Visual Basic Example

 Import System.Data  Import System.Data.SqlClient  Dim myConnection as new SQLConnection("server=localhost;uid=sa;pwd=; database=pubs")  Dim myCommand as new SQLCommand("select * from Publishers",myConnection)  Dim myReader as SQLDatareader  Try     myConnection.Open()     myReader=myCommand.ExecuteReader()     While (myReader.Read)        Response.Write(myreader("pub_name") + "<br>")     End While     myReader.Close()  Catch myException as Exception     Response.Write (myException.ToString())  Finally  If myConnection.State = Data.DBObjectState.Open then     MyConnection.Close()  End If  End If  End Try

Listing 9.30 Using the DataReader ”C# Example

 using System.Data;  using System.Data.SqlClient;  ...  SQLConnection myConnection  = new SQLConnection("server=localhost; uid=sa;pwd=;  database=pubs");  SQLCommand myCommand = new SQLCommand("select * from Publishers",myConnection);  SqlDataReader myReader = NULL;  try  {     myConnection.Open();     myReader=myCommand.ExecuteReader();     if(myReader != null)     {           While (myReader.Read())             Response.Write(myreader("pub_name") + "<br>");          myReader.Close();      }  }  catch (Eexception e)  {     Response.Write (e.ToString());  }  finally  {     If (myConnection.State == Data.DBObjectState.Open)        MyConnection.Close();  }

You probably will first notice the Try/Catch block statements in both VB and C# examples. C++ programmers should recognize this error trapping implementation immediately, which has been carried forward to ASP.NET in all .NET languages. The On Error mechanism that VB programmers are accustomed to has been retired . (See Chapter 2, "Scripting Languages," for more details about exceptions.)

If the connection failed, the catch statement would trap the error condition and handle it; in this case I simply write it to the browser. The finally block is always executed regardless of whether an exception is thrown.

ADO.NET goes another step by allowing the developer to create custom error messages for each row of data in a DataSet . When using the SQLDataAdapter , you can use the RowError property to attach error messages to rows if inserts or other actions fail. You also can filter rows that have errors to display or send them to an error-handling function. Listings 9.31 and 9.32 offer an example of applying an error message to a row of data in a DataSet . It demonstrates how to handle an insert command that fails.

Listing 9.31 Custom Error Handling in VB

 <%@ Import Namespace="System.Data" %>  <%@ Import Namespace="System.Data.SqlClient" %>  <html>  <head>  <script language="VB" ID="Script1" runat="server">  Sub Page_Load()      Dim myConnection as new _        SqlConnection("server=localhost;uid=sa;pwd=;database=pubs")      Dim myDataAdapter as new SqlDataAdapter("select * from Authors", myConnection)       Dim myCommandBuilder as new SqlCommandBuilder(myDataAdapter)      Dim myDataSet as new DataSet      myConnection.Open      myDataAdapter.Fill(myDataSet,"Authors")      myDataSet.Tables("Authors").Rows(0).RowError = "Error message for row 1"      myDataSet.Tables("Authors").Rows(1).RowError = "Error message for row 2"      Dim myDataTable as DataTable      myDataTable = myDataSet.Tables("Authors")      if ( myDataTable.HasErrors ) Then          Dim ErrorRows as DataRow()          ErrorRows = myDataTable.GetErrors()          Response.Write("DataTable " + myDataTable.TableName + _            " has " + ErrorRows.Length.ToString() + " Errors!<br>")          Dim I as Integer          For I = 0 To ErrorRows.Length -1             Response.Write("Row Error for row " + _               ErrorRows(i)("au_id").ToString() + " Error Msg = " + _               ErrorRows(i).RowError + "<br>")          Next I      Else          Response.Write("DataTable " + myDataTable.TableName + " has no errors")      End If  End Sub  </script>  </body>  </html>

Listing 9.32 Custom Error Handling in C#

 <%@ Import Namespace="System.Data" %>  <%@ Import Namespace="System.Data.SqlClient" %>  <html>  <head>  <script language="C#" ID="Script1" runat="server">  void Page_Load()  {      SqlConnection myConnection =        new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs");      SqlDataAdapter myDataAdapter =        new SqlDataAdapter("select * from Authors", myConnection);      SqlCommandBuilder myBuilder = new SqlCommandBuilder(myDataAdapter);      DataSet myDataSet = new DataSet();      myConnection.Open();      myDataAdapter.Fill(myDataSet,"Authors");       myDataSet.Tables["Authors"].Rows[0].RowError = "Error message for row 1";      myDataSet.Tables["Authors"].Rows[1].RowError = "Error message for row 2";      DataTable myDataTable;         // GetChanges for modified rows only.      myDataTable = myDataSet.Tables["Authors"];         // Check the DataSet for errors.      if ( myDataTable.HasErrors ) {          DataRow[] ErrorsRows = myDataTable.GetErrors();          Response.Write("DataTable " + myDataTable.TableName + " has " +             ErrorsRows.Length.ToString() + " Errors!<br>");          for (int i = 0; i <=  ErrorsRows.Length -1; i++)             Response.Write("Row Error for row " +               ErrorsRows[i]["au_id"].ToString() + " Error Msg = " +               ErrorsRows[i].RowError + "<br>");      }      else         Response.Write("DataTable " + myDataTable.TableName + " has no errors");  }  </script>  </body>  </html>

From the example, you can see that a DataSet is populated with the Authors table. Two custom error messages are created and attached to the first two rows in the table. The table then is checked for errors, and the GetErrors function fills a DataRow array. Finally, the array is parsed and the error messages are displayed. If no error has been created, the else would have executed, showing there were no errors.