Manually connect to a Sql Compact 4.0 database

[Thomas]

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?

[Thomas]

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

[Thomas]

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

[Damyan Bogoev]

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

[hkdave95]

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.

 

[Boris Georgiev]

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

 

 

[hkdave95]

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

[Boris Georgiev]

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