如何在C#中创建NVarchar(max)Sqlparameter? [英] How to create NVarchar(max) Sqlparameter in C#?

问题描述

我有以下代码使用存储过程并输入字符串参数@JobNumbers来拉回DataTable，该参数是动态创建的作业编号字符串(因此长度未知):

I've got the following code to pull back a DataTable using a stored procedure and inputting a string parameter @JobNumbers, which is dynamically created string of job numbers (and therefore length is unknown):

using (SqlConnection connection = new SqlConnection(con))
        {
            SqlCommand cmd = new SqlCommand("dbo.Mystoredprocedure", connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@JobNumbers", SqlDbType.VarChar, 4000);
            cmd.Parameters["@JobNumbers"].Value = JobNumber;

            SqlDataAdapter da = new SqlDataAdapter(cmd);

            connection.Open();
            da.Fill(JobDetails);
        }

如您所见，我当前已将JobNumber参数设置为4000的长度，该长度应足以容纳约500个工作编号，并且应该足够.但是，有可能在特殊情况下可能需要更多.因此，我想知道是否可以将参数设置为等效于nvarchar(max)的sql参数类型?

As you can see I've currently set the JobNumber parameter to a length of 4000, which should be enough to take around 500 job numbers and should be enough. However, there is the possibility that it may need more on the odd occasion. So, I was wondering, is there a way to set the parameter to the equivalent sql parameter type of nvarchar(max)?

我看过各种类似的问题(将参数传递给SQLCommand的最佳方法是什么?)，但是没有一个方法明确说明您是否可以(或不能)执行此操作.其次，如果我在存储过程中将@JobNumber参数设置为nvarchar(max)，是否甚至有必要这样做，因此大概根本不需要在C#中设置长度?如果执行此操作，则会出现此问题中建议的潜在性能问题何时应"SqlDbType" ;和大小"在添加SqlCommand参数时使用??

I've had a look at various similar questions (What's the best method to pass parameters to SQLCommand?) but none specifically say whether you can (or can't) do this. Secondly, is it even necessary to do this if I set the @JobNumber parameter in the stored procedure to nvarchar(max) and therefore presumably I wouldn't need to set the length in C# at all? If I do this will this have potential performance issues as suggested in this question When should "SqlDbType" and "size" be used when adding SqlCommand Parameters??

推荐答案

这是您显式设置nvarchar(max)的方式:

This is how you explicitly set nvarchar(max):

cmd.Parameters.Add("@JobNumbers", SqlDbType.NVarChar, -1);

如果您真的很关心性能，则可能需要考虑传递一个整数表: https://stackoverflow.com/a/10779593/465509

If you're really concerned with performance you might want to consider passing a table of integers: https://stackoverflow.com/a/10779593/465509

这篇关于如何在C#中创建NVarchar(max)Sqlparameter?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！