How to Add Record Count on Existing Code

Question

How to Add Record Count on Existing Code

FRANK LUJAN 40

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

Accepted answer

1 additional answer

Your answer

Answer 1

Michael Taylor 60,161

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.

FRANK LUJAN 40

I removed the @@rowcount and added the code suggested above, but I'm having a difficult time getting the results to appear on the webpage using Writeline. Here is current code. I tried with ToString() and without ToString().

public void OnGet()

{

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' or Format = 'Blu-ray' or Format = 'DVD' 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);

                }

                int totalRecordsRead = ListMovies.Count;

                Console.WriteLine(totalRecordsRead.ToString());

            }

        }

    }

}

catch (Exception ex)

{

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

}
```}

Michael Taylor 60,161 Reputation points

2025-01-10T19:18:36.1966667+00:00
That is honestly a different question then what you originally posted. Console doesn't work with web apps. It is only for console applications. If you have a web app then you need to return the data to the client either as part of an HTTP call or as part of the HTML, depending on what web framework you're using (MVC, ASP.NET WebForms, etc).

You should probably post a separate question about how to get data to show up in your web app as the code we need to see and the approach is dependent upon your web project stuff and has nothing to do with this SQL call.

See here,

MVC

Web API

WebForms
Michael Taylor 60,161 Reputation points

2025-01-10T19:21:02.8033333+00:00

For this particular code, I'd recommend you return IEnumerable<MovieInfo> and then return ListMovies from the method. Let the caller of this code (an MVC controller, API client, whatever) decide how to pass that data to the browser. Separate data access from UX concerns.

FRANK LUJAN 40

I think I'm getting close. I've managed to get the record count to appear on the webpage, but it's not incrementing like it should. What am I doing wrong in the code because it's remaining at 0 for every record? I have totalRecordsRead = ListMovies.Count + 1; inside the while clause, so I'm assuming it should increment with each pass. The website shows a 0 for every record.

BottomOfPage

public void OnGet()

{

try

{

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

    using (SqlConnection connection = new SqlConnection(connectionString))

    {

        connection.Open();

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

        using (SqlCommand command = new SqlCommand(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);

                    totalRecordsRead = ListMovies.Count + 1;

                }

            }

        }

    }

}

catch (Exception ex)

{

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

}

Bruce (SqlWork.com) 77,686 Reputation points Volunteer Moderator

2025-01-11T18:55:01.2966667+00:00
I would assume you are not binding to the correct value, but you don’t show your render code. Also the row count will be one more than actual, and if printed in each row the same value, as it calculated before the display. also it not clear why you need to calculate in the first place. You are binding to list, so you could just display the row count from the list

<div>Total Row Count: @ListMovies.Count</div>

FRANK LUJAN 40

When using the debugger, it was showing that the increments were being made. It seems the issue is when the webpage is being built. Are you referring to the webpage as "render code"? Here is that where is just add a new row to show totalrecords.

<h2>List of Movies</h2>

 <thead>

     <tr>

         <th>Title</th>

         <th>Year</th>

         <th>Format</th>

         <th>Length</th>

         <th>Video</th>

         <th>Audio</th>

     </tr>

 </thead>

 <tbody>

     @foreach(var item in Model.ListMovies)

     {

         <tr>

             <td>"@item.Title"</td>

             <td>@item.Year</td>

             <td>@item.Format</td>

             <td>@item.Length</td>

             <td>@item.Video</td>

             <td>@item.Audio</td>

         </tr>

        <tr>

            <td>@item.totalRecordsRead</td>

        </tr>

     }

 </tbody>

</table>

I also have it defined as public Int as follows:

public class IndexModel : PageModel

{

public List<MovieInfo> ListMovies = new List<MovieInfo>();

public int totalRecordsRead;

private object? movieinfo;



public void OnGet()

{

    try

    {

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

        using (SqlConnection connection = new SqlConnection(connectionString))

        {

            connection.Open();

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

            using (SqlCommand command = new SqlCommand(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);

                        totalRecordsRead = ListMovies.Count + 1;

                    }

                }

            }

        }

    }

    catch (Exception ex)

    {

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

    }

}

}

public class MovieInfo

{

public String? Title;

public String? Year;

public String? Format;

public String? Length;

public String? Video;

public String? Audio;

public int totalRecordsRead;

}

Michael Taylor 60,161 Reputation points

2025-01-12T03:24:24.09+00:00
No. You are not calculating the total items correctly. You shouldn't be incrementing anything each time through the loop. Refer back to my original code, Count is the # of records AFTER you are done looping. You don't need anything inside your loop to track anything.

public int totalRecordsRead; public void OnGet () { using (...) using (...) while (...) //DO NOT do anything with totalRecordsRead here... //Get the total count AFTER you've read all your movies in totalRecordsRead = ListMovies.Count; }

The next issue is that totalRecordsRead is a property of the model, not of MovieInfo. MovieInfo represents one movie so a record count makes no sense. Remove this property.

Inside your HTML the count will always be zero because MovieInfo doesn't have a record code. This is a property of the model. Therefore you shouldn't have it in your foreach loop but rather at the end of the loop and then it is on the model directly.

<tbody> @foreach(var item in Model.ListMovies) { <tr> <td>"@item.Title"</td> <td>@item.Year</td> <td>@item.Format</td> <td>@item.Length</td> <td>@item.Video</td> <td>@item.Audio</td> </tr> } </tbody> <tfooter> <tr> <td colspan="6">@Model.totalRecordsRead</td> </tr> </tfooter>

Notice it is a property on the model just like ListMovies. It is this property that is being set after you've read all the records. Finally you should Pascal case it like your other properties.
FRANK LUJAN 40 Reputation points

2025-01-12T16:55:17.83+00:00

Perfect, it now works! It may make sense to those that do this type of coding all the time, but to us amateurs, it takes a lot more practice. Thanks for all the help!

Answer 2

Bruce (SqlWork.com) 77,686 Volunteer Moderator

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.

FRANK LUJAN 40 Reputation points

2025-01-10T02:40:24.4933333+00:00

This seems to work when using SQL Server but not when adding the second query to this code. I don't get the second result set.
FRANK LUJAN 40 Reputation points

2025-01-10T05:08:19.1466667+00:00

How would you display the recordCount value on a web page.
Anonymous

2025-01-10T07:15:18.2966667+00:00

Hi @FRANK LUJAN , Welcome to Microsoft Q&A,

To get result sets from multiple queries, you need to process the NextResult method of the SqlDataReader. By default, the SqlDataReader will only process the first result set, so you must explicitly call NextResult to read the next result set.
FRANK LUJAN 40 Reputation points

2025-01-10T16:33:36.3166667+00:00

Deleted

FRANK LUJAN 40

I added the @@rowcount to the query and then added the reader.nextresult() property and wrote it to console, but still only the first result set appears on the webpage:

using (SqlConnection connection = new(connectionString))

{

connection.Open();

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

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

        }

        reader.NextResult();

        Console.WriteLine("\nTotal: ");

        while (reader.Read())

        {

            Console.WriteLine("\t" + reader[1]);

        }

    }

}

}

Bruce (SqlWork.com) 77,686 Reputation points Volunteer Moderator

2025-01-10T17:07:52.39+00:00

the data row is a zero based index

Console.WriteLine("\t" + reader[0]);

or better:

Console.WriteLine("\t" + reader.GetInt32(0).ToString());

but adding the extra network traffic to get the @@rowcount is much more expensive than a counter.

also can just get the list count:

var rowcount = ListMovies.Count;

Share via

How to Add Record Count on Existing Code

1 additional answer

Your answer