…meie igapäevast IT’d anna meile igapäev…

2008-10-28

SQL CE Database Editor v2 – BETA

Filed under: Programmeerimine — Sander @ 15:38:19

A bit more than a month ago, I released SQL CE Database Viewer. I promised in that post I would do a version 2 – with ability to edit the data and some other niceties.

Now I’ve finally finished the beta version 2 of it – so all the standard disclaimers apply. I plan to keep it in beta stage for a month – so, I will release the final version at the end of November or early December. I will put the whole project to CodePlex, under Mozilla Public License v1.1 – so, it is obviously open source and freeware.

As of now, you have to get the installer from here (1.1 MB).

All icons are from FamFamFam, SQL syntax highlighting is based on a method described in istaysharp.net. The installer includes Microsoft SQL CE 3.5 dll’s, so these are covered by SQL Server 2005 Compact 3.5 Redistribution EULA.

SQL CE Database Editor requires .NET 2.0 framework – and as with all .NET programs, the first time you start it, it will take much longer to start than subsequent times, as the native image is created on the first run.

Please report all issues and suggestions as comments to this post.

Features

  • View and edit data in both encrypted and unencrypted Microsoft SQL CE 3.5 databases
  • Simple and easily understandable overview of tables, indexes, keys and constraints
  • Run custom queries and DDL commands
  • Easily access SQL CE 3.5 utilities: compact, repair, shrink, verify and upgrade databases (from Microsoft SQL Server 2005 Compact Edition (3.1) and SQL Server Mobile Edition (3.0))
  • Does not require MS SQL CE 3.5
  • Does not require MS SQL Management Studio
  • Works from Windows Explorer (create association manually)

SQL CE Editor - database opened

After you open the database, you will get some general information about the database – size, location and so forth. Select individual tree nodes to see the table data, indexes, keys and constraints.

SQL CE Editor - column view

If you expand table node, you will get the information about the table columns. Column nodes with key icons are primary keys – and you can see the information about column in tooltip (Data type, Precision, Scale, Max Length).

Similarly, tooltips for indexes give more information about the details of that index.

If your database has images in it, double-click on the datagrid cell that displays the image fragment to make it bigger. On the image below are images from Microsoft’s Northwind.sdf sample database. Double-click on enlarged image will restore it to the original size.

SQL CE Editor image fields

Query window

After you’ve opened the database, you can run queries and DDL statements. As you can see from the image below, SQL syntax is highlighted. You can execute the query also with F5 function key.

One important feature is that you can add tables and columns to the query window from the database treeview – just hold down the control key and click on the table or column name. Note that brackets [] are automatically added to the table/column name.

SQL CE Editor - query window

Tools

Tools window hasn’t changed since v1 – except that if you have a database open in the main window and will click on Tools button, that database is automatically selected in Tools window as well.

These are all normal SqlCeEngine operations, it is just simpler to use them from CE Editor. Like it says, Repair will attempt to recover corrupted rows first, then remove those it was unable to recover. Verify will report that the database is corrupt if you’ve specified a wrong password.

Database will be backed up for all operations, including Verify.

To-do

  • Better installer – probably NSIS
  • Options to customize font size (maybe?)
  • Query RichTextBox behaves weirdly sometimes – probably sqlparse.dll has issues with high ASCII/multibyte characters. See what can be done about it
  • Fix/change namespaces in source files
  • Switch positions of Tools and Query buttons, more logical that way
  • Auto-close query window when Tools is clicked

