Just out of interest, why cant you include paramters to an SP inside a WITH statement?-CodePudding

I'm working on some VB.NET and need to kick off a SQL SP and if I put the following:

Using connection As New SqlConnection(conString)
            Dim command As New SqlCommand("usp_EXT_101_<SPName>", connection)
            command.CommandType = CommandType.StoredProcedure
            command.CommandText = "Do something"

Visual Studio will suggest that it can be simplified by using a WITH so it would now look like this:

 Dim command As New SqlCommand("usp_EXT_101_ConsolidatedGroupKickOff", connection) With {
        .CommandType = CommandType.StoredProcedure,
        .CommandText = "Do something"
    }

However, any parameters being passed to the SP have to remain outside the {} and it will not allow .Parameters.AddWithValue etc.

    Dim command As New SqlCommand("usp_EXT_101_ConsolidatedGroupKickOff", connection) With {
        .CommandType = CommandType.StoredProcedure,
        .CommandText = "Do something"
    }
    command.Parameters.AddWithValue("@someVar", strVarName)

Is this primarily because that command doesn't include = in its syntax? Just curious as to why the parameters assignments have to sit outside the WITH

CodePudding user response：

The With following a constructor with the {} is for assigning values only.

You can however use

With command
   .Parameters.AddWithValue("@someVar", strVarName)
End With

AFTER your object has been constructed.

Side note, I hate the use of With outside of the constructor as it makes code harder to read.

CodePudding user response：

Object initialisers are for initialising an object by setting properties, not calling methods. CommandType and CommandText are both properties, while AddWithValue is a method. If you used an actual With block, as opposed to an object initialiser, then you could include the method call:

Dim command As New SqlCommand("usp_EXT_101_ConsolidatedGroupKickOff", connection)

With command
    .CommandType = CommandType.StoredProcedure,
    .CommandText = "Do something"
    .Parameters.AddWithValue("@someVar", strVarName)
End With

I'm not suggesting you do that. Just explaining the different in the usages of With.