none
LINQ + Synchronization services RRS feed

  • Question

  • I'm trying to build an application that can be used both when connected to the server and when disconnected as well.

     

    When the application is connected to the server, it should query the actual SQL 2005 database. When it isn't, it should query it's local cache.

     

    So far I've managed to create the entire connected part. To query the SQL 2005 database it uses a LINQ datacontext.

    The synchronisation seems to be working as well. It creates the SQL compact database as it should.

     

    The problem lies in switching over from the SQL 2005 database to the SQL Compact 3.5 database. At first the LINQ datacontext gave an error about not being able to find the provider when I passed it the connectionstring to the Compact 3.5 database. To test whether I could reach the SQL Compact 3.5 database, I added a SQLCeConnection first, queried that without LINQ, and the result was as I expected. An added side-effect that after I did that, suddenly the LINQ datacontext seemed willing to connect (if anyone knows why, I'd be happy to hear), it however didn't manage to query the Compact database. It keeps giving errors about tables not being found. I checked the .dbml file and I decided to remove both the connectionstring part (as I programmatically supply that anyway) and removed all the "dbo." prefixes from the tables. This didn't work however.

     

    Some of the questions that face me now are:

    - Is it even possible to query both an SQL 2005 and a SQL Compact 3.5 database with the same Datacontext class? If not, how could I best solve adding another Datacontext to the project considering all the table names in the Compact database are the exact same as in the 2005 database. I tried adding the SQL-metal generated file into project but it gave me a lot of "duplicate name" errors.

    - Any other suggestions on how I could get a local cache of the database? I was looking at manually copying the mdf/ldf files from the server to a local SQLExpress version as well; but ran into some trouble with taking the database offline. At first I still had connections open. After I made sure all connections got terminated before taking it offline by putting it in single user mode, it did get taken offline. It got copied. It even went back online and into multi-user mode. But somehow as soon as I tried to get my Datacontext to connect to it again, it was suddenly offline and in single mode again (I used the SQL Management Studio to see the status of the DB).

    Thursday, June 12, 2008 1:15 PM

Answers

  • It should be possible to connect the DataContext to a SQL CE database but I'm not sure why it would claim the tables are not found unless they have different names.

    Can you turn on the log on the datacontext (set the .Log property to a stream writer) to see what SQL it is outputting and try pasting that into Management Studio connected to the CE database and see what comes back.

    [)amien

    Thursday, June 12, 2008 7:48 PM
    Moderator

All replies

  • It should be possible to connect the DataContext to a SQL CE database but I'm not sure why it would claim the tables are not found unless they have different names.

    Can you turn on the log on the datacontext (set the .Log property to a stream writer) to see what SQL it is outputting and try pasting that into Management Studio connected to the CE database and see what comes back.

    [)amien

    Thursday, June 12, 2008 7:48 PM
    Moderator
  •  

    Thank you for pointing out that using the same datacontext isn't an issue.

     

    After I enabled the logging I also saw that it was still trying to reach the table within the [dbo] schema. Apparently editting the dbml file with notepad doesn't refresh it in VS. You need to actually open it in VS again and then the changes will actually work.

     

    After that I ran into some issues where some queries ran fine, but some others (I found out later those were all queries that had string comparissons in them) didn't. Since the varchar type isn't known within SQL Compact, when synchronizing the sync service converted all the regular varchars into nvarchars. After doing the same in the SQL 2005 database and adapting the datacontext to this change, all seems to be working as it should.

    Tuesday, June 17, 2008 7:27 AM