Raising and Handling Stored Procedure Errors : SqlConnection Stored Procedure « ADO.Net « C# / CSharp Tutorial

1. C# / CSharp Tutorial
2. ADO.Net
3. SqlConnection Stored Procedure

/*
CREATE PROCEDURE RaiseError
    @Severity int,
    @State int = 1
AS
    RAISERROR ('Error of severity %d raised from stored procedure RaiseError.',
            @Severity, @State, @Severity)
    RAISERROR ('Error of severity %d raised from stored procedure RaiseError.',
            @Severity, @State, @Severity) WITH LOG

    RETURN
*/

using System;
using System.Data;
using System.Data.SqlClient;

    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString))
            {
                connection.InfoMessage += new SqlInfoMessageEventHandler(SqlMessageEventHandler);
                SqlCommand command = new SqlCommand("RaiseError", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@Severity", SqlDbType.Int);
                command.Parameters.Add("@State", SqlDbType.Int);
                for(int severity = -1; severity <= 26; severity++){
                    command.Parameters["@Severity"].Value = severity;
                    command.Parameters["@State"].Value = 0;
                    connection.Open( );
                    command.ExecuteNonQuery( );
                    connection.Close( );
                }
            }
        }
        private static void SqlMessageEventHandler(object sender,SqlInfoMessageEventArgs e)
        {
            Console.WriteLine("MESSAGE: {0}", e.Message);
        }
    }