Error Handling

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.141.104.97