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