Wednesday, December 21, 2011

Session Timeout in IIS 7 - Worker Process of ApplicationPool

Recently while testing my application, I was facing problem of Session time out.
By Default Session time is 20 mins and I wanted to extend it
if we want to extend it we can set in web.config within System.web tag
<sessionState mode="InProc" timeout="30"></sessionState>
that I already have done.
I set it as 30 mins.
but still my session was getting timed out after 20 min.
So I doubt that it doesn’t apply to my Code so I set It by code also like
System.Web.HttpContext.Current.Session.Timeout = 30;

still no progress


after a long invastigation I came to know Its because Of Default Settings of ApplicationPool for the application in IIS.

There is a setting for Worker process of IIS which will set memory and resource to allot to application application wide.

in my case I was testing my application alone. So application was being idle for more than 20 min to check weather session is alive or not..

To open the settings refer the steps

  1. Type “inetmgr” in run window to open IIS
  2. from left pane “Connections” select your application.
  3. right click on your application and refer the image to open “Advanced Settings” of application(to check which ApplicationPool is referred by application )1
  4. from “Application Settings” window you can see the Name of ApplicationPool. like V4.0, DefaultAppPool, V4.0 Classic.
  5. now close both opened windows.
  6. from left pan double click on “Application Pools”, a list of created application pools will be displayed. if you want to create new application pool for specific application refer this
  7. Right click on the ApplicationPool that was assigned to your Application and click on "Advanced Settings...."
  8.  in the popup window ... check the Idle time out for ... that cause the timeout while web application is idle.
Here are the steps to set Idle time..

Happy Deploying...

Thursday, December 15, 2011

Steps to Remove Menu options in Export for Report Page in SSRS

In SSRS(Sql Server Reporting Service), I wanted to remove extra options to export my report.

Here are the steps to remove option to export like (word, Excel, PDF, MHTML etc...) in SSRS (Sql Server Reporting Service)

Installation folder may be different from MSSQL.1, can be MSSQL.x (search for rsreportserver.config under Program Files\Microsoft SQL Server if not found)
Here are the steps for Disabling Excel Export Option in Reporting Services -
  1. Go to Database server where reporting services is installed, open the rsreportserver.config file located at <Drive Letter>:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer 
  2. Go to section.  
  3. Change this entry to i.e. add Visible="false" for Excel rendering extension 
  4. Same for PDF.  
Happy Reporting

Saturday, November 19, 2011

Create Excel File From DataTable

Hello Friends,
Many times we need to create Excel files in our application using existing Data.
I have figured out a class which will help to create excel file by just passing DataTable and preferred location to generate on server.
please download the cs file from here .. and use it :)

