Page 1
Page 2
Page 3
Page 4
Page 5
Of course,
usually, the SQL queries that you'll run on your SQLCE databases
are going to be a little more complicated than the example
we just used:
UPDATE employees SET City='Toronto' WHERE EmployeeID=2
Usually,
the record you'll want to UPDATE will depend on a record
or item you've clicked on on the device, the INSERT will
depend on data you've entered, and so on. It's
hardly surprising that by the time you've put together an
SQL command to run, there might be errors in it.
The
bad news: I can't fix your SQL bugs for you.
Sorry. I've already got my own collection of bugs
to fix, thanks...!
The
good news: Using the following tips, you can constantly
view, on your Desktop, the last SQL statement that was run
on your device. And, trust me, when an error occurs, having
a window on your desktop showing that you've just run an
incorrect SQL command:
UPDATE employeesSET City=Toronto WHERE
EmployeeID=2
is a lot
easier than trying to interpret those vague .Net exception
errors on the device.
Suggestion
2
Remember
I told you how SQLCE Database Viewer allows you to choose
how each type of file will be opened: either on the device
or on your Desktop ? Well, there's a third option, to open
the file using the built-in text editor.
Don't get
excited, I haven't written a full-blown text editor.
This is a very simple ASCII/UNICODE text-file editor,
with two Pocket PC friendly features:
- When you click on File\Save, it saves
the file straight back to your device.
- Every few seconds, the device's copy of the file is
checked. If it's been updated on the device, it's contents
will be refreshed (reloaded) on the Desktop.
So now,
we can add a few lines of code to our "Run some SQL" function,
which will save the SQL command to a simple one-line text
file on the device. Then, on our Desktop, we'll keep
this text file open in "SQLCE Database Viewer", and we'll
constantly have a dispay of the last SQL command that was
run.
using System.IO;
public void WriteSQLtofile(string SQLcmd)
{
StreamWriter sw;
sw = new StreamWriter("\\My Documents\\SQL.txt");
sw.Write(SQLcmd);
sw.Close();
}
private void RunSQL(string SQLcmd)
{
#if DEBUG
// In DEBUG mode (only), open up
the connection to the
// SQLCE database each time we run
an SQL command.
SqlCeConnection m_conn;
m_conn = new SqlCeConnection(connectionString);
m_conn.Open();
WriteSQLtofile(SQLcmd);
#else
// In Release mode, we'll use a
Global SqlCeConnection
// variable for an SQLCE connection
that'll always
// be kept open.
#endif
... (etc)
}
As promised,
when we run an invalid SQL command in our .Net CF program,
it'll still throw an exception, but within a few seconds,
our text-editor program refreshes itself and shows us the
SQL that caused the problem:
The eagle-eyed
amongst you will have noticed it's even "cleaned up" that
SQL statement - that single-line SQL statement has been
split up into five lines - making it easier to read.
This is just done for clarity (and is a lifesaver when your
SQL statements start getting complicated), and can be turned
off using the "Tidy it up for me!" checkbox.
Needless to say, you could take
this further, perhaps changing your exception handler code
to write the SQL error description to a different file.
This function
becomes invaluable when you are testing your application.
Supposing you're running your .Net CF application, and are
busy adding a new record. You hit your Save button
to save your changes, and straightaway, you get to see the
SQL INSERT command that's being run on your database.
Previous Page
Next Page