Here example which I did few months back. As you can see I do check if database exist to over-write to default values that can be removed or replaced by something more sensible. Also this show creation of only one database with one table but this can expand. Use your imagination to expand this example
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.IO;
using System.Windows.Forms;
namespace CW3_MobileDB
{
class EmulatorDB
{
private string conStr = @"Data Source=.\\Program Files\\CW3_MobileDB\\emuDB.sdf";
public void createNewDB()
{
if (System.IO.File.Exists(@".\\Program Files\\CW3_MobileDB\\emuDB.sdf"))
{
SqlCeConnection con = new SqlCeConnection();
SqlCeCommand ceCmd = new SqlCeCommand();
con.ConnectionString = conStr;
con.Open();
ceCmd.Connection = con;
ceCmd.CommandText = "DROP TABLE ComicsBook";
ceCmd.ExecuteNonQuery();
con.Close();
}
else
{
createDB();
}
createTableComicsBook();
insertComicsBookData();
}
/// <summary>
/// Create emulator database
/// </summary>
private void createDB()
{
SqlCeEngine engine = new SqlCeEngine();
engine.LocalConnectionString = conStr;
try
{
engine.CreateDatabase();
}
catch (SqlCeException sqlEx)
{
MessageBox.Show(sqlEx.ToString());
}
System.Diagnostics.Debug.WriteLine("emuDB created");
}
private void createTableComicsBook()
{
SqlCeConnection con = new SqlCeConnection();
SqlCeCommand ceCmd = new SqlCeCommand();
con.ConnectionString = conStr;
con.Open();
ceCmd.Connection = con;
string sqlCmd = "CREATE TABLE ComicsBook"
+ "(bookID nvarchar(1000) not null, gender nvarchar (30) not null, title nvarchar (50) not null,"
+"volume int, issueNum int, grade nvarchar (5) not null, secondGrade nvarchar (5), pricePaid float,"
+" guideValue float, action nvarchar (3))";
ceCmd.CommandText = sqlCmd;
ceCmd.ExecuteNonQuery();
con.Close();
System.Diagnostics.Debug.WriteLine("Table ComicsBook created");
}
private void insertComicsBookData()
{
SqlCeConnection con = new SqlCeConnection();
SqlCeCommand ceCmd = new SqlCeCommand();
con.ConnectionString = conStr;
con.Open();
ceCmd.Connection = con;
ceCmd.CommandText = "INSERT INTO ComicsBook"
+ "(bookID, gender, title, volume, issueNum, grade, secondGrade, pricePaid, guideValue)"
+ " VALUES('X-MEN-1963_105','Super-hero','X-MEN-1963','1','105','NM','None','1.1','200')";
try
{
ceCmd.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.ToString());
}
ceCmd.CommandText = "INSERT INTO ComicsBook"
+ "(bookID, gender, title, volume, issueNum, grade, secondGrade, pricePaid, guideValue)"
+ " VALUES('AMAZING SPIDERMAN_3','Super-hero','AMAZING SPIDERMAN','1','3','G','VG','7.5','25')";
try
{
ceCmd.ExecuteNonQuery();
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.ToString());
}
con.Close();
System.Diagnostics.Debug.WriteLine("Value add to ComicsBook");
}
}
}
Also you can find some code examples here
