SQL CE 3.5 problem with TableDirect table access

I try to insert hundreds of records into empty database table using TableDirect type of SqlCeCommand. The problem is I get an exception SqlCeException "Unspecified error" when calling SqlCeResultSet::Insert. Below is my code. Any hints?

Thanks

    public bool StoreEventsDB2(List<DAO.Event> events)
    {
        try
        {

            SqlCeCommand command = new SqlCeCommand("Event");
            command.CommandType = System.Data.CommandType.TableDirect;

            SqlCeResultSet rs = _databaseManager.ExecuteResultSet(command, ResultSetOptions.Updatable | ResultSetOptions.Scrollable );

            foreach (DAO.Event theEvent in events)
            {
                SqlCeUpdatableRecord record = rs.CreateRecord();
                record.SetInt32( 0, theEvent.ID );
                record.SetInt32( 1, theEvent.ParentID);
                record.SetString(2, theEvent.Name);
                record.SetDateTime(3, theEvent.DateTime);

                record.SetDateTime(4, theEvent.LastSynced);
                record.SetInt32(5, theEvent.LastSyncedTS);

                record.SetString(6, theEvent.VenueName);
                record.SetBoolean(7, theEvent.IsParentEvent);

                record.SetDateTime(11, DateTime.Now);

                rs.Insert(record);
            }

        }
        catch (SqlCeException e)
        {
            Log.Logger.GetLogger().开发者_运维百科Log(Log.Logger.LogLevel.ERROR, "[EventManager::StoreEventsDB] error: {0}", e.Message);
            return false;
        }
        catch (Exception e)
        {
            Log.Logger.GetLogger().Log(Log.Logger.LogLevel.ERROR, "[EventManager::StoreEventsDB] error: {0}", e.Message);
            return false;
        }
        return true;
    }

I am unsure how your connection is managed with the database manager which could be the culprit - make sure you are using one connection (sqlce doesn't play nice). Also the results set option "ResultSetOption.Scrollable" is not needed (at least I have never used it for an insert).

Below is the syntax I use when doing direct table inserts. Every database/data access object is wrapped in a using statement to dispose of objects after use - this is very important especially with the compact framework and sqlce as the garbage collection is less than ideal (you WILL get out of memory exceptions!). I have added a transaction to your code also so that the option is all or nothing.

Hope this helps:

using (var transaction = connection.BeginTransaction())
{
    using (var command = connection.CreateCommand())
    {
        command.Transaction = transaction;
        command.CommandType = CommandType.TableDirect;
        command.CommandText = "Event";

        using (var rs = command.ExecuteResultSet(ResultSetOptions.Updatable))
        {
            var record = rs.CreateRecord();

            foreach (DAO.Event theEvent in events)
            {
                record.SetInt32(0, theEvent.ID);
                record.SetInt32(1, theEvent.ParentID);
                record.SetString(2, theEvent.Name);
                record.SetDateTime(3, theEvent.DateTime);
                record.SetDateTime(4, theEvent.LastSynced);
                record.SetInt32(5, theEvent.LastSyncedTS);
                record.SetString(6, theEvent.VenueName);
                record.SetBoolean(7, theEvent.IsParentEvent);
                record.SetDateTime(11, DateTime.Now);
                rs.Insert(record);
            }
        }
        transaction.Commit();
    }
}