Change Entity Framework Connecting String To Server

Question

Here is what i have so far :

    <add name="gymEntities1" connectionString="metadata=res://*/DateModel.csdl|res://*/DateModel.ssdl|res://*/DateModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=gym;user id=sa;password=xxxx;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

it works on my LocalHost Database, and I can load my Data from it. however, I have a server, installed sqlserver with my database on that, basicaly when i change my sqlcommands connecting string this work but in some part of my program I used entity framework and have no idea how to change it connecting string, with some posts in stackoverflow I change that to

    <add name="gymEntities2" connectionString="metadata=res://*/DataModel.csdl|res://*/DataModel.ssdl|res://*/DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=tcp:46.105.124.144;initial catalog = gym ;User ID=sa;Password=xxxx&quot;" providerName="System.Data.EntityClient" />

but it still read datas from my localhost and not connect to my server. I don't know how when i change this connecting string to my server it still read data from my localhost database.

what is the best way to change connecting string from App.Config?

Answer 1

First Possible Issue:

It is less likely since other people suggested to you.But, it is possible that you are missing a connection string in one of your web.config or app.config. It is a good habit to copy your string to every project. Example. I have 3 different projects in my solution (Library, WCF, WPF). I copied the following connection string to each project (One sample for Local SQL Server and another for Azure):

<connectionStrings>
    <add name="LocalSQLServerSample.CodeREDEntities" connectionString="metadata=res://*/CodeRED.csdl|res://*/CodeRED.ssdl|res://*/CodeRED.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=MachineName\ServerName;initial catalog=CodeRED;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />
    <add name="AzureSQLServerSample.CodeREDEntities" connectionString="metadata=res://*/CodeRED.csdl|res://*/CodeRED.ssdl|res://*/CodeRED.msl;provider=System.Data.SqlClient;provider connection string='data source=azureservername.database.windows.net;initial catalog=&quot;CodeRED&quot;;persist security info=True;user id=CodeRED;password=R%Chd$g*VHs28eEr;MultipleActiveResultSets=True;App=EntityFramework'" providerName="System.Data.EntityClient" />
</connectionStrings>

Second possible issue:

You have mentioned that you are using entity framework. Are you using ObjectContext to access it? If yes, I have the method below to call it every time I want to access any database:

From sample above: name="LocalSQLServerSample.CodeREDEntities"

_containerName is CodeREDEntities (The same for all my connections). environment is to determine which database you are connecting to. For example, in the above connection sample, I have LocalSQLServerSample and AzureSQLServerSample and I usually have something like PRODUCTION, DEVELOPMENT, TESTING....

    public static ObjectContext getObjectContext(string environment, bool isReadOnly)
    {
        environment = environment == null ? "" : environment.Trim();
        environment = environment.Length == 0 ? "" : (environment + ".");

        ObjectContext objectContext = new ObjectContext(
                ConfigurationManager.ConnectionStrings[environment + _containerName].ToString());
        objectContext.DefaultContainerName = _containerName;
        objectContext.CommandTimeout = 0;

        objectContext.ContextOptions.ProxyCreationEnabled = !isReadOnly;

        return objectContext;
    }

Sample of how to use it:

Common is a common class that I use to store shared information such as getting common error format used for Common.getInnerExceptionMessage.

Also, you don't have to always pass environment, you can store it as a constant to be able to call it such as (I always pass it to be able to mix connection when I need to for specific calls): You can modify connection from anywhere by changing _selectedEnvironment if you do not wish to pass it everywhere.

    public const string _ENVIRONMENT_DEVELOPMENT = "LocalSQLServerSample";
    public const string _ENVIRONMENT_PRODUCTION = "AzureSQLServerSample";
    public static string _selectedEnvironment = _ENVIRONMENT_PRODUCTION;

Sample of getting item based on id:

Note: User is a class generated by entity framework from database.

    public UsersDataGrid GetItem(string environment, long id)
    {
        ObjectContext objectContext = Common.getObjectContext(environment, false);

        try
        {
            var item = objectContext.CreateObjectSet<User>()
                .Where(W => W.ID == id)
                .Select(S => new UsersDataGrid()
                {
                    Active = S.Active,
                    ID = S.ID,
                    Unique_ID = S.Unique_ID,
                    First_Name = S.First_Name.ToUpper(),
                    Last_Name = S.Last_Name.ToUpper(),
                    Email = S.Email,
                    School = S.School.Title.ToUpper(),
                    Gender = S.Gender.Title.ToUpper(),
                    TShirt_Size = S.TShirt_Size.Title.ToUpper(),
                    GUID = S.GUID + "",
                    Note = S.Note,
                    Machine_User = S.Machine_User,
                    Machine_Name = S.Machine_Name,
                    Created_On = S.Created_On,
                    Last_Updated_On = S.Updated_On
                }).FirstOrDefault();

            return item;
        }
        catch (Exception exception)
        {
            return new UsersDataGrid()
            {
                Note = ("Service Error: " +
                Common.getInnerExceptionMessage(exception))
            };
        }
    }

2nd Sample: Updating a user:

Note: Common.CopyValuesFromSourceToDestinationForUpdate is only a generalized method copying items from item object to entityItem, instead you can copy values normally such as entityItem.ID = item.ID and so on...

    public Result Update(string environment, User item)
    {
        ObjectContext objectContext = WCF_Service_Library.Classes.Common.getObjectContext(environment, false);

        try
        {
            var entityItem = objectContext.CreateObjectSet<User>()
                .AsEnumerable().Where(Item => Item.ID == item.ID).ToList().FirstOrDefault();

            if (entityItem == null)
                return new Result("Item does NOT exist in the database!");

            entityItem = Common.CopyValuesFromSourceToDestinationForUpdate(item, entityItem) as User;

            objectContext.SaveChanges();

            return new Result(entityItem.ID);
        }
        catch (Exception exception)
        {
            return new Result("Service Error: " + Common.getInnerExceptionMessage(exception));
        }
    }

Third issue (it does not look like it, but you may encounter it):

If you publish your app and ONLY sign your WPF project, you will not get error during publishing, but you may not be able to connect to the database. You must sign all your projects in your solution.

Hopefully this help you with your problem

Answer 2

Check the WebConfig of your startup project. Entity framework reads ConnnectionString from AppConfig when you run Update Model From Db operation .

But in runtime it reads ConnnectionString from WebConfig in your Startup project