EntityConnection String for SqlServerCe

Question

How would I create an EntityConnection string for an SqlServerCe database?

I tried following the MSDN Example on How to: Build an EntityConnection Connection String

const string PROVIDER = "System.Data.SqlClient";

static string BuildEntityConnString(string dbFileName, string password) {
  // dbFileName is filename without the extension
  SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
  sqlBuilder.DataSource = PROVIDER;
  sqlBuilder.InitialCatalog = dbFileName;
  sqlBuilder.IntegratedSecurity = true;
  sqlBuilder.Password = password;
  string providerString = sqlBuilder.ToString();
  EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
  entityBuilder.Provider = PROVIDER;
  entityBuilder.ProviderConnectionString = providerString 
  entityBuilder.Metadata = string.Format(@"res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl", dbFileName);
  string connString = entityBuilder.ToString();
  using (EntityConnection con = new EntityConnection(connString)) {
    con.Open();
    Console.WriteLine("{0} Entity String created.", dbFileName);
    con.Close();
  }
  return connString;
}

However, but it throws an error:

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: Named Pipes Provider, error: 40 - Could not open 
a connection to SQL Server)

With some searching on here, I was able to find info stating that the MSDN Example returns an SqlClient connection.

However, I was not able to find out how to return an Sql CE Connection.

I tried supplying the EntityConnection the SQL CE Connection String that allows me to open the .SWF database and read it conventionally:

const string PROVIDER = "Microsoft.SqlServerCe.Client.3.5";

static string BuildEntityConnString(string dbFileName, string password) {
  EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
  entityBuilder.Provider = PROVIDER;
  entityBuilder.ProviderConnectionString = myCeConnectionString;
  entityBuilder.Metadata = string.Format(@"res://*/{0}.csdl|res://*/{0}.ssdl|res://*/{0}.msl", dbFileName);
  string connString = entityBuilder.ToString();
  using (EntityConnection con = new EntityConnection(connString)) {
    con.Open();
    Console.WriteLine("{0} Entity String created.", dbFileName);
    con.Close();
  }
  return connString;
}

This version throws a different error:

Unable to find the requested .Net Framework Data Provider.
It may not be installed.

What could I be doing wrong?

I found this related SQL Server Fix, but the fix needs to be something I can set in my code so that my project will run on our Customers' computers at other locations without network administrators.

Ben M · Accepted Answer · 2011-05-24 16:47:32Z

1

You need to register the CE provider in your app.config. Also, AFAIK, "Microsoft.SqlServerCe.Client.3.5" is the pre-release name of the provider; the RTM is "System.Data.SqlServerCe.3.5".

Try changing PROVIDER to match the second string, and add this to app.config:

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

answered May 24, 2011 at 16:47

Ben M

22.6k3 gold badges69 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user153923 Over a year ago

Hi Ben. Is the PublicKeyToken listed above particular to an SqlServerCe instance, or is it just something I create through Visual Studio?

user153923 Over a year ago

I copied everything "as written" and got the MetadataException Unable to load the specified metadata resource.

user153923 Over a year ago

I just looked in my installed programs and verified that I am using Microsoft SQL Server Compact SP2 ENU, so I changed Version in your app.config settings to match that item's Product version of 3.5.8080.0. I still get the same error. :(

Ben M Over a year ago

PublicKeyToken is part of the assembly's strong name; it has nothing to do with SQL Server CE. (Read up on .NET strong names for more info.)

Ben M Over a year ago

It sounds like you haven't created an EDMX file. Have you tried simply adding an Entity Data Model item to the project, and selecting "SQL Server Compact" in the connection provider? That should tie it all together for you...

|

Collectives™ on Stack Overflow

EntityConnection String for SqlServerCe

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related