here is full code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Xml;

    public class ExcelHelper
        private static readonly ExcelHelper _instance = new ExcelHelper();

        public static ExcelHelper Instance
            get { return _instance; }
        /// <summary>
        /// Create one Excel-XML-Document with SpreadsheetML from a DataTable
        /// </summary>
        /// <param name="dataSource">Datasource which would be exported in Excel</param>
        /// <param name="fileName">Name of exported file</param>
        public void Create(DataTable dtSource, string strFileName)
            // Create XMLWriter
            using (XmlTextWriter xtwWriter = new XmlTextWriter(strFileName, Encoding.UTF8))

                //Format the output file for reading easier
                xtwWriter.Formatting = Formatting.Indented;

                // <?xml version="1.0"?>

                // <?mso-application progid="Excel.Sheet"?>
                xtwWriter.WriteProcessingInstruction("mso-application", "progid=\"Excel.Sheet\"");

                // <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet >"
                xtwWriter.WriteStartElement("Workbook", "urn:schemas-microsoft-com:office:spreadsheet");

                //Write definition of namespace
                xtwWriter.WriteAttributeString("xmlns", "o", null, "urn:schemas-microsoft-com:office:office");
                xtwWriter.WriteAttributeString("xmlns", "x", null, "urn:schemas-microsoft-com:office:excel");
                xtwWriter.WriteAttributeString("xmlns", "ss", null, "urn:schemas-microsoft-com:office:spreadsheet");
                xtwWriter.WriteAttributeString("xmlns", "html", null, "");

                // <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
                xtwWriter.WriteStartElement("DocumentProperties", "urn:schemas-microsoft-com:office:office");

                // Write document properties
                xtwWriter.WriteElementString("Author", "Sandeep Prajapati");
                xtwWriter.WriteElementString("LastAuthor", "Sandeep Prajapati");
                xtwWriter.WriteElementString("Created", DateTime.Now.ToString("u") + "Z");
                xtwWriter.WriteElementString("Company", "XXXXXXXXXX");
                xtwWriter.WriteElementString("Version", "12");

                // </DocumentProperties>

                // <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
                xtwWriter.WriteStartElement("ExcelWorkbook", "urn:schemas-microsoft-com:office:excel");

                // Write settings of workbook
                xtwWriter.WriteElementString("WindowHeight", "8010");
                xtwWriter.WriteElementString("WindowWidth", "14805");
                xtwWriter.WriteElementString("WindowTopX", "240");
                xtwWriter.WriteElementString("WindowTopY", "105");
                xtwWriter.WriteElementString("ProtectStructure", "False");
                xtwWriter.WriteElementString("ProtectWindows", "False");

                // </ExcelWorkbook>

                // <Styles>

                // <Style ss:ID="Default" ss:Name="Normal">
                xtwWriter.WriteAttributeString("ss", "ID", null, "Default");
                xtwWriter.WriteAttributeString("ss", "Name", null, "Normal");

                // <Alignment ss:Vertical="Bottom"/>
                xtwWriter.WriteAttributeString("ss", "Vertical", null, "Bottom");

                // Write null on the other properties
                xtwWriter.WriteElementString("Borders", null);
                xtwWriter.WriteElementString("Font", null);
                xtwWriter.WriteElementString("Interior", null);
                xtwWriter.WriteElementString("NumberFormat", null);
                xtwWriter.WriteElementString("Protection", null);

                // </Style>

                //<Style ss:ID="s16">
                xtwWriter.WriteAttributeString("ss", "ID", null, "s16");
                xtwWriter.WriteAttributeString("ss", "Bold", null, "1");
                xtwWriter.WriteAttributeString("ss", "Size", null, "11");
                xtwWriter.WriteAttributeString("ss", "Underline", null, "Single");

                // </Style>

                // </Styles>

                // <Worksheet ss:Name="xxx">
                xtwWriter.WriteAttributeString("ss", "Name", null, dtSource.TableName);

                // <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1" x:FullRows="1" ss:DefaultColumnWidth="60">
                xtwWriter.WriteAttributeString("ss", "ExpandedColumnCount", null, dtSource.Columns.Count.ToString());
                xtwWriter.WriteAttributeString("ss", "ExpandedRowCount", null, (dtSource.Rows.Count + 1).ToString());
                xtwWriter.WriteAttributeString("x", "FullColumns", null, "1");
                xtwWriter.WriteAttributeString("x", "FullRows", null, "1");
                //xtwWriter.WriteAttributeString("ss", "DefaultColumnWidth", null, "60");

                // Run through all rows of data source

                // <Row>
                foreach (DataColumn Header in dtSource.Columns)
                    // <Cell>
                    xtwWriter.WriteAttributeString("ss", "StyleID", null, "s16");

                    // <Data ss:Type="String">xxx</Data>
                    xtwWriter.WriteAttributeString("ss", "Type", null, "String");
                    // Write content of cell

                    // </Data>

                    // </Cell>


                foreach (DataRow row in dtSource.Rows)
                    // <Row>

                    // Run through all cell of current rows
                    foreach (object cellValue in row.ItemArray)
                        // <Cell>
                        //if (cnt == 0)
                        //    xtwWriter.WriteAttributeString("ss", "StyleID", null, "s16");

                        // <Data ss:Type="String">xxx</Data>
                        xtwWriter.WriteAttributeString("ss", "Type", null, "String");
                        // Write content of cell
                        string strcellValue = (cellValue == System.DBNull.Value ? string.Empty : (string)cellValue);

                        // </Data>

                        // </Cell>
                    // </Row>
                // </Table>

                // <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
                xtwWriter.WriteStartElement("WorksheetOptions", "urn:schemas-microsoft-com:office:excel");

                // Write settings of page
                xtwWriter.WriteAttributeString("x", "Margin", null, "0.4921259845");
                xtwWriter.WriteAttributeString("x", "Margin", null, "0.4921259845");
                xtwWriter.WriteAttributeString("x", "Bottom", null, "0.984251969");
                xtwWriter.WriteAttributeString("x", "Left", null, "0.78740157499999996");
                xtwWriter.WriteAttributeString("x", "Right", null, "0.78740157499999996");
                xtwWriter.WriteAttributeString("x", "Top", null, "0.984251969");

                // <Selected/>
                xtwWriter.WriteElementString("Selected", null);

                // <Panes>

                // <Pane>

                // Write settings of active field
                xtwWriter.WriteElementString("Number", "1");
                xtwWriter.WriteElementString("ActiveRow", "1");
                xtwWriter.WriteElementString("ActiveCol", "1");

                // </Pane>

                // </Panes>

                // <ProtectObjects>False</ProtectObjects>
                xtwWriter.WriteElementString("ProtectObjects", "False");

                // <ProtectScenarios>False</ProtectScenarios>
                xtwWriter.WriteElementString("ProtectScenarios", "False");

                // </WorksheetOptions>

                // </Worksheet>

                // </Workbook>

                // Write file on hard disk

