Appending To List

Question

Using C# how can I append to a List<string>? I am wanting to run two seperate SQL Queries and store the results of both in my List<string>. The first query returns

Bill and Joe

And the second query returns

James and Charles

But my Console.WriteLine() only produces Bill and Joe

What did I set-up improperly in my syntax for this to occur?

public static List<string> tempList = new List<string>();

static void Main(string[] args)
{   
  string qs = "Select Top 2 firstname from thistable";
  //
  GetFirstSetOfData(qs);

  string bc = "Select Top 2 firstname from thattable";
  //
  GetFirstSetOfData(bc);

  foreach (string tumble in tempList)
    Console.WriteLine(tumble);
}

private static void GetFirstSetOfData(string query)
{
  connection = new SqlConnection(ConnectionString);
  {
    cmd = new SqlCommand(queryString, connection);
    connection.Open();
    reader = cmd.ExecuteReader();
    while (reader.Read())
        tempList.Add(reader.GetValue(0).ToString());
    reader.Close();
  }
}

This wouldn't compile since `GetFirstSetOfData` can't access tempList. In your actual code, how is `tempList` defined? — Michael Stum, Jan 18 '17 at 23:27
Thanks. As the code stands, this should work. `tempList` is instantiated only once, and since it's a reference type, both calls to GetFirstSetOfData append to the same list instance. This is assuming that `thattable` actually returns the results you think it does. You can try `while (reader.Read()) { var x = reader.GetValue(0).ToString(); Console.WriteLine(x); tempList.Add(x); }` to verify that the query is returning what you expect. — Michael Stum, Jan 18 '17 at 23:31
(Unrelated to the issue, there are some recommendations about the code itself, e.g., use `using` around disposables like connection and reader, but that's unrelated to the issue you're asking about) — Michael Stum, Jan 18 '17 at 23:32
You should also try to isolate the method. let the `GetFirstSetOfData` return a list to the main method and do the add there — Nkosi, Jan 18 '17 at 23:33
@MichaelStum - good call, I didn't think about adding in a using block OR checking if reader was null. — StarsFlyFree FromCozyNights, Jan 18 '17 at 23:35
All good advices but your code should work if the second table has two rows. How did you check that the method reads effectively _James_ and _Charles_? — Steve, Jan 18 '17 at 23:35
@StarsFlyFreeFromCozyNights FWIW, reader would never be null. `cmd.ExecuteReader` will either throw an exception or return a reader. — Michael Stum, Jan 19 '17 at 00:26

score 0 · Answer 1 · answered Jan 18 '17 at 23:41

My guess is that it probably has something to do with the SQLConnection. It would be better to initialize the SQLConnection in your Main method instead of trying to connect to the same database a second time. Also, the SQLConnection should really be disposed of once you're done. The easy way to make sure that happens is to wrap things in a using statement, like so:

using (connection = new SQLConnection(connectionString))
{
    connection.Open();
    //do all your queries and things
}

You can see an excellent full example on MSDN here: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.close(v=vs.110).aspx

Try that and see if it works. I think the problem is that you're trying connection.open twice on the same connection. Could be wrong though, its been a while since I messed with sql in c#.

If there is something wrong with the connection he should see an exception at the Open call. Good advice about the using statement but this cannot be the problem with the code posted. — Steve, Jan 18 '17 at 23:51
Ok, didn't rember if that one would throw an error or not. However, some environments, particularly web servers, tend to suppress errors silently. So if He's running it through visual studio he would already know if it were a problem with the open statement, otherwise he ought to do something to confirm that his code is getting past that second call. — ashbygeek, Jan 19 '17 at 03:50

Appending To List

1 Answers1