Loving the Lambda SelectMany() method

Lambda is simply a wonderful addition to C#. With it I can iterate and build expression trees from simple to complex. What's more, they perform so very well. Sometimes I prefer the "readable" LINQ syntax, but most often I fall back to the lambda superset.

Today, I stumbled on a new (new to me) method: SelectMany(). MSDN defines it cryptically like this: "Projects each element of a sequence to an IEnumerable<(Of <(T>)>) and combines the resulting sequences into one sequence."

Here's how you might use it:

var _Offspring = _Context.Users
   .SelectMany(x => x.Offspring).Distinct();

That does something VERY difficult. Each User has a nested list of offspring. Using SelectMany() we create a new result list with a distinct list of offspring that is derived from the outer User list.

Astounding.

A better way to remove time from a SQL DateTime data type

'1/1/2000 1:30 AM' != '1/1/2000 1:31 AM'

Every database developer has had to remove the time from a datetime value once. Comparing dates with time included regularly renders false results. The most common approach is something like this:

DECLARE @x datetime
SET @x = GETDATE()
SET @x = CONVERT(varchar, @x, 101)

This approach does not actually remove the time from the datetime. It just moves it to midnight. To be fair, this is the best option SQL develoeprs have had before the DATE data type in SQL 2008.

Recently I discovered a second approach to removing time from a datetime value. The technique finds the distance from midnight, the moves the time back to midnight. The syntax looks like this:

DECLARE @x datetime
SET @x = GETDATE()
SET @x = DATEADD(d, DATEDIFF(d, 0, @date), 0)

What's bad about this technique is that it is hard to remember and type from the top of your head (and get right the first time). What's good about this approach, and surprising to me, is that it is the fastest technique - probably because it does not have to handle a conversion.

I tested this to make sure. Here's the test script:

DECLARE @Table TABLE
   (
   Date1 datetime
   ,Date2 datetime
   ,Date3 date
   )

DECLARE @i int
SET @i = 0
WHILE (@i < 1000000)
  BEGIN
    INSERT INTO @Table
    SELECT GetDate() + @i, null, null
    SET @i = @i + 1
  END
      
DECLARE @Timer datetime

SET @Timer = getdate()
UPDATE @Table SET Date2 = Date1, Date3 = Date1
PRINT 'Warm Up'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date2 = CONVERT(DATETIME, SUBSTRING(CONVERT(varchar, Date1), 1, 11))
PRINT '(English Culture only) Parse with SubString to DateTime'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date2 = CONVERT(varchar, Date1, 101)
PRINT 'Convert to Varchar 101 to DateTime'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date2 = CONVERT(DATE, Date1)
PRINT '(SQL2008 only) Convert from DateTime to Date to Datetime'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date3 = Date1
PRINT '(SQL2008 only) Convert from DateTime to Date'
PRINT DateDiff(ms, @Timer, getdate())

SET @Timer = getdate()
update @Table Set Date2 = DATEADD(d, DATEDIFF(d, 0, Date1), 0)
PRINT 'Use DateDiff then DateAdd'
PRINT DateDiff(ms, @Timer, getdate())

This takes about a minute to execute. It attempts many different types of conversion, including the ones that only SQL 2008 can support (remember that if you run it yourself).

Where I would have expected the classic VARCHAR(101) to perform well, it didn't. Not at all. The DATEADD technique nearly beats the Date data type in SQL 2008. Here are the crazy results:

Warm Up
14670

(English Culture only) Parse with SubString to DateTime
8796

Convert to Varchar 101 to DateTime
6250

(SQL2008 only) Convert from DateTime to Date to Datetime
4986

(SQL2008 only) Convert from DateTime to Date
4890

Use DateDiff then DateAdd
4936

Conclusion: all the techniques are within milliseconds of each other. It makes sense to code in a away that future developers can understand. If varchar(101) is eaiest in your shop, then keep on keeping on. But if you are targeting sheer performance, consider the DATEADD technique.

Our standard C# naming conventions

Everyone has their own coding standards. The most important thing is that you have standards – and if you are part of a team that you share and obey standards. Standards help developers on the team during development and during maintenance. Be consistent, and if you can – be smart about it.

The MSDN conventions are the source of my personal conventions. I thought I would share my preferences around case. This is how my developers standardize their code and a few of the rules we use. Just by looking I can come guess within 99% of exactly what objects are just by their case.

You might have your own preferences, but at least be consistent. This just happens to be what I like and use:

Example	Standard	What is it?
m_JerryNixon	m_ prefix, Pascal Case	Class member variable
_JerryNixon	_ prefix, Pascal Case	Local variable
JerryNixon	Simple Pascal case	Property
OnJerryNixon	On prefix, Pascal Case	Event
JerryNixons	Plural Pascal Case	Enumeration
JerryNixon()	Simple Pascal case	Method
jerryNixon	Simple Camel Case	Method parameter
JERRYNIXON	Upper case	Constant

Our overarching rule is – no abbreviating. And although there are a few more rules, I like to keep them slim. Developers like freedom.

Creating a simple Partitioned View over Horizontally Partitioned tables in SQL 2008.

This isn’t special to SQL 2008; it works in SQL 2000. Imagine a table with so many rows that query operations are slow.

The easiest solution is Horizontal Partitioning – splitting your table based on the value in one or more columns (like all of this year’s invoices go in Invoices_2009, etc.) And the easiest, and maybe the only, way to create Horizontally Partitioned tables is using a Partitioned View – a single view over the Partitioned tables that unions back to one table again.

You can commit all your SELECT, UPDATE, INSERT, and DELETE operations solely against the Partitioned View – even if your Partitioned tables are on separate files, databases, or even servers.  It’s all so stinking easy. There are a few tricks; but, this sample below should get you moving right along.

-- clean up any test artifacts
if object_id('test1') is not null drop table test1
if object_id('test2') is not null drop table test2
if object_id('test') is not null drop view test
GO
 



-- the partition column must have a CHECK and be part of the PRIMARY KEY
create table test1 (col1 int primary key check (col1 = 1), col2 varchar(50))
GO
 
-- the partition column must have a CHECK and be part of the PRIMARY KEY
create table test2 (col1 int primary key check (col1 = 2), col2 varchar(50))
GO
 
-- the partition view must use UNION ALL
create view test as 
    select * from test1 union all
    select * from test2
GO
 
-- add a record to each, let the view determine the correct table
insert into test select 1, 'one'
insert into test select 2, 'two'
GO
 
-- take a look, it's all partitioned correctly
select * from test1
select * from test2
GO
 
-- clear the table for another test
delete from test
-- insert into the first table,, like we did above
insert into test select 1, 'one'
-- now let the view move it to the other table with an update
update test set col1 = 2 where col1  = 1
GO
 
-- take a look, it's all moved like we want; magic
select * from test1
select * from test2

The 8 key responsibilities for Business Analysts on software projects

 

[edited 09/08/09]

 

The tension typical between developers and Business Analysts is mitigated with clear responsibilities. Sometimes, the developer *is* the Business Analyst; that’s not wrong, just uncommon on larger projects where the role is usually a dedicated resource.

Let’s consider the job of a dedicated Business Analyst (BA):

1. Extract: A BA determines the Requirements by extracting them from business (and government) policies and, when possible, the current or future end users.

