CRUD Opertaions with SQL Server CE database on device with Windows CE 5

Question

There are a smart device, OS of this device is Windows CE 5. I want to write a c# smart device application program that running on this device. C# program must communicate with a SQL Server CE database.

cedb1.sdf is a SQL Server CE database that are creating on device when program will be run, I call below method on FormLoad():

public void InitializeDatabase()
{
    string startupPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
    string datalogicFilePath = Path.Combine(startupPath, "cedb1.sdf");

    SqlCeEngine engine = new SqlCeEngine(ConnectionString);

    if(!File.Exists(datalogicFilePath))
        engine.CreateDatabase();
    string query = "create table PersonelType(Id int primary key identity not null,Caption nvarchar(100) null)";
        ExecuteNonQuery(query);

    query = "create table Personel(Id int primary key identity not null,PersonelTypeId int not null,FirstName nvarchar(100) not null,LastName nvarchar(100) not null,CardNumber nvarchar(100) null)";
        ExecuteNonQuery(query);

    query = @"ALTER TABLE Personel
                ADD CONSTRAINT MyConstraint FOREIGN KEY (PersonelTypeId) REFERENCES
                PersonelType(Id)
                ON UPDATE CASCADE
                on delete cascade";
    ExecuteNonQuery(query);
}

Database is successfully created.

Then on FormLoad() I call RefreshGrid() method to show all PersonelTypes in data grid:

private void RefreshGrid()
{
    PersonelTypeBLL personelTypeManager = new PersonelTypeBLL();
    dgPersonelTypes.DataSource = personelTypeManager.GetAll();
}

PersonelType is a businness object class:

public class PersonelType
{
    public int Id { get; set; }
    public string Caption { get; set; }
}

RefreshGrid() method calls GetAll() method in BLL :

public List<PersonelType> GetAll()
{
    var repository = new PersonelTypeDAL();
    var data = repository.GetAll();
    List<PersonelType> personelTypes = new List<PersonelType>();

    for (int i = 0; i < data.Rows.Count; i++)
    {
        PersonelType personelType = new PersonelType();
        personelType.Id = Convert.ToInt32(data.Rows[i]["Id"]);
        personelType.Caption = data.Rows[i]["Caption"].ToString();
        personelTypes.Add(personelType);
    }
    return personelTypes;
}

and corresponding method of GetAll() in BLL is GetAll() in DAL:

public DataTable GetAll()
{
    string query = "select id, caption from personeltype";
    return ExecuteDataTable(query);
}

the ExecuteDataTable method implemented in this way:

protected DataTable ExecuteDataTable(string commandText)
{
    using (SqlCeConnection con = new SqlCeConnection(ConnectionString))
    {
        SqlCeCommand cmd = new SqlCeCommand();
        cmd.Connection = con;
        cmd.CommandText = commandText;
        DataTable dt = new DataTable();
        SqlCeDataAdapter da = new SqlCeDataAdapter(cmd);
        con.Open();
        da.Fill(dt);
        con.Close();
        return dt;
    }
}

ConnectionString property is:

protected string ConnectionString
{
    get
    {
        string startupPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().GetName().CodeBase);
        string datalogicFilePath = Path.Combine(startupPath, "cedb1.sdf");
        return string.Format("DataSource={0};password=123456", datalogicFilePath);
    }
}

An exception occurred, message of exception is:

Error, a native exception has occurred in SDPOffDbPersonel.exe

an in details of this exception write:

ExceptionCode: 0xc0000005
ExceptionAddress: 0x01ca4008
Reading: 0x00650094
Faulting Mode: sqlceme35.dll
offset: 0x00004008

at NativeMethods.GetKeyInfo(IntPtr pTx, String pwszBaseTable, IntPtr PrgDbKeyInfo, Int32 cDbKeyInfo, IntPtr pError) at SqlCeDataReader.FillMetaData(SqlCeCommand command)
at SqlCeCommand.InitializeDataReader(SqlCeDataReader reader, Int32 resultType)
at SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
at SqlCeCommand.ExecuteDbDataReader(CommandBehavior behavior)
at DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at DbDataAdapter.Fill(DataTable[] datatables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at DbDataAdapter.Fill(DataTable dataTable)
at DALBase.ExecuteDataTable(String commandText)
at PersonelTypeDAL.GetAll()
at PersonelTypeBLL.GetAll()
at Form1.RefreshGrid()
at Form1.Form1_Load(Object sender, EventArgs e)
at Form.OnLoad(EventArgs e)
at Form._SetVisibleNotify(Boolean fVis)
at Control.set_Visible(Boolean value)
at Application.Run(Form frm)
at Program.Main()

What is problem? And how can I solve it?

regards

Answer 1

Looks like it is an issue with version mismatch between the System.Data.SqlServerCe.dll file and the unmanaged dll files on the device - http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/fd60ba69-e4d6-441a-901f-947ac7a46d3c/ - solution is to make sure same version is used in dev environment and device, perferably SSCE 3.5 SP2 - http://www.microsoft.com/en-us/download/details.aspx?id=8831