SQL Server: return all rows in table and read their values

Question

I have a table with 6 columns and 5 rows. I want to select all rows and read through them using the SqlDataReader (ASP.NET C#). I currently can access single columns using dataReader["columnName"].ToString() and store that in a string variable.

I want to read through the columns row by row.

Thanks for helping.

Answer 1

If you want to store the rows and columns into a collection of some sort, you can try using a List and Dictionary which will let you add as many rows as you need.

     List<Dictionary<string, string>> rows = new List<Dictionary<string, string>>();
     Dictionary<string, string> column; 
     string sqlQuery = "SELECT USER_ID, FIRSTNAME, LASTNAME FROM USERS";

    SqlCommand command = new SqlCommand(sqlQuery, myConnection);

    try
    {
        myConnection.Open();

        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {    //Every new row will create a new dictionary that holds the columns
             column = new Dictionary<string, string>(); 

             column["USER_ID"] = reader["USER_ID"].ToString();
             column["FIRSTNAME"] = reader["FIRSTNAME"].ToString();
             column["LASTNAME"] = reader["LASTNAME"].ToString();

             rows.Add(column); //Place the dictionary into the list
        }
        reader.Close();
    }
    catch (Exception ex)
    { 
         //If an exception occurs, write it to the console
         Console.WriteLine(ex.ToString());
    }
    finally
    {
        myConnection.Close();
    }

    //Once you've read the rows into the collection you can loop through to                     
    //display the results

    foreach(Dictionary<string, string> column in rows)
    {
        Console.Write(column["USER_ID"]) + " ";
        Console.Write(column["FIRSTNAME"] + " ";
        Console.Write(column["LASTNAME"] + " ";
        Console.WriteLine();
    }

Answer 2

Based on an MSDN example, here's a straightforward implementation:

    private static void ReadGetOrdinal(string connectionString)
    {
        string queryString = "SELECT DISTINCT CustomerID FROM dbo.Orders;";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using(SqlCommand command = new SqlCommand(queryString, connection))
            {
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {

                    // Call GetOrdinal and assign value to variable. 
                    int customerID = reader.GetOrdinal("CustomerID");

                    // Use variable with GetString inside of loop. 
                    while (reader.Read())
                    {
                        Console.WriteLine("CustomerID={0}", reader.GetString(customerID));
                    }
                }
            }
        }
    }

Answer 3

Here you go,

Member Variables:

static SqlConnection moConnection = new SqlConnection("Data Source=XXX;Initial Catalog=XXX;User ID=XXX;Password=XXX");
static SqlCommand moCommand = new SqlCommand();
static SqlDataAdapter moAdapter = new SqlDataAdapter();
static DataSet moDataSet = new DataSet();
static string msQuery;

In class method, you have to write below code:

DataSet loDataSet = new DataSet();
msQuery = "SELECT * FROM [TableName]";
Execommand(msQuery);
moAdapter.Fill(loDataSet);
DataTable loTable = new DataTable();
loTable = loDataSet.Tables[0];
if (loTable != null && loTable.Rows.Count > 0)
{
    foreach (DataRow foRow in loTable.Rows)
    {
       string lsUserID = Convert.ToString(foRow["UserID"]);
    }
}