1. Anticipate: A BA casts a vision to the Product Owner so she can anticipate Requirements that are not yet needed or have not yet been considered (like security).

1. Constrain: A BA constrains the user’s whims – functions geared to trends, individuals, or outdated processes – and focuses users on the core business needs.

1. Organize: A BA organizes disparate requirements into correlated categories for to manage and communicate Requirements with technical, left-brained resources.

1. Translate: A BA translates business Requirements into technical Requirements (not solutions); abstracting business complexity away from technical resources.

1. Safeguard: A BA safeguards the needs of business and system users in the development process by verifying functionality, accuracy, and completeness.

1. Simplify: A BA advocates simplicity all the time – especially in implementation, making the system useful but continually focusing on day-to-day ease of use.

1. Verify: A BA knows the use cases best. They advocate the users, verify the system against Requirements, and must reject implementations that don’t hit the target.

Someone discovered you can build the acrostic SAVE-COST. I didn’t plan for that, but it’s handy. Generally, I am on the developer side of the house. But when a BA handles those eight things well, we all just get along. If you know what I mean.

 

Have a nice day.

Implement an AJAX form in the MVC Framework

The MVC Framework (now in version 1.0) is Microsoft’s implementation of the MVC software pattern in ASP.Net.

Here, my model is using LINQ to SQL against my empty database called GLOSSARY with one table called TERMS with three columns (Id, Term, Definition). Hopefully, I have kept the scenario simple enough so you can follow me.

In this sample, I have one controller, one view, and one partial view. A partial view is basically an ASP.Net user control (even the same file extension). The partial is a list of terms. The view contains the partial and two identical forms allowing users to filter the list (one POST and one AJAX).

I hope this makes sense to you, because AJAX in MVC is pretty darn easy:

//
// GlossaryController.cs

public ActionResult Index()
{
    return View(Models.Term.SelectAll());
}

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Index(FormCollection collection)
{
    // used by non-AJAX form 
    string _SearchTerm;
    _SearchTerm = collection["SearchTerm"];
    // don't indicate the partial here, the view will call it
    return View(Models.Term.Where(x => x.Term.Contians(_SearchTerm)));
}

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Index2(FormCollection collection)
{
    // used by AJAX form 
    string _SearchTerm;
    _SearchTerm = collection["SearchTerm"];
    // you must indicate the partial here, it's all you want to render
    return View("Search", Models.Term.Where(x => x.Term.Contians(_SearchTerm)));
}

And now for the view(s):

// The view: Index.aspx

<!-- the non-AJAX form to filter the list -->
<% using (Html.BeginForm()) { %>
    <fieldset>
    <legend>Search</legend>
    <input name="SearchTerm" />
    <input type="submit" value="Go" />
    </fieldset>
<% } %>

<!-- the AJAX form to filter the list -->
<script src="/Scripts/MicrosoftAjax.js" type="text/javascript"></script>
<script src="/Scripts/MicrosoftMvcAjax.js" type="text/javascript"></script>
<% using (Ajax.BeginForm("Index2", new AjaxOptions { UpdateTargetId = "results" })) { %>
    <fieldset>
    <legend>Search</legend>
    <input name="SearchTerm" />
    <input type="submit" value="Go" />
    </fieldset>
<% } %>

<!-- render the partial inside the view -->
<div id="results">
    <% Html.RenderPartial("Search", Model); %>
</div>

// The partial: Search.ascx (a default LIST T4 template, nothing special/custom)

SQL Server 2008; fun tSQL syntax to shrink your queries

Here are most of the more basic syntax updates in SQL 2008. Enjoy.

-- old-school variables ;)

declare @x int
declare @y int
declare @z int

set @x = 1
set @y = 2
set @z = 3

-- combined initialization

declare 
     @a int
    ,@b int
    ,@c int

-- compound assigned

declare 
     @d int = 1
    ,@e int = 2
    ,@f int = 3

-- compound operator

set @i += 8
set @i -= 8
set @i /= 8
set @i *= 8
set @i %= 3

-- bitwise operators

set @i &= 1
set @i |= 1
set @i ^= 1

SQL Server 2008 new “grouping” tSQL candy

So many new features in SQL 2008. I presented on the updates at Trifecta a few months ago and never got around to posting some of my samples on my blog. Sorry about that if you were looking. Here’s the bit about grouping syntax. The code should create the sample table and data for you and run right away.

insert into Sales values
     (1999, 'Q1', 'Jerry', 123)
    ,(1999, 'Q2', 'Jerry', 234)
    ,(1999, 'Q3', 'Jerry', 345)
    ,(1999, 'Q4', 'Jerry', 456)

select * from
(
    values (1, 2), (2, 3), (3, 4)
) as DerivedTable(Col1, Col2)

-- ROLLUP = Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.

select
     Year
    ,AVG(Amount) as AverageSales
from 
    Sales
group by
    Year WITH ROLLUP

-- GROUPING = Indicates whether a specified column expression in a GROUP BY list is aggregated or not.

select
     Year
    ,AVG(Amount) as Average
    ,GROUPING(Year) as [YearRollUp?]
from 
    Sales
group by
    Year WITH ROLLUP

-- GROUPING SET = Specifies multiple groupings of data in one query. 

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING(Year) as [YearRollUp?]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter))

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING(Year) as [YearRollUp?]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter), (Year), ())

-- GROUPING_ID = Is a function that computes the level of grouping.

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING_ID(Year, Quarter) as [x]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter), (Year), ())

-- use it in the having

declare @level int = 1

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
    ,GROUPING_ID(Year, Quarter) as [x]
from 
    Sales
group by
    GROUPING SETS ((Year, Quarter), (Year), ())
having
    GROUPING_ID(Year, Quarter) = @level
    

-- CUBE = Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.

select
     Year
    ,Quarter
    ,AVG(Amount) as Average
from 
    Sales
group by
    cube(Year, Quarter, SalesPerson)    
order by
    Year, Quarter, Average

Passing a table-type parameter in ADO.Net

In SQL, you have always been able to create a custom type. But now you have the ability to create a custom type that is a TABLE type. With a TABLE type you literal define columns and column types. But you can then use this type as the input parameter type of a stored procedure. An age-old problem finally solved. How do you do it? I have a simple working demo below that shows the SQL and the ADO.Net code in C#. Happy coding!

// tSQL setup scripts
// create the SQL table

create table Bugs
(
     BugId int primary key
 identity(1,1)
    ,Title varchar(50)
    ,Details varchar(50)
)

// create the SQL table TYPE

create type BugsType as table
(
     Title varchar(50)
    ,Details varchar(50)
)

// create the SQL insert stored proc

create proc BugsInsert
(@x BugsType readonly) as
    set nocount on
    insert into Bugs 
        select * from @x

declare @p BugsType
insert into @p values
     ('Login Fails', 'Password rejected')
    ,('Login Fails', 'Username rejected')
exec BugsInsert @p

