CODEWITHSUNDEEP
c#sqlsql-serverconnectionsqldatareader
Serkan Kaya
Serkan Kaya

Reputation: 5

SQL get to C# with SqlDataReader

I have a problem. I want to get data from my SQL Server database. When the code is running, the first row isn't getting added to my arraylist. All other rows get added successfully. In SQL Server, the query works fine, but in VS, it doesn't work. 

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection baglanti = new SqlConnection("server=.; Initial Catalog=TripData;Integrated Security=SSPI");
    baglanti.Open();

    SqlCommand komut = new SqlCommand();
    komut.CommandText = "select top 50 trip_ID from Sayfa1$";
    komut.Connection = baglanti;

    komut.ExecuteNonQuery();

    SqlDataReader oku = komut.ExecuteReader();

    while (oku.Read())
    {
        foreach (var item in oku)
        {
            gecici.Add(oku["trip_ID"].ToString());
        }
    }
}

Upvotes: 0

Views: 72

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1499770

You're trying to iterate over the reader in two different ways - using both a foreach loop and using while (reader.Read()). You should do one or the other - I've personally seen more code using while (reader.Read()) than the foreach approach.

Additionally I'd suggest using using statements for the disposable objects in your code - and not calling ExecuteNonQuery first. (It's not clear why you're doing that at all). So you could write:

// Note that this looks like a mixture of UI and non-UI code; consider separating
// them for greater code reuse, separation of concerns etc.
private void button1_Click(object sender, EventArgs e)
{
    // Declare connectionString elsewhere, or have a common method to open the connection.
    // You're likely to need that in multiple places.
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (var command = new SqlCommand("select top 50 trip_ID from Sayfa1$", connection))
        {
            using (var reader = command.ExecuteReader())
            {    
                while (reader.Read())
                {
                    gecici.Add(oku["trip_ID"].ToString());
                }
            }
        }
    }
}

(Also if you're really using ArrayList as your post suggests, I'd definitely urge you to start using the generic collections, e.g. List<string> in this case.)

Upvotes: 4

Related Questions