Using stored procedure how will we know how many records are present in a table

Satyapriyanayak
Posted by Satyapriyanayak under SQL Server category on | Points: 40 | Views : 879
We will know how many records are present in a table using stored procedure. In the 1st method we use SqlDataReader, when we will click the count button total no of records will be shown in textbox. In 2nd method we use dataset when we will click the count button total no of records will be shown in a gridview.
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 runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" 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 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="Label1" 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();
}
}
}

Comments or Responses

Login to post response