Memory Leak with SqlCe: ExecuteNonQuery...

  • Thread starter Thread starter AndreB
  • Start date Start date
A

AndreB

Hi,

This is a Smart Device App with Windows CE.NET.

I experience a memory leak with the following code. Every time I append 500
records to my TestTable, I display a Messagebox.
If and in the "Start | Settings | Control Panel | System | Memory" window,
I can see that more than 200 KB of memory are lost.

My main code is a little loop.

int RecordNumber = 1;

while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}

Does anybody could tell me where I am wrong in my code.

Many thanks because I'm exhausted,

AndreB.

Following is the complete code to make your complete test.

//- Form1.cs

using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
// Add Reference to System.Data.Common;
using System.Data.Common;
// // Add Reference to System.Data.SqlServerCe;
using System.Data.SqlServerCe;
using System.IO;

namespace TestSqlCe
{
public class Form1 : System.Windows.Forms.Form
{
#region Constructor
public Form1()
{
InitializeComponent();

}
#endregion Constructor

#region Dispose
protected override void Dispose( bool disposing )
{
base.Dispose( disposing );
}
#endregion Dispose

#region InitializeComponent
private void InitializeComponent()
{
//
// Form1
//
this.ClientSize = new System.Drawing.Size(240, 275);
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
}
#endregion

#region Main
static void Main()
{
Application.Run(new Form1());
}
#endregion Main

#region Form1_Load
private void Form1_Load(object sender, System.EventArgs e)
{
string FileName = @"\My Documents\TEST.sdf";
if (File.Exists(FileName))
File.Delete(FileName);

// Create Database and Table
SqlCeEngine varEngine = new SqlCeEngine("Data Source = " +
FileName);
varEngine.CreateDatabase();
SqlCeConnection _ConnectionSqlCe = new SqlCeConnection("Data Source
= " + FileName);
SqlCeCommand varCreate = _ConnectionSqlCe.CreateCommand();
varCreate.CommandText = "Create TABLE TestTable (RecordNum int
IDENTITY(1,1),cCode nvarchar(7) PRIMARY KEY)";
_ConnectionSqlCe.Open();
varCreate.ExecuteNonQuery();
_ConnectionSqlCe.Close();
varCreate.Dispose();

// Create SqlCeCommand
_ConnectionSqlCe = new SqlCeConnection("Data Source = " +
FileName);
_ConnectionSqlCe.Open();
SqlCeCommand _CmdAppend = _ConnectionSqlCe.CreateCommand();
_CmdAppend.CommandText = "INSERT INTO TestTable (cCode) VALUES
(?)";
_CmdAppend.Parameters.Add(new
SqlCeParameter("@cCode",SqlDbType.NVarChar, 7,"cCode" ));

int RecordNumber = 1;
while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}
_CmdAppend.Dispose();
_ConnectionSqlCe.Close();
}
#endregion Form1_Load

}
}
 
Are you actually running out of memory? The GC doesn't just collect all the
time, it only does when necessary.

-Chris


AndreB said:
Hi,

This is a Smart Device App with Windows CE.NET.

I experience a memory leak with the following code. Every time I append
500
records to my TestTable, I display a Messagebox.
If and in the "Start | Settings | Control Panel | System | Memory"
window,
I can see that more than 200 KB of memory are lost.

My main code is a little loop.

int RecordNumber = 1;

while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}

Does anybody could tell me where I am wrong in my code.

Many thanks because I'm exhausted,

AndreB.

Following is the complete code to make your complete test.

//- Form1.cs

using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
// Add Reference to System.Data.Common;
using System.Data.Common;
// // Add Reference to System.Data.SqlServerCe;
using System.Data.SqlServerCe;
using System.IO;

