Click here to Skip to main content
Click here to Skip to main content
Search within:



 
Comments & Discussions (139)

SQL Server CE Query Tool

By , 9 Sep 2011
 

CEQuery-Installer- Click to enlarge image

Introduction

CEQuery was written in C# with the help of the Visual Studio 2008 platform. The new version has been upgraded to VS2010. SQL CE version 4 has been supported in this release. Some of the features of this tool are described below:

  • User is able to create a CE database. If the schema is provided, the tool is able to create the tables and columns.
  • User is able to overview an existing CE database [open with CEQuery by double click on sdf files] with its table and column structure in tree format.
  • User can design a query by dragging and dropping tables and columns from the database tree. The query pane in the tool is able to hold multiple queries provided that only one of those queries is active [as SQL Server CE engine does not support multiple queries in a single statement]. The tool is able to handle multiple query panes in the form of tabs. The query designer of the tool is able to highlight the keywords. The tool is able to handle the execution of the user's selected query.
  • User can manipulate [Insert/Update/Delete] records in a selected table.
  • User is able to generate scripts from a single table or multiple selected tables. [Both for SQL CE andr SQL Server.]
  • User is able to open multiple SQL Server CE files simultaneously.
  • User is able to convert a SQL Server database to an SDF file with schema and data.
  • User is able to change password of a SQL CE database. The tool is now capable of handling a database with no password.
  • User is able to Design CE Tables with Visual Interface.
  • User is able to Design Query with Visual Interface.
  • User is able to execute DB related operations like Validate, Repair, Compact, etc.
  • User is able to Export Query Result.

Background

In one of my projects, I have to use SQL Server CE database rigorously as the application interacts with the local version of the SQL Server CE file for displaying and manipulating data. SQL Server CE 3.5 version has been used in the application. The application is built on the Visual Studio 2005 platform with the support of .NET Framework 2.0. I was initially faced with some problem for querying the SQL Server CE files. Following are the reasons for creating a separate tool for querying a CE database:

  • SQL Server 2005 Management Studio does not support querying SQL Server CE 3.5 or SP1 database for displaying contents and querying data.
  • There are no free tools for querying a CE database available on the internet.
  • The only option to view the content of a CE database file is to install VS 2008 which has built in support for viewing and querying a CE database. But the UI provided for querying a CE database in Visual Studio 2008 is not a user friendly one. We need to type hell lot of things for querying a table. Users who are using SQL Server Management Studio will be very uncomfortable with Visual Studio 2008 for querying a CE database.

So to manage the queries for a CE database, the concept of writing a new tool comes to mind. As SQL Server CE architecture is completely written in C#, there should not be any problem to write a small new tool in a short time period. The main aspects of the tool are as follows:

  • The tool should be able to view the table and column structure in a CE database.
  • User should be able to design a query in less time like dragging and dropping table names and column names in the query pane from the database tree like in SQL Server Management Studio.
  • The tool should be able to create a new blank CE database. If a schema file is provided, the tool should be able to create tables and columns in the database.
  • The user should manipulate data [like Insert/Update/Delete] in any table from the tool.
  • The user should generate a script for an individual table as well as the whole database.
  • The user should be able to place multiple queries in a single query pane. Also she/he should be able to use multiple query panes.
  • The query editor should highlight the keywords in the query so that the user should feel like using SQL Server Management Studio.

Using the Code

A brief description of how to use the article or code has been provided in the help [CEQuery-Help.zip] file attached with this article.

Points of Interest

The following articles were very helpful in terms of learning for me:

This version has also been published in CodePlex. For the codebase of this application please refer to Codeplex Source Safe. Please note the following:

  • For "CEQuery v6 for SQL Server CE 3.5", user does not need to install SQL Server CE 3.5 SP2 separately. Setup.exe will install SQL Server CE 3.5 SP2 if it is not installed. This is targeted at all CPUs.
  • For "SQL Server CE 4.0 ", user needs to install SQL CE 4.0 separately based on the x86 or x64 version of SQL Server CE 4.0. This is targeted at all CPUs.

Improvement in the Latest Version

The latest version of CEQuery has some enhancements as well as bug fixes. The suggestions from users have been the keen interest for posting the new release. Also a new version of codebase and installer which point to SQL Server CE 4 has been attached here with the same functionality. For the CE4 installer, the user does not have to install CE4 separately.

