SQL Count() function. and setting variables

Question

    public void GetUsersDetails(Guid i)
{
    StringBuilder sb = new StringBuilder();
    sb.Append("DECLARE @NumberOfThreadsByOneUser smallint;");
    sb.Append(" SET=(SELECT COUNT(t.threadID)");
    sb.Append(" FROM Threads AS t");
    sb.Append(" INNER JOIN Users AS u ON u.UsersID=t.UsersID");
    sb.Append(" WHERE u.UsersID=@UserID)");

    string myConnectionString = AllQuestionsPresented.connectionString;
    using (SqlConnection conn = new SqlConnection())
    {
        SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
        cmd.Parameters.Add("UserID", SqlDbType.UniqueIdentifier).Value = i;
        SqlDataReader dr = cmd.ExecuteReader();


 dr.Read();
     QA = (Int32.TryParse(dr["NumberOfThreadsByOneUser"].ToString(), out result3)) ? int.Parse(dr["Replies"].ToString()) : 0;
    }
}

I wrote an SQL statement and what I want to get is the number of threads submitted by a user. So I declared a smallint variable. But i am not sure about the syntax of my sql statement. I want to read from the result. The QA int property should receive that count number..

Andomar · Accepted Answer · 2011-07-02 10:28:56Z

4

Use select instead of set to assign a variable based on a query:

sb.Append(" SELECT @NumberOfThreadsByOneUser = (SELECT COUNT(t.threadID)");

To use an output variable, do not declare it in SQL, but pass it:

cmd.Parameters.Add("@NumberOfThreadsByOneUser").ParameterDirection = 
    ParameterDirection.InputOutput;

Then after the command executes, you can retrieve it:

var result = cmd.Parameters("@NumberOfThreadsByOneUser").Value

Alternatively, using the reader approach, don't declare the variable in SQL and don't pass it as a parameter.

 sb.Append(" SELECT COUNT(t.threadID) as ThreadCount");
 ....
 sb.Append(" WHERE u.UsersID=@UserID"); // <-- no closing )

And read it like:

var QA = (int) cmd.ExecuteScalar();

Or:

var read = cmd.ExecuteReader();
read.Read(); // <-- Gotta move to the first row
var QA = (int) read["ThreadCount"]; // <-- or whatever your column alias is

edited Jul 2, 2011 at 10:28

answered Jul 2, 2011 at 10:23

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

Collectives™ on Stack Overflow

SQL Count() function. and setting variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related