This is a migrated thread and some comments may be shown as answers.
Manually connect to a Sql Compact 4.0 database
7 Answers 122 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Thomas
Top achievements
Rank 1
Thomas asked on 03 Jan 2012, 03:53 PM
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?

7 Answers, 1 is accepted

Sort by
0
Thomas
Telerik team
answered on 04 Jan 2012, 11:20 AM
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!

0
Thomas
Top achievements
Rank 1
answered on 04 Jan 2012, 03:33 PM
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
0
Damyan Bogoev
Telerik team
answered on 04 Jan 2012, 03:44 PM
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!

0
hkdave95
Top achievements
Rank 2
answered on 04 Dec 2014, 11:08 AM
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.

 

0
Boris Georgiev
Telerik team
answered on 08 Dec 2014, 06:30 PM
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.
 
0
hkdave95
Top achievements
Rank 2
answered on 08 Dec 2014, 06:45 PM
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
0
Boris Georgiev
Telerik team
answered on 09 Dec 2014, 04:33 PM
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.
 
Tags
Development (API, general questions)
Asked by
Thomas
Top achievements
Rank 1
Answers by
Thomas
Telerik team
Thomas
Top achievements
Rank 1
Damyan Bogoev
Telerik team
hkdave95
Top achievements
Rank 2
Boris Georgiev
Telerik team
Share this question
or