Stuck on SqlDataReader.GetValues Method

Question

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;
    }

Markus · Accepted Answer · 2014-02-06 19:45:37Z

12

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();
        }
    }
}

edited Feb 6, 2014 at 19:45

answered Feb 6, 2014 at 19:29

Markus

22.7k4 gold badges34 silver badges61 bronze badges

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

10 Comments

Rob Over a year ago

"myObject is an array of objects" - So is that ok? I was thinking myObject would be a single object that contained the results of the stored proc. "GetValues method returns an integer that contains the count that is placed in the array." - So all it's doing is counting something? What is it counting, and why do I care? I don't really care how many rows are I the returned data from the stored proc, I care about the actual data. It seems that the var COUNT will only contain a number representing how many items are in the stored proc.

Markus Over a year ago

@Rob: no, myObject is declared as Object[], so it is an array of objects. A SqlDataReader is used to retrieve one row of data after another. GetValues stores the data in the parameter and returns the number of fields that was read from the current row (!). If you want to retrieve multiple rows, you need to add a loop. I'll extend my answer.

Rob Over a year ago

I had the WHILE lopp going before, but I didn't think I needed it because I was expecting the GetValues() to return all my data in one big shot to myObject

Markus Over a year ago

@Rob: I've updated the sample. Note that a SqlDataReader is a way to retrieve data quickly in a forward-only manner. It does return data row by row.

Scott Chamberlain Over a year ago

@Rob if you want all the operations in one command you may want to look in to SqlDataAdapter's Fill( command. It will give you all of the results in one step and store the results in a DataTable or DataSet object.

|

Andy Brown · Accepted Answer · 2014-02-06 19:51:40Z

5

GetValues returns an int which is "The number of instances of Object in the array.", so you cannot assign that to myObject. This is cryptic. It actually means (I believe) the number of column values returned for the current row.

You need:

int numColumnsRetrieved = reader.GetValues(myObject);

or something similar.

Your data is then present inside myObject, but only for the current row, as per the docs "Populates an array of objects with the column values of the current row". So, you still need to iterate, such as:

var myObjectList = new List<object[]>(); // ... shudder ...
while (reader.Read())
{
    var myObject[] = new object[reader.FieldCount];
    int instances = reader.GetValues(myObject);
    myObjectList.Add(myObject);
}

You might find it useful to use a method that is more specific, such as GetString:

var usersAndBosses = new List<Tuple<string, string>>();
while (reader.Read())
{
    string username = reader.GetString(0); // column index 0
    string bossname = reader.GetString(1); // column index 1
    usersAndBosses.Add(Tuple.Create(username, bossname));
}

EDIT

If you want a spurious use for the return value from GetValues, you could flatten a table of strings into a list of strings:

var myStringList = new List<string>();
while (reader.Read())
{
    var myObject[] = new object[100];
    int colCount = reader.GetValues(myObject);
    for (int i = 0; i < colCount; i++)
    {
        myStringList.Add((string)myObject[i]);
    }
}

edited Feb 6, 2014 at 19:51

answered Feb 6, 2014 at 19:28

Andy Brown

19.2k3 gold badges55 silver badges62 bronze badges

3 Comments

Rob Over a year ago

Same question as I had for the other person who replied, why do I care about 'counting the instances of an object in the array'? I care about the data, not how much is there. A number doesn't do anything for me. I like your second block, I have a similar thing in my code above commented out. I can identify, using while(reader.read()), the 3 unique columns of data, using reader.getValue(0).ToString(), but then I would just need to load it into an array, and then pass out of the method using RETURN.

Andy Brown Over a year ago

First answer: I would steer clear of passing around arrays of objects, C# is a strongly typed language (even Tuples are lazy coding, although at least you can enforce type checking at compile time).

Andy Brown Over a year ago

Second answer: If, for instance you pass in an object array of length 10, if there were only 3 columns it will tell you it only put 3 values into the array of length 10. I haven't ever found a use for it. GetValues is not designed to dump all rows and columns, no method is, so you need to iterate using reader.Read()

Denis Kochetkov · Accepted Answer · 2018-10-27 08:28:30Z

Below there is ExecuteSelectQuery(...) function to get resuls of any SELECT queries.

The result of ExecuteSelectQuery is a list of arrays of System.Object elements which can be transformed to required types in calling code.

public sealed class DbPerformer
{
    private const string mySqlServerName = "localhost";
    private const string dataBaseName = "testdb";
    private const string uid = "login";
    private const string password = "password";

    private MySqlConnection connection;

    public DbPerformer()
    {
        string connectionString;
        connectionString = "server=" + mySqlServerName + ";";
        connectionString += "Username=" + uid + ";";
        connectionString += "Password=" + password;

        connection = new MySqlConnection(connectionString);
    }

    private bool OpenConnection()
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            switch (ex.Number)
            {
                case 0:
                    Debug.WriteLine("Cannot connect to server {0}", mySqlServerName);
                    break;

                case 1045:
                    Debug.WriteLine("Invalid username/password.");
                    break;
            }
            return false;
        }
    }

    private bool CloseConnection()
    {
        try
        {
            connection.Close();
            return true;
        }
        catch (MySqlException ex)
        {
            Debug.WriteLine(ex.Message);
            return true;
        }
    }

    public List<object[]> ExecuteSelectQuery(string selectQuery)
    {
        List<object[]> resultList = new List<object[]>();

        if (true == this.OpenConnection())
        {
            MySqlCommand command = new MySqlCommand(selectQuery, connection);
            MySqlDataReader resultReader = command.ExecuteReader();

            while (resultReader.Read())
            {
                object[] resultRow = new object[resultReader.FieldCount];
                resultReader.GetValues(resultRow);
                resultList.Add(resultRow);
            }

            resultReader.Close();
            this.CloseConnection();
        }
        else
        {
            throw new Exception("Can not open connection to database.");
        }
        return resultList;
    }
}

Collectives™ on Stack Overflow

Stuck on SqlDataReader.GetValues Method

3 Answers 3

10 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

10 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related