Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 C#.net
Posted 14-Jan-13 20:37pm
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

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);
 
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());
  Permalink  
Comments
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);
 
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 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";
this.DropDownList.DataBind();
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;
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 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)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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#.
  Permalink  
Comments
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.
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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.
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

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();
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

create proc countrows
as
begin
 
Declare @rowscount int
 
SELECT @rowscount= COUNT(*) FROM table_name
print @rowscount
end
 
Thanks
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 369
1 _Amy 230
2 OriginalGriff 220
3 Peter Leow 205
4 Andreas Gieriet 180
0 OriginalGriff 7,540
1 Sergey Alexandrovich Kryukov 6,412
2 Maciej Los 3,849
3 Peter Leow 3,653
4 CHill60 2,702


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 15 Jan 2013
Copyright © CodeProject, 1999-2014
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