How to retrieve multiple rows from SQL Server database in C# using SqlDataReader?

Question

I am trying to write a method which is supposed to retrieve multiple rows from a table in a database and use those data to instantiate a number of objects. However, as far as i can tell the database only returns the first row. When i do this:

public static List<Event> getMultipleEvents(string[] eventNames)
 {
 List<Event> rtnList = new List<Event>(); 
 string EventsToRetrieve = "";
 foreach (var item in eventNames)
 {
 if (EventsToRetrieve != "")
 {
 EventsToRetrieve += " OR ";
 }
 EventsToRetrieve += "eventName = '";
 EventsToRetrieve += item;
 EventsToRetrieve += "' ";
 }
 // This is the string that the method constructs based on the input i am testing with 
 //"eventName = 'event six' OR eventName = ' event two' OR eventName = ' event one' OR eventName = ' event seven' "
 using (SqlConnection sqlConnection = Globals.GetSqlConnection())
 {
 sqlConnection.Open();
 using (SqlCommand sqlCommand = new SqlCommand("SELECT * FROM questions WHERE " + EventsToRetrieve + ";", sqlConnection))
 {
 using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
 {
 if (sqlDataReader != null)
 {
 while (sqlDataReader.Read())
 {
 Event newEvent = new Event("", DateTime.MinValue, DateTime.MinValue);
 string startDateTimeStringFromDB = sqlDataReader["startDateDay"].ToString() + "-" + sqlDataReader["startDateMonth"].ToString() + "-" + sqlDataReader["startDateYear"].ToString();
 string endDateTimeStringFromDB = sqlDataReader["endDateDay"].ToString() + "-" + sqlDataReader["endDateMonth"].ToString() + "-" + sqlDataReader["endDateYear"].ToString();
 newEvent.EventName = sqlDataReader["eventName"].ToString();
 if (DateTime.TryParse(startDateTimeStringFromDB, out DateTime startDateTime))
 {
 newEvent.StartDate = startDateTime;
 }
 if (DateTime.TryParse(endDateTimeStringFromDB, out DateTime endDateTime))
 {
 newEvent.EndDate = endDateTime;
 }
 rtnList.Add(newEvent);
 }
 }
 }
 }
 }
 return rtnList;
 }

Can anyone explain to me what i am doing wrong ? I Also tried wrapping the while loop in a do while loop as suggested here How to read multiple resultset from SqlDataReader? but it didn't change anything.

• Richardissimo: Unrelated tip: Rather than having 3 columns startDateYear, startDateMonth, startDateDay, consider having one column startDate of type date. Aside from the storage space saving, and the validation of values being inserted and the SQL queries being easier to write, the C# is also easier to write... Several of those lines collapse into: newEvent.StartDate = sqlDataReader.GetDateTime(startDateOrdinal); where startDateOrdinal is the column number in the resultset. You could look that up with GetOrdinal, but if you don't use SELECT * you'd know which column it was.
• Richardissimo: It's worth looking at Why is SELECT * considered harmful?, including some of the less highly voted answers.

Answer 1

It doesn't seem to have any error in your code. however I believe that your query has errors.

First of all never ever use string concatenation when you are building your SQL Query. Instead use Parameterized Queries.

with the parameterized queries, it is even easier to debug your SQL statement since it does not include conditional string concatenations.

• marc_s: A ; at the end of a T-SQL statement IS a valid character - in fact, in some cases, it's even required and it's generally a recommend best practice to terminate each T-SQL statement with a ; in new SQL code being written
• Derviş Kayımbaşıoğlu: I checked it. It works in both ways. I am editing my answer. thank you