DataRepository dynamic connection string

Hello,

I'm working with dinamic connection strings and I have done it correctly in the Service Layer using this sentences:

MyProject.Service.ConnectionScope.Current.ConnectionStringKey = "DinnamicConnectionStringKey";
MyProject.Service.ConnectionScope.Current.DynamicConnectionString = newConnectionString;

 Now I wanna do the same with the DataRepository but i don't how can I do it.

I tried this:

DataRepository.ConnectionStrings[Provider].ConnectionString = newConnectionString; 

and this:

DataRepository.AddConnection(provider, newConnectionString);

but both don't work, the first one can't write the property and the second one changes the connection string but the providers still have the old.

I've looked into the forums:

http://community.codesmithtools.com/forums/p/2638/10825.aspx

http://community.codesmithtools.com/forums/p/2932/12166.aspx#12166

They tell how to add connection strings but .. when I call DataRepository my application is still using the old connection strings.

Any help will be appreciated.

Hi,

Im having the same problem. How can I resolve this?

I want to set the connection string at runtime because I'm storing diferent connections strings on the database (they are diferents environments: test, production, etc).

I don't want to have multiple connections (or dynamic connections). I want just to override the connection string stored in app.config. file when my app starts.

Thanks in advance.

Hi,

Can anybody help me?

Is it possible to do that?

Any suggestion or help will be greatly appreciated.

Thanks in advance.

Hi guys,

I'm working on this and I know something new.

My problem is on a BackgroundWorker that I'm using to load data on my forms. When I start the application I select the environment in a form. Then I change the current connection (I'm getting the new connection string from the db) and I make a query using the service layer and... It works.

But when I create the BackgroundWorker and is raised the DoWork event all the configuration is losed and the DataRepository uses the connection string that is stored on app.config.

I think that the configuration of the primary thread is not copied to the thread that uses the BackgroundWorker and then Service.ConnectionScope.Current property is not working well.

Am I right?

Thanks.

Hi again,

I'm still working on this. I think that my problem is commented but not resolved (http://community.codesmithtools.com/forums/p/4199/17338.aspx#17338).

I'm using BackgroundWorker when I load the grid in my forms. I use the GetPaged method on BeforeRowRegionScroll event of the grid. My BackgroundWorker looks for the records and refresh my grid adding the new records.

Do you know an alternative method to do this without using Backgroundworker?

The "best practice" is like Form1.cs way?

Thanks.

Check out this post from me....it provides some useful links.

http://community.codesmithtools.com/forums/p/7964/29551.aspx#29551

jeff

Finally:

I'm making this everytime that the app starts or when I choose another environment with app started:

            string newConnectionString = "Data Source=xxx.xxx.xxx.xxx;Initial Catalog= .........."
            string connectionStringName = "MyConnectionStringName";
            string providerKey = MyProject.Service.ConnectionScope.Current.DataProvider.Name;
            string applicationName = System.Threading.Thread.CurrentThread.Name;

            if (MyProject.DAL.DataRepository.Connections.ContainsKey(connectionStringName))
            {
    // I'm changing the environment
                MyProject.DAL.DataRepository.Connections.Remove(connectionStringName);
            }

            // Get the current provider
            MyProject.DAL.Bases.NetTiersProvider oldProvider = MyProject.DAL.DataRepository.Providers[providerKey];

   // Create a new provider
            MyProject.DAL.Bases.NetTiersProvider newProvider = new MyProject.DAL.SqlClient.SqlNetTiersProvider();

   // Initialize new provider
            NameValueCollection collection = new NameValueCollection();
            collection.Add("ProviderInvariantName", ((MyProject.DAL.SqlClient.SqlNetTiersProvider)oldProvider).ProviderInvariantName);
            collection.Add("Description", oldProvider.Description);
            collection.Add("ConnectionString", newConnectionString);
            collection.Add("ConnectionStringName", connectionStringName);
            collection.Add("UseStoredProcedure", ((MyProject.DAL.SqlClient.SqlNetTiersProvider)oldProvider).UseStoredProcedure.ToString());
            collection.Add("EntityCreationalFactoryType", oldProvider.EntityCreationalFactoryType.ToString());
            collection.Add("UseEntityFactory", oldProvider.UseEntityFactory.ToString());
            collection.Add("EnableEntityTracking", oldProvider.EnableEntityTracking.ToString());
            collection.Add("EnableMethodAuthorization", oldProvider.EnableMethodAuthorization.ToString());
            collection.Add("ApplicationName", applicationName);
            newProvider.Initialize(providerKey, collection);

   // Update all objects in memory
            MyProject.DAL.DataRepository.LoadProvider(newProvider, true);
            ((MyProject.DAL.SqlClient.SqlNetTiersProvider)MyProject.DAL.DataRepository.Providers[providerKey]).ConnectionString = newConnectionString;
            MyProject.Service.ConnectionScope.Current.DataProvider = newProvider;
            MyProject.Service.ConnectionScope.Current.ConnectionStringKey = connectionStringName;
            MyProject.Service.ConnectionScope.Current.DynamicConnectionString = newConnectionString; // Optional

Optionally you can update the app.config file everytime you make the change with this code:

            try
            {
                string m_strFullPath = "";
                Assembly asm = Assembly.GetExecutingAssembly();
                XmlDocument xmlDoc = new XmlDocument();
                FileAttributes originalAttributes;

                m_strFullPath = ConfigurationManager.OpenExeConfiguration(asm.Location).FilePath;
                xmlDoc.Load(m_strFullPath);

                originalAttributes = File.GetAttributes(m_strFullPath);
                if ((originalAttributes & FileAttributes.ReadOnly) == FileAttributes.ReadOnly)
                {
                    File.SetAttributes(m_strFullPath, FileAttributes.Normal);
                }
                XmlNodeList nodeList = xmlDoc.SelectSingleNode("/configuration/connectionStrings").ChildNodes;
                foreach (XmlNode xn in nodeList)
                {
                    if (string.IsNullOrEmpty(xn.Name) || xn.Name != "add")
                    {
                        continue;
                    }
                    XmlElement xe = (XmlElement)xn;

                    if (xe.GetAttribute("name") == connectionStringName )
                    {
                        xe.SetAttribute("connectionString", newConnectionString);
                        break;
                    }
                }
                xmlDoc.Save(m_strFullPath);

                File.SetAttributes(m_strFullPath, originalAttributes);

            }
            catch (System.NullReferenceException NullEx)
            {
                throw NullEx;
            }
            catch (Exception ex)
            {
                throw ex;
            }

It is working for me.

I think it would'nt be difficult to add a method somewhere (perhaps in DataRepository) to change the current connection passing the new connection string. I'll create a new Issue at Google Code.

I'm still having a problem with BackgroundWorker. By the moment I've disabled the background work and perhaps I will use a thread.

Thanks jeff.