Manually connect to a Sql Compact 4.0 database

9 posts, 0 answers
  1. Thomas
    Thomas avatar
    4 posts
    Member since:
    Jun 2011

    Posted 03 Jan 2012 Link to this post

    I am trying to connect to a deployed compact 4 database using a generated connectionstring.

    First... when I use the following connectionstring to connect to an MSSql server "data source=EV-SITEDESIGNER;initial catalog=SiteDesigner;user id=masterdba;password=*******" everything works fine.

    The connectionstring for the compact is "Data Source=C:\source\Database\SiteDesigner4.sdf;Password=********"

    Here is part of the code:

    var db = new SiteDesignerDB(connectionString);
    db.Add(new DALContactType { ContactTypeId = 1, ContactTypeName = "", CssName = "", RankId = 5 });


    The exception occurs in the Add(), not when creating the db object.

    The following exception is raised:
    *Exception: Unable to reach database server on host ''.
    Error Details: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)

    *Innerexception: Telerik.OpenAccess.RT.sql.SQLException: 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)
    at Telerik.OpenAccess.RT.Adonet2Generic.Impl.DBDriver.connect(ConnectionString connectionString, IDictionary driverProps)<br>
    at OpenAccessRuntime.Relational.sql.SqlDriver.InitializeFor(String url, Boolean noConnect, PropertySet props, Driver& driver, Connection& conn)


    Any clues why this works fine in with one of the databases, but not the other?
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 04 Jan 2012 Link to this post

    The call stack indicates, that you are still trying to use MSSQL, but you want to connect to a SqlCE4 database.
    For this to work, you will also need to change the provider name in the connection string. What you really should do is to either
    (a) make sure you have 2 connection strings defined with different names in the web/app.config that use the different provider names System.Data.SqlClient and System.Data.SqlServerCe.4.0; later you will need to pass the connection string name to the SiteDesignedDB constructor.
    (b) make sure that the SiteDesignerDB context instance is using a backend configuration "sqlce"; this can be achieved when constructing the BackendConfiguration instance that is used for this context.

    The preferred solution is (a), because then the connection string is not hard wire in the code, on the name(s) is/are.

    Regards,
    Thomas
    the Telerik team

    Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  3. DevCraft banner
  4. Thomas
    Thomas avatar
    4 posts
    Member since:
    Jun 2011

    Posted 04 Jan 2012 Link to this post

    I tried adding "Provider=using System.Data.SqlServerCe;" to the connectionstring, but that gives me a different message...

    ProviderName conflicts with backend setting 'mssql'.
    Parameter name: providerName
    Actual value was using System.Data.SqlServerCe.

    So it seems I cannot override the ProviderName...

    any ideas?

    -Thomas
  5. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 04 Jan 2012 Link to this post

    Hi Thomas,

    You should change the Domain Model’s backend to Microsoft SQL Server Compact. In order to achieve that goal you should do the following steps:
    - Open the Model Settings Dialog;
    - Go to the Backend and Model tab;
    - Select Microsoft SQL Server Compact from the Backend dropdown list;
    - Click on the OK button and save the model;
    - Go to the Error List and fix the warning which reports that the backend setting is not synchronized by double clicking on it and selecting the correct backend option;
    - Save the model;
    Hope that helps.

    Greetings,
    Damyan Bogoev
    the Telerik team

    Q3’11 of Telerik OpenAccess ORM is available for download. Register for the What's New in Data Tools webinar to see what's new and get a chance to WIN A FREE LICENSE!

  6. hkdave95
    hkdave95 avatar
    245 posts
    Member since:
    Nov 2006

    Posted 04 Dec 2014 Link to this post

    I am using the latest Open Access release.

    SQL Server Compact has moved on to version 4 and I am getting the following error.

    System.Configuration.ConfigurationErrorsException Database Provider System.Data.SqlServerCeProviderFactory, System.Data.SqlServerCe, Version=3.5.1.0...Could not load file or assembly...the located assembly's manifest definition...

    the amd64 and x86 directories containing the relevant SQL Server Compact libraries are getting added to the bin folder.

    I have added the following to my app.config:

      <system.data>
        <DbProviderFactories>
          <remove invariant="System.Data.SqlServerCe.4.0"/>
          <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
        </DbProviderFactories>
      </system.data>

    Any suggestions.

     

  7. hkdave95
    hkdave95 avatar
    245 posts
    Member since:
    Nov 2006

    Posted 04 Dec 2014 in reply to hkdave95 Link to this post

    Hi

    Remembered how I did it.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using Telerik.OpenAccess;

    namespace dareCount.Data
    {
        public partial class EntitiesModel
        {
            static partial void CustomizeBackendConfiguration(ref BackendConfiguration config)
            {
                config.ProviderName = "System.Data.SqlServerCe.4.0";
            }
        }
    }

  8. Boris Georgiev
    Admin
    Boris Georgiev avatar
    190 posts

    Posted 08 Dec 2014 Link to this post

    Hello,

    I suppose you are using XML mapping, so in the rlinq file the connection string, the provider name and version are also stored and I suppose there is a mismatch between the new connection string in the app.config and the connection string in the rlinq. This is why I would recommend you to delete the connection string from the app.config and start one of the wizards: Update Database from Model or Update from Database. If there is no connection string in the app.config, when one of these wizards are started, they will show you the Setup Database Connection page, where you could select the SQLSeverCe provder version 4.0 and setup the connection string. When you leave the wizard with the finish button it will update both connection strings stored in the app config and the rlinq.

    I hope that helps.

    Regards,
    Boris Georgiev
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  9. hkdave95
    hkdave95 avatar
    245 posts
    Member since:
    Nov 2006

    Posted 08 Dec 2014 in reply to Boris Georgiev Link to this post

    Hi Boris

    Sorry for any confusion.

    I actually solve the error in the post before yours.

    The code in question resolves the error as part of Telerik functionality and works fine.

    FYI ... I pass the connection string as a parameter to the Entity Model.

    Also I do not "manually" update schema to model or from database, it seems to happen automatically when I build my project.

    Whichever, however, and whatever :) it all seems to work now.

    Your post has certainly helped, thank you.

    Kind Regards,

    David
  10. Boris Georgiev
    Admin
    Boris Georgiev avatar
    190 posts

    Posted 09 Dec 2014 Link to this post

    Hi David,

    I am glad to see you have managed to resolve the issues on your side. 

    If any other questions arise, do not hesitate to contact us again.
     
    Regards,
    Boris Georgiev
    Telerik
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner