I'm facing this issue today. I made an API with .NET Core and I created a function to insert records into a SQL Server database.
SqlCommand.ExecuteNonQuery returns 1, but when I make a SELECT directly from SQL Server Management Studio, that returns 0 rows. To be precise, SELECT returns only the rows inserted manually with SQL Server Management Studio.
If I try to insert another row from SQL Server Management Studio, I can see that the primary key (ID autoincrement) is increasing even if I don't have that row.
My code:
public async Task<bool> InsertAuditTrail(AuditTrailModel auditTrail)
{
bool result = false;
string commandText = String.Concat(@"INSERT INTO [dbo].[AuditTrail] ",
"([NomeApplicazione],[N_Applicazione],[N_Record],[NomeTabella],[NomeCampo],[Utente],[Data],[Insert],[Delete],[Update],[DatoPrecedente],[DatoAggiornato]) ",
"VALUES (@NomeApplicazione, @N_Applicazione, @N_Record, @NomeTabella, @NomeCampo, @Utente, @Data, @Insert, @Delete, @Update, @DatoPrecedente, @DatoAggiornato)");
using (var connection = new SqlConnection(connStr))
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
using (var command = new SqlCommand(commandText, connection, transaction))
{
try
{
command.CommandType = CommandType.Text;
command.Parameters.AddWithValue("@NomeApplicazione", auditTrail.NomeApplicazione);
command.Parameters.AddWithValue("@N_Applicazione", auditTrail.N_Applicazione);
command.Parameters.AddWithValue("@N_Record", auditTrail.N_Record);
command.Parameters.AddWithValue("@NomeTabella", auditTrail.NomeTabella);
command.Parameters.AddWithValue("@NomeCampo", auditTrail.NomeCampo);
command.Parameters.AddWithValue("@Utente", auditTrail.Utente);
command.Parameters.AddWithValue("@Data", auditTrail.Data);
command.Parameters.AddWithValue("@Insert", auditTrail.Insert);
command.Parameters.AddWithValue("@Delete", auditTrail.Delete);
command.Parameters.AddWithValue("@Update", auditTrail.Update);
command.Parameters.AddWithValue("@DatoPrecedente", auditTrail.DatoPrecedente);
command.Parameters.AddWithValue("@DatoAggiornato", auditTrail.DatoAggiornato);
result = command.ExecuteNonQuery() > 0 ? true : false;
}
catch (Exception Ex)
{
await connection.CloseAsync();
errorMsg = Ex.Message.ToString();
}
}
}
}
return result;
}
