Pretty much everyone now days are writing code against relational databases. All those developers should be architecting their relational databases using 3^rd normal form(3nf). If you are not using 3^rd normal form it’s time to get your “shtuff” together. All applications should be storing their data properly, but when it comes to reporting most people want the data de-normalized. A common request that I have had over the years is to show a comma separated list of related items in a cell. This would typically result in some database function that would help out with this. I found a while ago this technique that has really helped out with this request and is easy to implement and performs very fast. We will be using the “Northwind” example database for our datasource.

Here are the tables we will be using

Below is a small example query that will de-normalize orders by customer. The magic is happening with the substring correlated sub-select outputs the data using FOR XML and concatenating the data on itself.

Source code

SELECT
CustomerID
,SUBSTRING
(
	(
		SELECT
		(', ' + CAST(OrderID AS VARCHAR(120)))
		FROM  Orders AS o2
		WHERE o.CustomerID = o2.CustomerID
 
		ORDER BY
		o2.OrderID
 
		FOR XML PATH( '' )
	),
3,
1000
) AS OrderIDs
 
FROM
 Orders AS o
 
GROUP BY CustomerID

Here is a small sample of the data that is returned.

Here is more of what a formatted report might look like

Source code

SELECT
c.CustomerID
,c.ContactName
,c.ContactTitle
,derCustomerOrders.Orders AS FiveMostRescentOrders
 
FROM
 Customers AS c 
 
LEFT JOIN
(
	SELECT
	CustomerID
	,SUBSTRING
	(
		(
			SELECT TOP 5
			(', ' + CAST(OrderID AS VARCHAR(120)) + ' (' + CONVERT(VARCHAR(10), OrderDate, 1) + ')')
			FROM  Orders AS o2
			WHERE o.CustomerID = o2.CustomerID
 
			ORDER BY
			o2.OrderID DESC
 
			FOR XML PATH( '' )
		),
	3,
	1000
	) AS Orders
 
	FROM
	 Orders AS o
 
	GROUP BY CustomerID
 
) derCustomerOrders
ON
c.CustomerID = derCustomerOrders.CustomerID
 
ORDER BY
c.ContactName

Here is a sample from the results

When I am working on an application I want the end user to feel like they are able to capture all the data they need and have a great experience while doing so. A great way of accomplishing this is to build common data capture controls that you can reuse all over the application and even across applications. To make this all happen we are going back to our toolbox for another tool. We will be reviewing the address control. The address control can attach itself to any known entity. After that it is pretty much just drag and drop. This makes it really easy to give a feature rich common control throughout the application and can save you a lot of time. The only thing about a control like this is that when reporting it can be a little more difficult than using its own table, but that is an acceptable trade off. This type of decision must be reviewed to make sure that it will scale with your app.

Not every problem has the same solution.

Here is a screenshot of the common address control in action.

Add Action

Here is the Sql Server table that stores the data

This is another really simple control that basically just takes the AssociatedRecordTypeID and the AssociatedRecordID and displays the related data and records it. I have not included any code in this example because it is a descent amount of boilerplate code, but the concept is very easy. I would like to encourage you to have controls like this in your toolbox for creating applications. Users really will enjoy using these nice controls, you will enjoy building applications quickly, and you will also be proud of the end result.

Everyone talks about writing reusable code and they strive to meet this objective. It actually turns out that most of these attempts fail. The reason for failure is because the code gets tailored to meet a projects specific needs. To fix this you must either not reuse code (not acceptable), put on your salesman hat and push back on the changes(see this for a laugh on what not to do), or shrink the coverage of the code. In this entry we will be covering the path of least resistance. That is to develop code that has a small scope, is easy to adopt, and saves time.

We will be going over creating simple user controls that are built in webforms in an asp.net website project. Using the website project type allows you to create user controls that you can easily move from project to project without having to worry about namespacing problems. I have found I can save a lot of time replacing standard controls like textboxes or dropdownlists with simple usercontrols. Then add a few features to them and the time savings start quickly adding up. This is a continuation on a concept from this other article “Shrtn ur cd“.

