concurrent SqlConnection and EntityConnection to a local Sql Express Server

Question

I'm using Linq2Entity for most of my database operations. However for database creation, table creation and initial data insertion I use plain SQL files. Therefore I need both an SqlConnection and an EntityConnection. Unfortunately the Entity Framework starts complaining that the Sql Server is not listening on the other end of the pipe.

I'm not sure what the problem is here, it could be due to user instancing. Clearing the pool of the SqlConnection or disposing the connection instance does not help.

The connection string I'm using is the following:

"Data Source=.\SQLEXPRESS; Initial Catalog=dbname; Integrated Security=SSPI;"

update:

I have tried to use the EntityConnection for database maintenance purposes but I'm running into troubles. I can't use the EntityConnection to create databases and drop databases. The following syntax is unsupported for an EntityConnection but works fine for an SqlConnection to ms SQL express.

CREATE DATABASE silverfit ON ( NAME = silverfit, FILENAME = 'c:\silverfit\silverfit.mdf' );

Also for some reason the EntityConnection does not allow me to change databases which is necessary to drop a database. I'm afraid I still need the SqlConnection to create the database and tables..

Is there a way for SqlConnections and EntityConnections to coexist for local ms SQL express databases?

Thanks, Wouter

Answer 1

I don't agree that you need a plain SQL connection and Entity connection for this task, at least as you've described it. You can execute SQL using the Entity connection. Look at EntityConnection.CreateDbCommand. Naturally, there's a danger here that you are doing DB-server-specific things on a non-DB-server-specific instance like a EntityConnection. But it probably beats having a separate connection, in this case.

Answer 2

Did you try to use the EntityConnection.StoreConnection to retrieve the SqlConnection and execute commands with it ?

Answer 3

Have you tried creating a SqlConnection which you then pass to the constructor for your EntityConnection? One of the overloads for EntityConnection takes a MetadataWorkspace and a DbConnection (from which SqlConnection derives.) The slight drawback of this is that you must create your MetadataWorkspace manually, which gathers up the .csdl, .ssdl, and .msl files that define your workspace. However, in the long run, you should be able to share a single connection for both executing DML queries, and using Entity Framework.

Answer 4

If you're trying attaching your database file to SQL Server Express at run time, which it seems as if you are, then only one connection can be open on it at once. This is a problem for other things, not just what you're trying to accomplish. Say, for instance, you connect to 'c:\silverfit\silverfit.mdf' through the server explorer in VS and try to open one of the tables in the db. After you open the table, try running your application. It will bomb.

However, if you open up SQL Management Studio Express (you can download it here), and then attach the database to the SQL Server, the problems you are experiencing should go away. At that point you should be able to open multiple connections to your database, via SQLConnection or EntityConnection.

Attaching your database at run time to the SQL Server express engine really only works well for demoing purposes, or proof of concepts.

Answer 5

Is ADO.NET holding a connection open to the database through connection pooling? Try adding Pooling=False to the connection string, as this may allow the database to be closed before you drop it.