Why use Prepare() if this MS BUG exists ?

  • Thread starter Thread starter Ciaran
  • Start date Start date
C

Ciaran

Hi there,

I recently came across a SQL CE BUG, which is documented by Microsoft

http://support.microsoft.com/default.aspx?scid=kb;EN-US;824462

The Resolution of this requires that I call the Dispose method for the
SqlCeCommand instances used with the SqlCeDataAdapter. If I use this
resolution my code looks like the following:

SqlCeCommand cmd = null;

protected SqlCeCommand GetMyCommand()
{

if (cmd == null)
{
cmd = new SqlCeCommand();
cmd.CommandText = "SELECT * FROM Table";
cmd.Connection = mySQLConnection;

try
{
cmd.Connection.Open();
cmd.Prepare();
}
Catch {...}
finally
{
cmd.Connection.Close();
}
}

return cmd;
}

mySqlAdapter.SelectCommand = GetMyCommand();

try
{
mySqlAdapter.Fill(...)
}
catch {...}
finally
{
if (sqlAdapter.SelectCommand != null)
sqlAdapter.SelectCommand.Dispose();
}

Here's my question.....

If I need to Dispose of the cmd after I use it, then if I use it again
I need to re-initialise it, which means I need to create the cmd each
time as follows:

protected SqlCeCommand GetMyCommand()
{
SqlCeCommand cmd = new SqlCeCommand()

cmd = new SqlCeCommand();
cmd.CommandText = "SELECT * FROM Table";
cmd.Connection = mySQLConnection;

......
}

Does this mean that there is no point in calling Prepare(), as I
thought
the point of Prepare() was you call it once to compile the cmd once.
Any
insight is much appreciated.
 
The key is that one needs to call Dispose when finished with the command
object - not necessarily after each use of it.

In the example shown in the KB article, the SqlCeDataAdapter and therefore
the SqlCeCommand object are going out of scope and therefore will not be
used after completion of the method.

In the case where Prepare is called and the SqlCeCommand object is used
repeatedly, you shouldn't call Dispose until you no longer plan to use the
prepared SqlCeCommand instance.

Hope this helps.
Jim Wilson, eMVP
http://www.jwhh.com
 
Thanks for the reply Jim,

In my Pocket PC app. the user is costantly querying the data from
the database loaded into the DataSet. Loading the DataSet is an
expensive task (+50,000 records).The handheld is locked down
so that this app. is the only one running, and as is the nature of
the Pocket PC the app. is never 'closed' as we expect in the
traditional sense. To me then it seems like a situation may never
arise where I no longer user the SqlCeCommand. Does this seem right
to you?

Cheers,
Ciaran
 
There is a hotfix available for this problem too. We install it on our
devices and it works well. Unfortunately you have to talk directly to MS
in order to get the patch, but I can mail you the Pocket PC 2003 ARMV4
version if you would like?

Cheers,

Richard.
 
Back
Top