Debugging Windows Service

Select the service name

Press the button Attach
Have a break point in your code to debug.

Exporting gridview to excel with multiple headers

In the code behind declare GridViewRow globally for the page.
Refer the link merging-gridview-with-multiple-headers
Add the ImageButton in the aspx page.

<asp:ImageButton ToolTip="Csv" ID="btnCsvDown" runat="server" ImageUrl="~/Images/csv.jpg" OnClick="ibtnDownload_Click" CommandName="Csv"/>

<asp:ImageButton ToolTip="Word" ID="btnWordDown" runat="server" ImageUrl="~/Images/word-icon.png" OnClick="ibtnDownload_Click" CommandName="Word" />

<asp:ImageButton ToolTip="Excel" ID="btnExcelDown" runat="server" ImageUrl="~/Images/excel-icon.png" OnClick="ibtnDownload_Click" CommandName="Excel"/>

<asp:ImageButton ToolTip="Pdf" ID="btnPdfDown" runat="server" ImageUrl="~/Images/pdf-icon.png" OnClick="ibtnDownload_Click" CommandName="Pdf"/>

Click events for all the download Image buttons.

protected void ibtnDownload_Click(object sender, ImageClickEventArgs e)
    {
        ImageButton imgButton = sender as ImageButton;
        GridViewExportUtil.Export("Report.xls", this.gvStylish, HeaderRow,
            DateTime.Now.AddDays(-20).ToShortDateString(),
            DateTime.Now.ToShortDateString(), imgButton.CommandName.ToUpper());
    }

The below source code is developed by Mattberseth.
I have updated this, when we are exporting a gridview with multiple headers. mattberseth

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;

