Saturday, December 31, 2011

Example of Repetitive Comments

Looking through some old code for a project I’m working on I saw a block of code similar to this:

/// <summary>
///
This is MY method
/// </summary>
/// <param name="intValue">
Some Number</param>
/// <param name="stringValue">
Some String</param>
/// <returns>
An empty string</returns>
public string MyMethod( // Returns An empty string
int intValue, // Some Number
string stringValue) // Some String
{
return string.Empty;
}
This code annoys me. Yet I see the problem: the XML comments are just a little too far away from the actual data. I think it would be cool to have tool tips for the parameters described in XML comments. It would have also helped name the parameters better.

Friday, December 16, 2011

Two devices to read a tech book

Last week I bought some technical books from the internet; they were stolen from my front door and I needed them immediately, so I ordered the e-book version. I like e-readers for pure text that is meant to be read in order (novels, histories, etc.), but technical books have diagrams, tables and code samples (“figures”) that may be referenced several pages later. So I decided to try using two devices …

two_dev_code

I use the reader for the text and the tablet for the figures. I don’t like to read lots of text on a tablet because it is backlit. I really like reading plain text on a reader; I bought mine this spring and read the Lord of the Rings (+ the Hobbit) on it. I like reading on it better than reading books.

Since the reader doesn’t support zoom, I like to use the tablet to zoom the figures; actually, the image support on the reader is so bad that it would be difficult to read my current book without viewing images on the tablet.

This is not a supported way to read an e-book and will probably never be. Even with the hassle of juggling two devices it is better than the supported way. It would be nice if I could move between figures without shuffling through the text. Some UX designer better than me needs to do something to improve the tech book reading experience.

Thursday, November 17, 2011

My Play

In this week’s This Developers Life, they discussed Play. Not to be out done, I thought I would write about my own play patterns. It was fun to hear about the race car driver and the home manufacturer, I want to hear more. To promote dialog, I am presenting my story.

Improv

Almost 2 years ago, I started taking Improv classes at the Blue Door Theatre in Spokane because I heard about some consultancy in New York City that provided Improv classes to all their consultants. The local NPR station announced the class on their arts calendar and I just said what the F# and I took the class. At that point I had never even seen live Improv.

Like many programmers, I have an Aspergers personality. This serves me well when I am actually doing my core job; for the rest of my life, not so much. Improv gives me a chance to play at being in the world where everyone else lives. It gives me the opportunity to playing at being an extrovert.

Through something we call “Yes, And”, I am learning to accepting the current situation and adapting to the current situation. And I am learning to do it quickly. As a programmer, I like to work carefully and look for the perfect solution; in Improv I just don’t have the time. (The programmer in me is telling me that this blog post isn’t good enough; if it wins, no one else will ever read this.)

Am I taking Improv to become a great actor and become famous? No. My chances of ever performing Improv in public: about 50/50 (based on the fact that most of us underestimate our own talents). Do I really care? Do you take Kung Fu to fight? If memory serves me right, Cane was able to avoid fighting as much as he fought. I have presented at Code Camps 5 times since I started Improv.

Technology

My work project is frozen to the technology that existed when it was started. I like to play with the new stuff. I have Windows 8 installed on my laptop. I am slowing working my way through WPF 4 Unleashed. I am looking into writing for the Android Tablet. No, this is an extension of work.

Music

I have a guitar, a bass and a keyboard that I attempt to use to make sounds with. In the past I found that the guitar to be a great stress reliever. On second thought I haven’t really even touched these things since I started Improv. Since classes don’t start up again until next year, I would take out an instrument and …

Christine

I have a 1982 Supra that I have tried to keep alive for the last dozen or so years. Due to some poor financial decisions on my part, I haven’t done much with her for the past couple of years. My dream is to restore her on YouTube. Yes, her name is a reference to the Stephen King book.

Wednesday, November 09, 2011

Tablets and Open Source Voting Software

Yesterday on NPR, they had a short piece on iPad Voting. I don't like modern voting machines because of the closed nature of their software; very few people know what is going on inside those things.

If I had my way, the voting software would be written as an open source project. The voting machines would be common hardware that could be used in schools after election season. On Election Day morning, representatives from all interested parties would download the source from the project's site, build, run unit tests, install the software and generally verify that the software is correct.

The hardware would probably be tablet computers. Right now the market would be iPad, Android and, coming soon, Windows Metro. I would imagine that each vendor would sponsor voting machine projects that use their hardware. When the local community center would buy tablet computers of some after school program, the quality of its open source voting software may affect the buying decision. You wouldn’t need to buy new hardware every election cycle, schools could go without the iPad for the first week of November (“Hey kids, this is a ‘yellow pad’, we used these in the 20th century”).

Monday, October 31, 2011

Windows 8 and Fear of Change

For Halloween, I will discuss Windows 8 and Metro. I hear lots of fear and loathing in regard to Microsoft’s strategy regarding Windows 8 and the tablet.

I’ve heard horror stories about how we will be forced to throw away all we’ve learned the last 10 years and learn to write apps in HTML5 and JavaScript. Tales that Silverlight is doomed to the bone yard and so on … In short, the technology world changing and all our skills will be null and void. In our next job, we will need to rehearse the phrase: “Do you want fries with that?”

Come on, part of being a developer is dealing with change and uncertainty. In the mid 1990s, we went from DOS and character based computers to the GUI Land of Windows 95. In the early 2000s it was .NET for everyone in Windows Land. And now there are rumors of doom about what Microsoft is up to with Windows 8.

