CODEWITHSUNDEEP
c#sql-server
Ian Best
Ian Best

Reputation: 530

Is there a way to insert a C# string into a SQL varbinary column?

I am using SqlConnection and SqlCommand in C# to insert rows of strings (via Parameters.AddWithValue) into SQL Server. One of the columns I need to insert into is a Varbinary. Is there any way to do the conversion in either C# or SQL Server?

Upvotes: 1

Views: 3161

Answers (1)

mgnoonan
mgnoonan

Reputation: 7200

According to this answer, you can use the CONVERT function to insert a string into a VARBINARY column. Make sure you use the proper encoding, as discussed in that answer.

insert Table_2 (Test) values( CONVERT(varbinary(30), N'this is a test') ) 
select * from Table_2
select CONVERT(nvarchar(30), test) from Table_2

So the C# code would look something like

    // Get from config
    string connectionString = "";

    using (var conn = new SqlConnection(connectionString))
    {
        string sql = "insert Table_2 (Test) values( CONVERT(varbinary(30), @nvarcharParam) )";
        using (var cmd = new SqlCommand(sql, conn))
        {
            var param = cmd.Parameters.AddWithValue("nvarcharParam", "This is a test");
            param.DbType = DbType.String;

            cmd.ExecuteNonQuery();
        }
    }

Upvotes: 2

Related Questions