How to send Boolean value to an Insert stored procedure in SQL Server?

Question

I'm trying to send some values to an Insert stored procedure, here in my code:

    private void btnEnter_Click(object sender, EventArgs e)
    {
        string[] parName = new string[6];
        parName[0] = "@username";
        parName[1] = "@password";
        parName[2] = "@fName";
        parName[3] = "@lName";
        parName[4] = "@gender";
        parName[5] = "@post";
        string[] parValue = new string[6];
        string gender = (rbWoman.Checked) ? "0" : "1";
        parValue[0] = txtUserName.Text;
        parValue[1] = txtPassword.Text;
        parValue[2] = txtFName.Text;
        parValue[3] = txtLName.Text;
        parValue[4] = gender;
        parValue[5] = (cb.SelectedIndex + 1).ToString();
        int affect = _clsT.Insert_Data("sp_Insert_User", parName, parValue);
        if (affect > 0)
            MessageBox.Show("ok");
        else
            MessageBox.Show(affect.ToString());
    }

and here is Insert_Data function that calls the stored procedure and send it parameters ...

protected int executeCommand(string query, Param[] p)
{
        try
        {
            connect();
            int affect = 0;
            SqlCommand cmd = new SqlCommand(query, _con);
            cmd.CommandType = CommandType.StoredProcedure;
            int count = p.GetUpperBound(0) + 1;
            for (int i = 0; i < count; i++)
                cmd.Parameters.AddWithValue(p[i].parName, p[i].parValue);
            affect = cmd.ExecuteNonQuery();
            return affect;
        }
        catch(SqlException ex)
        {
            return ex.Number;
        }
    }

and finally here is stored procedure code:

ALTER PROCEDURE [dbo].[sp_Insert_User]
     @username varchar(20), 
     @password varchar(20),
     @fName nvarchar(20),
     @lName nvarchar(20),
     @gender bit,
     @post tinyint
AS
BEGIN
    INSERT INTO admin(username, password, fName, lName, gender, post)
    VALUES (@username, @password, @fName, @lName, @gender, @post)
END

When I execute this stored procedure with these values (j123, s123, john, smith, true, 1), the stored procedure executes normally and affected values but when I try execute my C# code, I get a 8114 error.

So I search and found reason error that because of gender value that it's type is bool.

After that I try to send 0 or 1 instead of false or true but the error still persisting.

So could you tell me some guide to eliminate this error?

Answer 1

If you read the documentation, the CLR maps the Sql Server datatype bit to the C# bool, Nullable<bool> (aka bool?) or SqlBoolean.

So ...

Given a table,

create table dbo.foo
(
  id         int          not null identity(1,1) primary key clustered ,
  name       varchar(200) not null ,
  gender     bit              null ,
  send_email bit          not null ,
)

And given a stored procedure,

create procedure dbo.InsertFoo

  @name       varchar(200) ,
  @gender     bit ,
  @send_email bit 

as

  insert dbo.foo ( name , gender , send_email )
  values ( @name , @gender , @send_email )

go

You should be able to just to say the magic words:

[Edited to note that you have to jump through a view hoops to make the nullable bool work properly with null values. Consistency is the hobgoblin of little minds.]

static int InsertFoo( string name , bool? gender , bool sendEmail )
{
  int rowcount ;

  using ( SqlConnection conn = new SqlConnection("your-connect-string-here"))
  using ( SqlCommand cmd = conn.CreateCommand() )
  {

    cmd.CommandText = @"dbo.InsertFoo"                       ;
    cmd.CommandType = CommandType.StoredProcedure            ;
    cmd.Parameters.AddWithValue( "@name"       , name      ) ;
    cmd.Parameters.AddWithValue( "@gender"     , gender.HasValue ? (object) gender : (object)DBNull.Value    ) ;
    cmd.Parameters.AddWithValue( "@send_email" , sendEmail ) ;

    conn.Open() ;
    rowcount = cmd.ExecuteNonQuery() ;
    conn.Close() ;

  }
  return rowcount ;
}

And get what you want. Here's a test case:

int rc1 = InsertFoo( "john"    , null  , false ) ;
int rc2 = InsertFoo( "jane"    , null  , true  ) ;
int rc3 = InsertFoo( "sally"   , false , false ) ;
int rc4 = InsertFoo( "rebecca" , false , true  ) ;
int rc5 = InsertFoo( "emily"   , true  , false ) ;
int rc6 = InsertFoo( "zoe"     , true  , true  ) ;

Which produces

id name    gender send_email
-- ------- ------ ----------
 1 john     NULL      0
 2 jane     NULL      1
 3 sally     0        0
 4 rebecca   0        1
 5 emily     1        0
 6 zoe       1        1

as you might expect.