CODEWITHSUNDEEP
c#sql-serverasp.net-web-api
Reece Russell
Reece Russell

Reputation: 366

Data Doesn't Save In MSSQL

I have a .NET Web API (C#) where I can GET and POST data (Obviously). When I make a GET request, it returns all the data which it is supposed to. I have made my methods/functions to return a bool if a sql command worked. So when I make a PUT or POST request it returns true (which means it worked).

Then if I make a GET request or if I SELECT all in SQL Management Studio, the data hasn't saved. However Delete works.

This did work but stopped, all of a sudden.

This is my Add/POST method:

    public bool Add(string id, byte[] bytes)
    {
        SqlCommand Command = new SqlCommand("INSERT INTO [ag_Images] ([Id], [Data]) VALUES (@Id, @Data)");
        Command.Parameters.AddWithValue("@Id", id);
        Command.Parameters.AddWithValue("@Data", bytes);
        return _Sql.Execute(Command);
    }

Which passes a SqlCommand to:

    SqlTransaction _Transaction;

    public bool Execute(SqlCommand Command)
    {
        SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["AptestDB"].ConnectionString);
        using (conn)
        {
            try
            {
                conn.Open();
                _Transaction = conn.BeginTransaction("SQL_Execution");
                Command.CommandType = CommandType.Text;
                Command.Connection = conn;
                Command.Transaction = _Transaction;
                try
                {
                    Command.ExecuteNonQuery();
                    _Transaction.Commit();
                    return true;
                }
                catch
                {
                    _Transaction.Rollback();
                    return false;
                }
            }
            catch
            {
                return false;
            }

        }
    }

The above doesn't work. However, this does (SQL Management Studio)

DECLARE @Id VARCHAR(50)
DECLARE @Data VARBINARY(MAX)

SET @Id = '00000000-0000-0000-0000-000000000000'
SET @Data = CAST('' AS VARBINARY)

UPDATE [ag_Images] SET [Data] = @Data WHERE [Id] = @Id

UPDATE

I have removed the SqlTransaction, so far things are working.

Upvotes: 1

Views: 132

Answers (1)

Alexandru Clonțea
Alexandru Clonțea

Reputation: 1886

The saving method can return true even if the insert operation did not succeed - ExecuteNonQuery value can be -1 under certain conditions (i.e. triggers, constraints), which can mean (unless NOCOUNT is on, in which case the return value is always -1) that no rows were affected/inserted.

Modify the code to not lie to you anymore:

var rowsInserted = Command.ExecuteNonQuery();
if (rowsInserted > 0)
{
    _Transaction.Commit();
    return true;
}
else
{
    _Transaction.Rollback();
    return false;
}

or

var rowsInserted = Command.ExecuteNonQuery();
_Transaction.Commit(); //with -1 result, no changes are done anyway
return rowsInserted > 0;

If it were me:

  • I would check with a SQL Profiler that the command actually reaches the SQL server.
  • I would trace/log the result of ExecuteNonQuery and try to correlate that with other activities. If it were a timing issue...
  • When you say debugging, you mean with all Managed Debug Assistants activated?

UPDATE If removing the transaction is not satisfactory for you later on, I noticed another issue with the initial code. If removing it works, I now suspect that the actual issue was with transactions left hanging because of object reference being overwritten in the Execute method call:

_Transaction = conn.BeginTransaction("SQL_Execution");

If there are 2 or more threads competing for this, trouble is ensured.

Why not declare the Transaction in the method itself:

var _Transaction = conn.BeginTransaction("SQL_Execution");

Upvotes: 1

Related Questions