none
Throwing exception in Stored proc to catch in C# and rollback the transaction

    Question

  •  

     

    Hi,

    I am using sql2k5. I just wanted to throw an error from stored procedure with some message to C# to rollback my transaction.
    Here is how i wnated to do ( in sequence )

    C#
     =====
    Open a connection
    Begin the transaction

    Execute the command

    In the Stored Proc
     ===========
     do multiple operations one by one
     if error
      stop processing further
      Throw the error

    C#
    ========
    if exception
     rollback the transaction
    else
     commit the transaction

     

    I have tried using raise error in stored proc but never thrown exception

     

    Can any one let me know how to achieve this scenario??

     

    ~Mohan Babu


    Thursday, September 27, 2007 11:05 PM

All replies

  • The problem appears to be in the way that you are handling the error in the stored proc. When you execute a sql command through an object that you've instantiated through your code. You will only get an exception back in the code if the error level of the sql server is high enough. A good example of this would be if you were to set it up to intentionally violate a primary key constraint when executing your statement.

    From the way that you have phrased your question it seems that you are defining an "error" as some condition in the behavior of the statement or the data that is unacceptable with what you are trying to do. Either way I would argue that attempting to throw an exception from your SQL Server is likely an inefficient way to handle the condition that you detect in your stored proc.

    A much more common way to handle this type of situation is to pass in an OUT parameter to your stored proc that you can use to monitor the state of the activity you're performing. If for whatever reason you do need to handle the situation this way please submit the section of the T-SQL you're using to raise the error. Nothing below a level of 10 will come close to raising an exception in your application.

    John V. McCarthy
    Thursday, September 27, 2007 11:21 PM
  • Ok..I am executing dymanic sql statements and calling different stored procedures. If any one of the operations fails..i need to track what gone wrong and roll back the transaction and pass the error message to C# for trouble shooting.

    Can you please let me know how exactly can we acheive this?

     

    Friday, September 28, 2007 3:03 AM
  • In order to catch the SQL Exception that is thrown you would need to implement something like this.

    Code Block

    namespace TestApp1
    {
        class Program
        {
            static void Main(string[] args)
            {
                SqlConnection myConn = new SqlConnection("Data Source=myDataSourceName;Initial Catalog=myInitialCatalog;Persist Security Info=True;User ID=myID;Password=myPassword");
                myConn.Open();
                SqlCommand myCommand = new SqlCommand("sp_TestException", myConn);
              
                myCommand.CommandType = CommandType.StoredProcedure;

                try
                {
                    myCommand.ExecuteNonQuery();
                }
                catch (System.Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.InnerException);
                    Console.WriteLine(ex.StackTrace);
                }
            }
        }
    }


    Doing this will allow you to catch true sql exceptions arriving from your code execution. If this doesn't work for your situation then we need to redefine what it is that you're referring to as an exception condition in your stored procedures. If what's listed above doesn't work please respond back with a sample of the kind of exception that would be thrown in your stored proc and the code that calls it from your application.

    John V. McCarthy

    Friday, September 28, 2007 12:53 PM
  • I have done that using the RAISERROR function in stored procedures and recieved custom messages in C# for the errors occured. So all it would take is to create an error handler for each sql statement in your stored procedure by checking the @@ERROR value after each.

     

    Friday, September 28, 2007 3:00 PM
  • Harish,

     

    Can you please me know how do we do in stored proc ( code snippets please)

     

    Thanks

    ~Mohan

     

    Friday, September 28, 2007 4:49 PM