Retrieve results of a stored procedure on c#

Question

I have a stored procedure that selects and returns a list of years. In sql server I call it like this:

DECLARE @return_value int

EXEC    @return_value = [dbo].[TestName]
        @del= 0

SELECT  'Return Value' = @return_value

In order to receive the list.

My SP looks like this:

USE [TestTable]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TestName] (@delvarchar(7))

AS
BEGIN

                    SELECT   YEAR(  [added]) AS year FROM [MyTable]


                    GROUP BY  YEAR(  [added]) 
                    ORDER BY  YEAR(  [added])  DESC

END

I want to do the same from c# though and pass the values in a List.

What I am trying is:

using (var conn = new SqlConnection(constr))
using (var command = new SqlCommand("TestName", conn)
{
   CommandType = CommandType.StoredProcedure
})
{
   command.Parameters.AddWithValue("@del", del);
   SqlParameter retval = command.Parameters.Add("@return_value", SqlDbType.VarChar);
   retval.Direction = ParameterDirection.ReturnValue;
   conn.Open();
   command.ExecuteNonQuery();
   int retunvalue = (int)command.Parameters["@return_value"].Value;
   conn.Close();

   return retunvalue;
}

This does not return any values though, instead it only returns 0. What am I doing wrong and how can I get the list inside a variable as specified?

Editing the code following John Hanna's advise I have something like this:

        public List<string> getYears(string constr, int del)
        {
            using (var conn = new SqlConnection(constr))
            using (var command = new SqlCommand("TestName", conn)
            {
                CommandType = CommandType.StoredProcedure
            })
            {
                command.Parameters.AddWithValue("@del", del);
                List<string> retunvalue = new List<string>();
                conn.Open();

                SqlDataReader reader;
                reader = command.ExecuteReader();
                conn.Close();

                return retunvalue;
            }
        }

And by adding a breakpoint in order to explore reader I see that it is only contains errors:

Depth = '(reader).Depth' threw an exception of type 'System.InvalidOperationException'

As for Krishna's answer, dtList was empty with a count of 0, and I am not sure how to implement Badhon's suggestion.

Answer 1

Is dbo.TestName returns you a table or a value. If that returns you a table, then you would have to execute DataAdapter or execute DataReader. You should replace the above statement as

DataTable dtList=new DataTable();
SqlDataAdapter adapter=new SqlDataAdapter();
adapter.SelectCommand=command;
adapter.Fill(dtList);

Then you can iterate through the datatable and add that to your list

List<Object> listObj=new List<Object>();
foreach(var rows in dtList.Rows)
{
    listObj.Add(rows["Column_name"]);
}

I analysed your query, and found that, the statement

DECLARE @return_value int

EXEC    @return_value = [dbo].[TestName]
        @del= 0

SELECT  'Return Value' = @return_value

returns multiple tables. You can remove the last statement which is redundant.

SELECT  'Return Value' = @return_value

The datatable will now be populated with values.

Let me know if that works.

Answer 2

ExecuteNonQuery() is so called because its for use with something that doesn't query the data. The 0 you get back is the number of rows the command changed.

Instead use ExecuteReader() and you will get back a SqlDataReader object that you can call Read() on to move through rows and then examine the details of each.

If you want to return that to another method use ExecuteReader(CommandBehavior.CloseConnection) and then rather than Close() or Dispose() the connection after you are finished, Close() or Dispose() the reader and that will close the connection then.

If you only have one row with one column (or for some reason only care about the first column of the first row even though there is more), then ExecuteScalar() is a convenient way to get just that single value.

Answer 3

You shouldn't use ExecuteNonQuery, rather use ExecuteDataSet as follow:

public List<DSM_DocPropIdentify> GetDocPropIdentify(string docPropIdentifyID, string action, out string errorNumber)
    {
        errorNumber = string.Empty;
        List<DSM_DocPropIdentify> docPropIdentifyList = new List<DSM_DocPropIdentify>();
        DatabaseProviderFactory factory = new DatabaseProviderFactory();
        SqlDatabase db = factory.CreateDefault() as SqlDatabase;
        using (DbCommand dbCommandWrapper = db.GetStoredProcCommand("GetDocPropIdentify"))
        {
            // Set parameters 
            db.AddInParameter(dbCommandWrapper, "@DocPropIdentifyID", SqlDbType.VarChar, docPropIdentifyID);
            db.AddOutParameter(dbCommandWrapper, spStatusParam, DbType.String, 10);
            // Execute SP.
            DataSet ds = db.ExecuteDataSet(dbCommandWrapper);

            if (!db.GetParameterValue(dbCommandWrapper, spStatusParam).IsNullOrZero())
            {
                // Get the error number, if error occurred.
                errorNumber = db.GetParameterValue(dbCommandWrapper, spStatusParam).PrefixErrorCode();
            }
            else
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataTable dt1 = ds.Tables[0];
                    docPropIdentifyList = dt1.AsEnumerable().Select(reader => new DSM_DocPropIdentify
                    {
                        DocPropIdentifyID = reader.GetString("DocPropIdentifyID"),
                        DocPropertyID = reader.GetString("DocPropertyID"),
                        DocCategoryID = reader.GetString("DocCategoryID"),
                        DocTypeID = reader.GetString("DocTypeID"),
                        OwnerID = reader.GetString("OwnerID"),
                        IdentificationCode = reader.GetString("IdentificationCode"),
                        IdentificationSL = reader.GetString("IdentificationSL"),
                        AttributeGroup = reader.GetString("AttributeGroup"),
                        IdentificationAttribute = reader.GetString("IdentificationAttribute"),
                        IsRequired = reader.GetInt16("IsRequired"),
                        IsAuto = reader.GetInt16("IsAuto"),
                        SetOn = reader.GetString("SetOn"),
                        SetBy = reader.GetString("SetBy"),
                        ModifiedOn = reader.GetString("ModifiedOn"),
                        ModifiedBy = reader.GetString("ModifiedBy"),
                        Status = reader.GetInt32("Status"),
                        Remarks = reader.GetString("Remarks")
                    }).ToList();
                }
            }
        }
        return docPropIdentifyList;
    }