Sunday, June 16, 2013

Catch Error raised by Store Procedure

How to catch error message from store procedure in your ADO.NET code.
In this post I am going to discuss how to catch error from store procedure in your application code. And guys it’s very simple to do this task. Simply call your store procedure within try catch block of your ADO.NET code.

To show in example ,I have created a very simple store procedure in my Database. And this store procedure will throw error to my ADO.NET code. Let’s have a look in my store procedure

create procedure myprocedure
as
                begin
                                RAISERROR('Thsi is error',15,15);
                end

The RAISERROR() function will generate error to our ADO.NET code.

And the ADO.NET code to handle the error is.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;

namespace BlogProject
{
     class Program
    {
        static void Main(string[] args)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=test;Integrated Security=True";
            con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "myprocedure";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = con;
           
            try
            {
                int Value = (Int32)cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.Write(ex.Message);
            }
            Console.ReadLine();
        }
    }

}


No comments:

Post a Comment