SqlCommand and ExecuteReader return Non-Public Members

Question

I execute a scripted query from a .sql file with SqlCommand.

I get the result with an ExecuteReader.

But values are as non-public member and I can't access it.

Any idea why and how resolve that?

Code

string root = AppDomain.CurrentDomain.BaseDirectory;
string script = File.ReadAllText(root + @"..\SGBD\select_user_from_all_bases.sql");
string connectionString = @"Data Source=(local);Integrated Security=SSPI";
var conn = new SqlConnection(connectionString);

conn.Open();

var users = new SqlCommand(script, conn).ExecuteReader();

foreach (var user in users)
{

}

conn.Close();

enter image description here

Have you even looked at how you're supposed to use this API? — Adi Lester, Nov 26 '12 at 14:16
If you don't want all the fuss of dealing with that API, consider something like "dapper" (search on NuGet). The entire work is then: `var users = conn.Query(script).ToList();` — Marc Gravell, Nov 26 '12 at 14:20
@MarcGravell - Actually I want to expose this users in json so Dapper is perhaps what I search. Thanks. — GG., Nov 26 '12 at 14:36
@MarcGravell - I find a solution here : http://stackoverflow.com/a/8965906/652669 — GG., Nov 26 '12 at 14:49

score 1 · Accepted Answer · answered Nov 26 '12 at 14:17

This because ExecuteReader() returns a SqlDataReader, not the entries.

Look at the documentation of the SqlDataReader to see how you should do:

private static void ReadOrderData(string connectionString)
{
    string queryString =
        "SELECT OrderID, CustomerID FROM dbo.Orders;";

    using (SqlConnection connection =
               new SqlConnection(connectionString))
    {
        SqlCommand command =
            new SqlCommand(queryString, connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        // Call Read before accessing data.
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}, {1}", reader[0], reader[1]));
        }

        // Call Close when done reading.
        reader.Close();
    }
}

note: `command` and `reader` should also have `using` statements. — Marc Gravell, Nov 26 '12 at 14:21
Ok I tried that but reader[0] returned 0 so I thought it did't work. In fact this is the good result, I'm an idiot. Thank you. — GG., Nov 26 '12 at 14:32

score 0 · Answer 2 · answered Nov 26 '12 at 14:21

As mentioned already, you probably shouldn't be using foreach. For compatibility reasons, it needs to be implemented, but a while (reader.Read()) loop is simpler here. That said, you can use foreach anyway if you want. The user values implement IDataRecord, and you can use that interface to access the values.

foreach (IDataRecord user in reader)
{
    // access user[0], user["field"], etc. here
}

SqlCommand and ExecuteReader return Non-Public Members

Code

2 Answers2