Wrong results after Database Commit using C# and SQL

Question

All our CRUD operations have a CommitTransaction() at the end before returning the results of a Method. For instance, we have a method Create() that creates a record if there is no existing record with a Status: 'Float'. Otherwise, it selects the record and updates its contents.

This is working as checked in the Database. The problem is, the method is returning its previous data and not the updated one:

public class Sample
{
    public int ID {get; set;}
    public string Code {get;set;}
    public decimal Total {get; set;}
}

In Create(), Code and Total is entered by the user which should update the data in the database.

E.g.

ID | Code     | Total
1  | CodeName | 100

The user updates

Code  : 'Code1'
Total : 200

But the Method still returns

Code  : 'CodeName '
Total : 100

But if checked in the Database, it is already

ID | Code     | Total
1  | Code1    | 200

I have observed that this only happens when a CommitTransaction() is used which is not advisable to remove but a correct data return is also needed.

Here is a simplified replica of Create():

private string procCRUD = "procCreate @ID={0},@Code={1},@Total={2}";

public Sample Create(Sample data)
{
  IQueryable<ComponentType> query = contextBase.Samples.FromSql(procCRUD,"create",data.ID, data.Code, data.Total); // contextBase.Samples is the DbContext

  commit(); // this commits the transaction

  return query;
}

procCRUD is a stored procedure:

DROP PROCEDURE IF EXISTS procCreate
GO

CREATE PROCEDURE procCreate
    @proc                               varchar(20) = 'create',          
    @ID                                 bigint = 0,               
    @Code                               varchar(20)= NULL,      
    @Total                              int= 0   
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL varchar(MAX)
    SET @SQL = ''

    IF (@proc='create') BEGIN
        IF (NOT EXISTS(SELECT ID FROM Sample WHERE Status='Float')) 
        BEGIN
            INSERT INTO Sample(Code, Total) VALUES( @Code, @Total)
            SET @ID = @@IDENTITY;
        END
        ELSE 
        BEGIN
            SELECT @ID=ID FROM Sample WHERE Status='Float'

            UPDATE Sample SET 
                 Code = @Code
                ,Total = @Total
            WHERE ID=@ID
        END
    END

    SET @SQL = 'SELECT TOP 1 * FROM Sample '
    EXEC SP_EXECUTESQL @SQL
END
GO

I have tried manually setting the return value e.g. query.Code = data.Code before the return but it is time-consuming if I have more than 10 Class properties. Also, some properties are not entered by the user but retrieved from the database depends on another property entered.

I wonder if some of you also encountered this problem. Any help will be appreciated. Thank you.

(edited)

We will need to see an example of a full piece of code that reproduces the problem, but you should strive to make it a [mcve] so that it is as small as possible. All we can say right now is that you're probably doing something wrong, but then you probably know that already. — Lasse V. Karlsen, May 18 '20 at 12:20
You are commiting transactions before return the result of a select? Not when finishing the previous operation, not before running the select - after the select? — Christopher, May 18 '20 at 12:41
`This is working` - it isn't actually. Your stored procedure will absolutely insert duplicate `Status='Float'` entries under load. Please research "sql server insert only if not already there". — GSerg, May 18 '20 at 13:05
As for the actual problem, if that value is updated in the database and not returned by whatever ORM framework you are using, then look into how your mapping is set up and when and whether it updates the fields of the objects. — GSerg, May 18 '20 at 13:09
@Christopher I am committing transactions after the Select inside the Stored Procedure. The transaction is committed before the return. — , May 18 '20 at 13:21
@GSerg Thanks for the additional tip. My usual stored procedures are more detailed than provided and as experience, no duplicate records have been inserted using that syntax, rather it successfully updates the existing record. — , May 18 '20 at 13:38
It's interesting that you've **never** marked one of your questions as **answered**. And you have received a lot of correct answers, especially about very basic and important features of C# language. I'm getting the idea that, not only you're not able to reproduce your problems (that is fully evident here), but you're not even learning from truly acknowledging your errors and the tips you received. — , May 19 '20 at 06:44

score 1 · Answer 1 · 2020-05-19T06:33:27.853

You need to better explain what you are trying to do and what is the actual code behind your

contextBase.Samples.FromSql

At least, the following seems to be relevant, based on your (not complete) description.

Copying from base to parent.

I have tried manually setting the return value e.g. query.Code = data.Code before the return but it is time-consuming if I have more than 10 Class properties.

Please read the solution already suggested here. You will end up with defining a Map extension and using

query.Map(data);

The above should resolve your actual problem, but there are other inconsistencies in your question, that might affect the result.

SQL

procCRUD is a stored procedure:

    SET @SQL = 'SELECT TOP 1 * FROM Sample '
    EXEC SP_EXECUTESQL @SQL
END
GO

In the end of your stored procedure you are managing a select to return the correct value, but - as far as I can see - you are missing the correct where condition, something like a WHERE ID=@ID (or Status='Float', I don't know your app logic).

By the way, you should only Commit() after a BeginTransaction, but that is not shown in your question.

score 0 · Answer 2 · answered May 18 '20 at 14:10

Nothing in your description really makes sense. According to you, you:

do a select
commit a transaction
return the values
Wonder that the value returned and the one in the Database do not match?

If you want to retreive values as a Update or Insert operation is running, the only reliable syntax in SQL is OUTPUT: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

If you - for example - want to get teh Primary Key of something you just inserted, OUTPUT Inserted.ID is the only way that will reliably work. Unfortunatily, most non-MSSQL DBMS do not have such a feature.

Wrong results after Database Commit using C# and SQL

2 Answers2

Copying from base to parent.

SQL