Click here to Skip to main content
15,888,968 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, Using the below web api GET method with entity framework. Currently the class SchoolDBEntities() connects to DEV database. How do I change the connectionstring when the API gets moved up to QAT, Pre-PROD & PROD. Is there a way to change it from the web.config file?

C#
public class StudentController : ApiController
{
    public IHttpActionResult GetAllStudents ()
    {
        IList<StudentViewModel> students = null;

        using (var ctx = new SchoolDBEntities())
        {
            students = ctx.Students.Include("StudentAddress")
                        .Select(s => new StudentViewModel()
                        {
                            Id = s.StudentID,
                            FirstName = s.FirstName,
                            LastName = s.LastName
                        }).ToList<StudentViewModel>();
        }

        if (students.Count == 0)
        {
            return NotFound();
        }

        return Ok(students);
    }
}


What I have tried:

I have tried to use the code below. But couldn't get it working.

string connectionString = new System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);

System.Data.SqlClient.SqlConnectionStringBuilder scsb = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);

EntityConnectionStringBuilder ecb = new EntityConnectionStringBuilder();
ecb.Metadata = "res://*/Sample.csdl|res://*/Sample.ssdl|res://*/Sample.msl";
ecb.Provider = "System.Data.SqlClient";
ecb.ProviderConnectionString = scsb.ConnectionString;

dataContext = new SampleEntities(ecb.ConnectionString);


public class SingleConnection
    {
        private SingleConnection() { }
        private static SingleConnection _ConsString = null;
        private String _String = null;

        public static string ConString
        {
            get
            {
                if (_ConsString == null)
                {
                    _ConsString = new SingleConnection { _String = SingleConnection.Connect() };
                    return _ConsString._String;
                }
                else
                    return _ConsString._String;
            }
        }

        public static string Connect()
        {
            //Build an SQL connection string
            SqlConnectionStringBuilder sqlString = new SqlConnectionStringBuilder()
            {
                DataSource = "SQLSERVER\\DEV".ToString(), // Server name
                InitialCatalog = "TestDB",  			  //Database
                UserID = "devuser",         			  //Username
                Password = "devpassword",  				  //Password
            };
            //Build an Entity Framework connection string
            EntityConnectionStringBuilder entityString = new EntityConnectionStringBuilder()
            {
                Provider = "System.Data.SqlClient",
                Metadata = "res://*/DevModel.csdl|res://*/DevModel.ssdl|res://*/DevModel.msl",
                ProviderConnectionString = @"data source=SQLSERVER\DEV;initial catalog=TestDB;user id=devuser;password=devpassword"   // sqlString.ToString()
            };
            return entityString.ConnectionString;
        }
	}
Posted
Updated 20-Jun-18 11:08am
v3
Comments
F-ES Sitecore 20-Jun-18 4:24am    
It's usually done using something called a "config transform", but this also depends on what deployment tools you use (if any) to push the code to other environments.
Member 12586110 20-Jun-18 17:09pm    
Hi, Could you please help me do it with code. I updated my original post accordingly. Thank you.
F-ES Sitecore 21-Jun-18 4:41am    
If you want to do it in code the easiest way is to have the connection string for each environment in your config, so "ConnectionStringDev", "ConnectionStringTest" etc. Then have another config value (eg ConnectionStringName) that says what the current connection string is, so on your local machine you'd set that to "ConnectionStringDev". Rather than hard-coding the connection string to retrieve, you'll use the config value instead

string connectionString = new System.Configuration.ConfigurationSettings.AppSettings[System.Configuration.ConfigurationSettings.AppSettings["ConnectionStringName"]]);
j snooze 20-Jun-18 17:45pm    
Just as an FYI, if you have connection strings in your config file, and have the System.Configuration added to your project, all you have to do to get the connection string from the connection string section is this.
ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString

I'm assuming in your config file you have
the connectionStrings element along with the --add name="DefaultConnection" connectionString="my server connection information here"--
Member 12586110 21-Jun-18 13:11pm    
Hi, web.config file has the database connectionstring.

<connectionstrings>
<add name="mysqldb" connectionString="metadata=res://*/DevModel.csdl|res://*/DevModel.ssdl|res://*/DevModel.msl;provider=System.Data.SqlClient;provider connection string="data source=SQLSERVER\DEV;initial catalog=TestDB;user id=devuser;password=devpassword;MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />


How do I make use of this in my code.

string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["mysqldb"].ConnectionString;

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900