// C# // how to insert a table-type parameter using ADO.Net

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace TableValuedParameters
{
    public partial class Form1 : Form
    {
        DataTable m_DataTable = new DataTable();
        public Form1()
        {
            InitializeComponent();
            m_DataTable.Columns.Add(new DataColumn("Title", typeof(string)));
            m_DataTable.Columns.Add(new DataColumn("Details", typeof(string)));
            // you will need to add your own dataGridView to your form
            dataGridView1.DataSource = m_DataTable;
        }

        // this is the save button event handler (this form only inserts)
        private void button1_Click(object sender, EventArgs e)
        {
            string _ConnStr = @"server=.\sql2008express;database=jerry;integrated security=sspi;";
            using (SqlConnection _SqlConnection = new SqlConnection(_ConnStr))
            {
                _SqlConnection.Open();
                SqlCommand _SqlCommand = new SqlCommand("BugsInsert", _SqlConnection);
                _SqlCommand.CommandType = CommandType.StoredProcedure;
                // here's the magic, pass a datatable
                _SqlCommand.Parameters.AddWithValue("x", m_DataTable);
                _SqlCommand.ExecuteNonQuery();
            }
            this.Close();
        }
    }
}

LINQ to SQL Entity Base Class

So many things in LINQ to SQL are cool. But the handling of entities in a asp.net application drove me crazy. It was either the entity is already attached, different then the one in the database, or created with a different data context. So, I created my own base class that handles this and some other common issues with LINQ.

 

Let’s discuss.

 

The entities created by LINQ to SQL do not have a base class. As a result, it is VERY easy for them to inherit from anything, including my custom base class. Because the entity classes are already partial classes, all you need to do is create another class with the same name and the partial keyword. In addition, reference System.Runtime.Serialization. Done.

 

This is truly my gift to you. It took me a good 3 days to finally get all this working right. And the dynamic lambda stuff there (see the SelectOne() method) – that alone might make all this worth it. Nonetheless, it’s all yours. You’re welcome. :)

 

Here’s the code snippet to get you there (entity name = “Term”):

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Xml;
using System.Runtime.Serialization;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq.Expressions;
using System.Reflection;

namespace MvcApplication1
{
    public partial class Term : CustomEntityBase<Term, GlossaryDataContext, int>
    {
        static public IQueryable<Term> Search(string value)
        {
            return (Program.NewDataContext as GlossaryDataContext).Terms.Where(x => x.Word.Contains(value));
        }
    }

    #region Implementation

    // TODO: move to own file

    public class Program
    {
        static private object m_DataContext;
        static public object DataContext
        {
            get
            {
                if (m_DataContext == null)
                    m_DataContext = NewDataContext;
                return m_DataContext;
            }
        }
        static public object NewDataContext
        {
            get
            {
                return new GlossaryDataContext(); // custom connection string
            }
        }
    }

    // TODO: move to own file

    public interface ICustomEntitySimple
    {
        void Update();
        void Delete();
        void Insert();
    }

    // TODO: move to own file

    public interface ICustomEntity<E, C, T> : ICustomEntitySimple
    {
        string __KeyName
        {
            get;
        }
        T __KeyValue
        {
            get;
        }
        E Select(T key);
    }

    // begin base class

    // TODO: reference System.Runtime.Serialization

    [DataContract]
    public abstract class CustomEntityBase<E, C, T> : ICustomEntity<E, C, T>
        where E : class
        where C : System.Data.Linq.DataContext
    {
        public CustomEntityBase()
        {
        }

        public string __KeyName
        {
            get
            {
                if ((Program.DataContext as C).Mapping.GetTable(typeof(E)).RowType.IdentityMembers.Count == 0)
                    throw new Exception("No Primary Key set for entity");
                if ((Program.DataContext as C).Mapping.GetTable(typeof(E)).RowType.IdentityMembers.Count > 1)
                    throw new Exception("Compound Primary Key detected; not supported");

                string _KeyName;
                _KeyName = (Program.DataContext as C).Mapping.GetTable(typeof(E)).RowType.IdentityMembers[0].Name;
                if (string.IsNullOrEmpty(_KeyName))
                    throw new Exception("Primary Key property not found");

                return _KeyName;
            }
        }
        public T __KeyValue
        {
            get
            {
                PropertyInfo _KeyProperty;
                _KeyProperty = typeof(E).GetProperty(__KeyName);
                if (_KeyProperty == null)
                    throw new Exception("Primary Key property not found");

                return (T)_KeyProperty.GetValue(this, null);
            }
        }

        public static Table<E> SelectAll()
        {
            C _Context;
            _Context = Program.NewDataContext as C;

            Table<E> _Table;
            _Table = _Context.GetTable(typeof(E)) as Table<E>;

            return _Table;
        }

        public static E SelectOne(T key)
        {
            string _KeyName;
            _KeyName = (Program.DataContext as C).Mapping.GetTable(typeof(E)).RowType.IdentityMembers[0].Name;

            if (string.IsNullOrEmpty(_KeyName))
                throw new Exception("Primary Key property not found");

            // build query

            ParameterExpression _ParameterExpression;
            _ParameterExpression = Expression.Parameter(typeof(E), "x");

            MemberExpression _LeftExpression;
            _LeftExpression = MemberExpression.Property(_ParameterExpression, _KeyName);

            Expression _RightExpression;
            _RightExpression = Expression.Constant(key);

            BinaryExpression _BinaryExpression;
            _BinaryExpression = MemberExpression.Equal(_LeftExpression, _RightExpression);

            Expression<Func<E, bool>> _LambdaExpression;
            _LambdaExpression = Expression.Lambda<Func<E, bool>>
                    (
                        _BinaryExpression,
                        new ParameterExpression[] { _ParameterExpression }
                    );

            C _Context;
            _Context = Program.DataContext as C;

            Table<E> _Table;
            _Table = _Context.GetTable(typeof(E)) as Table<E>;

            return _Table.SingleOrDefault(_LambdaExpression);
        }

        public virtual E Select(T key)
        {
            return SelectOne(key);
        }

        #region Helpers

        private E DetatchedEntity
        {
            get
            {
                StringWriter _StringWriter;
                _StringWriter = new StringWriter();
                using (XmlWriter _XmlWriter = XmlWriter.Create(_StringWriter))
                {
                    DataContractSerializer _DataContractSerializer;
                    _DataContractSerializer = new DataContractSerializer(this.GetType());
                    _DataContractSerializer.WriteObject(_XmlWriter, this);
                }

                string _SerializedValue;
                _SerializedValue = _StringWriter.ToString();

                using (StringReader _StringReader = new StringReader(_SerializedValue))
                {
                    using (XmlReader _XmlReader = XmlReader.Create(_StringReader))
                    {
                        DataContractSerializer _DataContractSerializer;
                        _DataContractSerializer = new DataContractSerializer(this.GetType());

                        object _RawObject;
                        _RawObject = _DataContractSerializer.ReadObject(_XmlReader);

                        return (E)_RawObject;
                    }
                }
            }
        }

        private void Sync(E detachedEntity)
        {
            foreach (var _Property in this.GetType().GetProperties())
            {
                object _DetatchedValue;
                _DetatchedValue = _Property.GetValue(detachedEntity, null);
                if (_Property.CanWrite)
                    _Property.SetValue(this, _DetatchedValue, null);
            }
        }

        private void ProcessInsert(ref E entity)
        {
            PropertyInfo _DateCreated;
            _DateCreated = entity.GetType().GetProperty("DateCreated");
            if (_DateCreated != null)
                _DateCreated.SetValue(entity, DateTime.Now, null);
            ProcessUpdate(ref entity);
        }

        private void ProcessUpdate(ref E entity)
        {
            PropertyInfo _DateModified;
            _DateModified = entity.GetType().GetProperty("DateModified");
            if (_DateModified != null)
                _DateModified.SetValue(entity, DateTime.Now, null);
        }

        #endregion

        #region ICustomEntity

        public void Update()
        {
            C _Context;
            _Context = Program.NewDataContext as C;

            Table<E> _Table;
            _Table = _Context.GetTable(typeof(E)) as Table<E>;

            E _Entity = DetatchedEntity;
            ProcessUpdate(ref _Entity);
            _Table.Attach(_Entity);
            _Context.Refresh(RefreshMode.KeepCurrentValues, _Entity);

            _Context.SubmitChanges();
            Sync(_Entity);
        }

        public void Delete()
        {
            C _Context;
            _Context = Program.NewDataContext as C;

            Table<E> _Table;
            _Table = _Context.GetTable(typeof(E)) as Table<E>;

            E _Entity = DetatchedEntity;
            _Table.Attach(_Entity);
            _Table.DeleteOnSubmit(_Entity);

            _Context.SubmitChanges();
            Sync(_Entity);
        }

        public void Insert()
        {
            C _Context;
            _Context = Program.NewDataContext as C;

            Table<E> _Table;
            _Table = _Context.GetTable(typeof(E)) as Table<E>;

            E _Entity = DetatchedEntity;
            ProcessInsert(ref _Entity);
            _Table.InsertOnSubmit(_Entity);

            _Context.SubmitChanges();
            Sync(_Entity);
        }

        #endregion
    }

