visual basic barcode In closing, let s look at how we can handle SQL Server messages inside our application code. in Visual C#.NET

Painting Code 128A in Visual C#.NET In closing, let s look at how we can handle SQL Server messages inside our application code.

In closing, let s look at how we can handle SQL Server messages inside our application code.
Code 128 Maker In Visual C#.NET
Using Barcode generation for VS .NET Control to generate, create Code 128 Code Set C image in .NET applications.
www.OnBarcode.com
Scan Code128 In C#.NET
Using Barcode reader for .NET Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
Handling SQL Server messages on the client
Print Barcode In Visual C#
Using Barcode printer for .NET framework Control to generate, create Barcode image in Visual Studio .NET applications.
www.OnBarcode.com
Encoding PDF 417 In Visual C#.NET
Using Barcode creator for .NET framework Control to generate, create PDF 417 image in .NET applications.
www.OnBarcode.com
When a message is sent from SQL Server via a PRINT statement or a RAISERROR with a severity level of 10 or less, it generates an event on the .NET side. You can capture this event by writing a handler for the SqlConnection class s InfoMessage event. The handler for the InfoMessage event takes two parameters: the sender and an instance of SqlInfoMessageEventArgs. This class contains three properties. The first is the Message that was printed or generated by the RAISERROR statement. The second is the Source, which is usually the .Net SqlClient Data Provider. The third is the Errors property, which is a collection of SqlError objects and behaves just like it did when we saw it earlier. Listing 12 is an example.
UCC - 12 Creation In Visual C#
Using Barcode printer for VS .NET Control to generate, create UCC.EAN - 128 image in VS .NET applications.
www.OnBarcode.com
Barcode Drawer In Visual C#.NET
Using Barcode creator for Visual Studio .NET Control to generate, create Barcode image in Visual Studio .NET applications.
www.OnBarcode.com
Listing 12 Outputting SQL Server messages
Print ECC200 In Visual C#.NET
Using Barcode printer for .NET framework Control to generate, create ECC200 image in Visual Studio .NET applications.
www.OnBarcode.com
UPC-E Supplement 2 Generation In C#.NET
Using Barcode printer for .NET framework Control to generate, create UPC-E Supplement 2 image in .NET framework applications.
www.OnBarcode.com
using System.Data; using System.Data.SqlClient; class Program { static void Main(string[] args) { SqlConnection conn = new SqlConnection(@"Server=L60\YUKON; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand("PRINT 'Hello'", conn); conn.InfoMessage += new SqlInfoMessageEventHandler(conn_InfoMessage); try { cmd.Connection.Open();
Encoding Code 128B In None
Using Barcode encoder for Office Excel Control to generate, create Code 128 image in Office Excel applications.
www.OnBarcode.com
Generating Code 128 Code Set A In None
Using Barcode generator for Word Control to generate, create Code128 image in Office Word applications.
www.OnBarcode.com
Handling SQL Server errors on the client
Creating Barcode In Objective-C
Using Barcode encoder for iPhone Control to generate, create Barcode image in iPhone applications.
www.OnBarcode.com
PDF 417 Reader In Visual C#.NET
Using Barcode decoder for .NET framework Control to read, scan read, scan image in .NET applications.
www.OnBarcode.com
cmd.ExecuteNonQuery(); cmd.CommandText ="RAISERROR('An error as message', 5, 12)"; cmd.ExecuteNonQuery(); Console.WriteLine("No error returned"); } catch (SqlException sqlex) { Console.WriteLine("First Error Message: " + sqlex.Message); Console.WriteLine("Error Count: {0}", sqlex.Errors.Count.ToString()); } } static void conn_InfoMessage(object sender, SqlInfoMessageEventArgs e) { Console.WriteLine("SQL Server Message: {0}", e.Message); Console.WriteLine("Message Source: {0}", e.Source); Console.WriteLine("Message Count: {0}", e.Errors.Count.ToString()); } }
PDF417 Scanner In Visual Basic .NET
Using Barcode scanner for .NET Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Quick Response Code Decoder In C#.NET
Using Barcode reader for Visual Studio .NET Control to read, scan read, scan image in .NET framework applications.
www.OnBarcode.com
This returns the following result:
Code 128 Code Set C Maker In .NET Framework
Using Barcode creation for .NET framework Control to generate, create USS Code 128 image in .NET framework applications.
www.OnBarcode.com
ANSI/AIM Code 128 Printer In Java
Using Barcode maker for Java Control to generate, create Code 128B image in Java applications.
www.OnBarcode.com
SQL Server Message: Hello Message Source: .Net SqlClient Data Provider Message Count: 1 SQL Server Message: An error as message Message Source: .Net SqlClient Data Provider Message Count: 1 No error returned
Encoding EAN-13 Supplement 5 In None
Using Barcode printer for Office Excel Control to generate, create GTIN - 13 image in Excel applications.
www.OnBarcode.com
Decode ANSI/AIM Code 128 In None
Using Barcode recognizer for Software Control to read, scan read, scan image in Software applications.
www.OnBarcode.com
Another interesting characteristic of this approach is that you can capture informational RAISERROR statements as they re executed rather than when a batch ends. Listing 13 shows an example.
Read Code-128 In .NET Framework
Using Barcode reader for .NET framework Control to read, scan read, scan image in VS .NET applications.
www.OnBarcode.com
Barcode Encoder In VB.NET
Using Barcode encoder for .NET Control to generate, create Barcode image in .NET applications.
www.OnBarcode.com
Listing 13 Capturing RAISERROR statements
using System.Data; using System.Data.SqlClient; class Program { static void Main(string[] args) { SqlConnection conn = new SqlConnection(@"Server=L60\YUKON; Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand( @"PRINT 'Printed at buffer flush' RAISERROR('Starting', 0, 1) WITH NOWAIT; WAITFOR DELAY '00:00:03'; RAISERROR('Status', 0, 1) WITH NOWAIT; WAITFOR DELAY '00:00:03'; PRINT 'Done';", conn); conn.InfoMessage += new SqlInfoMessageEventHandler(conn_ShortMessage);
try {
Error handling in SQL Server and applications
cmd.Connection.Open(); cmd.ExecuteReader(); Console.WriteLine("No error returned"); } catch (SqlException sqlex) { Console.WriteLine("First Error Message: " + sqlex.Message); Console.WriteLine("Error Count: {0}", sqlex.Errors.Count.ToString()); } } static void conn_ShortMessage(object sender, SqlInfoMessageEventArgs e) { Console.WriteLine("[{0}] SQL Server Message: {1}", System.DateTime.Now.ToLongTimeString(), e.Message); } }
This returns the following result:
[3:39:26 [3:39:26 [3:39:29 [3:39:32 No error PM] SQL Server PM] SQL Server PM] SQL Server PM] SQL Server returned Message: Message: Message: Message: Printed at buffer flush Starting Status Done
Normally, when you do a series of PRINT statements inside a SQL Server batch or stored procedure, the results are all returned at the end. A RAISERROR WITH NOWAIT is sent immediately to the client, as is any previous PRINT statement. If you remove the WITH NOWAIT from the first RAISERROR, the first three lines are all printed at the same time when the RAISERROR WITH NOWAIT pushes them all to the client. This approach can provide a convenient way to return status information for long-running tasks that contain multiple SQL statements.
Summary
SQL Server error handling doesn t need to be an afterthought. SQL Server 2005 pro-
vides powerful tools that allow developers to selectively handle, capture, and consume errors inside SQL Server. Errors that can t be handled on the server can be passed back to the application. .NET has specialized classes that allow applications to capture detailed information about SQL Server exceptions.
Summary
About the author
Bill Graziano has been a SQL Server consultant for 10 years, doing production support, performance tuning, and application development. He serves on the board of directors for the Professional Association for SQL Server (PASS), where he serves as the vice president of marketing and sits on the executive committee. He s a regular speaker at conferences and user groups across the country. Bill runs the popular web site http:/ /SQLTeam.com and is currently a SQL Server MVP.
Copyright © OnBarcode.com . All rights reserved.