namespace TestSqlCe
{
public class Form1 : System.Windows.Forms.Form
{
#region Constructor
public Form1()
{
InitializeComponent();

}
#endregion Constructor

#region Dispose
protected override void Dispose( bool disposing )
{
base.Dispose( disposing );
}
#endregion Dispose

#region InitializeComponent
private void InitializeComponent()
{
//
// Form1
//
this.ClientSize = new System.Drawing.Size(240, 275);
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
}
#endregion

#region Main
static void Main()
{
Application.Run(new Form1());
}
#endregion Main

#region Form1_Load
private void Form1_Load(object sender, System.EventArgs e)
{
string FileName = @"\My Documents\TEST.sdf";
if (File.Exists(FileName))
File.Delete(FileName);

// Create Database and Table
SqlCeEngine varEngine = new SqlCeEngine("Data Source = " +
FileName);
varEngine.CreateDatabase();
SqlCeConnection _ConnectionSqlCe = new SqlCeConnection("Data
Source
= " + FileName);
SqlCeCommand varCreate = _ConnectionSqlCe.CreateCommand();
varCreate.CommandText = "Create TABLE TestTable (RecordNum int
IDENTITY(1,1),cCode nvarchar(7) PRIMARY KEY)";
_ConnectionSqlCe.Open();
varCreate.ExecuteNonQuery();
_ConnectionSqlCe.Close();
varCreate.Dispose();

// Create SqlCeCommand
_ConnectionSqlCe = new SqlCeConnection("Data Source = " +
FileName);
_ConnectionSqlCe.Open();
SqlCeCommand _CmdAppend = _ConnectionSqlCe.CreateCommand();
_CmdAppend.CommandText = "INSERT INTO TestTable (cCode) VALUES
(?)";
_CmdAppend.Parameters.Add(new
SqlCeParameter("@cCode",SqlDbType.NVarChar, 7,"cCode" ));

int RecordNumber = 1;
while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}
_CmdAppend.Dispose();
_ConnectionSqlCe.Close();
}
#endregion Form1_Load

}
}
Click to expand...
 
Hi Chris,

No, I'm not getting "out of memory" but when the memory comes to be low, the
loop becomes searching for memory and the app is blocked.

I've localized that problem on my real app where I can "Insert" only a few
records (less than 20 because I miss 100k for every "Insert" ) and then it
never ends.

Thanks, AndreB.


Are you actually running out of memory? The GC doesn't just collect all
the time, it only does when necessary.

-Chris


AndreB said:
Hi,

This is a Smart Device App with Windows CE.NET.

I experience a memory leak with the following code. Every time I append
500
records to my TestTable, I display a Messagebox.
If and in the "Start | Settings | Control Panel | System | Memory"
window,
I can see that more than 200 KB of memory are lost.

My main code is a little loop.

int RecordNumber = 1;

while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}

Does anybody could tell me where I am wrong in my code.

Many thanks because I'm exhausted,

AndreB.

Following is the complete code to make your complete test.

//- Form1.cs

using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
// Add Reference to System.Data.Common;
using System.Data.Common;
// // Add Reference to System.Data.SqlServerCe;
using System.Data.SqlServerCe;
using System.IO;

namespace TestSqlCe
{
public class Form1 : System.Windows.Forms.Form
{
#region Constructor
public Form1()
{
InitializeComponent();

}
#endregion Constructor

#region Dispose
protected override void Dispose( bool disposing )
{
base.Dispose( disposing );
}
#endregion Dispose

#region InitializeComponent
private void InitializeComponent()
{
//
// Form1
//
this.ClientSize = new System.Drawing.Size(240, 275);
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
}
#endregion

#region Main
static void Main()
{
Application.Run(new Form1());
}
#endregion Main

#region Form1_Load
private void Form1_Load(object sender, System.EventArgs e)
{
string FileName = @"\My Documents\TEST.sdf";
if (File.Exists(FileName))
File.Delete(FileName);

// Create Database and Table
SqlCeEngine varEngine = new SqlCeEngine("Data Source = " +
FileName);
varEngine.CreateDatabase();
SqlCeConnection _ConnectionSqlCe = new SqlCeConnection("Data
Source
= " + FileName);
SqlCeCommand varCreate = _ConnectionSqlCe.CreateCommand();
varCreate.CommandText = "Create TABLE TestTable (RecordNum int
IDENTITY(1,1),cCode nvarchar(7) PRIMARY KEY)";
_ConnectionSqlCe.Open();
varCreate.ExecuteNonQuery();
_ConnectionSqlCe.Close();
varCreate.Dispose();

// Create SqlCeCommand
_ConnectionSqlCe = new SqlCeConnection("Data Source = " +
FileName);
_ConnectionSqlCe.Open();
SqlCeCommand _CmdAppend = _ConnectionSqlCe.CreateCommand();
_CmdAppend.CommandText = "INSERT INTO TestTable (cCode) VALUES
(?)";
_CmdAppend.Parameters.Add(new
SqlCeParameter("@cCode",SqlDbType.NVarChar, 7,"cCode" ));

int RecordNumber = 1;
while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}
_CmdAppend.Dispose();
_ConnectionSqlCe.Close();
}
#endregion Form1_Load

}
}
Click to expand...
Click to expand...
 
