SQL select from database in C#

Question

I have the following code in C#:

string query = "SELECT * FROM Zboruri WHERE cod_aeroport = " + country;

using (var command = new SqlCommand(query, connection))
{
    var list = new ArrayList();
    var reader = command.ExecuteReader();

    if (reader.HasRows)
    {
         while (reader.Read())
         {
             string data1 = reader.GetString(1);
             string data2 = reader.GetString(2);
             list.Add(cod_aeroport);
             list.Add(data1);
             list.Add(data2);
         }
    }
    else
    {
        string raspuns = "nu este info";
        list.Add(raspuns);
    }

    reader.Close();
    connection.Close();
    return list;
 }

My database table has these columns:

data1(numeric(18,0))
data2(numeric(18,0))
...........

and it giving me error:

Index was outside the bounds of the array.

on this line of code:

string data2 = reader.GetString(2);

How can I fix error?

If your SELECT returns two columns - those are columns #0 and #1 - there is no column #2 .... — marc_s
– marc_s, Commented Sep 29, 2012 at 10:19
Also, ArrayList is considered pretty, er, old-school these days. Look into generic lists (List<T>) if you're using .NET 2.0 or later. — tomfanning
– tomfanning, Commented Sep 29, 2012 at 10:29

Francois · Accepted Answer · 2012-09-29 10:18:54Z

5

index starts at 0, not 1

string data1 = reader.GetString(0);
string data2 = reader.GetString(1);

answered Sep 29, 2012 at 10:18

Francois

11k9 gold badges53 silver badges81 bronze badges

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

Comments

John Woo · Accepted Answer · 2012-09-29 10:29:45Z

4

I'll prefer to use the column names retrieve from the table because it gives more description than using index of the column (which is usually the reason for index out of bounds exception)

ex,

string data1 = reader["colName1"].ToString();
string data2 = reader["colName2"].ToString();

side-note: please do use parameters in your query,

string query = "SELECT * FROM Zboruri WHERE cod_aeroport = @country";

and before you call ExecuteReader add this line,

command.Parameters.AddWithValue("@country", country);
var reader = command.ExecuteReader();

edited Sep 29, 2012 at 10:29

answered Sep 29, 2012 at 10:24

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Joachim Isaksson · Accepted Answer · 2012-09-29 10:29:33Z

1

The index is zero based, so the first column is 0, the second is 1. You should really use the named version of the columns though;

string data1 = reader["data1"].ToString();

One more thing, you should probably parameterize your query to avoid SQL injection problems, in this case it's almost as simple as your original query and much safer. It also helps the database save quite a bit of time and memory on similar queries;

string query = "SELECT * FROM Zboruri WHERE cod_aeroport = @country";
using (var command = new SqlCommand(query, connection))
{
    command.Parameters.AddWithValue("@country", country);
    var list = new ArrayList();
    ...

answered Sep 29, 2012 at 10:29

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

2 Comments

Igor Over a year ago

Thank you for your help, your advice was helpful to me!

Joachim Isaksson Over a year ago

@user1582145 You should probably find the most helpful answer to your question and mark the question as solved by that answer, that will help others find better solutions on this site.

Lucero · Accepted Answer · 2012-09-29 10:35:16Z

1

You should better access them by name, by resolving the index by name using the GetOrdinal() method first, so that the code doesn't break if the query is extended later.

if (reader.HasRows)
{
     int data1Index = reader.GetOrdinal("data1");
     int data2Index = reader.GetOrdinal("data2");
     while (reader.Read())
     {
         string data1 = reader.GetString(data1index);
         string data2 = reader.GetString(data2index);
         list.Add(cod_aeroport);
         list.Add(data1);
         list.Add(data2);
     }
}

Note that this is the slightly better approach compared to using the named indexer because it avoids looking up the index on every row.

Also, please do parametrize the query to avoid SQL injection.

edited Sep 29, 2012 at 10:35

answered Sep 29, 2012 at 10:27

Lucero

60.4k9 gold badges127 silver badges154 bronze badges

2 Comments

Igor Over a year ago

for column: data1(numeric(18,0)) it using reader.GetOrdinal(), bat if column: name(nvarchar(50)) what should by?

Lucero Over a year ago

@Igor, GetOrdinal() just maps the column name to the index that you need to use for the GetXxx() methods - the type of the column is irrelevant at that point.

Collectives™ on Stack Overflow

SQL select from database in C#

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related