Commands such as INSERT, UPDATE and DELETE do not return data. Use these commands with ExecuteNonQuery method of the Command object. Although you can successfully execute these commands using the ExecuteReader method, the .NET Data Provider will properly optimize the database access for INSERT, UPDATE, and DELETE statements only through the ExecuteNonQuery method.
The following example shows how to insert a row into the employee table using ExecuteNonQuery:
DBConn.Open();
DBTxn = DBConn.BeginTransaction();
// Set the Connection property of the Command object
DBCmd.Connection = DBConn;
// Set the text of the Command to the INSERT statement
DBCmd.CommandText = "INSERT into employee VALUES (15,'HAYES','ADMIN',6, " +
"'17-APR-2002',18000,NULL,4)";
// Set the transaction property of the Command object
DBCmd.Transaction = DBTxn;
// Execute the statement with ExecuteNonQuery, because we are not
// returning results
DBCmd.ExecuteNonQuery();
// Now commit the transaction
DBTxn.Commit();
// Close the connection
DBConn.Close();
Use the ExecuteScalar method of the Command object to return a single value, such as a sum or a count, from the database. The ExecuteScalar method returns only the value of the first column of the first row of the result set. Once again, you could use the ExecuteReader method to successfully execute such queries, but by using the ExecuteScalar method, you tell the .NET data provider to optimize for a result set that consists of a single row and a single column. By doing so, the data provider can avoid a lot of overhead and improve performance. The following example shows how to retrieve the count of a group:
// Retrieve the number of employees who make more than $50000
// from the employee table
// Open connection to Sybase database
OpenAccessConnection Conn;
Conn = new OpenAccessConnection("host=norman;Port=19996;
User ID=test01;Password=test01;");
Conn.Open();
// Make a command object
OpenAccessCommand salCmd = new OpenAccessCommand("SELECT count(sal) FROM" +
