How to count number of rows in SQL Server database?

Question

I am trying to count the number of unread messages in my DB Table but is proving to be very difficult. I've even read tutorials online but to no avail.

What I'm doing should be simple.

Here's what I'm trying to do:

COUNT NUMBER OF ROWS IN NOTIFICATIONSTABLE 
WHERE USERID = @0 AND MESSAGEWASREAD = FALSE

Can somebody please point me in the right direction? Any help will be appreciated.

Thank you

@helper RetrievePhotoWithName(int userid) 
{ 
    var database = Database.Open("SC"); 
    var name = database.QuerySingle("select FirstName, LastName, ProfilePicture from UserProfile where UserId = @0", userid); 
    var notifications =  database.Query("SELECT COUNT(*) as 'counter' FROM Notifications WHERE UserID = @0 AND [Read] = @1", userid, false); 

var DisplayName = ""; 
if(notifications["counter"] < 1) 
{ 
    DisplayName = name["FirstName"] + " " + name["LastName"]; 
} 
else 
{ 
    DisplayName = name["FirstName"] + ", you have " + notifications["counter"] + " new messages."; 
} 
<a href="@Href("~/Home")" title="My Account"><img src="@Href("~/Shared/Assets/Images/" + name["ProfilePicture"] + ".png")" id="MiniProfilePicture" />&nbsp;@DisplayName</a> 

database.Close();

}

kemiller2002 · Accepted Answer · 2012-05-15 13:41:27Z

8

SELECT COUNT(*) FROM NotificationsTable WHERE 
 UserID = @UserID AND MessageWasRead = 0;

Sql Count Function

Okay so this is based on what I think should be done. I don't know the underlying types, so it is going to be my best guess.

var notifications =  database.QuerySingle("Select COUNT(*) as NumRecs....");

if((int)notifications["NumRecs"] > 0)) .......

I changed the query for notifications to QuerySingle. You don't need a recordest, you only need a scalar value, so that should (hopefully remove your problem with the implicit conversion in the equals you were having.

I would also check to see if your database object implements IDisposable (place it in a using statement if so) as you are calling close, and this won't actually call close (I know it's not dispose but it might have dispose too) if you encounter and exception before the close function is called.

edited May 15, 2012 at 13:41

answered May 15, 2012 at 12:50

kemiller2002

116k28 gold badges202 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

shane Over a year ago

I was previously using: SELECT COUNT(*) AS NumberOfNotifications FROM Notifications WHERE UserId = @0 AND [Read] = @1", userid, false however that caused me so much problems I thought I was having a heart attack

shane Over a year ago

@Kevin, it still won't work. I have updated my question with code and details. I keep getting the following: "Compiler Error Message: CS0019: Operator '<' cannot be applied to operands of type 'System.Collections.Generic.IEnumerable<dynamic>' and 'int'" but I try changing var notifications = to int notifications = - but that just creates different problems. I've also read online that Counting rows in WebMastrix SQL is very problematic but the solutions have not worked.

kemiller2002 Over a year ago

ok this is a c# problem and not an sql problem. post your sql code, so i can see what the code looks like.

shane Over a year ago

You would know this is a C# problem if the other person didn't edit my original question!!! ARGH! I hate that!

shane Over a year ago

@Kevin - I have posted my full code for this helper, in my question. :)

Taryn · Accepted Answer · 2013-10-04 15:17:49Z

0

int unreadMessageCount = db.Query("SELECT * FROM Notification WHERE UserId=@0 AND Read=@1",UserId,false).Count();

string displayname = name["FirstName"] + " " + name["LastName"] + unreadMessageCount>0?",you have " + unreadMessageCount :"";

edited Oct 4, 2013 at 15:17

Taryn

249k57 gold badges375 silver badges409 bronze badges

answered Oct 4, 2013 at 14:02

Mehmet

1

Collectives™ on Stack Overflow

How to count number of rows in SQL Server database?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related