SQLParameters and loops???

[Guest]

Is there an easy why to build this in a loop rather than having to declare
every single SQLparameter, value etc etc

I would be really grateful for any advice!

Thanks


... CODE ...

Sub UploadData(ByVal sender As Object, ByVal e As EventArgs)

Dim MyConn As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim cmd As New SqlCommand("AddMenu", MyConn)
cmd.CommandType = CommandType.StoredProcedure


Dim Dateparam As New SqlParameter("@Date", SqlDbType.NVarChar, 255)
' Dim i As Integer
' For i = 0 To 11
Dim Item1param As New SqlParameter("@mItem1", SqlDbType.NVarChar, 255)
Dim Item2param As New SqlParameter("@mItem2", SqlDbType.NVarChar, 255)
Dim Item3param As New SqlParameter("@mItem3", SqlDbType.NVarChar, 255)
Dim Item4param As New SqlParameter("@mItem4", SqlDbType.NVarChar, 255)
Dim Item5param As New SqlParameter("@mItem5", SqlDbType.NVarChar, 255)
Dim Item6param As New SqlParameter("@mItem6", SqlDbType.NVarChar, 255)
Dim Item7param As New SqlParameter("@mItem7", SqlDbType.NVarChar, 255)
Dim Item8param As New SqlParameter("@mItem8", SqlDbType.NVarChar, 255)
Dim Item9param As New SqlParameter("@mItem9", SqlDbType.NVarChar, 255)
Dim Item10param As New SqlParameter("@mItem10", SqlDbType.NVarChar,
255)

Dim Price1param As New SqlParameter("@mPrice1", SqlDbType.NVarChar,
255)
Dim Price2param As New SqlParameter("@mPrice2", SqlDbType.NVarChar,
255)
Dim Price3param As New SqlParameter("@mPrice3", SqlDbType.NVarChar,
255)
Dim Price4param As New SqlParameter("@mPrice4", SqlDbType.NVarChar,
255)
Dim Price5param As New SqlParameter("@mPrice5", SqlDbType.NVarChar,
255)
Dim Price6param As New SqlParameter("@mPrice6", SqlDbType.NVarChar,
255)
Dim Price7param As New SqlParameter("@mPrice7", SqlDbType.NVarChar,
255)
Dim Price8param As New SqlParameter("@mPrice8", SqlDbType.NVarChar,
255)
Dim Price9param As New SqlParameter("@mPrice9", SqlDbType.NVarChar,
255)
Dim Price10param As New SqlParameter("@mPrice10",
SqlDbType.NVarChar, 255)

Dateparam.Value = tbDate.Text
Item1param.Value = tbMenuItem1.Text
Item2param.Value = tbMenuItem2.Text
Item3param.Value = tbMenuItem3.Text
Item4param.Value = tbMenuItem4.Text
Item5param.Value = tbMenuItem5.Text
Item6param.Value = tbMenuItem6.Text
Item7param.Value = tbMenuItem7.Text
Item8param.Value = tbMenuItem8.Text
Item9param.Value = tbMenuItem9.Text
Item10param.Value = tbMenuItem10.Text

Price1param.Value = tbMenuPrice1.Text
Price2param.Value = tbMenuPrice2.Text
Price3param.Value = tbMenuPrice3.Text
Price4param.Value = tbMenuPrice4.Text
Price5param.Value = tbMenuPrice5.Text
Price6param.Value = tbMenuPrice6.Text
Price7param.Value = tbMenuPrice7.Text
Price8param.Value = tbMenuPrice8.Text
Price9param.Value = tbMenuPrice9.Text
Price10param.Value = tbMenuPrice10.Text


cmd.Parameters.Add(Dateparam)
cmd.Parameters.Add(Item1param)
cmd.Parameters.Add(Item2param)
cmd.Parameters.Add(Item3param)
cmd.Parameters.Add(Item4param)
cmd.Parameters.Add(Item5param)
cmd.Parameters.Add(Item6param)
cmd.Parameters.Add(Item7param)
cmd.Parameters.Add(Item8param)
cmd.Parameters.Add(Item9param)
cmd.Parameters.Add(Item10param)

cmd.Parameters.Add(Price1param)
cmd.Parameters.Add(Price2param)
cmd.Parameters.Add(Price3param)
cmd.Parameters.Add(Price4param)
cmd.Parameters.Add(Price5param)
cmd.Parameters.Add(Price6param)
cmd.Parameters.Add(Price7param)
cmd.Parameters.Add(Price8param)
cmd.Parameters.Add(Price9param)
cmd.Parameters.Add(Price10param)
' Next

MyConn.Open()
cmd.ExecuteNonQuery()
MyConn.Close()
End Sub

 

[Kevin Spencer]

Hi Tim,

There are 3 aspects to using variables that are classes:

1. Declare the variable (Dim DateParam As SqlParameter)
2. Instantiate the variable (DateParam = New SqlParameter())
3. Assign the variable's value (DateParam.Name =...DateParam.Type =...)

In your code, you are declaring, instantiating, and assigning your variables
all in one swell foop. That is perfectly acceptable, however, it does result
in a small performance degradation.

It is better to declare variables outside of a loop if you intend to use
them inside the loop. Declare and instantiate your variables at the top of
your function. Assign them in the loop. Or declare them at the top of your
function, and instantiate and assign them in the loop. The first option will
yield the best performance. The second will make smaller code that takes
less time to write. Perhaps the best compormise would be to declare and
instantiate them at the top of the code, using the constructor overload that
takes a name, type, and size. Then assign the value inside the loop.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
What You Seek Is What You Get.