Tag Archive | visual studio

Automatically Generate POCOs From DB With T4

The T4 template engine is insanely powerful. I didn’t really realize just how powerful it was until I had a use case for it today. I stood up a database with about 40 tables in it, and planned to use an ORM to access the database. To use the ORM, I needed POCOs (Plain Old C# Objects) that represented my database. Some of these tables had 30-50 or so columns and I didn’t want to code all of this by hand – it would take literally days.

Surprisingly, I got the whole thing done in about an hour with the help of the T4 template engine.

For those who are not familiar, T4 is a text template engine created by Microsoft which combines plain text and control logic to generate text output. In reality it’s a lot like how you use Razor or WebForms view engines to generate HTML; you can embed “code nuggets” almost exactly like you do in WebForms. The only real difference is that with T4 you’re creating a text file instead of a webpage.

To create a T4 template in Visual Studio 2010 or 2012, simply add a text file to your project… I called mine PocoGenerator.txt for example. Then, rename the file’s extension from “.txt” to “.tt” – the file will then be treated as a T4 Template by Visual Studio. Your output will appear in the code-behind .cs file attached to the .tt file you just created. In my scenario I wanted each of my POCOs to have their own file, so I did a bit of trickery to make that happen.

I wrote this nifty T4 template which connects to a database, queries all tables (ignoring sys tables), and then creates one POCO per table under a file named <tablename>.cs which is placed in a directory relative to the template’s location. Give this a go – you won’t be disappointed! And of course, if you need it to do more than I do, just modify it and make it your own!

<#@ template language="C#" hostspecific="true" debug="True" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.Xml" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Management.Sdk.Sfc" #>
<#@ import namespace="System" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#
    //**********************************************************************************************
    // This T4 generates POCOs from the specified DB and saves them to the specified folder which 
    // is relative to the template's location. One file per table/POCO.
    //**********************************************************************************************

    //****************************
    // DEFINE YOUR VARIABLES HERE
    //****************************
    // The SQL server name or IP
    string sqlServer = "9.9.9.9";
    // The SQL username
    string sqlLogin = "admin";
    // The SQL password
    string sqlPassword = "password";
    // The SQL database to generate the POCOs for
    string sqlDatabase = "MyDatabase";
    // The namespace to apply to the generated classes
    string classNamespace = "Your.Namespace.Here";
    // The destination folder for the generated classes, relative to this file's location.
    string destinationFolder = "PocoFolder";

    // Loop over each table and create a class file!
    Server server = new Server(sqlServer);
    server.ConnectionContext.LoginSecure = false;
    server.ConnectionContext.Login = sqlLogin;
    server.ConnectionContext.Password = sqlPassword;
    server.ConnectionContext.Connect();

    foreach (Table table in server.Databases[sqlDatabase].Tables)
    {
        // Skip sys tables
        if (table.Name.StartsWith("sys"))
        {
            continue;
        }
#>
using System;

namespace <#= classNamespace #>
{
    /// <summary>
    /// Represents a <#= table.Name #>.
    /// NOTE: This class is generated from a T4 template - you should not modify it manually.
    /// </summary>
    public class <#= table.Name #> 
    {
<# 
        // Keep count so we don't whitespace the last property/column
        int columnCount = table.Columns.Count;
        int i = 0;

        // Iterate all columns
        foreach (Column col in table.Columns)
        {
            i++;
            string propertyType = GetNetDataType(col.DataType.Name);

            // If we can't map it, skip it
            if (string.IsNullOrWhiteSpace(propertyType))
            {
                // Skip
                continue;
            }

            // Handle nullable columns by making the type nullable
            if (col.Nullable && propertyType != "string")
            {
                propertyType += "?";
            }
#>
        public <#= propertyType #> <#= col.Name #> { get; set; }
<#
            // Do we insert the space?
            if (i != columnCount)
            {
#>

<#
            }
#>
<#
        }
#>
    }
}      
<#
        // Write new POCO class to its own file
        SaveOutput(table.Name + ".cs", destinationFolder);
    } 
#>
<#+
    public static string GetNetDataType(string sqlDataTypeName)
    {
        switch (sqlDataTypeName.ToLower())
        {
            case "bigint":
                return "Int64";
            case "binary":
            case "image":
            case "varbinary":
                return "byte[]";
            case "bit":
                return "bool";
            case "char":
                return "char";
            case "datetime":
            case "smalldatetime":
                return "DateTime";
            case "decimal":
            case "money":
            case "numeric":
                return "decimal";
            case "float":
                return "double";
            case "int":
                return "int";
            case "nchar":
            case "nvarchar":
            case "text":
            case "varchar":
            case "xml":
                return "string";
            case "real":
                return "single";
            case "smallint":
                return "Int16";
            case "tinyint":
                return "byte";
            case "uniqueidentifier":
                return "Guid";
                
            default:
                return null;
        }
    }

    void SaveOutput(string outputFileName, string destinationFolder)
    {
        // Write to destination folder
        string templateDirectory = Path.Combine(Path.GetDirectoryName(Host.TemplateFile), destinationFolder);
        string outputFilePath = Path.Combine(templateDirectory, outputFileName);
        File.Delete(outputFilePath);
        File.WriteAllText(outputFilePath, this.GenerationEnvironment.ToString()); 

        // Flush generation
        this.GenerationEnvironment.Remove(0, this.GenerationEnvironment.Length);
    }
#>

Note that when the files are generated, they will not automatically be included in the project. You will have to add them manually as existing items.

This T4 Template supports regeneration, so anytime you update your database schema just re-run the template to create updated .cs files! Enjoy!

Visual Studio 2012 Intellisense Not Working – SOLVED

So, this post is about our beloved IDE instead of actual code.

I recently upgraded my home PC from Visual Studio 2010 and 11 Beta to Visual Studio 2012. The very first thing I noticed was that after about 10 minutes of programming my Intellisense quit working and never came back. I thought to myself “what the hell Visual Studio? 2010 didn’t have these problems?!” and then, after a swig of beer, proceeded to exercise my Google-Fu to solve this issue.

Strangely, I did not find the “correct” aka permanent solution to this problem. So, after a ton of screwing around and guess-and-check, here’s how I solved it. Note that for this fix to work, you have to abandon Visual Studio 2010 or 11 PERMANENTLY. You don’t have to uninstall either of them, but if you open a solution in them it seems you’ll re-break Visual Studio 2012. Stupid, I know. Maybe a hotfix will come out eventually to fix this issue. Anyway…

If your Intellisense is anything but intelligent, and has stopped working, do the following:

1. Open the start menu and type “%AppData%” and press enter to get to your Application Data Folder.

2. Either you were automatically placed in the “Roaming” folder or you weren’t. If you weren’t, go to the “Roaming” folder.

3. Open the “Microsoft” folder.

4. Open the “VisualStudio” folder.

5. Here you’ll see a folder titled “11.0” (the VS 2012 folder) and probably also “10.0” (the VS 2010 folder).

6. DELETE (or rename) the “10.0” folder. Note that you can now kiss your Visual Studio 2010 settings and preferences goodbye (your projects will be safe and sound).

7. DELETE (or rename) all other folders that are not the “11.0” folder, assuming you used to have Visual Studio 2008 or whatever.

Now restart Visual Studio 2012 and you should be good to go!

Make Your Debugging Life Easier

Sorry for the delay in posts, May has been a very busy month.

In order to accurately debug or profile an external assembly or library (AKA one you’re not directly compiling), you need the associated PDB files to accompany each of the DLLs. These files give the debugger some information about the compiled assembly so that your debugger or profiler can become aware of function names, line numbers, and other related meta data.

One thing that sucks is debugging and profiling native Microsoft .NET assemblies. When debugging an exception where you have no line number, or performance profiling an application and not knowing what method is being referred to, it’s easy to become very frustrated, very quickly. The latter scenario happened to me just this week. I was performance profiling my application at work and found that a large portion of the application’s time (~43%) was spent in a method called “clr.dll” (displayed here as memory addresses):

Performance Profiling - No Symbols

Performance Profiling - No Symbols

This was not exactly a useful indication of what REALLY happened. What am I supposed to do with the knowledge that over 40% of my application’s time is spent in a Microsoft assembly called “clr.dll”? Not much, which is a little concerning. I needed to know what was really happening!

Fortunately, there’s a solution for this very issue. A little known feature implemented in Visual Studio 2010 is the ability to connect to Microsoft’s Symbol Servers and obtain most of the debugging symbols for their assemblies and libraries!

Just go to Tools –> Options –> (expand) Debugging –> Symbols

Here, select/check the “Microsoft Symbol Servers” as a source for Symbols. Now, getting the symbols from Microsoft every time you debug or profile is going to be slow and painful (and it’ll even give you a pop-up saying as much once you check the Microsoft Symbol Servers), so be sure that you specify a directory for the “Cache symbols in this directory” input – it will keep a local copy of the PDBs and simply check for updates every so often. As a result, you get your regular debugging/profiling AND you can see the function names of the Microsoft assemblies!

Using this feature, I was able to re-evaluate my latest performance tests and see that the supposed “clr.dll” method was actually “TransparentProxyStub_CrossContext” – a method buried deep within the WCF framework:

Performance Profiling - Symbols

Performance Profiling - Symbols

A little Google-Fu and a discussion with a co-worker who is well-versed in WCF told me that my application was actually spending its time waiting for a reply from a WCF request. Since this was expected behaviour (the application calls out to a service for every request), it put my performance profiling mind at ease.

Take advantage of Microsoft’s PDBs, especially when the price is right – free. You’d be amazed how useful they are in your day-to-day debugging and profiling.