CODEWITHSUNDEEP
c#sqlconverters
Tristan Descartes
Tristan Descartes

Reputation: 145

What is the correct way to convert a value that is bit in Sql to Int in C#

Question: What is the right way to convert a value that is a bit in Sql to an Int in C#?

Objective: Using Sql2008R2 and C#, convert the value to integer to insert into another table.

Error: The following code returns this error. Input string was not in a correct format.

    public struct Mystruct
    {
        public Int32 Active
    }

    public static bool ReturnActivity
    {
        String sql = "";

       {
           conn.Open();

           sql = "SELECT " +
                     "database.dbo.table1.Active " +
                 "FROM " +
                     "database.dbo.tabe1 ";

           SqlCommand cmd = new SqlCommand(sql, conn);

           SqlDataReader dr = cmd.ExecuteReader();
           while (dr.Read())
           {
                MyStruct ReturnActivity = new MyStruct();

                ReturnActivity.Active = Convert.ToInt32(dr.GetValue(0).ToString());

                SomeArraylist.Add(MyStruct);

           }

                dr.Close();
                conn.Close();

                return true;
     }

Thank you in advance for any comments, suggestions or recommendations.

Upvotes: 1

Views: 6184

Answers (3)

Dave Zych
Dave Zych

Reputation: 21887

I'm assuming it's the line 

ReturnActivity.Active = Convert.ToInt32(dr.GetValue(0).ToString());

That is failing. It's failing because GetValue returns an object, and calling ToString on the object (note that it's underlying type is of type bool) is returning "True" or "False", not the 0 or 1 that you see in sql. Use GetBoolean to get it as a bool type, then check the returned value to get either 0 or 1:

ReturnActivity.Active = dr.GetBoolean(0) ? 0 : 1;

Also, there's a thing called a Verbatim String, where instead of this:

sql = "SELECT " +
      "database.dbo.table1.Active " +
      "FROM " +
      "database.dbo.tabe1 ";

You can do this:

sql = @"SELECT 
            database.dbo.table1.Active
        FROM
            database.dbo.tabe1";

Which is much easier to look at and maintain.

Upvotes: 2

JNYRanger
JNYRanger

Reputation: 7097

Normally when you pull a BIT value from a SQL database you use GetBoolean() instead of simply GetValue(). You can then simply pass a boolean value to Convert.ToInt32(bool b) instead of a string. This will return either a 0 or 1 accordingly.

There is no need to convert to a string and then back into an int.

Upvotes: 1

p.s.w.g
p.s.w.g

Reputation: 149030

Use the GetBoolean method to return the value of a bit column as a bool:

ReturnActivity.Active = dr.GetBoolean(0) ? 1 : 0;

Note that ?…: is the ternary operator. It will evaluate the boolean expression to the left of the ? and return the value to the left of the : if it is true, or the value to the right of the : if it is false.

This is roughly equivalent to:

if (dr.GetBoolean(0))
{
    ReturnActivity.Active = 1;
} else {
    ReturnActivity.Active = 0;
}

Upvotes: 3

Related Questions