Using arraylist to insert items into DB

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 it开发者_StackOverflowem

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!

---

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()

---

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

---

** 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.

---

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

---

Please go through the following link

How to insert a c# datetime var into SQL Server

---

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.