Monday, September 23, 2013

Not able to debug ASP.Net MVC application after windows password change

Some days back, I got a request from one of the team members to solve one issue in his machine. It was simple. He changed his windows login password. After that he is not able to debug ASP.Net MVC application. If he really understands about how his development environment is set up or he know how to google, this would not happen. As of him he gets the below error message when he try to debug MVC 4 application from Visual Studio. Immediately he shared his screen to me.
"Unable to start debugging on the web server. Could not start ASP.NET debugging. More information may be available by starting the project without debugging."
I had to start from the beginning such as how its running ? Is it from Visual Studio development server or IIS ? Its in IIS. So which app pool is been used? What is the identity of the application pool? We could see that its his windows login. Not the Network Service, ApplicationPool Identity or any other system accounts.

This gives the hint. He changed the windows password but he didn't updated the app pool identity. Updated the application pool identity with new password and things started working.

Happy debugging.

Monday, September 9, 2013

SQL Query to find number of table rows stored in different partitions

Sometime if we are analysing production applications, we may need to check why some queries are taking more time. There are some case where all the data may be going to same database file which is against the partition strategy of the database. Below is the query which gets the details about how the table rows are distributed in partitions.


DECLARE @TableName sysname = 'Users';
SELECT p.partition_number, fg.name as FileGroup, p.rows
FROM sys.partitions p
    INNER JOIN sys.allocation_units au
    ON au.container_id = p.hobt_id
    INNER JOIN sys.filegroups fg
    ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID(@TableName)


We can directly evaluate the partition function as below to know what will be value of partition for a given value.This will also help us to determine which rows went to which partition. But the above is more handy to use.


$Partition.<partition name>(value)

Happy debugging.

Note: The credits for this query goes to my team mate who don't have a blog as of now. May be this will inspire him to start his blog :)

Tuesday, September 3, 2013

Reading formatted MS Excel cell values using Open XML SDK and showing as HTML

Most of the existing mechanisms which helps to access Excel cells from our applications doesn't give us the formatting of the excel cell. If we select the excel data using OleDB connectors it return plain text. The Excel COM interop give more, but it gives format details for entire cell. What about an Excel cell contains data like below and we want to show the excel cell values with formatting in a MVC application using a Rich Text Editor say KendoUI editor?

Bold data,
Italics, Underlined and big font in green color

Since everything in new Excel file format is XML based and open, the sdk provides the options. But not as easy as we think. We need to get the reference string and we need to iterate through the RunProperties collection. The code is as follows

private string GetCellValue(Cell excelCell)
        {
            if (excelCell == null) 
                return null;
            if (excelCell.DataType == null)
                return excelCell.InnerText;
 
            string value = excelCell.InnerText;
            switch (excelCell.DataType.Value)
            {
                case CellValues.SharedString:
                    value = ReadFromSharedString(excelCell);
                    break;
                case CellValues.Boolean:
                    switch (value)
                    {
                        case "0":
                            value = "false";
                            break;
                        default:
                            value = "true";
                            break;
                    }
                    break;
            }
            return value;
        }

This starts with the assumption that you have basic idea how to obtain Excel Cell object using open xml. If you are not clear on it please refer the link. Make sure you have the open xml sdk dll named DocumentFormat.OpenXml.dll. You may download from this link too.

What is SharedString and ReadFromSharedString? Actually excel keeps a reference to the value and the actual value will be there in a different node. The reference will normally be integer and that will be stored in the excelCell.InnerText property.Lets see the code for ReadSharedString();

private string ReadFromSharedString(Cell cell)
{
    string value = cell.InnerText;
    SharedStringTablePart sstPart = GetSharedTablePart(cell);
    // lookup value in shared string table
    if (sstPart != null && sstPart.SharedStringTable != null)
    {
        int index = int.Parse(value);
        if (sstPart.SharedStringTable.ElementAt(index).ChildElements.Count == 0)
        {
            value = sstPart.SharedStringTable.ElementAt(index).InnerText;
        }
        else
        {
            //ChildElements is collection of Run
            value = GetStringValueFromRuns(sstPart.SharedStringTable.ElementAt(index).ChildElements);
        }
    }
    return value;
}


First part is about how to get the SharedStringTablePart object? Next is about why we are calling GetValueFromRuns(). First things is simple. You can understand by looking the below code. Second one I will explain after the code segment to get the SharedStringTable.


private SharedStringTablePart GetSharedTablePart(Cell cell)
{
    Worksheet workSheet = cell.Ancestors<Worksheet>().FirstOrDefault();
    SpreadsheetDocument doc = workSheet.WorksheetPart.OpenXmlPackage as SpreadsheetDocument;
    SharedStringTablePart sharedStringTablePart = doc.WorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
    return sharedStringTablePart;
}


Now coming to the GetStringValueFromRuns. Basically the cell data is arranged as series of Run elements. Each run element represents unique formatting and corresponding text.In this case 'Bold Data' will be a run element and 'Italics' will be next run element. So we need to iterate the run collection and create the required formatting.


private string GetStringValueFromRuns(OpenXmlElementList openXmlElementList)
{
    StringBuilder text = new StringBuilder();
    foreach (Run run in openXmlElementList)
    {
        //    //t ADD OPENNING TAGS HERE, if any
        if (run.RunProperties != null)
        {
            foreach (OpenXmlLeafElement element in run.RunProperties.OrderBy(e => e.LocalName))
            {
                //StartTagWrite is the extension method.
                element.StartTagWrite(text);
            }
        }
        text.Append(run.Text.Text.Replace("\n","<br/>"));
        if (run.RunProperties != null)
        {
            foreach (OpenXmlLeafElement element in run.RunProperties.OrderByDescending(e => e.LocalName))
            {
                element.EndTagWrite(text);
            }
        }
    }
    return text.ToString();
}


