Generating Enums out of Database Lookup Tables

With the help of this great article, I succeeded in generating c# enumerations automatically, out of lookup key/values from a database.

 Code: (for a template include)

<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ output extension=".cs" #>

<#+

    public static Dictionary<string, Dictionary<int, string>> GetSettings()
    {
        Dictionary<string, Dictionary<int, string>> settings = new Dictionary<string, Dictionary<int, string>>();

        try   
        {
            using (SqlConnection sqlServerConnection = new SqlConnection("my Connection String"))
            {
                sqlServerConnection.Open();

                SqlCommand sqlCommand = new SqlCommand("SELECT Group, Code, Description FROM Lookups WHERE Code = -1", sqlServerConnection);

                SqlDataReader sqlReader = sqlCommand.ExecuteReader();

                while(sqlReader.Read())
                {
                    settings.Add((sqlReader.GetString(2) ?? string.Empty).Trim(), new Dictionary<int, string>());
                }

                sqlCommand = new SqlCommand("SELECT Group, Code, Description FROM Lookups WHERE Code != -1", sqlServerConnection);

                sqlReader = sqlCommand.ExecuteReader();

                while(sqlReader.Read())
                {
                    foreach(var setting in settings)
                    {
                        setting.Value.Add(sqlReader.GetInt32(1), (sqlReader.GetString(2) ?? string.Empty).Trim());
                    }
                }

                sqlServerConnection.Close();
            }
        }

        catch (Exception)
        {
        }

        return settings;
    }
 #>

Code for the tt template:

<#@ include file="DbHelper.ttinclude" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>

namespace Lab.Generate_Enums_Dynamically

{
     <#   
        var settings = GetSettings();

        foreach (var settingGroup in settings)
        {

     #>
        public enum <#= settingGroup.Key.Replace(" ", "_") #>
        {
            <#
                List<string> enumEntries = new List<string>();
                foreach(var setting in settingGroup.Value)
                {
                    enumEntries.Add(string.Format("{0} = {1}", setting.Value.ToString().Replace(" ", "_"), setting.Key));

                }

            #>
            <#= string.Join(",\n", enumEntries) #>
        }
    <# } #>
}

Displaying Non-ASCII Characters Via Windows Command Prompt Window

I stumbled across this problem several times, but never took the time to dig in to finally find the solution. For instance, in writing a .NET Console application, if you google for it, you will find that most of the posts mention setting System.Console.OutputEncoding accordingly. But this was not making it work for me. I have figured out the problem, and the answer is developed in the following stackoverflow post.

Getting a UserPrincipal object

I was trying relentlessly to find a way to get a UserPrincipal out of a WindowsIdentity object - because this was the only identity object I had in my application, and I was sure that whatever other identity data I would need should come from it.

Well, first of all I found out that you can create a WindowsPrincipal out of a WindowsIdentity object, just using one of WindowsPrincipal's constructors.

But most importantly, I did succeed in obtaining a UserPrincipal - but with no relation whatsoever to my Identity object.

Let me just single out the most common reason why you would be interested in a UserPrincipal object - to get a user name, out of its user string.

So, back to getting a UserPrincipal, the only pre-requisite is to have the related Domain (the Active Directory domain the user is related to) at hand. Once you have it, then all you need is:

PrincipalContext ctx = new PrincipalContext(ContextType.Domain, _domain);
UserPrincipal user = UserPrincipal.FindByIdentity(ctx, Usf);

.NET Web Services Deserialization: Issues and Workarounds

I ran into the following problem:

A 3rd-party web service was given to me to be added into one .NET project I am working with.
Upon doing so, the reference.cs that is generated after adding a Service Reference into a Visual Studio project - was empty. In other words - no code was rendered that "translated" the web service definition language (wsdl) into .NET (in my case, C#) understandable code. No types to instance to make use of the service.

In a short, I researched for the topic, and found out that the Add Service Reference into a Visual Studio project action will, behind the scenes, try to deserialize the wsdl xml into C# code. In case there are problems and the process fails somehow (and a good way to know that is running the framework command tool SvcUtil.exe), a solution might be trying the deserialization in a different way.

As far as I understood (all what I am writing here is from googling, I did not check things for myself on code), the default .NET wsdl deserialization is used via the DataContractSerializer. There is a way, however, to use a different serializer the framework also offers - XmlSerializer.

To manually deserialize the wsdl into a .NET class, you can use another framework command tool: wsdl.exe.

This is what worked for me - the .cs file generated in this step was ok (not empty). I added it to my project tree and instanced the type, and was finally using the web service.

Interesting stuff on svcutil.exe can be found here.
Googling for wsdl.exe and svcutil.exe is fundamental.
For more on their difference, click here.

Sql Server like STRAGG functionality

Making a tiny modification to this answer, you have STRAGG functionality in SqlServer:

CREATE TABLE yourtable
([FieldA] int, [FieldB] varchar(1))
;

INSERT INTO yourtable
([FieldA], [FieldB])
VALUES
(1, 'A'),
(1, 'B'),
(2, 'A')
;

select distinct t1.FieldA,
  STUFF((SELECT distinct ',' + t2.FieldB
         from yourtable t2
         where t1.FieldA = t2.FieldA
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'') data
from yourtable t1;

As usual, however, this ended up making me raise another question, still waiting for an answer :-)

Customizing Visual Studio's Find Results

This link explains how.