public class GridViewMultipleHeadersExportUtil
{
    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "utf-8");
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("oCodepage", "65001");

        HttpContext.Current.Response.Charset = "utf-8";
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1253");

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid
                Table table = new Table();

                //  include the gridline settings
                table.GridLines = gv.GridLines;

                table.Rows.Add(CreateTableRow("Report Generated on: " +
                 DateTime.Now.ToString("MM/dd/yyyy"), 5));

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(gv.HeaderRow);

                    foreach(TableCell tcHeader in gv.HeaderRow.Cells )  
                        tcHeader.BackColor = System.Drawing.Color.LightGray;  
                    //gv.HeaderRow.BackColor = System.Drawing.Color.LightGray; 
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    //Set the default color  
                    System.Drawing.Color color = row.BackColor;
                    if (color != System.Drawing.Color.White)
                    {
                        row.BackColor = System.Drawing.Color.White;
                        foreach (TableCell tcRow in row.Cells)
                            tcRow.BackColor = color;
                    }

                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                //   if (gv.FooterRow != null)
                //   {
                //       GridViewMultipleHeadersExportUtil.PrepareControlForExport(gv.FooterRow);
                //       table.Rows.Add(gv.FooterRow);
                //   }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //render the htmlwriter into the response

                String s = sw.ToString();

                HttpContext.Current.Response.Write(s);
                HttpContext.Current.Response.End();
            }
        }
    }


    public static void Export(string fileName, GridView gv, GridViewRow HeaderRow, 
                              string fdate, string tdate, string strExportType)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);

        switch (strExportType)
        {
            case "WORD":
                HttpContext.Current.Response.ContentType = "application/ms-word";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".doc");
                break;
            case "EXCEL":
                HttpContext.Current.Response.ContentType = "application/ms-excel";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".xls");
                break;
            case "PDF":
                HttpContext.Current.Response.ContentType = "application/pdf";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".pdf");
                break;
            case "CSV":
                HttpContext.Current.Response.ContentType = "application/text";
                fileName = fileName.Replace(Path.GetExtension(fileName), ".csv");
                break;
        }

        HttpContext.Current.Response.AddHeader(
    "content-disposition", string.Format("attachment; filename={0}", fileName));

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid
                Table table = new Table();

                //  include the gridline settings
                table.GridLines = gv.GridLines;

                table.Rows.Add(CreateTableRow("Report from " + fdate + " to " + tdate, 5));
                table.Rows.Add(CreateTableRow("Report Generated on: " +
                 DateTime.Now.ToString("MM/dd/yyyy"), 5));

                //  add the new header row to the table
                if (HeaderRow != null)
                {
                    PrepareControlForExport(HeaderRow);

                    //This set entire excel row as LightGray
                    //HeaderRow.BackColor = System.Drawing.Color.LightGray;

                    //This set only columns we are exporting
                    foreach (TableCell tcHeader in HeaderRow.Cells)
                        tcHeader.BackColor = System.Drawing.Color.LightGray;

                    table.Rows.Add(HeaderRow);
                }

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    //Since we are merging the header cell. We have to remove the cell which are not needed
                    /**/
                    GridViewRow gridViewHeader = gv.HeaderRow;
                    for (int removeCell = 2; removeCell >= 0; removeCell--)
                        gridViewHeader.Cells.RemoveAt(removeCell);
                    
                    //This set only columns we are exporting
                    foreach (TableCell tcHeader in gridViewHeader.Cells)
                        tcHeader.BackColor = System.Drawing.Color.LightGray;
                    //gridViewHeader.BackColor = System.Drawing.Color.LightGray;
                    /**/

                    //GridViGridViewMultipleHeadersExportUtilewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    //table.Rows.Add(gv.HeaderRow);
                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(gridViewHeader);
                    table.Rows.Add(gridViewHeader);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(row);

                    /**/
                    //Set the default color
                    System.Drawing.Color color = row.BackColor;

                    //Set the entire excel row as white
                    if (color != System.Drawing.Color.White)
                        row.BackColor = System.Drawing.Color.White;

                    if (row.Cells[0].Text.ToUpper().Contains("TOTAL") ||
                        row.Cells[0].Text.ToUpper().Contains("GRAND"))
                    {
                        color = System.Drawing.Color.LightGray;
                        row.Font.Bold = true;
                    }

                    //Set the color only for the Total and Grand rows
                    if (color != System.Drawing.Color.White)
                        foreach (TableCell tcRow in row.Cells)
                            tcRow.BackColor = color;
                    /**/

                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewMultipleHeadersExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }
    /*
    private static void ExportToPdf()
    {
        // Create instance of a new document
        Document doc = new Document(PageSize.A4, 10, 10, 50, 50);
        // Change   the content type to application/pdf !Important
        HttpContext.Current.Response.ContentType = "application/pdf";
        // Get   Instance of pdfWriter to be able to create 
        // the document in the OutputStream
        PdfWriter.GetInstance(doc, HttpContext.Current.Response.OutputStream);
        // Create   Style Sheet
        StyleSheet styles = new iTextSharp.text.html.simpleparser.StyleSheet();
        //--styles.LoadTagStyle("ol", "leading", "16,0");
        doc.Add(new Header(iTextSharp.text.html.Markup.HTML_ATTR_STYLESHEET, "Style.css"));
        // Open the   document to be able to write to it
        doc.Open();
        //--styles.LoadTagStyle("li", "face", "garamond");
        //--styles.LoadTagStyle("span", "size", "8px");
        //--styles.LoadTagStyle("body", "font-family", "times new roman");
        //--styles.LoadTagStyle("body", "font-size", "10px");
        // Parse   html to PDF understandable objects
        var objects = iTextSharp.text.html.simpleparser.HTMLWorker.ParseToList(new StreamReader(Server.MapPath("Order.aspx"), Encoding.Default), styles);
        for (int k = 0; k < objects.Count; k++)
        {
            doc.Add((IElement)objects[k]);
        }
        // Close   the document, rendering it to the browser
        doc.Close();
    }
     */
   
    /// Replace any of the contained controls with literals
    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            //ctl.GetType().FullName == "System.Web.UI.WebControls.DataControlLinkButton")
            Control current = control.Controls[i];
            switch (current.GetType().Name)
            {
                case "LinkButton":
                    //ReplaceControl(control, i, (current as LinkButton).Text);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                    break;
                case "ImageButton":
                    //ReplaceControl(control, i, (current as ImageButton).AlternateText);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                    break;
                case "HyperLink":
                    //ReplaceControl(control, i, (current as HyperLink).Text);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                    break;
                case "DropDownList":
                    //ReplaceControl(control, i, (current as DropDownList).SelectedItem.Text);
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                    break;
                case "CheckBox":
                    //ReplaceControl(control, i, (current as CheckBox).Checked ? "True" : "False");
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                    break;
            }
           
            if (current.HasControls())
            {
                GridViewMultipleHeadersExportUtil.PrepareControlForExport(current);
            }
        }
    }

    //private static void ReplaceControl(Control control, int i, string text)
    //{
    //    control.Controls.Remove(text);
    //    control.Controls.AddAt(i, new LiteralControl(text));
    //}

    public static TableRow CreateTableRow(string text, int iColSpan)
    {
        TableRow objTableRow = new TableRow();
        TableCell tc = new TableCell();
        tc.Text = text;
        tc.HorizontalAlign = HorizontalAlign.Left;
        tc.Font.Bold = true;
        tc.ColumnSpan = iColSpan;
        objTableRow.Cells.Add(tc);
        return objTableRow;
   }
}

