Retrieving Informational Messages

Severity levels of 10 and lower are used to represent informational messages and do not cause a SqlException to be raised.
To retrieve informational messages:

Create an event handler and subscribe to the InfoMessage event exposed by the SqlConnection object. This event's delegate is shown in the following code fragment.

·         public delegate void SqlInfoMessageEventHandler( object sender,

·                                                              SqlInfoMessageEventArgs e );

Message data is available through the SqlInfoMessageEventArgs object passed to your event handler. This object exposes an Errors property, which contains a set of SqlError objects—one per informational message. The following code fragment illustrates how to register an event handler that is used to log informational messages.

public string GetProductName( int ProductID )

  SqlConnection conn = null;

try

    conn = new SqlConnection(

        "server=(local);Integrated Security=SSPI;database=northwind");

    // Register a message event handler

    conn.InfoMessage += new SqlInfoMessageEventHandler( MessageEventHandler );

    conn.Open();

    // Setup command object and execute it

    . . .

  catch (SqlException sqlex)

    // log and handle exception

    . . .

  finally

    if(conn != null) conn.Close();

// message event handler

void MessageEventHandler( object sender, SqlInfoMessageEventArgs e )

  foreach( SqlError sqle in e.Errors )

    // Log SqlError properties

    . . .

Performance

This section introduces a number of common data access scenarios, and for each one, provides details about the most high-performance and scalable solution in terms of ADO.NET data access code. Where appropriate, performance, functionality, and development effort are compared. This section considers the following functional scenarios:

. Retrieving a result set and iterating through the retrieved rows.
. Retrieving a single row with a specified primary key.
. Retrieving a single item from a specified row.
. Checking to see whether or not a row with a particular primary key exists. This is a variation of the single item lookup scenario in which a simple Boolean return is sufficient.

Retrieving Multiple Rows

In this scenario, you want to retrieve a tabulated set of data and iterate through the retrieved rows to perform an operation. For example, you might want to retrieve a set of data, work with it in disconnected fashion, and pass it to a client application as an XML document (perhaps through a Web service). Alternatively, you might want to display the data in the form of a HTML table.
To help determine the most appropriate data access approach, consider whether you require the added flexibility of the (disconnected) DataSet object, or the raw performance offered by the SqlDataReader object, which is ideally suited to data presentation in business-to consumer (B2C) Web applications. Note The SqlDataAdapter used to populate a DataSet internally uses a SqlDataReader to access the data.

Shashi Ray