0

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!

Improve this question

6 Answers 6

Reset to default
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()
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

So do I pass on my ArrayList?
What if I just stick my INSERT query inside the For Each item loop? Works too doesn't it? Or is this more intensive?
I am not sure about the performance here but leave the connection open until you finish your inserts. that sounds better.
so make a function just to open and one to close the connection?
no, put your foreach loop between myconn.open() and myconn.close()
|
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

Improve this answer

9 Comments

It would be for 10 items max, and I can't use SP with my current provider at the moment (f*** provider) ...
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.
indeed, do you know how tugberk wants to do it?
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.
so item is my arraylist? and how would I pass it on to my dalOrder?
|
1

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

Improve this answer

1 Comment

Yes it would be for small amounts, 10 MAX.
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

Improve this answer

3 Comments

I will only be inserting about 10 rows, tops.
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.
Trouble I don't really know how I need to change my code in order to use TugBerks method.
0

Please go through the following link

How to insert a c# datetime var into SQL Server

Improve this answer

1 Comment

The trouble is I don't know how to read out my Arraylist out.
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.