Re: Multiple inserts question

Batch updates are detailed here:
http://blogs.msdn.com/dataaccess/archive/2005/05/19/420065.aspx
http://msdn2.microsoft.com/en-us/library/ms810297.aspx
http://weblogs.asp.net/despos/archive/2004/05/14/131714.aspx
http://www.developer.com/db/article.php/3494396

--
I hope this helps,
Steve C. Orr,
MCSD, MVP, CSM, ASPInsider
http://SteveOrr.net


"Yankee Imperialist Dog" <YankeeImperialistDog@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:3D6EB09B-F93D-49A2-9389-43778E8A1916@xxxxxxxxxxxxxxxx
Thanks for the reply, it is very much appreciated and it appears to be a
pointer to the answer, but it still leaves a question: How does one go about
"batching commands together?"
(given thay are sent to SQL server 2005, usinf dot.net 2.0)

Thanks
--
Share The Knowledge. I need all the help I can get and so do you!


"Steve C. Orr [MCSD, MVP, CSM, ASP Inside" wrote:

Considering ADO.NET has built-in connection pooling, recreating the database
connection each time through the loop is actually a lot more efficient than
you might think.
So if it isn't broken, perhaps you shouldn't fix it...
But if you really need to buy some performance you might want to look into
batching your commands together so there is only one round trip to the
database instead of gv.rows.count-1.

--
I hope this helps,
Steve C. Orr,
MCSD, MVP, CSM, ASPInsider
http://SteveOrr.net


"Yankee Imperialist Dog" <YankeeImperialistDog@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote in message news:BD23646F-5211-4932-AF8C-BEC04667B707@xxxxxxxxxxxxxxxx
> Given that i'm passing a GridView to a function (i have a reason).
> Is this the best way to do multiple inserts?
> The following works, but it's seems there should be a better way then > to
> keep recreating the connection and command variables?
> Any Ideas?
> private function fn_MyInsert(ByVal gv As GridView, ByVal iStratID As
> Integer) As Boolean
> For i As Integer = 0 To gv.Rows.Count - 1
> ' Response.Write(i.ToString)
> Dim chk As CheckBox = gv.Rows(i).FindControl("chk_Included")
> If chk.Checked Then
> Dim sqlcon2 As New SqlConnection(constr)
> Dim sqlcmd2 As SqlCommand = sqlcon2.CreateCommand
> sqlcmd2.CommandType = Data.CommandType.StoredProcedure
> sqlcmd2.CommandText = "sp_AddPortfolioToStrategy"
> sqlcmd2.Parameters.AddWithValue("@iStratID", iStratID)
> sqlcmd2.Parameters.AddWithValue("@iPorID",
> gv.DataKeys(i).Value)
> sqlcon2.Open()
> sqlcmd2.ExecuteNonQuery()
> sqlcon2.Close()
> sqlcmd2.Dispose()
> sqlcon2 = Nothing
> End If
> Next
> RETURN True
> End Function
> -- > Share The Knowledge. I need all the help I can get and so do you!



.

Relevant Pages