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.

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, Dec 16 '18 at 22:07
It's worth looking at [Why is SELECT * considered harmful?](https://stackoverflow.com/questions/3639861/why-is-select-considered-harmful), including some of the less highly voted answers. — Richardissimo, Dec 16 '18 at 22:07

Derviş Kayımbaşıoğlu · Answer 1 · 2018-12-16T21:12:33.183

2

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.

edited Dec 16 '18 at 21:12

answered Dec 16 '18 at 20:44

Derviş Kayımbaşıoğlu

28,492
4
50
72

2

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 – marc_s Dec 16 '18 at 21:06
1

I checked it. It works in both ways. I am editing my answer. thank you – Derviş Kayımbaşıoğlu Dec 16 '18 at 21:12

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

1 Answers1