To add some details, if you're using a stored procedure then setting the
NOCOUNT
option on causes this effect. However, it does not explain why no data is found so as pointed out you either add no data or it is rolled back.
Also note that the rollback is not necessarily done on the server side
To test different variations for stored procedure behaviour consider the following scenario
Test table
CREATE TABLE NoCountTable (
col1 int
);
GO
Test procedures
CREATE PROCEDURE NoCountProcedure AS
BEGIN
SET NOCOUNT ON;
INSERT INTO NoCountTable VALUES (1);
END;
GO
CREATE PROCEDURE CountProcedure AS
BEGIN
SET NOCOUNT OFF;
INSERT INTO NoCountTable VALUES (1);
END;
GO
CREATE PROCEDURE RollbackProcedure AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
INSERT INTO NoCountTable VALUES (1);
ROLLBACK;
END;
GO
CREATE PROCEDURE ExceptionProcedure AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION
INSERT INTO NoCountTable VALUES (1/0);
ROLLBACK;
END;
GO
Calling program
int result;
using (SqlConnection connection = new SqlConnection()) {
connection.ConnectionString = "server=.\\<instance_name_here>;integrated security=true";
using (SqlCommand command = new SqlCommand()) {
command.Connection = connection;
try {
connection.Open();
command.CommandText = "NoCountProcedure";
result = command.ExecuteNonQuery();
Debug.WriteLine(string.Format("{0} returned {1}",
command.CommandText, result));
command.CommandText = "CountProcedure";
result = command.ExecuteNonQuery();
Debug.WriteLine(string.Format("{0} returned {1}",
command.CommandText, result));
command.CommandText = "RollbackProcedure";
result = command.ExecuteNonQuery();
Debug.WriteLine(string.Format("{0} returned {1}",
command.CommandText, result));
command.CommandText = "ExceptionProcedure";
result = command.ExecuteNonQuery();
Debug.WriteLine(string.Format("{0} returned {1}",
command.CommandText, result));
} catch (System.Exception exception) {
Debug.WriteLine(string.Format("Exception occurred {0}: {1}",
exception.Source, exception.Message));
}
}
}
The output from the program is
NoCountProcedure returned -1
CountProcedure returned 1
RollbackProcedure returned -1
Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.dll
Exception occurred .Net SqlClient Data Provider: Divide by zero error encountered.
The statement has been terminated.
But as pointed out by @Pete-OHanlon, this is merely explaining different possibilities since you haven't posted any code.