Converting data from nvarchar to varchar in C#

Question

As part of an integration task I am doing, I have a column in my database that has the type nvarchar(30) and I need call a WCF service and pass through the values in this column (among others) that will then be stored in a column that has the type varchar(30) in the other database.

What should I do (presumably in the code that calls the WCF service) to convert my strings into "varchar friendly" strings?

Update: Nothing has gone wrong yet. However I will be doing an initial migration of 120,000 records through this service and then about 300 new records will be pushed through this service each day. As such, manual intervention of any kind is very undesirable and I'm just thinking about what might go wrong in advance. I have no control over the target database (with VARCHAR column), however I do know that it is SQL Server and C# for the application (not sure if they're using ADO.NET).

Answer 1

If the destination column represents ascii characters so simply use Encoding.Convert method

example:

Encoding.ASCII.GetString(Encoding.Convert(Encoding.Unicode, Encoding.ASCII, bytes))

Answer 2

There is nothing you have to do : all strings in .NET are UTF-16 encoded :

• when you retrieve your NVARCHAR column (using ADO .NET I presume), you automatically get a .NET string (UTF-16, any necessary conversion is automatic).
• when you store this .NET string in a VARCHAR column, any necessary conversion from UTF-16 is automatically performed as well.

See here for more information on data type mappings for Sql Server in ADO .NET.

You just have to make sure that all your strings can be converted flawlessly from the source character set to the destination character set.

Answer 3

Your problem isn't the datatypes, it's the data. All possible values that can be stored as nvarchar do not have an equivalent in varchar (depending on the collation). If the second database is under your control, the most trouble free way to pass the data is to change the varchar field to nvarchar now.

If you can't do that, then you need to check the data before you send it to ensure it is data that can convert. Depending on the kinds of data issues you have you may want to strip out characters that won't convert or replace them with some other character or put the record into some type of holding table rather than sending it to have it manaully fixed. Ahmed's suggestion looks like a possibility, I haven't tried it so I don't know if it will work. You may be lucky and not have any data problems (not every database that uses nvarchar is using any characters that varchar with the proper country collation won't have), but you have to plan for the problem.