ExecuteNonQuery

1. Method Overview

SqlCommand.ExecuteNonQuery()

Applicable Scenarios: INSERT (Create), UPDATE (Modify), DELETE (Remove). Do NOT use it for SELECT queries
Return Value: int type, representing the number of rows affected after SQL execution

How it works: The database returns the number of changed rows after running DML statements. Your application reads this number to verify whether the operation took effect.

Business Logic Based on Return Values

1. INSERT: Successful insertion → returns 1; failure throws exceptions;
2. UPDATE:

• Matching records found and updated → returns ≥1
• No records match the WHERE condition → returns 0 (statement runs without errors, yet zero rows get updated)

1. DELETE:

• Matching records deleted → returns ≥1
• No matching records → returns 0

Key distinction: Returning 0 is NOT equivalent to an application‑level error. The SQL syntax executes normally; simply no qualifying rows have been altered.

INSERT Example For New Records

private void button2_Click(object sender, EventArgs e)
{
    string connectionString = "Data Source=.;Initial Catalog=db;Integrated Security=True";
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand sqlCom = new SqlCommand();
        sqlCom.Connection = conn;
        // Parameterized SQL to prevent SQL injection
        sqlCom.CommandText = "INSERT INTO [dbo].[Article]([Title]) VALUES(@Title)";
        sqlCom.CommandTimeout = 60;

        SqlParameter param = new SqlParameter("@Title", SqlDbType.NVarChar, 250);
        param.Value = "This is a new title" + DateTime.Now;
        param.Direction = ParameterDirection.Input;
        sqlCom.Parameters.Add(param);

        int res = sqlCom.ExecuteNonQuery();
        MessageBox.Show(res.ToString()); 
        // res equals 1 when insertion succeeds
    }
}Code language: JavaScript (javascript)

Advantage: Parameterized queries eliminate SQL injection risks caused by raw string concatenation.

UPDATE Modification Example

conn.Open();
SqlCommand sqlCom = new SqlCommand();
sqlCom.Connection = conn;
sqlCom.CommandText = "UPDATE [dbo].[Article] SET [Title] = @Title WHERE id = @id";
sqlCom.CommandTimeout = 60;

SqlParameter param = new SqlParameter("@Title", SqlDbType.NVarChar, 250);
param.Value = "This is a new title"+DateTime.Now;
param.Direction = ParameterDirection.Input;
sqlCom.Parameters.Add(param);

// Two equivalent approaches for adding parameters
// Approach 1: Instantiate then append
SqlParameter param2 = new SqlParameter("@id", SqlDbType.Int);
param2.Value = 5;
sqlCom.Parameters.Add(param2);

// Approach 2: Fluent inline initialization (commented in snippet above)
//sqlCom.Parameters.Add(new SqlParameter("@id", SqlDbType.Int){Value=5});

int res = sqlCom.ExecuteNonQuery();
MessageBox.Show(res.ToString());Code language: JavaScript (javascript)

• Row with id=5 exists in table → res=1
• No row matches id=5 → res=0
  Sample business check:

if(res > 0)
{
    MessageBox.Show("Update completed");
}
else
{
    MessageBox.Show("Target record not found, nothing updated");
}Code language: JavaScript (javascript)

Important Notes

1. Do not rely solely on absence of exceptions to confirm business success
   A lack of SQL errors does not guarantee data changes. For example you may get return value 0 when filter criteria match zero rows. Always validate against affected row count.
2. Avoid SELECT statements with ExecuteNonQuery
   It will only return -1 for SELECT calls and cannot fetch dataset results. Use ExecuteReader() / ExecuteScalar() for data retrieval.
3. Benefits of the using statement
   using(SqlConnection conn) automatically disposes database connections after execution and prevents connection leaks.
4. Exact parameter‑name matching required
   Names like @Title and @id inside SQL must exactly match corresponding SqlParameter definitions. Case‑insensitive, but naming cannot differ.

Comparison of Three SqlCommand Methods

Method	Purpose	Return Value
ExecuteNonQuery	Insert / Update / Delete (INSERT/UPDATE/DELETE)	int Number of affected rows
ExecuteScalar	Single‑row single‑column queries (COUNT, retrieve auto‑increment ID etc.)	object Value from first row and first column
ExecuteReader	Multi‑row multi‑column SELECT queries	SqlDataReader data stream

ExecuteNonQuery