Here are some examples of a few that I like to use:

For example here is the code for a simple textbox

Source code

<table cellpadding="0" id="tbl" runat="server" border="0" cellspacing="0">
    <tr>
        <td id="tdLabel" runat="server">
            <asp:label id="lblLabel" associatedcontrolid="txtTextBox" runat="server"></asp:label>
        </td>
        <td>
            <asp:textbox id="txtTextBox" runat="server"></asp:textbox>
            <asp:requiredfieldvalidator id="RequiredFieldValidator1" controltovalidate="txtTextBox" runat="server" text="*"></asp:requiredfieldvalidator>
        </td>
    </tr>
</table>

Here is the code for the code behind for the text box

Source code

public partial class Common_Controls_SmartTextBox : SmartControlBase
{
 
    public string Text
    {
        get { return this.txtTextBox.Text; }
        set { this.txtTextBox.Text = value; }
    }
 
    public TextBox TextBox
    {
        get { return this.txtTextBox; }
    }
 
    public bool Enabled
    {
        get { return this.txtTextBox.Enabled; }
        set { this.txtTextBox.Enabled = value; }
    }
 
    public int TextBoxMaxLength
    {
        get { return this.txtTextBox.MaxLength; }
        set { this.txtTextBox.MaxLength = value; }
    }
 
    public TextBoxMode TextBoxMode
    {
        get { return this.txtTextBox.TextMode; }
        set { this.txtTextBox.TextMode = value; }
    }
 
    public Unit TextBoxWidth
    {
        get { return this.txtTextBox.Width; }
        set { this.txtTextBox.Width = value; }
    }
 
    protected override Label Label
    {
        get { return this.lblLabel; }
    }
 
    protected override RequiredFieldValidator RequiredFieldValidator
    {
        get { return this.RequiredFieldValidator1; }
    }
 
    protected override System.Web.UI.HtmlControls.HtmlTableCell LabelCell
    {
        get { return this.tdLabel; }
    }
}

Here is the code behind for the control base class (very basic code)

Source code

public abstract class SmartControlBase : UserControl
{
 
    protected abstract Label Label { get; }
    protected abstract HtmlTableCell LabelCell { get; }
    protected abstract RequiredFieldValidator RequiredFieldValidator { get; }
 
    protected override void OnLoad(EventArgs e)
    {
        base.OnLoad(e);
 
        if (!this.IsPostBack)
        {
            if (this.Page is IEditPageBase)
            { this.LabelWidth = (this.Page as IEditPageBase).LabelWidth; }
            else
            { this.LabelWidth = int.Parse(ConfigurationManager.AppSettings["LabelWidthDefault"]); }
 
            if (this.LabelText != null && this.RequiredFieldValidator != null)
            {
                this.RequiredFieldValidator.ErrorMessage = string.Format("{0} is Required.", this.LabelText);
            }
        }
    }
 
    public string LabelText
    {
        get { return this.Label.Text; }
        set { this.Label.Text = value; }
    }
 
    public bool Required
    {
        get { return this.RequiredFieldValidator.Enabled; }
        set { this.RequiredFieldValidator.Enabled = value; }
    }
 
    public string ErrorMessage
    {
        get { return this.RequiredFieldValidator.ErrorMessage; }
        set { this.RequiredFieldValidator.ErrorMessage = value; }
    }
 
