c# - Rollback transaction when found error in datagridview

Question

I have trying to update a table on clicking of a button and the data is inside the datagridview its does update the table but when it finds an error it should rollback the whole transaction where i am failing to do so. I have a stored procedure written in ssms. Here's the SSMS Code:

ALTER PROCEDURE UpdateSaleWarehouse 
(@color nvarchar(100)
,@size nvarchar(100)
,@quality nvarchar(100)
,@lot nvarchar(100)
,@balanceQty nvarchar(100)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @trancount int;
SET @trancount = @@trancount;

BEGIN TRY
 -- add transaction
 IF @trancount = 0
    BEGIN TRANSACTION
     ELSE
     SAVE TRANSACTION UpdateSaleWarehouse;
        --business logic goes here
        Update Warehouse
        SET Balance_Quantity =Balance_Quantity-@balanceQty
        WHERE Color=@color AND Size=@size AND Quality=@quality AND Lot=@lot AND Balance_Quantity>@balanceQty
        
     if @trancount = 0
     COMMIT;
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION UpdateSaleWarehouse;
END CATCH
END
GO

AND The code written in visual studio

for (int i = 0; i < dg_InvoiceProductDetails.Rows.Count - 1; i++)
        {
            using (SqlConnection conW = new SqlConnection(conPInvoice))
            {
                conW.Open();
                SqlTransaction tran = conW.BeginTransaction();
                try
                {
                    using (SqlCommand cmd = new SqlCommand("UpdateSaleWarehouse", conW, tran))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.AddWithValue("@color", Convert.ToString(dg_InvoiceProductDetails.Rows[i].Cells[2].Value));
                        cmd.Parameters.AddWithValue("@size", Convert.ToString(dg_InvoiceProductDetails.Rows[i].Cells[4].Value));
                        cmd.Parameters.AddWithValue("@quality", Convert.ToString(dg_InvoiceProductDetails.Rows[i].Cells[6].Value));
                        cmd.Parameters.AddWithValue("@lot", Convert.ToString(dg_InvoiceProductDetails.Rows[i].Cells[7].Value));
                        cmd.Parameters.AddWithValue("@balanceQty", Convert.ToString(dg_InvoiceProductDetails.Rows[i].Cells[8].Value));
                        cmd.ExecuteNonQuery();
                        tran.Commit();
                    }
                    
                }
                catch (Exception ex)
                {
                    try
                    {
                        tran.Rollback();
                    }
                    catch (Exception exRollBack)
                    {
                        Console.WriteLine(exRollBack.Message);
                    }
                }
            }
        }

Answer 1

等待大神答复