Write me if help full
Happy Coding..

Friday, November 18, 2011

Scroll to particular record in GridView

Hello Friends,
here is a tip to scroll the gridview to particular record.
the basic concept is html scrolling. to scroll at particular position create an anchor tag with name as address (eg. #ScrollPoint '# is must')

now redirect the page to the location using javascript call
window.location.href = "#ScrollPoint";

same thing can be achieved by anchorlink href
here is sample code

<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" OnClientClick="return MoveTo();" />
    <div style="height: 100px; overflow: auto;">
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
                <asp:TemplateField HeaderText="ID" SortExpression="ID">
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("ID") %>'></asp:TextBox>
                        <a name='<%# Eval("ID") %>'></a>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("ID") %>'></asp:Label>
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />

<script type="text/javascript">
        function MoveTo() {
            window.location.href = "#" + document.getElementById('<%= TextBox2.ClientID %>').value;
            return false;

Code Behind
protected void Page_Load(object sender, EventArgs e)
            if (!IsPostBack)

        private void BindControls()
            DataTable dt = new DataTable();
            for (int i = 0; i < 200; i++)

            GridView1.DataSource = dt;


        private static void AddRow(DataTable dt)
            DataRow dr = dt.NewRow();
            dr["ID"] = dt.Rows.Count + 1;
            dr["Name"] = "Sandeep" + dt.Rows.Count + 1;
Happy Coding...

Wednesday, September 14, 2011

Power of common table expression (CTE)

Hello friends,
Recently I came across a case where I was supposed to generate report for Customer based on services availed to him and for that I used A common table expression (CTE) and it really impressed.
initially I referred this great document of MSDN
here is my Table of data.(Actual Tables are not like this, I have managed a simplified Table for the Demo.)
Original Table Sandeep
Here is query to generate Table
declare @t table (id int identity,CustID int, AgreementName varchar(100), ServDate Datetime,Price float)
insert @t select 70       ,'First Agreement',CONVERT(datetime,'2011-01-05'),10
insert @t select 70       ,'Second Agreement',CONVERT(datetime,'2011-01-06'),10
insert @t select 75       ,'First Agreement',CONVERT(datetime,'2011-03-14'),10
insert @t select 70       ,'Second Agreement',CONVERT(datetime,'2011-01-05'),30
insert @t select 75       ,'Third Agreement',CONVERT(datetime,'2011-01-05'),10
insert @t select 90       ,'Third Agreement',CONVERT(datetime,'2011-04-16'),20
insert @t select 75       ,'Third Agreement',CONVERT(datetime,'2011-05-05'),10
insert @t select 90       ,'Third Agreement',CONVERT(datetime,'2011-02-20'),90
select * from @t

The scenario is to display Customer’s total bill based on the services availed to him according to Service Agreement and Date..

And Expect out put is as follow

Output Table Sandeep

For that I used the following query

;with cteTable(CustID,combined,inx,price)
select CustID,  AgreementName + ' ['+ CONVERT(varchar,ServDate,106)+']'                        
, inx=ROW_NUMBER() over (PARTITION by CustID order by AgreementName, ServDate),Price
from @t t
select CustID, convert(varchar(max),combined), 1,(price) from cteTable where inx=1
union all
select cteConcate.CustID, convert(varchar(max),cteConcate.finalstatus+', '+cteTable.combined), cteConcate.inx+1, cteTable.price
from cteConcate
inner join cteTable on cteTable.CustID = cteConcate.CustID and cteTable.inx=cteConcate.inx+1

select CustID, MAX(finalstatus) Description ,sum(Price1) Price from cteConcate group by CustID

if have any query please let me know.

Happy Coding….

Friday, June 10, 2011

Access UserControl from Page

To perform some reparative operation in application, its preferred to use UserControls.
Not necessary to have same display and bindings at everyplace. In that case we need to set values or call methods or pass values accordingly.
Here I have tried to figure out three cases, Hope it is helpful.
  1. Pass Table to Usercontrol and Set dynamically value to Controls
  2. Set Properties of control
  3. Call Usercontrol's Methods

Monday, June 6, 2011

Set Default Value as Function to column of a Table

Generally we need to set default value to particular column in Database Table like isActive is true, IsDeleted is false.
same way many times we set CreatedOn Date as CurrentDate and that is by query of insert GetDate().
Static Values :
Its very easy to set some static value as Default value for column and that is as follow :

Functions :
Set Default Date for Createdon Field as Follow :
User Defined Functions :
Set User Defined function as Default Value as follow:
In my case I am supposed to get Random Hexa Code for Color. so I Have created an User Defined function for that
CREATE FUNCTION [dbo].[GetRandomColorCode]
RETURNS varchar(6)
Declare @Color varchar(6)

Now set the function in columns as follow :


If this do not allow us to set Default value try following query to set

 DF_Table_1_ColorCode DEFAULT ([dbo].[GetRandomColorCode]()) FOR ColorCode

All the best

Happy Coding..

Thursday, May 26, 2011

Track Error in Javascript Page

Many times we have faced problems to find what is javascript errors and exactly where??
To track javascript error, It is a good practice to write code in Try..Catch
but sometimes some small syntax error are not allowing to execute the whole js file.
so to track such error during development, we can track by this sample code
  <script type="text/javascript">
        onerror = CallOnError;
        function CallOnError(msg, url, line) {
            var strErrMessage = "";
            strErrMessage += "Message: " + msg + "\n";
            strErrMessage += "Page: " + url + "\n";
            strErrMessage += "Line Number: " + line + "\n\n";
            return true;

Here is a sample error

try to put on your page

  <script type="text/javascript">
            alert('This is First Test Page);

Enjoy Coding

Monday, May 16, 2011

Second Highest Salary- Sql Query

Create Table #Emp(id int identity primary key,
    Emp varchar(100),
    Salary int)

insert into #Emp
select 'XXXX',17000 union all
select 'DDDD',21000 union all
select 'WWWW',30000 union All
Select 'HHHH',21000 union All
select 'CCCC',30000 union All
Select 'TTTT',21000 union All
SElect 'PPPP',21000

select * from #Emp

select #Emp.* 
from (select ROW_NUMBER() over (order by salary desc) as RowNumber, Salary
   from #Emp
    group by Salary) as tempEmp inner join #Emp on tempEmp.Salary = #Emp.Salary
   where tempEmp.RowNumber=2

Drop Table #Emp

Data Will Look like this


Output will be like this


Friday, April 29, 2011

Protect image from being copied

Hello Friends,

Many times I am asked to protect some copyrighted images over web,
so finally I have found a better way to protect from being copied.
There are some cases by which we can copy the image
Here I have taken care of following cases
  • Right click on image and Save the Image
  • Save the Entire page and all images displayed on page will be downloaded to its Data Folder
  • in mozila Firefox, We can save the media files by following steps
    1. Right click on Page, select “View Page Info”
    2. ViewSourceSandeep
    3. It will open a window, Select “Media” tab, here a list of all files will be available
    4. select particular file and click on “Save As” button to save the media
    5. SavemediaSandeep

Now Let’s Start our main topic,that is protect image from being copied
There is a concept called Data URI scheme.
Generally to display any image on page, we use Relative_URL and if we display image using this, the images can be easily copied.
Here we will save image in database and retrieve the same
To Create table
CREATE TABLE [dbo].[Images](
 [ImageID] [int] IDENTITY(1,1) NOT NULL,
 [ImageName] [varchar](50) NULL,
 [Image] [image] NULL,
 [ext] [varchar](50) NULL,
 [ImageID] ASC


the aspx page will be very simple like

Monday, April 4, 2011

Fetch Data from Control Without Loop- Best Use of LINQ

Hi friends….

Many times I need to generate collection type Data from GridView or Repeater or any other control.. and in that case I used to loop through each rowitem of particular control..

here each row of repeater is listitem of LINQ query.

same way you can perform for all DataBound Controls like GridView, Repeater, DataList, DropDownlist, CheckboxList, Radiobuttonlist etc…

but after using LINQ, I am able to do that very easily

here is sample of Repeater control to Get List of data

List ctrls = (from item in rptctrlList.Items.Cast()
                                    where ((CheckBox)item.FindControl("chkctrl")).Checked
                                    select new ctrlInfo()
                                        ctrlId = Convert.ToInt32(((HiddenField)item.FindControl("hdnctrlId")).Value),
                                        UserctrlId = Convert.ToInt64(((HiddenField)item.FindControl("hdnUserctrlId")).Value),

now I can pass XML to database from this List<ctrlInfo> using


var ctrlXML = from ctrl in ctrls
                          select new XElement("ctrl",
                          new XElement("UserId", ctrl.UserId),
                          new XElement("ctrlId", ctrl.ctrlId),
                          new XElement("UserctrlId", ctrl.UserctrlId));

Now…. You can pass the XML to Stored Procedure. and in Stored Procedure


c.value('ctrlId[1]','int') As ctrlId
, c.value('UserId[1]','BigInt') As UserId
, c.value('UserctrlId[1]','BigInt') As UserctrlId
FROM @ctrlIdListXML.nodes('/ctrls/ctrl')e(c)

you can have Table of selected records of Repeater control.

using merge statement you can perform operation.


Hope this Helps

All the Best

Friday, April 1, 2011

Apply Frame to Image with CSS


Many times we need to display image like profile image or advertisements

and at that time we need to decorate it within frame…


Here we have a simplest way to apply frame which will help to set frame to any size image and unique for all..

here I have a sample code for it

img {
    background: url("shadow.gif") no-repeat scroll right bottom transparent;
    border-color: #EEEEEE -moz-use-text-color -moz-use-text-color #EEEEEE;
    border-right: medium none;
    border-style: solid none none solid;
    border-width: 1px medium medium 1px;
    padding: 4px 10px 10px 4px;}
     <img src=''>

and the you can have any kind of frame image

here I am uploading a sample image


and the output you will get is


All the Best

Monday, March 7, 2011

Publish webservice locally without separate hosting


here is a way to deploy web service withing project...
Here are the steps for it

in your Project... right click on Project in Solution Explorer.
Click on Add new Item and Select "Web Service"

Name it as per required... and Click on "Add" Button...

now you have your web service ready in local environment...

put you code in Web Service .Cs file

Build the Source and make sure its error free...

now Deployment Part

Right click on "References" in Solution Exporer,

You will find.. three Options like

  •     Add Reference
  •     Add Web Reference
  •     Add Service Reference

Select "Add Web Reference",

you will get a Pop up,

in Below there are three links
Browse to:

    * Web services in this solution
    * Web services on the local machine
    * Browse UDDI Servers on the local network

select "Web services on the local machine "

You will get a list of Web Services existsing on your local machine

select which one you created in current Project

it will be something like


now you will get a list of your methods of the web Service..

if till here everything is fine... that means you are going right....


At right side, in Web Reference Name if wish change the name , that name will be the Service name..

now click on "Add Reference"

now in your project you are able to run your service within your IIS.. without any external Deployment

Now whenever you update the Source of Service class... before building the source one update the Service Reference by right click on Service in Web reference Folder in solution explorer..

To change url of your Service...
  1. Remove Url Value from Settings.
  • Steps
    • In Solution Explorer, In Properties Folder, double click on Settings.settings file
    • remove the path.
    • Now in Web.Config file set your Server path Instead of localhost

Now world is yours...

All The Best