CODEWITHSUNDEEP
c#sqlado.net
amin sedaghaty
amin sedaghaty

Reputation: 97

which sql query is better for bit type?

I have a method to get the count of usernames with the lock flag in database

public bool UsernameExistInDatabase(string username)
{
    bool states = false;
    using (SqlConnection con = new SqlConnection(this._connectionString))
    {
        string query = "SELECT COUNT(username) FROM tbl_sample WHERE (lock = '1')";
        SqlCommand cmd = new SqlCommand(query, con);
        con.Open();
        int result = Convert.ToInt32(cmd.ExecuteScalar());
        con.Close();
        if (result > 0)
        {
            states = true;
        }
    }
    return states;
}

but I have three different ways

SELECT COUNT(username) FROM tbl_sample WHERE (lock = '1')
SELECT COUNT(username) FROM tbl_sample WHERE (lock = 1)
SELECT COUNT(username) FROM tbl_sample WHERE (lock = 'true')

which one should I choose for my code (i.e. which one is better)

table structure running 3 query and same result

Upvotes: 0

Views: 311

Answers (4)

n8wrl
n8wrl

Reputation: 19765

I suggest =1 and I can't resist rewriting your method to use using all the way...

public bool UsernameExistInDatabase(string username)
{
    using (SqlConnection con = new SqlConnection(this._connectionString))
    {
        string query = "SELECT COUNT(username) FROM tbl_sample WHERE (lock = 1)";
        con.Open();
        using (SqlCommand cmd = new SqlCommand(query, con))
        {
            int result = Convert.ToInt32(cmd.ExecuteScalar());
            if (result > 0)
            {
                // No worries about con.Close()! Using will handle it
                return true;
            }
        }
    }
    return false;
}

I have to say the method name itself confuses me - we're clearly looking for whether a user exists in the database and is locked? Are we saying the user exists in the DB as the method name suggests?

Upvotes: 0

Jamie Pollard
Jamie Pollard

Reputation: 1599

You should always make it clear to the next person who has to deal with your code what your intentions are. In this case your column is a non null-able bit column, which has the potential values 0 or 1 (ignoring any conversions).

SELECT COUNT(username) FROM tbl_sample WHERE lock = 1

Is your best way here - no strange internal conversions and no confusion about what your doing.

Upvotes: 0

Xiaoy312
Xiaoy312

Reputation: 14477

Use where lock = 1; '1' and true both will occur an unnecessary conversion. Also, 'true' is incorrect.

bit

An integer data type that can take a value of 1, 0, or NULL.

remarks

The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Src : https://msdn.microsoft.com/en-IN/library/ms177603.aspx

Upvotes: 1

Mark Rabjohn
Mark Rabjohn

Reputation: 1713

Oddly enough, since there's loads of ways to indicate true, yet most DB engines reliably treat Zero as false, I generally factor true to be NOT False as follows:

SELECT COUNT(username) FROM tbl_sample WHERE (lock <> 0)

Obviously if NULL is an issue and you expect NULL to read as false, you'd have to do the following:

SELECT COUNT(username) FROM tbl_sample WHERE (lock IS NOT NULL AND lock <> 0)

Upvotes: 1

Related Questions