Finding out SQLParameters of a SQLCommand

At some point in the past, in my VB6 days, I remember being able 开发者_开发知识库to create a sql command object (not necessarily the same one as today's .NET flavour), and have the sql parameters automatically filled in.

This allowed me do do things like only passing in parameters that I definitely knew to exist, and if two different clients were using different versions of the database, I could call my procedures knowing that I would still get a meaningful response.

Something like this:

Dim cmd as SqlCommand
Set cmd = New SqlCommand(Connection)
cmd.CommandText = "MagicStoredProcedure"
cmd.CommandType = CommandType.StoredProcedure
If cmd.Parameters.Count > 0 Then
    If cmd.Parameters(0).Name = "@FirstParameter" Then
        cmd.Parameters("@FirstParameter").Value = someValue
    End If
End If
Dim r as Recordset
Set r = cmd.ExecuteRecordset()

I remember doing this, but I cannot find any examples of my own, and trying to do this in .NET does not seem to work at all. All the examples I have seen (and I have searched for some time) add the parameters manually.

Any pointers?

---

I have found the SQLCommandBuilder.DeriveParameters procedure, which does exactly what I wanted.

SQLCommandBuilder.DeriveParameters(cmd)
If cmd.Parameters.Contains("@FirstParameter") Then
    cmd.Parameters("@FirstParameter").Value = someValue
End If

Not where I expected it, though. I'd expect that baked in to the Command object, or in the Parameters collection.

---

There's a template solution that I have used in the past for DB access, that uses a class's properties mapped to stored procedure variables and builds the string on the fly when needed.

Look here for an explanation

It looks very similar to what you get when you add stored procedures and tables from a connected database to a project when using Visual Studio.

Perhaps it can point you in the right direction.