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