StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Bulk Saving?

RE: Bulk Saving?

Dustin Taylor — Tue, 20 Apr 2010 09:14:26 GMT

Hello :),

Which feature are you referring to? The ConcurrencyException options Trent mentioned are already available, and have been for quite a while. The SQLBulkCopy is a function of SQL server, and is very handy.

Bulk Saving?

Edhy Rijo — Tue, 20 Apr 2010 09:14:26 GMT

Hi guys,

I have an import process of CSV files with more than 300 thousand records, I have to read each one and compare against my records and then insert them in another table for future reference with other imports.

My approach is to loop through the imported records collection and then add them to the BO using BO.NewRow(), so far so good, the problem comes when I need to save them using BO.Save(), it takes a long time to actually insert those 300+K records.

I am using the DDT for all my data modeling, and this table is setup to use Store Procedures for all Insert/Update/Delete and also is using an AutoIncrement PK.

In the BO I have turn off the Update/Delete ConcurrencyType and there is no value in the RowVersionOrTimestampColumn.

Is there anything else I may need to do to save those records faster?

RE: Bulk Saving?

ChanKK — Sat, 17 Apr 2010 07:40:26 GMT

Hi
Any plan to add this feature to BO by SF team?

RE: Bulk Saving?

Trent L. Taylor — Sun, 20 Sep 2009 10:55:37 GMT

That is the only place that the dialog is fired from. So if you want to handle this on a BO by BO basis, then you will have to handle the ConcurrencyException event.

RE: Bulk Saving?

Edhy Rijo — Sun, 20 Sep 2009 10:37:25 GMT

Thanks for the info.

I believe the AutoHandleCollisions property will handle All or Nothing action, is there a way to control which BO to exclude from the AutoHandleCollisions action?

RE: Bulk Saving?

Trent L. Taylor — Sun, 20 Sep 2009 10:25:38 GMT

Yes. There are a number of ways to make sure that there is not any concurrency exception popping up, for example, you can handle the ConcurrencyException event and manage this yourself (which you would just ignore any collisions).

Most likely you are doing this on an SF dialog. If so, you can set the AutoHandleCollisions property to false and it should prevent that dialog from appearing.

RE: Bulk Saving?

Edhy Rijo — Fri, 18 Sep 2009 11:57:36 GMT

Trent,

Talking about concurrency, in this same process, when I need to made some modifications to the imported data, I am using an SF BO with UpdateConcurrencyType and DeleteConcurrencyType set to OFF, but when I try to save any modifications I will get the SF Concurrency Manager form but without showing any value to be selected.

In this case I don't want or need any type of concurrency checking, what or where else do I need to check to make sure this SF Concurrency Manager form does not show up and my records are simply just saved?

RE: Bulk Saving?

Edhy Rijo — Wed, 02 Sep 2009 09:03:27 GMT

BTW, is there a way to simply bulk update the records instead of inserting them? Another process is to loop through the inserted records a 2nd or 3th time to re-process them, and after that, the modified ones should be save back to the server.

RE: Bulk Saving?

Trent L. Taylor — Wed, 02 Sep 2009 08:36:53 GMT

Bulk copies....can't live without'em! :D

RE: Bulk Saving?

Keith Chisarik — Tue, 01 Sep 2009 22:38:32 GMT

you can get that better with some tweaking :)

Congrats, I remember well going from 15 minutes to 15 seconds and how good it felt.

RE: Bulk Saving?

Edhy Rijo — Tue, 01 Sep 2009 22:24:43 GMT

[quote][b]Keith Chisarik (09/01/2009)[/b][hr]My bet is ... 6 seconds.[/quote]

It took 33 seconds and I am very, very, very happy. :P

Thanks again.

RE: Bulk Saving?

Keith Chisarik — Tue, 01 Sep 2009 17:39:40 GMT

My bet is ... 6 seconds.

RE: Bulk Saving?

Edhy Rijo — Tue, 01 Sep 2009 17:14:06 GMT

[quote][b]Keith Chisarik (09/01/2009)[/b][hr]ahh yes I was pulling from a fields mapping table because my BO structure does not match my SQL tables structure.

Looks like you got it. FYI, if they ,match you dont need to do the column mapping step and it will 'just work' :)[/quote]

