使用arraylist将项目插入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中创建了一个包含项目的ArrayList。我将这些传递给我的bllOrder,它将它传递给我的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?

这对一个项目很有用,但是我如何为我的ArrayList做呢?

All help is much appreciated!

非常感谢所有帮助!

6 个解决方案

#1

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.

而不是传递单个订单项,传递订单列表,然后在方法内循环它。使它像Public Function InsertOrder(objOrder As List(Of Order))作为Boolean,然后使用objOrder作为Orders列表来循环它。

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

将以下代码放在代码后面的foreach循环中并传递当前项值;

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

    result = cmd.ExecuteNonQuery()

#2

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

将项数组转换为xml字符串,您可以使用openxml在存储过程中进行批量插入。 http://msdn.microsoft.com/en-us/library/ms191268.aspx也引用sql server 2005的旧帖子http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-表到SQL服务器 - 2005年与-XML-parameters.aspx

#3

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

您可以调整代码隐藏中现有的“for each”逻辑来构建产品的arraylist或List <> - 此数组/列表应该是Order对象的属性。通过您的BAL将Order对象传递给DAL。

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

然后迭代DAL代码(InsertOrder)中的Order对象中的产品

insert each row individually in a loop

在循环中单独插入每一行

or dynamically build an insert statement for the Order .

或动态构建Order的insert语句。

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.

对于大量产品的订单,我会选择@ Aravind的答案。

#4

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.

我使用SqlClient.SqlBulkCopy。这实际上是批量插入的.Net版本,要使用它,您需要将要插入的对象插入DataTable或创建一个类来读取实现IDDataReader的数据。如果您插入1,000行,那么您应该看到显着的性能增加和更整洁的代码。

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

#5

Please go through the following link

请浏览以下链接

How to insert a c# datetime var into SQL Server

如何将c#datetime var插入SQL Server

#6

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.

我建议你使用逗号分隔值。不要在DAL层中发送数组列表,而是函数应该从表示层返回格式化的逗号分隔值,并将此值发送到DAL,最后分离存储过程中的值。

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

备选您可以在XML中格式化值并将其发送到存储过程。

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

为了执行它,您的参数应该是varchar类型。

#1