While using the C# code to delete rows in SQL Server, the affect rows amount returned is 2. But there is only one item in the table. Here is the code.

            int result = -1;
            using (SqlConnection sqlConnection = new SqlConnection(AppConfiguration.ConnectionStringIguide))
            {
                string sql = string.Format("delete from atblOrders where OrderID='{0}'", orderId);
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = sqlConnection;
                    sqlCommand.CommandText = sql;
                    sqlCommand.CommandType = CommandType.Text;
                    sqlConnection.Open();
                    result = sqlCommand.ExecuteNonQuery();
                    sqlConnection.Close();
                }
            }

I copy the SQL into SQL Server Management Studio and run the SQL. It prints out two lines of 1 rows affected.

(1 rows affected) (1 rows affected) Completion time: 2021-12-13T13:53:52.0466180 08:00

If I use select query with the same id, it only returns one item. So, why there is two rows affected while deleting?

CodePudding user response：

As mentioned in the comments, this is most likely because there's a trigger (either for or instead of) delete defined for this table.

However, this is not the main problem in your code. The main problem in your code is that it's vulnerable to SQL injection attacks since you're concatenating strings into your SQL instead of using parameters.

For more information, you can read my blog post entitled Back to basics: SQL Injection, and Sandy's question and it's answers here on stackoverflow - Why do we always prefer using parameters in SQL statements?