Yes they match, but it did not work without the mapping, so I figured to be in the safe side to just do the Field Name loop and it will not cost anything, I am still gaining a lot of time now, I will do the test in a couple of minutes with 365K records which took almost 10 minutes to save before, will post the results later.

RE: Bulk Saving?

Keith Chisarik — Tue, 01 Sep 2009 17:07:29 GMT

ahh yes I was pulling from a fields mapping table because my BO structure does not match my SQL tables structure.

Looks like you got it. FYI, if they ,match you dont need to do the column mapping step and it will 'just work' :)

RE: Bulk Saving?

Edhy Rijo — Tue, 01 Sep 2009 13:42:47 GMT

Never mind, I found it:
[quote]For Each fieldName As String In Me.BizTransactionItemsImport1.AllFieldsList()
bulkCopy.ColumnMappings.Add(fieldName, fieldName)
Next
[/quote]

Thanks Keith, it works the first run, I will now do a test with couple of thousand of records.

RE: Bulk Saving?

Edhy Rijo — Tue, 01 Sep 2009 13:18:52 GMT

Hi Keith,

I am very close to make this work. I only having some problem with the bulkCopy.ColumnMappings.Add()

[quote]For Each row_conversionrule As BUSINESS.bo_mapfields In Me.Bo_mapfields.GetEnumerable()

bulkCopy.ColumnMappings.Add(row_conversionrule.mapf_sqlfield, row_conversionrule.mapf_sqlfield)

Next[/quote]

This code is not very clear to me, I guess I need to get all the field names from the BO to be mapped in the bulkCopy. Would you please post the For...Each code for a real BO sample?

RE: Bulk Saving?

Keith Chisarik — Tue, 01 Sep 2009 12:36:08 GMT

This saved my butt way back when I was using DB2 (uhggg) , I use it a decent amount with SQL and it has treated me good :)

Enjoy!

RE: Bulk Saving?

Edhy Rijo — Tue, 01 Sep 2009 12:26:59 GMT

Hi Keith,

Thanks a lot for the code sharing. I was just reading an article about SqlBulkCopy [url=http://dotnetslackers.com/articles/ado_net/SqlBulkCopy_in_ADO_NET_2_0.aspx]here[/url] and then saw your post.

Yes, my BO match the SQL data structure. I will try to implement this one.

RE: Bulk Saving?

Keith Chisarik — Tue, 01 Sep 2009 12:22:55 GMT

if your SQL table matches the structure of your BO, you can skip the mapping line, ohterwise us it to map your datatable fields to your SQL table fields before the copy.

Optional: bulkCopy.ColumnMappings()

RE: Bulk Saving?

Keith Chisarik — Tue, 01 Sep 2009 12:21:09 GMT

SQLBULKCOPY

Somthing like this: (pass in the currentdatatable fo your BO)

Private Sub bulkLoadFromMultipleTables(ByVal dtToInsert As DataTable)

Dim x As DateTime = DateTime.Now

Using destinationConnection As SqlConnection = _

New SqlConnection(MicroFour.StrataFrame.Data.DataLayer.DataSources("sql").ConnectionString)

destinationConnection.Open()

Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationConnection)

bulkCopy.DestinationTableName = _sqlTable.Trim

Try

'bulkCopy.NotifyAfter = 10000

'bulkCopy.SqlRowsCopied += New SqlRowsCopiedEventHandler(s_SqlRowsCopied)

bulkCopy.BatchSize = 1000

'should already match due to the join process and field aliasing done

For Each row_conversionrule As BUSINESS.bo_mapfields In Me.Bo_mapfields.GetEnumerable()

bulkCopy.ColumnMappings.Add(row_conversionrule.mapf_sqlfield, row_conversionrule.mapf_sqlfield)

bulkCopy.WriteToServer(dtToInsert)

bulkCopy.Close()

Dim y As DateTime = DateTime.Now

Dim diff As TimeSpan = y - x

MsgBox("seconds: " & diff.Seconds & ":" & diff.Milliseconds)

Catch ex As Exception

MsgBox(ex.ToString)

Finally

' Close the SqlDataReader. The SqlBulkCopy

' object is automatically closed at the end

' of the Using block.

dtToInsert.Dispose()

End Try

End Using

End Sub

' of the Using block.