Enhancements

  • New: Enhanced SQL Editor with Undo and Redo
  • New: Export SQL Server database with Windows Authentication support
  • New: Visual Table Editor
  • New: Visual Key Editor
  • New: Visual Query Designer
  • New: Validate and Repair DB
  • New: Compact and Shrink DB
  • New: Automatically upgrades old version database to CE4.
  • New: Double click to open SDF file in CEQuery. This feature has been provided by "Bjørn"

Bug Fixes

  • Bug 1: Bug fix for Byte Array pointed out by "Digitalbeach"
  • Bug 2: Bug Fix for IDENTITY attribute pointed out by "Tony Vaughan"
  • Bug 3: Bug fix for NVARCHAR length out by "John C"

History

  • 08th September, 2011: Updated code base and installer
  • 21st March, 2011: Updated code base and installer
  • 28th February, 2011: Updated code base and installer
  • 30th April, 2009: Updated code base and installer
  • 27th April, 2009: Third release
  • 18th March, 2009: Second release
  • 16th February, 2009: First release

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

 ray_mayukh
Software Developer
India India
Member
No Biography provided

Article Top
 
Sign Up to vote   Poor Excellent
Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

 
You must Sign In to use this message board.
Search this forum  
    Spacing  Noise  Layout  Per page   
FirstPrevNext
GeneralRe: Error: Unable to load native components of SQL Server Compactmember_awatts13 Sep '11 - 1:27 
I get the same error.
 
My application uses SQL CE v4, I therefore have SQL CE v4 installed. I downloaded your v4 edition and tried to open my v4 SDF file and got the same error and error call stack.
Andy
Sign In·View Thread·Permalink
GeneralRe: Error: Unable to load native components of SQL Server Compactmemberray_mayukh13 Sep '11 - 6:00 
Sorry to hear it again. But I have tested both the version in 32 bit machine and both works fine. Does your machine uses 64bit environment? If so, can you please check if the program can run after giving "full access to everyone" for the SQL CE Installation folder?
 
C:\Program Files\Microsoft SQL Server Compact Edition\v4.0
C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0
 
~mayukh
Sign In·View Thread·Permalink
GeneralRe: Error: Unable to load native components of SQL Server Compactmember_awatts13 Sep '11 - 6:20 
According the KB article the exception asks to reference
 
>>
On 64-bit computers, a SQL Server Compact (“Compact”) based application may fail with one of the following two symptoms:
 
Symptom 1
The application is trying to load a version of Compact that is incompatible with the version that is installed on the computer. Incorrect versions could lead to application instability and the loading of Compact would be aborted.
 
Symptom 2
The application cannot load the native components of SQL Server Compact that corresponds to the ADO.NET provider
<<
 
I can confirm I've a 64bit environment, however, I've SQL CE v4 and not v3.5. I've also ran the app as Administrator which results in the same error. The applications I've developed on my machine use SQL CE v4 and database files created in v4.
 
Perhaps I'll download the source and see where it's failing.
Andy
Sign In·View Thread·Permalink
GeneralRe: Error: Unable to load native components of SQL Server Compactmemberbitspam14 Sep '11 - 14:15 
mayukh,
I too was having the same problem as others. I believe the problem lies with the way you have referenced the SQLCE .dll's when trying to configure a private deployment. I solved the issue on my system by:
 
-creating an app.config file (named CEQuery.exe.config) with the contents:
 
<?xml version="1.0"?>
<configuration>
  <startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <probing privatePath="SqlCe4"/>
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" />
        <bindingRedirect oldVersion="4.0.0.0" newVersion="4.0.0.1" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>
 
-create a sub-folder in your install folder named "SqlCe4" (matching the privatepath above) which contains System.Data.SqlServerCe.dll and the amd64 and x86 folders with sqlce*.dll files and Microsoft.VC90.CRT folder with required c++ file and manifest.
 
-I removed your copy of the System.Data.SqlServerCe.dll file in the root of your install folder.
 
Have a look at this for more info:
 
