title: How to store UTF-8 bytes from a C# String in a SQL Server 2000 TEXT column
body: <p>I have an existing SQL Server 2000 database that stores UTF-8 representations of text in a TEXT column. I don't have the option of modifying the type of the column, and must be able to store non-ASCII Unicode data from a C# program into that column.</p>
  
  <p>Here's the code:</p>
  
  <pre><code>sqlcmd.CommandText =
      "INSERT INTO Notes " +
      "(UserID, LocationID, Note) " +
      "VALUES (" +
          Note.UserId.ToString() + ", " +
          Note.LocationID.ToString() + ", " +
          "@note); " +
      "SELECT CAST(SCOPE_IDENTITY() AS BIGINT) ";
  
  SqlParameter noteparam = new SqlParameter( "@note", System.Data.SqlDbType.Text, int.MaxValue );
  </code></pre>
  
  <p>At this point I've tried a few different ways to get my UTF-8 data into the parameter. For example:</p>
  
  <pre><code>// METHOD ONE
  byte[] bytes = (byte[]) Encoding.UTF8.GetBytes( Note.Note );
  char[] characters = bytes.Select( b =&gt; (char) b ).ToArray();
  noteparam.Value = new String( characters );
  </code></pre>
  
  <p>I've also tried simply</p>
  
  <pre><code>// METHOD TWO
  noteparam.Value = Note.Note;
  </code></pre>
  
  <p>And</p>
  
  <pre><code>// METHOD THREE
  byte[] bytes = (byte[]) Encoding.UTF8.GetBytes( Note.Note );
  noteparam.Value = bytes;
  </code></pre>
  
  <p>Continuing, here's the rest of the code:</p>
  
  <pre><code>sqlcmd.Parameters.Add( noteparam );
  sqlcmd.Prepare();
  
  try
      {
      Note.RecordId = (Int64) sqlcmd.ExecuteScalar();
      }
  catch
      {
      return false;
      }
  </code></pre>
  
  <p>Method one (get UTF8 bytes into a string) does something strange -- I think it is UTF-8 encoding the string a second time.</p>
  
  <p>Method two stores garbage.</p>
  
  <p>Method three throws an exception in ExecuteScalar() claiming it can't convert the parameter to a String.</p>
  
  <p>Things I already know, so no need telling me:</p>
  
  <ul>
  <li>SQL Server 2000 is past/approaching end-of-life</li>
  <li>TEXT columns are not meant for Unicode text</li>
  <li>Seriously, SQL Server 2000 is old. You need to upgrade.</li>
  </ul>
  
  <p>Any suggestions?</p>