CODEWITHSUNDEEP
c#sqlcharacter-encodingsql-server-2008-r2
Shahar Shokrani
Shahar Shokrani

Reputation: 8752

nvarchar column result with question marks

I'm trying to update the queue item and retrieve it's column text content.

the problem is that special signs such as Hebrew chars resulted in question marks: ????

I can see the text perfectly fine by making direct SELECT clause (within the sql management studio):

Message's column
-------
היי     
hey

When i try to retrieve the data it get scrambled :

 היי     --->    ??? (Not OK)
 hey     --->    hey (OK)

My table:

CREATE TABLE [dbo].[MyQueue](   
    [Message] [nvarchar](1000) NOT NULL 
    --some additional columns
)

This is my Stored procudure:

ALTER procedure [dbo].[MyDequeue] (
)
as
begin
    with CTE as (
            SELECT TOP (100) *
            FROM MyQueue WITH (xlock,READPAST)
            WHERE Locked = 0
            and HasError=0
            and Success=0)
        UPDATE CTE
        SET Locked = 1, LockTime=getUtcDate()
        OUTPUT INSERTED.*;
end

I'm reading this item by these function:

    public IEnumerable<MyQueue> Dequeue(int batchSize)
    {
        var cmd = dataManager.CreateCommand();
        cmd.CommandText = "MyDequeue";
        cmd.CommandType = System.Data.CommandType.StoredProcedure;

        using (var reader = dataManager.ExecuteReader(cmd))
        {
            var ordinals = reader.LoadFields();
            List<MyQueue> items = new List<MyQueue>();
            while (reader.Read())
            {
                items.Add(new MyQueue()
                {
                    Message = reader.GetString(ordinals["Message"])
                    // some additional properties init
                });
            }
            return items;
        }
    }


    public static Dictionary<string, int> LoadFields(this IDataReader reader)
    {
        Dictionary<string, int> loadedColumns = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
        for (int i = 0; i < reader.FieldCount; i++)
        {
            try
            {
                loadedColumns.Add(reader.GetName(i), i);
            }
            catch (System.ArgumentException) { }
        }
        return loadedColumns;
    }

Upvotes: 1

Views: 2393

Answers (1)

Shahar Shokrani
Shahar Shokrani

Reputation: 8752

Solved (credit to @dan-guzman):

The data need to be insert with parameterized query and the character literal N prefix.

like: N 'היי'.

Upvotes: 4

Related Questions