title: SQL varbinary column to byte array in c# fails when result set is passed in as XML
body: <p>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</p>
  
  <pre><code>            // Create a list of unpackConfig object.
              List&lt;UnpackConfig&gt; listToUse = new List&lt;UnpackConfig&gt;();
  
              // 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;
  
                      }
  
                  }
              }
  </code></pre>
  
  <p>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 </p>
  
  <h2>SQL</h2>
  
  <p>In my SQL I get the table result into a variable and pass that variable into CLR</p>
  
  <pre><code>-- @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''))
  </code></pre>
  
  <p>-- Call CLR with extractedRawOutputs</p>
  
  <h2>CLR / C# Code</h2>
  
  <pre><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&lt;UnpackConfig&gt; listToUse = new List&lt;UnpackConfig&gt;();
  
              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;               
  </code></pre>
  
  <p>What I do next with the data is</p>
  
  <pre><code>// 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&lt;object[][]&gt;();
                  var iPms = ioNames.DeserializeFromByteArray&lt;IEnumerable&lt;InstrumentParameter&gt;&gt;();
                  iPms = iPms.ToList();
  </code></pre>
  
  <p>-- The Method --</p>
  
  <pre><code>/// &lt;summary&gt;
          /// Deserializes the specified byte array.
          /// &lt;/summary&gt;
          /// &lt;typeparam name="T"&gt;Type to Use&lt;/typeparam&gt;
          /// &lt;param name="byteArray"&gt;The byte array.&lt;/param&gt;
          /// &lt;returns&gt;T.&lt;/returns&gt;
          public static T DeserializeFromByteArray&lt;T&gt;(this byte[] byteArray)
          {
              using (var ms = new MemoryStream(byteArray))
              {
                  return (T)new BinaryFormatter().Deserialize(ms);
              }
          }
  </code></pre>
  
  <h2>Question</h2>
  
  <p>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.</p>