transaction in stored procedure

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

VS2008

C#

, +

i want to know that if transaction is failed in stored procedure then how to know in c# where stored procedure called?

SQL

ALTER PROCEDURE [dbo].[sp_eContractorChange] 
(
@UserId nvarchar(30)=null,

@Contractor nvarchar(30)=null,

@Mode char(1)
)
AS
BEGIN	
     if @Mode='U'
     begin
           BEGIN TRY
           BEGIN TRAN
           insert into eContractorTransLog(UserId,Contractor) values(@UserId,@Contractor);
           update TUser] set Department=@Contractor where UserID=@UserId;
           
           COMMIT TRAN
           END TRY
           BEGIN CATCH
           -- Execute error retrieval routine.
           -- EXECUTE usp_GetErrorInfo;
           ROLLBACK TRAN
     
           END CATCH
     end
END

C#

 using (cmd = new SqlCommand("sp_eContractorChange", con))
 {
      cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Parameters.Add(new SqlParameter("@UserId", comboBoxUserId.Text.Trim()));
                        cmd.Parameters.Add(new SqlParameter("@Contractor", txtContractorName.Text.Trim()));


cmd.Parameters.Add(new SqlParameter("@Mode", "U"));
                        dr = cmd.ExecuteReader();
                      
                        if (dr != null)
                        {

                            Clear();
                            MessageBox.Show("User's contractor has been updated succesfully.");

                        }
}

Herre if tran failed it return dr not is equal to null ,how can be recognise in cE?

Posted 24-Nov-13 22:46pm

Member 7909353

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2013-11-24T23:08:00

What?
Why are you using ExecuteReader on a query that doesn;t return any rows regardless of if it succeeds or fails? ExecuteReader never returns null - it returns a Reader which may or may not have rows, but it can't return null.
In this case, it will always return a reader which contains no rows because at no point in your query do you select any rows!

Return a status code by all means:

SQL

...
        ROLLBACK TRAN
        RETURN -1
        END CATCH
    END
    RETURN 0
END

And use ExecuteScalar to perform the SP and return an integer result.

[edit]I can't spell "ExecuteScalar" this morning... - OriginalGriff[/edit]