CODEWITHSUNDEEP
c#sql-server
user1238350
user1238350

Reputation: 43

How to get to retrieve Bytes[] from VARCHAR in Ado.Net?

I need to retrieve some data stored as varchar in an SQL table to bytes[] in c#.

The SQL server is running MS SQLServer 2005. The table in question has a column defined as varchar(3900), non null. This column is usually filled by c++ code writing different POD structures straight into the column. Basically passing the pointer to the structure to the c++ sql code updating the table. Different c++ programs read and write to this table and share the same headers describing those POD structures. The type of POD structure is indicated by another column in the table. Lets call it DataType The way the table is defined (as the column) cannot be changed. It would require a way to huge change in the currently functioning systems.

How can I get the full content (the 3900 bytes) of the varchar to Bytes[] in C# My aim is use ADO.net to get rows from this table. Read this column and the DataType one. Depending on DataType build an Object following the skeleton of the c++ POD structure.

Then how to write back to the table from my C# Object to the varchar(3900).

I read this question but it seems ADO.net sees this column as string and then truncate the content. I cannot seem to get the "raw" content of the varchar column.

Any idea?

Edit:

In c++ The data written to the column is declared as a char[3901]. Complete POD structures are "written" over it using memcopy. Before being stored in the table using ADO: It is first converted to a WideCharString using MultiByteToWideChar Then to a Bstring BSTR which in turn is put into a VARIANT and finaly used by ADO to update the table.

Upvotes: 1

Views: 3222

Answers (3)

jfrankcarr
jfrankcarr

Reputation: 481

Depending on the encoding of the data, you may be able to use LINQ and BitConverter to handle the conversion from string to char array to byte array and back again as show in this example snippet.

string TestValue = "1234567890";

byte[] Result = TestValue.ToCharArray().SelectMany(item => BitConverter.GetBytes(item)).ToArray<byte>();

string BackToTestValue = new string(Result.Select((item, index) => BitConverter.ToChar(Result, Math.Min(index, Result.Length - 2))).Where((item, index) => index % 2 == 0).ToArray<char>());

Of course, instead of the fixed test string shown here you would use the value you read from the database. I think you've already seen how that works from other answers.

Upvotes: 0

Chris Taylor
Chris Taylor

Reputation: 53709

Of course I should give the obligatory "You should be using a binary data type to store binary data..." but I am sure you know that and for what ever reason you are stuck with the varchar data type.

That said, here is a snippet of very basic ADO.NET code to read/write the binary data.

The code assumes that the following table exists

CREATE TABLE BinTest
(
   BinData varchar(3000) NOT NULL
)

The following code will insert a binary array of data into the table and the read it back. For arrays limited to the size of the varchar this should work with out needing to use more advanced methods to read the binary data.

using System;
using System.Data.SqlClient;

class Program
{
  static void Main(string[] args)
  {
    byte[] bytes = new byte[]{0,1,2,3,4,65,66,4,3,2,1,0};

    using(var con = new SqlConnection(@"<your connection string here>"))
    {
      con.Open();

      var cmd = con.CreateCommand();

      // Insert binary data into varchar
      cmd.CommandText = "insert into BinTest (BinData) values (cast(@BinData as varbinary))"; 
      cmd.Parameters.AddWithValue("BinData", bytes);
      cmd.ExecuteNonQuery();

      // Read binary data from varchar
      cmd.Parameters.Clear();
      cmd.CommandText = "select cast(BinData as varbinary) from BinTest";
      using (var rdr = cmd.ExecuteReader())
      {
        while (rdr.Read())
        {
          if (!rdr.IsDBNull(0))
          {
            var data = rdr.GetSqlBinary(0);
            Console.WriteLine(BitConverter.ToString(data.Value));
          }
        }
      }
    }
  }
}

Upvotes: 1

Nicholas Carey
Nicholas Carey

Reputation: 74345

Given a table

create table foobar
(
  id        int           not null primary key ,
  some_text varchar(2000) not null ,
)

To retrieve some_text as octets:

select id = id ,
       some_octets = convert(varbinary(2000),some_text)
from foobar

An insert and update:

declare @id          int
declare @some_octets varbinary(2000)

insert foobar ( id , some_text ) values ( @id , convert(varchar(2000),@some_octets) )

update foobar set @some_text = convert(varchar(2000),@some_octets)
where id = @id

Upvotes: 1

Related Questions