Cloning a temp table schema in SQL

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/cloning_a_temp_table_schema_in_sql.htm]

SQL temp tables can be a functional and performance life-saver because they let you store intermediate results. You can then do resource-heavy calculations on this smaller result set, or just break a complex procedure into multiple, easier-to-handle parts.  Sometimes you'll have two temp tables with the same schema. SQL provides as easy way to clone a temp table (thanks to our Paylocity SQL architect for showing me this). Here's a sample:

CREATE TABLE #Main1 --prePaging
(
  colInt int,
  colString varchar(20)
)

Insert Into #Main1 Values(10, 'aaa')
Insert Into #Main1 Values(20, 'bbb')
Insert Into #Main1 Values(30, 'ccc')

--Dynamically create a close of #Main1, call it #Main2
--This doesn't touch data
Select * Into #Main2 From #Main1 Where 1=2

--show that Main2 exists, and has the right schema
Select * From #Main1
Select * From #Main2

--Now insert some data into Main2
Insert Into #Main2 (colInt, colString) Values(40, 'ddd')
Select * From #Main2

drop table #Main1
drop table #Main2

The key is the line "Select * Into #Main2 From #Main1 Where 1=2", which dynamically creates table Main2 and sets its columns equal to that of Main1.

 

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

Easily insert huge amounts of test data

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/easily_insert_huge_amounts_of_test_data.htm]

I've plugged the free MassDataHandler tool before - an open source tool that lets you use XML script to easily insert data. One limitation of the tool (perhaps to be solved in future releases) is the inability to easily insert mass amounts of data.

 

Say you want a table to have 10,000 rows so you can do some SQL performance tuning. Ideally you could specify some algorithm to dictate the number of rows and how each row of data is unique. For example, you may want to say "Insert 10,000 rows of company data, using different company names of the form 'co' + i.ToString(), such as co1, co2, co3, etc...".

 

You can easily do this. First you could use the MDH to insert the parent data. Then for specific high-volume tables, you could use the SQL while loop to specify the insert strategy, like so:

Declare @i int select @i = 1  WHILE (@i <= 10000) BEGIN    --Define the dynamic data to insert   Declare @co varchar(10)   select @co = 'co' + cast(@i as varchar(4))    --Do the SQL insert   Insert into MyTable (    [co],     [SomeColumn] )   Values(    1,     @co,     'someData' );    --increment the counter   select @i = (@i + 1) END

The would quickly insert 10,000 rows of test data into MyTable. You could customize the technique for other tables, adding multiple inserts in the loop, or adjusting for a multi-column unique index.

 

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

Migrating legacy apps to Asp.net AJAX

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/migrating_legacy_apps_to_aspnet_ajax.htm]

We've all heard how awesome Ajax is, so the practical question is how do I start using it? It's easy enough if you're building an app from scratch, but it's always harder when migrating a legacy app. Here are some things I've discovered while trying to migrate a 4-year ASP 2.0 web app (which was itself migrated from .Net 1.1).

 

Step 1: Update the web.config. Because a legacy app probably already has a big config, you'll need to merge sections in. While you can get the full web.config file from creating a new Ajax app, I've copied it here for convenience and greyed out the nodes likely to already exist. The relevant sections are

• configSection.sectionGroup

• page.controls

• compilation.assemblies

• httpHandlers

• httpModules

• system.web.extensions

• system.webServer