    #endregion
}

Connect to a SQL Database using Windows Account on different Domain

The problem is you can’t use windows authentication if you are not on the same domain. SSMS wants to pass your current windows credentials which, if you are not on the same domain, are not the correct ones. There’s a solution. It is (syntax shows SSMS 2008):

runas /netonly /user:northamerica\jnixon "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

Tip #3: Record life

A data warehouse may have multiple rows representing one row in the OLTP source.

This is because a warehouse can only provide analysts a record as of October 1, 2008 if and only if INSERT/UPDATE/DELETE operations result in unique record snapshots in the warehouse.

Time box prep-logic should execute as OLTP data is being initially processed in the warehouse from the temporary, intermediary copy/source.

Here's a sample, source OLTP table:

CREATE TABLE People
(
Id int primary key
,Name varchar(50)
)

Some modelers like to build their audit tables like this:

CREATE TABLE People
(
Id int
,Name varchar(50)
,CreatedOn datetime default getdate()
,Version int NOT NULL
)

I like to build my audit tables like this:

CREATE TABLE People
(
Id int primary key
,Name varchar(50)
,LiveFrom datetime default getdate()
,LiveTo datetime
)

The problem with the first approach is that it requires a considerable lookup to calculate the next version number. Another problem with the first approach is that time boxes require a double query to find the timely record and the test for the subsequent version.

The benefit of the second approach is that no lookup ever need occur. The problem with the second approach is that unchanged records require an audit table UPDATE to LiveTo. But, that's straight forward and the resulting queries using LiveFrom and LIveTo are quite simple.

I admit both approaches work. I just prefer the second.

Microsoft’s new Charting Control

At PDC (2008), Microsoft announced a Web Form and Win Form control for charting. That’s right, both! And it’s not a dumbed-down chart either. It’s part of an ongoing effort to extend BI to the end user.

Now, if I were a charting vendor, I would be totally angry with Microsoft. But because I am a developer, I finally have charting options without increasing project cost or complexity with a third party component.

Click here to see the download site on MSDN.

The TSQL to insert an image/blog

I recently needed this syntax.

If you want to insert a local file into a database image field (varbinary(max) field) then this is the syntax:

CREATE TABLE MyTable 
    (id int, image varbinary(max))

INSERT INTO MyTable
    SELECT      1
        ,(SELECT * FROM OPENROWSET(
            BULK 'C:\file.bmp', SINGLE_BLOB) as x )

Tip #2: Row Identification

If you are designing an OLTP database, subtle changes to your schema can help minimize the complexity and increase efficiency of your data warehouse ETL.

To identify an OLTP table's row, ETL matches the primary key. The problem is: the primary key can be from a long list of data types and can be comprised of multiple columns.

Here's a simple OLTP table:

Create table MyTable
{
Id int identity(1, 1) primary key
,col1 varchar(50)
}

ETL can handle any type of primary key. But we are not doing what we can do, but doing what we should do. Matching keys and data types across systems is cute, but not efficient.

A simple and consistent mechanism to identify a row will help simplify ETL and guarantee the correct identification of a single record across multiple systems.

Here's a better OLTP table:

Create table MyTable
{
Id int identity(1, 1) primary key
,col1 varchar(50)
,RowId uniqueidentifier default newid() NULL
}

So what does this do? First, and foremost, it doesn't change anything in your current transactional applications. However, it allows the ETL to easily match records across systems.

There is a side benefit: replication. SQL replication needs a row identity to function. You get this bonus for free.

This simple schema change doesn't impact your transactional system, makes your ETL less complex and more efficient, and simplifies replication.

So, why not?

The ETL between OLAP and OLTP

I am an armchair data modeler. That makes me about as dangerous as a cotton ball. Most developers never think about modeling. I'm trained, read-up, and think hard about modeling concepts. Now that some of my hair-brained ideas have made it into production, I have some strong opinions.

OLTP is about data-write performance. Yet, it is typically judged by its performance to read data. That's because 99% of applications running reports against the OLTP model. Sometimes it's from ignorance, but sometimes it from the desire for "real-time" numbers.

The fallacy of real-time is ridiculous. About .0000000001% of applications really require real-time. If NASA can operate a $5B land rover with two hours latency, analysts can handle a 15 or 30 minute lag.

This is where the gap between OLAP and OLTP comes to play.

ETL moves and transforms our OLTP data to our report-friendly OLAP store. But the greater the gap between OLTP and OLAP, the greater the ETL execution cost. The greater that cost, the less often it can be executed. The less often it's executed, the greater the report latency. And so, here we are.

What creates the gap between OLTP and OLAP models? Can you create a good OLTP model that is "nearly" an OLAP model? No. But, yes. And in this post, I am going to share with you one excellent tip that will speed up ETL, increase reporting options, and cost hardly anything.

Let's take a simple OLTP table called users. Here's a typical structure:

CREATE TABLE Users
(
UserId int primary key identity(1, 1),
FirstName varchar(50),
LastName varchar(50)
)

There is nothing wrong with this structure. However, it provides no information to the downstream ETL. As a result, we must build time-based snapshots to understand changes. Instead, a simple schema change can simplify the ETL, shrinking that gap between the OLTP and OLAP models.

Let's take that OLTP table and update its structure:

CREATE TABLE Users
(
UserId int primary key identity(1, 1),
FirstName varchar(50),
LastName varchar(50),
CreatedOn DateTime,
UpdatedOn DateTime
)

With two new columns, the ETL comparison logic can easily identify new rows, updated rows, and quickly scan for deleted rows. I know ETL frequency determines slowly changing dimension granularity. But, things can get too granular. Spread your ETL executions and you improve OLTP performance and make negligible impact on report currency.

