CODEWITHSUNDEEP
c#.netsql-server
Igor
Igor

Reputation: 329

SQL select from database in C#

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?

Upvotes: 2

Views: 26412

Answers (4)

Lucero
Lucero

Reputation: 60190

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.

Upvotes: 1

John Woo
John Woo

Reputation: 263693

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

Upvotes: 4

Joachim Isaksson
Joachim Isaksson

Reputation: 180877

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

Upvotes: 1

Francois
Francois

Reputation: 10968

index starts at 0, not 1

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

Upvotes: 5

Related Questions