xml version="1.0"?>
<configuration>
    <configSections>
        <sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
            <sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
                <section name="scriptResourceHandler" type="System.Web.Configuration.ScriptingScriptResourceHandlerSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
                <sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35">
                    <section name="jsonSerialization" type="System.Web.Configuration.ScriptingJsonSerializationSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="Everywhere"/>
                    <section name="profileService" type="System.Web.Configuration.ScriptingProfileServiceSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
                    <section name="authenticationService" type="System.Web.Configuration.ScriptingAuthenticationServiceSection, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="false" allowDefinition="MachineToApplication"/>
                sectionGroup>
            sectionGroup>
        sectionGroup>
    configSections>
    <system.web>
        <pages>
            <controls>
                <add tagPrefix="asp" namespace="System.Web.UI" assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            controls>
        pages>
       
        <compilation debug="true">
            <assemblies>
                <add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            assemblies>
        compilation>
        <httpHandlers>
            <remove verb="*" path="*.asmx"/>
            <add verb="*" path="*.asmx" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            <add verb="*" path="*_AppService.axd" validate="false" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            <add verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" validate="false"/>
        httpHandlers>
        <httpModules>
            <add name="ScriptModule" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
        httpModules>
    system.web>
    <system.web.extensions>
        <scripting>
            <webServices>
               
               
               
               
               
               
            webServices>
           
        scripting>
    system.web.extensions>
    <system.webServer>
        <validation validateIntegratedModeConfiguration="false"/>
        <modules>
            <add name="ScriptModule" preCondition="integratedMode" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
        modules>
        <handlers>
            <remove name="WebServiceHandlerFactory-Integrated"/>
            <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            <add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
            <add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"/>
        handlers>
    system.webServer>
configuration>
 

 

Step 1b. Beware of the web.config node: <xhtmlConformance mode="Legacy" />. This is inserted by the ASP.Net 1.1 to 2.0 migration. Setting this to legacy will mess up the HTML output that Ajax needs. ScottGu explains more here. If you have this node, you'll want to set it to "Transitional", however that could likely break a lot of your javascript. Note that as an intermediate step, you can still switch this node to "Transitional" in preparation for migrating to Ajax sometime in the future.

 

Step 2: Ensure that the System.Web.Extensions assembly is correctly referenced by your app. The MS Ajax download installs this in the GAC. You may need to copy it locally depending on how your application deploys.

 

 

It's really that easy. I was concerned that there would be some nuance - you'd need to modify some hidden file in a hidden folder, each page would need tweaking, or it just wouldn't work. I was pleasantly surprised how easy it was to migrate.

 

Once you have an ASP.Net Ajax app, then you can start using all the wonderful AJAX things like the amazing update panel, or the Ajax controls.

 

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

 

The difference between array and ref array

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/the_difference_between_array_and_ref_array.htm]

Sometimes you'll want to pass an object (like an array) into a method, and have that method update the object. For an array, the common ways to do this are using the ref keyword, or modifying a member of an array. It's easy to confuse these two approaches because if you're just updating a member, they appear to have the same affect. However they're actually fundamentally different - passing in an array by ref lets you modify the array reference itself, such as changing it to a new array with a new length. The code snippet below illustrates this:

 

 

    #region Normal Array

    [TestMethod]
    public void TestMethod1()
    {
      //Normal array changes individual member
      string[] astr = new string[]{"aaa"};
      ModifyArray1(astr);
      Assert.AreEqual("bbb", astr[0]);
    }

    [TestMethod]
    public void TestMethod2()
    {
      //Non-ref array doesn't change array itself
      string[] astr = new string[] { "aaa" };
      ModifyArray2(astr);
      Assert.AreEqual(1, astr.Length);
      Assert.AreEqual("aaa", astr[0]);
    }

    public static void ModifyArray1(string[] astr)
    {
      astr[0] = "bbb";
    }

    public static void ModifyArray2(string[] astr)
    {
      astr = new string[] { "ccc", "ccc" };
    }

    #endregion

    #region Ref Array

    [TestMethod]
    public void TestMethodRef1()
    {
      string[] astr = new string[] { "aaa" };
      ModifyArrayRef1(ref astr);
      Assert.AreEqual("bbb", astr[0]);
    }

    [TestMethod]
    public void TestMethodRef2()
    {
      //Ref array can change the array itself, like giving it a new length
      string[] astr = new string[] { "aaa" };
      ModifyArrayRef2(ref astr);
      Assert.AreEqual(2, astr.Length);
      Assert.AreEqual("ccc", astr[0]);
    }

    public static void ModifyArrayRef1(ref string[] astr)
    {
      astr[0] = "bbb";
    }

    public static void ModifyArrayRef2(ref string[] astr)
    {
      astr = new string[] { "ccc", "ccc" };
    }

    #endregion

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