When you begin to reduce the OLTP and OLAP gap, you improve ETL performance (and complexity) – and, for the first time, you have the strategic opportunity to increase ETL frequency for high-risk periods of the year. Then, dial it back to something reasonable the rest of the year.

The answer to ETL performance cannot be hardware because databases scale up, not out. Storage, opposed to popular myth, isn't cheap. High-availability, high-performance, secure storage is still expensive to procure, operate, and maintain. That may not change.

The answer to ETL performance is reducing the contention between the OLTP model and the OLAP. You can't do everything, but you can do a lot. I've seen ELT processes reduce from 10 major steps to 8. That's huge. With some more techniques, I think we could see another 20% improvement.

But why?

Until it's fast and easy to move OLTP data to OLAP, businesses will run reports and analysis against the OLTP store. Once a reasonable frequency is established in your environment, reports will perform better (against the OLAP store) and your application will perform better (against the OLTP store).

Two closing remarks

1. CreatedOn and UpdatedOn columns are useless until populated. When the warehouse cannot trust their values, all is lost, and ETL developers will return to their evil ways. Build this into your testing practice, or, like I do, build it into your DAL so you can't forget.

2. ETL itself can negatively impact OLTP performance. I know that. But techniques like this and refusing to calculate anything until you're in the warehouse stage makes it an easy trade off from the myriad reports killing the store when it needs to handle transactions.

Everything that is Microsoft

For those of you wondering what you get with a Team Suite MSDN subscription, here's a peek. Plus, I think it's cool to have list of Everything (95%) that is Microsoft in one list.

Applications (57)

Access 2.0
Access 2003
Access 2007
Accounting
Business Contact Manager
Business ScoreCard Manager
Front Page
Groove 2007
Hyper-V Server 2008
Hyper-V Server 2008 R2
InfoPath 2003
InfoPath 2007
Interconnect 2004
Interconnect 2007
Internet Explorer 6.0
MapPoint 2004
MapPoint 2006
MapPoint 2009
Office 2002
Office 2003
Office 2007
Office 95
Office Communicator 2005
Office Communicator 2007
Office Communicator 2007 R2
Office Communicator Mobile
Office Communicator Web
Office Communicator Web Access
Office Servers 2007
Office XP
OneNote 2003
OneNote 2007
Outlook 2003
Outlook 2007
PerformancePoint Server
ProClarity
Project 2002
Project 2003
Project 2003 Server
Project 2007
Project 95
Project Portfolio Server 2006
Project Portfolio Server 2007
Publisher 2002
Publisher 2003
Publisher 2007
SharePoint Designer 2007
Small Business Accounting(App)
Virtual PC 2004
Virtual PC 2007
Virtual PC for Mac 6.1
Virtual PC for Mac 7.0
Virtual Server 2005
Virtual Server 2005 R2
Visio 2002
Visio 2003
Visio 2007

Business Solutions (23)

Dynamics AX 2009
Dynamics AX 4.0
Dynamics Axapta 3.0
Dynamics CRM 1.0
Dynamics CRM 1.2
Dynamics CRM 3.0
Dynamics CRM 4.0
Dynamics GP 10.0
Dynamics GP 7.5
Dynamics GP 8.0
Dynamics GP 9.0
Dynamics NAV 4.0
Dynamics NAV 5.0
Point of Sale 1.0
Small Business Accounting
Small Business Manager Financials 7.5
Small Business Manager Financials 8.0
Small Business Manager Financials 9.0
Solomon 5.5
Solomon 6.0
Solomon 6.5
Solomon 7.0
Solomon FRx
Designer Tools (2)
Expression 1
Expression 2
Developer Tools (38)
Access Developer Extensions 2003
Automatic Graph Layout
Electronic Learning Libraries
eMbedded Visual C++ 4.0
Macro Assembler 6.11
MDAC
ODBC Data Packs
QuickBasic 4.5
Robotics Studio
SharePoint Services 3.0 Tools
Visual Basic .NET 2003
Visual Basic 2.0
Visual Basic 3.0
Visual Basic 4.0
Visual Basic 6.0
Visual Basic 6.0 Code Advisor
Visual Basic Applications (VBA)
Visual C++ 1.52
Visual C++ 2.0
Visual C++ 4.2
Visual C++ Browser Toolkit
Visual C++ Tools
Visual FoxPro "Sedna"
Visual FoxPro 7.0
Visual FoxPro 8.0
Visual FoxPro 9.0
Visual J#.NET
Visual Modeler
Visual SourceSafe 2005
Visual SourceSafe 6.0d
Visual Studio 2005
Visual Studio 2008
Visual Studio Team System
Visual Studio.NET
Visual Studio.NET 2003
Windows Embedded CE 6.0 R2
Windows XP Embedded
XNA Game Studio

MSDN Library (11)

2001-10 MSDN Library October
2005-07 MSDN Library July
2006-01 MSDN Library January
2006-05 MSDN Library May
2006-06 MSDN Library June
2006-07 MSDN Library July
2006-08 MSDN Library August
2006-12 MSDN Library December
2007-01 MSDN Library January
2007-04 MSDN Library April
2007-06 MSDN Library June

Operating Systems (30)

Compute Cluster Pack
MS-DOS
Small Business Server 2003
Small Business Server 2003 R2
Windows 3.1 (16-bit)
Windows 3.11 (16-bit)
Windows 3.2 (16-bit)
Windows 7
Windows Advanced Server
Windows CE .NET Platform Builder 4.1
Windows CE .NET Platform Builder 4.2
Windows CE 5.0
Windows CE DirectX Kit
Windows CE Embedded 6.0
Windows CE Toolkit Visual C++ 6.0
Windows Essential Business Server 2008
Windows Internet Explorer 7
Windows Internet Explorer 8
Windows Server 2003
Windows Server 2003 R2
Windows Server 2008
Windows Server 2008 R2
Windows Services for UNIX 1.0
Windows Services for UNIX 2.0
Windows Services for UNIX 3.0
Windows Services for UNIX 3.5
Windows Small Business Server 2008
Windows Vista
Windows XP
Workgroups 3.11 (16-bit)

Servers (60)

Antigen
Application Virtualization for Terminal Services
BizTalk Server 2002
BizTalk Server 2004
BizTalk Server 2006
BizTalk Server 2006 R2
BizTalk Server Accelerators and Adapters
Commerce Server 2002
Commerce Server 2007
Connected Services Framework
Content Management Server
Customer Care Framework 2005
Customer Care Framework 2008
Customer Care Framework 2009
Desktop Optimization Pack
Exchange Server 2003
Exchange Server 2007
Forefront Client Security
Forefront Client Security Beta
Forefront Security for Exchange Server
Forefront Security for SharePoint
Forefront Server Security Management Console
Forms Server
Groove Server
Host Integration Server 2000
Host Integration Server 2004
Host Integration Server 2006
Identity Integration Server 2003
Identity Lifecycle Manager 2007
ISA Server 2004
ISA Server 2006
Live Communications Server 2003
Live Communications Server 2005
Mobile Info 2001 Server
Mobile Info 2002 Server
Office Communications Server 2007
Office Communications Server 2007 R2
Operations Manager 2000
Operations Manager 2005
Project Server
Search Server 2008
SharePoint Server 2001
SharePoint Server 2003
SharePoint Server 2007
SharePoint Server 2007 Search
Speech Server 2004
SQL Server 2000
SQL Server 2005
SQL Server 2008
SQL Server 6.5
System Center Capacity Planner
System Center Configuration Manager
System Center Data Protection Manager
System Center Essentials 2007
System Center Mobile Device Manager
System Center Operations Manager
System Center Reporting Manager 2006
System Center Virtual Machine Manager
Systems Management Server 2003
Systems Management Server 2003 R2

