Is there a better way to call a Stored Procedure with SqlParameters in VB?

Question

Anyone know of a better way to create and initialize SqlParameters in VB.NET? Using 3 lines per variable seems quite excessive. Unfortunately the constructors for this class are rather ridiculous, so I'm thinking of just writing my own sub for initializing each parameter. This is how I've been doing it.

Dim ID As New SqlParameter("@ID", SqlDbType.Int)
ID.Value = val
query.Parameters.Add(ID)

Here is the full code that I use for even a simple Store Procedure which only takes a single argument. I have some however which take 15-20 and that's a LOT of lines of code using this clunky method.

Dim query As New SqlCommand("spGetInitStatusEntry", connection)
query.CommandType = CommandType.StoredProcedure
Dim ID As New SqlParameter("@ID", SqlDbType.Int)
ID.Value = val
query.Parameters.Add(ID)
Dim dt As New DataTable
Dim da As New SqlDataAdapter(query)
da.Fill(dt)

Final Implementation

I created the following Module, as recommended, with two supporting <Extension()>s.

Module TcomExtensions
    <System.Runtime.CompilerServices.Extension()> _
    Public Sub AddParameter(ByRef dbCommand As SqlCommand, ByVal parameterName As String, _
            ByVal parameterValue As Object, ByVal dbType As SqlDbType)

        Dim dbParameter As SqlParameter = Nothing
        dbParameter = dbCommand.CreateParameter

        dbParameter.ParameterName = parameterName
        dbParameter.Value = parameterValue
        dbParameter.DbType = dbType

        dbCommand.Parameters.Add(dbParameter)
    End Sub

    <System.Runtime.CompilerServices.Extension()> _
    Public Function ToDataTable(ByVal value As SqlCommand) As DataTable
        Dim dt As New DataTable
        Using da As New SqlDataAdapter(value)
            da.Fill(dt)
        End Using
        Return dt
    End Function
End Module

And my implementation becomes as follows:

Dim spPrefill As New SqlCommand("spGetPrefillStatusEntry", connection) With {.CommandType = CommandType.StoredProcedure}
'Only one line per added parameter.
spPrefill.AddParameter("@ID", val, SqlDbType.Int)
'Populate the table.
dt = spPrefill.ToDataTable()

I made an Extension Method that targets the SqlCommand class to handle adding parameters. — NoAlias, Feb 29 '12 at 22:13
Gosh, you guys made it really hard for me to choose. The_Black_Smurf and LarsTech both gave stellar answers. I went with sort of a hybrid. The community guidelines recommend giving the answer to the person with less rep as a courtesy in the event of a tie. To anyone looking for answers, both of these are excellent. :) — Chiramisu, Mar 01 '12 at 02:24

LarsTech · Answer 1 · 2012-03-01T01:08:38.457

3

You can also combine your first two lines:

Dim query As New SqlCommand("spGetInitStatusEntry", connection) With {.CommandType = CommandType.StoreProcedure}

Extention methods can also be your friend here, too:

<Extension()> _
Public Function ToDataTable(ByVal value As SqlCommand) As DataTable
  Dim dt As New DataTable
  Using da As New SqlDataAdapter(value)
    da.Fill(dt)
  End Using
  Return dt
End Function

Along with Mike Hofer's suggestion for AddWithValue, this can now be reduced to:

Dim query As New SqlCommand("spGetInitStatusEntry", connection) With {.CommandType = CommandType.StoreProcedure}
query.Parameters.AddWithValue("@ID", key)
Dim dt As DataTable = query.ToDataTable();

If not comfortable using AddWithValue, you can write the parameter information in one line, too:

query.Parameters.Add(New SqlParameter("@ID", SqlDbType.Int) With {.Value = key})

edited Mar 01 '12 at 01:08

answered Feb 29 '12 at 22:07

LarsTech

80,625
14
153
225

I'm not really understanding the `` method so well, even after playing with it and reading on [MSDN](http://msdn.microsoft.com/en-us/library/bb384936.aspx). If I go with Mike's suggestion, how then do I define the `DataType`? If it can be implicit, is that really a good idea? Thanks :) – Chiramisu Mar 01 '12 at 00:22
@Chiramisu Extensions methods are useful. It can be hard to explain: You have to put your extension code in a module and decorate it with the ` _` attribute (Import System.Runtime.CompilerServices), which basically says "take my first argument and do something with it". As far as `AddWithValue()`, it takes care of the type for you based on the data you are passing it. I've seen arguments saying it's good and bad, but I've never had it not work for me. I'll post an update showing the parameters as a one-liner. – LarsTech Mar 01 '12 at 01:00

The_Black_Smurf · Accepted Answer · 2012-03-01T00:01:53.310

With those extension...

 <System.Runtime.CompilerServices.Extension()> _
Public Sub AddParameterValue(ByRef dbCommand As System.Data.Common.DbCommand, ByVal parameterName As String, ByVal parameterValue As Object)
    dbCommand.AddParameter(parameterName, parameterValue)
End Sub


<System.Runtime.CompilerServices.Extension()> _
Public Sub AddParameter(ByRef dbCommand As System.Data.Common.DbCommand, ByVal parameterName As String, Optional ByVal parameterValue As Object = Nothing, Optional ByVal sourceColumn As String = "")
    Dim dbParameter As System.Data.Common.DbParameter = Nothing


    dbParameter = dbCommand.CreateParameter

    dbParameter.ParameterName = parameterName
    dbParameter.Value = parameterValue
    dbParameter.SourceColumn = sourceColumn

    dbCommand.Parameters.Add(dbParameter)

End Sub

...you'll be able to add a parameter with 1 line of code

Dim query As New SqlCommand("spGetInitStatusEntry", connection)
query.CommandType = CommandType.StoredProcedure
query.AddParameterValue("@ID", key)
Dim dt As New DataTable
Dim da As New SqlDataAdapter(query)
da.Fill(dt)

The function is pretty generic and the parameter type isn't specified but you can add it as parameter if you really need it.

I gave you the points only because you have less rep and both yours and LarsTech's answers were equally good and helped me out greatly. Thank you :) — Chiramisu, Mar 01 '12 at 02:25

score 0 · Answer 3 · answered Feb 29 '12 at 22:05

0

query.AddWithValue("@ID", key)

answered Feb 29 '12 at 22:05

Mike Hofer

16,477
11
74
110

Is there a better way to call a Stored Procedure with SqlParameters in VB?

Final Implementation

3 Answers3