Getting column info from db with C#

Question

In PHP I am able to retrieve information from a db like so:

<?php
    $sql = "SELECT * FROM users";
    $result = mysql_query($sql);
    $data = array();
    while($row = mysql_fetch_assoc($result))
    {
        $data[] = $row;
    }

I am trying to acomplish the same thing in C#:

OdbcCommand cmd = new OdbcCommand("SELECT * FROM users WHERE id = @id");
        cmd.Parameters.Add("@id", id);
        OdbcDataReader reader = cmd.ExecuteReader();
        Dictionary<string, string> data = new Dictionary<string, string>();
        while (reader.Read())
        {
            data.Add("id", reader.GetString(0));
            data.Add("username", reader.GetString(1));
            data.Add("firstName", reader.GetString(2));
        }
        return data;

Is it possible to reference the column in the table by name without going through all this trouble?

Answer 1

You can use the OdbcDataAdapter class to populate a DataSet, which would probably be a bit simpler.

Answer 2

Yes, but it is SLOW. Not slower than your approach, granted, but slow - I would mark your whole code for a review...

• Dont ask for "*", ask for the fields. Good SQL practice - and as you know the fields, guess what, you dont ahve to guess them. On top, you assume id is fiel 0, username field 1...

If that is not possible - read the docomentation. There is a "GetOrdinal" method that takes a field name and.... returns.... the field index.

That said, this whole code is fully redundant. Have a look at proper data access layers, or at least BLTOOLKIT - you can move all that code into ONE abstract method with the rest being automatically generated.