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






/*
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);
        }
    }








32.15.SqlConnection Stored Procedure
32.15.1.Call a stored procedure
32.15.2.Call stored procedure with no parameter
32.15.3.Call stored procedure with parameter and return value
32.15.4.Call storedprocedure and pass in the parameter
32.15.5.Call stored procedure with parameters using SqlCommand
32.15.6.Call StoredProcedure with input and output parameters
32.15.7.Catch exception when calling stored procedure
32.15.8.Retrieving a Return Value from a Stored Procedure
32.15.9.Retrieving a Stored Procedure Output Parameter
32.15.10.Retrieving Data Using a SQL Server Stored Procedure
32.15.11.Retrieves stored procedure parameter information
32.15.12.Call stored procedure
32.15.13.Call stored procedure with SqlCommand
32.15.14.Raising and Handling Stored Procedure Errors