JSON 序列化和 .NET SQL 参数
我最近加入了一个团队，他们使用 JSON 序列化将参数数组传递到 SQL Server 存储过程，然后在其中反序列化并提取所需的值，即每个存储过程都有一个 VARCHAR(MAX) 类型的“@Parameters”参数。创建和执行命令的框架是用 C# 编写的，并使用标准 .NET 类型（SqlCommand、SQlParameter）等。
看来，当序列化内容的长度大于阈值时，存储过程未正确执行，但没有例外被提出。似乎什么也没有发生。运行 SQL Profiler 我观察到没有尝试在 SQL Server 中执行存储过程。
例如： 在一种情况下，某个类型只有 30 个实例，并且序列化的属性不超过 8 个。序列化成功，并将值赋给SqlCommand参数集合中的sql参数（只有一个参数）。命令已执行但没有任何反应。如果某种类型出现的次数较少，则成功。当它不成功时，不会引发异常。
使用： SQL Server 2008 C# .NET 4.0 Newtonsoft 提供的 JSON 序列化。 代码中的 SqlParameter 创建为 varchar max。 客户端服务器架构 - 没有中间服务。
有谁知道使用 SqlCommand 作为 sql 参数传递的 JSON 序列化值的限制，或者对可能导致此行为的原因有任何想法吗？
I have recently joined a team where they are using JSON serialization to pass parameter arrays to SQL Server stored procedures where they are then deserialized and the required values extracted i.e. Each stored procedure has a '@Parameters' parameter of type VARCHAR(MAX). The framework creating and executing the command is written in C# and uses standard .NET types (SqlCommand, SQlParameter) etc.
It appears that when the serialized content is greater in length than a threshold that the stored procedure is not being properly executed but no exceptions are raised. Nothing appears to happen. Running SQL Profiler I have observed that there is no attempt to execute the stored procedure in SQL Server.
For example:
In one case there are just 30 instances of a type with no more than eight properties being serialized. The serialization succeeds and the value is assigned to the sql parameter in the parameters collection of a SqlCommand (there is only one parameter). The command is executed but nothing happens. If there are fewer occurrences of a type then it succeeds. When it does not succeed an exception is not being raised.
Using:
SQL Server 2008
C# .NET 4.0
JSON Serialization provided by Newtonsoft.
The SqlParameter in code is created as a varchar max.
Client Server architecture - there are no intermediary services.
Does anyone know of a limit for JSON serialized values being passed as a sql parameter with a SqlCommand or have any ideas as to what might be causing this behaviour?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我已经弄清楚我的案例中发生了什么。它与 sql 参数中的值的序列化无关，因为我发现如果我等待足够长的时间（在我的例子中为 5 -7 分钟），该过程最终会执行。
在存储过程中，有一个游标用于提取感兴趣的记录（这是一个批量更新过程）。该游标正在调用 CLR 函数来反序列化 Json 以获得所需的每个值。通过将反串行化的数据插入到临时表中以供游标使用，“问题”得到了解决。
I've figured out what was happening in my case. It had nothing to do with the serialziation of the value in a sql parameter as I found that if I waited long enough (5 -7 minutes in my case) that the procedure eventually executed.
In the stored procedure there is a cursor being used to extract the records of interest (this is a batch update procedure). This cursor was calling the CLR function to deserialize the Json for each value it required. By inserting the desirialized data into a temp table for use in the cursor the 'issue' was resolved.