10 kommentaari »

  1. One thing, though – you probably need to be logged in as local admin during the installation, so regsvr32 can register sqlparse.dll.

    Also, I haven’t tested this on Vista, only XP.

    kommentaar kirjutas dukelupus — 2008-10-31 @ 12:30:39 | Vasta

  2. Very nics but the SQl do not exept “ä,ü,ö” for Tabelnames. Perhaps you can fix that easy?

    kommentaar kirjutas DarkDirk — 2009-01-06 @ 00:06:06 | Vasta

  3. I’ve been experimenting with your editor and think it’s great. I have noticed a few things though and am wondering how current your effort is to actually fix a few things. Also as Open Source, I would be interested in contributing some assistance as I am working more and more with SQL CE, and having a functioning editor is a wonderful benefit. I can see embedding it in an application an making it available to Admin users, etc.

    1) I noticed that when I tried to open a database about 350 megs, I got an error about max size. It would be useful to be able to set the acceptable max size in a configuration dialog, etc.

    2) I noticed when opening a table with an image column, each row loaded in the grid threw an exception. Eventually the table loaded, but not without stopping on every row.

    3) A config setting to set a ceiling on the number of rows loaded into the grid would be useful as well.

    4) I noticed the toggle from design to table data when the Table node is clicked. However, once in design it does not toggle back to table data unless you step off the node and return.

    5) Can you direct me to the source code?

    I look forward to hearing from you.

    Regards.

    Don Robins
    dbr@outformations.com
    http://www.outformations.com

    kommentaar kirjutas Don Robins — 2009-01-12 @ 06:52:14 | Vasta

  4. Hello I’ve just downloaded your tool and soon as it runs it comes up with the following error? The main interfaces shows if I click OK but any other button comes up with the same error, any ideas?

    See the end of this message for details on invoking
    just-in-time (JIT) debugging instead of this dialog box.

    ************** Exception Text **************
    System.IO.FileLoadException: Could not load file or assembly ‘System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
    File name: ‘System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ —> System.IO.FileLoadException: Could not load file or assembly ‘System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=3be235df1c8d2ad3, Retargetable=Yes’ or one of its dependencies. The located assembly’s manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
    File name: ‘System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=3be235df1c8d2ad3, Retargetable=Yes’

    WRN: Assembly binding logging is turned OFF.
    To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
    Note: There is some performance penalty associated with assembly bind failure logging.
    To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

    at SqlCeViewer2.mainForm.treeDb_AfterSelect(Object sender, TreeViewEventArgs e)
    at System.Windows.Forms.TreeView.OnAfterSelect(TreeViewEventArgs e)
    at System.Windows.Forms.TreeView.TvnSelected(NMTREEVIEW* nmtv)
    at System.Windows.Forms.TreeView.WmNotify(Message& m)
    at System.Windows.Forms.TreeView.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.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

    ************** Loaded Assemblies **************
    mscorlib
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3082 (QFE.050727-3000)
    CodeBase: file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
    —————————————-
    SqlCeEditor
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.0.0
    CodeBase: file:///C:/Program%20Files/SQL%20CE%20Editor/SqlCeEditor.exe
    —————————————-
    System.Windows.Forms
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
    —————————————-
    System
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
    —————————————-
    System.Drawing
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
    —————————————-
    System.Xml
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3082 (QFE.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
    —————————————-
    System.Configuration
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
    —————————————-
    trxms4fq
    Assembly Version: 2.0.0.0
    Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
    CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
    —————————————-

    ************** JIT Debugging **************
    To enable just-in-time (JIT) debugging, the .config file for this
    application or computer (machine.config) must have the
    jitDebugging value set in the system.windows.forms section.
    The application must also be compiled with debugging
    enabled.

    For example:

    When JIT debugging is enabled, any unhandled exception
    will be sent to the JIT debugger registered on the computer
    rather than be handled by this dialog box.

    kommentaar kirjutas Duncan — 2009-06-26 @ 18:02:38 | Vasta

  5. Hello – please try the version at Codeplex, http://sqlcedatabaseeditor.codeplex.com/ – and let me know if it worked better for you.

    kommentaar kirjutas dukelupus — 2009-06-26 @ 18:36:45 | Vasta

  6. I had same error as Duncan even with version from codeplex, and I installed SQL CE runtime from Microsoft and it works now.

    http://www.microsoft.com/downloads/details.aspx?FamilyId=DC614AEE-7E1C-4881-9C32-3A6CE53384D9&displaylang=en

    kommentaar kirjutas IgorK — 2009-07-23 @ 11:38:52 | Vasta

  7. Very nice. I especially like the sync tracking information.

    A limit on the number of rows appearing in the grids would be very useful.

    Is it possible to determine the size of tables? I’ve not found a way of estimating table sizes in SQL CE.

    kommentaar kirjutas Lane Buckner — 2009-11-16 @ 20:22:08 | Vasta

  8. I downloaded the version on Codeplex but get the following error:

    “Retrieving the COM class factory for component with CLSID {8F6C7662-E8A1-11D0-B9B3-2A92D0000000} failed due to the following error: 80040154.”

    The breakpoint is in Parser.cs on line 35:
    sqlParser = (IVBSQLParser)new vbSQLParser();

    kommentaar kirjutas johnh — 2009-11-18 @ 00:24:33 | Vasta

  9. you can get table size by adding up the datatype numbers. THe SQL CE editor should be capable of doing this will a bit of code: http://support.microsoft.com/kb/827968

    kommentaar kirjutas test — 2009-12-28 @ 23:14:06 | Vasta

  10. CLSID 8F6C7662-E8A1-11D0-B9B3-2A92D00000 implemented in SQLPARSE.dll.
    This DLL along with SQLSTR.dll available in VStudio60/Office2K/SQLServer2K,
    but not in the latest MS products.
    You have to copy both DLL into c:\Windows\System32 and run “regsvr32 sqlparse.dll”
    Without SQLSTR.dll in system32: regsvr32 will fail to register sqlparse.dll.

    kommentaar kirjutas mk142857 — 2011-01-30 @ 20:53:06 | Vasta


RSS feed for comments on this post. TrackBack URI

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s

Create a free website or blog at WordPress.com.