StrataFrame Forum

SQLParameter question...

http://forum.strataframe.net/Topic2088.aspx

By StarkMike - 8/4/2006

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?
By Trent L. Taylor - 8/4/2006

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 Smile  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. Smile

By StarkMike - 8/4/2006

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 SqlParameter)



loParms.Add(New SqlParameter("@Param1", 1))

loParms.Add(New SqlParameter("@Param2", 2))



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



FYI, SqlHelper is a method in the DAAB.
By Trent L. Taylor - 8/4/2006

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

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

By StarkMike - 8/4/2006

Thanks Trent. You're always full of help. CoolWink
By Trent L. Taylor - 8/4/2006

Thanks!  That's what we're here for! Wink
By StarkMike - 8/24/2006

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
By StrataFrame Team - 8/24/2006

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)

By StarkMike - 8/24/2006

WOW! I never would have thought of that. Thanks!
By StrataFrame Team - 8/24/2006

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.