Using arraylist to insert items into DB

Question

I have created an ArrayList with items in my Order.aspx.vb. I pass these on to my bllOrder, which passes it on to my dalOrder.

Order.aspx.vb

Dim item As RepeaterItem
For Each item In rptProductList.Items
   objOrder.OrderCustID = Session("CustID")
   objOrder.OrderProdID = DirectCast(item.FindControl("ddlProducts"), DropDownList).SelectedValue
   bllOrder.InsertOrder(objOrder)
Next item

dalOrder

Function InsertOrder(ByVal objOrder As Order) As Boolean
    Dim Result as New Boolean

    myconn.open()

    Dim SQL As String = "INSERT INTO order(OrderCustID, OrderProdID) VALUES (?,?)"
    Dim cmd As New OdbcCommand(SQL, myconn)

    cmd.Parameters.AddWithValue("OrderCustID", objOrder.OrderCustID)
    cmd.Parameters.AddWithValue("OrderProdID", objorder.OrderProdID)

    result = cmd.ExecuteNonQuery()

    myconn.close()

    Return Result
End Function

This is good for one item, but how would I do it for my ArrayList?

All help is much appreciated!

tugberk · Answer 1 · 2011-05-23T11:48:16.107

2

instead of passing single Order item, pass a List of Orders and them loop it though inside your method. make it like that Public Function InsertOrder(objOrder As List(Of Order)) As Boolean and then use objOrder as a list of Orders to loop it through.

put the following code inside a foreach loop following code and pass the current item values;

cmd.Parameters.AddWithValue("OrderCustID", objOrder.OrderCustID)
    cmd.Parameters.AddWithValue("OrderProdID", objorder.OrderProdID)

    result = cmd.ExecuteNonQuery()

edited May 23 '11 at 11:48

answered May 23 '11 at 10:10

tugberk

57,477
67
243
335

So do I pass on my ArrayList? – CustomX May 23 '11 at 10:13
What if I just stick my INSERT query inside the For Each item loop? Works too doesn't it? Or is this more intensive? – CustomX May 23 '11 at 10:20
I am not sure about the performance here but leave the connection open until you finish your inserts. that sounds better. – tugberk May 23 '11 at 10:22
so make a function just to open and one to close the connection? – CustomX May 23 '11 at 10:23
no, put your foreach loop between myconn.open() and myconn.close() – tugberk May 23 '11 at 10:24
but how would I do this in the Order.aspx.vb page? – CustomX May 23 '11 at 10:29
@Tom instead of passing single Order item, pass a List of Orders and them loop it though inside your method. make it like that `Public Function InsertOrder(objOrder As List(Of Order)) As Boolean` and then use objOrder as a list of Orders to loop it through. – tugberk May 23 '11 at 11:47

score 1 · Answer 2 · answered May 23 '11 at 10:10

1

Convert the array of items into an xml string and you can do a bulk insert in the stored procedure using openxml. http://msdn.microsoft.com/en-us/library/ms191268.aspx also refer an older post for sql server 2005 http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

answered May 23 '11 at 10:10

Aravind

4,125
1
28
39

It would be for 10 items max, and I can't use SP with my current provider at the moment (f*** provider) ... – CustomX May 23 '11 at 10:33
then i guess bulk insert is not an option for you and you may have to loop as many number of items to store them. – Aravind May 23 '11 at 10:35
indeed, do you know how tugberk wants to do it? – CustomX May 23 '11 at 10:38
he says the same thing. just create a loop for the number of items in the array list and put the insert code inside the loop. – Aravind May 23 '11 at 10:47
so `item` is my arraylist? and how would I pass it on to my `dalOrder`? – CustomX May 23 '11 at 10:51
you call the 'InsertOrder' method inside the foreach loop u got there. – Aravind May 23 '11 at 10:57
yes, this is how i've got it going now, is this fine? I always open and close the connection though. – CustomX May 23 '11 at 10:59
if its only 10 items max you can use a single connection.given the limitations i guess this should be ok. – Aravind May 23 '11 at 11:06
so the way I'm going it is fine!? – CustomX May 23 '11 at 11:24

iandayman · Answer 3 · 2011-05-23T10:45:27.383

** edited to account for extra info **

You could adapt the existing "for each" logic in your codebehind to build an arraylist or List<> of products - this array/list should be a property of your Order object. Pass the Order object to the DAL via your BAL as currently.

Then iterate through the products in the Order object in the DAL code(InsertOrder) and either

insert each row individually in a loop
or dynamically build an insert statement for the Order .

You should wrap it in a transaction to ensure the order is rolled back competely if one row fails to insert.

For orders with large amout of products i'd go for @Aravind's answer.

Yes it would be for small amounts, 10 MAX. – CustomX May 23 '11 at 10:29 — CustomX, May 23 '11 at 10:29

score 1 · Answer 4 · answered May 23 '11 at 10:53

1

I’d use SqlClient.SqlBulkCopy. This is effectively a .Net version of Bulk Insert, to use it you need to either have your objects you want to insert in a either a DataTable or create a class to read your data that implements IDDataReader. If your inserting 1,000’s of rows then you should see a dramatic performace increase and much tidier code.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

answered May 23 '11 at 10:53

pingoo

2,074
14
17

I will only be inserting about 10 rows, tops. – CustomX May 23 '11 at 10:54
In that case it might not be worth the extra coding effort, insted use tugberk solution. Bulk Copy is a cleaner solution as it uses Transaction's ect. – pingoo May 23 '11 at 10:59
Trouble I don't really know how I need to change my code in order to use TugBerks method. – CustomX May 23 '11 at 11:04

score 0 · Answer 5 · edited May 23 '17 at 10:00

0

Please go through the following link

How to insert a c# datetime var into SQL Server

edited May 23 '17 at 10:00

Community

1
1

answered May 23 '11 at 11:25

Maxymus

1,460
2
14
22

The trouble is I don't know how to read out my Arraylist out. – CustomX May 23 '11 at 11:34

score 0 · Answer 6 · answered May 23 '11 at 11:36

I will suggest you to use the comma seperated values . Do not send the array list in your DAL Layer instead a function should return a formatted comma seperated value from the Presentation Layer and send this value to DAL and finally seperate the values in your stored procedure.

Alternative You can format your values in an XML and send it to Stored Procedure.

In order to perform it your parameter should be of varchar type.

Using arraylist to insert items into DB

Order.aspx.vb

dalOrder

6 Answers6