Create the Select All Command Method

Purpose:

To create a method that will return a SqlCommand object that will be used to retrieve all of the records from a table.

Starting Point:

You have VisualStudio.NET open.  You are creating the RegionHelper command class in the Data Access project.  The code generated by the Data Adapter Wizard while it was generating the stored procedures for the Region table should be readily available.  The Select Command method for the class should be complete.

Steps:                                                

  1. Copy the method ‘theSelectCommand’ that you completed earlier, and paste the copy in just below the original.
  2. Change the name of the new method to ‘theSelectAllCommand’.
  3. Eliminate the parameter declaration in your new method. 
  4. Change the CommandText property of the SqlCommand object to ‘RegionsSelectCommand’.  (Recall that this is what we named our ‘Select All’ stored procedures.)
  5. Eliminate the two lines of code that make reference to the stored procedure’s ‘@RegionID’ parameter.  (The only parameter this stored procedure requires is the standard ‘@RETURN_VALUE’ parameter.)
  6. That should be all you need to do.  The code for this method should now look something like this:

    public SqlCommand theSelectAllCommand()
    {
          SqlCommand thisSqlCommand = new SqlCommand();
          thisSqlCommand.CommandText = "[RegionsSelectCommand]";
       thisSqlCommand.CommandType = CommandType.StoredProcedure;
       thisSqlCommand.Connection = new SqlConnection(Database.CONNECTION_STRING);
       thisSqlCommand.Parameters.Add(new SqlParameter("@RETURN_VALUE", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, ((System.Byte)(0)), ((System.Byte)(0)), "", DataRowVersion.Current, null));
          return thisSqlCommand;
    }
  7. Compile and save.

Rationale:

This method is very easy to create from scratch, and this process makes it even easier.

Discussion:

Note that this method follows exactly the same pattern as the previous command method: declare a command object, configure it, return it to the caller.  This pattern is constant.

This is the simplest a command procedure can be.  In fact, you will soon notice that all of the command procedures that have no input parameters and return a dataset look almost exactly like this.  Only the name of the stored procedure (the CommandText property) will change from one such method to another.  How would you exploit that fact to get those command objects as quickly as possible?

Previous Step: Create the Select Command Method

Next Step: Create the Select All Command Method