Stuck on SqlDataReader.GetValues Method

I am making a call to a SQL stored procedure which is returning a small table of users and their bosses. What I would like to do is compare this information against a variable in a different part of my application to enable/disbale editing of a GridView row. To accomplish this, it seems that the GetValues method would return what I need, which is the entire dataset the stored procedure is returning. My thikning is that once I have that data in my app, I could load that dataset into an array, and loop through it to do what I need to do.

The problem is this. In my code below I am getting the error

Cannot implicitly convert type ‘int’ to ‘object’

When I look at the documentation on this method, the return value is an int.

My question is, since the stored procedure contains string data (user and boss names), why is the GetValues mehtod returning an int? How is my actual data represented as a number And how do I get my data into an string based array?

I’ve been looking at many examples and information on the internet, this seems to be a common problem new people have. I’m just not understanding or getting it, and I’m not making any progress. Any help is greatly appreciated!

    public Object[] GetDeptAppData()
    {
        Object[] myObject;
        string sp_deptapp, sp_status, sp_supervisor;

        //execute stored procedure to get data from database
        SqlConnection sqlConnection = new SqlConnection("Data Source=MyServer;Initial Catalog=MyCatalog;Persist Security Info=True;User ID=MyUser;Password=MyPassword");
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader;

        cmd.CommandText = "SP_Admin_DeptApp_Load";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Connection = sqlConnection;
        sqlConnection.Open();
        reader = cmd.ExecuteReader();

        if (reader.HasRows == true)
        {
            //sp_deptapp = reader.GetValue(0).ToString();
            //sp_status = reader.GetValue(1).ToString();
            //sp_supervisor = reader.GetValue(2).ToString();

            myObject = reader.GetValues(myObject);
        }
        else
        {
            //nothing in reader, throw exception
        }
        sqlConnection.Close();

        return myObject;
    }

Answer

The error you encounter is located in this line:

myObject = reader.GetValues(myObject);

myObject is an array of objects and the GetValues method returns an integer that contains the count that is placed in the array. So in order to solve your error, you can change the line to the following:

var count = reader.GetValues(myObject);

Also, in your code, you only retrieve one row of data. This is – of course – fine if you only expect one row. When expecting multiple rows, you typically loop over the rows like this:

while (reader.Read())
{
    // Read row and add row to list
}

For a sample on how to use SqlDataReader, see this link.

---

Sample

If you want to retrieve multiple rows and store the data, I suggest to add a class to store the data of a row (you might want to verify the data types so that they match the types that the SP returns):

public class Data
{
    public string DeptApp { get; set; }
    public string Status { get; set; } // this might be another data type
    public string Supervisor { get; set; }
}

Then retrieve the rows as follows:

public Data[] GetDeptAppData()
{
    //execute stored procedure to get data from database
    using (SqlConnection sqlConnection = new SqlConnection("Data Source=MyServer;Initial Catalog=MyCatalog;Persist Security Info=True;User ID=MyUser;Password=MyPassword"))
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "SP_Admin_DeptApp_Load";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = sqlConnection;
        sqlConnection.Open();
        using (var reader = cmd.ExecuteReader())
        {
            if (!reader.HasRows)
            {
                // throw exception
            }
            var lst = new List<Data>();
            while (reader.Read())
            {
                var row = new Data();
                row.DeptApp = reader.GetString(0);
                row.Status = reader.GetString(1);
                row.Supervisor = reader.GetString(2);
                lst.Add(row);
            }
            return lst.ToArray();
        }
    }
}