none
Which one of the following does SqlCommand require in order to execute a stored procedure? RRS feed

  • Question

  • Hi all,
    I came across a question in the link

    Which one of the following does SqlCommand require in order to execute a stored procedure?

    a. Setting the value of the SqlCommand object's ExecuteReader property
    b. An open SqlConnection object 
    c. A call to the SqlCommand object's ExecuteReader method 
    d. An open OleDbConnection object
    e. An instantiated StoredProc object

    The correct answer its showing is e.
    Can someone please explain what it means... especially about StoredProc object?
    Monday, November 30, 2009 6:46 PM

Answers

  • In a database a stored procedure, a view, a table, a trigger, ...etc are all defined as "objects". They are "database objects". The "instantiated StoredProc object" is to about the database object and not client side .NET object. You can not "instantiate" a stored procedure. It is a poor choice of words. But a stored procedure can be created, compiled and saved. The execute will call a compiled version of the "database object".

    Now, looking at the given options:

    a. Setting the value of the SqlCommand object's ExecuteReader property
    [Not Required. First of all ExecuteReader is a method and not an property. Second, even for the method, it is not required until and unless you plan to get the result in a Reader object. We could be getting the result back into a DataSet, which does not require the call to ExecuteReader but to DataAdapter's Fill.]

    b. An open SqlConnection object
    [Not Required. Using SqlDataAdapter does not require an Open Connection.]

    c. A call to the SqlCommand object's ExecuteReader method
    [Not Required. This is not required until and unless you plan to get the result back in a DataReader object. We could be getting the result back into a DataSet, which does not require the call to ExecuteReader but to DataAdapter's Fill.]

    d. An open OleDbConnection object
    [Not Required. Using OleDbDataAdapter does not require an Open Connection.]

    e. An instantiated StoredProc object
    [This is the only one option remaining and the only one of the given options that is absolutely required.]

    Hope this clarifies it better.

    • Marked as answer by boldtechie Tuesday, April 20, 2010 5:49 PM
    Wednesday, January 13, 2010 10:30 PM
    Moderator

All replies

  • My guess is that it just means that you should have a stored procedure database object in the backend database you are connecting to. Without the stored procedure database object available in the database, the SqlCommand will throw an appropriate "Could not find stored procedure" exception.

    As far as the open connection object and the ExecuteReader options are concerned, we can avoid those by using SqlDataAdapter instead of SqlCommand. The SqlDataAdapter does not require a open connection as it will internally open it in case the connection is in a closed state and internally create a command with the CommandText and the Connection object. But we still need the stored procedure in the backend. The code could look something like:

    using (SqlConnection cn = new SqlConnection(connectionstring))
    {
        using (SqlDataAdapter da = new SqlDataAdapter("MyProc", cn))
        {
            DataSet ds = new DataSet();
            da.Fill(ds);
            Console.WriteLine(ds.Tables[0].Rows[0][0]);
        }
    }
    Monday, November 30, 2009 11:39 PM
    Moderator
  • That's is pretty bad example of what people create as an interview questions. The e answer should sound like "Set CommandText property to the name of stored procedure". There is no stored procedure object as an object in this case and all SqlCommand needs in this case is to specify name of the stored procedure that exists in a database and then set CommandType property of SqlCommand to StoredPorcedure.
    Another issues here is that answer c is correct, based on how the question is worded, since it has no constraints and calling ExecuteReader will also execute stored procedure
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, December 1, 2009 11:07 AM
    Moderator
  • Ya a really bad question in my opinion. Unfortunately, i came to know that this question is also there in brainbench.
    There is no such class such as StoredProc. Then how can u instantiate it. Cant understand what they actually mean by giving the answer as e.
    Tuesday, December 1, 2009 5:32 PM

  • See the below you have to specify that command text is type of storeprocedure.
    string
     commandText = "sp_name"
    
    ;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
    using (SqlCommand command = new SqlCommand(commandText, connection))
    {
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
    while (reader.Read())
    {
    Console.WriteLine(String.Format("{0}, {1}" ,
    reader[0], reader[1]));
    }
    }
    }
    }



    Tejas Mer
    Monday, December 7, 2009 11:02 AM
  • Hi Tejas I agree to the above code... it works... ok...
    But, the doubt I have is about "instantiated StoredProc object". We dont instantiate such a thing... If you are referring to the CommandType then its an Enum... and its not instantiable...

    Or is it instantiating some internal class called StoredProc like a factory pattern. What I mean is when we set the command type to CommandType.StoredProcedure is it internally creating some instance of a class called StoredProc?
    Tuesday, December 8, 2009 5:28 PM
  • In a database a stored procedure, a view, a table, a trigger, ...etc are all defined as "objects". They are "database objects". The "instantiated StoredProc object" is to about the database object and not client side .NET object. You can not "instantiate" a stored procedure. It is a poor choice of words. But a stored procedure can be created, compiled and saved. The execute will call a compiled version of the "database object".

    Now, looking at the given options:

    a. Setting the value of the SqlCommand object's ExecuteReader property
    [Not Required. First of all ExecuteReader is a method and not an property. Second, even for the method, it is not required until and unless you plan to get the result in a Reader object. We could be getting the result back into a DataSet, which does not require the call to ExecuteReader but to DataAdapter's Fill.]

    b. An open SqlConnection object
    [Not Required. Using SqlDataAdapter does not require an Open Connection.]

    c. A call to the SqlCommand object's ExecuteReader method
    [Not Required. This is not required until and unless you plan to get the result back in a DataReader object. We could be getting the result back into a DataSet, which does not require the call to ExecuteReader but to DataAdapter's Fill.]

    d. An open OleDbConnection object
    [Not Required. Using OleDbDataAdapter does not require an Open Connection.]

    e. An instantiated StoredProc object
    [This is the only one option remaining and the only one of the given options that is absolutely required.]

    Hope this clarifies it better.

    • Marked as answer by boldtechie Tuesday, April 20, 2010 5:49 PM
    Wednesday, January 13, 2010 10:30 PM
    Moderator