Each Run will be having Run properties which define the attributes of each run. Important thing to note down is the Run properties classes are different in different Office versions.

Note the extension methods StartTagWrite and EndTagWrite. Those are defined in a different class


    static class Extension
    {
        public static void StartTagWrite(this OpenXmlLeafElement runElement, StringBuilder writer)
        {
            IWriteOperation x = new HTMLWriteOperation { Writer = writer };
            x.StartTagWrite((dynamic)runElement);
        }
        public static void EndTagWrite(this OpenXmlLeafElement runElement, StringBuilder writer)
        {
            IWriteOperation x = new HTMLWriteOperation { Writer = writer };
            x.EndTagWrite((dynamic)runElement);
        }
    }

Here comes a design challenge. Look at the casting using dynamic. This is because each run property is different class. ie there is no base class for run properties. The property class Bold Italic and Color don't have a common base. The base which we gets is the OpenXmlLeafNode. This is in fact a base class for many nodes other than Run properties. So we need to use the dynamic casting and call the method. Since its dynamic it will call corresponding methods defined in the interface.Below is the interface declaration and corresponding class


private interface IWriteOperation
{
    void StartTagWrite(Bold bold);
    void StartTagWrite(Italic it);
    void StartTagWrite(Underline underline);
    void StartTagWrite(Color c);
    void StartTagWrite(Font font);
    void StartTagWrite(FontSize fontSize);
    void StartTagWrite(FontFamily fontFamily);
    void StartTagWrite(FontScheme fontScheme);
    void StartTagWrite(RunFont runFont);
    void StartTagWrite(Strike strike);
    void StartTagWrite(VerticalTextAlignment vertialAlignment);
    void StartTagWrite(Shadow shadow);
    void StartTagWrite(Outline outline);
    void StartTagWrite(Condense condense);

    void EndTagWrite(Bold b);
    void EndTagWrite(Italic b);
    void EndTagWrite(Underline b);
    void EndTagWrite(Color u);
    void EndTagWrite(Font u);
    void EndTagWrite(FontSize u);
    void EndTagWrite(FontFamily u);
    void EndTagWrite(RunFont u);
    void EndTagWrite(FontScheme u);
    void EndTagWrite(Strike u);
    void EndTagWrite(VerticalTextAlignment u);
    void EndTagWrite(Shadow u);
    void EndTagWrite(Outline u);
    void EndTagWrite(Condense u);
}
private class HTMLWriteOperation : IWriteOperation
{
    public StringBuilder Writer { getset; }

    void IWriteOperation.StartTagWrite(Bold iThing) { Writer.AppendLine("<B>"); }
    void IWriteOperation.EndTagWrite(Bold iThing) { Writer.AppendLine("</B>"); }

    void IWriteOperation.EndTagWrite(Underline aThing) { Writer.AppendLine("</U>"); }
    void IWriteOperation.StartTagWrite(Underline aThing) { Writer.AppendLine("<U>"); }

    void IWriteOperation.StartTagWrite(Italic aThing) { Writer.AppendFormat("<I>"); }
    void IWriteOperation.EndTagWrite(Italic aThing) { Writer.AppendFormat("</I>"); }

    void IWriteOperation.StartTagWrite(Strike u) { Writer.AppendLine("<del>"); }
    void IWriteOperation.EndTagWrite(Strike aThing) { Writer.AppendLine("</del>"); }

    void IWriteOperation.StartTagWrite(Color u)
    {
        if (u.Rgb != null)
        {
            string colorFormat = @"<span style=""color:#{0};"">";
            string span = string.Format(colorFormat, u.Rgb.Value.Substring(2, 6));
            Writer.AppendLine(span);
        }
    }
    void IWriteOperation.EndTagWrite(Color aThing)
    {
        Writer.AppendLine("</span>");
    }
    void IWriteOperation.StartTagWrite(RunFont u)
    {
        if (u.Val.HasValue)
        {
            string fontFormat = @"<span style=""font-family:'{0}';"">";
            string span = string.Format(fontFormat, u.Val.Value);
            Writer.AppendLine(span);
        }
    }
    void IWriteOperation.EndTagWrite(RunFont aThing)
    {
        Writer.AppendLine("</span>");
    }

    void IWriteOperation.EndTagWrite(FontSize aThing)
    {
        Writer.AppendLine("</span>");
    }
    void IWriteOperation.StartTagWrite(FontSize u)
    {
        string fontSizeFormat = @"<span style=""font-size:{0}px;"">";
        string span = string.Format(fontSizeFormat, u.Val);
        Writer.AppendLine(span);
    }

    void IWriteOperation.StartTagWrite(Font u) { }
    void IWriteOperation.EndTagWrite(Font aThing) { }

    void IWriteOperation.EndTagWrite(FontScheme aThing) { }
    void IWriteOperation.StartTagWrite(FontScheme u) { }

    void IWriteOperation.EndTagWrite(Condense aThing) { }
    void IWriteOperation.StartTagWrite(Condense u) { }
    
    void IWriteOperation.EndTagWrite(VerticalTextAlignment aThing) { }
    void IWriteOperation.StartTagWrite(VerticalTextAlignment u) { }

    void IWriteOperation.EndTagWrite(Shadow aThing) { }
    void IWriteOperation.StartTagWrite(Shadow u) { }

    void IWriteOperation.StartTagWrite(FontFamily u) { }
    void IWriteOperation.EndTagWrite(FontFamily aThing) { }

    void IWriteOperation.StartTagWrite(Outline u) { }
    void IWriteOperation.EndTagWrite(Outline aThing) { }   
}

Reading a cell using OpenXML itself involves lot of code. So this is certainly not going to be easy to understand. Let me know if anybody needs a sample which does it.