Development | .NET Compact Framework

SQLCEDBView
Written by Michael Gledhill  [author's bio]  [read 20367 times]
Edited by Derek

   

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 

Back to .NET Compact Framework | [Article Index]