Get affected rows on ExecuteNonQuery

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?

ExecuteNonQuery - returns the number of rows affected.

SqlCommand comm;
// other codes
int numberOfRecords = comm.ExecuteNonQuery();

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.

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

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

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);
            }
        }

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;