Error Handling

The SQLServerError object collects information relevant to errors and warnings generated by the SQL Server server. See SQLServerError Class for more information.

The SQLServerException object is created and thrown when the SQL Server server returns an error. Exceptions generated by the data provider are returned as standard run time exceptions. If an exception is raised by an error from the data source, it will be returned as a provider-specific exception, for example:

[DataDirect] [SQL Server Provider] [SQL Server] <Exception message>

If the exception is raised by the data provider itself, it is returned as a standard .NET Framework exception.

[DataDirect] [SQL Server Provider] {Exception message}

Some provider-specific code is required when handling provider-specific exceptions.

See SQLServerException Class for more information.

Handling Raiserror Statements

The data provider returns errors generated by raiserror statements by throwing a SQLServerException.

For example, suppose you have the following stored procedure and user-defined error:

sp_addmessage 20100,'This is a sample raiserror'
create procedure raiserrortest as
begin
select 1
raiserror 20100
end

The raiserror statement will throw an exception in the application.

SQLServerConnection Conn;
Conn = new SQLServerConnection("host=bowhead;
port=1433;User ID=test01;Password= test01");
SQLServerCommand 
DBCmd = new SQLServerCommand("raiserrortest", Conn);
SQLServerDataReader myDataReader;
try
{
    Conn.Open();
    DBCmd.CommandType = CommandType.StoredProcedure;
    myDataReader = DBCmd.ExecuteReader();
    while (myDataReader.Read())
    {
        // Just keep reading all the records till finished.
    }
    // Now skip to the next result set.
    // This will throw an exception as the raiserrortest
    // stored procedure generates an error at this point.
    myDataReader.NextResult();
}
catch (Exception ex)
{
    // Display the exception in a messagebox
    MessageBox.Show (ex.Message);
}
// Close the connection
Conn.Close();

Handling Print Statements

The SQL Server data provider returns all Print statement errors as database warnings. The data provider initiates the SQLServerInfoMessageEvent when a Print statement is encountered, as shown in the following code fragment:

// Define an event handler
public void myHandler(object sender, SQLServerInfoMessageEventArgs e)
// Display any warnings in a messagebox
{
   MessageBox.Show (e.Message,"This is the Print Statement");
}

Add the following code fragment to a method, and call that method:

SQLServerConnection Conn;
Conn = new SQLServerConnection("host=bowhead;port=1433;User ID=test01;
Password=test01");
SQLServerCommand DBCmd = new SQL ServerCommand("printtest", Conn);
SQLServerDataReader myDataReader;
try
{
    // Add the user's event handler to the InfoMessage
    // delegate
    Conn.InfoMessage += new SQLServerInfoMessageEventHandler(myHandler);
    Conn.Open();
    DBCmd.CommandType = CommandType.StoredProcedure;
    myDataReader = DBCmd.ExecuteReader();
    while (myDataReader.Read())
    {
        // Just keep reading all the records until finished.
}
    // Now skip to the next result set.
    // This will throw a SQLServerInfoMessageEvent as the
    // printtest storedprocedure generates a warning
    // at this point.
    myDataReader.NextResult();
}
catch (Exception ex)
{
    // Display any exceptions in a messagebox
    MessageBox.Show (ex.Message);
}
// Close the connection
Conn.Close();