SQL Server Compact Private Deployment tweaks
Sign In·View Thread·Permalink
GeneralRe: Error: Unable to load native components of SQL Server CompactmemberjcBrasil15 Sep '11 - 2:07 
I followed your tip but it didn't work for me.
I've gotten this error:
"The volume for a file has been externally altered such that the opened file is no longer valid."
If I remove the file "CEQuery.exe.config" the error becomes "could not load the file or assembly..."
Sign In·View Thread·Permalink
GeneralRe: Error: Unable to load native components of SQL Server Compactmemberray_mayukh15 Sep '11 - 21:24 
Can you please take the codebase of the application and run in your machine to see where the error is coming on?
Sign In·View Thread·Permalink
GeneralRe: Error: Unable to load native components of SQL Server CompactmemberDiluk16 Jan '12 - 18:48 
There's an issue with loading libraries on 64bit. Open the codebase and set the target CPU to x86 to resolve the issue.
Sign In·View Thread·Permalink
GeneralRe: Error: Unable to load native components of SQL Server Compactmemberray_mayukh15 Sep '11 - 21:20 
Thank you very much for the support. I have tested all my code for 32 bit machine and I have face no problem. I don't have any 64but environment. Thus, I could not able to reproduce the error. I am searching one such environment to test my code.
 
~Mayukh
Sign In·View Thread·Permalink
GeneralRe: Error: Unable to load native components of SQL Server Compactmember_awatts5 Oct '11 - 6:31 
The solution to the problem is per bitspam's recommendation. I did what he first suggested which failed, however I then noticed his old/new version numbers and that you're deploying v4.0.0.1
 
In bitspam's suggested config file, I swapped the version numbers around therefore making old version "4.0.0.1" and new version "4.0.0.0" and copying my v4.0.0.0 System.Data.SqlServerCe file into the sub-folder.
 
Seems to work fine now.
Andy
Sign In·View Thread·Permalink
GeneralRe: Error: Unable to load native components of SQL Server CompactmemberMember 9622 Mar '12 - 10:48 
I can confirm exact same error in a 32 bit environment with the setup here that I just downloaded a few minutes ago. It's windows 7 32 bit. I'll try the config file thing someone reported in this thread.
There is no failure only feedback
Sign In·View Thread·Permalink
Last Visit: 31 Dec '99 - 18:00     Last Update: 23 May '13 - 13:37Refresh« Prev1234567891011 Next »

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

About Article
User friendly query designer and data manipulation tool for SQL Server CE
Type Article
Licence CPOL
First Posted 16 Feb 2009
Views 190,276
Downloads 12,633
Bookmarked 148 times

, +
Top News

The Next Version of Android - Some of What's Coming

Get the Insider News free each morning.
Related Videos
Entity Framework: Querying with Entity SQL Part 1
Entity Framework: Querying with Entity SQL Part 2
Introduction to Pocket PC Development
Some internal SQL Sever Queries
Pocket Personal Health Record
Windows Mobile App Development Part 5: Intro to using SQL Server CE
Accessing MS SQL Server CE v1.0/v2.0 without ADO
LINQ and Deploying SQL Server CE 3.5
Bringing DCOM remoting functionality to Windows CE and .NET CF2.0
Top 10 steps to optimize data access in SQL Server: Part IV (Diagnose database performance problems)
SQL Server 2005 Distilled - Chapter 3: Enterprise Data Management
HowTo: Install the Northwind and Pubs Sample Databases in SQL Server 2008 Express
Install and Configure SQL Server for SQLCE replication
SQL Server DO's and DONT's
Interactive SQL Tool (using ADO)
Data Driven Unit Testing for a DotNetNuke Private Assembly Module
10 Steps to Creating a Windows Mobile Database Application Using .NET and SQL Anywhere 10
SQL Server Management Studio: Basic Startup Options
QueryCommander SQL Editor
Extracting a SQL CE DB from Isolated Storage in WP7 Mango
Stop writing connection management code every time you access the database
Creating a Mobile AJAX Process Using SQL Anywhere Web Services
Permalink | Advertise | Privacy | Mobile
Web03 | 2.6.130523.1 | Last Updated 9 Sep 2011
Article Copyright 2009 by ray_mayukh
Everything else Copyright © CodeProject, 1999-2013
Terms of Use
Layout: fixed | fluid