Using stored procedure and c# how will we know how many records are present in a table [Resolved]

Posted by Srinibaschampati under ASP.NET on 8/22/2013 | Points: 10 | Views : 1473 | Status : [Member] | Replies : 4
Using stored procedure and c# how will we know how many records are present in a table in asp.net




Responses

Posted by: Satyapriyanayak on: 8/22/2013 [Member] [MVP] Silver | Points: 50

Up
0
Down

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


If this post helps you mark it as answer
Thanks

Srinibaschampati, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Satyapriyanayak on: 8/22/2013 [Member] [MVP] Silver | Points: 25

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


If this post helps you mark it as answer
Thanks

Srinibaschampati, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/22/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
-- Procedure looks like below to return total number of records in a table
CREATE PROCEDURE USP_GetCount(@P_count int OUTPUT)

AS
BEGIN
SELECT @P_count = COUNT(*) FROM TableName
END


C# code to call procedure and return output value from that procedure
            string strConnString = "youConnectionString";

SqlConnection con = new SqlConnection(strConnString);
con.Open();
SqlCommand com = new SqlCommand("USP_GetCount", con);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add("@RowCount", SqlDbType.Int).Direction =
ParameterDirection.Output;

SqlDataReader reader = com.ExecuteReader();
reader.Close();
total_count.Text = com.Parameters["@RowCount"].Value.ToString();
con.Close();

highlighted part is for getting output parameter value from a stored procedure

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Srinibaschampati, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kmandapalli on: 8/22/2013 [Member] Silver | Points: 25

Up
0
Down
Hi,

You can simply execute the following query:
CREATE PROC GetCount
AS
BEGIN
select count(*) from table_name
END

In the front-end ie, in your code behind file you can call the stored proc as
SqlConnection con = new SqlConnection();
con.Open();
SqlCommand cmd = new SqlCommmand("GetCount", con);
int result = cmd .ExecuteNonQuery();

Thats it...

Kindly Mark as answer if satisfied.....


Regards,
Shree M.

Kavya Shree Mandapalli

Srinibaschampati, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response