Entity Framework and SQL Server database connection string

c# connection-string database entity-framework sql-server

Question

I've been using LocalDbs to store data ever since I started working with EF. Instead, I'd want to start working with SQL Server databases, but configuring the connection string is giving me some trouble.

https://msdn.microsoft.com/en-us/library/jj653752(v=vs.110).aspx#ssehttps://www.connectionstrings.com/

I searched Google, but none of the solutions worked for me, indicating that I must be doing something incorrectly (some connections strings produce exceptions, while others don't, but won't put anything into the database either);

Should I use both the connection string in the EF and SQL Server scripts while dealing with them?App.config & setting the DB path in the context's CTOR (by usingAppDomain.CurrentDomain.SetData("DataDirectory", path); (Or is it theapp.config plenty enough?

The following connection strings have been tested by me:

Data Source=.\GURUBEAST-PC\GURUSQL;Initial Catalog=iManager;Trusted_Connection=True;MultipleActiveResultSets=True;

Data Source=.\GURUBEAST-PC\GURUSQL;Database=iManager;Integrated Security=True;Trusted_Connection=True;MultipleActiveResultSets=True;

Data Source=.\GURUBEAST-PC\GURUSQL;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL11.GURUSQL\MSSQL\DATA\iManager.mdf;Database=iManager;Trusted_Connection=True;MultipleActiveResultSets=True;

Data Source=.\GURUBEAST-PC\GURUSQL;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL11.GURUSQL\MSSQL\DATA\iManager.mdf;Database=iManager;Trusted_Connection=True;

Data Source=.\GURUBEAST-PC\GURUSQL;Database=iManager;Trusted_Connection=True;

Data Source=.\GURUBEAST-PC\GURUSQL;Initial Catalog=iManager;Integrated Security=SSPI;

Data Source=.\GURUBEAST-PC\GURUSQL;Initial Catalog=iManager;User id=GURUBEAST-PC\GuruBeast;

where the database's name is "iManager." My instance of SQL Server uses Windows auth.

What am I doing incorrectly? Which folder—the programme files directory or theApp_Data (I tried both; I've seen both, but neither worked.)

best regards

1
0
5/10/2017 6:14:37 PM

Accepted Answer

The computer where the SQL Server instance is running may be located using the Data Source key.
It may have many strings, but the ones most often used in LAN environments are those that start with the name of the server machine and end with an eventual instance name.

Consequently, if your local PC is called GURUBEAST-PC and you didn't provide an instance name at the time of installation, the connectionstring Data Source just includes the name of the machine, GURUBEAST-PC. You should include your instance name in the Data Source key if you have one. GURUBEAST-PC\GURUSQL

This will ensure that all computers sharing a LAN have the chance to use the same connection string, even if the connection is established from the computer hosting the SQL Server.

Many shortcuts may be used to represent the local PC if the Data Source is set to the local computer:

(LOCAL)
localhost
.
\.

and finally replace the PC name on these shortcuts with the instance name

1
5/10/2017 7:22:24 PM

Popular Answer

Entity Framework will build your connection string after you have determined your host name. Here is an example of a connection string you may use to access the AdventureWorks database that is housed on your own copy of SQL Server 2014, which is appropriately called sql2014.

<connectionStrings>
    <add name="AdventureWorksEntities" connectionString="metadata=res://*/DataModels.AdventureWorksDb.csdl|res://*/DataModels.AdventureWorksDb.ssdl|res://*/DataModels.AdventureWorksDb.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\sql2014;initial catalog=AdventureWorks;persist security info=True;user id=App_AdventureWorks;password=asdasdfasdfasdf;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
</connectionStrings>

Then, your database context would like this. Once again, EF creates something for you.

public partial class AdventureWorksEntities : DbContext
{
    public AdventureWorksEntities()
        : base("name=AdventureWorksEntities")
    {
    }


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow