Before we create a table in SSCE, you need to understand that SSCE only supports a subset of the data types provided in full SQL Server. The specific list is: bigint, integer, smallint, tinyint, bit, numeric (p, s), money, float, real, datetime, national character(n) (Synonym:nchar(n)), national character varying(n) (Synonym:nvarchar(n)), ntext, nchar, binary(n), varbinary(n), image, uniqueidentifier, ROWGUIDCOL , and IDENTITY [(s, i)].
The last two, strictly speaking are not data types but properties of the data column. SSCE was designed to be small and lightweight (hence the Compact name). To keep it small, many of the datatypes were eliminated.
A full list, with detailed explanations can be found at on the MSDN site: http://msdn2.microsoft.com/en-us/library/ms172424.aspx or http://shrinkster.com/lij.
Now that you know what’s available, let’s talk about how to create a table. In keeping with it’s theme of compactness, SSCE doesn’t include classes for manipulating the structures inside a SSCE database directly. Instead, you have to do everything through SQL DDL (Data Definition Language) statements. You know, Create Table, Drop Table, etc.
The code below shows the fairly simple steps involved. First you need to open a connection to the database. You do this by creating a SqlCeConnection object and passing in the connection string. The connection string has the same format I described in my previous post on creating the database.
Next, we create a string to hold some SQL (sql), and create a SqlCeCommand object (cmd). Finally we call the ExecuteNonQuery method of the command object to kick off the SQL. Here’s the code, with some try / catch logic thrown in to trap for errors.
SqlCeConnection cn = new SqlCeConnection(ConnectString());
if (cn.State==ConnectionState.Closed)
{
cn.Open();
}
SqlCeCommand cmd;
string sql = “create table CoolPeople (“
+ “LastName nvarchar (40) not null, “
+ “FirstName nvarchar (40), “
+ “URL nvarchar (256) )”;
cmd = new SqlCeCommand(sql, cn);
try
{
cmd.ExecuteNonQuery();
lblResults.Text = “Table Created.”;
}
catch (SqlCeException sqlexception)
{
MessageBox.Show(sqlexception.Message, “Oh Fudge.”,
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, “Fooey.”, MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
finally
{
cn.Close();
}
There’s a few things I’d like to point out. First, the connection. In this simple example, I opened and closed the connection in the same routine. For talking to large databases, especially when doing so from a web app, this methodology makes a lot of sense. You keep the connection open for a brief time and reduce the load on your server.
With SSCE however that need does not exist. The database is local with one connection, so you save nothing by the brief open / close connection, and instead slow yourself down. With SSCE I would recommend you open the connection when your application launches, and close it when it exists. Pass it to the classes that need it. You will gain a lot of speed doing so, and minimal cost in terms of memory.
The other thing to note, within the line that instantiates the new SqlCeConnection I have a method called ConnectString(). To make life easy for my demo I encapsulated the connection string in a method that returns a string. You could place your connection in a string, method, or whatever is convenient for you. I already documented the code to create a connection string in yesterday’s post on creating a database, so I won’t bore you with it again.
Would you like to see the database? Cool. Open up SQL Server Management Studio (I’m guessing you have SQL Express or SQL Server Developer Edition installed). When it opens, pick SQL Server Mobile as the database type. Under Database File, click the Browse for more… option and navigate to the sdf file you created, and click on OK. Enter in the password, and click OK again to open the database.
Now you can do many of the normal things you’d do with a SQL Server database. Click on the Tables in the tree and you should see it expand and show our “CoolPeople” table. You can drill down further to see all the fields.
OK, we’ve created our table, the next step will be to load it. But that’s for the next post!
Arcane Code 
HI
very good code juhi
please keep in touch
Simple yet effective help:)
Thanks
Nice job, good code.
hi , gr8 work .but i have one query is it possible to pass the table name through variable.
thanks
Nice work…………..GOOD
hello sir good coding thanks
Thanks a lot