sqlCommand and programatically retrieving and setting parameters of stored procedures

Question

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.

 // 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.

Have you tried DbNull.Value instead of null?

Rytmis
– Rytmis

2012-01-27 22:59:10 +00:00
Commented Jan 27, 2012 at 22:59 — Rytmis
– Rytmis, Commented Jan 27, 2012 at 22:59
Good call, thanks. (See my response below.)

DuncanMack
– DuncanMack

2012-01-29 18:14:53 +00:00
Commented Jan 29, 2012 at 18:14 — DuncanMack
– DuncanMack, Commented Jan 29, 2012 at 18:14

Eric H · Accepted Answer · 2012-01-27 23:07:28Z

2

Use DBNull.Value instead of null.

answered Jan 27, 2012 at 23:07

Eric H

1,7891 gold badge11 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DuncanMack Over a year ago

Ah, bingo. I was thinking my syntax (or approach) was wrong and it didn't occur to me that my null value might not, in fact, be null.

Collectives™ on Stack Overflow

sqlCommand and programatically retrieving and setting parameters of stored procedures

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related