Split SQL 2005 column values into multiple rows

Suppose that you have a column that stored multiple values in a single row using some delimiter as ‘,’. Now you are tasked with splitting these values into a separate row for each value and you need to do this on multiple rows in the table.

   1: select distinct r.items

   2: from Order o

   3: cross apply dbo.split(o.Products,',') r

Here the Order Table has a Products column that has multiple Products stored in a single column separated with commas like ‘Tea,Coffee’ associated with a single Order. What the above query does is it gives a distinct list of names of all Products stored in all rows in the Order table. The Split() is a UDF which splits the string into one row for each value

Similarly if you need to check if these multiple values stored in the Products column contain a particular string from a list of values, i,e,

   1: select o.* from Company c

   2: join Order o on c.ItemID = o.companyItemID

   3: where (select * from dbo.split(o.Products) in ('TBD','Tea', 'Coffee'))

Since the above would not compile hence the right solution to do something like this would be:

   1: select o.* from Company c

   2: join Order o on c.ItemID = o.companyItemID

   3: cross apply dbo.Split(Products,',') p 

   4: where p.Items in ('TBD','Tea','Coffee')

what we are doing here is a inner join on the Split() table valued UDF which returns a table containing one row for each value split and then comparing each Item to see if it matches one of the product names i.e. Tea or Coffee.

Dispose and Finalize methods and .NET Memory optimizations

I have jotted down a few points on implementing the IDisposable and Finalize patterns in .NET.

Please find it here : Dispose and Finalize methods

I also jotted down some .net Memory Optimization tips: Please find them here : Some Tips on .NET Memory optimizations

Creating dynamically generated logfiles with log4net

If you need to generate logfile names dynmically when using log4net then we can do this using the log4net Properties.

To learn more on this please visit : creating dynamic logfile names with log4net

Tips on using log4net RollingFileAppender

I have compiled a list of useful properties that you can set on the RollingFileAppender in the log4net XML config file which can determine the order in which log file backups will be generated, whether they will rollover based on dates or size or both.

For more information please check this link out : Tips on using log4net RollingFileAppender

ConfigSectionHandler for Hierarchical configs

Here is a quick way to create a ConfigSection Handler for reading Hierarchical configs. For e.g. suppose you need to read a config file which has the following structure:

MySpace
 
  23
   Rohit
  Gupta
  

For this you would create the 2 classes, one for the Parent Config and another for the chold config like this:

  [XmlRoot("MainConfig")]
   public class MainConfig
   {
       private static readonly MainConfig instance =
 (MainConfig)ConfigurationManager.GetSection("MainConfig");
       public static MainConfig GetInstance()
       {
           if (instance == null)
               throw new ConfigurationErrorsException(
                   "Unable to locate or deserialize the 'MainConfig' section.");

           return instance;
       }

       public string Company { get; set; }

       public SubConfig FriendsConfig { get; set; }
   }

   [XmlRoot("SubConfig")]
   public class SubConfig
   {
       public int ID { get; set; }
       public string Name { get; set; }
       public string LastName { get; set; }
   }

Then you would create a configsectionHandler class which reads the config from the .config file:

public class MainConfigSectionHandler : IConfigurationSectionHandler
   {
       #region IConfigurationSectionHandler Members

       public object Create(object parent, object configContext, System.Xml.XmlNode section)
       {
           MainConfig typedConfig = GetConfig(section);

           if (typedConfig != null)
           {
               #region Get Sub Configs
               foreach (XmlNode node in section.ChildNodes)
               {
                   switch (node.Name)
                   {
                       case "SubConfig":
                           SubConfig friendsConfig = GetConfig(node);
                           typedConfig.FriendsConfig = friendsConfig;
                           break;
                       default:
                           break;
                   }
               }
               #endregion
           }


           return typedConfig;
       }

       public T GetConfig(System.Xml.XmlNode section) where T : class
       {
           T sourcedObject = default(T);
           Type t = typeof(T);
           XmlSerializer ser = new XmlSerializer(typeof(T));
           sourcedObject = ser.Deserialize(new XmlNodeReader(section)) as T;
           return sourcedObject;
       }

       #endregion

   }

After this you would add a entry in the app.config for this configsection Handler as the following:

Finally to read this config, you would write the following:

MainConfig config = MainConfig.GetInstance(); Console.WriteLine(config.Company);
Console.WriteLine(config.FriendsConfig.ID);
Console.WriteLine(config.FriendsConfig.LastName);
Console.WriteLine(config.FriendsConfig.Name);

Lucene: Multifield searches

We can run multifield searches in Lucene using either the BooleanQuery API or using the MultiFieldQueryParser for parsing the query text. For e.g. If a index has 2 fields FirstName and LastName and if you need to search for "John" in the FirstName field and "Travis" in the LastName field one can use a Boolean Query as such:

BooleanQuery bq = new BooleanQuery();
Query qf = new TermQuery(new Lucene.Net.Index.Term("FirstName", "John"));
Query ql = new TermQuery(new Lucene.Net.Index.Term("LastName", "Travis"));
bq.Add(qf, BooleanClause.Occur.MUST);
bq.Add(ql, BooleanClause.Occur.MUST);
IndexSearcher srchr = new IndexSearcher(@"C:\indexDir");
srchr.Search(bq);

Now if we need to search a single term across either of the FirstName and LastName fields then we can use the MultiFieldQueryParser as follows:

Query query = MultiFieldQueryParser.parse("commonName",
new String[] { "FirstName", "LastName" },
new SimpleAnalyzer());
srchr.Search(query);

Now if you need to search the term that must exist in both the fields then we use the following:

 Query query = MultiFieldQueryParser.Parse("commonName",
new String[] { "FirstName", "LastName" },
new BooleanClause.Occur[] { BooleanClause.Occur.MUST,BooleanClause.Occur.MUST}
, new SimpleAnalyzer());
srchr.Search(query);

Finally if you don’t want a term to occur in one of the Fields (say FirstName) then use:

 Query query = MultiFieldQueryParser.Parse("commonName",
new String[] { "FirstName", "LastName" },
new BooleanClause.Occur[] { BooleanClause.Occur.MUST_NOT,BooleanClause.Occur.MUST},
new SimpleAnalyzer());
srchr.Search(query);  

so if you need to search a single term across multiple fields then use MultiFieldQueryParser, if you need to search different terms in different fields then use the BooleanQuery as shown first