October 13, 2002 - The SqlCommand Class | WebReference

October 13, 2002 - The SqlCommand Class

Yehuda Shiran October 13, 2002
The SqlCommand Class
Tips: October 2002

Yehuda Shiran, Ph.D.
Doc JavaScript

IBuySpy Store uses stored procedures to interact with the MSSQL database. Use the SqlCommand class to assemble an instance object with all the information required by the stored procedure. This class cannot be inherited. When created, the SqlCommand object points to the beginning of the database, for both read and write. This class includes four methods: ExecuteReader(), ExecuteNonQuery(), ExecuteScalar(), and ExecuteXmlReader().

ExecuteReader() executes stored procedures that return rows from the database. They are usually stored in an SqlDataReader object. The method GetProductCategories() uses ExecuteReader() to execute the stored procedure ProductCategoryList. 

  public function GetProductCategories() : SqlDataReader {
    var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
    var myCommand : SqlCommand = new SqlCommand("ProductCategoryList", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;
    myConnection.Open();
    var result : SqlDataReader  = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
    return result;
  }
ExecuteNonQuery() executes SQL queries such as INSERT, DELETE, UPDATE, and SET. Run ExecuteNonQuery() when you pass back information from the stored procedure through parameters as opposed to records. The GetProductDetail() method uses ExecuteNonQuery(): 

  .
  .
  myConnection.Open();
  myCommand.ExecuteNonQuery();
  myConnection.Close();
  .
  .
ExecuteScalar() retrieves a single value (for example, an aggregate value) from a database. ExecuteXmlReader() sends the CommandText to the Connection and builds an XmlReader object. The latter two methods are not used in IBuySpy.