Insert into database byte[0] as a varbinary datatype parameter

Question

This is a simple program that creates a test database with a single table containing two columns of type VARCHAR(50) and VARBINARY(8000). I want to save an empty array (byte[0]) in the varbinary column, but after program executes, the BINVAL column contains an array of 8000 null bytes instead of an empty array.

How can an empty byte array be saved in a varbinary column?

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace ConsoleApplication1
{
    internal class Program
    {
        static string connString = "Server=localhost\\SQLEXPRESS;Integrated security=True;database=master";
        
        public static void Main(string[] args)
        {
            var sqlCommandParams = new List<SqlParameter>();
            ExecuteParametrizedDbCommand("Use master; CREATE DATABASE TestDB", sqlCommandParams);
            ExecuteParametrizedDbCommand("USE TestDB; CREATE TABLE SampleData (STRVAL VARCHAR(50), BINVAL VARBINARY(8000))", sqlCommandParams);
            sqlCommandParams.Add(new SqlParameter
            {
                ParameterName = "@STRVAL",
                SqlDbType = SqlDbType.VarChar,
                Value = string.Empty
            });
            
            sqlCommandParams.Add(new SqlParameter
            {
                ParameterName = "@BINVAL",
                SqlDbType = SqlDbType.Binary,
                Value = new byte[0]
            });
            
            ExecuteParametrizedDbCommand(
                "USE TestDb INSERT INTO SampleData (STRVAL, BINVAL) VALUES (@STRVAL, @BINVAL)", 
                sqlCommandParams);
        }
        
        public static void ExecuteParametrizedDbCommand(string command, IEnumerable<SqlParameter> commandParams)
        {
            try
            {
                using( var conn = new SqlConnection(connString) )
                {
                    using( var cmd = conn.CreateCommand() )
                    {
                        cmd.CommandText = command;
                        cmd.Parameters.AddRange(commandParams.ToArray());
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch( Exception ex )
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}

Answer 1

You need to use VarBinary not Binary. I would also advise you to specify the length of the parameter.

Furthermore, you should probably just change the database you are connecting to, rather than a USE statement.

You shouldn't hard-code the connection string, put it into a settings file. Then you can use SqlConnectionBuilder to change the database to master for the CREATE DATABASE command.

And you should just use a new list of parameters for each command. You could even use a params array.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace ConsoleApplication1
{
    internal class Program
    {
       
        public static void Main(string[] args)
        {
            var connBuilder = new SqlConnectionStringBuilder(Properties.ConnectionString);
            connBuilder.Database = "master";
            using(var conn = new SqlConnection(connBuilder.ConnectionString))
                ExecuteParametrizedDbCommand(conn, "CREATE DATABASE TestDB");

            ExecuteParametrizedDbCommand("CREATE TABLE SampleData (STRVAL VARCHAR(50), BINVAL VARBINARY(8000))");
            
            ExecuteParametrizedDbCommand(
                "INSERT INTO SampleData (STRVAL, BINVAL) VALUES (@STRVAL, @BINVAL)", 
                new SqlParameter("@STRVAL", SqlDbType.VarChar, 50) {
                    Value = string.Empty
                },
                new SqlParameter("@BINVAL", SqlDbType.VarBinary, -1) {
                    Value = new byte[0]
                });
        }
        
        public static void ExecuteParametrizedDbCommand(string command, params SqlParameter[] commandParams)
        {
            using( var conn = new SqlConnection(Properties.ConnectionString) )
                ExecuteParametrizedDbCommand(conn, command, commandParams);
        }

        public static void ExecuteParametrizedDbCommand(SqlConnection conn, string command, params SqlParameter[] commandParams)
        {
            try
            {
                using( var cmd = new SqlCommand(command, conn) )
                {
                    if(commandParams != null)
                        cmd.Parameters.AddRange(commandParams.ToArray());
                    conn.Open();
                    cmd.ExecuteNonQuery();
                }
            }
            catch( Exception ex )
            {
                Console.WriteLine(ex.Message);
            }
        }
    }
}