.NET C# to parse MySQL queries? Impossible? Pavel can make it ;) (with some ifs and buts offcourse ;)

The most interesting task in my career came the end of the last week. As a Vizibility employee I was asked if I can port osCommerce live site to out vShop solution (http://www.vizibility.co.uk/tmenu/vshop.asp) The interesting thing is that I had no access to osCommerce nor did I ever worked with it. What's even more chalenging - osCommerce database was given to me as SQL queries, something like : drop table if exists table ; create table table ( -- Some things here ... ); insert into table () values ( ); As a normal software developer so there were 3 big chalanges: 1. How to parse this SQL thing (it is MySQL) ? 2. How to figure out

the osCommerce Logic ?
3. How to port all those things as a script so it can be applied to our live servers by the support team?


It's really chalanging don't you think? First I thought it will be a good step to install fresh copy of osCommerce, import the products and try to port them as CSV or something. But then I understood it will take too much time, so I decided to parse the SQL queries on my own and store them in a predefined objects like tables, rows and so on and so on. Then I could easilly transfer them as a text files in the format we needed.

And I almost made it - the parsing methods are done (I was able to parse this thing in a day but some bugs still appear).

Seems the more I work on our shopping solution the more easier I can understand other's shopping solutions.

Great task in a great company!

5 minutes C# code to open Web.Config from Windows Forms application.

In my previous post about IIS, Virtual Directories and Directory Services you saw how can you obtain the sites per IIS instance as well sa all the root virtual directories. Now it is time to show you how can you obtain the Web.Config file for a root directory. At first glance I thought it will be easy, I thought I will simply need declare the following: using System.Web.Configuration; And / or : using System.Configuration; Well it's not quite this way. In order to load web.config file from a windows forms application you need to have some kind of context to be used or, in other words, file mapping. The following method takes two parameters : path to the root directory and DirectoryEntry, representing the root directory

object (you can see my previous post ( Get IIS sites and virtual directories with .NET / C# / Directory Services ) learn how to obtain DirectoryEntry for a web root directory.
And here is the method:

       List < string []> GetWebConfigAppSettings( string Path, DirectoryEntry entry)
       {
             List < string []> items = new List < string []>();
             if (entry.Properties[ "Path" ].Value != null )
            {
                FileInfo fiWebConfig = new FileInfo (entry.Properties[ "Path" ].Value + @"\web.config" );
                if (fiWebConfig.Exists)
                {
                   WebConfigurationFileMap fm = new WebConfigurationFileMap ();
                   System.Collections. Hashtable props = new System.Collections. Hashtable ();
                   foreach (System.DirectoryServices. PropertyValueCollection property in entry.Properties)
                   {
                         string propertyName = property.PropertyName;
                        props.Add(propertyName, property.Value);
                   }
                   VirtualDirectoryMapping mapping = new VirtualDirectoryMapping (fiWebConfig.Directory.FullName, true );
                   fm.VirtualDirectories.Add(entry.Properties[ "AppRoot" ].Value.ToString(), mapping);
                   System.Configuration. Configuration cfg = System.Web.Configuration. WebConfigurationManager .OpenMappedWebConfiguration(fm, entry.Properties[ "AppRoot" ].Value.ToString());
                   foreach ( KeyValueConfigurationElement elmt in cfg.AppSettings.Settings)
                   {
                        items.Add( new string [] { elmt.Key, elmt.Value });
                   }
                }
            }
             return items;
       }



The method above will return a List of all in the web.config file (the appSettings section), you can easilly adjust it to fit your needs.

As you can see the magic begins in :

> System.Configuration. Configuration cfg = System.Web.Configuration. WebConfigurationManager .OpenMappedWebConfiguration(fm, entry.Properties[ "AppRoot" ].Value.ToString());


method. You simply need to make some preparations befor ask it to open the web.config.

Note : this code again is written in few minutes and does not pretend to be optimized, you can again use it at your own will.