Getting a list from a SQL query in C#

Question

I want to turn the results of a SQL query into a C# list. I've tried using the following code which doesn't work - Visual Studio returns

An unhandled exception of type 'System.InvalidCastException' occurred in System.Data.dll

Code:

public List<string> carList(int id)
{
        List<string> list = new List<string>();

        SqlConnection conn = new SqlConnection(connStr);
        conn.Open();

        string query = "SELECT ID FROM Cars WHERE custID = " + id;
        SqlCommand cmd = new SqlCommand(query, conn);

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                list.Add(reader.GetString(0));
            }
        }

        conn.Close();

        return list;
    }

The result of the SQL query should just be a single column with one or more ID's (integers). I then want to put these all into a list. Hopefully this gives you a bit of an idea of what I'm trying to do.

If someone could point out what I'm doing wrong here then I'd be very thankful.

"doesn't work" - how? Do you get an error? The list is empty? Something else? — stuartd
– stuartd, Commented Jan 20, 2017 at 16:43
You should get the value as integer instead of GetString(0) and then call .ToString() on the value to add it in the list. — Ali Baig
– Ali Baig, Commented Jan 20, 2017 at 16:48
What's the data type of Cars.ID? I bet it's not a string. Try reader.GetInt32(0).ToString() — Dour High Arch
– Dour High Arch, Commented Jan 20, 2017 at 16:48
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection — marc_s
– marc_s, Commented Jan 20, 2017 at 17:06
@marc_s Ok, I didn't realise this could pose a security risk, thanks for pointing out. — Viva
– Viva, Commented Jan 20, 2017 at 17:16

Ali Baig · Accepted Answer · 2017-01-20 16:54:44Z

1

Give it a try as suggested in the comments

while (reader.Read())
{
    list.Add(reader.GetInt32(0).ToString());
}

The type of data on 0th index should be an integer which should be converted to a string before adding to the list.

answered Jan 20, 2017 at 16:54

Ali Baig

3,8674 gold badges36 silver badges49 bronze badges

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

Comments

D Stanley · Accepted Answer · 2017-01-20 17:00:03Z

1

If you want a more generic way to convert any single-column result set to a list of strings you can use:

while (reader.Read())
{
    list.Add(reader.GetValue(0).ToString());
}

answered Jan 20, 2017 at 17:00

D Stanley

153k12 gold badges189 silver badges257 bronze badges

1 Comment

Viva Over a year ago

Ok, that might just come in handy later on. Thanks!

Collectives™ on Stack Overflow

Getting a list from a SQL query in C#

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related