Recursive counter

[Sam Collett]

I am looping through a table to get the number of replies to a post on
a discussion system. 'posID' is the primary key (i.e. thread id),
'posParent' is the parent thread. However, it only seems to go down one
level (i.e. direct replies, but not replies of replies)
private int ReplyCount(int Id, int replies) {
SqlConnection SqlConn = new SqlConnection(SqlConnString);
// get child posts SQL statement
string SQL = "SELECT * FROM post WHERE posParent = @posId";
SqlDataAdapter SqlDa = new SqlDataAdapter();
// select command
SqlDa.SelectCommand = new SqlCommand(SQL, SqlConn);
// add sql parameter to select command
SqlDa.SelectCommand.Parameters.Add(new SqlParameter("@posId", Id));
DataSet childPosts = new DataSet();
SqlDa.Fill(childPosts, "Child");
// increment replies counter
replies+=(int)childPosts.Tables["Child"].Rows.Count;
if (childPosts.Tables["Child"].Rows.Count > 0) {
for (int i = 0; i < childPosts.Tables["Child"].Rows.Count; i++)
{
ReplyCount((int)childPosts.Tables["Child"].Rows[i]["posID"],replies);
}
}
return replies;
}

[bruce barker]

you are not including the child count, try:

for (int i = 0; i < childPosts.Tables["Child"].Rows.Count; i++)
{
replies +=
ReplyCount((int)childPosts.Tables["Child"].Rows[i]["posID"],replies);
}
note: this is a very slow way to count. you should write a sql stored proc
to do it.

-- bruce (sqlwork.com)
"Sam Collett" <sa*********@gmail.com> wrote in message
news:10**********************@f14g2000cwb.googlegr oups.com...

I am looping through a table to get the number of replies to a post on
a discussion system. 'posID' is the primary key (i.e. thread id),
'posParent' is the parent thread. However, it only seems to go down one
level (i.e. direct replies, but not replies of replies)
private int ReplyCount(int Id, int replies) {
SqlConnection SqlConn = new SqlConnection(SqlConnString);
// get child posts SQL statement
string SQL = "SELECT * FROM post WHERE posParent = @posId";
SqlDataAdapter SqlDa = new SqlDataAdapter();
// select command
SqlDa.SelectCommand = new SqlCommand(SQL, SqlConn);
// add sql parameter to select command
SqlDa.SelectCommand.Parameters.Add(new SqlParameter("@posId", Id));
DataSet childPosts = new DataSet();
SqlDa.Fill(childPosts, "Child");
// increment replies counter
replies+=(int)childPosts.Tables["Child"].Rows.Count;
if (childPosts.Tables["Child"].Rows.Count > 0) {
for (int i = 0; i < childPosts.Tables["Child"].Rows.Count; i++)
{
ReplyCount((int)childPosts.Tables["Child"].Rows[i]["posID"],replies);
}
}
return replies;
}

[Sam Collett]

Seems I don't need the second parameter (replies):
private int ReplyCount(int Id)

Loop then becomes:
if (childPosts.Tables["Child"].Rows.Count > 0) {
for (int i = 0; i < childPosts.Tables["Child"].Rows.Count; i++)
{
replies +=
ReplyCount((int)childPosts.Tables["Child"].Rows[i]["posID"]);
}
}

Not the most efficient way, but I don't expect too many replies (less
than 20). How would this be done via a stored procedure, and would it
really make a difference?