Desperate for help with SQL CE

[Geoff Callaghan]

I can't seem to find any of the tools that the documentation says I need to
get SQL CE working. I'm very familiar with SQL server, but I can't even seem
to get started with SQL CE. All I want to do is create a small, simple
database and put it on my device. It doesn't have to talk to any other
database, or sync up, or anything like that. Just add, edit and delete
records. However, I can't even find the tools to create the database in the
first place. The documentation said where to find Query Analyzer...and it
isn't there.

Geoff Callaghan

 

[Peter Foot [MVP]]

Query analyser is included in the sqlce.dev.devicetype.cab file which you'll
find here:-
C:\Program Files\Microsoft Visual Studio .NET
2003\CompactFrameworkSDK\v1.0.5000\Windows CE\wce300\
And then in the subdirectory for your device processor type e.g. arm for
Pocket PC 2000 / 2002 ARM devices
Armv4 and WCE420x86 are special cases for 2003 based device / emulator
respectively.

However if you deploy a project which has a reference to
System.Data.SqlServerCe then it should automatically deploy and install the
appropriate package for you when you debug/deploy to a device/emulator.

Peter

 

[Geoff Callaghan]

OK, that's great for getting it on the device, but how do I create my
database in the first place? I can't do that on my device, it would take
weeks.

 

[Ilya Tumanov [MS]]

SQL CE database has to be created on the device. You can't find tools on
your PC because there aren't any.

That would change in Whidbey; meanwhile create the database with Query
Analyzer running on device or programmatically.



Best regards,



Ilya



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Peter Foot [MVP]]

Theres no way to directly modify a SqlCe database from the desktop, you
could use a tool such as:-
http://www.gui-innovations.com/html/remotesqlce.html

Peter

 

[Geoff Callaghan]

So what you're telling me is that no my WinCE device...A device with no
keyboard...I'm supposed to lay out and populate a relational database?

 

[Chris Tacke, eMVP]

You can do it via code, replication, RDA or a 3rd-party tool like those
suggested. Currently there is no tool to create a device database on the
desktop.

--
<ctacke/>
www.OpenNETCF.org
Your CF searches start and end here

 

[Geoff Callaghan]

Well, that brings up another question. If I build the SQL database on my
device, how do I get it to my other devices? Is my best bet to create a
program that builds the database for me? Is there at least a scripting
language?

 

[papppp]

I would do it in two ways:

#1
a. Create an empty SQLCE database on the device using the PPC Query Analyzer
or programmatically, using the System.Data.SqlServerCe.SqlCeEngine.
b. Write a SQL script file on your desktop. Save it as .sql and copy it to
the device. In Pocket Query Analyzer go to the "SQL" tab, open the .sql file
and execute it.
note: Before writing your script you must learn the major differences
between SQL and SQLCE language (see below...).

#2
a. Create your model database on the desktop, in SQL Server.
b. Use the System.Data.SqlServerCe.SqlCeRemoteDataAccess component to "Pull"
the tables from the desktop database to the SQLCE database. Make sure that
you set the TrackOption parameter of the Pull method to TrackingOn or
TrackingOnWithIndexes so that your tables are pulled together with their
indexes.

And some important information about differences between SQL - SQLCE :
1. SQLCE doesn't accept brackets (ex. [Table1].[Field1]). Use double-quote "
instead (ex. "Table1"."Field1"). Don't forget that SQL Server on your
desktop will not accept double-quotes unless you set the SET
QUOTED_IDENTIFIER ON option.
2. Pocket Query Analyzer doesn't accept the keyword GO. If you want to write
multiple SQL statements in your script, you should end each statement with a
semicolon ( ; ), although I'm not sure about this.
3. When you "Pull" tables using the SqlCeRemoteDataAccess, you must first
drop the destination tables from the SQLCE database, otherwise you get an
exception.
4. During the Pull, the destination table will have exactly the same fields
(name, type, size), the same indexes, the same primary keys but you can't
pull defaults or relationships. And you should also take care of any
unsupported field data types (ex. in SQLCE, instead of decimal you use
numeric).

I hope this helps....

 

[Jeff]

Seems a pain I know, but what we've done is to programmatically build
the database via the application, if it doesn't exist. Code similar
to...

conn = New System.Data.SqlServerCe.SqlCeConnection(LocalSystem.ConnectionString)
conn.Open()

SQL = "CREATE TABLE users (username nvarchar(50) Primary
Key NOT NULL, password nvarchar(50) NOT NULL, timestamp datetime NOT
NULL)"

Dim cmd As New System.Data.SqlServerCe.SqlCeCommand(SQL,
conn)

cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()

conn.Close()


As for copying the db to other handhelds, a simple way is to
activesync to a desktop, copy the db to the desktop, then sync all the
other handhelds and copy over one at a time.

Also, word of warning, as this happened to us. As you add and delete
new rows, SQLCE doesn't manage it as good as SQL Server. You have to
programmatically call a compact function to compact the database.
This will probably only affect you if your database grows over time
and you run out of memory.

 

[Geoff Callaghan]

So doing that will create a file with a .db extension, which I can copy to
my desktop? Thanks, that's probably what I'll do.

 

[Darren Shaffer]

You'll get a file on device with a ".sdf" extension. If you copy it
to your desktop, then include it in your smart device app project
as "content", you can then deploy it to other devices either
through VS.Net deployment or by including it in a CAB file.

-Darren