Click here to Skip to main content
11,496,146 members (1,769 online)
The site is currently in read-only mode for maintenance. Posting of new items will be available again shortly.
See more: C# ASP.NET SQL-Server
how to count the no.of records of table in SQL server and save it in a variable from
Posted 14-Jan-13 21:37pm
use this code.
string connectionString = "Data Source=youServerHere;Initial Catalog=YourDataBase;User ID=sa; Password=123;";
System.Data.SqlClient.SqlConnection sqlConnection = new System.Data.SqlClient.SqlConnection(connectionString);
System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand("SELECT COUNT(*) FROM tblProduct");
            sqlCommand.Connection = sqlConnection;
int RecordCount = Convert.ToInt32(sqlCommand.ExecuteScalar());
2012programmer at 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 at 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 at 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 at 15-Jan-13 7:34am
my +5!
Faisalabadians at 15-Jan-13 7:38am
thanks AshishChaudha ...
2012programmer at 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 at 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);

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

SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sqlCommand);

now you have records in "records" table. use it further
2012programmer at 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 at 16-Jan-13 2:33am
hi, you can do this with below code.
DropDownList.DataSource = records;
DropDownList.DataValueField = "Id";
DropDownList.DataTextField = "Lecturetime";
2012programmer at 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 at 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;
case 1:
r1.Visible = false;
r2.Visible = false;
r1.Text = "MS-Office";
r1.Visible = true;
case 2:
r1.Visible = false;
r2.Visible = false;
r1.Text = "Hardware & Networking";
r1.Visible = true;
r2.Visible = true;
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,, 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 at 16-Jan-13 3:13am
you don't have id, so you remove this line from code

DropDownList.DataValueField = "Id";
2012programmer at 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)
The COUNT(*) function returns the number of records in a table.

SELECT COUNT(*) FROM table_name

Write down this statement in a stored procedure, assign the return value from the stored procedure to a variable in C#.
2012programmer at 15-Jan-13 3:35am
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.

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)

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.
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
int lsCountVal = Convert.ToInt32(lObjDr [0].ToString()); 
// Close DataReader
// Close Connection
con .Close();
con Dispose();
create proc countrows
Declare @rowscount int
SELECT @rowscount= COUNT(*) FROM table_name
print @rowscount


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

  Print Answers RSS
0 Dnyaneshwar@Pune 692
1 CHill60 248
2 RyanDev 230
3 Sascha Lefèvre 205
4 OriginalGriff 155
0 Sergey Alexandrovich Kryukov 10,372
1 OriginalGriff 8,871
2 Sascha Lefèvre 3,899
3 Maciej Los 3,422
4 Richard Deeming 2,600

Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 15 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100