How do I get SqlCommand ExecuteNonQuery result?

Question

In order to check if specific user is db_owner, i excute the following query:

"select is_rolemember('db_owner', '" & p_userName & "')"

using the SqlCommand ExecuteNonQuery method.

How do I get the query result?

Here is my code:

    Dim com As SqlCommand = New SqlCommand(sql, m_connection)               
    com.ExecuteNonQuery()

sql is the query, and m_connection is the connectionString.

Answer 1

You can use ExecuteScalar

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

like Lucero said.

EX:

cmd.CommandText = "SELECT COUNT(*) FROM dbo.region";
Int32 count = (Int32) cmd.ExecuteScalar();

Returning the Int.

Answer 2

While everyone has given the answer I would like to point out that your sql is vulnerable to injection if p_userName can in anyway be influenced by a client.

Also note that is_rolemember can return Null (See Microsoft Reference) Below is an implementation that is not vulnerable to Sql Injection (it uses parameterized sql).

Dim com As SqlCommand = New SqlCommand("select is_rolemember('db_owner', @UserName)", m_connection)
com.Parameters. AddWithValue("@UserName", p_userName)
Dim result As Object = com.ExecuteScalar
If (result = DBNull.Value) Then
   Throw New Exception("database_principal or role is not valid, or you do not have permission to view the role membership.")
Else
    Return CType(result,Int32)
End If