CS 112D Lecture Notes - The Command Classes (Chapter 4 part 2)

Collections

• Collection - a standard VB class for an ordered set of items that can be referred to as a unit. (Similar to an array)
• A Collection can store a heterogeneous set (elements can be of different data types, unlike with arrays)
• Add - method to add an item to a Collection
• Remove - method to delete an item from a Collection
• To loop (iterate) through each item in a Collection, use a For Each loop
• Here's an example which will output "HelloGoodbye" in a Message Box:

      Dim MyCollection As New Collection()
      MyCollection.Add("Hello")
      MyCollection.Add("Goodbye")
      Dim oneInst As String
      Dim AnswerString As String = ""
      For Each oneInst In MyCollection
          AnswerString = AnswerString & oneInst
      Next
      MsgBox(AnswerString)
    

The Command Classes

• CommandText - property to set the text of the command (e.g. the query text)
• CommandType - property to set what type of command this is. Values can be CommandType.Text (SQL code) or CommandType.StoredProcedure
• Connection - property to set the Connection object to be used.
• Parameters - property to store a Collection of Parameter objects, each of which stores data types, values, etc. of parameters.
  • Parameter objects and Collections of them are different for different Data Providers:
  • SqlParameterCollection is based on named parameters (such as "@state")
  • OledbParameterCollection and OdbcParameterCollection are based on positional parameters (like functions in programming, where the order of parameters in a function call specifies which is which). "?" is used in the command text for parameter placeholders.
  • Data types for parameters should be Data Provider-specific, e.g. SqlDbType.VarChar
  • Parameter Direction can be ParameterDirection.Input (for query parameters), ParameterDirection.ReturnValue (for results), among others.

Executing a Command

• Commands can be executed in one of four ways:
  • ExecuteNonQuery - Executes a SQL command that doesn't return any records.
  • ExecuteScalar - Executes a SQL command and returns the first column of the first row. (Used when query is used to get one value like a count or ID.)
  • ExecuteReader - Executes a SQL command and returns the resulting set of records in a DataReader object.
  • ExecuteXmlReader - Available to SqlCommand objects only. Returns records as XML via a XmlReader object. We won't discuss these now.
• ExecuteNonQuery() This function can be used to execute a query that modifies a database or the data within it. It returns a single integer value indicating the success or failure of the query. 0 indicates failure, -1 means a DDL or SELECT command succeeded, and a positive integer indicates the number of rows affected by a INSERT, UPDATE, or DELETE query.
• ExecuteNonQuery() can also be used with Parameters, both for input and for output. Using an output parameter with this method is more efficient than ExecuteScalar.
• ExecuteScalar() This function returns the value contained in the first record of the first row returned.
• ExecuteReader() This function returns a DataReader that gives access to the query results in a forward-only, read-only manner.
• For examples of these commands, see my Command Examples project

Transactions

• Transaction - a standard database term referring to the ability to perform a group of operations in an "all-or-nothing" manner.
• Commit - make a transaction and all its operations final
• Rollback - undo all operations made as part of a transaction
• There is a Transaction class for each of the 4 Data Providers, e.g. SqlTransaction
• BeginTransaction() - method of a Connection object to start a transaction. Returns a Transaction object. Should be called after opening the connection.
• The Command class has a property called Transaction which stores a reference to a Transaction object. Setting this property makes the command part of the transaction.
• Rollback() - method of a Transaction object to undo/cancel its commands.
• Commit() - method of a Transaction object to make its commands permanent.
• Attempting to Rollback or Commit a transaction in which nothing was executed will cause a runtime exception. Such an exception can be handled if it's in a "Try" block with "Catch" following.

Return to the main CS 112D page