title: sqlCommand and programatically retrieving and setting parameters of stored procedures
body: <p>I'm new to .NET but need to swim in the deep end for a bit.  I've been tasked with expanding an existing unit test that currently just attempts to receive the columns for all stored procedures (as a means of testing the integrity of the db connections and the schema therein).  The goal is to retrieve the parameters for all stored procedures and attempt to run them with null values, testing to verify that no record sets are returned.  I've been beating my head against the wall trying to get up to speed with ADO.NET but cannot for the life of me figure out how to do this.  This is as far as I've gotten (taken somewhat out of context).  The test complains that no parameter is being passed in, but I thought I was setting the existing parameter set to null and simply passing it back.</p>
  
  <pre><code> // bind our sql schema gridview
  foreach (SqlSchemaItem item in items)
     {
   // pull a connection
   using (var sqlConnection = new SqlConnection(connectionString))
   {
       // open connection
       sqlConnection.Open();
  
       // get schema
       try
       {
          /*
           * Part 1 - test that the schema is ok...
           */
              var sqlCommand = new SqlCommand(item.ObjectName, sqlConnection);
              sqlCommand.CommandType = CommandType.StoredProcedure;
              SqlCommandBuilder.DeriveParameters(sqlCommand);
              sqlCommand.ExecuteReader(CommandBehavior.SchemaOnly);
  
              // success to console
              Console.WriteLine(item.ObjectName + " is ok.");
  
          /*
           * Part 2 - test that the stored procedure does not return any data.
           */
  
          // set all the parameters to NULL
          foreach (SqlParameter parameter in sqlCommand.Parameters)
          {
              if (parameter.Direction == ParameterDirection.Input || parameter.Direction == ParameterDirection.InputOutput)
              {
                 parameter.Value = null;
                 Console.WriteLine("Parameter {0} set to null", parameter.ParameterName, parameter.Value);
              }
           }
  
           var temp = sqlCommand.ExecuteReader(CommandBehavior.SingleResult);
           if (temp.HasRows) {
               Console.WriteLine(string.Format("A record was returned in {0} with value {0}", temp.GetName(0), temp.GetString(0)));
           }
           else {
              Console.WriteLine("No result was returned");
            }
           Console.WriteLine(" ");
           }
      catch ...
      finally ...
      etc.
  </code></pre>