Entity Framework Connection String Variable

sridhar_vardhi · 13 September 2016 10:11

Hi Team,

I just wanted to know how i can proceed with the Entity Frame work connection string variable creation in the Octopus deploy. Below is my Connection string format. I just wanted to know how i can create the global variable to use for my Web.config.

<add name="MLMEntities" connectionString="metadata=res://*/EDMX.MLM_DataModel.csdl|res://*/EDMX.MLM_DataModel.ssdl|res://*/EDMX.MLM_DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=LAP103721;initial catalog=Workflow;user id=**;password=****;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Please help me how to create the variable for the above connection string

Dalmiro · 13 September 2016 19:04

Hi,

Thanks for reaching out. You can use the method outlined in this doc: http://docs.octopusdeploy.com/display/OD/Configuration+files#Configurationfiles-ConfigurationVariablesConfigurationvariables

Let me know how it goes,
Dalmiro

sridhar_vardhi · 13 September 2016 20:49

Hi Dalmiro,

Thank you very much for your reply. If you observe my connection string it included the Entityframework parameters in that. If possible can you please help me with some sample parameter names using the above connection string variables.

Thanks in advance for your help.

Thanks,
Sreedhar

Dalmiro · 13 September 2016 20:52

Hi Sreedhar,

Which part of the connection string is going to be replaced with a variable?

Approach A - the whole connection string is a variable. This would be the easiest one.

Approach B - Only specific parts of the connection string will be variables. Like:

<add name="MLMEntities" connectionString="metadata=res://*/EDMX.MLM_DataModel.csdl|res://*/EDMX.MLM_DataModel.ssdl|res://*/EDMX.MLM_DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=[Variable for data source];initial catalog=Workflow;user id=[Variable for user];password=[Variable for Pass];MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Thanks,
Dalmiro

sridhar_vardhi · 14 September 2016 08:56

Hi Dalmiro,

If i wanted the the specific parts of the connection string , Do i need to specify as shown in below? what i need to do with &quot value?

metadata=res:///EDMX.MLM_DataModel.csdl|res:///EDMX.MLM_DataModel.ssdl|res://*/EDMX.MLM_DataModel.msl;
provider=System.Data.SqlClient;
provider connection string=“
data source=[Variable for data source];
initial catalog=Workflow;
user id=[Variable for user];
password=[Variable for Pass];
MultipleActiveResultSets=True;
App=EntityFramework”"
providerName=“System.Data.EntityClient”

Dalmiro · 14 September 2016 19:02

Hi Sridhar,

If your connection string is working with that &quot value on it on your dev environment, then just leave it there.

The logic behind the variable replacement is easy:

Lets say you have this connection string:

<add name="MLMEntities" connectionString="metadata=res://*/EDMX.MLM_DataModel.csdl|res://*/EDMX.MLM_DataModel.ssdl|res://*/EDMX.MLM_DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=LAP103721;initial catalog=Workflow;user id=**;password=****;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

You want to replace the User and Password during the deployment, so you put variables on the connection string for those 2 values:

<add name="MLMEntities" connectionString="metadata=res://*/EDMX.MLM_DataModel.csdl|res://*/EDMX.MLM_DataModel.ssdl|res://*/EDMX.MLM_DataModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=LAP103721;initial catalog=Workflow;user id=#{UserID};password=${Password};MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

Then you create 2 variables called UserID and Password on your project (see attached screenshot).
Finally you enable the feature Replace Variables in Files and set your config file as a target for variable replacement (see attached screenshot).

That should do it.

Cheers,
Dalmiro