CODEWITHSUNDEEP
c#sql-serverencodingbinaryascii
john
john

Reputation: 45

How to get string back in c# after converting to binary in SQL Server

I have string value key:

TxtProductKey.Text has value "key"

Encode this value 

 byte[] Key = Encoding.ASCII.GetBytes(TxtProductKey.Text.Trim());

Save it in database table in a column of datatype binary(50) null.

The value in the table looks like this:

0x6B00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Now I am trying to get it back in string value that I entered is Key

byte [] TempKey = (byte[])LicenseInfo.Tables[0].Rows[0]["ProductKey"];
var newText = Encoding.ASCII.GetString(TempKey);

but the result I am getting in newText is:

k\0\0\0\0\0\00\

Where am I doing something wrong? I hope for your suggestions

C# code for saving value in database:

Sqlconnection.Open();

SqlCommand Cmd = new SqlCommand("SpAddAttempts", Sqlconnection);
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.Parameters.Add("@Attempts", SqlDbType.Int).Value = Attempt;
Cmd.Parameters.Add("@Activate", SqlDbType.Bit).Value = Activate;
Cmd.Parameters.Add("@Key", SqlDbType.Binary).Value = Key;

Cmd.ExecuteNonQuery();
CloseConnection();

Stored procedure is:

ALTER PROCEDURE [dbo].[SpAddAttempts]
    @Attempts INT,
    @Activate BIT,
    @Key BINARY
AS
BEGIN
    UPDATE License 
    SET Attempts = @Attempts,
        Activate = @Activate,
        ProductKey = @Key 
    WHERE LicenseId = '1'
END

Upvotes: 2

Views: 668

Answers (1)

Caius Jard
Caius Jard

Reputation: 74710

I don't think you can: you only stored a single byte (the equivalent of char k) in your db table.. 0x68 is k, the next byte is 0x00 - ascii null - string terminator - whatever your language of choice will refer to it as, it's definitely not e

So, because only the first byte has been preserved and the rest is ascii NUL 000000000000000000000000....), there's no way to know whether the rest of the value was ey, eyboard, oolaid etc.. The fault is during the storing of the value in the table (you didn't show code for that part) - it's only stored the first byte

I recommend you store this text as text, not binary; it seems like an unnecessary world of pain to store it as binary when it starts life as text and you clearly want it back out as text.

EDIT: Now you've posted the code that does the saving, I can see youve declared the parameter as type Binary, but you havent specified a length, so the length is defaulting to 1 byte. This is why only one of your bytes is getting saved. You need to declare the parameter more like this:

cmd.Parameters.Add("@Key", SqlDbType.Binary, binaryData.Length); //dont pass an array longer than what the table can store, or you'll get a "data would be truncated" error

Upvotes: 2

Related Questions