64

I am currently working on a C# project and I am running an insert query which also does a select at the same time, e.g.:

INSERT INTO table (SELECT * FROM table WHERE column=date)

Is there a way I can see how many rows were inserted during this query?

BartoszKP
  • 34,786
  • 15
  • 102
  • 130
Boardy
  • 35,417
  • 104
  • 256
  • 447

6 Answers6

121

ExecuteNonQuery - returns the number of rows affected.

SqlCommand comm;
// other codes
int numberOfRecords = comm.ExecuteNonQuery();
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 3
    I'm doing this, and for some reason even though a new row was created in my table, this methods returns -1. Should I also have something in my sql statement? – JoeManiaci Feb 03 '16 at 21:46
  • 3
    SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete See https://blogs.msdn.microsoft.com/spike/2009/01/27/sqlcommand-executenonquery-returns-1-when-doing-insert-update-delete/ – Manuel Alves Dec 28 '16 at 16:30
  • 2
    This does not always work as one might expect. For an UPDATE the number of returned rows is the number of rows that *could* have been changed rather than the number of rows that were actually changed. Let's say you're doing an UPDATE where up to 50 rows might be affected. However for 35 of those rows the UPDATE doesn't cause any data to be changed. The data changes for only 15 rows of the 50 possible rows. In this case one would hope that "15" would be the returned value but instead the returned value is 50, the total number of rows. – techjp May 18 '17 at 15:11
  • 1
    @Ian: so is there a way to get the 15? – serv-inc Sep 02 '17 at 11:05
  • 2
    @serv-inc, I don't think there is a way to get "15" for an UPDATE with a single line of code. You would need to execute a SELECT count(*) statement separately *before* the update is run to find out how many rows are going to be updated. As long as your tables are properly indexed this won't add any noticeable additional overhead. – techjp Sep 05 '17 at 16:15
  • 3
    @serv-inc: Another option is to make sure that the number of potentially affected rows is the same as the number of actually affected rows. So rather than doing "UPDATE table SET val1 = 5;" use "UPDATE table SET val1 = 5 WHERE val1 <> 5;". – techjp Sep 05 '17 at 17:02
  • 1
    @Ian It should be pointed out that that is how any SQL RDBMS behaves, not some behavior unique to the SqlClient class or SQL Server. If you tell an RDBMS to update a value to the value already present, the RDBMS will still do all the relevant work, obtaining locks on the rows, filling your transaction log, modifying relevant indices, firing relevant triggers, etc. It's entirely up to the user (aka, the application developer) to not specify redundant updates if they don't want to perform redundant updates. – Bacon Bits Nov 21 '18 at 16:42
17

If you run the SQL from your question in a SqlCommand and check the return value of ExecuteNonQuery it should tell you how many records were affected.

From the documentation:

Return Value
Type: System.Int32
The number of rows affected.

M.Babcock
  • 18,753
  • 6
  • 54
  • 84
7

Be sure of one thing also You need to add a statement in the connection string For example:

string const "Server=localhost; PORT=3306; Database=db; User id=root; password='';UseAffectedRows=True";
MySqlConnection con = new MySqlConnection(const);
con.Open();
MySqlCommand cmd = new MySqlCommand(con);
cmd.CommandText = "Update db set table = value where Column = value";
int numberOfRecords = cmd.ExecuteNonQuery();

Be sure of:

UseAffectedRows=True

so it will return a right value of rows affected

Ahmad Al-Ghazali
  • 71
  • 1
  • 1
2

ExecuteNonQuery return the affected rows ONLY WHEN Use Affected Rows in the connections properties is set, if not (default) returns matched rows.

0

If you run a bulk of ExecuteNonQuery(), and commit them all in once, you can get the number of total changes after connection by read the return value from "SELECT total_changes();"

The function to get the total changes:

public static long GetTotalChanges(SQLiteConnection m_dbConnection)
        {
            string sql = "SELECT total_changes();";
            using (SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection))
            {
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    reader.Read();
                    return (long)reader[0];
                }
            }
        }

Use it in another function:

public static long MyBulkInserts()
        {
            using (SQLiteConnection m_dbConnection = new SQLiteConnection())
            {
                m_dbConnection.Open();
                using (var cmd = new SQLiteCommand(m_dbConnection))
                {
                    using (var transaction = m_dbConnection.BeginTransaction())
                    {
                        //loop of bulk inserts
                        {
                            cmd.ExecuteNonQuery();
                        }
                        transaction.Commit();
                    }
                }
                return GetTotalChanges(m_dbConnection);
            }
        }
Feng Jiang
  • 1,776
  • 19
  • 25
0

I realize you are trying to do this with the ExecuteNonquery, but what about ExecuteScalar and using the OUTPUT directive in your query?

For Insert:

declare @resulttable
(
  rowid int
)
insert yourtable
output inserted.rowid
  into @resulttable
select *
from someothertable

select count(1) affectedrows
from @resulttable

or for Update, if you only want to know the rows that changed

declare @resulttable
(
  beforefield1 varchar(255),
  afterfield1 varchar(255)
)
update tbl1
set field1 = replace(field1, 'oldstring', 'newstring')
output deleted.field1,
    inserted.field1
  into @resulttable
from someothertable

select count(1) affectedrows
from @resulttable
where beforefield1 != afterfield1;
john_roa
  • 1