Hi again Chris,

I guess the GC has NOTHING to release in MY loop as I delete and create NO OBJECT in it:

while (true)
{
_CmdAppend.Parameters["@cCode" ].Value = RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();
RecordNumber++;
}

Thanks Chris,
AndreB.

Are you actually running out of memory? The GC doesn't just collect all the
time, it only does when necessary.

-Chris


AndreB said:
Hi,

This is a Smart Device App with Windows CE.NET.

I experience a memory leak with the following code. Every time I append
500
records to my TestTable, I display a Messagebox.
If and in the "Start | Settings | Control Panel | System | Memory"
window,
I can see that more than 200 KB of memory are lost.

My main code is a little loop.

int RecordNumber = 1;

while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}

Does anybody could tell me where I am wrong in my code.

Many thanks because I'm exhausted,

AndreB.

Following is the complete code to make your complete test.

//- Form1.cs

using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
// Add Reference to System.Data.Common;
using System.Data.Common;
// // Add Reference to System.Data.SqlServerCe;
using System.Data.SqlServerCe;
using System.IO;

namespace TestSqlCe
{
public class Form1 : System.Windows.Forms.Form
{
#region Constructor
public Form1()
{
InitializeComponent();

}
#endregion Constructor

#region Dispose
protected override void Dispose( bool disposing )
{
base.Dispose( disposing );
}
#endregion Dispose

#region InitializeComponent
private void InitializeComponent()
{
//
// Form1
//
this.ClientSize = new System.Drawing.Size(240, 275);
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
}
#endregion

#region Main
static void Main()
{
Application.Run(new Form1());
}
#endregion Main

#region Form1_Load
private void Form1_Load(object sender, System.EventArgs e)
{
string FileName = @"\My Documents\TEST.sdf";
if (File.Exists(FileName))
File.Delete(FileName);

// Create Database and Table
SqlCeEngine varEngine = new SqlCeEngine("Data Source = " +
FileName);
varEngine.CreateDatabase();
SqlCeConnection _ConnectionSqlCe = new SqlCeConnection("Data
Source
= " + FileName);
SqlCeCommand varCreate = _ConnectionSqlCe.CreateCommand();
varCreate.CommandText = "Create TABLE TestTable (RecordNum int
IDENTITY(1,1),cCode nvarchar(7) PRIMARY KEY)";
_ConnectionSqlCe.Open();
varCreate.ExecuteNonQuery();
_ConnectionSqlCe.Close();
varCreate.Dispose();

// Create SqlCeCommand
_ConnectionSqlCe = new SqlCeConnection("Data Source = " +
FileName);
_ConnectionSqlCe.Open();
SqlCeCommand _CmdAppend = _ConnectionSqlCe.CreateCommand();
_CmdAppend.CommandText = "INSERT INTO TestTable (cCode) VALUES
(?)";
_CmdAppend.Parameters.Add(new
SqlCeParameter("@cCode",SqlDbType.NVarChar, 7,"cCode" ));

int RecordNumber = 1;
while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}
_CmdAppend.Dispose();
_ConnectionSqlCe.Close();
}
#endregion Form1_Load

}
}
Click to expand...
Click to expand...
 
