Getting user count from database

Is the following code using good practices? All I want to do is get a count of the number of rows which any given user has:

objSQLCommand = New SqlCommand("select count(id) as record_count from table1 where user = @strUser", objSQLConnection)
objSQLCommand.Parameters.Add("@strUser", SqlDbType.VarChar, 3).Value = strUser

objSQLCommand.Connection.Open()
objSQLDataReader = objSQLCommand.ExecuteReader()
objSQLDataRead开发者_开发问答er.Read()

intRecordCount = objSQLDataReader("record_count")

objSQLDataReader.Close()
objSQLCommand.Connection.Close()

---

I would suggest:

• Converting the command to a stored procedure

• Have the SP return the count as its only output

• Then use ExecuteScalar (no need for a reader)

• Wrap using around the connection and command to dispose properly.

---

Since you're returning a single value you could just use ExecuteScalar instead of the reader.

See sample here.

---

Why not use LINQ to Entities? Your code has a time warp feel to it...

---

Its not terrible practice, as per what @amelvin and @ChrisW said. And...

you could wrap up your sql query in a Stored Procedure, just two lines are different:

objSQLCommand = New SqlCommand("GetCountByUser", objSQLConnection);
objSQLCommand.CommandType = CommandType.StoredProcedure;

On a design note, you could probably keep the user reference as an ID rather than using a VarChar for each row:

IE

UsersTable
ID       Name               Email
1       Oshirowanen     Oshirowanen@hotmail.com
2        5arx                   5arx@test.com
3        JeffA                jeffattwood@stackexchange.com

RecordTable
ID        UserID    OtherColumns
1234            1    Oshirenin 1
1235            1    Oshirenin 2
1236            2    5arx's column #1
1237            3    Jeff Attwood was here
1238            2    Another one of 5arx's column

So you could call (in stored proc or inline SQL) it with the numeric ID of the user. This approach stops you duplicating the username in every row of the record table.