unable to access remote database in C#

2019-09-12 08:17发布

问题:

i have changed the connection string to point to a database in the remote server. But when I execute the project the program still points to the local db.

<entityFramework>
<providers>
  <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>


<connectionStrings> 
<add name="TEDALS_Ver01.DAL.TedalsContext" 
  connectionString="Data Source=FE0VMC0643\SQLEXPRESS;
  AttachDbFilename=|DataDirectory|\TeDaLSdev.mdf;
  Integrated Security=False" 
  providerName="System.Data.SqlClient" 
/>
<!--<add
   name="TEDALS_Ver01.DAL.TedalsContext"
   connectionString="Server=FE0VMC0643; Database=TeDaLSdev; "
   providerName="System.Data.SqlClient" />-->

<!--<add name="TEDALS_Ver01.DAL.TedalsContext"
     providerName="System.Data.SqlClient" 
     connectionString="Server=FE0VMC0643;Data Source=FE0VMC0643;
     Initial Catalog=TeDaLSdev;
     Integrated Security=True;
     Connect Timeout=15;
     Encrypt=False;
     TrustServerCertificate=False;" />-->

</connectionStrings> 

Comments are the different connection strings i have treid so far. i never had a connections string when I was using the LocalDB.

Constructor for Connection

public class TedalsContext : DbContext
{
    public TedalsContext()
        : base("TedalsContext")
    {
        //Database.SetInitializer<TedalsContext>(null);
    }
}

i am using SQL Server Express as my database. I have also tried changing the name of the parameter for base in constructor as the name of the Database. But it did not change anything.

I have already tried if I have access to the database through SSMS. I am able to create tables but I am unable to rename the database as such(I do not have access rights to rename the database TeDalSdev).

Are there any other work around i could try? Should the name of the remote database and the local database should be the same to avoid changing a lot of code?

UPDATE

Controller

public class LsystemFamiliesController : Controller
{
    private TedalsContext db = new TedalsContext();
    //Code goes here
}

回答1:

I've added a connection string from a web config file of one of my projects and all the fields that (should) be checked. This is within the <connectionStrings> tags. You need to make it your default connection and you can only have one connection string as the default connection.

<add name="DefaultConnection" providerName="System.Data.SqlClient" 
    connectionString="Data Source=Providedbyhost;integrated 
    security=false;Initial Catalog=DB_Name;User Id=UserId;Password=password;
    Trusted_Connection=false; TrustServerCertificate=true;
    Encrypt=true;MultipleActiveResultSets=True" />

Where you have your DB Context:

public TedalsContext()
    : base("DefaultConnection")

Switch everything to DefaultConnection, until you get it working, then you can focus on changing names. Comment out your local db connection strings. Even remove it from the project if you need to (and save it elsewhere), while you are testing this.

I've also added a screen shot of the my folder directory, to show which web config folder I am modifying. See it's highlighted in blue.

Also this screen shot shows you where to navigate to test your connection string within VS.

I suggest you look here:

How to: Access SQL Server Using Windows Integrated Security

What will be the connectionstring for mssql windows authentication

and this:

Connection string using Windows Authentication

As I suggested in the discussion. I recommend also contacting web hosting provider, as I have had this problem where it will not connect and it's been a problem at the hosting end.

If you need more help, shout out.



回答2:

Under your TedalsContext constructor, use : base(connectionStringName).

Otherwise, the context will treat it as code first and create a TedalContext inside your SQLExpress Local DB.

public class TedalsContext : DbContext
{
    public TedalsContext()
        : base("TEDALS_Ver01.DAL.TedalsContext")
    {
        //Database.SetInitializer<TedalsContext>(null);
    }
}