C# boolean needs converted to bit for SQL Server so instead of True it needs to be 1

Question

I have a C# property which is of data type bool and when it gets set, it becomes a True or False.

However I need for it to match up with a SQL Server table column of type bit so that it is saved as a 1 or 0.

SQL Server column:

StormOut bit

C# property

public bool StormOut { get; set; }

C# SQL statement:

string querystring = "UPDATE tblSignOnOff SET StormOut = " + storm.StormOut + " WHERE id = 1902";

Otherwise currently the SQL statement in C# is

UPDATE tblSignOnOff 
SET StormOut = True 
WHERE id = 1902

Which results in an error:

Invalid column name 'True'.

Solution 1

You have missed single quotes. Change like this:

string querystring = "UPDATE tblSignOnOff SET StormOut = '" + storm.StormOut + "' WHERE id = 1902";

But an important note: You should always use parameterized queries like below. This kind of string concatenations are open for SQL Injection:

string querystring = "UPDATE tblSignOnOff SET StormOut = @StormOut WHERE id = @id";
yourCommand.Parameters.AddWithValue("@id", 1902);
yourCommand.Parameters.AddWithValue("@StormOut", storm.StormOut);

Solution 2

You should use parameters and avoid string concatenation

string Command = "UPDATE tblSignOnOff SET StormOut @StormOut WHERE id = @id";
using (SqlConnection mConnection = new SqlConnection(ConnectionString))
{
    mConnection.Open();

    using (SqlCommand myCmd = new SqlCommand(Command, mConnection))
    {
        myCmd.Parameters.AddWithValue("@id", 1902); // TODO set this value dynamically
        myCmd.Parameters.AddWithValue("@StormOut", storm.StormOut);

        int RowsAffected = myCmd.ExecuteNonQuery();
    }
}

Comments

[Admin]

Ok cool, I thought I was going to have to do "Convert.ToInt16 your Boolean value. That will make true/false, 0 or 1 "

[Corak]

Yes to: "use parameterized queries"! Don't ever build sql statements by simply concatenating strings/values.

[Admin]

What is difference from Add vs AddWithValue ?

[Jon Skeet]

I'd personally avoid AddWithValue, specify the type directly and use the Value property, but this is at least better than the original code...

[Byyo]

stackoverflow.com/questions/21110001/…

[Admin]

@JonSkeet I don't follow you. use Value property ? example?

[Byyo]

@Jon is this just a personal thing or are there good reasons to avoid AddWithValue

[Jon Skeet]

@Byyo: It avoids using inferences you don't want. See blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… for example.

[Jon Skeet]

@MillRunner: command.Parameters.Add("@id", SqlDbType.Int).Value = customerID;

[Byyo]

that was rude copy & paste

[Irshad]

@Byyo If you really think that I copy paste your answer, tell me, I'll remove mine..