I answered this for you in your duplicate post on
microsoft.public.sqlserver.ce, which is the appropriate
newsgroup for this question.
--
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com

AndreB said:
Hi,

This is a Smart Device App with Windows CE.NET.

I experience a memory leak with the following code. Every time I append
500
records to my TestTable, I display a Messagebox.
If and in the "Start | Settings | Control Panel | System | Memory"
window,
I can see that more than 200 KB of memory are lost.

My main code is a little loop.

int RecordNumber = 1;

while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}

Does anybody could tell me where I am wrong in my code.

Many thanks because I'm exhausted,

AndreB.

Following is the complete code to make your complete test.

//- Form1.cs

using System;
using System.Drawing;
using System.Collections;
using System.Windows.Forms;
using System.Data;
// Add Reference to System.Data.Common;
using System.Data.Common;
// // Add Reference to System.Data.SqlServerCe;
using System.Data.SqlServerCe;
using System.IO;

namespace TestSqlCe
{
public class Form1 : System.Windows.Forms.Form
{
#region Constructor
public Form1()
{
InitializeComponent();

}
#endregion Constructor

#region Dispose
protected override void Dispose( bool disposing )
{
base.Dispose( disposing );
}
#endregion Dispose

#region InitializeComponent
private void InitializeComponent()
{
//
// Form1
//
this.ClientSize = new System.Drawing.Size(240, 275);
this.Text = "Form1";
this.Load += new System.EventHandler(this.Form1_Load);
}
#endregion

#region Main
static void Main()
{
Application.Run(new Form1());
}
#endregion Main

#region Form1_Load
private void Form1_Load(object sender, System.EventArgs e)
{
string FileName = @"\My Documents\TEST.sdf";
if (File.Exists(FileName))
File.Delete(FileName);

// Create Database and Table
SqlCeEngine varEngine = new SqlCeEngine("Data Source = " +
FileName);
varEngine.CreateDatabase();
SqlCeConnection _ConnectionSqlCe = new SqlCeConnection("Data
Source
= " + FileName);
SqlCeCommand varCreate = _ConnectionSqlCe.CreateCommand();
varCreate.CommandText = "Create TABLE TestTable (RecordNum int
IDENTITY(1,1),cCode nvarchar(7) PRIMARY KEY)";
_ConnectionSqlCe.Open();
varCreate.ExecuteNonQuery();
_ConnectionSqlCe.Close();
varCreate.Dispose();

// Create SqlCeCommand
_ConnectionSqlCe = new SqlCeConnection("Data Source = " +
FileName);
_ConnectionSqlCe.Open();
SqlCeCommand _CmdAppend = _ConnectionSqlCe.CreateCommand();
_CmdAppend.CommandText = "INSERT INTO TestTable (cCode) VALUES
(?)";
_CmdAppend.Parameters.Add(new
SqlCeParameter("@cCode",SqlDbType.NVarChar, 7,"cCode" ));

int RecordNumber = 1;
while (true)
{
_CmdAppend.Parameters["@cCode" ].Value =
RecordNumber.ToString().PadRight(7,' ');
_CmdAppend.ExecuteNonQuery();

if (RecordNumber % 500 == 0)
{
if (MessageBox.Show("\n\n" + RecordNumber.ToString()+ "
Record(s)\n\n","Test
Append",MessageBoxButtons.OKCancel,MessageBoxIcon.None,MessageBoxDefaultButton.Button2)
!= DialogResult.OK)
break;
}
RecordNumber++;
}
_CmdAppend.Dispose();
_ConnectionSqlCe.Close();
}
#endregion Form1_Load

}
}
Click to expand...
 
I have the same problem, but I do not find anything similar in the microsoft.public.sqlserver.ce forum.

Could you please add the link hier? or write something here?
Thanks in advance
 
Back
Top