Connecting to .sdf

2
votes

I am new to this world, quite frankly I dislike its complexity, how ever that's besides the point.

I created a new database called Sample.sdf, I then created a new ASP.NET MVC3 application. I was reading that you have to copy the .sdf database to the root of the project. So I did that, I tried to create a new ADO.net connection to this compact database, but should I have to do that?

Can I not use connection string object in the web.config and configure it to point to that database? if so is there a way to test the connection?

I am curious because I plan to use this with Entity Framework.

Update:

So I have figured a few things out:

One the .sdf has to go into the App_Data folder and then from their you create a ADO.net entity model based on the existing database. It auto sees the database and does the rest for you, or least in my case it did.

However it throws an error, upon clicking next in the Entity Data Model Wizard, after chosing my data connection, I got:

Unable to create new database.

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)

System.Data.SqlClient.SqlException (0x80131904): 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 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

I am not really sure how to proceed.

1
.netasp.net-mvcentity-frameworksql-server-ce
The SqlException occurs because somehow you're trying to connect to your SQL Server Compact database using the full-blown SQL Server classes - that won't work. Either you didn't pick SQL Server Compact as your database type when you created the Entity data model, or your connection string got mangled some other way. - marc_s

1 Answers

0
votes

I believe you are on the right path but since it is your first time I will recommend that you follow Microsoft's step by step walk-through which is available here:

Database first approach: http://msdn.microsoft.com/en-us/data/jj206878.aspx

The general learning resource page is here: http://msdn.microsoft.com/en-us/data/ee712907

That is what I used in the beginning to get a hang of MVC and Entity Framework. It is very simple and straight forward and I assure you, that you will grow to love it.

I hope this helps.