CODEWITHSUNDEEP
c#sqlvarbinary
Raskaroth
Raskaroth

Reputation: 659

c# and sql hexadecimal to varbinary

This has had me puzzled for quite some time, and I have not been able to find a clear answer..

Why is it that while using the Query analyzer in SQL Server 2005 express, I am able to submit a hexadecimal value eg:

UPDATE Table_A
SET    COLUMN_B = 0xabc123ff (example)
WHERE  (COLUMN_A = 'hello')

When I use that in c# it gives me the error "Incorrect syntax near COLUMN_B"

And when I make a stored procedure it still (seems to) work as well, when just opening it through visual studio..

However when I call this stored procedure in visual studio through c# I get the error: "Incorrect syntax near COLUMN_B"

===EXTRA INFO===

COLUMN_B is a varbinary(1740).

I tried receiving the input as a varchar and then converting it, but it doesn't like this either. also converting it does not work..

I have seen some queries out there that seems to do what I want, but they are rather large. How can I ensure that my c# code would handle exactly the same as when entering the data through a query?

Apologies if this seems a bit unclear, I'll provide more code later if required as I do not have it on hand currently..

UPDATE

The bit shown below is what I use for to call my sql stored procedure, this has worked fine with all my other stored procedures so far..

 using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WebDB"].ConnectionString))
                {
                    //State the Stored Proc and add Values to 'cmd' to pass to the Stored Proc
                    SqlCommand cmd = new SqlCommand("_USP_inv", conn);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("Name", C5_name.Text);
                    cmd.Parameters.AddWithValue("Inventory", inventory);

                    try
                    {
                        // Open Connection and execute Stored Proc
                        conn.Open();
                        cmd.ExecuteScalar();

                    ....
                    finally
                    {
                        if (conn.State == System.Data.ConnectionState.Open)
                        {
                            //Close connection IF open
                            conn.Close();
                        }
                    }
                }

and the stored procedure as it is now

    @Name varchar(10),
    @Inventory varbinary(1740)
AS  
    UPDATE Inventory
    SET Inventory = @Inventory
    WHERE (Name = @Name)

Upvotes: 1

Views: 1602

Answers (2)

sfuqua
sfuqua

Reputation: 5853

Your sample value is an Int64 but MSBuild is using the Int32 version of AddWithValue. 

cmd.Parameters.AddWithValue("Inventory", 0xabc123ff).DbType = DbType.Int64;

That will compile and the insert will work. However, I'm not entirely sure that's what you want. When using 0xabc123ff in Management Studio, the table loads with 0xABC123FF. But when executed through C# it loads as 0x00000000ABC123FF.

I think what we really need is a byte array. Yep, that does the trick -- saves 0xABC123FF, just like Management Studio:

byte[] j = new byte[] { 0xab, 0xc1, 0x23, 0xff };
cmd.Parameters.AddWithValue("Inventory", j);

If the array can be null, then the simplest thing to do is default the parameter to NULL in your stored procedure, and simply don't add the parameter in C# if null:

if (j != null) {   cmd.Parameters.AddWithValue("Inventory", j);   }

Upvotes: 1

Matt
Matt

Reputation: 4117

If you have to take the information as Hex then it may be worth building a small worker class to convert the data on the C# app side before passing it into SQL, this can be done by converting the hex two characters at a time into its ASCII equivalent with Int32.Parse(string, NumberStyle.HexNumber)

Not sure if this applies to your requirement but its one option!

Upvotes: 0

Related Questions