SQL Server：返回表中的所有行并读取其值

Question

我有一个6列5行的表。我想选择所有行并使用SqlDataReader（ASP.NET
C＃）仔细阅读它们。我目前可以使用访问单个列dataReader["columnName"].ToString()并将其存储在字符串变量中。

我想逐行阅读各列。

感谢您的帮助。

Answer 1

如果要将行和列存储到某种类型的集合中，可以尝试使用“列表和字典”，这将使您可以根据需要添加任意多的行。

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