create tables and insert data in sql server mobile on dekstop

Hello (sorry my bad english, im brazilian)

I was using Visual Studio 2003 and SQL Server CE 2.0 for C# mobile applications. The .sdf database were created in the emulator or in the mobile device itself using Query Analizer.

The application developed need some initial data to run, and this data is obtained executing one service that reads a postgree database, and insert the data in the SQL CE database of the mobile device. But, given the size of the database (maybe 10.000 rows), it tooks too much time (sometimes 6 hours).

Now we are migrating to Visual Studio 2005 and SQL Server 2005 Mobile Edition.

I want to know if its possible to create the .sdf database and load the data into this database on the desktop. Maybe through the execution of a .sql script, or through a service executed on the desktop.

After this, its just upload de .sdf file to the mobile device.

Thanks

Robson

Yes, you can create and populate your SQL Mobile database on the desktop as long as that desktop or server meets one of these criteria:

1. it contains a licensed copy of Visual Studio 2005

2. it contains a licesed copy of SQL Server 2005

3. it runs Windows XP Tablet PC edition

The code to do so is covered in the SQL Mobile Books Online.

There are other approaches as well, including third party tools like those at www.primeworks.pt, using SQL Server 2005 Integration Services, or creating and populating the database within SQL Server 2005 management studio.

-Darren

|||

Daren,

thanks for the help... I have found the way to create a sql server mobile 2005 database and insert data on the desktop using c# (running on desktop off course) at these forum topics:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=213973&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

I′ve used my licensed copy of Visual Studio 2005 to do it. Now I′ll test the solution to make a benchmark... I hope that now i will be able to create the database for my application faster....

thanks again...

|||

Please let us know how it works out for you.

Darren

|||

I have created the following table ('cidade' means city in portuguese):

CREATE TABLE cidade ( idcidade numeric(18,0) NOT NULL, codigo integer NOT NULL, descricao nvarchar(80) NOT NULL, ddd nvarchar(3), naturalidade nvarchar(80), idunidadefederativa numeric(18,0) NOT NULL )

The program (written in c# with visual studio 2005 and sql server 2005 mobile edition) insert 5565 rows in this table. It reads a sql insert line from a text file and execute the sql, eg:

INSERT INTO cidade VALUES (1, 1, 'ALTA FLORESTA D OESTE', NULL, NULL, 21)

It tooks 6 seconds to do it (running on a HP notebook with celeron processor).So, 927.5 rows per second.

Before, when we insert data on a database located at a pocket pc, this operation took 20 - 30 minutes (using c# compact framework from visual studio 2003 and sql ce 2.0).

|||

thanks for sharing your benchmark results - that's very good news.

-Darren

sql

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?

Create sdf relationships in VS2005

Hello,

I've created a database in VS2005 for my mobile app, but I can't figure out how to establish the table relationships. I'm not new to databases, but I'm new to VS. Any help is appreciated.

Thanks,

You must create the relationships (foreig key constraints) in SQL (using the query window in SSMS), or in code, like this:

ALTER TABLE MyOrders ADD FK_CustOrder FOREIGN KEY (CustID) REFERENCES MyCustomers(CustID)Hope this assists.|||

Is the table designer available in the compact edition? If so, where is it? Right clicking on a table in the object explorer doesn't provide the same functionality as the express edition.

Gus

|||No the table designer is not available for compact edition. Although MS has tried to maintain the same look and feel between different versions of SQL Server there are significant differences (limitations) with SQL Server CE that have meant that not all the functionality is available|||

Thanks for the information Nick. I understand the need to differentiate the product's editions, but these differences should be at the product's high level functional capabilities. Something like this isn't conducive to rapid application development. Even Access provides this capability.

|||Currently I am using VS2005 Professional, but the trial will be running out soon. I don't have the $$ to buy it, but can afford to get Standard edition. Will Standard allow me to create sdf's the way Pro does?