Using Stored Procedure and C# to Know How Many Records are Present in a Table

In this blog we will know Using stored procedure and c# how many records are present in a table.

Method 1:

Stored procedure

CREATE PROCEDURE ccount

@RowCount int output

AS

set nocount on

select * from student

set @RowCount = @@ROWCOUNT

RETURN

Default.aspx code
 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="Count_record_stored_procedure.WebForm1" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:Label ID="Label2" runat="server" Text="Total records"></asp:Label><br />

        <asp:TextBox ID="total_count" runat="server"></asp:TextBox>

        <asp:Button ID="btn_count" runat="server" Text="Count total records" OnClick="btn_count_Click" />

    </div>

    </form>

</body>

</html>

Default.aspx.cs code
 

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

namespace Count_record_stored_procedure

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        SqlCommand com;

 

        protected void btn_count_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection(strConnString);

            con.Open();

            com = new SqlCommand("ccount", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.Add("@RowCount", SqlDbType.Int).Direction =

            ParameterDirection.Output;

            SqlDataReader reader;

            reader = com.ExecuteReader();

            reader.Close();

            total_count.Text = com.Parameters["@RowCount"].Value.ToString();

            con.Close();

        }

    }

}

Method 2:

Stored procedure

 

CREATE PROCEDURE counttotalrecords

AS

select count(*) from student

Default.aspx code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="Count_record_stored_procedure._Default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head id="Head1" runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:Button ID="btn_count" runat="server" Text="Count total records" OnClick="btn_count_Click" /><br />

        <asp:Label ID="Label2" runat="server" Text="Total records"></asp:Label><br />

        <asp:GridView ID="GridView1" runat="server">

        </asp:GridView>

    </div>

    </form>

</body>

</html>

Default.aspx.cs code
 

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

namespace Count_record_stored_procedure

{

    public partial class _Default : System.Web.UI.Page

    {

        string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        SqlCommand com;

        SqlDataAdapter sqlda;

        DataSet ds;

 

        protected void btn_count_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection(strConnString);

            con.Open();

            com = new SqlCommand("counttotalrecords", con);

            com.CommandType = CommandType.StoredProcedure;

            sqlda = new SqlDataAdapter(com);

            ds = new DataSet();

            sqlda.Fill(ds, "student");

            GridView1.DataSource = ds;

            GridView1.DataMember = "student";

            GridView1.DataBind();

            con.Close();

        }

    }

}