Thursday, January 30, 2014

How to change connection string for EDMX in runtime?



In some cases, we need to change the connection string of edmx model in run time; we can change it from code behind code.

In this article, I am explaining - How to change connection string for EDMX in runtime?

Check the below code-

  protected void btnGetData_Click(object sender, EventArgs e)
        {
            try
            {
                TestDBEntities context = new TestDBEntities(GetConnectionString("DBName"));
                var result1 = from data in context.tab_Employees
                                       select data;
                gvEmpDetails.DataSource = result1.ToList();
                gvEmpDetails.DataBind();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

//Method to get connection string
        private string GetConnectionString(string strDBName)
        {
            SqlConnectionStringBuilder sqlBuilder;
            EntityConnectionStringBuilder entityBuilder;
            try
            {
                sqlBuilder = new SqlConnectionStringBuilder();
                sqlBuilder.DataSource = @"server name";
                sqlBuilder.InitialCatalog = strDBName;
                sqlBuilder.IntegratedSecurity = true;
                sqlBuilder.MultipleActiveResultSets = true;

                entityBuilder = new EntityConnectionStringBuilder();
                entityBuilder.Provider = "System.Data.SqlClient";
                entityBuilder.ProviderConnectionString = sqlBuilder.ToString();
                entityBuilder.Metadata = @"res://*/ModelName.csdl|res://*/ModelName.ssdl|res://*/ModelName.msl";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return entityBuilder.ToString();
        }


In above code, while creating connection string, you need to pass details for connection string. Like-
Database name, Server name etc.

Here ModelName= Name of the model which you have created while generating edmx.

No comments:

Post a Comment