[RESOLVED] sqlCE database, create tables

Hi guys, I'm playing around with creating an app for my pocket pc and I need a small database for it.

I've already managed to create the database itself on the device, but I need a way to add tables and columns to those tables and establish relationships. Is there a GUI way to do this? I know I can do it in code, but I'm not that sharp with SQL.

I'm using VS.2005. I've added the database as a datasource and I can create the tables and stuff that way, but it appears that it's only a dataset and not the actual database file. Is there a quick way I could just update my dataset to the database?

Sorry if these are newb questions, I just haven’t had the time to devote to getting good at database programming.

Thanks for any help

We prefer to create the DB objects through code - it's rather simple...

But to do it from VS 2005

VIEW menu - Server Explorer. You will see Data Connections - ADD connections. I left it selected to My Computer - browsed to a folder where the SQL CE db was - put in the password - tested the connection - all that worked.

For some reason my OK button is not enabled - so I'm not able to go the next step.

Does this work for you?

Do you want to see how to create tables and such in code?

Thanks szlamany, I guess I'll give it a go in code. I've found some tutorials, but not everything in them works as expected.

I've created my tables and columns using code, at least I think it did, it would be nice to see them :).

How do I assign a column as the primary key?

Is there a way to get it to auto increment as I add new records?

Thanks!

Create Table XYZ (Col1 int identity, Col2 nvarchar(1), Primary Key (Col1))

I believe that's the SQL CE syntax for auto-increment (identity) and how to create a primary key.

Did you try the SERVER EXPLORER steps I posted? I'm curious if you can get the OK button to be enabled on that dialog box.

When I try to create it on my computer it gives me an OK on test connection, but when I try to hit next, it fails and says it can't access the file.

It works fine when I create it on my device though, but I still end up with a dataset and not the actual database file in datasources explorer.

I'll give your code a try, thanks again for the help. More to come I'm sure :wave: .


BTW, is there a way to delete a datasource from the project?

I don't ever use datasources - we like to execute sql strings and return datareaders and such for working the database.

I'm taking your advice and doing everything through code. Things are going smoothly right now and the app is able to add and display records. Thanks for all the help.:D