Part of the bargain of working in this business is change; we get to work on the cool new things and we have to work on the cool new things. We spend more time keeping up, learning things we need to be up to date. By choosing what to learn, we are also placing bets on winners and losers. If I study Window 8, I am betting for Microsoft and against Google and Apple. Of course, I could hedge my bet by studying both Window 8 and Android (that would reduce my potential reward.

Windows 8 etc.

Windows 8 (WinRT, Metro, etc.) represents to response to iOS and Android tablets. I don’t know if Windows 8 will preserve the market share that Microsoft has enjoyed with Windows for the past generation. I don’t know if Windows 8 will be a second Vista. It is even possible that this is the beginning of the end of Windows or even Microsoft.

I can’t say that Microsoft is late to the Tablet game. There was the PocketPC that was a small tablet that used a “pen” and there was a tablet version of Windows XP (also used a “pen”). These are pen tablets, the cool new tablets are touch tablets.

Microsoft is taking a different approach than Apple. Apple has two Operating Systems: iOS for tablets and devices and OSX for full blown computers. Microsoft is going with One OS to Rule Them All: Windows 8 will be on desktop and little tablet devices. I suppose WP8 will be Windows 8.

There are as many changes in the other platforms. Change defines our industry. Every change represents risks to all participants. Not changing also represents risks. This is just part of the job.

Sunday, September 25, 2011

Filtering files by Date Range

In this For Work post I am wrote a function that will return a list of files (FileInfo objects actually) for a given search string and date range.

The Problem

My internal customer wants archive files in a given directory for a given date range. Right now I am working on the part where I get the files; the rest of the problem is beyond the scope of this post.

My Solution

I am using FileInfo.GetFiles() to get a list of FileInfo objects for a given filename filter and then use Linq to filter that list for the date range.

So here’s my function:

/// <summary>
/// Returns a List of FileInfo for a given serch pattern and date range.
/// </summary>
/// <param name="searchPath">
/// The path + the search string
/// </param>
/// <param name="startDt">
/// The beginning date for the search (as of midnight)
/// </param>
/// <param name="endDt">
/// The end date of the search (as of midnight;
/// use DateTime(y, m, d 23, 59, 59) to get the whole day)
/// </param>
/// <param name="searchOp">
/// Specifies whether to search the current directory, or the current directory
/// and all subdirectories.        
/// </param>
/// <returns>
/// A List of FileInfo
/// </returns>
public static List<FileInfo> SearchFiles(string searchPath, DateTime? startDt, 
    DateTime? endDt, SearchOption searchOp)
{
    // Break searchPath into parts
    string directory = Path.GetDirectoryName(searchPath);
    string pattern = Path.GetFileName(searchPath);

    // DirectoryInfo exposes GetFiles used below
    var dinfo = new DirectoryInfo(directory);
    // Get all of the files that meet the criteria
    var finfol = new List<FileInfo>(dinfo.GetFiles(pattern, searchOp));

    // Throw out the files that are out of the date range
    // Here is where I would add aditional filters
    return new List<FileInfo>(
        from f in finfol
        where (f.LastWriteTime >= (startDt ?? DateTime.MinValue)) &&
              (f.LastWriteTime <= (endDt ?? DateTime.MaxValue))
        select f);
}

Thursday, August 18, 2011

Creating Simple Form Item Template for VS 2008

In this For Work blog entry, I am going to create a Visual Studio Item Template for a Windows Form. We have several patterns of forms that we need to create over and over again. I want to be able to create my own Form with all my setting set by default, include some standard controls and derive from my base class, etc.

I am not using Export Template because I want to see all the working parts.

The Process

Create a Starter Form

Create a form that has everything I want in my template in a WinForm project. In this case I will pretend that we created a WinForm called MyForm.cs.

Gather the Template Files together

I created a directory somewhere on the file system and copy the form’s source files into that directory. Add to those files an ico file and a new text files with a .vstemplate extension. So for my template, I would put the following files”

MyForm.cs The Form’s code file. Not yet changed, will replace class name and namespace later.
MyForm.Designer.cs The Form’s designer code files. Will also need replace class name and namespace.
MyForm.resx (Optional) Form’s resources, In this example, I use this to set a different icon.
BICYCLE.ICO The Icon file that appears in the Add New Item dialog
MyFormCS.vstemplate Empty file. Will be the Template metadata file.

Add/Replace Template Parameters in Source files

In the process Template Parameters are substituted with values created by the New Item Wizard when the new item is created. They are declared in the form of $parameter$

In my demo, I use the following Template Parameters:

rootnamespace The full namespace of the item; “root” namespace suggests something different
safeitemrootname The name of the item (or class) being created.

So given the following code:

using System; using System.Windows.Forms; namespace ItemTemplatePlayGround { public partial class MyForm : Form { public MyForm() { InitializeComponent(); } …

I change the code to read:

using System; using System.Windows.Forms; namespace $rootnamespace$ { public partial class $safeitemrootname$ : Form { public $safeitemrootname$() { InitializeComponent(); }  

Fill out the .vstemplate file

The .vstemplate file contains most the metadata that Visual Studio needs use my template. Things like the name and description of the template, the icon that is displayed in the new New Item dialog, the project type, etc. It also contains a list of all the files that make up the templates and instructions on how to handle them. (NOTE: the physical location of the template file determines the Template’s “Language” and “Category”, so I can’t claim that it contains all the metadata).

Notice that Template Parameters appear in the .vstemplate file.

<VSTemplate Type="Item" Version="2.0.0" 
            xmlns="http://schemas.microsoft.com/developer/vstemplate/2005">
  <TemplateData>
    <Name>My Lame Form</Name>
    <Description>An empty Form</Description>
    <DefaultName>MyForm.cs</DefaultName>
    <ProjectType>CSharp</ProjectType>
    <Icon>BICYCLE.ico</Icon>
  </TemplateData>
  <TemplateContent>
    <ProjectItem TargetFileName="$fileinputname$.cs"  
                 ReplaceParameters="true" 
                 SubType="Form">MyForm.cs</ProjectItem>
    <ProjectItem TargetFileName="$fileinputname$.Designer.cs"  
                 ReplaceParameters="true">MyForm.Designer.cs</ProjectItem>
    <ProjectItem TargetFileName="$fileinputname$.Designer.resx"
                 ReplaceParameters="true">MyForm.Designer.resx</ProjectItem>
  </TemplateContent>
</VSTemplate>

I have chosen not to go over every detail of the .vstemplate file. There are denser articles on the web that cover them in painful detail.

The TempateData section gives data about the template as a whole. Name and Description are what you think they are. DefaultName is used to propose a name in the Add New Item dialog. ProjectType is the language of the item; either CSharp or VisualBasic. Icon refers to the icon (.ico) file that provides the icon next to the project type name in the New Item dialog.

The TemplateContent contains ProjectItem elements for each of the files in the template (except the icon file and the .vstemplate itself). If your project contains 5 files total, there should be 3 ProjectItem elements.

Within the ProjectItem element, the TargetFileName attribute is the name of the new file created by the template. ReplaceParameters determines if Template Parameters in this file are replaced. SubType determines the Visual Studio Editor used to design this file; you only need this attribute if this file uses a designer. The Element’s inner text represents the name of the file within the template itself. The element value is the before file name and the TargetFileName is the after.

Where are my template files?

The default your item template files are located in My Documents\Visual Studio 2008\Templates\ItemTemplates\Language. On my system, Visual Studio 2008 was looking in C:\Users\jacks\Documents\Visual Studio 2005\Templates\ItemTemplates. So, it is a good idea to make sure that Visual Studio is looking in the right place.

To check/change the template location: Tools => Options, select the Projects and Solutions group and the General tab.

Links

Finished Template

Saturday, July 23, 2011

Generate several files from one template with the T4 Toolbox

I have been playing with using T4Toolbox to generate more than one file from a single template within Visual Studio.

T4Toolbox.Template

The Template class is the T4Toolbox’s version of TextTransformation that has a very special method called RenderToFile(); this method writes the text to be generated to a file. So, If I wanted to generate a file for each item in a list (say a list of database tables), I could use a foreach loop to create a new instance of my Template class and call Its RenderToFile method with different file names.

<#@ template language="C#v3.5" hostSpecific="true" #>
<#@ output extension="txt" #>
<#@ include file="T4Toolbox.tt" #>
<#
    Write("This file (minimum.txt) is also generated");
    for (int i = 7; i <= 11; ++i)
    {
        string fileName = string.Format("file{0:00}.lam", i);
        var lt = new LameFileTemplate(fileName);
        lt.RenderToFile(fileName);
    }
#>
<#+
private class LameFileTemplate : Template
{
    public LameFileTemplate(string fileName)
    {
        this.FileName = fileName;
    }
    private string FileName {get; set;}
    public override string TransformText()
    {
        #><#=this.FileName#><#+
        return this.GenerationEnvironment.ToString();
    }
}
#>

When I save this file, Visual Studio runs the template and creates several files. Notice that creates a file that matches the file name of the template; this can’t be helped. Since the default output extension is “cs”, it is important to set this to something else.

image
My template and generated files

NOTE: In a failed demo that I gave at the Portland Code camp this spring I wrote a template that would create a SELECT stored procedure for each table in a given database (each in its own file). The demonstration was too complicated and hard to follow. It used the Generator class SMO and a bunch of other stuff.

Thursday, June 23, 2011

The GAX Property Directive

One of the things that really bother me about T4 templates is the lack of direct parameter support (at least in the Visual Studio 2008/.NET 3 timeframe).

In an earlier post, I passed parameter by writing the parameter data to a file outside of the template and reading the data from within. I used a common assembly linked to both sides of the divide. This works for me, but it is really hacky.

GAX Template Host

During my preparation for my Portland Code Camp presentation, I came across Guidance Automation Toolkit for Visual Studio 2008. It has its own TemplateHost that includes a Dictionary of parameters.

Parameters are exposed to the Template through a custom directive. On the template, the parameter is declared using a directive like this:

<#@ property processor="PropertyProcessor" name="MyName" type="String" #>
The directive contains the following parts:
processor Should be "PropertyProcessor" (there may be a way to write your own processor, I haven’t gone down that rat hole yet).
name The Variable name that can appear in your code. It works just like a variable, however, tangable T4 Editor doesn’t provide Intenesence (I wouldn’t have expected it).
type Any .NET Type that the template knows about. You should use the .NET type name (as opposed to C# or VB.NET type).
In C# the parameters are created like this:
var arguments = new Dictionary();
arguments.Add("MyProperty", new PropertyData("String", typeof(string)));
To pass parameters from your code you need to create a Dictionary<string, PropertyData>, add your parameters and pass them as the second argument into the TemplateHost constructor (the first can be any string as far as I can tell).

Simple Sample

The Template

<#@ template language="C#" debug="True" #>
<#@ output extension="txt" #>
<#@ assembly name="System.dll" #>
<#@ import namespace="System" #>
<#@ property processor="PropertyProcessor" name="MyProperty" type="Nullable<Int32>"#>
<#@ property processor="PropertyProcessor" name="MyName" type="String"#>
My Property Test
<# if (MyProperty.HasValue) {#>
Property Value: <#= MyProperty.Value #>!
<#}#>
Name: <#= MyName #>

C# code

static void Main(string[] args)
{
    // Prepare template parameters
    var arguments = new Dictionary<string, PropertyData>();
    arguments.Add("MyProperty", new PropertyData(42, typeof(int?)));
    arguments.Add("MyName", new PropertyData("Jack Stephens", typeof(string)));

    // Initialize GAX template host
    // The Template Host is from GAX, not the default host
    var host = new TemplateHost("Random String", arguments);
    host.TemplateFile = Path.Combine(Directory.GetCurrentDirectory(), 
        "PropertyTest.tt");

    // Transform template
    string template = File.ReadAllText(host.TemplateFile);

    ITextTemplatingEngine engine = new Engine();
    string output = engine.ProcessTemplate(template, host);

    Console.Write(output);
    Console.ReadKey();
}

References

Monday, June 13, 2011

Portland Code Camp 2011 Presentation Notes

Again, I'm late getting this up. I will give usual excuses of being busy. In the future I will not promise anything before the next weekend after the code camp.

Since I developed this talk on this blog, this entry will essentially be a link list. I put a copy of the slides in Google doc and you can reference it here

This presentation is similar, but not the same as a presentation I gave at the Boise Code Camp in February. In the interest of being DRY (Don’t Repeat Yourself) I will reference the notes for that presentation.

Tools

For this presentation, I used the Tangible T4 Editory and the T4 Toolbox.

Template Parts

I presented a slide that showed the basic template parts in a small mock template:

<#@ template language="C#" #>
<#@ directive property=“value” #>
<# var item = "Statement Block"; #>
Text Block 
<#= "Expression Block" #>
<#+ 
    string ClassFeatureBlock(string thingy)
    {
        return thingy;
    } 
#>

Hello World Monty

I showed some of the elementary features of T4 templates by refactoring a basic template that would create a text file that contained the infamous words “Hello World”. A script of that exercise is here

Basic Code Generation Demonstration

To demonstrate Code Generation I set out to generate a simple T-SQL Select Statement using Sql Management Objects (SMO).

Here I will refer to the Boise Code Camp Notes.

Generating Multiple Files using T4 Toolbox

I used the result of the Basic Code Generation Demonstration to attempt to generate select scripts for all the tables in a given database. This demonstration was based on a series of blog posts by Oleg Synch (Part 1, Part 2, Part 3 & Part 4).

I do intend to give my take on this process. Perhaps that is why I'm over a week late on these notes. I will add a link to these notes later.

GAX Property Directive

I discussed using Guidance Automation Extensions's property directive to overcome T4's lack of native support for passing parameters into templates. This part was based on another Oleg Synch article Understanding T4: <#@ property #> directive.

Again, I will add links to future blogs post(s) on this subject here.

Generating a Nullable ADO.NET DataSet Wrapper

This is when I went to Show and Tell Mode. I demonstrate a Nullable DataSet Wrapper that I wrote in these blog entries. I wrote the templates in VB.NET to generate code in C#.

Part 1, Part 2, Part 3 & Part 4

Last Word

This presentation reached too far and, like Icarus, I failed to hit the mark. The number of forward links is a sign of that. I intend to back-blog in the areas the missing material and add links when I am done.

Tuesday, May 31, 2011

Writing Kindle Presentation Notes with MobiPocket Creator

Next weekend I will be presenting on Code Generation with T4 Templates at the Portland Code Camp and I bought a Kindle a week or so ago. So, how do I play with my new toy without getting behind on preparing for my presentation?

Mobipocket Creator Publisher Edition

I am creating small ebooks with Word and Mobipocket Creator Publisher Edition. I work though a demo or other presentation block and write it in Word. Then I use the Mobipocket Creator to create an ebook. I put it on my Kindle and review it before I go to bed.

Is this really a net gain or just a way to justify playing with my new toy instead of doing real work? I will find out come Saturday in Portland

Tuesday, May 17, 2011

Hanselminutes #266 and Alt.Net Seattle’s "Open Source in Business"

I just finished listening to Hanselminutes Open Source vs. Making Money vs. Freaking Lasers - Are we all Evil? With Chris Sells and it reminded me of the Open Source in Business session that I attended at Alt.Net Seattle. This post is attempting to tie these two things together.

Open Source vs. Commercial Software

Should we share our non-core software? Scott and Chris suggest that open sourcing your software is a cost. If you open source a project to increase good will among the programmer community; it is a good thing but not necessarily profitable.

In Alt.Net Seattle’s Open Source in Business, a couple of participants claim that their companies benefited from "free" programming and used the contributor’s list to fill positions.

Scott and Chris talk about the Portland open source culture and how making money isn’t necessarily the biggest thing in Portland. Some of the open source that way to keep more of what they make.

Companies Being Evil

Apple and Google have done evil things in the last few years, but Microsoft is still evil. Scott and Chris suggested that Microsoft isn’t well organized enough to be evil. No company of any size is beloved by everyone.

Is evil associated with making money over customers? In any publicly traded company, stock holder value is king over everything else. Microsoft may feel more evil because they charge us directly. People may like them better if pricing was less complicated (the pricing discussion goes on from there, but I won’t go there in this post).

Since Google and Facebook make money from advertising and don't charge us anything, they feel less evil (Facebook privacy policy makes them feel more evil to me).

Originality of .NET Open Source

At Alt.NET we also discussed the relative lack of originality in the .NET ecosystem. Many of the major open source projects (NHibernate, NUnit, etc.) are copies of Java open source projects. Perhaps many .NET developers are shy about starting ambitious open source project fearing that Microsoft will somehow co-opt it. Perhaps Microsoft is too disorganized to get out of the way of some cool open source project.

This may be the way that Microsoft is evil. It also could be that the companies that use Microsoft software are more conservative and value safety over innovation. I've worked for a few companies that view open source as evil -- to these companies, Microsoft is a crusader fighting against the chaos.

Sunday, May 08, 2011

Thoughts durring Alt .NET Seattle: Explaining my lame laptop.

Confession: I cam to Alt .Net Seattle with a HP Pavilion Entertainment PC Laptop with a HUGE screen. The thing is a monster. And it is totally uncool. I am not going to replace it for another year: I am not made of money.

I wonder if there are things I can to to acknowledge my folly in a light self deprecating way. Perhaps a sticker that says something like "I'm a slack jawed yokel from Spokane" or somehow making light of the fact that it isn't an Apple (it's 40% Apple here), but that seams too heavy to me. A sticker would be nice but what should is say? etc.

Monday, May 02, 2011

Thoughts before Alt .NET Seattle: Part II: Using jrcs3.com

I am going to Alt.Net 2011 Seattle Conference next weekend. Since I am not on the bleeding edge of technology at work, I’m looking into modern methods and technologies.

Using my domain

Several years ago, I bought the domain jrcs3.com; it the initials for my legal name John Robert Chilton Stephens III.

Since I will be hanging out with the cool kid and in technology, all the cool kids have vanity domain, I need to make sure that my domain is visible on my cards, etc. So this weekend I:

  • Moved this blog from jrcs3.blogspot.com to brochure.jrcs3.com (it is “brochure” because someone said that it isn’t a blog until it gets comments).
  • Created a jrcs3.com event email address for the Alt.Net Seattle 2011. You will have to attend the event to see what it is.

When I get my next paycheck (Monday or Tuesday) I will:
  • Buy a year of hosting for jrcs3.com
  • Write a simple home page for it in Razor (linking to brochure.jrcs3.com). I have already created a WebMatrix project.
  • Print out a couple of sheets of business cards to hand out.

Friday, April 29, 2011

Thoughts before Alt .NET Seattle: Part I: TDD

I am going to Alt.Net 2011 Seattle Conference next weekend. Since I am not on the bleeding edge of technology at work, I’m looking into modern methods and technologies.

TDD

On my last couple of gigs, when I bring up Test Driven Development, I get a lecture about how it is overly academic and would never work "here" (one place even banned NUnit altogether). Yes, it may take more time to code, but at these places, we spend a lot of time fixing things after the fact.

I agree that it can be taken too far. There are times that it doesn’t make sense to write test before writing trivial code. UI is infamously un-testable. Be pragmatic, it is just as extreme to reject automated testing altogether

I like the way TDD appears to make you think. You know, this function needs to do these 5 things, and what about this edge case, etc. And I have some tests that I can use later, for less effort than if wrote the test later.

I have snuck some tests into the projects that I have more control over. Most of the tests are too large (cover more one thing) and break other TDD rules. Since I can’t get approval to use an IOC container, they are not unit test. Even with these lower quality tests, I have found bugs and have been able to safely re-factor.

Thursday, April 14, 2011

Going to St. Louis

Nine years ago, my sister Susie was run over by a bus and killed. I am going to St. Louis to help my mother plant a tree in her honor.

I both look forward to and dread this trip. After all, it is the place of her death. It is a city that looms large in my mind even though I have never been there. I should have gone years ago and face the demons, now is as good a time as any.

Tuesday, April 12, 2011

Generate Linq To Sql .DBML Fragments

Another user for my post on getting the schema from a Stored Procedure. This time I am generating a Dbml fragment.

In the Column element, I am not providing a DbType. I don’t use Linq to Sql in such a way that it actually uses that value for anything important. I am also assuming that CanBeNull is true.

Yes, this is totally a message for work post. If you need detail as to what I’m doing, check this out.

<#@ assembly name="TsqlDesriptionUtil.dll" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ import namespace="TsqlDesriptionUtil" #>
<#@ import namespace="System.Collections.Generic" #>
<#+
    public class DbmlFunctionFromTSqlCmd : TextTransformation
    {
        public string FunctionName;
        public string FunctionMethod;
        public List<TableDescription> tables;

        public override string TransformText()
        {
#>    <Function Name="<#= FunctionName
                #>" Method="<#= FunctionMethod #>">
<#+ foreach(TableDescription table in tables)
    { #>        <ElementType Name="<#= FunctionMethod #>Result_<#= table.TableName #>">
<#+ foreach(FieldDescription field in table.Fields)
   { #>             <Column Name="<#= field.FieldName #>" 
                      Type="<#= field.FieldType.FullName  #>" 
                      CanBeNull="true" />
<#+ } #>
         </ElementType>
<#+ } #>
    </Function>
<#+ 
        return this.GenerationEnvironment.ToString();
    }
} #>

Again, for Demonstration purposes I wrote a simple T4 Template that includes the template above and uses the library I wrote in this post to get a description of the data.

<#@ template language="C#v3.5" debug="true" 
#><#@ output extension="txt" 
#><#@ include file="DbmlFunctionFromTSqlCmd.tt" 
#><#@ assembly name="TsqlDesriptionUtil.dll" 
#><#@ import namespace="TsqlDesriptionUtil" 
#><#
    // Since I'm generating XML, I will go to extreme measures to avoid new 
    // line characters like the strange formatting of declarations above ^
 
    // Get the table descriptions for given T-SQL
    string conString = @"Data Source=localhost;" +
      @"Initial Catalog=Northwind;Integrated Security=True";
    string commandString = "EXEC  CustOrderHist @CustomerID='ALFKI'";
    List<TableDescription> tables = 
      TsqlCommandSchema.GetFieldDescription(conString, commandString);
 
    // Generate DBML Fragment:
    var gen = new DbmlFunctionFromTSqlCmd();
    gen.FunctionName = "dbo.GetBigTableDataUseTempTab";
    gen.FunctionMethod = "MyNew";
    gen.tables = tables;
    Write(gen.TransformText()); 
#>

(If I were more of a studman programmer dude, I'd write a Visual Studio Add-in that would insert this fragment directly into my .dbml file.)

Tuesday, March 29, 2011

Generate DataSet .XDS

Last week on my Brochure, I wrote about getting the schema of a Stored Procedure or other T-SQL command. Now I am going to bring this a little farther and generate am .XDS file that can be used to generate a Typed DataSet. This T4 Template include file takes a List and a DataSet name and generates the .XSD.

<#@ assembly name="TsqlDesriptionUtil.dll" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ import namespace="TsqlDesriptionUtil" #>
<#@ import namespace="System.Collections.Generic" #>
<#+
    public class DataSetFromTSqlCmd: TextTransformation
    {
        public string dsName;
        public List<TableDescription> tables;

        public override string TransformText()
        {
#><?xml version="1.0" encoding="utf-8"?>
<xs:schema id="<#= dsName #>" 
  targetNamespace="http://jrcs3.blogspot.com/<#= dsName #>.xsd"
  xmlns:mstns="http://jrcs3.blogspot.com/<#= dsName #>.xsd" 
  xmlns="http://jrcs3.blogspot.com/<#= dsName #>.xsd" 
  xmlns:xs="http://www.w3.org/2001/XMLSchema" 
  xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
  xmlns:msprop="urn:schemas-microsoft-com:xml-msprop" 
  attributeFormDefault="qualified" 
  elementFormDefault="qualified">
  <xs:annotation>
    <xs:appinfo source="urn:schemas-microsoft-com:xml-msdatasource">
      <DataSource DefaultConnectionIndex="0" 
          FunctionsComponentName="QueriesTableAdapter"
          Modifier="AutoLayout, AnsiClass, Class, Public" 
          SchemaSerializationMode="IncludeSchema" 
          xmlns="urn:schemas-microsoft-com:xml-msdatasource">
        <Connections />
        <Tables />
        <Sources />
      </DataSource>
    </xs:appinfo>
  </xs:annotation>
 <xs:element name="<#= dsName #>" 
        msdata:IsDataSet="true" 
        msdata:UseCurrentLocale="true" 
        msprop:Generator_UserDSName="<#= dsName #>" 
        msprop:Generator_DataSetName="<#= dsName #>" 
        msprop:EnableTableAdapterManager="true">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
<#+ foreach(TableDescription table in tables)
    { #>
        <xs:element 
            name="<#= table.TableName #>"  
            msprop:Generator_UserTableName="<#= table.TableName #>" 
            msprop:Generator_RowDeletedName="<#= table.TableName #>RowDeleted" 
            msprop:Generator_RowChangedName="<#= table.TableName #>RowChanged" 
            msprop:Generator_RowClassName="<#= table.TableName #>Row" 
            msprop:Generator_RowChangingName="<#= table.TableName #>RowChanging" 
            msprop:Generator_RowEvArgName="<#= table.TableName #>RowChangeEvent" 
            msprop:Generator_RowEvHandlerName="<#= table.TableName 
               #>RowChangeEventHandler" 
            msprop:Generator_TableClassName="<#= table.TableName #>DataTable" 
            msprop:Generator_TableVarName="table<#= table.TableName #>" 
            msprop:Generator_RowDeletingName="<#= table.TableName #>RowDeleting" 
            msprop:Generator_TablePropName="<#= table.TableName #>">
          <xs:complexType>
            <xs:sequence>
<#+ foreach(FieldDescription field in table.Fields)
 { #>
                <xs:element 
                    name="<#= field.FieldName #>" 
                    msprop:Generator_UserColumnName="<#= field.FieldName #>"
                    msprop:Generator_ColumnVarNameInTable="column<#= field.FieldName 
                      #>" 
                    msprop:Generator_ColumnPropNameInRow="<#= field.FieldName #>" 
                    msprop:Generator_ColumnPropNameInTable="<#= field.FieldName 
                      #>Column" 
                    type="xs:<#= field.XsdTypeName #>" 
                    minOccurs="0" />
<#+ } #>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
<#+ } #>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>
<#+ 
  return this.GenerationEnvironment.ToString();
 }
} #>

For Demonstration purposes I wrote a simple T4 Template that includes the template above and uses the library I wrote in this post to get a description of the data.

<#@ template language="C#v3.5" debug="true" 
#><#@ output extension="xsd" 
#><#@ include file="DataSetFromTSqlCmd.tt" 
#><#@ assembly name="TsqlDesriptionUtil.dll" 
#><#@ import namespace="TsqlDesriptionUtil" 
#><#
    // Since I'm generating XML, I will go to extreme measures to avoid new line 
    // characters like the strange formatting of declarations above ^

    // Get the table descriptions for given T-SQL
    string conString = @"Data Source=localhost;" +
      @"Initial Catalog=Northwind;Integrated Security=True";
    string commandString = "EXEC  CustOrderHist @CustomerID='ALFKI'";
    List<TableDescription> tables = 
      TsqlCommandSchema.GetFieldDescription(conString, commandString);

    // Generate XSD:
    var gen = new DataSetFromTSqlCmd();
    gen.dsName = "MyNewDS";
    gen.tables = tables;
    Write(gen.TransformText()); 
#>

Since TsqlCommandSchema.GetFieldDescription supports multiple results sets and Stored Procedure that use temporary tables.

At work I will probably integrate this template into an existing code generation tool using my TTCommucator tool.

Tuesday, March 22, 2011

Getting the Schema of a Stored Procedure Results Set

Visual Studio is pretty good at discovering the schema of a stored procedure if the procedure doesn't use temporary tables and returns only one result set. You can drag the stored procedure on to a DataSet or Linq to Sql Design Surface and it is generated for you. In real life, stored procedures don’t fit into that neat box.

Since I am lazy and don’t like to build DataSets by hand, I wrote this little class. Note that I am actually executing the T-SQL command. Any side effect of the stored procedure will take place; this is acceptable in my environment, it may not be in yours.

I am getting less information from this method than I get from static sources like SQL Server’s SMO; for example, I am not getting the length of strings. I am getting enough information to do what I want to do.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace TsqlDesriptionUtil
{
    public class TsqlCommandSchema
    {
        public static List<TableDescription> GetFieldDescription(
            string conString, string commandString)
        {
            var rVal = new List<TableDescription>();
            int tableNumber = 1;
            using (var con = new SqlConnection(conString))
            {
                using (var com = new SqlCommand(commandString, con))
                {
                    con.Open();
                    // WARNING: I am executing the command, beware of side effects!
                    SqlDataReader reader = 
                        com.ExecuteReader(CommandBehavior.CloseConnection);
                    // Table Loop
                    do
                    {
                        var fieldDescriptionList = new List<FieldDescription>();
                        // Field Loop
                        for (int i = 0; i < reader.FieldCount; ++i)
                        {
                            Type fieldType = reader.GetProviderSpecificFieldType(i);
                            fieldDescriptionList.Add(
                                new FieldDescription(reader.GetName(i), 
                                reader.GetDataTypeName(i), reader.GetFieldType(i)));
                        }
                        // T-SQL doesn't give table names, so I will make up my own
                        rVal.Add(new TableDescription(string.Format(
                            "Table{0}", tableNumber++), fieldDescriptionList));
                    } while (reader.NextResult());
                    con.Close();
                }
            }
            return rVal;
        }
    }
    public class TableDescription
    {
        public TableDescription(string tableName, List<FieldDescription> fields)
        {
            this.TableName = tableName;
            this.Fields = fields;
        }
        public string TableName { get; set; }
        public List<FieldDescription> Fields { get; set; }
        public int Count
        {
            get
            {
                if (this.Fields != null)
                    return this.Fields.Count;
                else
                    throw new Exception("Fields not loaded");
            }
        }
    }
    public class FieldDescription
    {
        public FieldDescription(string fieldName, string dataTypeName, Type fieldType)
        {
            this.FieldName = fieldName;
            this.DataTypeName = dataTypeName;
            this.FieldType = fieldType;
        }
        public string FieldName { get; set; }
        public string DataTypeName { get; set; }
        public Type FieldType { get; set; }
        public string XsdTypeName
        {
            get
            {
                Type t = this.FieldType;
                return getXsdTypeNameForType(t);
            }
        }

        private static string getXsdTypeNameForType(Type t)
        {
            string rVal = string.Empty;
            //.NET Framework type XML Schema (XSD) type
            switch (t.FullName)
            {
                case "System.Boolean": rVal = "Boolean"; break;
                case "System.Byte": rVal = "unsignedByte"; break;
                case "System.Byte[]": rVal = "base64Binary"; break;
                case "System.DateTime": rVal = "dateTime"; break;
                case "System.Decimal": rVal = "decimal"; break;
                case "System.Double": rVal = "Double"; break;
                case "System.Int16": rVal = "short"; break;
                case "System.Int32": rVal = "int"; break;
                case "System.Int64": rVal = "long"; break;
                case "System.SByte": rVal = "Byte"; break;
                case "System.String": rVal = "string"; break;
                case "System.String[]": rVal = "ENTITIES"; break;
                case "System.TimeSpan": rVal = "duration"; break;
                case "System.UInt16": rVal = "unsignedShort"; break;
                case "System.UInt32": rVal = "unsignedInt"; break;
                case "System.UInt64": rVal = "unsignedLong"; break;
                case "System.Uri": rVal = "anyURI"; break;
                case "System.Xml.XmlQualifiedName": rVal = "QName"; break;
            }
            return rVal;
        }
    }
}

I compiled the above C# source file into an assembly. Here is a xUnit test that demonstrates its use.

[Fact(DisplayName = "Run SP 001")]
public void RunSp001()
{
    string conString = @"Data Source=localhost;Initial Catalog=My_Db;" + 
        "Integrated Security=True";
    string commandString = "EXEC  GetBigTableDataUseTempTab 101";
    List<TableDescription> rVal = 
        TsqlCommandSchema.GetFieldDescription(conString, commandString);

    foreach (var item in rVal) 
    {
        Console.WriteLine(string.Format("{0}: {1}", item.TableName, item.Count));
        foreach(var field in item.Fields)
            Console.WriteLine(
                string.Format("\t{0}\t{1}\t{2}\t{3}", 
                    field.FieldName, 
                    field.DataTypeName, 
                    field.FieldType, 
                    field.XsdTypeName));
    }
}

Monday, March 21, 2011

GetXsdTypeNameForType() function

For a project I'm working on, I need to convert .NET Framework types into XML Schema (XSD) types. I wrote a little function to do the translation:

public static string GetXsdTypeNameForType(Type t)
{
    string rVal = string.Empty;
    switch (t.FullName)
    {
        case "System.Boolean": rVal = "boolean"; break;
        case "System.Byte": rVal = "unsignedByte"; break;
        case "System.Byte[]": rVal = "base64Binary"; break;
        case "System.DateTime": rVal = "dateTime"; break;
        case "System.Decimal": rVal = "decimal"; break;
        case "System.Double": rVal = "double"; break;
        case "System.Int16": rVal = "short"; break;
        case "System.Int32": rVal = "int"; break;
        case "System.Int64": rVal = "long"; break;
        case "System.SByte": rVal = "byte"; break;
        case "System.String": rVal = "string"; break;
        case "System.String[]": rVal = "ENTITIES"; break;
        case "System.TimeSpan": rVal = "duration"; break;
        case "System.UInt16": rVal = "unsignedShort"; break;
        case "System.UInt32": rVal = "unsignedInt"; break;
        case "System.UInt64": rVal = "unsignedLong"; break;
        case "System.Uri": rVal = "anyURI"; break;
        case "System.Xml.XmlQualifiedName": rVal = "QName"; break;
    }
    return rVal;
}

NOTE: There are places where I had to make a decision as to which XSD type would map to System.TimeDate, System.String, System.Byte[], etc. And there's the question of which XSD type I will map to my custom Employee, Appointment or Cow types!

Monday, March 07, 2011

Boise Code Camp 2011 Presentation Notes

Sorry I’m late getting this up. I will give usual excuses of being busy.

Since I developed this talk on this blog, this entry will essentially be a link list. I put a copy of the slides in Google doc and you can reference it here

Template Parts

I presented a slide that showed the basic template parts in a small mock template:

<#@ template language="C#" #>
<#@ directive property=“value” #>
<# var item = "Statement Block"; #>
Text Block 
<#= "Expression Block" #>
<#+ 
    string ClassFeatureBlock(string thingy)
    {
        return thingy;
    } 
#>

Hello World Monty

I showed some of the elementary features of T4 templates by refactoring a basic template that would create a text file that contained the infamous words “Hello World”. A script of that exercise is here

Basic Code Generation Demonstration

To demonstrate Code Generation I set out to generate a simple T-SQL Select Statement using Sql Management Objects (SMO).

The Target Code looked something like this:

Include (SelectSpInclude.tt)
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#@ assembly name="Microsoft.SqlServer.ConnectionInfo" #>
<#@ assembly name="Microsoft.SqlServer.Smo" #>
<#@ import namespace="Microsoft.SqlServer.Management.Smo" #>
<#+
    public class SelectSpInclude: TextTransformation
    {
        public string ServerName;
        public string DbName;
        public string TableName;

        public override string TransformText()
        {
 
        Table table = getTable(ServerName, DbName, TableName);
#>
SELECT <#+ bool forgetComma = true;
 foreach (Column col in table.Columns)
{ #><#+if (!forgetComma) Write("      ,");#>[<#= col.Name #>]
<#+ forgetComma = false;
} #>
FROM <#= table.Name #><#+

        return this.GenerationEnvironment.ToString();
    } 
    Table getTable(string serverName, string dbName, string tableName)
    {
        Server server = new Server(serverName);
        Database database = new Database(server, dbName);
        Table table = new Table(database, tableName);
        table.Refresh();
        return table; 
    }
}
#>
Template (EmployeesSp.tt)
<#@ template language="C#v3.5" #>
<#@ output extension="sql" #>
<#@ include file="SelectSpInclude.tt" #>
<#
    SelectSpInclude gen = new SelectSpInclude();
    gen.ServerName = @"localhost\SQLEXPRESS";
    gen.DbName = "Northwind";
    gen.TableName = "Employees"; Write(gen.TransformText());
#>

Result (EmployeesSp.sql)
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[Title]
      ,[TitleOfCourtesy]
      ,[BirthDate]
      ,[HireDate]
      ,[Address]
      ,[City]
      ,[Region]
      ,[PostalCode]
      ,[Country]
      ,[HomePhone]
      ,[Extension]
      ,[Photo]
      ,[Notes]
      ,[ReportsTo]
      ,[PhotoPath]
FROM Employees

Somewhere in this part of the presentation, someone asked if it is possible to generate a series of files from a single template, a separate code file for each table in a database. Oleg Sych has a blog post that does just that here

Generating a Nullable ADO.NET DataSet Wrapper

This is when I went to Show and Tell Mode. I demonstrate a Nullable DataSet Wrapper that I wrote in these blog entries. I wrote the templates in VB.NET to generate code in C#.

Part 1, Part 2, Part 3 & Part 4

Sunday, March 06, 2011

T4 Template Tour of First Gear

At the Boise Code Camp, I lead off with a demonstration of some basic features of T4 Templates. The point of the presentation is to get you from no T4 to the level of most blog examples. I got good reviews from the few who attended my session, so I thought I would attempt to write it down. Here goes…

The Script

First, create a Text file and change the extension to "tt". We’ll call it "Hello.tt". I will add a template directive and tell T4 that I want to use C#. I add the text "Hello World" below the directive. When I save it, "Hello.cs" appears below the template.

Template (Hello.tt):
<#@ template language="C#" #>
Hello World
Output (Hello.cs):
Hello World

Also notice that Visual Studio complains that Hello.cs won’t compile. For this demonstration, I don’t want to create a C# code file, so I will tell the template to create a "txt" file with an output directive.

Template (Hello.tt):
<#@ template language="C#" #>
<#@ output extension="txt" #>
Hello World
Output (Hello.txt):
Hello World

Visual Studio is happy because it doesn’t try to compile "txt" files. (Since it doesn't change, I won't repeat the output again.)

Now, let’s add some actual C# template code, I’m going to create a variable called "name" in a Statement Block and reference it in an Expression Block.

Template (Hello.tt):
<#@ template language="C#" #>
<#@ output extension="txt" #>
<# string name = "World"; #>
Hello <#= name #>

Now I’m going to replace the variable with a function. The function will need to be in a Class Feature Block. Just to speed things up, I’m going to use a Write call in a Statement Block to render the name. You can write text to the output using either Expression Blocks OR calls to Write().

Template (Hello.tt):
<#@ template language="C#" #>
<#@ output extension="txt" #>
Hello <# Write(GetName()); #>
<#+ 
    string getName()
    {
        return "Word";
    }
#>

Now I want to move my function into an include file. I will create a new text file and call it "HelloFunctions.tt" I move the Class Feature Block into that file. Notice that there is no template directive. Also, you must save the include file BEFORE you save the template file.

Include (HelloFunctions.tt)
<#+ 
    string getName()
    {
        return "Word";
    }
#>
Template (Hello.tt):
<#@ template language="C#" #>
<#@ include file="HelloFunctions.tt" #>
<#@ output extension="txt" #>
Hello <# Write(GetName()); #>

Now, for a brief look under the hood, I can show you the source code for the Text Transformation. Here I add "debug="true"" to the template file, save it and look in the temp directory (you can get your temp directory by typing "set temp" in a command prompt)

Template (Hello.tt):
<#@ template language="C#" #>
<#@ include file="HelloFunctions.tt" #>
<#@ output extension="txt" #>
Hello <# 
// This comment will show up in .cs file
Write(GetName()); 
#>
Generated C# File (hcs3svyj.0.cs in this case)
namespace Microsoft.VisualStudio.TextTemplatingBFD11436D0A4595408B06EA4E1B2A636 {
    using System;
    using Microsoft.VisualStudio.TextTemplating.VSHost;

    
    #line 1 "c:\vsprojects\SimpleT4\Hello.tt"
    public class GeneratedTextTransformation : 
        Microsoft.VisualStudio.TextTemplating.TextTransformation {
        public override string TransformText() {
            try {
                this.Write("Hello ");

                #line 4 "c:\vsprojects\SimpleT4\Hello.tt"

// This comment will show up in .cs file
Write(getName()); 

          
                #line default
                #line hidden
            }
            catch (System.Exception e) {
                System.CodeDom.Compiler.CompilerError error = new 
                    System.CodeDom.Compiler.CompilerError();
                error.ErrorText = e.ToString();
                error.FileName = "c:\\vsprojects\\SimpleT4\\Hello.tt";
                this.Errors.Add(error);
            }
            return this.GenerationEnvironment.ToString();
        }

        #line 1 "c:\vsprojects\SimpleT4\HelloFunctions.tt"

 string getName()
 {
  return "World";
 }

  
        #line default
        #line hidden
    }

    #line default
    #line hidden
}

These source files have helped me understand what is going on behind the scenes. For example, I didn’t really get the difference between Statement Blocks (<# #>) and Class Feature Blocks (<#+ #>) until I saw the generated.

Afterthought

This isn’t a transcription! Written and spoken communication is different by nature.

In the presentation I would change the value of "World" with different words ("Earth", "Boise", "Boise Code Camp", etc); In a presentation, this added proofiness that the templates were run and some lame humor to lighten the mood of the talk. In the written form, it would force me to repeat the Output after each sample (and make this longer).

Tuesday, February 22, 2011

Boise Code Camp: Code Generation with T4 Templates

I will be presenting at the Boise Code Camp on Saturday, February 26, 2011, 9:30 AM in the Farnsworth room in the BSU SUB!

T4 Templates (Text Template Transformation Toolkit) is the code generator built into Visual Studio. This session is a basic introduction to code generation in C# and .NET using T4 Templates in Visual Studio 2008. I will takes you on a journey starting from the basic "Hello World" template into a world where you can wrote reusable templates and interrogate SQL Server or an ADO.NET DataSet for table descriptions.

Check it out!

Wednesday, February 16, 2011

Four Styles of T4 Template Code

As I work through T4 templates as I get ready for Boise Code Camp, I’ve noticed a few styles of template code. Just for kicks, I am going to name them and give an example of each using a code generation that I blogged about early last year. Each template uses an include file that I present after the four samples. Each template relies on its style to the point of absurdity; I didn’t go as far as to eliminate all text blocks.

Real life templates are mixtures of these styles applied at appropriate places.

I adapted the code generation that I did here

Embed

This can also be called the Classic ASP style. All generated code is added as text blocks or expression blocks (<#= #>). The logic is contained in code blocks mixed in with the text and expression blocks. For simple logic, this style is probably OK. It can be difficult to read and maintain as the logic gets more complex. This is the most concrete approach.

<#@ template language="C#v3.5" debug="true" #>
<#@ output extension="sql" #>
<#@ include file="TableDescriptionUtilCS.tt" #>
<#
    string ConnectionString = @"Data Source=localhost\SQLEXPRESS;" + 
        "Initial Catalog=Northwind;Integrated Security=True";
    string Table = "Employees"; 

    TableDescriptionUtil tdu = new TableDescriptionUtil();
    List<TableDescription> Columns = tdu.GetTableDescription(ConnectionString, Table); 

    int counter;
#>
CREATE PROCEDURE [dbo].[Insert<#=Table #>]
<#
    counter = 1;
    foreach (TableDescription item in Columns)
    {
        #>    @<#= item.ColName #> <#= item.FullTypeName #><#
        if (item.Status == 0x80)
            #> OUT<#
        if (item.ColIsNullable != 0) 
            #> = NULL<#
        if (counter < Columns.Count)
            #>,<#
        counter++;
        #><#= "\r\n"#><#
    }
#>

AS

INSERT INTO [<#=Table #>] (<#
    counter = 1;
    foreach (TableDescription item in Columns)
    {
        if (item.Status != 0x80)
        {
            if (counter > 1)
                #>, <#
            if (counter % 5 == 0)
                #><#= "\r\n    " #><#
            counter++;
            #>[<#= item.ColName #>]<#
        }
    }
#>)
VALUES (<#
    counter = 1;
    foreach (TableDescription item in Columns)
    {
        if (item.Status != 0x80)
        {
            if (counter > 1)
                #>, <#
            if (counter % 5 == 0)
                #><#= "\r\n    " #><#
            counter++;
            #>@<#= item.ColName #><#
        }
    }
#>)

SET @<#
    foreach (TableDescription item in Columns)
    {
        if (item.Status == 0x80)
        {
            #><#=item.ColName #><#
            break;
        }
    }
#> = @@IDENTITY

Inline Write

This style uses code blocks that contain Write(), WriteLine(), PushIndent(), PopIndent(), and other statements.

This approach is more abstract that Embed, it is easier for me to read than Embed or String Function.

<#@ template language="C#v3.5" debug="true" #>
<#@ output extension="sql" #>
<#@ include file="TableDescriptionUtilCS.tt" #>
<# 
    string ConnectionString = @"Data Source=localhost\SQLEXPRESS;" +
        Initial Catalog=Northwind;Integrated Security=True";
    string Table = "Employees";

    TableDescriptionUtil tdu = new TableDescriptionUtil();
    List<TableDescription> Columns = tdu.GetTableDescription(ConnectionString, Table);

    int counter;
#>
CREATE PROCEDURE [dbo].[Insert<# Write(Table); #>]
<#
    PushIndent("    ");
    counter = 1;
    foreach (TableDescription item in Columns)
    {
        Write("@" + item.ColName + " " + item.FullTypeName);
        if (item.Status == 0x80)
            Write(" OUT");
        if (item.ColIsNullable != 0)
            Write(" = NULL");
        if (counter < Columns.Count)
            WriteLine(",");
        counter++;
    }
    PopIndent();
#>


AS

INSERT INTO [<# Write(Table); #>] (<#
    PushIndent("    ");
    counter = 1;
    foreach (TableDescription item in Columns)
    {
        if (item.Status != 0x80)
        {
            if (counter > 1)
                Write(", ");
            if (counter % 5 == 0)
                WriteLine(String.Empty);
            counter++;
            Write(string.Format("[{0}]", item.ColName));
        }
    }
    PopIndent();
#>)
VALUES (<#
    PushIndent("    ");
    counter = 1;
    foreach (TableDescription item in Columns)
    {
        if (item.Status != 0x80)
        {
            if (counter > 1)
                Write(", ");
            if (counter % 5 == 0)
                WriteLine(string.Empty);
            counter++;
            Write(String.Format("@{0}", item.ColName));
        }
    }
    PopIndent();
#>)

SET @<#
    foreach (TableDescription item in Columns)
    {
        if (item.Status == 0x80)
        {
            Write(item.ColName);
            break;
        }
    }
#> = @@IDENTITY

String Function

Here I call a function in an expression block that returns a bit of generated code as a string. The functions are in feature blocks (or include file, linked assemblies, etc.).

Since I did some primitive string based code gen for a number of years, I find myself relying on this approach. This approach can lead to code just as ugly as the Embed approach. This approach is good for adapting old fashioned code gen to T4.

<#@ template language="C#v3.5" debug="true" #>
<#@ output extension="sql" #>
<#@ include file="TableDescriptionUtilCS.tt" #>
<# 
    string ConnectionString = @"Data Source=localhost\SQLEXPRESS;" + 
        "Initial Catalog=Northwind;Integrated Security=True";
    string Table = "Employees";

    TableDescriptionUtil tdu = new TableDescriptionUtil();
     List<TableDescription> Columns = tdu.GetTableDescription(ConnectionString, Table);

     int counter;
#>
CREATE PROCEDURE [dbo].[Insert<#= getTableName(Table) #>]
<#= getParameterList(Columns) #>

AS

INSERT INTO [<#= getTableName(Table) #>] (<#= getFieldNames(Columns) #>)
VALUES (<#= getValueNames(Columns) #>)


SET @<#=getIdentityFieldName(Columns) #> = @@IDENTITY
<#+
    string getTableName(string table)
    {
        // Yes, this is hard core overkill
        return table;
    }
    string getParameterList(List<TableDescription> columns)
    {
        string rVal = string.Empty;
        int counter = 1;
        foreach (TableDescription item in columns)
        {
           rVal += "    @" + item.ColName + " " + item.FullTypeName;
           if (item.Status == 0x80)
               rVal += " OUT";
           if (item.ColIsNullable != 0)
               rVal += " = NULL";
           if (counter < columns.Count)
               rVal += ",\r\n";
           counter++;
       }
       return rVal;
    }
    string getFieldNames(List<TableDescription> columns)
    {
        string rVal = string.Empty;
        int counter = 1;
        foreach (TableDescription item in columns)
        {
            if (item.Status != 0x80)
            {
                if (counter > 1)
                    rVal += ", ";
                if (counter % 5 == 0)
                    rVal += "\r\n    ";
                counter++;
                rVal += string.Format("[{0}]", item.ColName);
           }
        }
        return rVal;
    }
    string getValueNames(List<TableDescription> columns)
    {
        string rVal = string.Empty;
        int counter = 1;
        foreach (TableDescription item in columns)
        {
        if (item.Status != 0x80)
        {
            if (counter > 1)
                rVal += ", ";
            if (counter % 5 == 0)
                rVal += "\r\n    ";
            counter++;
            rVal += string.Format("@{0}", item.ColName);
        }
    }
    return rVal;
    }
    string getIdentityFieldName(List<TableDescription> columns)
    {
        string rVal = string.Empty;
        foreach (TableDescription item in columns)
        {
            if (item.Status == 0x80)
            {
                rVal =item.ColName;
                break;
            }
        }
        return rVal;
    }
#>

Write Function

Here I call a function to generate code a block of code with Write functions.

Unlike the String Function approach, the code generation is relying on a side effect of the function, so this approach isn’t functional.

<#@ template language="C#v3.5" debug="true" #>
<#@ output extension="sql" #>
<#@ include file="TableDescriptionUtilCS.tt" #>
<# 
    string ConnectionString = @"Data Source=localhost\SQLEXPRESS;" + 
        "Initial Catalog=Northwind;Integrated Security=True";
    string Table = "Employees";

    TableDescriptionUtil tdu = new TableDescriptionUtil();
    List<TableDescription> Columns = tdu.GetTableDescription(ConnectionString, Table);

    int counter;
#>
CREATE PROCEDURE [dbo].[Insert<# writeTableName(Table); #>]
<# writeParameterList(Columns); #>

AS

INSERT INTO [<# writeTableName(Table); #>] (<# writeFieldNames(Columns); #>)
VALUES (<# writeValueNames(Columns); #>)


SET @<# writeIdentityFieldName(Columns); #>  = @@IDENTITY
<#+
    void writeTableName(string table)
    {
        Write(table);
    }
    void  writeParameterList(List<TableDescription> columns)
    {
        PushIndent("    ");
        int counter = 1;
        foreach (TableDescription item in columns)
        {
            Write("@" + item.ColName + " " + item.FullTypeName);
            if (item.Status == 0x80)
                Write(" OUT");
            if (item.ColIsNullable != 0)
                Write(" = NULL");
            if (counter < columns.Count)
                WriteLine(",");
            counter++;
        }
        PopIndent();
    }
    void writeFieldNames(List<TableDescription> columns)
    {
         PushIndent("    ");
         int counter = 1;
         foreach (TableDescription item in columns)
         {
             if (item.Status != 0x80)
             {
                 if (counter > 1)
                     Write(", ");
                 if (counter % 5 == 0)
                     WriteLine(String.Empty);
                 counter++;
                 Write(string.Format("[{0}]", item.ColName));
             }
         }
         PopIndent();
    }
    void writeValueNames(List<TableDescription> columns)
    {
        PushIndent("    ");
        int counter = 1;
        foreach (TableDescription item in columns)
        {
            if (item.Status != 0x80)
            {
                if (counter > 1)
                    Write(", ");
                if (counter % 5 == 0)
                    WriteLine(string.Empty);
                counter++;
                Write(String.Format("@{0}", item.ColName));
            }
        }
        PopIndent();
    }
    void writeIdentityFieldName(List<TableDescription> columns)
    {
        foreach (TableDescription item in columns)
        {
            if (item.Status == 0x80)
            {
                Write(item.ColName);
                break;
            }
        }
    }
#>

Include File: TableDescriptionUtil.tt

I included this file in all the examples above.

<#@ assembly name="System.Data.dll" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="Microsoft.VisualStudio.TextTemplating" #>
<#+
public class TableDescriptionUtil : TextTransformation
{
    /// <remarks>
    /// I don't actually call this, but it is necessary for T4 Templates to work
    /// </remarks>
    public override string TransformText()
    {
        return "";
    }
    /// <summary>
    /// Gets a DataReader containing selected column information for all of the
    /// columns in the indecated table from SQL Server System Tables
    /// </summary>
    /// <param name="tableName">Get information on THIS table</param>
    /// <param name="conn">An existing open connection to be used </param>
    /// <returns>A DataReader pointing to the results</returns>
    private SqlDataReader GetTableDefReader(string tableName , SqlConnection conn )
    {
        SqlDataReader reader;// = new SqlDataReader();
        string getDataForTable = 
             " SELECT c.name AS col_name, " +
             "   t.name AS type_name, " +
             "   CAST(c.length AS INT) AS length, " +
             "   c.isnullable, " +
             "   CAST(c.status AS INT) AS status " +
             " FROM " +
             "  sysObjects o " +
             "  JOIN sysColumns c ON c.id = o.id " +
             "  JOIN systypes t on c.xtype = t.xtype AND t.status = 0 " +
             " WHERE o.name = @tableName " +
             " ORDER BY c.colid ";
        using (var cmd = new SqlCommand(getDataForTable, conn))
        {
            cmd.Parameters.AddWithValue("@tableName", tableName);
            reader = cmd.ExecuteReader();
        }
        return reader;
    }

    /// <summary>
    /// Gets a List of column descriptions for a given table
    /// </summary>
    /// <param name="ConnectionString">Connection string to the database</param>
    /// <param name="Table">The name of the table to describe</param>
    /// <returns>A list of column descriptions</returns>
    public List<TableDescription> GetTableDescription(string ConnectionString, 
         string Table) 
    {
        SqlDataReader reader;
        List<TableDescription> Columns = new List<TableDescription>();
        using (var conn = new SqlConnection(ConnectionString))
        {
            conn.Open();
            reader = GetTableDefReader(Table, conn);
            while (reader.Read())
            {
                TableDescription Column = new TableDescription();
                Column.ColName = reader.GetString(0);
                Column.TypeName = reader.GetString(1);
                Column.ColLength = reader.GetInt32(2);
                Column.ColIsNullable = reader.GetInt32(3);
                Column.Status = reader.GetInt32(4);
                Column.FullTypeName = GetTypeName(Column.TypeName, Column.ColLength);
                Columns.Add(Column);
            }
            conn.Close();
        }
        return Columns;
    }
    /// <summary>
    /// Gets the TypeName as it should appear in a SP Parameter List
    /// </summary>
    /// <param name="TypeName"></param>
    /// <param name="ColLength"></param>
    /// <returns>The processed Type Name as a string</returns>
    private string GetTypeName(string typeName , int ColLength )
    {
        switch (typeName.ToLower())
        {
        case "varchar":
        case "nvarchar":
            string lenString  = ColLength.ToString();
            if (ColLength == -1 )
                lenString = "MAX";
            return string.Format("{0}({1})", typeName, lenString);
        default:
            return typeName;
        }
    }
}
public class TableDescription
{
    public string ColName {get; set;}
    public string TypeName {get; set;}
    public string FullTypeName {get; set;}
    public int ColLength {get; set;}
    public int Status {get; set;}
    public int ColIsNullable {get; set;}
}
#>

Wednesday, February 02, 2011

Happy Groundhog Day!

Traditionally we have pork sausage for dinner. I leave it as an exercise to the reader to work out the bad pun.

Sunday, January 23, 2011

Referencing Assemblies: T4 Gotcha

When I run T4 Templates through Visual Studio 2008, I don't have to give the full path to reference GAC assemblies:

<#@ assembly name="System.Data.dll" #>

But when I run the template in my own executable using Microsoft.VisualStudio.TextTemplating.Engine, I have problems.

If the Template code is in VB.NET, I get:

vbc : Command line (0,0) : error BC2006: Compiling transformation: option 'r' requires ': <file_list>'

If the Template code is in C#, I get:

c:\Users\jacks\AppData\Local\Temp\rnftuopt.0.cs(3,18) : error CS0234: Compiling transformation: The type or namespace name 'Data' does not exist in the namespace 'System' (are you missing an assembly reference?)

To fix this, I referenced the full path of the assembly.

<#@ assembly name="C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll" #>

NOTE: This doesn't seam to apply to non-GAC assemblies; I have noticed that I don't have give the full path of my own assemblies as long as they are in the same folder as my executable.

Friday, January 14, 2011

Fulfilled my contract with AT&T

Just over a two years ago, I bought an iPhone 3G (no S).

In Spokane, I haven't have any problems AT&T. But Spokane is the first place named in AT&T's Postcard Commercial in 2009, so AT&T knows that we get better than average service here in Spocompton.

If I was to stay in Spokane, I would probably be safe getting an upgrade from AT&T. The trouble is that I am working month to month as a temp and I don't know where I will be working if/when my current contract runs out.

I've hear horror stories of bad service in real cities like New York or Chicago. And Verizon will offer the iPhone 4 next month; some say that AT&T's problems are caused by the popularity of the iPhone and Verizon will degrade too.

I think I will stay with my antique phone for awhile.

Wednesday, January 05, 2011

Running FTP.EXE from C#

NOTE: Ultimately, I didn't go with this solution.

Since I need to log on to various FTP servers with various security configurations and FTP.EXE seams to figure out these configurations and the free FTP libraries I’ve played with can’t I have decided to wrap FTP.EXE with C#.

Telling it what to do

I wanted to avoid sending my commands to FTP.EXE via command files because I would have to dynamically write text files and make sure that they are secure. System.Diagnostics.Process allows me to send commands to its StandardInput stream, but I can’t figure out how to make it accept passwords. To solve these problems, I log in using command files (which I can secure and only allow myself and the program to see, and then write the rest of the commands to the StandardInput stream.

The Command File

The command file needs to reside in a directory with no spaces and the file needs to be saved using ANSI encoding. Here I open a server and login, the rest is up to the commands that I send to the stream.

open localhost
username
password

Sample Application

This simple console application demonstrates using the command file to log in and StandardInput to send a List command (and Quit).

using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;

namespace DemoFtp
{
    class Program
    {
        private const string FTP_EXE_LOCATION = @"C:\Windows\System32\ftp.exe";
        private const string DEFAULT_COMMAND_FILE_LOCATION = @"C:\temp\localhost.txt";

        static void Main(string[] args)
        {
            try
            {
                string commandFileLocation = getCommandFileLocation(args);

                var commands = new List<string>();
                commands.Add("ls -la");
                commands.Add("quit");   // Don't forget to log out!
                string output = RunWithResponses(
                    FTP_EXE_LOCATION, 
                    string.Format("-s:{0}", commandFileLocation), 
                    commands);
                Console.WriteLine(output);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.ReadKey();
        }

        private static string getCommandFileLocation(string[] args)
        {
            string commandFileLocation;
            if (args.Length > 0)
                commandFileLocation = args[0];
            else
                commandFileLocation = DEFAULT_COMMAND_FILE_LOCATION;
            return commandFileLocation;
        }

        public static string RunWithResponses(string exeName, string argsLine,
            List<string> commands)
        {
            StreamReader outputStream = StreamReader.Null;
            StreamReader errorStream = StreamReader.Null;
            StreamWriter inputStream = StreamWriter.Null;
            string output = string.Empty;
            try
            {
                // Setup the process
                Process newProcess = new Process();
                newProcess.StartInfo.FileName = exeName;
                newProcess.StartInfo.Arguments = argsLine;
                newProcess.StartInfo.UseShellExecute = false;
                newProcess.StartInfo.CreateNoWindow = true;
                newProcess.StartInfo.RedirectStandardInput = true;
                newProcess.StartInfo.RedirectStandardOutput = true;
                newProcess.StartInfo.RedirectStandardError = true;
                newProcess.Start();
                inputStream = newProcess.StandardInput;
                outputStream = newProcess.StandardOutput;
                errorStream = newProcess.StandardError;
                inputStream.AutoFlush = true;

                // Issue My commands
                foreach (string command in commands)
                {
                    inputStream.WriteLine(command);
                }

                // Get the rsults and wait for it to end
                output = outputStream.ReadToEnd();
                newProcess.WaitForExit();
            }
            finally
            {
                // Clean up
                if (outputStream != StreamReader.Null)
                    outputStream.Close();
                if (errorStream != StreamReader.Null)
                    errorStream.Close();
                if (inputStream != StreamWriter.Null)
                    inputStream.Close();
            }
            return output;
        }
    }
}