Re: Multiple inserts question

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

  • Re: Making a database connection global
    ... connection would be opened the same amount of time (which no-one yas yet ... Anyway, if he don't want to listen, a different angle will probably not ... to reference the database connection once. ... I created a class with a static database connection and the class opens ...
    (microsoft.public.dotnet.framework.aspnet)
  • Database Connection Question
    ... we were using an asp include file with database functions ... and would open and close the database connection multiple times in 1 script. ... I don't know, the persistent connection seems much better to me, easier to ...
    (microsoft.public.inetserver.asp.db)
  • Re: garbage collection
    ... it will get a reader back.. ... you can not reuse the connection on which it opened. ... > garbage collection issue, its a logical error. ... > on database connection is, ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Database Connection question
    ... the other is a standalone app and is using ... >java.sql.DriverManager for Connection. ... Every time after obtaining a ... >My task is to write generic code for database connection to be used in both ...
    (comp.lang.java.programmer)
  • Re: simplifying repeating database connections in a class?
    ... I wrote my first PHP class today. ... >> repeat the database connection lines, ... That's why I'm calling the include twice, once in each function, ...
    (comp.lang.php)
Loading