SQL varbinary column to byte array in c# fails when result set is passed in as XML

Question

I am coding a method which will extract raw data from the database in two methods. The first method is a c# application (.Net 4.6) which extracts data from SQL (Server 2015). Code is as below and it works well

            // Create a list of unpackConfig object.
            List<UnpackConfig> listToUse = new List<UnpackConfig>();

            // Check if SQL Connection is still open.
            if (conn.State == ConnectionState.Open)
            {

                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "SP_GetOutputsForUnpackingByBatchID";

                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter param = new SqlParameter("@BatchID", BatchID);
                param.Direction = ParameterDirection.Input;
                param.DbType = DbType.String;
                cmd.Parameters.Add(param);

                using (SqlDataReader rdr = cmd.ExecuteReader())
                {

                    while (rdr.Read())
                    {
                        // Initialise our class.
                        UnpackConfig uc = new UnpackConfig();


                        //Get the OutputID and Assign.
                        uc.OutputId = rdr.GetInt64(ApplicationConstants.ord_OutputID);

                        //Get the runID and Assign.
                        uc.RunId = rdr.GetInt64(ApplicationConstants.ord_RunId);

                        // Get the instrument Output Names.
                        // Check if we are dealing with lab data.
                        bool isLd = rdr.GetBoolean(ApplicationConstants.ord_isLD);

                        byte[] ioNames = null;
                        if (!isLd)
                        {

                            long ioNamesArraySize = rdr.GetBytes(ApplicationConstants.ord_IONames, 0, null, 0, 0);
                            ioNames = new byte[ioNamesArraySize];
                            ioNames = (byte[])rdr[ApplicationConstants.ord_IONames];

                            // Assign Instrument Output Names.
                            uc.InstOutputNames = ioNames;
                        }
                        else
                        {
                            // We are dealing with lab data instrument output names is not applicable.
                            uc.InstOutputNames = null;
                        }

                        // Get the IsLabData flag and Assign.  
                        uc.IsLabData = isLd;

                        // Get the data.
                        byte[] outputs = null;
                        long outputsArraySize = rdr.GetBytes(ApplicationConstants.ord_RawData, 0, null, 0, 0);
                        outputs = new byte[outputsArraySize];
                        outputs = (byte[])rdr[ApplicationConstants.ord_RawData];

                        // Assign the raw data.
                        uc.RawData = outputs;

                    }

                }
            }

The second method is from SQL using a stored procedure which calls a CLR method. This does the same as above first method but the byte[] array conversion fails. In SQL the column which stores the byte data is of type varbinary

SQL

In my SQL I get the table result into a variable and pass that variable into CLR

-- @temp_table_outputs_local has the outputs filtered by batchID. As we are calling from sql we do not need to use sql reader in c#. 

-- Covert the raw outputs extracted into a xml
Declare @extractedRawOutputs nvarchar(max)

-- Assign the raw outputs extracted to our variable which stores XML string
set @extractedRawOutputs = (select * from ' + @temp_table_outputs_local + ' for XML PATH(''Node''), ROOT(''Root''))

-- Call CLR with extractedRawOutputs

CLR / C# Code

DataSet tt = new DataSet();
                        tt.ReadXml(new StringReader(extractedRawOutputs));

                        DataTable rawOutputs = new DataTable();
                        // Target table (tt) will alway have only one table.
                        // TODO might be good to put the number in application constants.
                        rawOutputs = tt.Tables[0];

     // Create a list of unpackConfig object.
            List<UnpackConfig> listToUse = new List<UnpackConfig>();

            foreach (DataRow row in rawOutputs.Rows)
            {
                // Initialise our class.
                UnpackConfig uc = new UnpackConfig();

                //The 0 stands for "the 0'th column and so on", so the first column of the result.
                // Do somthing with this rows string, for example to put them in to a list

                //Get the OutputID and Assign.
                uc.OutputId = Convert.ToInt64(row[ApplicationConstants.ord_OutputID]);

                //Get the runID and Assign.
                uc.RunId = Convert.ToInt64(row[ApplicationConstants.ord_RunId]);

                // Get the instrument Output Names.
                // Check if we are dealing with lab data.
                bool isLd = Convert.ToBoolean(Convert.ToInt32(row[ApplicationConstants.ord_isLD]));

                // NOTE:  All the above assigns work.

                Byte[] ioNames = null;
                if (!isLd)
                {
                    ioNames = Encoding.ASCII.GetBytes(row[ApplicationConstants.ord_IONames].ToString());
                    // NOTE: The below statement raises the exception cannot convert system.string to sytem.byte[] blah blah..
                    ioNames = (Byte[])row[ApplicationConstants.ord_IONames];

                    // Assign Instrument Output Names.
                    uc.InstOutputNames = ioNames;
                }
                else
                {
                    // We are dealing with lab data instrument output names is not applicable.
                    uc.InstOutputNames = null;
                }

                // Get the IsLabData flag and Assign.  
                uc.IsLabData = isLd;

                // Get the data.
                byte[] outputs = null;
                // NOTE: the below works however the data is different from what the first method is fetching. Even the array size is different.
                outputs = Encoding.ASCII.GetBytes(row[ApplicationConstants.ord_RawData].ToString());

                // Assign the raw data.
                uc.RawData = outputs;               

What I do next with the data is

// Extract the InstrumentOutputNames and Outputs.
                object[][] data = null;
                // NOTE : The below statement fails with conversion error. 
                // The data is not a valid binary data blah blah..
                data = outputs.DeserializeFromByteArray<object[][]>();
                var iPms = ioNames.DeserializeFromByteArray<IEnumerable<InstrumentParameter>>();
                iPms = iPms.ToList();

-- The Method --

/// <summary>
        /// Deserializes the specified byte array.
        /// </summary>
        /// <typeparam name="T">Type to Use</typeparam>
        /// <param name="byteArray">The byte array.</param>
        /// <returns>T.</returns>
        public static T DeserializeFromByteArray<T>(this byte[] byteArray)
        {
            using (var ms = new MemoryStream(byteArray))
            {
                return (T)new BinaryFormatter().Deserialize(ms);
            }
        }

Question

How do i create the byte array in the second method which equates the same as the first method. Hope i have explained correctly. Please do not hesitate to ask for further information.

Answer 1

You cannot cast a string to Byte[] directly...

Try something like

Encoding.Unicode.GetBytes(YourString);

Read more

But admittably there are some open questions, so may be I got something wrong...

This line

set @extractedRawOutputs = (select * from ' + @temp_table_outputs_local + ' for XML PATH(''Node''), ROOT(''Root''))

seems to be taken out of dynamically created SQL, but that cannot be complete...

Your problem might be bound to C# string, which is unicode, and SQL string, which must be specifically declared as unicode (leading N).