none
Custom data file extension with linq DataContext

    Question

  • We use sqlce 3.5SP2 with our application, and currently the data file has the .sdf extension - this all works really well. We use linq for almost all data access to the file.

    To better manage our application in the Windows environment, we would like to give the data file a custom extension, for example .ftd.

    This works fine when we use raw sql calls, for example

    Using conn As New SqlCeConnection("Data Source=" & _file & ";Password=pwd;Persist Security Info=False")

    but fails if we use linq, for example

            Dim _db = New DBContext2(_file)
            Dim dataList As List(Of Item)
    
            Dim qry = From c As Item In _db.Items
                        Where (c.IsActive = True)
                        Order By c.Identifier
                        Select c
            dataList = qry.ToList

    DBContext2 inherits DataContext declaring Table definitions of our business objects.

    If _file = MyData.sdf this works (so we know the connection string is correct); if _file = MyData.ftd we get the following exception:

    A network-related or instance-specific error occurred while establishing a connection
    to SQL Server. The server was not found or was not accessible. Verify that the
    instance name is correct and that SQL Server is configured to allow remote
    connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
    Server/Instance Specified)

    Can anyone confirm this is a known issue with linq? Is there any way we can use a custom file extension for the sqlce data file, and still use linq?

    Please note that I am aware of the post at http://social.msdn.microsoft.com/Forums/sqlserver/en-US/40563b2b-308a-429a-a81d-a5b737ab6b29/custom-file-extension - this does not seem to encompass the use of linq.

    I was unable to find the Using Compact as a Custom Doc Format (Recipe Manager) sample, which could possibly answer my question.

    Thanks,
    Tim



    • Edited by ausadmin Monday, September 30, 2013 5:24 AM
    Monday, September 30, 2013 4:48 AM

Answers

  • Construct/initialize the DataContext class using the SqlCeConnection object instead of just the file name, and you should be good to go.

    So use: 

    Dim _db = New DBContext2(conn)


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com


    Monday, September 30, 2013 6:38 AM
    Moderator

All replies

  • Construct/initialize the DataContext class using the SqlCeConnection object instead of just the file name, and you should be good to go.

    So use: 

    Dim _db = New DBContext2(conn)


    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com


    Monday, September 30, 2013 6:38 AM
    Moderator
  • Erik,

    Thanks for your quick reply, you are correct, using the SqlCeConnection object does allow the use of custom extensions. It has taken me a little time to confirm this as I knew we had tested this approach already and found that it still failed.

    In fact, I think we are seeing a separate bug on

    DataContext.DatabaseExists

    This works correctly for the .sdf extension but always returns false for our .ftd extension. Previously we were testing just with DatabaseExists and so concluded that even using the SqlCeConnection object to create the DataContext did not work.

    I suspect this may be related to another known issue as described at http://connect.microsoft.com/VisualStudio/feedback/details/337452/system-data-linq-datacontext-databaseexists-fails-with-sql-compact-and-datadirectory-macro

    In our application it is important to be able to confirm the sqlce data file exists, so I am considering implementing a new method as a workaround in our DBContext2 (inherits DataContext ), to firstly check if the file exists, then confirm it is a sqlce database by doing a simple query like

    "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME > '__%'"

    using SqlCeDataReader. Do you think this makes sense, or can you suggest a better workaround?

    Thanks,
    Tim

    Tuesday, October 01, 2013 9:29 AM
  • Maybe you can use the code here: http://erikej.blogspot.dk/2010/08/how-to-upgrade-version-3x-database-file.html

    Please mark as answer, if this was it. Visit my SQL Server Compact blog http://erikej.blogspot.com

    Tuesday, October 01, 2013 12:03 PM
    Moderator
  • Thanks, that code will be helpful.
    Tim

    Wednesday, October 02, 2013 11:17 PM