Export GridView with Images to Word Excel and PDF Formats in ASP.Net Soure Code References

Merging Gridview with Multiple Headers

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : System.Web.UI.Page 
{
    #region Declarations
    CustomersDataObject objData = null;
    GridViewRow HeaderRow = null;
    #endregion

    #region Events
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            objData = new CustomersDataObject();
            //DataTable dt =  objData.Select().Table ;
            gvStylish.DataSource = objData.Select();
            gvStylish.DataBind();
        }
    }

    protected void gvStylish_RowCreated(object sender, GridViewRowEventArgs e)
    {
        //http://www.codeproject.com/Articles/249155/Rows-and-Columns-Merging-in-ASP-NET-GridView-Contr
        //http://sammisoft.net/mboard/mboard/mboard.asp?exe=view&board_id=pds&group_name=sammisoft&idx_num=194&page=22&category=0&search_category=&search_word=&order_c=intVote&order_da=asc
        //http://www.thaicreate.com/dotnet/forum/048135.html

        if (e.Row.RowType == DataControlRowType.Header)
        {
            GridView HeaderGrid = (GridView)sender;
            HeaderRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
            for (int cellCount = 0; cellCount < e.Row.Cells.Count; cellCount++)
            {
                if (e.Row.Cells[cellCount].Text.Trim() == "CustomerID"
                    || e.Row.Cells[cellCount].Text.Trim() == "CompanyName"
                    || e.Row.Cells[cellCount].Text.Trim() == "ContactName")
                {
                    //Row Span
                    HeaderRow.Cells.Add(HeaderRowMerg(e, 2, cellCount, System.Drawing.Color.Gray.Name));
                }

                else
                {
                    //Col Span
                    HeaderRow.Cells.Add(HeaderColumnMerg(e, 3, "Subjects", System.Drawing.Color.Gray.Name));
                    break;
                }

            }
            gvStylish.Controls[0].Controls.AddAt(0, HeaderRow);
        }
    }

    /// 

    /// Merging datarow cells
    /// 
    /// /// protected void gvStylish_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            /*Merging starts*/
            int _iNoOfCellsToCheck = 6;
            for (int rowIndex = gvStylish.Rows.Count - 2; rowIndex >= 0; rowIndex--)
            {
                GridViewRow gvRow = gvStylish.Rows[rowIndex];
                GridViewRow gvPreviousRow = gvStylish.Rows[rowIndex + 1];
                //for (int cellCount = 0; cellCount < gvRow.Cells.Count; cellCount++)
                for (int cellCount = 1; cellCount < _iNoOfCellsToCheck; cellCount++)
                {
                    if (gvRow.Cells[cellCount].Text == gvPreviousRow.Cells[cellCount].Text)
                    {
                        gvRow.Cells[cellCount].RowSpan =
                            gvPreviousRow.Cells[cellCount].RowSpan < 2 ?
                            2 :
                            gvPreviousRow.Cells[cellCount].RowSpan + 1;

                        gvPreviousRow.Cells[cellCount].Visible = false;
                    }
                }
            }
            /*Merging ends*/

            //If we want to set background color for the row
            //if (e.Row.Cells[0].Text.Trim().ToUpper().Contains("TOTAL"))
            if (e.Row.Cells[0].Text.Trim().ToUpper().Contains("MUTHU"))
            {

                SetRowBackColor(e, System.Drawing.Color.Silver.Name);
            }
            else
                //if (e.Row.Cells[0].Text.Trim().ToUpper().Contains("GRAND"))
                if (e.Row.Cells[0].Text.Trim().ToUpper().Contains("VIJAY"))
                {
                    SetRowBackColor(e, "#999999");
                }
        }
    }
    #endregion

    #region Functions
    private TableCell HeaderRowMerg(GridViewRowEventArgs e, int iRowSpan, int iIndex, string backcolor)
    {
        TableHeaderCell Cell_Header = new TableHeaderCell();
        Cell_Header.Text = e.Row.Cells[iIndex].Text;
        Cell_Header.HorizontalAlign = HorizontalAlign.Center;
        Cell_Header.Font.Bold = true;
        Cell_Header.RowSpan = iRowSpan;
        Cell_Header.Style.Add("background-color", backcolor);
        e.Row.Cells[iIndex].Attributes.Add("style", "display: none;");
        return Cell_Header;
    }

    private TableCell HeaderColumnMerg(GridViewRowEventArgs e, int iColumnSpan, string _strHeaderText, string backcolor)
    {
        TableHeaderCell cell = new TableHeaderCell();
        Cell_Header.Text = _strHeaderText;
        Cell_Header.HorizontalAlign = HorizontalAlign.Center;
        Cell_Header.ColumnSpan = iColumnSpan;
        Cell_Header.Font.Bold = true;
        Cell_Header.Style.Add("background-color", backcolor);
        return Cell_Header;
    }

    bool IsInt(string strVal)
    {
        try
        {
            int value = int.Parse(strVal);
            return true;
        }
        catch
        {
            return false;
        }
    }

    private void SetRowBackColor(GridViewRowEventArgs e, string color)
    {
        e.Row.BackColor = System.Drawing.Color.FromName(color);
        e.Row.Font.Bold = true;
        foreach (TableCell tc in e.Row.Cells)
        {
            if (IsInt(tc.Text))
                tc.Text = Convert.ToInt32(tc.Text).ToString("#,#");
        }
    }
    #endregion
}