    public Unit LabelWidth
    {
        get
        {
            return this.Label.Width;
        }
        set
        {
            this.Label.Width = value;
 
            this.LabelCell.Attributes.Add("style", string.Format("width:{0}px;", this.LabelWidth.Value));
        }
    }

Here is using the control in the user interface. Doing this kind of technique is great also because if you use the same prefix to the control you can layout the controls on the page using a very simple format and them based on the type of control just change the suffix afterwards.

Source code

<tr>
    <td>
        <uc:smarttextbox id="FirstName" runat="server" required="true" labeltext="First Name" />
    </td>
    <td>
        <uc:smarttextbox id="LastName" runat="server" required="true" labeltext="Last Name" />
    </td>
</tr>

Here is retrieving the code on the edit screen

Source code

if (this.IsValid)
{
    string fName = this.FirstName.Text;
    string lName = this.LastName.Text;
}

There are many more examples that can save you time like with drop down lists and datepickers and textboxes with masks.

Before you can do anything with Microsoft CRM you must first be authenticated. Here are some prerequisites that you must have in place before you can get started.

• Download the Microsoft CRM 4 SDK
• Have the CrmDiscoveryService.wsdl file from your IFD site

Microsoft CRM 4 uses Claim based (aka token) authentication and it communicates using a request/response format. Now that you know that you have crossed the hardest bridge. Authentication will take place using known credentials like domain\username and password. In the example below we will be creating an Account inside of CRM to show the interaction. Lets get started.

• This code is simply supplying the credentials to our custom method to authenticate and initialize our CRMService.
• Then we new up an instance up an account setting the name field on the account and creating it in CRM.

Source code

string username = "username";
string password = "password";
string domain = "domain";
 
CrmService myCrm = this.IFDConnection("OrgName",
"server.com",
domain,
username,
password);
 
account newAccount = new account();
newAccount.name = string.Concat(username, " - Testing account service");
 
myCrm.Create(newAccount);

Next we dig into the IFDConnection method

• First we setup some variables and fix the server string
• Then we initialize our CrmDiscoveryService and set the url based on the server name. This is important because you are specifying the authentication type you are working with.

Source code

private CrmService IFDConnection(string organization, string server, string domain, string username, string password)
{
    // A CrmService reference.
    CrmService CrmService = null;
    // URL of the Web application.
    string WebApplicationUrl = String.Empty;
    // GUID of the user's organization.
    Guid OrganizationId = Guid.Empty;
    //Remove any trailing forward slash from the end of the server URL.
    server = server.TrimEnd(new char[] { '/' });
    // Initialize an instance of the CrmDiscoveryService Web service proxy.
    CrmDiscoveryService disco = new CrmDiscoveryService();
    disco.Url = "http://" + server + "/MSCRMServices/2007/SPLA/CrmDiscoveryService.asmx";

• Next we are going to make a request using our known credentials
• We then get a response with a possible list of Organizations
• We then enumerate the list looking for the supplied organization

Source code

//Retrieve a list of available organizations.
RetrieveOrganizationsRequest orgRequest = new RetrieveOrganizationsRequest();
orgRequest.UserId = domain + "\\" + username;
orgRequest.Password = password;
RetrieveOrganizationsResponse orgResponse = (RetrieveOrganizationsResponse)disco.Execute(orgRequest);
//Find the desired organization.
foreach (OrganizationDetail orgdetail in orgResponse.OrganizationDetails)
{
	if (orgdetail.OrganizationName.ToLower() == organization.ToLower())
		{

• Now that we found our organization we request an authentication ticket using our supplied credentials
• Then we receive a response with our authentication ticket

Source code

//Retrieve the ticket.
RetrieveCrmTicketRequest ticketRequest = new RetrieveCrmTicketRequest();
ticketRequest.OrganizationName = organization;
ticketRequest.UserId = domain + "\\" + username;
ticketRequest.Password = password;
RetrieveCrmTicketResponse ticketResponse = (RetrieveCrmTicketResponse)disco.Execute(ticketRequest);

• Now we take our authentication ticket and create an authentication token. We specifiy the AuthenticationType = 2 which mean IFD authentication.
• Now that our token is created we can start working with CRM.
• So we new up a CRMService and assign our token and the organization’s url and other details.
• Then we return the created CrmService

Source code

//Create the CrmService Web service proxy.
CrmAuthenticationToken sdktoken = new CrmAuthenticationToken();
sdktoken.AuthenticationType = 2;
sdktoken.OrganizationName = organization;
sdktoken.CrmTicket = ticketResponse.CrmTicket;
CrmService = new CrmService();
CrmService.CrmAuthenticationTokenValue = sdktoken;
CrmService.Url = orgdetail.CrmServiceUrl;
WebApplicationUrl = orgdetail.WebApplicationUrl;
OrganizationId = orgdetail.OrganizationId;
break;
}
}
return CrmService;

Complete code Here

Source code

protected void Page_Load(object sender, EventArgs e)
{
    string username = "username";
    string password = "password";
    string domain = "domain";
 
    CrmService myCrm = this.IFDConnection("OrgName",
                                            "server.com",
                                            domain,
                                            username,
                                            password);
 
    account newAccount = new account();
    newAccount.name = string.Concat(username, " - Testing account service");
 
    myCrm.Create(newAccount);
}
 
private CrmService IFDConnection(string organization, string server, string domain, string username, string password)
{
    // A CrmService reference.
    CrmService CrmService = null;
    // URL of the Web application.
    string WebApplicationUrl = String.Empty;
    // GUID of the user's organization.
    Guid OrganizationId = Guid.Empty;
    //Remove any trailing forward slash from the end of the server URL.
    server = server.TrimEnd(new char[] { '/' });
    // Initialize an instance of the CrmDiscoveryService Web service proxy.
    CrmDiscoveryService disco = new CrmDiscoveryService();
    disco.Url = "http://" + server + "/MSCRMServices/2007/SPLA/CrmDiscoveryService.asmx";
    //Retrieve a list of available organizations.
    RetrieveOrganizationsRequest orgRequest = new RetrieveOrganizationsRequest();
    orgRequest.UserId = domain + "\\" + username;
    orgRequest.Password = password;
    RetrieveOrganizationsResponse orgResponse = (RetrieveOrganizationsResponse)disco.Execute(orgRequest);
    //Find the desired organization.
    foreach (OrganizationDetail orgdetail in orgResponse.OrganizationDetails)
    {
        if (orgdetail.OrganizationName.ToLower() == organization.ToLower())
        {
            //Retrieve the ticket.
            RetrieveCrmTicketRequest ticketRequest = new RetrieveCrmTicketRequest();
            ticketRequest.OrganizationName = organization;
            ticketRequest.UserId = domain + "\\" + username;
            ticketRequest.Password = password;
            RetrieveCrmTicketResponse ticketResponse = (RetrieveCrmTicketResponse)disco.Execute(ticketRequest);
            //Create the CrmService Web service proxy.
            CrmAuthenticationToken sdktoken = new CrmAuthenticationToken();
            sdktoken.AuthenticationType = 2;
            sdktoken.OrganizationName = organization;
            sdktoken.CrmTicket = ticketResponse.CrmTicket;
            CrmService = new CrmService();
            CrmService.CrmAuthenticationTokenValue = sdktoken;
            CrmService.Url = orgdetail.CrmServiceUrl;
            WebApplicationUrl = orgdetail.WebApplicationUrl;
            OrganizationId = orgdetail.OrganizationId;
            break;
        }
    }
    return CrmService;
}

I believe there are always places in which you can improve efficiency. Some things stand out more than other, and usually the easy things get worked on last. I wanted to share with you a little trick to help out with managing data from the V (view) to the C (Controller).

My primary pattern for architecture is using MVC but not the ASP.Net MVC. Our company also does iPhone apps so we get exposed to other coding languages. The great thing about this is that you get to pick and choose what you like about each language. The MVC pattern that I follow is derived from that on the iPhone and this little trick comes in very handy in both objective-c and .net.

The point in time in which you are moving data from the user interface (View) into your Controller class a significant amount of time savings that can be gained. By implementing some nice extension methods (categories in the iPhone world) that can reduce the amount of code you put out.

Let me show you:

Extend the TextBox object to include one these so you don’t have to look for nulls

Source code

public static Decimal TextAsDecimal(this TextBox txt)
{
	decimal value = 0;
 
	if (!string.IsNullOrEmpty(txt.Text.Trim()))
	{ decimal.TryParse(txt.Text.Trim(), out value); }
 
	return value;
}

Source code

public static Decimal? TextAsDecimalNull(this TextBox txt)
{
	decimal value = 0;
	decimal? emptyValue = null;
 
	if (!string.IsNullOrEmpty(txt.Text.Trim()))
	{
	    decimal.TryParse(txt.Text.Trim(), out value);
 
	    return value;
	}
	else
	{ return emptyValue; }
 
}

Implement this so you don’t have to worry about setting the value from your storage to your TextBox

Source code

public static void TextSetFromDecimalNumber(this TextBox txt, Decimal? number)
{
	if (number.HasValue)
	{ txt.Text = number.Value.ToString(PageBase.DecimalFormatString); }
}

This is just a few examples that I usually implement, but I also do this for more data type handling and for more controls.  I usually have an extensions.cs file in each one of my projects and carrying them with me to whatever project I do. That way I hit the ground running. So take some of these concepts and implement your own extensions and start reducing the amount of code that you write. Let me know if you have any questions and I try and help you out.

Part 2

“The significant problems we face cannot be solved at the same level of thinking we were at when we created them.”

This quote is very empowering and enlightening. You can’t read a blog, watch the news, or read a paper (if your over 50 ) without reading something that talks about the economy and being upside down. When I read this quote it said to me that there are things you can do about it and it is an exciting time. Things are getting mixed up within technology because of how the economy is. Apple, Google and Microsoft are pushing each other to become better. Big technology companies like the big 3 were becoming fat and happy on the economy being so good that they didn’t have to do anything to make money.

Now the big 3 are having to get back to what put them at the top by pushing each other to innovate. There are a ton of cool really innovative products that can make your company run better for a fraction of what you used to spend.

Now is the time to look at what you’re doing within your organization see what you can streamline, optimize and simplify. What we used to know as far as how technology and software used to work is no longer the case. Take a look back at the quote for this entry and note that what you thought a year ago to be a fact may need to be revisited.  Pick up a book, read a new blog, or get a new General Ledger statement and demand change.

Right now is not about cutting people but refocusing. Take a negative and make it a positive. No matter what the industry your are in the lines are getting blurred. Things are becoming more viral and flexible within every week.

Take a look at a cool piece of technology like the iPad. The iPad is changing computing how we know it today. The iPad is based on storing your data in the cloud and doing simple focused tasks on the device. The iPhone is a mini portable computer in your pocket. Manage your business with these devices from wherever you with a few flicks of your finger. Use Google docs with all these devices and then when you get to a computer you can continue to work on the same spreadsheets.

Mix up your business and become more social, and try to have more FUN while doing so. List your business on Facebook and create some thought provoking entries of your own. Business is no longer about a lot of hours and a few smiles. This a great time to take those things and tell them to kiss your gritz and start having fun. If you notice someone around you stuck in a slump by them a nice treat and give them a smile.

Get out there and provoke change in your life and in your business. Think back on what your thoughts were a year ago and kick the tires to see what happens. Find more time to smile and help someone around find their smile also.

Every website worth anything has a database of some kind. A lot of programmers do not like to think too much about this part of the application. I am an architect and part of that is dealing with everything as a whole and analyze if it fits with a solution. We will be focusing mainly on the storage in this entry and more specifically the storage of date/time values.

Date/time is tracked everywhere within application and is usually tracked relatively. Most date/times are recorded to your data storage as DateTime.Now(.Net) or GetDate()(Sql Server). This is easy and as far as you know there is nothing wrong with this. In fact you may even think this is great because you are capturing when someone created or modified something for auditing or a scheduling a task that has to be run. Tracking for auditing is great.

The problem with retrieving a value from DateTime.Now or GetDate() is that they are relative time values. Once you saved that data to your data storage like Sql Server is when you can start running into problems. You see storing relative server date/time is an extremely biased recording event. You are no longer flexible in your hosting environment and it may prevent you from moving to a cloud environment or even changes within your hosting provider. Employees outside your current time zone may also be misguided as to what is actually happening in the system because they only see a date they may think it is relative to where they are.

The way to properly record date/time data should be through storing UTC(absolute) time. Most all programming languages have the ability retrieve absolute time(UTC) DateTime.UtcNow(.Net) or GetUTCDate()(Sql Server). I also like to add an indicator in my column name in my db that the date/time is UTC. For example a column name may be “CreatedDateUTC” so that way I know how to display the data stored for that column.

Now that you are recording the absolute time you must now display the correct date/time to the end user. Here is what you should do:

1. Using javascript request the end users timezone
2. Save the end users timezone to a cookie
3. Use the timezone cookie to get the timezone offset in your server side code
4. Have a server side function that is accessible throughout your whole application like an extension method that converts the given date/time for the end user to their time using the timezone offset
5. Always record UTC date/time to your data storage

Doing these few steps to ensure there will be less confusion within your application and allow users to view data relevant to where they are working from . You will also be able to be more portable and move your websites or databases to the cloud or hosting environments outside of your time zone.

How would you like to be able to start working on something and finish at another computer right where you left off? Typically cloud company = big money to spend. The new way of computing is getting common data into the cloud (what we typically call websites) and have applications around them that know how to interact with those cloud applications. On top of all that have devices like iPhones that are aware of the cloud services and make life even easier by interacting with your cloud data.

A great free service that you can take advantage of and many large companies and governments already are is Google Apps. This service offers free email with a large number of email addresses included @yourdomainname.com. This is one of the best email programs on the web and is basically gmail but you use your domain name. You can use pop3 and smtp, imap, and it will even work like exchange. You can use it in any email client you currently use now. They have a really easy tutorial on how to setup your email with your domain registrar like GoDaddy and you will have free working email within 1 hour.

Within the Google Apps account that you setup when you setup your email they also have several other features:

Online Apps:

• You don’t have to keep buying word processing, spreadsheet, or presentation software any more with this online no installation required apps.
• Online spreadsheets with a huge library of functions
• Online word processing with a great rich text editor
• Easy to use presentation software.

Shared Calendar:

• Collaborate within your organization
• Schedule appointments
• Share your appointments with your clients in a caldav format
• Publish your company calendar
• Look at other peoples availability

Share Contacts

• Share contacts throughout your organization
• Update contact info and everyone gets the update
• Have groups of contacts for marketing

Shared Files

• Have an online shared file server
• Files that are backup online
• Access and share files on the web
• Work on files with other people in your organization

Chat

• Have secure chat that is specific to only your organization
• Contacts are automatically added when a new employee is hired or fired

Web based Administration

• Administer email
• Administer your Google Apps Intranet site
• Administer permissions

Personalized

• Create a personalized home page for employees to visit when they start there browser
• Allow users to adjust their homepage to add data they find helpful to getting their job done
• Add widgets like weather to their homepage
• Add many different domains. If you have a domain like mywebsite.com you can add also mywebsite.net or any other website url and receive email on both under one Google Apps Account.

Integration

• Google marketplace offers other apps that will integrate or add onto your Google Apps Account
• They have CRM’s that will integrate into your Google Apps account and when you add a contact into Google Apps or the CRM it will show up in the other one
• Add-in software that works like Visio and works with your Google Docs

So if any of these things you might find useful and you want to ease your pocket book and make technology work for you. You might want to look at the Google Apps service and try their free version.