ASP.Net VB存储过程允许空参数 - 如何将NULL为空的整数传递到存储过程

Question

ABC的值（参数）可以为NULL。我正在努力如何将其设置为null。下面的声明不起作用。如果我尝试设置Nullable（OfInteger）类型，则会出现错误，该值是'错误的类型'。提前致谢。

Dim ABC as Integer = Nothing 

Public Function RunStoredProce(ByVal **ABC** As Integer, ByVal DEF As String) _ 
       As IEnumerable(Of MyProjectStoredProcResults) Implements MyFunction.GetThatDataResult 

     Dim y = (From p In dc.uspReportMyFunction(**ABC**, DEF) 
      Select p).ToList 

     Return y 

    End Function 

Answer 1

Dim ABC As Nullable(Of Integer) 

Public Function RunStoredProce(ByVal ABC As Nullable(Of Integer), ByVal DEF As String) _ 
      As IEnumerable(Of MyProjectStoredProcResults) Implements MyFunction.GetThatDataResult 

    Dim y = (From p In dc.uspReportMyFunction(ABC, DEF) 
     Select p).ToList 

    Return y 

End Function 

相信linqtosql会自动处理可空转换。

Answer 2

您可以整数空的，只有通过它，如果它有一个值：

通知ABC是如何被传递为Nullable类型：

Public Function RunStoredProce(ByVal ABC As Integer?, ByVal DEF As String) As IEnumerable(Of MyProjectStoredProcResults) Implements MyFunction.GetThatDataResult 
    Return (From p In dc.uspReportMyFunction(ABC, DEF) Select p).ToList 
End Function 

我不知道如何你的系统设置，但假设uspReportMyFunction是调用存储过程的功能，你会希望排除ABC当它是空：

If ABC.HasValue Then 
    'Add ABC as a parameter 
End If 

'Add DEF as a parameter 

'Execute the stored procedure 

编辑

让我们说这是执行存储过程

public void SaveSomething(int jobID, int? postNumber) 
{ 
    using (var data = new DataAccess(this.ConnectionString)) 
    { 
     data.ProcedureName = "SaveSomething"; 

     data.AddParm("@JobID", SqlDbType.Int, JobID); 
     if (postNumber.HasValue) 
      data.AddParm("@PostNumber", SqlDbType.Int, PostNumber.GetValueOrDefault()); 

     return data.ExecNonQuery(); 
    } 
} 

从前端的数据功能，你可以调用这个函数是这样的：

int jobID = 1; 
int? postNumber = null; 

GetWorkOrderDetailAdjustment(jobID, postNumber); //postNumber == null