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

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

Answer

Not sure if or why intel-sense suggests that.

You can ceratinly do this:

    With New SqlCommand("usp_EXT_101_ConsolidatedGroupKickOff", MyCon)

        .CommandText = "my commmand test"
        .Parameters.Add("@PKID", SqlDbType.Int).Value = mypkvalue
        .ExecuteNonQuery()

    End With

However, be it c# or vb.net? We REALLY but REALLY want .net to tidy and clean things up after such code – especially the conneciton object.

So, this code stub tends to be better:

    Using conn As New SqlConnection(My.Settings.TEST4)
        Dim strSQL As String =
                "INSERT INTO tblUsers (User, Password, PictureURL, Country,League" &
                "VALUES (@User, @Pass, @PicURL, @Country, @League)"

        Using cmdSQL As New SqlCommand("strSQL", conn)

            With cmdSQL
                With .Parameters
                    .Add("@User", SqlDbType.NVarChar).Value = user.Text
                    .Add("@Pass", SqlDbType.NVarChar).Value = pwd.Text
                    .Add("@PicURL", SqlDbType.NVarChar).Value = pfp.Text
                    .Add("@Country", SqlDbType.NVarChar).Value = cntry.Text
                    ' do you want value, or text from cbo box?
                    ' .Add("@League", SqlDbType.NVarChar).Value = cboLeague.SelectedItem.Value
                    .Add("@League", SqlDbType.NVarChar).Value = cboLeague.SelectedItem.Text
                End With
                conn.Open()
                .ExecuteNonQuery()
            End With
        End Using
    End Using

But, I do NOT recommend you use with + initializer. But, you can!!!

    Using conn As New SqlConnection(My.Settings.TEST4)
        Dim strSQL As String =
                "INSERT INTO tblUsers (User, Password, PictureURL, Country,League" &
                "VALUES (@User, @Pass, @PicURL, @Country, @League)"

        With New SqlCommand("strSQL", conn)
            .Parameters.Add("@User", SqlDbType.NVarChar).Value = user.Text
            .Parameters.Add("@Pass", SqlDbType.NVarChar).Value = pwd.Text
            .Parameters.Add("@PicURL", SqlDbType.NVarChar).Value = pfp.Text
            .Parameters.Add("@Country", SqlDbType.NVarChar).Value = cntry.Text
            ' do you want value, or text from cbo box?
            ' .Add("@League", SqlDbType.NVarChar).Value = cboLeague.SelectedItem.Value
            .Parameters.Add("@League", SqlDbType.NVarChar).Value = cboLeague.SelectedItem.Text
            conn.Open()
            .ExecuteNonQuery()
            .Dispose()
        End With
    End Using

In above, we did not setup the SQL command object in a using block, and thus we would (should) in theory add a dispose to the command object as per above.

I think that if one likes (and I do) the use of with clause – somthing I miss much in c#?

Then as noted, I would still suggest a using block – since EVEN with a un-handled error, such blocks of code will clean up, and dispose the objects – that makes the tiny bit of effort and price of the using block worth that effort. You can write VERY bad code – no error handling, or raise events, or exit or do whatever the heck you want! But, with that using block, no matter what you do, .net will clean up the object for you. That is a huge boatload of peace of mind here.

As noted, while it is VERY tempting to use a new keyword in the using block, and I am guilty of doing this? Well, for database connections and objects, I don’t risk the tiny savings of one line of code (using vs new object).

But, this would be just fine:

    Using conn As New SqlConnection(My.Settings.TEST4)
        Dim strSQL As String =
                "INSERT INTO tblUsers (User, Password, PictureURL, Country,League" &
                "VALUES (@User, @Pass, @PicURL, @Country, @League)"

        Using cmdSQL As New SqlCommand(strSQL, conn)
            With cmdSQL.Parameters
                .Add("@User", SqlDbType.NVarChar).Value = user.Text
                .Add("@Pass", SqlDbType.NVarChar).Value = pwd.Text
                .Add("@PicURL", SqlDbType.NVarChar).Value = pfp.Text
                .Add("@Country", SqlDbType.NVarChar).Value = cntry.Text
                .Add("@League", SqlDbType.NVarChar).Value = cboLeague.SelectedItem.Text
            End With
            conn.Open()
            cmdSQL.ExecuteNonQuery()
        End Using
    End Using

So, yes, we from vb land all really do like this feature. And the supposed argument against this is the rise of “confusing code.

Nope, not buying that excuse and that’s not my experience. While this experience may vary and be different for others – its less code, and inside of a “block” of code? Your mind is trained to read blocks of code in a “if” or whatever, and having a block of code inside a “with”? The mind sees and reads that bit of code as a related block of code –

In one word? Nice!!!