More: How normal life experience helps you better understand software (Part II)

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/more_how_normal_life_experience_helps_you_better_understand.htm]

I mentioned in my previous post how normal life (i.e. things besides software) can help you better understand software engineering because it sometimes explains concepts in a more easily-understood context. Here are some more examples:

• Reuse - Developers are notorious for avoiding code reuse. However, in the real world, we wouldn't dream of constantly re-inventing the wheel. For example, you'd go to the hardware store to get standard size nails and bolts (as opposed to smelting your own metal), you'd fuel your car at a standard gas station (as opposed to using your own processed fuel), or you'd buy furniture (instead of growing your own wood, cutting the pieces, and assembling things yourself). While there are exceptions, generally we reuse standard things. Entire franchises and industries exist to provide us those things. The point is that there simply isn't enough time or resources to do everything from scratch. Same thing applies to software engineering.
• Demand for simplicity and reliability. When you see a light switch, you expect a standard behavior - simply switch on or off, with perhaps an intermediate state to dim the lights. You just want to take the light switch for granted and move onto other more important things. What you don't want is to constantly need to tinker with it and "hope" that it works. Same thing with software. People expect our software to just work, so that they can move on to their important tasks. So much software is like a broken light switch - you need to tinker with the interface, tweak the config file, add an external dependence, maybe recompile something, etc... Ideally, you can just run a simple install script (I like using MSBuild to automate all those tasks) and then take it for granted.
• Resources limit you - In normal life, things cost money, and that limits us. If you want twice as much food for a party, you pay more for it. In programming it's easy to ignore the cost of resources (like disk space or CPU cycles) because the machine is so fast and the program is usually developed on a dev machine with a light load where the cost of resources is easily ignored. The obvious problem is that when the code goes into production, and there's literally 1000x more demand, it can screech to a halt. Practically, we've all seen developers squander resources (like using tons of unnecessary, yet expensive database hits) in a way that you'd never do in other aspects of daily living.
• Get rid of  junk - Physical objects, like cars and furniture, eventually wear down and become junk. For example, you won't drive across the country in a car so worn that it could leave you stranded. While you may be able to salvage spare parts or something, it's time to move on. Code is the same way. Some code, via run-away bug lists, flawed design, obsolete technology, or obsolete purpose (business requirements totally change), essentially becomes junk. While parts may still be salvageable, bad code becomes a millstone hanging around your neck, and it may be best to move on (i.e. overhaul, rewrite, use new technologies). The problem is that many developers are emotionally attached to their code, and would rather sink with it than cast it away.

  ---

  Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

How normal life experience helps you better understand software

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/how_normal_life_experience_helps_you_better_understand_softw.htm]

There are some things in software engineering that are hard to explain, or hard to convince others to do. One benefit of normal life experience, i.e. things besides programming, is that they can sometimes more effectively explain those difficult concepts by emphasizing them in a different context.

Most excuses for bad code come from "I don't have time to do it properly", or "It's just throw-away code for my own personal use, it will never be used in production." Yeah right. There are so many developer bad practices with analogies to normal-day life activities that show how silly these excuses are.

For example, here's a partial list

