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?

Agree with @MarcGravell - read about SQL injection. Also, accept some answers. — infojolt, Sep 10 '12 at 20:32
You may also want to accept a few answers to the 6 questions you've already asked; it really helps the site work better when people give you good answers and they are rewarded for it. — tmesser, Sep 10 '12 at 20:32
It's just a sample database while i'm learning how to do login authentication. I'll get to security later. — Tony, Sep 10 '12 at 20:34
If you are learning to do login authentication why not learn to do it properly? — infojolt, Sep 10 '12 at 20:42

score 37 · Accepted Answer · answered Sep 10 '12 at 20:31

37

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.

answered Sep 10 '12 at 20:31

driis

161,458
45
265
341

I was in the middle of typing this when I got the notification that you beat me to it. :) – Ari Roth Sep 10 '12 at 20:35
3

Don't use rows.Count. That's asking for how many rows exist. If there are many, it will take some time to count them. All you really want to know is "is there at least one?" You don't care if there are 10 or 1000 or a billion. You just want to know if there is at least one. If I give you a box and ask you if there are any marbles in it, will you dump the box on the table and start counting? Of course not. FROM https://stackoverflow.com/questions/6264554/how-to-check-empty-datatable bool hasRows = dataTable1.Rows.Any() – ger Nov 20 '17 at 19:23

score 15 · Answer 2 · answered Sep 10 '12 at 20:31

15

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.

answered Sep 10 '12 at 20:31

Justin Niessner

242,243
40
408
536

2

`dt` was explicitly set to be a `new DataTable()`; will it *ever* be null? – KeithS Sep 10 '12 at 20:32
@KeithS - No. But I'm also not sure that the code above is a sample or the complete code. In either case, it's not hurting anything. – Justin Niessner Sep 10 '12 at 20:35

score 2 · Answer 3 · answered Mar 17 '19 at 18:48

As of C# 6.0 you can use the Null conditional operator ?. (or ?[] for arrays).

The null conditional operator simplifies the statement to:

if (dt?.Rows?.Count > 0)

This returns false when:

the data table dt is null
data table Rows dt.Rows is null
the number of rows dt.Rows.Count is 0

Using the null conditional operator you can avoid manually checking both the data table and count properties, eg if (dt != null && dt.Rows.Count > 0)

score 1 · Answer 4 · answered Sep 10 '12 at 21:01

It seems to me using a DataTable and SqlDataAdapter is a little bit too heavy for the task.

You can just use a DataReader here:

        SqlCommand command = new SqlCommand(comm, conn);
        using (var reader = command.ExecuteQuery()) 
        {
            if (reader.Read())
            {
                //logic
                var userName = reader.GetString(0);
                var password = reader.GetString(1);
                // etc
            }
        }

score 0 · Answer 5 · edited Nov 03 '14 at 14:10

0

It will be better if you use try catch for checking whether the table is empty or not , just handle the IndexOutOfRangeException exception.

Use the following logic:

try
{
   //dataTable operations
}
catch(IndexOutOfRangeException)
{
}

For me its working.

edited Nov 03 '14 at 14:10

Paul Williams

16,585
5
47
82

answered Nov 29 '13 at 16:58

Arun Agarwal

787
6
10

score 0 · Answer 6 · answered Oct 03 '18 at 10:27

0

For DataSet you can check like this:

if (ds.Tables[0].Rows.Count > 0)

answered Oct 03 '18 at 10:27

BinaryTie

281
1
21
49

score 0 · Answer 7 · answered Feb 25 '19 at 20:25

0

I know this question IS OLD and the answers helped at the moment it was published, but there is an easy way to solve this right now as follows:

if ((dataTableName?.Rows?.Count ?? 0) > 0)

answered Feb 25 '19 at 20:25

YeinCM-Qva

141
2
15

Checking if a datatable is null

7 Answers7

Linked