SqlConfigurationSource

Topics: Enterprise Library Core, General discussion
Oct 30, 2007 at 7:42 AM
Hello!

I wonder if anyone can explain how to use the SqlConfigurationSource as a configuration source instead of for example a file configuraton source. I would also like to know how to get it working in the designer so that it can be configured using the designer. Do I need to recompile the entire Enterprise Library, with the code for the SqlConfigurationSource?

Is there any documentation available for this? Or any examples on how to do it?

Regards, Jörgen
Oct 31, 2007 at 9:51 AM
Hi Jörgen,

I am using SQLConfigurationSource.
1. You need to execute the sql file that comes with the ConfiguartionSource against a database and make sure you have a proper database to store configuration data.
This generates a couple of tables and four procedures.
2. After that, copy Design and runtime dlls to bin folder where you are running Enterprise Library Configuration Tool if they are not already there.
3. Execute entlibconfig.exe,
3.a. Create a "New Application",
3.b. Right click on "Application Configuration" node and select "New / Configuration Sources"
3.c. Right click on "Configuration Sources" and select "New / Sql Configuartion Source"
3.d. Fill in the properties, The connection string that you created on first step, then fill the four procedure names that was created in first step too.
4. Add you own stuff, Logging, Caching etc...
5. And save!
If everything works fine, Check the tables in the database, you should see your own data. (Don't modify the data in the tables, let entlibConfigTool handle that)

If you have any other question mail me at mertsakarya@hotmail.com.

For example I modified the "Sql Configuartion Source" source code that, the configuration is retrieved by multiple computers from database...
Interesting things can be created...

Regards,
Mert


Oct 31, 2007 at 12:27 PM
Thanks for your answer Mert.

I have tried doing the following:

- Building the projects in the SqlConfiguration solution.
- Copying the Microsoft.Practices.EnterpriseLibrary.SqlConfigurationSource.Design.dll, Microsoft.Practices.EnterpriseLibrary.SqlConfigurationSource.dll, Microsoft.Practices.EnterpriseLibrary.Common.dll, Microsoft.Practices.EnterpriseLibrary.Configuration.Design.dll to the \Bin folder where the EntLibConfig.exe resides. This made the application not start anymore, and it wanted to send error information to Microsoft.
- I then removed the Microsoft.Practices.EnterpriseLibrary.Common.dll and the Microsoft.Practices.EnterpriseLibrary.Configuration.Design.dll and this made the EntLibConfig.exe start, but it threw an exception when I tried to create a new application and I couldn't do anything more. (Please look at the bottom of this post for the exact Exception information.
- When I remove the Microsoft.Practices.EnterpriseLibrary.SqlConfigurationSource.Design.dll and Microsoft.Practices.EnterpriseLibrary.SqlConfigurationSource.dll everything works fine again.
- Creating the tables in a database was not a problem.

Any ideas on why this happens? I am running XP, with .NET runtime v2.0.50727. Could it be that in the QuickStart for the May 2007 release, the referenced Microsoft.Practices.EnterpriseLibrary.Configuration.Design assembly is different from the one in the \Bin under the Enterprise Library catalogue?

Best regards, Jörgen

EXCEPTION:

System.IO.FileLoadException: Could not load file or assembly 'Microsoft.Practices.EnterpriseLibrary.Configuration.Design, Version=3.1.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
File name: 'Microsoft.Practices.EnterpriseLibrary.Configuration.Design, Version=3.1.0.0, Culture=neutral, PublicKeyToken=null'
at System.ModuleHandle.ResolveType(Int32 typeToken, RuntimeTypeHandle* typeInstArgs, Int32 typeInstCount, RuntimeTypeHandle* methodInstArgs, Int32 methodInstCount)
at System.ModuleHandle.ResolveTypeHandle(Int32 typeToken, RuntimeTypeHandle[] typeInstantiationContext, RuntimeTypeHandle[] methodInstantiationContext)
at System.Reflection.Module.ResolveType(Int32 metadataToken, Type[] genericTypeArguments, Type[] genericMethodArguments)
at System.Reflection.CustomAttribute.FilterCustomAttributeRecord(CustomAttributeRecord caRecord, MetadataImport scope, Assembly& lastAptcaOkAssembly, Module decoratedModule, MetadataToken decoratedToken, RuntimeType attributeFilterType, Boolean mustBeInheritable, Object[] attributes, IList derivedAttributes, RuntimeType& attributeType, RuntimeMethodHandle& ctor, Boolean& ctorHasParameters, Boolean& isVarArg)
at System.Reflection.CustomAttribute.GetCustomAttributes(Module decoratedModule, Int32 decoratedMetadataToken, Int32 pcaCount, RuntimeType attributeFilterType, Boolean mustBeInheritable, IList derivedAttributes)
at System.Reflection.CustomAttribute.GetCustomAttributes(Assembly assembly, RuntimeType caType)
at System.Reflection.Assembly.GetCustomAttributes(Type attributeType, Boolean inherit)
at Microsoft.Practices.EnterpriseLibrary.Configuration.Design.ConfigurationDesignManagerDomain.Load(Assembly assembly)
at Microsoft.Practices.EnterpriseLibrary.Configuration.Design.ConfigurationDesignManagerDomain.LoadFrom(String path)
at Microsoft.Practices.EnterpriseLibrary.Configuration.Design.ConfigurationUIHierarchy.Load()
at Microsoft.Practices.EnterpriseLibrary.Configuration.Design.AddConfigurationApplicationNodeCommand.ExecuteCore(ConfigurationNode node)
at Microsoft.Practices.EnterpriseLibrary.Configuration.Design.ConfigurationNodeCommand.Execute(ConfigurationNode node)
at Microsoft.Practices.EnterpriseLibrary.Configuration.Console.MainForm.CreateNewApplication()
at Microsoft.Practices.EnterpriseLibrary.Configuration.Console.MainForm.OnNewAppMenuItemClick(Object sender, EventArgs e)
at System.Windows.Forms.MenuItem.OnClick(EventArgs e)
at System.Windows.Forms.MenuItem.MenuItemData.Execute()
at System.Windows.Forms.Command.Invoke()
at System.Windows.Forms.Command.DispatchID(Int32 id)
at System.Windows.Forms.Control.WmCommand(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.TreeView.WndProc(Message& m)
at Microsoft.Practices.EnterpriseLibrary.Configuration.Design.UI.CustomTreeView.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value HKLM\Software\Microsoft\Fusion!EnableLog (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value HKLM\Software\Microsoft\Fusion!EnableLog.
Oct 31, 2007 at 2:25 PM
What I understand from the error message is;
SQLConfiguration requires "Common" and "ConfigurationDesign" dll libraries.
When you compile SQLConfigurationSource, remove the references of these two dlls and add them from the folder where entLibConfigTool exe resides. Because after entlib 3 there are two versions of dlls and the default ones are that come with the binaries which are strong named (means version and key dependent). If you compile from the source, you cannot use the strong named assemblies. Actually you can but you also have to make the assembly you compiled a strong named assembly with the correct dlls.

What I do is; (the unsecure way)
- I install the source of entlib (probably somewhere like "C:\EntLib3Src"),
- then run "C:\EntLib3Src\App Blocks\BuildLibrary.bat"
which builds the whole library from scratch to somewhere like "C:\EntLib3Src\App Blocks\bin"
The dlls in this folder are different from the original (binary) ones.
- C:\EntLib3Src\App Blocks\bin folder contains all the dlls and executables you need and they are not strong named...

I just work on that folder for Design time
and copy the necesary dlls from this folder for runtime. Actulally, VS2005 does that automatically if I reference the files in "C:\EntLib3Src\App Blocks\bin"
Remember this way you can XCOPY anything...

Hope this helps...
Nov 1, 2007 at 12:51 PM
Hello again.

Thank you so much for your help. I have been able to get it working now, at least to as big an extent as needed right now.

I have a couple of questions left though:

- When I build according to your description, it seems like the assemblies generated are not generated in the same manner as the ones delivered as strong named assemblies with Enterprise Library May 2007? I think this is because the solution used for the build does not include all the application block projects actually available. For example, the SqlCe project is not built using this solution.

- The SqlConfigurationSource doesn't work! Or at least not until I use a patched version (from Alois Kraus). Something with SerializableConfigurationSection or something like that.

- The SqlConfigurationSource doesn't seem to support protection, i.e. encryption of the configuration sections. This is a big drawback since the connection string for the SqlConfigurationSource is stored in clear text... It is also a drawback since it won't let you protect any of the sections, meaning that all other connection strings under the Data Access Block are also left in clear text in the configuration. So, if someone gets hold of the *.config file they get the connection string for reading the configuration from Db, where they will then find the rest of the connection strings in clear text... Not good... Any ideas on how to correct this? I guess I can hack the SqlConfigurationSource and try to imitate the way that a FileConfigurationSource works...

Thanks again!

/Jörgen
Nov 2, 2007 at 12:23 PM
Hi Jörgen

- I believe "SqlCe" is part of a project called "Entlib Contrib" so it is better to build it from the source with the new updated references. I think there are minor differences between Entlib 2007, 3.0, 3.1.
If you want to use distributed project, always build from the source. That's what I do, but I know that it also has its own drawbacks.

I've never considered security on Configuration files before.

I modified the "SqlConfigurationSource" and added the computer property to some tables and procedures, that way I can modify from one source and distribute it to other computer instantly...
So you can modify "SqlConfigurationSource" the way you like it, eg. add encryption. One idea might be encrypting data on the database using database tools. But in "SqlConfigurationSource" you have to write the connection string clearly...
Apr 7, 2008 at 1:03 PM


jorgas wrote:
Hello again.

Thank you so much for your help. I have been able to get it working now, at least to as big an extent as needed right now.

I have a couple of questions left though:
...
- The SqlConfigurationSource doesn't work! Or at least not until I use a patched version (from Alois Kraus). Something with SerializableConfigurationSection or something like that.

/Jörgen


what is this patch about, couldn't find it. I run into that issue with SerializableConfigurationSection as well when adding a DAAB section. The contained connectionstrings are not of SerializableConfigurationSection. But since connection strings necessary in every DAAB it's unusable for now :/ Anyone got this solved?
May 3, 2008 at 10:31 AM
I Tried This:

What I understand from the error message is;
SQLConfiguration requires "Common" and "ConfigurationDesign" dll libraries.
When you compile SQLConfigurationSource, remove the references of these two dlls and add them from the folder where entLibConfigTool exe resides. Because after entlib 3 there are two versions of dlls and the default ones are that come with the binaries which are strong named (means version and key dependent). If you compile from the source, you cannot use the strong named assemblies. Actually you can but you also have to make the assembly you compiled a strong named assembly with the correct dlls.

What I do is; (the unsecure way)
- I install the source of entlib (probably somewhere like "C:\EntLib3Src"),
- then run "C:\EntLib3Src\App Blocks\BuildLibrary.bat"
which builds the whole library from scratch to somewhere like "C:\EntLib3Src\App Blocks\bin"
The dlls in this folder are different from the original (binary) ones.
- C:\EntLib3Src\App Blocks\bin folder contains all the dlls and executables you need and they are not strong named...

I just work on that folder for Design time
and copy the necesary dlls from this folder for runtime. Actulally, VS2005 does that automatically if I reference the files in "C:\EntLib3Src\App Blocks\bin"
Remember this way you can XCOPY anything...


------------------------------------------------------------------------

But when i open the entLibConfigTool i still don't have the option to pick SqlConfigurationSource.

Can someone please provide a simple example with the changed web.config and some C# code using the properties ???.

Thanks
May 6, 2008 at 1:48 AM
hello!

following the discussion list i am successfully able to get the SQLConfigurationSource appear in the entlib config tool.

when saving failes with SerializableConfigurationSection error .

so where do i get the Alois Kraus patch from . link on all the sites are broken.

Thanks
May 6, 2008 at 9:23 AM
Edited May 6, 2008 at 9:25 AM
After trying a litle bit more a finally put this SqlConfigurationSource to work. And i have to admit that is very easy, the worst part is the almost inexisting documentation about it out there!.

I will provide a zip file, that have a simple VS Solution/DataBase with source code, that basically store the settings in a SQL Server 2005 DB. Any doubt please contact me, i gladly will help if i know :).


Source Code --> http://members.netmadeira.com/lgouveia/SqlConfigurationSourceExample.rar.


Lisber Pontes
May 12, 2008 at 8:09 PM


Lisber wrote:
After trying a litle bit more a finally put this SqlConfigurationSource to work. And i have to admit that is very easy, the worst part is the almost inexisting documentation about it out there!.

I will provide a zip file, that have a simple VS Solution/DataBase with source code, that basically store the settings in a SQL Server 2005 DB. Any doubt please contact me, i gladly will help if i know :).


Source Code --> http://members.netmadeira.com/lgouveia/SqlConfigurationSourceExample.rar.


Lisber Pontes



the only changes I could find in your package are in the unit tests? Did u by chance something wrong in packaging your changes?
Jan 13, 2009 at 7:47 AM
dear all , ive managed to extend the sql configuration source so it can work in the oracle database and still available  in  the entlib config tools. However ive' noticed, when try to save the data access application block, the cconnection string throw and error, something like "connection string ... serializableconfigsection" . i search on goolgle and this kind of behaviour also shown in the existing sql cnfiguration source.
my question is , how can i modifiy the entlib so it can store the connection string from DAAB.  I've noticed the ConnectionStringSection is not based on serializableconfigurationsection, but namedconfiguration..blabla.
Please,  is there any simple work araound. I've looked at Alois Kraus Solution, but i need more direction so i only need to change the entlib if necessary.
Jan 13, 2009 at 7:54 AM
Also store that connectionstring in the original *.config file.

Or another solution would be to get the connection string as a property and not use it with entlib but use it with ADO.NEt directly, this way you can by pass entlib.

I've had the similiar issue. That is not problem of Oracle, that is problem of design of EntLib. Actually that is not a problem but a flaw.

Mert



From: [email removed]
To: [email removed]
Date: Tue, 13 Jan 2009 00:47:47 -0800
Subject: Re: SqlConfigurationSource [entlib:17125]


From: iwanarif
dear all , ive managed to extend the sql configuration source so it can work in the oracle database and still available in the entlib config tools. However ive' noticed, when try to save the data access application block, the cconnection string throw and error, something like "connection string ... serializableconfigsection" . i search on goolgle and this kind of behaviour also shown in the existing sql cnfiguration source.
my question is , how can i modifiy the entlib so it can store the connection string from DAAB. I've noticed the ConnectionStringSection is not based on serializableconfigurationsection, but namedconfiguration..blabla.
Please, is there any simple work araound. I've looked at Alois Kraus Solution, but i need more direction so i only need to change the entlib if necessary.
Read the full discussion online.
To add a post to this discussion, reply to this email (entlib@discussions.codeplex.com)
To start a new discussion for this project, email entlib@discussions.codeplex.com
You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on codePlex.com.
Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at codeplex.com


Windows Live™: Keep your life in sync. Check it out.
Jan 13, 2009 at 10:34 AM
thank you for your response mertsakarya. adding connection string in conventional web.config or app.config would be nice solution. the draw back using this approach is that I still cannot get the connection string in the enterprise library module . let say i add logging application block (in the sql configuration source) , and add database trace listener into it. the config tools automatically add the data application block and when you try to save it , it still give the same problem. i still can remove the DAAB but when i try to direct the databaseinstance in the databasetrace listener to the connection string inside di web.config , I canot edit the field ( it says (NONE) and not editable).  It would be nice to embrace all the feature in the enterprise library into single manageable configuration source (in DB) . Hope you can help me.
Jan 14, 2009 at 10:06 PM
Edited Jan 14, 2009 at 11:46 PM
Iwan,

You were right in pointing out that the ConnectionStringSection is not SerializableConfigurationSection based. As Alois Kraus indicated in his post, this is the main reason that the SqlConfigurationSource provider sample code released as a part of entLib3.1 would not work with DAAB. So now it's time to exercise the ultimate strength of the Enterprise Library - modify its code and rebuild!

To solve the problem you're facing try following steps

In SqlConfigurationSystem.cs

Add using System.Reflection;

then find the following code block:

            SerializableConfigurationSection configSection = (SerializableConfigurationSection)Activator.CreateInstance(Type.GetType(configSectionType));
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.CloseInput = true;
            using (System.IO.StringReader stringReader = new System.IO.StringReader(xmlData))
            {
                using (XmlReader reader = XmlReader.Create(stringReader, settings))
                {
                    configSection.ReadXml(reader);
                    reader.Close();
                }
                stringReader.Close();
            }


and change it to:

            ConfigurationSection configSection = (ConfigurationSection)Activator.CreateInstance(Type.GetType(configSectionType));
            XmlReaderSettings settings = new XmlReaderSettings();
            settings.CloseInput = true;
            using (System.IO.StringReader stringReader = new System.IO.StringReader(xmlData))
            {
                using (XmlReader reader = XmlReader.Create(stringReader, settings))
                {
                    if (configSection is SerializableConfigurationSection)
                    {
                        ((SerializableConfigurationSection)configSection).ReadXml(reader);
                    }
                    else
                    {
                        reader.Read();
                        configSection.GetType().GetMethod("DeserializeSection", BindingFlags.NonPublic | BindingFlags.Instance).Invoke(configSection, new object[] { reader });
                    }           
                    reader.Close();
                }
                stringReader.Close();
            }

   
then change the signature of  SqlConfigurationSystem.SaveSection()

from:  public void SaveSection(string sectionName, SerializableConfigurationSection configurationSection)
to:     public void SaveSection(string sectionName, ConfigurationSection configurationSection)

inside the SaveSection() method, change following code block:

                   using (XmlWriter writer = XmlWriter.Create(output, settings))
                    {
                        configurationSection.WriteXml(writer);
                        writer.Close();
                        writer.Flush();
                    }

to:
                    using (XmlWriter writer = XmlWriter.Create(output, settings))
                    {
                        if (configurationSection is SerializableConfigurationSection)
                        {
                            ((SerializableConfigurationSection)configurationSection).WriteXml(writer);
                        }
                        else
                        {
                            string _data = (string)configurationSection.GetType().GetMethod("SerializeSection", BindingFlags.NonPublic
                                                                                                                                                                        | BindingFlags.Instance).Invoke(configurationSection, new object[]
                                                            { configurationSection, "SerializableConfigurationSection", ConfigurationSaveMode.Full });
                            writer.WriteRaw(_data);
                        }
                        writer.Close();
                        writer.Flush();
                    }


In SqlConfigurationSourceImplementation.cs


change the signature of SqlConfigurationSourceImplementation.SaveSection()

from: 
public void SaveSection(string sectionName, SerializableConfigurationSection section)
to:    public void SaveSection(string sectionName, ConfigurationSection section)

In SqlConfiguratioinSource.cs

change the signature of SqlConfigurationSource.Save()

from: public void Save(string connectionString, string setStoredProcedure, string section, SerializableConfigurationSection configurationSection)
to: public void Save(string connectionString, string setStoredProcedure, string section, ConfigurationSection configurationSection)

and, inside the Add() method remove the following type checking code

if (!(configurationSection is SerializableConfigurationSection))
                throw new ArgumentException(string.Format(Resources.Culture, Resources.ExceptionUnexpectedType,
                                                                    typeof(SerializableConfigurationSection).Name), "configurationSection");


and change its call to the save() method

from:

            SerializableConfigurationSection serializableSection =
                configurationSection as SerializableConfigurationSection;
            Save(parameter.ConnectionString, parameter.SetStoredProcedure, sectionName, serializableSection);

to just:
            Save(parameter.ConnectionString, parameter.SetStoredProcedure, sectionName, configurationSection);

obviously, you may also need to remove following type checking from the ValidateArgumentsAndConnectionInfo() method

            if (!(configurationSection is SerializableConfigurationSection))
                throw new ArgumentException(Resources.ExceptionConfigurationSectionNotSerializable, "configurationSection");


In SqlConfigurationManager.cs

change the signature of SqlConfigurationManager.SaveSection()
from: public static void SaveSection(string sectionName, SerializableConfigurationSection section, SqlConfigurationData data)
to: public static void SaveSection(string sectionName, ConfigurationSection section, SqlConfigurationData data)

As you can see, all of these changes are rather straightforward, the code should be self-explanatory too. Hope this may help to remove the limitation you're facing.



Jan 15, 2009 at 4:15 AM
Dear Larry. YOUR'E THE BEST !!!!
after following your direction im able to store the DAAB in SQL/Oracle configuration Source. Im agree that your explananation is very clear and straight forward to.
I'm sure that this direction will be helpfull to many people that facing the same problem as i am. Thank you again.
Jun 30, 2009 at 6:51 AM

Great!Thanks