none
How can I loop the recordset? RRS feed

  • Question

  • Hi,

    I have a database with a few rows, I built this query:

    Code Block

    SELECT ID, Path, ArtName FROM table

     

     

    How can I loop the recordset with:

    Code Block

    while (dr.Read())

    {

    }

     

     

    Or there is another way.

    I tried a lot of time without success...

    Thanks, Idan.

    Wednesday, October 3, 2007 8:17 PM

Answers

  • Hi!

     

    You could try using a DataTable and dataadapter when you get the data.

     

    Code Block

    using (SqlConnection cn = new SqlConnection(CONNECTION_STRING))

    using (SqlCommand cmd = new SqlCommand("SELECT ID, Path, ArtName FROM table", cn))

    using (SqlDataAdapter da = new SqlDataAdapter(cmd))

    {

    DataTable table = new DataTable("MyTable");

    da.Fill(table);

     

    //Here you can decide if you wish to return the table if you have a specific GetData() method or if you wish to do something with the data right away.

     

    foreach (DataRow row in table.Rows)

    {

    //Do you thing here

    }

    }

     

     

    Hope this helps

     

    //Micke

    Wednesday, October 3, 2007 9:23 PM
  • Hi all

     

    in "foreach" as variable used "row"

    you can get value from any column in this row in each iteration like

     

    Code Block

    String stringData=Convert.ToString(row["COLUMN_NAME_1"]);

    int intData=Convert.ToInt32(row["COLUMN_NAME_2"]);

     

     

     

    Thursday, October 4, 2007 8:57 AM

All replies

  • Code Block

    private static void ReadOrderData(string connectionString)
    {
        string queryString =
            "SELECT OrderID, CustomerID FROM dbo.Orders;";

        using (SqlConnection connection =
                   new SqlConnection(connectionString))
        {
            SqlCommand command =
                new SqlCommand(queryString, connection);
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            // Call Read before accessing data.
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",
                    reader[0], reader[1]));
            }

            // Call Close when done reading.
            reader.Close();
        }
    }


    OR

    private static DataSet SelectRows(DataSet dataset,
        string connectionString,string queryString)
    {
        using (SqlConnection connection =
            new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = new SqlCommand(
                queryString, connection);
            adapter.Fill(dataset);
            return dataset;
        }
    }

    you can use

    foreach(DataRow row in DataSet.Tables[0].Rows)
    {
     ...
    }


    Wednesday, October 3, 2007 8:33 PM
  • Hi!

     

    You could try using a DataTable and dataadapter when you get the data.

     

    Code Block

    using (SqlConnection cn = new SqlConnection(CONNECTION_STRING))

    using (SqlCommand cmd = new SqlCommand("SELECT ID, Path, ArtName FROM table", cn))

    using (SqlDataAdapter da = new SqlDataAdapter(cmd))

    {

    DataTable table = new DataTable("MyTable");

    da.Fill(table);

     

    //Here you can decide if you wish to return the table if you have a specific GetData() method or if you wish to do something with the data right away.

     

    foreach (DataRow row in table.Rows)

    {

    //Do you thing here

    }

    }

     

     

    Hope this helps

     

    //Micke

    Wednesday, October 3, 2007 9:23 PM
  • What do you mean by "loop the recordset"? Theres no actual record sets involved with ADO.Net. If you just want to loop through a collection of rows ( and using a reader isnt an acceptable solution ) then you will have to use either a DataTable or DataSet and a DataAdapter. Once a DataAdapter fills a table ( either one associated with a dataset or not ) you can loop through the rows as much as you like.

    Wednesday, October 3, 2007 10:40 PM
  • Mikael,

    I tried your code and it displays "System.Data.DataRow" instead of the row.

    What can I do?

    Thank you.

    Thursday, October 4, 2007 8:44 AM
  • Hi all

     

    in "foreach" as variable used "row"

    you can get value from any column in this row in each iteration like

     

    Code Block

    String stringData=Convert.ToString(row["COLUMN_NAME_1"]);

    int intData=Convert.ToInt32(row["COLUMN_NAME_2"]);

     

     

     

    Thursday, October 4, 2007 8:57 AM
  • Mikael and AndreyDev - Thank you very much

    Thursday, October 4, 2007 9:32 AM