AddressAccessDeniedException in WCF 2010

Today when executing the service it gives the below exception.
HTTP could not register
URL http://+:8002/.../. Your process does not have access rights to this namespace (see http://go.microsoft.com/fwlink/?LinkId=70353 for details).

The reason for the above exception is given in the book Programming WCF Services O'REILLY by JUVAL LOWY

How to instruct windows to register the URL

• Go to programs and select "Visual Studio Tools"
• Right click on Visual Stutdio Command Prompt and select Run as administrator

We get a command prompt shows the path "C:\WINDOWS\system32" We have to give netsh http add urlacl url=http://+:8002/ user="MachineOrDomain\UserName"

@@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

@@IDENTITY	SCOPE_IDENTITY	IDENT_CURRENT	IDENT_SEED	IDENT_INCR
Any table Current session Any scopes.	Any table Current session Current scope.	Specific table Any session Any scope.	Returns the original seed value If we re-seed using DBCC CHECKIDENT won't change the value.	Returns the increment value specified during the creation of an identity column in a table or view that has an identity column.
Table or View	Table or View	Table or View
If user truncates or drop the table also, it will return the last identity value generated in the current session	If user truncates or drop the table also, it will return the last identity value generated in the current session	If user truncated the table it will return the Ident_Seed value. It return null when user drop the table.

