I am dealing with a very large amount of data add it to a dataset
using DataTable.Rows.Add. When creating the new row using .NewRow I
get an OutOfMemoryException at around row 4 million plus. I know that
this is a very large number of rows, but I have 4 GB of Ram and the
Task Manager shows that I am only using about 35% of system memory.
Can someone help me understand why I am getting this error when
apparently I am not running out of memory. Do I need to release any
objects during this process. I am pretty much following what every
example shows on using this code. The code can be seen below.
Dim dtLatestDate As DateTime
Dim dsData As New DataSet
Dim dsScadaHistory As New DataSet1
Dim drScadaHistory, drLookup, drNameLookup() As DataRow
Dim idxRows, idxColumns, iRowCount As Integer
Dim dtScadaDateTime As DateTime
Dim strName As String
Dim keyObject(1), keyNameObject(0) As Object
'set up connections to the two SQL Server
Dim conSurvalent As New
SqlClient.SqlConnection(My.Settings.conSurvalent)
Dim conSQL As New SqlClient.SqlConnection(My.Settings.conSQL)
'Set up commands and dataadapters
Dim cmdGetLatestDate As New SqlClient.SqlCommand("SELECT
MAX(DateTime) FROM ScadaHistory", conSQL)
Dim daSurvalent As New SqlClient.SqlDataAdapter("SELECT * from
ARCHIVE_QSE_Rev3 WHERE TIME > @[EMAIL PROTECTED]
", conSurvalent)
daSurvalent.SelectCommand.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@[EMAIL PROTECTED]
",
System.Data.SqlDbType.DateTime, 8, "TIME"))
'setup data adapter for the names pull
Dim daNames As New SqlClient.SqlDataAdapter("SELECT Name,
NameID FROM Names", conSQL)
'setup insert name into names table
Dim cmdInsertName As New SqlClient.SqlCommand("INSERT INTO
NAMES (Name) VALUES (@[EMAIL PROTECTED]
)", conSQL)
cmdInsertName.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@[EMAIL PROTECTED]
",
System.Data.SqlDbType.VarChar, 40, "Name"))
'load names table into dataset
daNames.Fill(dsScadaHistory, "Names")
Dim Array(2) As String
'get the latest date for SCADA data
conSQL.Open()
dtLatestDate = IIf(IsDBNull(cmdGetLatestDate.ExecuteScalar),
Date.Now.AddYears(-20), cmdGetLatestDate.ExecuteScalar)
conSQL.Close()
'get SCADA data to convert
daSurvalent.SelectCommand.Parameters("@[EMAIL PROTECTED]
").Value =
dtLatestDate
daSurvalent.Fill(dsData, "ScadaHistory")
iRowCount = dsData.Tables(0).Rows.Count
While (idxRows < iRowCount)
'get the date time
dtScadaDateTime =
dsData.Tables(0).Rows(idxRows).Item("TIME")
'initialize so that will skip TIME column
idxColumns = 1
'loop through all columns for each datetime and insert a
row for each match value/status pair
While (idxColumns < dsData.Tables(0).Columns.Count)
'get column name (which is point name)
strName =
dsData.Tables(0).Columns(idxColumns).ColumnName
'get NameID from foreign table
keyNameObject(0) = strName
drNameLookup =
dsScadaHistory.Tables("Names").Select(String.Format("Name = '{0}'",
keyNameObject(0)))
'if the name does not exist then insert the name into
the table, which will write back to the dataset
If (drNameLookup.Length = 0) Then
conSQL.Open()
cmdInsertName.Parameters("@[EMAIL PROTECTED]
").Value = strName
cmdInsertName.ExecuteNonQuery()
'reload dataset with names after insert
daNames.Fill(dsScadaHistory, "Names")
conSQL.Close()
'get NameID from foreign table
keyNameObject(0) = strName
drNameLookup =
dsScadaHistory.Tables("Names").Select(String.Format("Name = '{0}'",
keyNameObject(0)))
End If
'create SCADA history record with datetime and source
name
**************RIGHT HERE IS WHERE I GET THE
ERROR****************************
drScadaHistory =
dsScadaHistory.Tables("ScadaHistory").NewRow
'add datetime to row
drScadaHistory("DateTime") = dtScadaDateTime
'add name to row
drScadaHistory("NameID") = drNameLookup(0)("NameID")
'add value to row
drScadaHistory("Value") =
dsData.Tables(0).Rows(idxRows).Item(idxColumns)
'add status to row
drScadaHistory("Status") =
dsData.Tables(0).Rows(idxRows).Item(idxColumns + 1)
'add datarow to dataset
dsScadaHistory.Tables("ScadaHistory").Rows.Add(drScadaHistory)
drScadaHistory.AcceptChanges()
'increment column
idxColumns += 2
End While
'increment index
idxRows += 1
End While
*****************************THIS IS THE
ERROR****************************
System.OutOfMemoryException was unhandled
Message="Exception of type 'System.OutOfMemoryException' was
thrown."
Source="System.Data"
StackTrace:
at System.Data.Common.DoubleStorage.SetCapacity(Int32
capacity) at System.Data.RecordManager.set_RecordCapacity(Int32
value) at System.Data.RecordManager.GrowRecordCapacity() at
System.Data.RecordManager.NewRecordBase() at
System.Data.DataTable.NewRecord(Int32 sourceRecord) at
System.Data.DataTable.NewRow(Int32 record) at
System.Data.DataTable.NewRow() at DBOSchema.Form1.Form1_Load(Object
sender, EventArgs e) in C:\Do***ents and Settings\All Users\Do***ents
\Visual Studio 2008\Projects\DBOSchema\DBOSchema\Form1.vb:line 74
at System.EventHandler.Invoke(Object sender, EventArgs e) at
System.Windows.Forms.Form.OnLoad(EventArgs e) at
System.Windows.Forms.Form.OnCreateControl() at
System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl() at
System.Windows.Forms.Control.WmShowWindow(Message& m) at
System.Windows.Forms.Control.WndProc(Message& m) at
System.Windows.Forms.ScrollableControl.WndProc(Message& m) at
System.Windows.Forms.ContainerControl.WndProc(Message& m) at
System.Windows.Forms.Form.WmShowWindow(Message& m) at
System.Windows.Forms.Form.WndProc(Message& m) at
System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&
m) at
System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&
m) at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr
hWnd, Int32 msg, IntPtr wparam, IntPtr lparam) at
System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd,
Int32 nCmdShow) at
System.Windows.Forms.Control.SetVisibleCore(Boolean value) at
System.Windows.Forms.Form.SetVisibleCore(Boolean value) at
System.Windows.Forms.Control.set_Visible(Boolean value) at
System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32
reason, ApplicationContext context) at
System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32
reason, ApplicationContext context) at
System.Windows.Forms.Application.Run(ApplicationContext context) at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at
Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[]
commandLine) at DBOSchema.My.MyApplication.Main(String[] Args) in
17d14f5c-a337-4978-8281-53493378c1071.vb:line 81 at
System.AppDomain._nExecuteAssembly(Assembly assembly, String[]
args) at System.AppDomain.ExecuteAssembly(String assemblyFile,
Evidence assemblySecurity, String[] args) at
Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext
executionContext, ContextCallback callback, Object state) at
System.Threading.ThreadHelper.ThreadStart()
InnerException:
|
