CODEWITHSUNDEEP
sqlvb.net
k80sg
k80sg

Reputation: 2473

Saving multiple items to SQL database

I have some items in my listbox which I will store everyone of them into my database, what I am doing now is looping the box and call my database saving logic to save every single item. I thought this is pretty inefficient, is there anyway that I can use to batch save my items so that I don't open and close the connection as many times as my items. Thanks.

 For Each item In outletToBox.Items
        .CamCode = Items.ToString
        .CamCampaignAutoID = retID
        .CamRemarks = uitxtCamRemarks.Text.Trim


       '---use savetable object to save to database table---
 Next

Upvotes: 0

Views: 1373

Answers (3)

Zeph
Zeph

Reputation: 1728

Depends on if you have access to change the db procedure. If you can change your sql procedure to be aware of multiple values in one parameter you're all set. You can create a delimited string (using any character that wouldn't occur in the text, comma, pipe, etc...) or pass xml, then parse out the values in the sql proc. Can you access savetable object?

Upvotes: 0

Purplegoldfish
Purplegoldfish

Reputation: 5284

I had to do something similar yesterday. My approach was to build up a List of whatever I had to save in my database, then serialize it to XML. I passed the XML as a parameter to a stored procedure which then processed it and saved the data.

Overall I would say this is a much more effective solution than calling the databse multiple times as your application gets the data it wants saved and gives it to your database in one transaction.

As an example of how you can do this here is my code which loops through a CheckBoxList and creates a List of the selected items then serializes it to XML. You should easily be able to adapt this to work with your ListBox

  ' This is the list that will hold each of our selected items
  Dim listOfSelectedItems As New List(Of ListItem)

  ' Loop through the CheckBoxList control and add all selected items to
  ' the listOfSelectedItems List if the item has its Selected property
  ' set to true
  For Each item As ListItem In chkNotify.Items
     If (item.Selected = True) Then

        listOfSelectedItems.Add(item)
     End If
  Next

  ' Declare a new XMLSerializer 
  Dim serializer As New XmlSerializer(listOfSelectedItems.GetType)

  ' Declare a StringWriter
  Dim writer As StringWriter = New StringWriter()

  ' Serialize the listOfSelectedItems List
  serializer.Serialize(writer, listOfSelectedItems)

  ' Store our XML in a String variable
  Dim serializedXML As String = writer.ToString()

Upvotes: 1

Luis
Luis

Reputation: 1294

Normally, you don't have to close the connection, you keep the connection open and do multiple inserts. You can either commit after every insert, or do groups of inserts and only then commit. You are right, closing and opening connections is expensive. More information is needed about the DB you are using to specify if there are methods to do multiple inserts in batches.

Upvotes: 0

Related Questions