Thursday 26 January 2012

ProgrammerFail–N Layered Architecture

What is wrong with the following code?

using (SqlDataReader dr = ...)

At the first look, it seems perfect but:

  • N layered architecture
  • Layers are: DAO –> Factory –> BL –>WebApp

 

And this code with the previous row:

protected void ddlUnits_SelectedIndexChanged(object sender, EventArgs e)
        {
            using (SqlDataReader dr = ...)

ProgrammerFail

!!!FAIL!!!

Monday 9 January 2012

Sql Server Management Studio tuning: T-Sql Formatter and SSMS Tools Pack


I think Microsoft Sql Server Management Studio is a great tool for managing Sql Server, writing and optimizing Transact Sql code and for anything with Sql Server but I have always missed some functions:
  • Formatting Sql code
  • Code Snippets (Like in Visual Studio)
  • CRUD Stored Procedure generation
I had to reinstall my working machine last week and started searching for some free Sql Server Management Studio addins for these functions and finally I found a solution:
Poor Man’s T-SQL Formatter for formatting Sql code.
SSMS Tools Pack for the others.

Poor Man’s T-SQL Formatter is a free add in that formats Sql scripts with a single hotkey. As Visual Studio, we can format the whole document or just the selected part of it.
After the installation it puts two entries into the SSMS Tools menu: Format T-SQL Code and T-SQL Formatting Options.
PoorMansTSqlFormatterOptions
These are my personal settings. I have changed the default hotkey to CTRL+K, CTRL+D and unchecked all the extras, because the default settings were uncomfortable for me (This hotkey is the same as the debugging hotkey in SSMS Tools Pack but I disabled this function because I think I wont use it).
It also has a free web service for testing at poorsql.com.
SSMS Tools Pack is also a free add-in created by Mladen Prajdić SQL Server MVP. It has a lot of features but for me the most importants are:
  • Execution Plan Analyzer!!!!!!! (Verry important)
    SSMSToolsPack_QueryPlanAnalyzer
  • SQL Snippets (Fires on Enter – as default – or Tab key)
    SSMSToolsPack_Snippets
  • Search Results in Grid Mode
    SSMSToolsPack_SearchGridResults
  • CRUD (Create, Read, Update, Delete) stored procedure generationSSMSToolsPack_CRUDGeneration

  • And a lot of other good stuff Mosolygó arc.
I also have to mention the Format SQL feature that I think is a Fake Function. It only makes keywords uppercase or lowercase, doesn’t format anything in my code.

Summary: I recommend installing these to everybody who uses SQL Server. SSMS Tools Pack works with every version of SQL Server from SQL Server 2005 (as the install shows), and unfortunately I don’t have any information about Poor Mans T-SQL Formatter but I tried it with SQL Server 2008 and it worked fine for me.
Note: EVERYBODY has to try the Execution Plan Analyzer, it is really-really worth it.

The download links are:

Happy Coding!
Morzel

Tuesday 3 January 2012

Server Side Dynamic TreeView in Asp.Net

 

In most cases loading large TreeView in one step can be a good performance killer. The generated html is really large and our page is loading so slow at the client and the average user don’t want to see the full tree together.

Let see, what needs for loading dynamically an asp.net server control on the server side.

  • We need some data (In that case that’s will the ProductCategory table in the AdventureWorks sample database).
  • We need a method to determine if the given node has childs or not.
  • We need a TreeView Mosolygó arc.
  • First step:
    • We have to load the first level nodes of the TreeView on the first page load.
    • Handle the OnTreeNodePopulate event if the childnode has more childs on the next level.
    • Set the nodes expanded property to false. The TreeNodePopulate event will be fired when we click on a node. The default value is true and the tree is expanded.
  • Repeat the previous step on every TreeNodePopulate event.

 

The ProductCategory table contains the following records: ProductCategoriesTable
The value of ParentProductCategoryID is null at the root elements and the ProductCategoryID of the parent element at the others.

 

We need a method that queries the list of the child nodes for a given node (DAO\CategoryDAO.cs):

public static List<ProductCategory> GetCategoriesByParentID(int? parentID)
{
    using (AdventureWorksLT2008Entities ctx = new AdventureWorksLT2008Entities())
    {
        return ctx.ProductCategory.Where(cat => cat.ParentProductCategoryID == parentID || (null == parentID && cat.ParentProductCategoryID == null)).ToList();
    }
}

 

Add a TreeView for the page (Default.aspx):

<asp:TreeView runat="server" ID="tvCategories"
    OnTreeNodePopulate="tvCategoris_TreeNodePopulate"></asp:TreeView>

 

Make an AddChildNodes method for populate the child nodes of the given node (Default.aspx.cs):

protected void AddChildNodes(TreeNodeCollection nodes, TreeNode node)
{
    List<ProductCategory> categories = null;
    int parentID = -1;
    
    if (null != node && int.TryParse(node.Value, out parentID))
        categories = CategoryDAO.GetCategoriesByParentID(parentID);
    else
        categories = CategoryDAO.GetCategoriesByParentID(null);

    foreach (var cat in categories)
    {
        TreeNode newNode = new TreeNode();
        newNode.Value = cat.ProductCategoryID.ToString();
        newNode.Text = cat.Name;
        newNode.PopulateOnDemand = null == node;
        newNode.Expanded = false;

        nodes.Add(newNode);
    }
}

If the new node is leaf node, we have to set the value of the PopulateOnDemand property to false.

 

Load the root elements on the first page load (Default.aspx.cs):

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
        AddChildNodes(tvCategories.Nodes, null);
}

 

Create an event handler for the TreeNodePopulate event (Default.aspx.cs):

protected void tvCategoris_TreeNodePopulate(object sender, TreeNodeEventArgs e)
{
    if (e.Node.ChildNodes.Count == 0)
    {
        AddChildNodes(e.Node.ChildNodes, e.Node);
    }
}

 

The full project is downloadable from my Skydrive public folder.

 

Happy and Bug free coding!

Morzel