How to Add Record Count on Existing Code

Question

What is easiest way to add Record count for this code:

try

{

String connectionString = "Data Source=XPSSLOWPC;Initial Catalog=Movies;Integrated Security=True;";

using (SqlConnection connection = new(connectionString))

{

    connection.Open();

    String sql = "select Title, Year, Format, Length, Video, Audio from Info where Format = 'Ultra HD' order by Title";

    using (SqlCommand command = new(sql, connection))

    {

        using (SqlDataReader reader = command.ExecuteReader())

        {

            while (reader.Read())

            {

                MovieInfo movieinfo = new MovieInfo();

                {

                    movieinfo.Title = reader.GetString(0);

                    movieinfo.Year = reader.GetString(1);

                    movieinfo.Format = reader.GetString(2);

                    movieinfo.Length = reader.GetString(3);

                    movieinfo.Video = reader.GetString(4);

                    movieinfo.Audio = reader.GetString(5);

                };

                ListMovies.Add(movieinfo);

            }

        }

    }

}
```}

catch (Exception ex)

{

```yaml
Console.WriteLine("Exception: " + ex.ToString());
```}

Answer 1

Adding a counter is the easiest:

var recordCount = 0;
String connectionString = "Data Source=XPSSLOWPC;Initial Catalog=Movies;Integrated Security=True;";

using (SqlConnection connection = new(connectionString))
{
    connection.Open();
    String sql = "select Title, Year, Format, Length, Video, Audio from Info where Format = 'Ultra HD' order by Title";

    using (SqlCommand command = new(sql, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                ++recordCount;
                MovieInfo movieinfo = new MovieInfo();
                {
                    movieinfo.Title = reader.GetString(0);
                    movieinfo.Year = reader.GetString(1);
                    movieinfo.Format = reader.GetString(2);
                    movieinfo.Length = reader.GetString(3);
                    movieinfo.Video = reader.GetString(4);

                    movieinfo.Audio = reader.GetString(5);
                };

                ListMovies.Add(movieinfo);
            }
        }
    }
}

Note: you could a “select @@rowcount” to the query and it would come in the second result set.

Answer 2

If you want to know how many records you read then you already have that information. You're adding the records to a list. However many items are in the list are the # of rows you read.

using (SqlConnection...)
   using (SqlReader ...)
      while (...Read())  
         ...

//How many did you read?
int totalRecordsRead = ListMovies.Count;

If the ListMovies isn't empty when you start adding records to it then just subtract that out of the calculation.

int originalCount = ListMovies.Count;
//read the data
int totalRecordsRead = ListMovies.Count - originalCount;

You don't really need to do anything special or modify your query at all.