Tools and Resources (101)

.NET Compact Framework 1.0
.NET Compact Framework 2.0
.NET Framework 1.0
.NET Framework 1.1
.NET Framework 2.0
.NET Framework 3.5
.NET Framework Redistributable 1.0
.NET Framework Redistributable 2.0
.NET Framework Redistributable 3.0
.NET Micro Framework
16-bit SDK and DDK's
Academic Alliance Tools
Application Compatibility
ASP .NET AJAX 1.0
Bank Framework SDK and DDK's
Baseline Security Analyzer 2.0.1
Bookshelf Symbol 7
Cabinet SDK and DDK's
CMC SDK and DDK's
CRC 3.05
Data Analyzer 2002
Developer Security Resource Kit
Device Simulator
DHTML Editing SDK and DDK's
Digital Dashboard
DirectX SDK and DDK's
Enterprise Instrumentation framework
eScrum
ESP 1.0
FabriKam 3.1
GIF Animator
Healthcare Framework
Help Workshop
IIS
ILMerge
Interix 2.2
Internet Security and Acceleration
ISDN
Log Parser
Mobile Internet Toolkit
Modem Developer Kit
MSDN Online Web Resource
MSXML 4.0
MSYubin7 1.5
Office Resource Kit
Office System
Palm Size PC 1.2
PassPort Manager
Passport SDK and DDK's
Platform SDK and DDK's
Pocket PC 2003
Provisioning System
Repository SDK and DDK's
Security SDK and DDK's
Server Appliance Kit
Services for Netware
SharePoint Products and Technologies
Smart Card
Smart Tag SDK and DDK's
SMS 2003 SDK and DDK's
Soap Toolkit
Solver Foundation
Source Code Migration Tool
Speech SDK and DDK's
SQL 2000 Sybase
SQL Server Migration Assistant
SQL Server Tool (SQLH2)
Standard User Analyzer
Translation Glossaries
UDDI SDK and DDK's
Unified Communications Managed
Virtual Earth
Visio Tools
Visual Basic for Applications
Volume Shadow Copy Service
Web Control SDK and DDK's
Web Services Enhancements
Web Storage SDK and DDK's
Windows Academic Program
Windows Communication Foundation
Windows DNA XML
Windows Hardware Compatibility
Windows Installer
Windows Media
Windows Mobile 2002
Windows Mobile 2003
Windows Mobile 5.0
Windows Mobile 6.0
Windows NT 3.51
Windows Point of Service
Windows Real-Time Communications
Windows Rights Management
Windows SDK and DDK's
Windows Server Update
Windows SharePoint Services
Windows Vista Upgrade Advisor 1.0
Windows Workflow Foundation
WindowsFS
WindowsFX Beta 1
WindowsFX Runtime Components CTP
XML Parser 3.0

Three Architects

In software, "architect" and "engineer" are controversial. They overlap other industries' recognition of formal training and certification. In software, the implication of "formal" training does not exist. Someone may be an "architect" or "engineer" without formal training – indeed without any training.

I can't change the world. The vocabulary ship has already sailed. Nothing is going to unwind architect or engineer from the software lexicon. We have to roll with the punches. And since we are, let's try to establish a little clarity – specifically around architect.

Software architects design solutions, but may also write or develop software like engineers. Likewise, software engineers write or develop software, but may also design solutions like architects. One thing no one ever does is "architect" anything. The word architect is not a verb; design is a verb.

So, why not just call them designers. You see, there is an unfortunate ambiguity in the software industry with designer. Designer is typically understood to be a graphics designer – someone responsible for the look-and-feel of something. Basically, that parking spot is already taken by Mac users.

Let's start.

As I see it, there are three types of software architects. Hopefully, the subsequent text will bring you to the same conclusion. The most important part is: there is not just one type of software architect. That alone might be the most important point.

At its highest level, an architect is responsible for a plan. It is the level or scope of this plan that separates the different types. Although these three roles are discrete, some architects may fulfill two. Very few architects (very very few) can address all three. But, necessity guides the world.

The Enterprise Architect

The Enterprise Architect is the high-level visionary. He doesn't develop software beyond the white board. He thinks in multi-year, multi-platform, and multi-vendor terms. He is sensitive to economic, competitive, and strategic implications. He may have been an engineer once, maybe.

The Enterprise Architect produces the technical vision for an organization. He prefers PowerPoint over Visio, and presents to both technical managers and the Board of Directors. He is responsible for innovation, agility, and practicality. He may be titled "Chief Architect" or CTO.

An Enterprise Architect might say, "We want our products to be Software as a Service."

The Solution Architect

Less likely to wear a tie than the Enterprise Architect, the Solution Architect interacts with teams in practical ways. The Solution Architect (sometimes called Solutions Architect) selects technologies, platforms, and vendors in the context of real software initiatives. He was once an engineer.

The Solution Architect produces project designs for engineers. He prefers Visio over Visual Studio, and remains loosely engaged though a project's lifecycle. He is responsible for project integration, high level implementation consistency, and modernization.

A Solution Architect might say, "We want to expose Product ABC with Web Services."

The Technical Architect

The Technical Architect may be confused with a Lead Developer. A project with more than one team may have more than one Lead Developer. But a project will only have one Technical Architect. He interacts with the team daily. He selects components, patterns, standards. He settles debates.

The Technical Architect produces reference implementations for engineers. He prefers Visual Studio over anything else. He is typically a lead developer. He is responsible for team cohesion, software quality, and the correct implementation of the Solution Architect's design.

A Technical Architect might say, "Product ABC public services will use WCF over SSL."

Lead Developer (I mention him only to clarify Technical Architect.)

The Lead Developer, sometimes called Team Lead, is responsible of team morale, mentoring, code review, and on time delivery. He is a developer, but has the elevated authority necessary to ensure his team's adherence to the Technical Architect's reference implementation.

A Lead Developer might say, "I'll implement encryption, you validate the input parameters."

Wrap it up

Software terms are dangerous because they mean different things to different people. You may or may not agree with my definition of architect. No matter what, this is true: before your project begins, make sure every member has shared definition for titles and understand clearly who is what.

Process Reengineering Primer

In a good economy, organizations invest in process reengineering to promote operational efficiency, competitive advantage, market agility, and to improve day-to-day human factors.

In a struggling economy, organizations must also invest as efficiency, competitiveness, agility, and human factors are more vital to business success than ever.

Reengineering business processes is trickier than it sounds. Having an outsider involved can loosen tensions around problems, and help move the reengineering steps in a positive direction.

Process reengineering has three major steps

First, target vital processes. Every business has them. Some have one, some hundreds. Don't boil the ocean. Select a few critical, related processes to start. Then start.

It's typically easy to know which processes to choose. They are very core to the business, involve the most people, span the longest time, and often are the most painful.

Second, get stakeholder consensus on the process as it is today, the "as-is" model. Usually, this is difficult; most workers loosely agree, but individually customize their part of the process.

