Display Count of each records from database in ASP.Net using C# and VB.Net

Hi vereato,

You need to make use of GROUP BY with SUM and COUNT function.

The make use of DataList control for displaying the result.

Take reference of the below sample and implement in your code.

SQL

DECLARE @table AS TABLE(TVSeries VARCHAR(50),Title INT,Seasons VARCHAR(50),Episodes INT)
INSERT INTO @table VALUES('Prison Break',1,'Season 1',25)
INSERT INTO @table VALUES('Prison Break',2,'Season 2',25) 
INSERT INTO @table VALUES('Prison Break',3,'Season 3',15) 
INSERT INTO @table VALUES('Prison Break',4,'Season 4',10)  
INSERT INTO @table VALUES('Quantico',1,'Season 1',10) 
INSERT INTO @table VALUES('Quantico',2,'Season 2',15)  
INSERT INTO @table VALUES('Quantico',3,'Season 3',15) 

SELECT DISTINCT TVSeries
	,SUM(Episodes) 'Series'
	,Count(Seasons) 'Seasons'
FROM @table
GROUP BY TVSeries

HTML

<asp:DataList ID="dlTvSeries" runat="server" RepeatColumns="1" CellSpacing="3" RepeatLayout="Table">
    <ItemTemplate>
        <table class="table">
            <tr>
                <td><%# Eval("TVSeries") %> <%# Eval("Seasons") %> Seasons</td>
            </tr>
            <tr>
                <td>Episodes <%# Eval("Series") %> Series<hr /></td>
            </tr>
        </table>
    </ItemTemplate>
</asp:DataList>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        string query = "DECLARE @table AS TABLE(TVSeries VARCHAR(50), Title INT, Seasons VARCHAR(50), Episodes INT)";
        query += "INSERT INTO @table VALUES('Prison Break',1,'Season 1',25)";
        query += "INSERT INTO @table VALUES('Prison Break',2,'Season 2',25)";
        query += "INSERT INTO @table VALUES('Prison Break',3,'Season 3',15)";
        query += "INSERT INTO @table VALUES('Prison Break',4,'Season 4',10)";
        query += "INSERT INTO @table VALUES('Quantico',1,'Season 1',10)";
        query += "INSERT INTO @table VALUES('Quantico',2,'Season 2',15)";
        query += "INSERT INTO @table VALUES('Quantico',3,'Season 3',15)";
        query += "SELECT DISTINCT TVSeries";
        query += "    ,SUM(Episodes) 'Series'";
        query += "    ,Count(Seasons) 'Seasons'";
        query += "FROM @table ";
        query += "GROUP BY TVSeries";
        string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter(query, con))
            {
                using (DataTable dt = new DataTable())
                {
                    sda.Fill(dt);
                    dlTvSeries.DataSource = dt;
                    dlTvSeries.DataBind();
                }
            }
        }
    }
}

VB.Net

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim query As String = "DECLARE @table AS TABLE(TVSeries VARCHAR(50), Title INT, Seasons VARCHAR(50), Episodes INT)"
        query += "INSERT INTO @table VALUES('Prison Break',1,'Season 1',25)"
        query += "INSERT INTO @table VALUES('Prison Break',2,'Season 2',25)"
        query += "INSERT INTO @table VALUES('Prison Break',3,'Season 3',15)"
        query += "INSERT INTO @table VALUES('Prison Break',4,'Season 4',10)"
        query += "INSERT INTO @table VALUES('Quantico',1,'Season 1',10)"
        query += "INSERT INTO @table VALUES('Quantico',2,'Season 2',15)"
        query += "INSERT INTO @table VALUES('Quantico',3,'Season 3',15)"
        query += "SELECT DISTINCT TVSeries"
        query += "    ,SUM(Episodes) 'Series'"
        query += "    ,Count(Seasons) 'Seasons'"
        query += "FROM @table "
        query += "GROUP BY TVSeries"
        Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As SqlConnection = New SqlConnection(conString)
            Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)
                Using dt As DataTable = New DataTable()
                    sda.Fill(dt)
                    dlTvSeries.DataSource = dt
                    dlTvSeries.DataBind()
                End Using
            End Using
        End Using
    End If
End Sub

Output