SNichols1124
asked on
System.OutofMemoryException error when inserting records using sql server ce 3.0 + .Net Framework 4.0
I have an unusual problem. I have a bit of code that runs fine in the .NET 2.0 Framework with Compact Framework 2.0, but generates a 'System.OutofMemoryExcepti
Private Shared Sub UpdatePLUTableInSIMSDataba
AppServices.Logger.Write("
Using comm As New SqlServerCe.SqlCeCommand()
'Set connection string
comm.Connection = DBClass.conn
'Drop PLU table
Try
comm.CommandText = "DROP TABLE PLU"
comm.ExecuteNonQuery()
Catch ex As Exception
AppServices.Logger.Write("
End Try
'Create PLU table
Try
Dim createSDF_PLUTable As String = DBClass.LoadResource("Crea
comm.CommandText = createSDF_PLUTable
comm.ExecuteNonQuery()
Catch ex As Exception
AppServices.Logger.Write("
End Try
'Retrieve all records from PLU and insert them into the SIMSDB
Using dt As DataTable = AppServices.DataBase.ExecS
'Execute Insert For Each Record.
Dim insertSDF_PLU As String = DBClass.LoadResource("SDF_
Dim sbInsert As New System.Text.StringBuilder(
For Each row As DataRow In dt.Rows
sbInsert.Append(insertSDF_
sbInsert.Replace("{0}", IIf(row.Item(0) Is DBNull.Value, _
"null", row.Item(0)).ToString) 'PLU
sbInsert.Replace("{1}", IIf(row.Item(1) Is DBNull.Value, _
"null", row.Item(1)).ToString) 'OLD_PLU
sbInsert.Replace("{2}", IIf(row.Item(2) Is DBNull.Value, _
"null", row.Item(2)).ToString) 'DEPT
sbInsert.Replace("{3}", IIf(row.Item(3) Is DBNull.Value, _
"null", row.Item(3)).ToString) 'DESC
sbInsert.Replace("{4}", IIf(row.Item(4) Is DBNull.Value, _
"null", row.Item(4)).ToString) 'PRICE
sbInsert.Replace("{5}", IIf(row.Item(5) Is DBNull.Value, _
"null", row.Item(5)).ToString) 'RECALLED_ITEM
sbInsert.Replace("{6}", IIf(row.Item(6) Is DBNull.Value, _
"null", row.Item(6)).ToString) 'BSL
sbInsert.Replace("{7}", IIf(row.Item(7) Is DBNull.Value, _
"null", row.Item(7)).ToString) 'CURRENT_ON_HAND
sbInsert.Replace("{8}", IIf(row.Item(8) Is DBNull.Value, _
"null", row.Item(8)).ToString) 'ITEM_LIFECYCLE
sbInsert.Replace("{9}", IIf(row.Item(9) Is DBNull.Value, _
"null", row.Item(9)).ToString) 'VELOCITY_CODE
sbInsert.Replace("{10}", IIf(row.Item(10) Is DBNull.Value, _
"null", row.Item(10)).ToString) 'INVENTORY_TYPE
sbInsert.Replace("{11}", IIf(row.Item(11) Is DBNull.Value, _
"null", row.Item(11)).ToString) 'VENDOR_NUMBER
sbInsert.Replace("{12}", IIf(row.Item(12) Is DBNull.Value, _
"null", row.Item(12)).ToString) 'SBT_FLAG
sbInsert.Replace("{13}", IIf(row.Item(13) Is DBNull.Value, _
"null", row.Item(13)).ToString) 'ORDER_CAP
sbInsert.Replace("{14}", IIf(row.Item(17) Is DBNull.Value, _
"N", row.Item(17)).ToString) 'Alcohol_Flag
sbInsert.Replace("'{15}'",
"null", String.Format("'{0}'", row.Item(18).ToString))) 'Prim_SKU
sbInsert.Replace("'{16}'",
"null", String.Format("'{0}'", row.Item(19).ToString))) 'Expanded Item Description 1
sbInsert.Replace("'{17}'",
"null", String.Format("'{0}'", row.Item(20).ToString))) 'Expanded Item Description 2
sbInsert.Replace("'{18}'",
"null", String.Format("'{0}'", row.Item(21).ToString))) 'Expanded Item Description 3
sbInsert.Replace("'{19}'",
"null", String.Format("'{0}'", row.Item(22).ToString))) 'Expanded Item Description 4
sbInsert.Replace("{20}", IIf(row.Item(23) Is DBNull.Value, _
"null", row.Item(23).ToString)) 'UOM Units
sbInsert.Replace("'{21}'",
"null", String.Format("'{0}'", row.Item(24).ToString))) 'Unit of Measure
sbInsert.Replace("{22}", IIf(row.Item(25) Is DBNull.Value, _
"null", row.Item(25).ToString)) 'Unit Price
sbInsert.Replace("'{23}'",
"null", String.Format("'{0}'", row.Item(26).ToString))) 'Label Price
sbInsert.Replace("{24}", IIf(row.Item(27) Is DBNull.Value, _
"null", row.Item(27).ToString)) 'Stocking UPC
sbInsert.Replace("{25}", IIf(row.Item(28) Is DBNull.Value, _
"null", row.Item(28).ToString)) 'Print Label
sbInsert.Replace("{26}", IIf(row.Item(29) Is DBNull.Value, _
"null", row.Item(29).ToString)) 'Print GTIN
Try
comm.CommandText = sbInsert.ToString
comm.ExecuteNonQuery()
Catch ex As Exception
AppServices.Logger.Write("
End Try
sbInsert.Length = 0
Next row
End Using
End Using
AppServices.Logger.Write("
End Sub
Private Shared Sub UpdatePLUTableInSIMSDataba
AppServices.Logger.Write("
Using comm As New SqlServerCe.SqlCeCommand()
'Set connection string
comm.Connection = DBClass.conn
'Drop PLU table
Try
comm.CommandText = "DROP TABLE PLU"
comm.ExecuteNonQuery()
Catch ex As Exception
AppServices.Logger.Write("
End Try
'Create PLU table
Try
Dim createSDF_PLUTable As String = DBClass.LoadResource("Crea
comm.CommandText = createSDF_PLUTable
comm.ExecuteNonQuery()
Catch ex As Exception
AppServices.Logger.Write("
End Try
'Retrieve all records from PLU and insert them into the SIMSDB
Using dt As DataTable = AppServices.DataBase.ExecS
'Execute Insert For Each Record.
Dim insertSDF_PLU As String = DBClass.LoadResource("SDF_
Dim sbInsert As New System.Text.StringBuilder(
For Each row As DataRow In dt.Rows
sbInsert.Append(insertSDF_
sbInsert.Replace("{0}", IIf(row.Item(0) Is DBNull.Value, _
"null", row.Item(0)).ToString) 'PLU
sbInsert.Replace("{1}", IIf(row.Item(1) Is DBNull.Value, _
"null", row.Item(1)).ToString) 'OLD_PLU
sbInsert.Replace("{2}", IIf(row.Item(2) Is DBNull.Value, _
"null", row.Item(2)).ToString) 'DEPT
sbInsert.Replace("{3}", IIf(row.Item(3) Is DBNull.Value, _
"null", row.Item(3)).ToString) 'DESC
sbInsert.Replace("{4}", IIf(row.Item(4) Is DBNull.Value, _
"null", row.Item(4)).ToString) 'PRICE
sbInsert.Replace("{5}", IIf(row.Item(5) Is DBNull.Value, _
"null", row.Item(5)).ToString) 'RECALLED_ITEM
sbInsert.Replace("{6}", IIf(row.Item(6) Is DBNull.Value, _
"null", row.Item(6)).ToString) 'BSL
sbInsert.Replace("{7}", IIf(row.Item(7) Is DBNull.Value, _
"null", row.Item(7)).ToString) 'CURRENT_ON_HAND
sbInsert.Replace("{8}", IIf(row.Item(8) Is DBNull.Value, _
"null", row.Item(8)).ToString) 'ITEM_LIFECYCLE
sbInsert.Replace("{9}", IIf(row.Item(9) Is DBNull.Value, _
"null", row.Item(9)).ToString) 'VELOCITY_CODE
sbInsert.Replace("{10}", IIf(row.Item(10) Is DBNull.Value, _
"null", row.Item(10)).ToString) 'INVENTORY_TYPE
sbInsert.Replace("{11}", IIf(row.Item(11) Is DBNull.Value, _
"null", row.Item(11)).ToString) 'VENDOR_NUMBER
sbInsert.Replace("{12}", IIf(row.Item(12) Is DBNull.Value, _
"null", row.Item(12)).ToString) 'SBT_FLAG
sbInsert.Replace("{13}", IIf(row.Item(13) Is DBNull.Value, _
"null", row.Item(13)).ToString) 'ORDER_CAP
sbInsert.Replace("{14}", IIf(row.Item(17) Is DBNull.Value, _
"N", row.Item(17)).ToString) 'Alcohol_Flag
sbInsert.Replace("'{15}'",
"null", String.Format("'{0}'", row.Item(18).ToString))) 'Prim_SKU
sbInsert.Replace("'{16}'",
"null", String.Format("'{0}'", row.Item(19).ToString))) 'Expanded Item Description 1
sbInsert.Replace("'{17}'",
"null", String.Format("'{0}'", row.Item(20).ToString))) 'Expanded Item Description 2
sbInsert.Replace("'{18}'",
"null", String.Format("'{0}'", row.Item(21).ToString))) 'Expanded Item Description 3
sbInsert.Replace("'{19}'",
"null", String.Format("'{0}'", row.Item(22).ToString))) 'Expanded Item Description 4
sbInsert.Replace("{20}", IIf(row.Item(23) Is DBNull.Value, _
"null", row.Item(23).ToString)) 'UOM Units
sbInsert.Replace("'{21}'",
"null", String.Format("'{0}'", row.Item(24).ToString))) 'Unit of Measure
sbInsert.Replace("{22}", IIf(row.Item(25) Is DBNull.Value, _
"null", row.Item(25).ToString)) 'Unit Price
sbInsert.Replace("'{23}'",
"null", String.Format("'{0}'", row.Item(26).ToString))) 'Label Price
sbInsert.Replace("{24}", IIf(row.Item(27) Is DBNull.Value, _
"null", row.Item(27).ToString)) 'Stocking UPC
sbInsert.Replace("{25}", IIf(row.Item(28) Is DBNull.Value, _
"null", row.Item(28).ToString)) 'Print Label
sbInsert.Replace("{26}", IIf(row.Item(29) Is DBNull.Value, _
"null", row.Item(29).ToString)) 'Print GTIN
Try
comm.CommandText = sbInsert.ToString
comm.ExecuteNonQuery()
Catch ex As Exception
AppServices.Logger.Write("
End Try
sbInsert.Length = 0
Next row
End Using
End Using
AppServices.Logger.Write("
End Sub
Last Comment
ASKER
The code runs fine in the .NET 2.0 Framework. We have upgraded to .NET 4.0 Framework and the bit of code that runs fine in that environment, no longer runs in the .NET 4.0 Framework with Compact Framework 2.0. It is a Windows applications on Windows XP SP3. The file that is being imported is around 50 MB in size on average and it throws the 'System.OutofMemoryExcepti
I understand the problem now. What confuses me is your reference to compact framework. If you are not using compact framework (as you are doing winforms), there is no need to mention it.
>The file that is being imported is around 50 MB in size
What sort of file and what type of importing you are doing? Can you show the code?
>The file that is being imported is around 50 MB in size
What sort of file and what type of importing you are doing? Can you show the code?
ASKER
The applications It is .NET Framework 4.0 that is grabbing the data from SQL Server 2005 and loading data into a SQL Table on SQL Server Compact Edition to be used on a device. The entire routine is listed in the original post. It is loading a file into a datatable (text file) and inserting one row at a time. Around row 200,000, it starts throwing the out of memory exception.
Hmm. Although the capacity specification states that maximum number of rows is limited by available storage, looks like SQL CE is being unable to handle all those rows.
ASKER
It appears this way, but when running the application on .NET Framework 2.0 on the Windows XP SP3 box, the same file using the same code works like a charm.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
We have implemented this solution and it has corrected the problem.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Where is the code running? On a device?