A scope is a module: a stored procedure, trigger, function, or batch.
Each query window in Query Analyzer is a session.

How to get all the session running in the server?
EXEC sp_who 'test';

How to check the current session

SELECT @@SPID       SessionID,
       USER_NAME()  AS [User_Name] ,
       SUSER_NAME() AS [Login identification name of the user.],
       CURRENT_USER AS [Name of the current user] ,
       SYSTEM_USER  AS [System_user],
       SESSION_USER AS [Session_user],
       USER         AS [User]

IDENT_SEED

1. DBCC CHECKIDENT ('dbo.IdentityTest', RESEED, 10)
2. Returns the original seed value, if we re-seed using DBCC CHECKIDENT.

3. IDENT_SEED will return the seed value created for the Idetntiy column( when table creation or Alteration).

   It won't changed by DBCC CHECKIDENT ('dbo.IdentityTest', RESEED, 10)

References
RESED Identity Column
SQLAuthority

Trim Leading Or Trailing Control Characters

DECLARE @MyString VARCHAR(1000)
--Control character

DECLARE @Tab            VARCHAR(2)
DECLARE @LineFeed       VARCHAR(2)
DECLARE @CarriageReturn VARCHAR(2)
DECLARE @SearchPattern  VARCHAR(10)

SET @Tab            =              CHAR(9)
SET @LineFeed       =              CHAR(10)
SET @CarriageReturn =              CHAR(13)

--Search for any single of more chars not with specified range
SET @SearchPattern = '%[^ '   + @Tab + @LineFeed + @CarriageReturn + ']%'

SET @MyString = CHAR(10) + ' ' + CHAR(10)+ CHAR(9) + 'My name is ' + CHAR(10) 
				+ ' babu ' + CHAR(10) + ' ' + CHAR(10) + CHAR(10) + ' ' + CHAR(10)

SELECT LEN(@MyString) AS TotalLength
SELECT @MyString AS MyString

SELECT LEN( 
RTRIM( LTRIM( 
	SUBSTRING( @MyString, 
		PATINDEX(@SearchPattern, @MyString), 
		LEN(@MyString) - PATINDEX(@SearchPattern, @MyString) - PATINDEX(@SearchPattern, 
		REVERSE(@MyString)) + 2
		) 
	) ) 
) AS AfterTrimedLength

SELECT 
RTRIM( LTRIM( 
	SUBSTRING( @MyString, 
		PATINDEX(@SearchPattern, @MyString), 
		LEN(@MyString) - PATINDEX(@SearchPattern, @MyString) - PATINDEX(@SearchPattern, 
		REVERSE(@MyString)) + 2
		) 
	) ) AS AfterTrimed

Auto center pop up window

function PopupCenter() {
    var width = 600;
    var height = 480;
    var top = (screen.height / 2) - (height / 2);
    var left = (screen.width / 2) - (width / 2);

    var parametar = 'toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=no, resizable=no, copyhistory=no, width=' + width + ', height=' + height + ', top=' + top + ', left=' + left;
    var url = 'Default.aspx'
    var windowName = 'Default';
    var targetWin = window.open(url, windowName, parametar);
    targetWin.focus();
    return false;
}

Page with query string

function PopupCenter(Name, Age) {
    var width = 600;
    var height = 480;
    var top = (screen.height / 2) - (height / 2);
    var left = (screen.width / 2) - (width / 2);

    var parametar = 'toolbar=no, location=no, directories=no, status=no, menubar=no, scrollbars=no, resizable=no, copyhistory=no, width=' + width + ', height=' + height + ', top=' + top + ', left=' + left;
    var url = 'Default.aspx?Name=' + Name + '&Age=' + Age;
    var windowName = 'Default';
    var targetWin = window.open(url, windowName, parametar);
    targetWin.focus();
    return false;
}

References
www.javascripter.net
msdn
htmlgoodies