问题描述
我正在使用NLog登录我的应用程序，并且在此过程中，我们正在记录客户编号，该客户编号是C#中的字符串，而数据库中是varbinary(32).我将以下SQL代码用于此特定参数. SQL语句的其余部分工作正常:
I am using NLog to log in my application and as part of that we are logging the customer number, which is a string in C#, and a varbinary(32) in the database. I am using the following SQL code for this specific parameter. The rest of the SQL statement works fine:
CONVERT(varbinary(32), @CustNumber)
和以下NLog参数:
<parameter name="@CustNumber" layout="${event-context:item=CustNumber}" />
和下面的C#代码添加Nlog参数:
and the following code in C# to add the Nlog parameter:
myEvent.Properties.Add("CustNumber", custNumber);
由于某些原因，存储在实际表中的值只是原始客户编号"字符串的第一个字符.我已经进行了两次和三次检查，以确保在将字符串发送到数据库之前不会将其截断.任何帮助将不胜感激.
For some reason the value being stored in the actual table is only the first character of the original Customer Number string. I have double and triple checked to make sure I am not truncating the string before it is sent to the database. Any help would be greatly appreciated.
推荐答案
原因是当您插入时，您正在将Unicode(nvarchar(xx))字符串转换为varbinary.然后，当您选择时，您将转换为varchar(xx).如果您转换为nvarchar(xx)，它将可以正常工作.
The reason is that when you insert you're converting a Unicode (nvarchar(xx)) string to varbinary. Then when you select you're converting to varchar(xx). If you convert to nvarchar(xx) it will work fine.
例如:
-
插入这是一个测试"，因为varbinary(30)结果为0x7468697320697397312012074657374.
inserting 'this is a test' as varbinary(30) results in 0x7468697320697320612074657374.
插入N'this is a test'，因为varbinary(30)结果为0x74006800690073002000690073002000610020007400650073007400.
inserting N'this is a test' as varbinary(30) results in 0x74006800690073002000690073002000610020007400650073007400.
因此，当您转换回来时，如果您指定varchar(30)，则前00将截断字符串.
So when you convert back, if you specify varchar(30) the first 00 will truncate the string.
这对我来说很好:
delete from Table_2
insert Table_2 (Test) values( CONVERT(varbinary(30), N'this is a test') )
select * from Table_2
select CONVERT(nvarchar(30), test) from Table_2
这也是
delete from Table_2
insert Table_2 (Test) values( CONVERT(varbinary(30), 'this is a test') )
select * from Table_2
select CONVERT(varchar(30), test) from Table_2
这篇关于为什么在SQL Server中将CONVERT字符串转换为VARBINARY仅转换第一个字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！