My name is Archana CM from Microsoft SQL Developer Support team, we support many data access technologies including Entity Framework, SSIS.
I had chance to work with developer who was having issues in his Entity Framework, one of the issue was while adding data to .mdf file which was on file system.
In today's blog I am sharing my experience on how we could resolve the issue for him and what issues he was facing.
It was Windows application and Entity Framework was used. As a backend SQLExpress was used in his application and he was saving data to .mdf file which was on file system.
When we executed the application and while trying to add data to .mdf file we could see below error message
Message: The underlying provider failed on Open.
Stack trace : at System.Data.EntityClient.EntityConnection.OpenStoreConnectionIf(Boolean openCondition, DbConnection storeConnectionToOpen, DbConnection originalConnection, String exceptionCode, String attemptedOperation, Boolean& closeStoreConnectionOnFailure)
at System.Data.EntityClient.EntityConnection.Open()
at System.Data.Objects.ObjectContext.EnsureConnection()
at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
at System.Data.Objects.ObjectContext.SaveChanges()
at EFLenoard.DataMgr.AddFacility(String name, String address, String city) in D:\Research\EFParentChildInsert\EFParentChildInsert2010\DataMgr.cs:line 35
Inner Exception : InnerException = {"An attempt to attach an auto-named database for file D:\\Research\\EFParentChildInsert\\EFParentChildInsert2010\\bin\\Debug\\SplDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC shared …
Here is the connection string that was used, while issue was occurring.
<connectionStrings>
<add name="SQLDBEntities" connectionString="metadata=res://*/SplDBModel.csdl|res://*/SplDBModel.ssdl|res://*/SplDBModel.msl;provider=System.Data.SqlClient;provider connection string="data source=.\SQLEXPRESS;attachdbfilename=|DataDirectory|\SQLDB.mdf;integrated security=True;user instance=True;multipleactiveresultsets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
In this case, Windows authentication with user instance was used to connect to SQL server which was mainly causing the issue.
In order to resolve the above issue, we had 2 solutions
Solution 1:
In the existing connection string to remove the “user Instance=true” and it works.
Probable cause of the issue could be as below:
Solution 2:
We created new connection to database with SQL Authentication as a workaround.
Thus connection string turns out to be as below.
<connectionStrings>
<add name="SQLDBEntities" connectionString="metadata=res://*/SplDBModel.csdl|res://*/SplDBModel.ssdl|res://*/SplDBModel.msl;provider=System.Data.SqlClient;provider connection string="data source=.\SQLEXPRESS;attachdbfilename=D:\SQLDB.mdf;persist security info=True;
user id=saa;password=***;multipleactiveresultsets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>
Once applying either of these solutions, with below code we could save data to .mdf file which was on file system. By attached the .mdf file to SQL Server data can be confirmed.
public class DatatoMDFOnFileSystem
{
private SQLDBEntities _sqlDataContext = new SQLDBEntities();
private string _errorMessage;
public long AddDatatoMDFOnFileSystem(string name, string address, string city)
{
_errorMessage = String.Empty;
try
{
string connectString = ConfigurationManager.ConnectionStrings["SQLDBEntities"].ToString();
using (_sqlDataContext = new SQLDBEntities(connectString))
{
EFDatatoMDFOnFileSystem efDatatoMDFOnFileSystem = EFDatatoMDFOnFileSystem.CreateEFDatatoMDFOnFileSystem(0, name, address, city);
if (efDatatoMDFOnFileSystem != null)
{
_sqlDataContext.AddToEFDatatoMDFOnFileSystem(efDatatoMDFOnFileSystem);
_sqlDataContext.SaveChanges();
id = efDatatoMDFOnFileSystem.Id;
Console.WriteLine(String.Format("Record added with id, {0}.", id));
}
}
}
catch (Exception err)
{
_errorMessage = err.Message;
}
finally
{
Console.WriteLine("Error Message, {0}.", _errorMessage);
}
return id;
}
}
Happy Coding!!!!
Author : Archana , SQL Developer Engineer , Microsoft
Reviewed by : Snehadeep(MSFT), SQL Developer Technical Lead, Microsoft
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.