datatable not accepting the value of varbinary

Question

the value buf has a datatype varbinary(max) and the value is 0x0000002D

string buF =
    "0x" + BitConverter.ToString((byte[])dt.Rows[i]["BuF"]).Replace("-", "");
    Label3.Text = buF;

i use the value to find the fileid

DataTable dt = new DataTable();
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = ConfigurationManager.ConnectionStrings["XYZ"].ConnectionString;
        connection.Open();
        SqlCommand sqlCmd = new SqlCommand("SELECT FileID FROM Backed where MachineID = @machineID  AND BuF =@buF", connection);
        SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
        sqlCmd.Parameters.AddWithValue("machineID", strID);
        sqlCmd.Parameters.AddWithValue("buF", buF);
        sqlDa.Fill(dt);
        connection.Close();

i does not use the value of buf and i dont get the correct filid....

but if i use this with the value0x0000002D instead of buf i get the file id... why is this happening... i tried everything but nothing seems to work

i need help

Answer 1

AddWithValue makes a parameter of the type it sees. When you pass a long it uses a number, when a string it uses the ascii. Try this: sqlCmd.Parameters.AddWithValue("buF",long.Parse(buF));

Answer 2

The equivalent type for varbinary in C# is byte[], not string. Also, as Hogan said, AddWithValue tries to assume a data type. Instead, you can make it explicit:

sqlCmd.Parameters.Add("buF", SqlDbType.VarBinary, -1).Value = yourByteArray;

The -1 for length corresponds to varbinary(max).

Answer 3

It would be better to work with the binary data as byte[] and not use strings and string conversions if you don't have to. Here is a complete example that should work to demonstrate how to read and query varbinary(max) types.

static void Test(SqlConnection openConnection)
{
    using(SqlCommand cmd = openConnection.CreateCommand())
    {
        cmd.CommandText =
            @"create table #Test 
            (bin varbinary(max), num int);
            insert into #Test (bin, num) 
            values (0x0000002D, 1);";
        cmd.ExecuteNonQuery();

        cmd.CommandText = "SELECT TOP 1 bin FROM #Test;";
        byte[] binValue = (byte[])cmd.ExecuteScalar();

        cmd.CommandText = "SELECT * FROM #Test WHERE bin = @bin;";
        var parameter = new SqlParameter("@bin", SqlDbType.VarBinary, -1);
        cmd.Parameters.Add(parameter);
        parameter.Value = binValue;

        DataTable table = new DataTable();
        using (var reader = cmd.ExecuteReader())
        {
            table.Load(reader);
        }

        Debug.Assert(table.Rows.Count == 1);
    }
}