英文:
When calling a stored procedure with ADO.NET, how do I tell what parameter types the database actually used?
问题
我在C#中有一个调用存储过程的代码。它使用ADO.NET调用位于我的SQL Server上的存储过程。尽管我知道服务器上存储过程的一个参数是 VARCHAR(120)，但我在C#中使用 AddWithValue 将其添加到参数集合中，得出结论是在C#中使用的字符串参数应该转换为 SqlDbType 为 NVARCHAR。我该如何判断服务器是否使用了 NVARCHAR 类型还是 VARCHAR 类型？关于ADO.NET是否足够智能地从服务器读取正确的参数类型，我得到了一些混乱的信息，我想通过实证测试来验证。
我的当前简陋解决方案如下：
- 选取一个在
VARCHAR中不存在的字符，例如ǹ即 'Latin Small Letter N With Grave'。 - 观察 SQL Server 将
SELECT ǹ转换为?。 - 编写一个查询，在输入
?时返回值。 - 将该查询输入
ǹ。 - 观察查询的行为，好像我输入的是
?。 - 得出结论，即使我给它
NVARCHAR，SQL 仍然使用了VARCHAR。
英文:
I have a stored procedure call in C#. It uses ADO.NET to call the stored procedure that is on my SQL Server. Despite knowing that one of the parameters of the stored procedure on the server is VARCHAR(120), I used AddWithValue to add it to my parameter collection in C# and that has concluded that the string parameter used in C# should be translated to a SqlDbType of NVARCHAR. How do I tell if the server has used the NVARCHAR type or the VARCHAR type? I'm getting mixed messages regarding if ADO.NET is smart enough to read the correct parameter type from the server and I would like to test this empirically.
My current crude solution was as follows:
- Take a character that does not exist in
VARCHAR, e.g.ǹi.e. 'Latin Small Letter N With Grave'. - Observe that SQL Server translates
SELECT ǹto?. - Write a query that returns values when
?is fed to it. - Feed said query
ǹ. - Observe that the query has acted as if I fed it
?. - Conclude that SQL has used
VARCHAReven though I gave itNVARCHAR.
答案1
得分: 1
以下是翻译好的部分：
"> that has concluded that the string parameter used in C# should be translated to a SqlDbType of NVARCHAR. How do I tell if the server has used the NVARCHAR type or the VARCHAR type? I'm getting mixed messages regarding if ADO.NET is smart enough to read the correct parameter type from the server and I would like to test this empirically.
There is no such functionality. ADO.Net does not query anything from the server, it just sends whatever you tell it. If that has defaulted to nvarchar then that is what will be sent. This is why you should always specify the data types and lengths/precision/scale.
Having said that, in the case of stored procedures, the server will then convert the data back to the original type to fit into the parameter declaration, with a slight overhead, and possible loss of precision or characters etc."
英文:
> that has concluded that the string parameter used in C# should be translated to a SqlDbType of NVARCHAR. How do I tell if the server has used the NVARCHAR type or the VARCHAR type? I'm getting mixed messages regarding if ADO.NET is smart enough to read the correct parameter type from the server and I would like to test this empirically.
There is no such functionality. ADO.Net does not query anything from the server, it just sends whatever you tell it. If that has defaulted to nvarchar then that is what will be sent. This is why you should always specify the data types and lengths/precision/scale.
Having said that, in the case of stored procedures, the server will then convert the data back to the original type to fit into the parameter declaration, with a slight overhead, and possible loss of precision or characters etc.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论