Re: SQLCE 3.0 losing data after transaction

Ginny,
We have a dataAcess component that implements a Singleton. This component
also allow to conect to multiple databases, so also implement a connection
pooling.
The database is created by de Replication object When it not exists in a
previous moment (the app. sync with a Sql server 2005).
We do not use dataset, only SqlCeResultSet to get data from the database.
This problem ocurr since I implement the connection pooling (before the
component only conect to one database) so I think keep multiple active
connection cause the problem.

Code to create de database
**********************************************************
mReplication = New SqlCeReplication
mReplication.SubscriberConnectionString = "Data Source=" & dataBasePath &
";Password=" & dataBasePassword & ";Max Database Size=128;Default Lock
Escalation=100;"

If Not File.Exists(dataBasePath ) Then
mReplication.AddSubscription(AddOption.CreateDatabase)
Else
'Se controla que la suscripción exista en la base
existPublication = Me.ExistPublication(mReplication.Publication)

If Not existPublication Then

mReplication.AddSubscription(AddOption.ExistingDatabase)
End If
End If
**********************************************************

Piece of code of component to access multiple databases
***********************************************************

Public Class clsDataAccessComponent
Implements IDisposable

Private Shared mobjAccess As clsDataAccessComponent
Private Shared mMutex As New Mutex
Private Shared mConnection As SqlCeConnection
Private Shared mConnectionPool As Hashtable = New Hashtable()

Private Sub New()
End Sub

'SINGLETON
Public Shared Function GetInstance(ByVal databaseId As String ) As
clsDataAccessComponent

mMutex.WaitOne()

If mobjAccess Is Nothing Then
'Retorna una nueva instancia de la clase
mobjAccess = New clsDataAccessComponent()
End If

mobjAccess.Connect(databaseId)

mMutex.ReleaseMutex()

Return mobjAccess

End Function

Private Sub Connect(ByVal databaseId As String)

'Se busca la conexión en el pool de conexiones, si existe se setea
como activa
If mConnectionPool.ContainsKey(databaseId) Then
mConnection = CType(mConnectionPool.Item(databaseId),
SqlCeConnection)
Else
'Si no existe, se crea, se agrega y se setea como la activa
Dim connection As New
SqlCeConnection(clsDataAccessUtils.ConnectionString(databaseId))
connection.Open()

mConnectionPool.Add(databaseId, connection)

mConnection = connection
End If

Public Sub Disconnect(ByVal databaseId As String)

'Se busca la conexión en el pool de conexiones
If mConnectionPool.ContainsKey(databaseId) Then

mConnection = CType(mConnectionPool.Item(databaseId),
SqlCeConnection)

If mConnection.State = ConnectionState.Open Then

mConnection.Close()
End If

'Se elimina la conexión del pool
mConnectionPool.Remove(databaseId)

mConnection = Nothing

End If

End Sub

Private Function GetCommand(ByVal Query As String, Optional ByVal
Transaction As SqlCeTransaction = Nothing, _
Optional ByVal Type As CommandType =
CommandType.Text) As SqlCeCommand

Dim SqlCommand As SqlCeCommand
Try
'Si es una transacción la abre
If Transaction IsNot Nothing Then
SqlCommand = CType(Transaction.Connection.CreateCommand(),
SqlCeCommand)
SqlCommand.Transaction = Transaction
Else
SqlCommand = mConnection.CreateCommand()
End If

'Asigna la consulta y el tipo de la misma, pueden ser 3 (Texto,
TableDirect o StoredProcedure)
SqlCommand.CommandText = Query
SqlCommand.CommandType = Type

'Retorna el comando
Return SqlCommand

Catch ex As Exception
Throw ex
End Try
End Function

Public Sub Execute(ByVal Query As String, Optional ByVal Parameters As
List(Of SqlCeParameter) = Nothing, _
Optional ByVal Transaction As SqlCeTransaction =
Nothing, _
Optional ByVal Type As CommandType = CommandType.Text)

Dim SqlCommand As SqlCeCommand
Try
'Se crea el comando
SqlCommand = GetCommand(Query, Transaction, Type)

GetParameters(SqlCommand.Parameters, Parameters)

'Ejecuta
SqlCommand.ExecuteNonQuery()

SqlCommand.Dispose()

Catch ex As Exception
Throw ex
End Try
End Sub
**********************************************************

"Ginny Caughey MVP" wrote:

Pablo,

Do you have a *small* code sample that illustrates this issue including code
to create the database?

How much memory is available on the device you're using? Are you using
DataSet or something less memory hungry like SqlCeResultSet?

Thanks,

--

Ginny Caughey
Device Application Development MVP



"Pablo Barros" <Pablo Barros@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5D679B61-5EC3-4881-A279-4EBF594F7E5F@xxxxxxxxxxxxxxxx
Hi, I have the same problem, after some rows are inserted in various
tables
and we read the data, the data inserted (not all some rows) dissapeared.
Do
you find a resolution for the problem?
Thks

"ErusGladius" wrote:

Hi,
We are having difficulties on our mobile application. We were using CE
2.0 and it was working perfect. Now that we upgraded to 3.0 , problems
occur every day. We are using Singleton Connection and transaction.

We are having difficulty on memory usage, after some work and
inserting many rows to the database we get a "not enough memory"
exception. And we can not go anywhere after that. Closing the
connection and opening again does not work.Only solution is to close
the application.

Our 2nd problem is , sometime we lose data. After some rows are
inserted and we succesfully read the data , there is a chance to lose
the data thats being inserted. We lose not all the rows but some rows.
And it occurs like there is a huge transaction and its roll backed.
But there is no such transaction. We commit our transaction after each
succesful insert.

Any help will be appreciated


.

Relevant Pages

  • Re: SQLCE 3.0 losing data after transaction
    ... You can also control the flush scheduling time using Connection String ... only SqlCeResultSet to get data from the database. ... CommandType.Text) As SqlCeCommand ... If Transaction IsNot Nothing Then ...
    (microsoft.public.sqlserver.ce)
  • Re: SQLCE 3.0 losing data after transaction
    ... You may be right that the connection sharing is the problem. ... You can have multiple connections against the database in SqlCe 3, ... CommandType.Text) As SqlCeCommand ... If Transaction IsNot Nothing Then ...
    (microsoft.public.sqlserver.ce)
  • Re: Problem with SQLServerCE
    ... deployed and overwriting the new and updated database file on the device. ... SqlCeTransaction transaction = null; ... connection = GetConnection; ... SqlCeCommand command = new SqlCeCommand("SELECT id from ...
    (microsoft.public.sqlserver.ce)
  • Re: Connection types and speeds
    ... The memory used by holding the connection open is not ... All recordset inserts seen to consequently have 0ms execution time, ... actually written to the database while the code has regained control ... RecordsetClone of a form) when a transaction was rolled back, ...
    (microsoft.public.access.queries)
  • Re: psycopg, transactions and multiple cursors
    ... > first time a change is made to the database. ... My intention is actually to keep them in the same transaction. ... >> cursor fetches one record from the database at a time). ... > generated from the same connection, ...
    (comp.lang.python)
Quantcast