how to count the no.of records of table in SQL server and save it in a variable from C#.net

Thanks Vani Kulkarni for early response.Kindly let me know how to write a stored procedure. I am new to programming. Can any body tell complete code. Thanks in advance.

Solution 2

Hi,

Select count(*) from table_name will take too much time on large data. If you want it realy fast then you should use:

SELECT    Total_Rows= SUM(st.row_count) FROM sys.dm_db_partition_stats st WHERE object_name(object_id) = 'TableName' AND (index_id < 2)

RKS

Posted 14-Jan-13 22:16pm

Rahul K Singh

Solution 4

SQL

DB side Part

Declare @TotalCnt Int

SELECT @TotalCnt = COUNT(*) FROM table_name

Select @TotalCnt AS TotalCount

On C# side Store the output in variable using command Execute scalar.

Posted 14-Jan-13 22:30pm

Hitesh R

Updated 15-Jan-13 0:50am

v2

Solution 5

C#

String lsSqlStr = "Select Count(ColumnName) From TableName"
SqlCommand lObjCmd = New SqlCommand(lsSqlStr con)
// con defines the connection string

SqlDataReader lObjDr = New SqlDataReader(); 
lObjDr = lObjCmd.ExecuteReader();

// Open DataReader to Read the Data
 lObjDr.Read();

int lsCountVal = Convert.ToInt32(lObjDr [0].ToString()); 

// Close DataReader
lObjDrClose();
lObjDr.Dispose();

// Close Connection
con .Close();
con Dispose();

Posted 15-Jan-13 0:44am

KManishS

Updated 15-Jan-13 0:50am

v2

Solution 7

SQL

create proc countrows
as
begin

Declare @rowscount int

SELECT @rowscount= COUNT(*) FROM table_name
print @rowscount
end

Thanks

Posted 15-Jan-13 0:52am

AshishChaudha

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Faisalabadians · Accepted Answer · 2013-01-15T00:46:00

Solution 6

use this code.

C#

string connectionString = "Data Source=youServerHere;Initial Catalog=YourDataBase;User ID=sa; Password=123;";

System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);

sqlConnection.Open();
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM tblProduct");
            sqlCommand.Connection = sqlConnection;
 
int RecordCount = Convert.ToInt32(sqlCommand.ExecuteScalar());

Posted 15-Jan-13 0:46am

Faisalabadians

Comments

2012programmer 15-Jan-13 7:17am

Thank you very much Every body.This code is working.I need some more help. I was saving the selected list items of a dropdown list into column of a database table.My requirement is to count the records with each "list item". If there are 10 records with one "listitem" then that listitem should be removed from the dropdown list.
The above code is working fine for one list item, But I need for 7 list items. Do I need to write the stored procedure or I need to write select statements 7 times.

Faisalabadians 15-Jan-13 7:23am

as you are saying you have stored it in database and if there are 10 records for any particular list item, then you must use stored procedure to do that and return only those record, which don't have a count of 10.

2012programmer 15-Jan-13 7:58am

Yes Exactly. Kindly let me know the code for stored procedure and C# code. My table name is "Register", Column Name is "Lecturetime", List Items are(1.Monday,2.Tuesday, 3.Wednesday, 4.Thursday, 5.Friday, 6.Saturday, 7.Sunday). Thanks in advance "Faisaladians".

AshishChaudha 15-Jan-13 7:34am

my +5!

Faisalabadians 15-Jan-13 7:38am

thanks AshishChaudha ...

2012programmer 15-Jan-13 8:24am

Kindly let me know the code for stored procedure and C# code. My table name is "Register", Column Name is "Lecturetime", List Items are(1.Monday,2.Tuesday, 3.Wednesday, 4.Thursday, 5.Friday, 6.Saturday, 7.Sunday). Thanks in advance "Faisaladians".

Faisalabadians 15-Jan-13 10:00am

hi, first create stored procedure by running this script

CREATE procedure GetListRecordNotHavingTenCount as

SELECT Lecturetime
FROM Register
GROUP BY Lecturetime
HAVING ( COUNT(Lecturetime) < 10 )

after that, use this code to get records which have count less then 10 in the database.

DataTable records = new DataTable();
string connectionString = "Data Source=YourDataServer;Initial Catalog=YourDataBase;Integrated Security=True";
SqlConnection sqlConnection = new SqlConnection(connectionString);

sqlConnection.Open();
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = "GetListRecordNotHavingTenCount";
sqlCommand.Connection = sqlConnection;

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(records);

now you have records in "records" table. use it further

2012programmer 16-Jan-13 2:26am

Hi, Thank you once again for your great support. Kindly let me know how to append these "records" table values as dropdown list item. Initially if there are no records in the database, then what values it will append to the dropdown list.

Faisalabadians 16-Jan-13 2:33am

hi, you can do this with below code.
DropDownList.DataSource = records;
DropDownList.DataValueField = "Id";
DropDownList.DataTextField = "Lecturetime";
this.DropDownList.DataBind();

2012programmer 16-Jan-13 2:53am

What is "Id" here in thi code?. I am getting this error "DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'Id'."

2012programmer 16-Jan-13 3:12am

I am creating a web based application (Student Registration Form). In this Web Page I have 1 dropdown list (which shows the lecturetimings like Saturday, Sunday, Monday, Tuesday,........etc). After selecting a list item(ex: Saturday It will show availble courses as Radio buttons (Example:MS-Office,C#.Net). In the selected index change event of drop down list-I have written a switch case: for every different casecase 0:
r1.Visible = false;
r2.Visible = false;
break;
case 1:
r1.Visible = false;
r2.Visible = false;
r1.Text = "MS-Office";
r1.Visible = true;
break;
case 2:
r1.Visible = false;
r2.Visible = false;
r1.Text = "Hardware & Networking";
r2.Text="C#.net";
r1.Visible = true;
r2.Visible = true;
break;
...........
Upto case7 written.
Then I have some text boxes for (StudentName, EmailId, ContactNo.& address0
I have checkbox(I agree terms & Conditions)
There is a submit button( on clicking this, the information wil be saved in data base table registration(StudentName,EmailId,ContactNo.,Address, Lecturetimings, Course)Ex:(John, john@john.com, 9999999999, India, Saturday,MS-Office).
Now My requirement is if there 10 records with Saturday then in the dropdown list saturday should not be shown. Thanks in advance for taking pains for me.

Faisalabadians 16-Jan-13 3:13am

you don't have id, so you remove this line from code

DropDownList.DataValueField = "Id";

2012programmer 16-Jan-13 3:29am

Ok.This is working well.It showing 2 items in dropdown list as there entries with Saturday and Sunday.Intially if there are no records in the table then it should show all the items in the list like (Monday,.....Saturday).
And One more problem is its not showing the radio buttons when I select any list item(after applying this code)

Member 14793808 28-Jul-20 11:54am

I am creating a web based application, i want to show date-wise percentage of attendance students base of absent and present. how can I do it?
i have two date of calendar for selection date duration(From date and To date), and some drop down lists of (semester,program,course,shift), I want to show percentage Base of selected dates with student record.Thanks in advance for taking pains for me.