OleDbCommand: ExecuteNonQuery, ExecuteReader, ExecuteScalar
Before we start I just wanted to clarify several things: I don't use Oledb namespace in my everyday work. Working with MSSQL I use SqlClient. Using SqlCommand object I never (and really don't) understand actually why we need SqlCommand's ExecuteScalar function in order to output a single query value, it is far more efficient to use ExecuteNonQuery along with procedure with output parameter instead (IMHO).
In any case -- here I want to speak especially about OledbCommand. Speaking of which it has the same functions as SqlCommand has but the implementations are very different from one to another. We can use Oledb namespace dealing with all databases (It is much faster to use SqlClient when we have to deal with MSSQL 7.0 and higher but still...).
OledbCommand, oh man -- when we look through the source code it turns out that ExecuteReader and ExecuteScalar are actually the same functions - I told you -- don't use ExecuteScalar:
public object ExecuteScalar()
{
OleDbConnection.OleDbPermission.Demand();
object obj1 = null;
try
{
this.executeQuery = true;
using (OleDbDataReader reader1 =
this.ExecuteReaderInternal(CommandBehavior.Default, "ExecuteScalar"))
{
if (reader1.Read() && (0 < reader1.FieldCount))
{
obj1 = reader1.GetValue(0);
}
return obj1;
}
}
catch
{
throw;
}
return obj1;
}
and ExecuteReader:
public OleDbDataReader ExecuteReader(CommandBehavior behavior)
{
OleDbDataReader reader1;
OleDbConnection.OleDbPermission.Demand();
this.executeQuery = true;
try
{
reader1 = this.ExecuteReaderInternal(behavior, "ExecuteReader");
}
catch
{
throw;
}
return reader1;
}
You can see -- they both call ExecuteReaderInternal (no magic), ExecuteScalar just fetches the first field from reader inside itself and it looks a bit ugly vs. ExecuteNonQuery. Despite this fact one can learn a lot about how to use using directive and validate if the reader is not empty from this piece of code.
You can notice though that OledbCommand.ExecuteNonQuery looks also very alike ExecuteReader:
public int ExecuteNonQuery()
{
OleDbConnection.OleDbPermission.Demand();
this.executeQuery = false;
try
{
this.ExecuteReaderInternal(CommandBehavior.Default, "ExecuteNonQuery");
}
catch
{
throw;
}
return this.recordsAffected;
}
The difference is in executeQuery property. Being set to false we don't go through the huge piece of code inside ExecuteReaderInternal.
Friday, April 15, 2005 5:38 PM
