JamTay317 February 2016

Entity Framework changing connection string with c#

I have an app that the connection string changes by per user and machine. that said I have made a test connection string just to get it testing. Then once that is working I will add the dynamic connection string.

After some research I have figured out that there is a problem with the connection string.

If someone could please tell me where I am wrong I would appreciate it.

var s = @"metadata=res://*/ProcurementModel.csdl|res://*/ProcurementModel.ssdl|
          res://*/ProcurementModel.msl;provider=System.Data.SqlClient;
          provider connection string=&';data source=JAMES-DESKTOP\SQLEXPRESS;initial catalog=MyDatabase;persist security info=True;user id=User;password=*****;MultipleActiveResultSets=True;App=EntityFramework&';";

Update:

public ProcurementContext()
{
    var s =
            @"metadata=res://*/ProcurementModel.csdl|res://*/ProcurementModel.ssdl|
            res://*/ProcurementModel.msl;provider=System.Data.SqlClient;
            provider connection string=&';data source=JAMES-DESKTOP\SQLEXPRESS;initial catalog=MyDatabase;persist security info=True;user id=jd;password=P@ssw0rd;MultipleActiveResultSets=True;App=EntityFramework&';";

    _connectionString = s;
}

Getting data:

public List<Procurement> ParcelListByUser(string userName)
{
    using (var context = new ProcurementEntities(_connectionString))
    {
        return context.Procurements.Where(p => p.UserName == userName).ToList();
    }
}

Constructor:

public ProcurementEntities(string connectionString)
    : base(connectionString)
{
}

Error message:

Format of the initialization string does not conform to specification starting at index 165.

Configuration:

The configuration of the Procurement

public class ProcurementConfiguration:DbConfiguration
{
        

Answers


Piero Alberto February 2016

I think the issue is in your connection string. I've made the same thing and I have a function to create the connection string in an automatic way.

The code:

    public static string GetConnectionString()
    {
        // Build the provider connection string with configurable settings
        string cn = "server=" + mdlImpostazioni.p.dbServer;
        cn += ";database=" + mdlImpostazioni.p.dbName;
        cn += ";uid=" + mdlImpostazioni.p.dbUser;
        cn += ";pwd=" + mdlImpostazioni.p.dbPassword + ";";
        var providerSB = new SqlConnectionStringBuilder(cn);
        var efConnection = new EntityConnectionStringBuilder();
        // or the config file based connection without provider connection string
        efConnection.Provider = "System.Data.SqlClient";
        efConnection.ProviderConnectionString = providerSB.ConnectionString;
        // based on whether you choose to supply the app.config connection string to the constructor
        efConnection.Metadata = @"res://*";  //-----> very important
        return efConnection.ToString();
    }

In this way, I pass the return value to my db context constructor, like in your code. My constructor is:

public partial class Entities : DbContext
{
    public Entities(string nameOrConnectionString)
        : base(nameOrConnectionString)
    {

    }

    public void Close()
    {
        this.Dispose();
    }
}

Try it and ask if you have some problem.


Alexei February 2016

One way to change your connection string dynamically is to only change entity framework underlying connection's connection string. Something like this:

myDbContext.Database.Connection.ConnectionString = "Data source=JAMES-DESKTOP\SQLEXPRESS;initial catalog=MyDatabase;persist security info=True;user id=DynamicUser;password=DynamicPassword;MultipleActiveResultSets=True;App=EntityFrameworkForUser";

This way, your application does not have to hardcode tokens related to entity framework (i.e. metadata).

Post Status

Asked in February 2016
Viewed 1,172 times
Voted 14
Answered 2 times

Search




Leave an answer