Storing special unicode characters in SQL Server and retrieving in .NET [duplicate]

Question

I cannot properly store special unicode characters in SQL Server and then retrieve them.

SQL Server setup:

create table TestUni
(
    UniCol nvarchar(20)
)

insert into TestUni values ('😅')
insert into TestUni values ('電')

Now this command returns question marks:

select len(UniCol), UniCol from TestUni

2    ??
1    ?

When I create a .NET client to read the data, it also returns question marks:

var sqlConnection = new SqlConnection("***");

sqlConnection.Open();

var sqlCommand = new SqlCommand("select * from TestUni", sqlConnection);
        var reader = sqlCommand.ExecuteReader();

var str = string.Empty;

while(reader.Read())
{
    str += reader["UniCol"];
}

File.WriteAllText("d:\\test.txt", str);

The contents of the file:

How to properly retrieve special characters from database?

Answer 1

You need to use N prefix to indicate a unicode value going in

create table TestUni
(
  UniCol nvarchar(20)
)

insert into TestUni values ('😅')
insert into TestUni values ('電')
insert into TestUni values (N'😅')
insert into TestUni values (N'電')

select len(UniCol), UniCol from TestUni