StrataFrame Forum » .NET Forums » General .NET Discussion » SQLParameter question...

SQLParameter question...

StarkMike — Thu, 24 Aug 2006 14:18:58 GMT

Consider this code...

If a = True Then
'add sqlparameter to parameter array
End If

If b = True Then
'add sqlparameter to parameter array
End If

If c = True Then
'add sqlparameter to parameter array
End If

Each time this code runs the parameter array could change sizes... The first time it could contain only one parameter... the second time it runs it could contain three parameters.

How do i define the parameter array when i dont know how many dimensions I'll need, and how do I add parameters to the array?

RE: SQLParameter question...

StrataFrame Team — Thu, 24 Aug 2006 14:18:58 GMT

No problem... you can pass anything that implements the IEnumerable interface for that object type, so, if you have an array or a custom object that implements the interface, you can pass it as well.

RE: SQLParameter question...

StarkMike — Thu, 24 Aug 2006 14:13:21 GMT

WOW! I never would have thought of that. Thanks!

RE: SQLParameter question...

StrataFrame Team — Thu, 24 Aug 2006 14:04:22 GMT

The 'lo' prefix is a hold-over from the FoxPro days when we had weak-typing and we had the prefix for the Hugarian notation to tell us what the variable represented; the 'lo' stands for local object.

The best way to create a copy of the list is through the copy constructor and you just pass the first list to the constructor of the second list.

Dim loParams2 As New System.Collections.Generic.List(Of SqlParameter)(loParams)

RE: SQLParameter question...

StarkMike — Thu, 24 Aug 2006 13:22:39 GMT

Ok, so now how do I create a copy of this generic list? I've done a little research and i dont think it implements the ICloneable interface so I cant use the Clone method. A couple of places I have found tell me just to use a For Each loop to add the existing objects from list A to list B. It works but it doesnt seem like the BEST way. Is it? or should I use a different type list/collection that does implement the ICloneable interface... which one would I use?

Dim loParams As New System.Collections.Generic.List(Of SqlParameter)

P.S. what does the 'lo' prefix stand for?

Thanks

RE: SQLParameter question...

Trent L. Taylor — Fri, 04 Aug 2006 09:17:13 GMT

Thanks! That's what we're here for! ;)

RE: SQLParameter question...

StarkMike — Fri, 04 Aug 2006 09:14:32 GMT

Thanks Trent. You're always full of help. :cool:;)

RE: SQLParameter question...

Trent L. Taylor — Fri, 04 Aug 2006 09:12:33 GMT

If it expects an array then you need to make one simple change:

SqlHelper.ExecuteNonQuery(CONNECTION_STRING, CommandType.StoredProcedure, "StoredProcedureName", loParms.ToArray())

RE: SQLParameter question...

StarkMike — Fri, 04 Aug 2006 09:07:43 GMT

The reason I was asking this question is because I am using the Data Access Application Block (DAAB) to execute a stored procedure and it accepts sqlparameters as a parameter array.

If i understand what you said correctly then this is how I would want my example to look...

Dim loParms As New System.Collections.Generic.List(Of [b]SqlParameter[/b])

loParms.Add([b]New SqlParameter("@Param1", 1)[/b])
loParms.Add([b]New SqlParameter("@Param2", 2)[/b])

SqlHelper.ExecuteNonQuery(CONNECTION_STRING, CommandType.StoredProcedure, "StoredProcedureName", [b]loParms[/b])

FYI, SqlHelper is a method in the DAAB.

RE: SQLParameter question...

Trent L. Taylor — Fri, 04 Aug 2006 08:44:00 GMT

First of all you can redimension arrays and maintain the contents using the Presernve command:

Redim Preserve MyArray(x)

However, this is an old, and antiquaited way of doing things that just brings frustraion to a developer :) You will want to use a collection or a generic list. If you are adding parameters to an SqlCommand, this is not an issue because the Parameters collection is already set for you.

If a = True Then
loCommand.Parameters.Add(MyParm)
End If

If b = True Then
loCommand.Parameters.Add(MyParm)
End If

One class that I use all of the time is a generic List(). This allows you to define a collection of any internal type dynamically.

Dim loParms As New System.Collections.Generic.List(Of AnyClass)

loParms.Add(Class1)
loParms.Add(Class2)

To clear out the list use the Clear command:
loParms.Clear()

You could also create your own collection and collection items by inheriting the CollectionBase class. Bottom line is that you are not going to want to use an array in this situation. You will want to use a collection, list, or dictionary.

Let me know if I need to clear things up. :)