Build command objects

DbCommand is the object which plays the same role in working with data sources as DbConnection does in connecting to them. Everything that’s related to dealing with data can be done by using command objects, like selecting, inserting, deleting or updating.

As you could build connection objects with DbConnectionBuilder, you can do the same with DbCommand and DbCommandBuilder. But let’s talk about SqlCommandBuilder, instead of DbCommandBuilder.

You can pass a SqlDataAdapter object into the constructor. This is particularly useful if you’d like to generate automatic texts for the insert, delete and update commands, depending on the select command you specified. Consider the following code:

SqlConnection theConnection = new SqlConnection(“connectionstring”);
SqlDataAdapter theAdapter = new SqlDataAdapter(“SELECT * FROM Customers”, theConnection);
SqlCommandBuilder theBuilder = new SqlCommandBuilder(theAdapter);

And after this code, you are able to call the GetUpdate/Delete, etc command methods, which returns a pregenerated, strongly typed SqlCommand object depending on the conflict option and the SELECT command specified.

It is possible to execute commands in different ways. There are four methods you should be familiar with:

  • ExecuteScalar: executes the command, then returns the first column in the first row of the result set. Ideal for aggregate functions, such as SUM, MIN, MAX, COUNT…
  • ExecuteNonQuery: executes the command, and returns the number of rows affected. An ideal candidate for INSERT and UPDATE commands.
  • ExecuteReader: executes the command, and returns an instance of DbDataReader, which is a forward-only, read-only cursor. Has one overload, in which you can pass a member of the CommandBehvaior enumeration. CommandBehavior is introduced right after this list.
  • ExecuteXmlReader: builds an XmlReader object on the result.

As I promised, the CommandBehavior enumeration:

  • CloseConnection: when the returned DataReader is closed, the connection is automatically closed too.
  • Default: multiple result sets allowed, database can be affected. You shouldn’t pass this member explicitly, since calling the ExecuteReader method without parameters is equivalent.
  •  KeyInfo: query returns column and primary keys information.
  • SchemaOnly: query returns column information only.
  • SequentialAccess: lets the DataReader process large binary data. You can use the GetBytes or GetChars of the DataReader when using this mode.
  • SingleResult: the query returns a single result set.
  • SingleRow: the query returns a single row, with improved performance.

One often omitted setting of the DbCommand object is the CommandType. It can take a member of the CommandType enumeration, with a quite straightforward set of members, which have speaking names: StoredProcedure, TableDirect, Text. You should set it to the appropriate value before you call the execute method.

Finally let’s talk about parameters a bit. The first rule, which should be carved into stone is that you should never, never ever construct SQL commands with string concatenation, because if you’d do so, you’re application is doomed to be abused. Instead, you should build parameterized SQL commands. SqlCommand exposes a strongly typed SqlParameter collection property called Parameters. You should add your parameters to this collection, by calling the Add or AddWithValue methods. The use of these parameters are quite straightforward.

Since parameters can be bidirectional, you should explicitly specify your parameter’s direction by setting the same-named property to a member of the ParameterDirection enumeration. The possible values are: Input, Output, InputOutput, ReturnValue. I think you can figure out when to use which.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s