C# sqlserverce create database / table

How to create a SQL Server CE 3.5 database utilisting a .NET CF 3.5 extension method:
1. Add a semi colon delimited resource file to your .NET CF mobile project.

view plaincopy to clipboardprint?

1. CREATE TABLE OrderHeader(  
2. [ID] uniqueidentifier not null,  
3. [CustomerID] uniqueidentifier not null,  
4. [ReqestedDlvryDate] datetime not null,  
5. [Comments] nvarchar(1000),  
6. [CreateDate] datetime not null,  
7. [CreatedByID] uniqueidentifier not null,  
8. [LastEditDate] datetime null,  
9. [LastEditBy] uniqueidentifier not null,  
10. CONSTRAINT [PK_OrderHeader] PRIMARY KEY  ( [ID] )  
11. );  
12.   
13. CREATE TABLE OrderDetail(  
14. [ID] uniqueidentifier not null,  
15. [OrderID] uniqueidentifier not null,  
16. [ProductID] uniqueidentifier not null,  
17. [Qty] int not null,  
18. CONSTRAINT [PK_OrderDetail] PRIMARY KEY  ( [ID] ),  
19. CONSTRAINT [FK_OrderHeader] FOREIGN KEY([OrderID]) REFERENCES [OrderHeader]([ID])  
20. );  

2. Add an extension method on System.Data.SqlServerCE.SqlCeEngine to create the
database based on the semi colon delimited string passed in from the resorce file:

view plaincopy to clipboardprint?

1. using System;  
2. using System.Data;  
3. using System.Data.Common;  
4. using System.Data.SqlServerCe;  
5. using System.Collections.Generic;  
6. using System.IO;  
7. using System.Linq;  
8. using System.Text;  
9.   
10. namespace SQLCEDatabaseDemo.ExtensionMethods  
11. {  
12.     public static class Extensions   
13.     {  
14.         public static bool CreateDatabase(this SqlCeEngine sqlceDB, string commands)   
15.         {   
16.             bool success = false;   
17.             SqlCeConnection sqlCon = null;   
18.             try   
19.             {   
20.                 sqlCon = new SqlCeConnection(sqlceDB.LocalConnectionString);   
21.                 if (!System.IO.File.Exists(sqlCon.DataSource))   
22.                 {   
23.                     sqlceDB.CreateDatabase();   
24.                     sqlCon.Open();   
25.                     string[] sqlStatements = commands.Split(';');   
26.                     SqlCeCommand cmd = sqlCon.CreateCommand();   
27.                       
28.                     foreach (string cmdText in sqlStatements)   
29.                     {   
30.                         if (!String.IsNullOrEmpty(cmdText.Trim()))   
31.                         {   
32.                             cmd.CommandText = cmdText; cmd.ExecuteNonQuery();   
33.                         }   
34.                     } success = true;   
35.                 }   
36.             }   
37.             finally   
38.             {   
39.                 if (sqlCon != null)   
40.                 {   
41.                     sqlCon.Close(); sqlCon.Dispose();   
42.                 }   
43.             } return success;   
44.         }   
45.           
46.         public static void DeleteDatabase(this SqlCeEngine sqlceDB, string path)   
47.         {   
48.             System.IO.File.Delete(path);   
49.         }   
50.     }  
51. }  

3. Execute using the following code

view plaincopy to clipboardprint?

1. string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().ManifestModule.FullyQualifiedName);  
2. string con = String.Format("data source='{0}\\testdb.sdf'; mode=Exclusive;", path);  
3. using(System.Data.SqlServerCe.SqlCeEngine eng = new System.Data.SqlServerCe.SqlCeEngine(con))  
4. {  
5.     if (eng.CreateDatabase(Properties.Resources.CreateSchema))  
6.     {      
7.         MessageBox.Show("DB created successfully");  
8.     }  
9. }  

http://www.nickharris.net/2010/03/how-to-create-a-sqlceengine-createdatabase-extension-method-using-the-net-compact-framework-3-5-and-sql-compact-edition-3-5/