CODEWITHSUNDEEP
sql-serverdatabase
shane
shane

Reputation: 239

How to count number of rows in SQL Server database?

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

}

Upvotes: 0

Views: 4503

Answers (2)

Mehmet
Mehmet

Reputation: 1

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 :"";

Upvotes: 0

kemiller2002
kemiller2002

Reputation: 115440

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.

Upvotes: 8

Related Questions