The goal is a model which all (or most) stakeholders agree represent today's process – even at a high level. Moreover, they agree to use it. Simply codifying process like this creates efficiencies.

A codified process helps create accountability insofar as task responsibility. It also helps new employees understand their role systematically, rather than organically.

Third, include management to reengineer the "as-is" model into a "to-be" model. Don't shoot for perfection. Clean up glaring flaws and work to improve day-to-day human factors.

What is a day-to-day human factor? These are process areas that drive people crazy. Things like superfluous paperwork, repetition, duplication, and throw-away, legacy busywork.

Models can be high-level or detailed. Err on the side of high-level. "Adaptive discovery" is a concept to implement the process happy path now and slowly fold in edge cases over time (if ever).

The real world

Having a "to-be" model isn't the same as working the "to-be" model. Once you have a better, more-ideal model ready, it's time to create a strategy to put it into everyday life.

Introducing a "to-be" model is not easy. Here are some tips:

1. Keep it flexible. The "to-be" model isn't perfect, yet. You may need to tweak it on-the-fly; you will disprove assumptions. Have someone authorized to make these changes.
   
2. Open doors. Make sure stakeholders have an avenue to give feedback. They are your best measure to success and the fastest path to failure (user acceptance).
   
3. Remember the pain. Workers won't like this level of change right away. Make it easier by reminding them of the "as-is" problems addressed by the "to-be" model.
   
4. Top down. Getting models mapped is a bottom-up approach; implementing them is top-down. Management must be bought in, and reiterate "this will happen" no matter what.
   
5. All at once. Granted, some existing work will need to complete using the old model. However, pick a date. From then on, no new work follows the old model – bring down the hammer.
   

Use a system

In the software world, Business Process Management (BPM) is the tool family that implements process models, presents tasks, governs adherence, and reports progress. They can be free, expensive, simple, and complex. Whichever you choose, just choose. A BPM can contribute to a "to-be" model's success more than any other factor (other than user acceptance).

Here's where some complexity comes in. An expert should have been used to help reengineer your processes. An expert should be used to integrate a BPM.

I have introduced BMP three ways. As a software architect, I think a lot about how to integrate BPM with systems. To that end, I think these represent the only viable options. They are:

1. Full Integration. We built a line of business system from scratch. BPM was in its DNA. Using the system was using the BPM if the user knew it or not.
   
2. Partial Integration. We extended an existing system. Users interacted with a side-by-side application which also monitored user activity to minimize manual inputs.
   
3. No Integration. We implemented a status tracking system for manual processes which could not be otherwise tracked. Users used task lists to interact with the BPM.
   

Which option you pick will greatly impact user acceptance. Sometimes you only have two choices, sometimes one. You should know your choices, understand the implications, and choose.

Reporting Shangri-La

Workflow nirvana from the perspective of the worker has to do with authority, responsibility, and clarity. But, workflow nirvana from the perspective of management has to do with efficiency, visibility, enforcement, and reports.

Reports in workflow try to answer some common questions:

1. What is making my process slow?
   
2. What could make my process faster?
   
3. What if I made a change like XYZ?
   
4. How are my employees performing?
   
5. What's the current status of ABC?
   

Here's the problem. Reports are at the end. You need data to see trends. So, many organizations feel reports can be shelved for a later date. They are correct. However, most NEVER return to them and NEVER garner the exponential benefits they offer.

If you read nothing else, read this: the myriad benefits of reengineering business process pale to the benefits delivered through back-end reports. Follow the steps above, but don't stop until you have the reports necessary to answer your specific business questions.

Next, I'll talk about agility with BPM and "continual improvement".

In ASP.Net: export results to Excel

Seriously, who isn't asked to perform this task?

The general technique is this: Excel renders HTML just fine - pass Excel your HTML and you have what you want. The trick: ContentType.

Here's how:

// setup for excel
//
Response.Clear();
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
//
// fetch data, fill the grid (any HTML)
//
var _Query = from Result in new DataContext().Employees
              select Result;
GridView _GridView;
_GridView = new GridView();
_GridView.DataSource = _Query;
_GridView.DataBind();
//
// write it out
//
System.IO.StringWriter _StringWriter;
_StringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter _HtmlTextWriter;
_HtmlTextWriter = new HtmlTextWriter(_StringWriter);
_GridView.RenderControl(_HtmlTextWriter); // key!
Response.Write(_StringWriter.ToString());
Response.End();

Word 2007 & VSTO 3/2008 & adding a Custom Task Pane (no, not Custom Actions Pane)

Why didn't the Microsoft Visual Studio Tools for Office 3 for Microsoft Visual Studio 2008 call them Custom Action Panes like Excel? Maybe because they have a knack for naming things. Who knows. But Globals.ThisAddIn.CustomTaskPanes is the collection you'll need.

Document-specific or Global

Because I am using a Word Add In instead of a Document-type project, I should assign the task pane to a specific document - the user might have more than one open and only desire my task pane on the current one.

There should be only one

In a Word Add In, if you add a custom task pane more than once, you actually get more than one task pane! Isn't that amazing? So we will need to first check to make sure the task pane has not already been added.

Choose your own title

When you add a custom task pane you can give it a title. Wonderful. In Excel you're stuck with "document actions" - lame. I talked with the VSTO product team and they confirmed that there's no option to rename it.

Remember task pane orientation

Most Office users and, subsequently, most developers don't even know a task pane can be dragged to other parts of Office - including a horizontal disposition on the bottom or top of the document. Handle it.

Here's the Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using Word = Microsoft.Office.Interop.Word;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Word;
using Microsoft.Office.Tools.Word.Extensions;
using System.Windows.Forms;
using Microsoft.Office.Tools;

public partial class ThisAddIn
{
    public void ShowTaskPane()
    {
        // what word doc gets this task pane?

        Microsoft.Office.Interop.Word.Window _Window;
        _Window = this.Application.ActiveDocument.ActiveWindow;

        // only one per document

        foreach (CustomTaskPane _Item in CustomTaskPanes)
            if (_Item.Window == _Window)
            {
                // make sure you can see it

                _Item.Visible = true;
                return;
            }

        // what control will we show in the task pane?

        UserControl _Control;
        _Control = new CustomPanes.Test();

        // what will we call our task pane?

        string _Title;
        _Title = "My Special Title";

        // add the task pane

        CustomTaskPane _CustomTaskPane;
        _CustomTaskPane = 
            CustomTaskPanes.Add(_Control, _Title, _Window);

        // restrict orientation

        _CustomTaskPane.DockPositionRestrict = 
            Microsoft.Office.Core.MsoCTPDockPositionRestrict
            .msoCTPDockPositionRestrictNoHorizontal;

        // make sure you can see the new task pane

        _CustomTaskPane.Visible = true;
    }
}

Validate XML/XSD using XmlReaderSettings

In the .Net Framework 3.5, the XmlValidationReader was deprecated changing the syntax used to validate XML against an XSD schema. If you use it you will get this message: "Use XmlReader created by XmlReader.Create() method using appropriate XmlReaderSettings instead. http://go.microsoft.com/fwlink/?linkid=14202".

 

I put together a simple reusable method to demonstrate the new approach.

 

using System.Text;
using System.Xml;
using System.IO;
using System.Xml.Schema;

