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

}

}

}