CODEWITHSUNDEEP
c#sql-server
user1078389
user1078389

Reputation: 23

update error handling in C#

I have a customer table in sqlserver which contains a rowversion field and I am incrementing it everytime I update the record, I just have to check with 

if(Customer.rowversion=@roeversion ) where customerID=@customerID

execute the update.

else RAISERROR('Update cannot be executed. There is a row version conflict.', 16, 1)

So have to now pass an out param from my c# code and return the error value. and also - Get the Error Code for the statement just executed.

SELECT @ErrorCode=@@ERROR

So how should I return the value from SQLSERVER update query into my c# code so that I can display the message.

Upvotes: 1

Views: 355

Answers (2)

competent_tech
competent_tech

Reputation: 44971

If you do not already, you should have your database code in a stored procedure. The stored procedure would look something like:

CREATE PROCEDURE s_my_procedure
  @RowVersion    int ,
  @CustomerID    int ,
  ... additional fields here
  @ErrorCode     INT OUTPUT
AS
  IF EXISTS(SELECT 1
              FROM Customer
             WHERE RowVersion = @RowVersion
               AND CustomerID = @CustomerID)
    BEGIN
      UPDATE Customer
         SET ...
       WHERE RowVersion = @RowVersion
         AND CustomerID = @CustomerID

      SET @ErrorCode = 0
    END 
  ELSE
    BEGIN
      SET @ErrorCode = 1234 -- Something meaningful to your app
    END

You should try to avoid raising errors whenever possible.

Then, assuming you have a stored procedure executed by a command:

cmd.ExecuteNonQuery();

int ErrorCode = 0;

// Note that if you are not sure about your sp, you should test this for dbnull.value first
ErrorCode = Convert.ToInt32(cmd.Parameters["@ErrorCode"].Value);

Upvotes: 0

Adam Rackis
Adam Rackis

Reputation: 83376

If you're calling your sproc via ado.NET, then the SqlParameter you pass to the sproc would be set up like this:

SqlParameter P = new SqlParameter("Name of your column", SqlDbType.Int);
P.Direction = ParameterDirection.Output;

//call your sproc
int result = (int)P.Value;

EDIT

Since you're using Linq-to-SQL, adding this sproc into the methods sections should create a c# method signature for this sproc with the out parameter added for you.

Upvotes: 3

Related Questions