/// <summary>
/// Validate XML against XSD
/// </summary>
/// <param name="xml">raw XML string</param>
/// <param name="xsd">raw XSD string</param>
/// <returns>bool validation result</returns>
static bool ValidateXml(string xml, string xsd)
{
    try
    {
        // build XSD schema
        
        StringReader _XsdStream;
        _XsdStream = new StringReader(xsd);

        XmlSchema _XmlSchema;
        _XmlSchema = XmlSchema.Read(_XsdStream, null);

        // build settings (this replaces XmlValidatingReader)

        XmlReaderSettings _XmlReaderSettings;
        _XmlReaderSettings = new XmlReaderSettings()
        {
            ValidationType = ValidationType.Schema
        };
        _XmlReaderSettings.Schemas.Add(_XmlSchema);

        // build XML reader

        StringReader _XmlStream;
        _XmlStream = new StringReader(xml);

        XmlReader _XmlReader;
        _XmlReader = XmlReader.Create(_XmlStream, _XmlReaderSettings);

        // validate

        using (_XmlReader)
        {
            while (_XmlReader.Read())
                ;
        }

        // validation succeeded

        return true;
    }
    catch 
    {
        // validation failed

        return false;
    }
}

Intel's new Core i7 Extreme Processor

Intel's new super-processor is the Core i7. It is 4-core and 8-thread at 3.2Ghz. It has "Turbo Boost Technology" for physics and AI in gaming (with supported hardware). Like its predecessor it is hyper-threaded. Unlike its predecessor it can be over-clocked.

The Core i7 can encode video 79% faster than the Intel Core 2 Extreme Qx9770. It's computational power is 25% faster. However, given its power and head ratings - don't expect these hummers in laptops any time soon.

The value of Version 1.1

As agile processes reform the SLDC, expectations and deliverables converge. However, initial releases still have gaps. This is a software tautology: user acceptance arrives when the second release arrives. Version 1.0 is successful development. Version 1.1 is successful software.

Version 1.0 planning should include version 1.1 planning. Version 1.1 translates to user acceptance – software success's pinnacle measure. For project completion, stop at version 1.0. For successful software, push on for version 1.1.

Here's the deal. Version 1.0 can be great, but users rarely understand day-to-day impact of software until they use it day-to-day. It's not a sign of failure. On the contrary, it's a sign of partial success that users are exercising the application. It's the path to success.

So release – twice.

Simple Master/Details, Data Bound WPF Sample

It's time to stop ignoring WPF.

Before we do, let's be honest.

Five reasons WPF sucks:

1. Using Blend (the WPF extra-Visual Studio design tool) is sloppy
2. Editing raw XAML (like HTML for WinForms) is painfully slow
3. Cool third party controls are few compared to WinForms
4. The likelihood of major, forthcoming syntactic changes is high
5. Samples and real world case studies are rare or absent

Five reasons WPF rocks:

1. Done right, WPF can create an unmatched user experience
2. Most WPF development ports to Silverlight without change
3. Vector-based objects render faster and cleaner than bitmaps
4. The .Net Framework is the same in WPF or WinForms
5. Microsoft loves you when you adopt new technologies

Number 5 is real. Microsoft's WinForm support isn't even near sunset; the WinForms and WPF convergence is inevitable and imminent. Microsoft is investing heavily in WPF. Visual Studio 2010 is 100% WPF. That's amazing. Forthcoming applications will continue this.

Frankly, ignoring WPF is foolishness.

Consider HTML. Developers who watched HTML emerge, followed syntactic changes and enhancements more easily/slowly. XAML is young and simple. Follow it now. Train yourself on fundamental syntax and simple adoption of inevitable future enhancements.

WPF isn't going anywhere.  You don't need a blue badge to know WPF is strategic to Microsoft. It's growing in adoption, support, and power. The development experience is not as fast as WinForms today, but it's close. Eventually, they will be in parity.

Would I recommend WPF as a production platform?

Some argue WPF is the most future-oriented technology. Maybe. But developer velocity and maintenance is important. Today's WPF will be different than the next WPF. Deprecation of controls like ListView is an example. WPF needs to compliment other technologies, in my opinion. It would be difficult to recommend WPF as the sole LOB platform. But, that's me.

Now, let's create a simple application.

Here's what you are building -a simple master/detail form:



First create a WPF Application project. I called mine WpfApplication1. Clever, huh?

Second, spin up Northwind. Create a Linq to SQL class and add an Employee entity.

 
Third, create an Employees.cs class. Here's the code: 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;

namespace WpfApplication1
{
    public class Employees
        : ObservableCollection<Employee>
    {
        public Employees()
        {
            var _Result = from _Employee in
                new DatabaseDataContext().Employees
                select _Employee;
            foreach (var item in _Result)
                this.Add(item);
        }
    }
}

The reason for this class is so you can reference it as a self-filling DataContext from within the XAML. There are PLENTY of other ways to set the DataContext. This is just how I am doing it here. Honestly, I was trying to do it all without any Window1.xaml code behind. Once you understand DataContext, you can do whatever you want.


Forth, edit Window1.xaml. Here's the code: 

<Window x:Class="WpfApplication1.Window1"
    x:Name="Window"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:data="clr-namespace:WpfApplication1"
    Title="Window1" Height="300" Width="300">
    <Window.Resources>
        <DataTemplate x:Key="ListTemplate">
            <Grid>
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="20"  />
                    <ColumnDefinition Width="*"  />
                    <ColumnDefinition Width="*"  />
                </Grid.ColumnDefinitions>
                <Image Source="{Binding Path=Photo}" 
                       Width="20" Stretch="UniformToFill" />
                <Label Grid.Column="1" 
                       Content="{Binding Path=FirstName}" />
                <Label Grid.Column="2" 
                       Content="{Binding Path=LastName}" />
            </Grid>
        </DataTemplate>
    </Window.Resources>
    <Window.DataContext>
        <data:Employees />
    </Window.DataContext>
    <Grid>
        <Grid.ColumnDefinitions>
            <ColumnDefinition Width="150" />
            <ColumnDefinition Width="*" />
        </Grid.ColumnDefinitions>
        <ListBox BorderThickness="0"
            IsSynchronizedWithCurrentItem="True"
            ItemTemplate="{StaticResource ListTemplate}" 
            ItemsSource="{Binding}" />
        <StackPanel Grid.Column="1">
            <Image Source="{Binding Path=Photo}" 
                   Stretch="Fill" />
            <Label Content="{Binding Path=FirstName}" />
            <Label Content="{Binding Path=LastName}" />
            <Label Content="{Binding Path=Title}" />
        </StackPanel>
    </Grid>
</Window>

Not much code, really?

Beautiful. Now hit F5.

There are a lot of neat things here:

1. <DataTemplate> is used to display the user list and little photo. You can create templates on the page or in external files to be shared by pages/windows.



2. xmlns:data="clr-namespace:WpfApplication1" is added so I can reference the classes in my project in the XAML of this window (like in the Window.DataContext).



3. <Window.DataContext> references the structure for binding. The reason you add this in the XAML is the data types and sample data appear in the designer.



4. IsSynchronizedWithCurrentItem="True" is the tag on the ListBox that indicates the SelectedItem of the control changes the CurrentItem on the CurrencyManger.

Have fun everyone!