Checking if a datatable is null

Question

The following code is what I've been using to retrieve user information from a sql database.

            string userName = LoginUser.UserName;
            string password = LoginUser.Password;
            string comm = "SELECT Username,Password,Clientname,Role FROM Users WHERE Username = '" + userName + "';";
            bool rememberUserName = LoginUser.RememberMeSet;

            SqlConnection conn = new SqlConnection(connstring);
            conn.Open();

            SqlCommand command = new SqlCommand(comm, conn);
            SqlDataAdapter da = new SqlDataAdapter(command);
            DataTable dt = new DataTable();
            da.Fill(dt);
            DataRow dr = dt.NewRow();
            if (dt != null)
            {
                //logic
            }

However, (dt != null) does not return false when there is no entry in the database with the username equal to LoginUser.Username. Is there a different way to check whether or not the sqlcommand is successful?

Answer 1

Why not just change the statement a bit to see if the DataTable is either null or has no rows:

if(dt != null && dt.Rows.Count > 0)

Also, on a side note, you should look into Parameterized Queries as well rather than building your SQL dynamically. It will reduce the number of attack vectors for attackers trying to compromise your application.

Answer 2

You will get an empty DataTable if no records match, so you can check on the number of records returned:

if (dt.Rows.Count > 0)

And, slightly off topic, please read the comments below your question, then Google the terms SQL Injection and Parameterized SQL statements. Try starting with this.