带问号的 nvarchar 列结果 [英] 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: ????

通过直接SELECT子句(在sql management studio中)，我可以很好地看到文本:

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)

我的桌子:

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

这是我的存储过程:

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;
    }

推荐答案

已解决(感谢@dan-guzman):

Solved (credit to @dan-guzman):

数据需要通过参数化查询和字符文字N前缀插入.

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

喜欢:N 'היי'.

这篇关于带问号的 nvarchar 列结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！