• Bad Labeling - Many developers give their variables and methods useless names, such as "x1" or "DoEverything()". But we label things in normal life, like our luggage that we check in at the airport, or boxes when moving houses. Imagine how silly (and time-consuming) it would be to refuse to label your luggage because "you don't have time - I'll just look for the black suitcase".
• Packaging and a clean contract - lots of code has messy contracts - it's not clear how to call the code, or where the code's responsibility ends and the consumer's begins. Apply this to moving houses - the contract is clear - you put things in designated moving boxes (packing them within those boxes however you see fit), and the movers haul them to the new location. Imagine the mess if you "didn't have time" to pack the boxes. Some movers will still do it for you, but it will cost a lot more.
• Kicking off  a process - a lot of developers program only in series. But in real life we often kick something off while we go do another thing - for example with chores like starting the dishwasher, letting things dry or melt, or letting plants grow. Once you kick these things off, they're easy to maintain. But if you wait until one such task is finished before starting the next, you'll never get all the chores done.
• The cost of failure - in most engineering practices, failure can be devastating. If your car breaks down on the highway, it's bad. In civil engineering, a failure in a bridge or building could cause the entire structure to collapse and cost lives and tens of millions of dollars. In software engineering, a lot of developers don't really account for potential failure (error checking code, security flaws, bad logic, etc...). Software has errors for several reasons, including that software engineering is still relatively new and people are still amazed that software actually works, management doesn't want to pay to ensure that program works, or because it's just hard making something be solid. Either way, in software engineering it can be easy to ignore the cost of failure, but this can be much clearer in other fields.
• The need for peer review - In most daily activities you'd ask for help if something is complicated, whether it's asking for directions while driving, or how to fix an appliance in your house. However, it still amazes me that many management teams develop incredibly complex applications, but don't want to "waste" time reviewing that error-prone work. It's almost as if some teams spend more time reviewing how to fix their $50 toaster than how to check their $500,000 software application.

Experience in software engineering is great, but there are some concepts that are just really easy for some people to understand outside of a software-engineering context. Once understood, they can then re-apply to their engineering discipline.

---

Living in Chicago and interested in working for a great company? Check out the careers at Paylocity.

Using CodeSmith to create your own Domain-Specific-Language

[This was originally posted at http://timstall.dotnetdevelopersjournal.com/using_codesmith_to_create_your_own_domainspecificlanguage.htm]

Using CodeSmith to create your own Domain-Specific-Language

Yesterday I mentioned about software factories and domain-specific languages (DSL). A DSL is just that - working at a higher level language that maps to a specific problem domain instead of constantly re-inventing the wheel with a lower-level language. Some common examples of DSLs are:

• SQL
• Regular Expressions
• XPath

Each of these could be achieved by coding in a "low level" language like C#, but you wouldn't think of doing that because it'd be too slow and error prone. It's so easy to use each language because it maps naturally to what you're trying to do in that domain.

This same concept applies to application development. For example, an application has different domains:

• Initial data for your application (like security settings, roles, out-of-the-box dropdown values, etc...) - usually achieved with lots of custom SQL scripts.
• UI formatting - usually achieved with tons of table or CSS references, or highly-refactored controls
• Validation
• Data access code

Each of these can have their own DSL, which you could easily create using a code-generator like CodeSmith. You could abstract the concepts to an XML schema, and then use CodeSmith as a "Compiler" to transform that xml into the appropriate output (sql, html, or C# code files). CodeSmith's out-of-the-box XmlProperty feature, along with text based templates and huge online community make it very easy to do.

For example, instead of having tons of custom SQL scripts for your security data, you may have a hierarchal XML file that (1) is completely refactored and maps directly to the business needs (something potentially impossible with a procedural language like SQL), (2) can be easily validated via the XML schema and CodeSmith checks, and (3) is much easier to track version history on.

Microsoft offers their own DSL toolkit, but I think it doesn't yet compete with CodeSmith because the MS DSL toolkit: (1) requires you to learn a whole new GUI syntax (whereas CodeSmith is intuitive C# and Xml templates), (2) seems limited in what it can generate, and (3) screws up Visual Studio by inserting a new reference (or something like that) into every project.

Once you start code-generating tedious stuff, such as using your own DSL, you'll never go back.

---

Living in Chicago and interested in a great company? Check out the careers at Paylocity.