.Net Development ,SQL server , Sharepoint and Jquery Tips and tricks

SQL SERVER – Simple Example of Recursive CTE

2012-01-26T22:04:00.000-08:00

Table name is WorkFlow :

In the above table every workflow step contains its next step Id .So i want to get the record in which sequence starts from initial state and iterates to final step. For this Recursive CTE is the best solution in SQL SERVER

Query tro fetch the sequence is

WITH Emp_CTE AS (
SELECT WorkFlowId ,Name ,NextSteps, 0 As Level1
FROM WorkFlow
WHERE NextSteps IS NULL

UNION ALL
SELECT e.WorkFlowId ,e.Name ,e.NextSteps,ecte.Level1 + 1
FROM WorkFlow e
INNER JOIN Emp_CTE ecte ON ecte.WorkFlowId = e.NextSteps
)
SELECT *
FROM Emp_CTE
order by level1 desc
GO

Result set given below

Joins in LINQ to SQL

2011-12-21T21:25:00.000-08:00

The following post shows how to write different types of joins in LINQ to SQL. I am using the My Sample database and LINQ to SQL for these examples

SampleDbDataContext dataContext = new SampleDbDataContext();

Inner Join :-

var q1 = from b in dataContext.Blogs
join c in dataContext.Comments on b.BlogId equals c.BlogId
select new
{
b.BlogId,
b.BlogTitle,
b.BlogDescription,
c.CommentsId,
c.CommentDescription
};

Above LINQ In SQL

SELECT [b].[BlogId], [b].[BlogTitle], [b].[BlogDescription], [c].[CommentsId],[c].[CommentsDescription]
FROM [dbo].[Blogs] AS [b]
INNER JOIN [dbo].[Comments] AS [c] ON [b].[BlogId] = [c].[BlogId]

Left Join :-

var q2 = from b in dataContext.Blogs
join c in dataContext.Comments on b.BlogId equals c.BlogId into g
from s in g.DefaultIfEmpty()
select new
{
b.BlogId,
b.BlogTitle,
b.BlogDescription,
s.CommentsId,
s.CommentDescription
};

Above LINQ In SQL

SELECT [b].[BlogId], [b].[BlogTitle], [b].[BlogDescription], [c].[CommentsId],[c].[CommentsDescription]
FROM [dbo].[Blogs] AS [b]
LEFT OUTER JOIN [dbo].[Comments] AS [c] ON [b].[BlogId] = [c].[BlogId]

LinkedIn button on profile page

2011-12-20T23:19:00.000-08:00

Something you may want to do, if you have a LinkedIn account (it's free!) is to add a button in your webpage or blog to make it easier for people to see your LinkedIn profile.

Here's how to find the button and code to paste into your site.

1. Go to your Profile page in LinkedIn
2. Click "My Profile"
3. Click on "Edit Public Profile Settings" (on the top right of the page)
4. Click "Promote your profile with customized buttons"
5. Pick a button, copy the code

or easier yet: http://www.linkedin.com/profile?promoteProfile=

Also, make sure your profile is not set to Private. Make it viewable to the world.

For users of Blogger, where to place the button.

In Blogger, you have to go to the Layout tab, click on "Add a Gadget" and add an "HTML/JavaScript" section where you paste the code. Title section is not mandatory.

How to find a url of Active tab from Active Internet Explore Instance

2011-09-28T03:20:00.000-07:00

First Add new class name as IEInstance.cs

The code for this class

using System;
using System.Drawing;
using System.ComponentModel;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Text;
using System.Collections;
public delegate bool IECallBack(int hwnd, int lParam);
namespace IEToolbarEngine
{
public class IEInstance//Form is a class in System.Windows.Forms namespace
{
[DllImport("user32.Dll")]
public static extern int EnumWindows(IECallBack x, int y);
[DllImport("User32.Dll")]
public static extern void GetWindowText(int h, StringBuilder s, int nMaxCount);
[DllImport("User32.Dll")]
public static extern void GetClassName(int h, StringBuilder s, int nMaxCount);
[DllImport("User32.Dll")]
public static extern IntPtr PostMessage(IntPtr hWnd, int msg, int wParam, int lParam);

public static ListBox lbPublic;

private ListBox listBox1; //ListBox is a class in System.Windows.Forms namespace
static IntPtr listBoxHandle;// IntPtr is a class in System namespace
static IntPtr windowHandle;
static StringBuilder sb, sbc;
static int i = 0;
static ArrayList myAl;

public IEInstance() //Constructor
{
InitializeComponent();
}

#region Windows Form Designer generated code
private void InitializeComponent()
{
// this.GetIE = new System.Windows.Forms.Button();
this.listBox1 = new System.Windows.Forms.ListBox();
myAl = new ArrayList();
}
#endregion

public void GetIEButton()
{
listBoxHandle = listBox1.Handle;
EnumWindows(new IECallBack(IEInstance.EnumWindowCallBack), (int)listBoxHandle);

}

private static bool EnumWindowCallBack(int hwnd, int lParam)
{
windowHandle = (IntPtr)hwnd;
listBoxHandle = (IntPtr)lParam;
lbPublic = (ListBox)ListBox.FromHandle(listBoxHandle);
sb = new StringBuilder(1024);
sbc = new StringBuilder(256);
GetClassName(hwnd, sbc, sbc.Capacity);
GetWindowText((int)windowHandle, sb, sb.Capacity);
int ind = sb.ToString().IndexOf("- Microsoft");
if (ind ==-1)
ind = sb.ToString().IndexOf("- Windows");
String xMsg2 = string.Empty;
if (ind != -1)
xMsg2 = sb.ToString().Substring(0,ind - 0) + " " + windowHandle;
else
xMsg2 = sb + " " + windowHandle;
String xMsg = windowHandle.ToString();
if (sbc.Length > 0)
{
if (sbc.ToString().Equals("IEFrame"))
{
myAl.Add(windowHandle);
i++;
if (lbPublic.Items.Count < 1)
lbPublic.Items.Add(xMsg2);
}
}
return true;
}

}
}

------
I have inherited this class with form class .In "lbPublic" .This is a ListBox type . After executing this class your lbPublic will fill the active tab title + handler of the tab. You will just add this whole code in your class file ,no need to extent more.

Now , for the sample Add a form in which you will call the above class method GetIEButton();
In a form include a COM component reference MICROSOFT HTML OBJECT LIBRARY.

After adding reference include Add the below line the using area
using mshtml;

Now in the form Load method

private void Form1_Load(object sender, EventArgs e)
{

//// Below is the IEInstance class object
IEInstance ieButton = new IEInstance();
ieButton.GetIEButton();

SHDocVw.ShellWindows shellWindows = new SHDocVw.ShellWindowsClass();
string filename = string.Empty; ;

foreach (SHDocVw.InternetExplorer ie in shellWindows)
{
filename = Path.GetFileNameWithoutExtension(ie.FullName).ToLower();
mshtml.IHTMLDocument2 htmlDoc = ie.Document as mshtml.IHTMLDocument2;
if (htmlDoc != null)
{
string strIe = htmlDoc.title + " " + ie.HWND.ToString();
//htmlDoc = (HTMLDocument)axWebBrowser1.Document;
if (filename.Equals("iexplore"))
{

if (IEInstance.lbPublic.Items[0].ToString() == strIe)
{
filename = ie.LocationURL;

// break;
goto NewLine;
}

}
}

//Console.WriteLine( "Web Site : {0}", ie.LocationURL );

if (filename.Equals("explorer""))
Console.WriteLine("Hard Drive : {0}", ie.LocationURL);
}

NewLine:
int i2 = 0;
MessageBox.Show(filename);

}

This is in working state .now you will get thr url of active tab of active Internet Explorer .

START WITH and CONNECT BY in Oracle SQL

2011-07-13T00:40:00.000-07:00

One way to store hierarchical data is to create a self referencing table. It is called self referencing because the foreign key (column parent_id) references the same table as the table in which the foreign key is:

create table tblActivity (

id number primary key,

parent_id references tblActivity, -- references itself

descr varchar2(20),

);

This table is now filled with some values:

-- 1st level:

insert into tblActivity values ( 1, null, 'Yahoo' , 1);

-- 2nd level:

insert into tblActivity values ( 2, 1, 'Entertainment' , 1);

insert into tblActivity values ( 3, 1, 'Science' , 1);

insert into tblActivity values ( 4, 1, 'Social Science', 1);

-- 3rd level (below Entertainment)

insert into tblActivity values ( 5, 2, 'Awards' , 1);

insert into tblActivity values ( 6, 2, 'Comedy' , 1);

insert into tblActivity values ( 7, 2, 'Humor' , 0);

insert into tblActivity values ( 8, 2, 'Magic' , 1);

-- 4th level (below Humor)

insert into tblActivity values (19, 7, 'Jokes' , 1);

insert into tblActivity values (20, 7, 'Advice' , 1);

insert into tblActivity values (21, 7, 'Parody' , 1);

-- 4th level (below Magic)

insert into tblActivity values (16, 8, 'Card Tricks' , 1);

insert into tblActivity values (17, 8, 'Magazines' , 1);

insert into tblActivity values (18, 8, 'Organizations' , 1);

-- 3rd level (below Science)

insert into tblActivity values ( 9, 3, 'Astronomy' , 1);

insert into tblActivity values (10, 3, 'Biology' , 1);

insert into tblActivity values (11, 3, 'Geography' , 1);

insert into tblActivity values (12, 3, 'Physics' , 1);

insert into tblActivity values (13, 3, 'Research' , 1);

-- 4th level (below Astronomy)

insert into tblActivity values (22, 9, 'Galaxies' , 1);

insert into tblActivity values (23, 9, 'Pictures' , 1);

insert into tblActivity values (24, 9, 'Stars' , 1);

-- 4th level (below Physics)

insert into tblActivity values (25, 12, 'Electricity' , 1);

insert into tblActivity values (26, 12, 'Mechanics' , 1);

-- 3rd level (below Social Science)

insert into tblActivity values (14, 4, 'Psychology' , 1);

insert into tblActivity values (15, 4, 'Sociology' , 1);

-- 4th level (below Psychology)

insert into tblActivity values (27, 14, 'Conferences' , 1);

insert into tblActivity values (28, 14, 'Intelligence' , 1);

--Now Select

select * from   tblActivity

start with id = 3
connect by prior id = parent_id;

It return the below mention record

Science
    Astronomy
      Galaxies
      Pictures
      Stars
    Biology
    Geography
    Physics
      Electricity
      Mechanics
    Research

Asp.net Datalist item fetches using javascript‏

2011-07-12T03:15:00.000-07:00

string scr = @"<script>
function Done()
{

var Hdnval;
var HdnControl;
var foo = document.getElementById('dtlImages');
var inps = foo.getElementsByTagName('input');
var inpsmage = foo.getElementsByTagName('img');
for(var i=0;i<inps.length;i++){
if(inps[i].type=='checkbox')
{
if (inps[i].checked== true)
{
HdnControl = inpsmage[i];
Hdnval = inpsmage[i].src;
}
}}
var fName=Hdnval;
var ret= new Array(fName,HdnControl);
//SetPreviewElements(HdnControl,HdnControl)
window.opener.update(ret);
window.close();
}
</script>;";
ClientScript.RegisterClientScriptBlock(this.GetType(), "done", scr);

Dynamic SQL Query with Sql Paging‏

2011-07-12T03:10:00.000-07:00

Create PROC DBO.Article_SelectByCriteria_BySqlPaging

-- Parameters of Article_SelectByCriteria--------------
@P_GUID                     VARCHAR(40)='' , -- OPTIONAL
@P_parentArticleId          INT = 0 , --OPTIONAL
@P_postingURL               nVARCHAR(200) = '' , --OPTIONAL
@P_authorId                 INT = 0, --OPTIONAL
@P_elaphObjectId            INT =0, --OPTIONAL
@P_assignedBy     BIGINT=0, --OPTIONAL
@P_assignedTo               BIGINT= 0 , --OPTIONAL
@P_assignedDate             VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_publishedBy              BIGINT = 0, -- OPTIONAL
@P_publishedDate            VARCHAR(50) = '12:00:00 AM' ,-- OPTIONAL
@P_lastModifiedBY           BIGINT = 0 , -- OPTIONAL
@P_lastModifiedDate        VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_archivedBy              BIGINT = 0, --OPTIONAL
@P_archivedDate            VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_expirationDate          VARCHAR(50) = '12:00:00 AM' , --OPTIONAL
@P_CountryId               TINYINT = 0 , --OPTIONAL
@P_IsSectionPageNewsTicker BIT = 0 , --OPTIONAL
@P_isHomePageNewsTicker    BIT = 0 , --OPTIONAL
@P_isPrinterFriendly       BIT = 0 , --OPTIONAL
@P_isSendToFriend          BIT = 0 , --OPTIONAL
@P_isAddToFavorite         BIT = 0, --OPTIONAL
@P_isWriteToAuthor         BIT = 0 , --OPTIONAL
@P_isSubscribedArticle     BIT = 0 , --OPTIONAL
@P_isDisablePrintCutPaste BIT = 0, --OPTIONAL
@P_IsCopyRight             BIT = 0 , --OPTIONAL
@P_IsArticleRating         BIT = 0 , --OPTIONAL
@P_IsComments              BIT = 0 , --OPTIONAL
@P_articleStatusId         INT = 0, --OPTIONAL
@P_articleTypeId           TINYINT = 0 ,--OPTIONAL
@P_createdBy               BIGINT = 0 , --OPTIONAL

@PageSize int, @PageNumber int

WITH RECOMPILE

AS
SET NOCOUNT ON
DECLARE
@STMT nvarchar(max),    -- SQL statement to execute
@ubound int

Declare @RowStart int
Declare @RowEnd int

if @PageNumber > 0
Begin

SET @PageNumber = @PageNumber -1

SET @RowStart = @PageSize * @PageNumber + 1;
SET @RowEnd = @RowStart + @PageSize - 1 ;
--IF @start < 1 SET @start = 1
--IF @maxct < 1 SET @maxct = 1
-- SET @ubound = @start + @maxct

DECLARE @L_WhereClause VARCHAR(8000)
DECLARE @L_SQL Varchar(8000)
SET @L_WhereClause = ''
SET @L_SQL = ''

If @P_parentArticleId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ParentArticleId =' + CAST(@P_parentArticleId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ParentArticleId =' +   CAST(@P_parentArticleId AS VARCHAR(20))
            END

    IF @P_GUID <> ''
             BEGIN
    IF @L_WhereClause <> ''
                     SET @L_WhereClause = @L_WhereClause + ' AND Article.GUID =' + ''''+ @P_GUID + ''''
                  ELSE
                     SET @L_WhereClause = @L_WhereClause + ' Article.GUID =' + ''''+ @P_GUID + ''''
      END


    IF @P_postingURL <> ''
             BEGIN
    IF @L_WhereClause <> ''
                     SET @L_WhereClause = @L_WhereClause + ' AND Article.PostingURL =' + ''''+ @P_postingURL + ''''
                  ELSE
                     SET @L_WhereClause = @L_WhereClause + ' Article.PostingURL =' + ''''+ @P_postingURL + ''''
      END

    If @P_authorId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.AuthorId =' + CAST(@P_authorId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.AuthorId =' +   CAST(@P_authorId AS VARCHAR(20))
            END
      If @P_elaphObjectId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ElaphObjectId =' + CAST(@P_elaphObjectId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ElaphObjectId =' +   CAST(@P_elaphObjectId AS VARCHAR(20))
            END
If @P_assignedBy <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.AssignedBy =' + CAST(@P_assignedBy AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.AssignedBy =' +   CAST(@P_assignedBy AS VARCHAR(20))
            END
If @P_assignedTo <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.AssignedTo =' + CAST(@P_assignedTo AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.AssignedTo =' +   CAST(@P_assignedTo AS VARCHAR(20))
            END

       If @P_assignedDate <> '12:00:00 AM'

      BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.AssignedDate =' + '''' + @P_assignedDate + ''''
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.AssignedDate =' +   '''' + @P_assignedDate + ''''
            END

        If @P_publishedBy <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.PublishedBy =' + CAST(@P_publishedBy AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.PublishedBy =' +   CAST(@P_publishedBy AS VARCHAR(20))
            END
     -- If @P_publishedDate <> '12:00:00 AM'

--    BEGIN
           --      IF @L_WhereClause <> ''

--      SET @L_WhereClause = @L_WhereClause + ' AND Article.PublishedDate =' + '''' + @P_publishedDate + ''''
               -- ELSE
        --       SET @L_WhereClause = @L_WhereClause + ' Article.PublishedDate =' +   '''' + @P_publishedDate + ''''
          -- END

If @P_lastModifiedBY <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedBY =' + CAST(@P_lastModifiedBY AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.LastModifiedBY =' +   CAST(@P_lastModifiedBY AS VARCHAR(20))
            END
         If @P_lastModifiedDate <> '12:00:00 AM'

      BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.LastModifiedDate =' + '''' + @P_lastModifiedDate + ''''
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.LastModifiedDate =' +   '''' + @P_lastModifiedDate + ''''
            END
      If @P_archivedBy <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedBy =' + CAST(@P_archivedBy AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ArchivedBy =' +   CAST(@P_archivedBy AS VARCHAR(20))
            END

          If @P_archivedDate <> '12:00:00 AM'

      BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ArchivedDate =' + '''' + @P_archivedDate + ''''
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ArchivedDate =' +   '''' + @P_archivedDate + ''''
            END

         If @P_expirationDate <> '12:00:00 AM'

      BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ExpirationDate =' + '''' + @P_expirationDate + ''''
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ExpirationDate =' +   '''' + @P_expirationDate + ''''
            END

        If @P_CountryId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.CountryId =' + CAST(@P_CountryId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.CountryId =' +   CAST(@P_CountryId AS VARCHAR(20))
            END

        If @P_IsSectionPageNewsTicker <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsSectionPageNewsTicker =' + CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsSectionPageNewsTicker =' +   CAST(@P_IsSectionPageNewsTicker AS VARCHAR(20))
            END

        If @P_isHomePageNewsTicker <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsHomePageNewsTicker =' + CAST(@P_isHomePageNewsTicker AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsHomePageNewsTicker =' +   CAST(@P_isHomePageNewsTicker AS VARCHAR(20))
            END

      IF @P_isPrinterFriendly <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.isPrinterFriendly =' + CAST(@P_isPrinterFriendly AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.isPrinterFriendly =' +   CAST(@P_isPrinterFriendly AS VARCHAR(20))
            END

IF @P_isSendToFriend <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.isSendToFriend =' + CAST(@P_isSendToFriend AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.isSendToFriend =' +   CAST(@P_isSendToFriend AS VARCHAR(20))
            END

         IF @P_isAddToFavorite <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsAddToFavorite =' + CAST(@P_isAddToFavorite AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsAddToFavorite =' +   CAST(@P_isAddToFavorite AS VARCHAR(20))
            END
   IF @P_isWriteToAuthor <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsWriteToAuthor =' + CAST(@P_isWriteToAuthor AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsWriteToAuthor =' +   CAST(@P_isWriteToAuthor AS VARCHAR(20))
            END
   IF @P_isSubscribedArticle <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsSubscribedArticle =' + CAST(@P_isSubscribedArticle AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsSubscribedArticle =' +   CAST(@P_isSubscribedArticle AS VARCHAR(20))
            END

IF @P_isDisablePrintCutPaste <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsDisablePrintCutPaste =' + CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsDisablePrintCutPaste =' +   CAST(@P_isDisablePrintCutPaste AS VARCHAR(20))
            END
IF @P_IsCopyRight <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsCopyRight =' + CAST(@P_IsCopyRight AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsCopyRight =' +   CAST(@P_IsCopyRight AS VARCHAR(20))
            END
IF @P_IsArticleRating <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsArticleRating =' + CAST(@P_IsArticleRating AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsArticleRating =' +   CAST(@P_IsArticleRating AS VARCHAR(20))
            END
IF @P_IsComments <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.IsComments =' + CAST(@P_IsComments AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.IsComments =' +   CAST(@P_IsComments AS VARCHAR(20))
            END




          IF @P_articleStatusId <> 0

     BEGIN
                 IF @P_articleStatusId = 1
                    BEGIN
               IF @L_WhereClause <> ''

          SET @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' + CAST(@P_articleStatusId AS VARCHAR(20)) + ' AND Article.PublishedDate <= ' + '''' + CAST(getdate() AS VARCHAR(50)) + '''' + ' AND Article.expirationDate > ' + '''' +
CAST(getdate() AS VARCHAR(50)) + ''''
                    ELSE
                 SET @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' +   CAST(@P_articleStatusId AS VARCHAR(20)) + ' AND Article.PublishedDate <= ' + '''' + CAST(getdate() AS VARCHAR(50)) + '''' + ' AND Article.expirationDate > ' + '''' + CAST(getdate() AS VARCHAR(50)) + ''''

                    END
     ELSE
     BEGIN
    IF @L_WhereClause <> ''

         SET @L_WhereClause = @L_WhereClause + ' AND Article.ArticleStatusId =' + CAST(@P_articleStatusId AS VARCHAR(20))
                  ELSE
                SET @L_WhereClause = @L_WhereClause + ' Article.ArticleStatusId =' +   CAST(@P_articleStatusId AS VARCHAR(20))
             END
    END



IF @P_articleTypeId <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.ArticleTypeId =' + CAST(@P_articleTypeId AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.ArticleTypeId =' +   CAST(@P_articleTypeId AS VARCHAR(20))
            END

      IF @P_createdBy <> 0

     BEGIN
                 IF @L_WhereClause <> ''

        SET @L_WhereClause = @L_WhereClause + ' AND Article.CreatedBy =' + CAST(@P_createdBy AS VARCHAR(20))
                 ELSE
               SET @L_WhereClause = @L_WhereClause + ' Article.CreatedBy =' +   CAST(@P_createdBy AS VARCHAR(20))
            END

      IF @L_WhereClause = ''

        SET @L_WhereClause = 'where row >= ' + CONVERT(varchar(9), @RowStart ) + ' AND
                row <= ' + CONVERT(varchar(9),@RowEnd)


SET @STMT = ' SELECT TOP 500 *
                FROM (
                      SELECT ROW_NUMBER() OVER(ORDER BY PublishedDate Desc) AS row, ArticleId, PublishedDate
                      FROM    Article
                     ) AS tbl
                '+@L_WhereClause+' '

EXEC (@STMT)

end

GO

//// Now You can execute the SP
Article_SelectByCriteria_BySqlPaging
@PageSize = 15,
@PageNumber = 11

javascript date difference‏

2011-07-12T02:56:00.000-07:00

Oracle Trigger

2011-07-12T02:14:00.000-07:00

// Below trigger insert record in bps_company_h when any insert or update operation perform in //bps_company

CREATE OR REPLACE TRIGGER trg_insupd_bps_company
   AFTER INSERT OR UPDATE
   ON bps_company
   REFERENCING NEW AS NEW OLD AS OLD
   FOR EACH ROW
DECLARE
   v_err_msg   VARCHAR2 (1024);
BEGIN
   INSERT INTO bps_company_h
               (company_id, company_code, company_name,
                is_enabled, consumer_no_min_length,
                consumer_no_max_length, min_bill_amount,
                max_bill_amount, bill_type_id,
                consumer_no_dt_id, grace_time,
                allowed_days_after_due_date, provider1_id,
                provider2_id, provider3_id, barcode_length,
                barcode_format, created_by, created_date,
                updated_by, updated_date, allow_bulk_billing,
                is_reversal_applicable, surcharge_type_id,
                surcharge_value, surcharge_min_value,
                surcharge_max_value, bill_marking_required,
                sms_recipient, email_recipient, long_name,
                sub_category_id, display_fields,IS_REMAINING_AMOUNT_AVAILABLE
               )
        VALUES (:NEW.company_id, :NEW.company_code, :NEW.company_name,
                :NEW.is_enabled, :NEW.consumer_no_min_length,
                :NEW.consumer_no_max_length, :NEW.min_bill_amount,
                :NEW.max_bill_amount, :NEW.bill_type_id,
                :NEW.consumer_no_dt_id, :NEW.grace_time,
                :NEW.allowed_days_after_due_date, :NEW.provider1_id,
                :NEW.provider2_id, :NEW.provider3_id, :NEW.barcode_length,
                :NEW.barcode_format, :NEW.created_by, :NEW.created_date,
                :NEW.updated_by, :NEW.updated_date, :NEW.allow_bulk_billing,
                :NEW.is_reversal_applicable, :NEW.surcharge_type_id,
                :NEW.surcharge_value, :NEW.surcharge_min_value,
                :NEW.surcharge_max_value, :NEW.bill_marking_required,
                :NEW.sms_recipient, :NEW.email_recipient, :NEW.long_name,
                :NEW.sub_category_id, :NEW.display_fields,:NEW.IS_REMAINING_AMOUNT_AVAILABLE
               );
EXCEPTION
   WHEN OTHERS
   THEN
      v_err_msg := SQLERRM;
END;

Insert Groups,Assign Role and add folder in document libarary‏

2011-07-12T02:10:00.000-07:00

private void InsertGroups(string CompanyName, string GroupDes)
        {
            string DeptName = string.Empty;
            for (int i = 0; i < 6; i++)
            {
                using (SPSite site = new SPSite("http://lbs-sharepoint:1984/"))
                {
                    using (SPWeb web = site.OpenWeb())
                    {

                        SPUserCollection users = web.AllUsers;
                        SPUser owner = users[string.Format("{0}\\{1}", "cybernet", "laksolcom_adeel")];
                        //SPUser member = users[string.Format("{0}\\{1}", "cybernet", "laksolcom_adeel")];
                        SPGroupCollection groups = web.SiteGroups;

                        string GroupName = "";
                        if (i == 0)
                        {
                            DeptName = "Sales";
                            GroupName = CompanyName.Trim() + "-Sales-reader";
                        }
                        if (i == 1)
                        {
                            DeptName = "Marketing";
                            GroupName = CompanyName.Trim() + "-Marketing-reader";
                        }
                        if (i == 2)
                        {
                            DeptName = "Product";
                            GroupName = CompanyName.Trim() + "-Product-reader";
                        }
                        if (i == 3)
                        {
                            DeptName = "Supply Chain";
                            GroupName = CompanyName.Trim() + "-Supply Chain-reader";
                        }
                        if (i == 4)
                        {
                            DeptName = "Technical";
                            GroupName = CompanyName.Trim() + "-Technical-reader";
                        }
                        if (i == 5)
                        {
                            DeptName = "Fianance";
                            GroupName = CompanyName.Trim() + "-Fianance-reader";
                        }
                        string GroupDescription = GroupDes;//"Company Name group description.";

                        groups.Add(GroupName, owner, null, GroupDescription);
                        SPGroup NewSPGroup = groups[GroupName];

                        // here’s where you define your role type:
                        SPRoleDefinition role = web.RoleDefinitions["Read"];
                        SPRoleAssignment roleAssignment = new SPRoleAssignment(NewSPGroup);
                        roleAssignment.RoleDefinitionBindings.Add(role);
                        web.RoleAssignments.Add(roleAssignment);
                        web.Update();

                        SPDocumentLibrary _MyDocLibrary = (SPDocumentLibrary)web.Lists["LBS Document"];
                        SPFolderCollection _MyFolders = web.Folders;
                        string folderName = txtTo.Text + " " + DeptName;
                        _MyFolders.Add("http://lbs-sharepoint:1984/LBS%20Document/" + folderName + "/");
                        _MyDocLibrary.Update();
                        SPFolder folder = web.GetFolder("http://lbs-sharepoint:1984/LBS%20Document/" + folderName + "/");

                        SPGroupCollection spc = web.SiteGroups;

                        SPGroup spGroup = web.SiteGroups[GroupName];
                        SPRoleDefinition spRole = web.RoleDefinitions["Read"];
                        SPRoleAssignment roleAssignment1 = new SPRoleAssignment(spGroup);
                        roleAssignment1.RoleDefinitionBindings.Add(spRole);
                        folder.Item.BreakRoleInheritance(true);
                        folder.Item.RoleAssignments.Add(roleAssignment1);
                        folder.Update();

                    }

                }
            }
        }

Dropdownlist selected value or text from jquery

2011-07-12T01:58:00.000-07:00

fetches selected dropdownlist text
var ddlName = $("#ddlName option:selected").text();

fetches selected dropdownlist value
var ddlName = $("#ddlName option:selected").value();

Save Data from Dataset into Excel File‏

2011-07-12T01:53:00.000-07:00

Response.ContentType = "application/vnd.ms-excel";
//remove the charset from the content-type header. Response.Charset = "";
System.Text.StringBuilder strOutput = new System.Text.StringBuilder();
strOutput.Append("<?xml version=\"1.0\"?><?mso-application progid=\"Excel.Sheet\"?>");
strOutput.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
strOutput.Append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
strOutput.Append("</ExcelWorkbook>");
strOutput.Append("<Styles>");
strOutput.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
strOutput.Append("<Alignment ss:Vertical=\"Bottom\" />");
strOutput.Append("<Borders/>");
strOutput.Append("<Font/>");
strOutput.Append("<Interior/>");
strOutput.Append("<NumberFormat/>");
strOutput.Append("<Protection/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"m52377878\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"m52377888\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"m52388202\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"m52388212\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s25\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s26\">");
strOutput.Append("<Borders/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s27\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s34\">");
strOutput.Append("<Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Bottom\" ss:WrapText=\"1\"/>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Size=\"12\" ss:Bold=\"1\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s41\">");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s42\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders/>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s43\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#969696\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s47\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s48\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s49\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s50\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("<Style ss:ID=\"s51\">");
strOutput.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\"/>");
strOutput.Append("<Borders>");
strOutput.Append("<Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("<Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"2\"/>");
strOutput.Append("</Borders>");
strOutput.Append("<Font x:Family=\"Swiss\" ss:Bold=\"1\"/>");
strOutput.Append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/>");
strOutput.Append("</Style>");
strOutput.Append("</Styles>");
strOutput.Append("<Worksheet ss:Name=\"Standard Status Report\">");
strOutput.Append("<Table ss:ExpandedColumnCount=\"6\" x:FullColumns=\"1\" x:FullRows=\"1\">");
strOutput.Append("<Column ss:Width=\"108.75\"/>");
strOutput.Append("<Column ss:Width=\"339.75\"/>");
strOutput.Append("<Column ss:Width=\"54\"/>");
strOutput.Append("<Column ss:Width=\"131.25\"/>");
strOutput.Append("<Column ss:Width=\"76.5\"/>");
strOutput.Append("<Column ss:Width=\"71.25\"/>");
//report heading strOutput.Append("<Row ss:Height=\"15.75\">");
strOutput.Append("<Cell ss:MergeAcross=\"5\" ss:StyleID=\"s34\"><Data ss:Type=\"String\">Standard Status Report</Data></Cell>");
strOutput.Append("</Row>");
strOutput.Append("<Row ss:Height=\"13.5\">");
strOutput.Append("<Cell ss:MergeAccross=\"5\"><Data ss:Type=\"String\"></Data></Cell>");
strOutput.Append("</Row>");
foreach(DataRow drStandard in dsStandard.Tables["Standard"].Rows)
{
bool bAlternateRow = false;
//standard name strOutput.Append("<Row>");
//BOC - marefin - 20050929 - encode special chars in xml strOutput.Append("<Cell ss:MergeAcross=\"2\" ss:StyleID=\"s51\"><Data ss:Type=\"String\">Standard Name: " + Server.HtmlEncode(drStandard["StandardName"].ToString()) + (drStandard["assigned"].ToString() == "0" ? "" : "~") + "</Data></Cell>");
//EOC - marefin - 20050929 - encode special chars in xml strOutput.Append("<Cell ss:MergeAcross=\"2\" ss:StyleID=\"m52388202\"><Data ss:Type=\"String\">Standard Status: " + (drStandard["Active"].ToString() == "1" ? "Active" : "Needs Review") + "</Data></Cell>");
strOutput.Append("</Row>");
//column headers strOutput.Append("<Row>");
strOutput.Append("<Cell ss:StyleID=\"s41\"><Data ss:Type=\"String\">Standard Line Category</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s42\"><Data ss:Type=\"String\">Components</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s42\"><Data ss:Type=\"String\">Source</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s42\"><Data ss:Type=\"String\">Manufacturer</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s42\"><Data ss:Type=\"String\">Mfg. Part #</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s43\"><Data ss:Type=\"String\">Product Status</Data></Cell>");
strOutput.Append("</Row>");
foreach(DataRow drLineCategory in dsStandard.Tables["StandardLineCategory"].Select("StandardId = " + drStandard["StandardId"]))
{
bool bDisplayLineCategoryName = true;
//strOutput.Append("<Row>"); //strOutput.Append("<Cell><Data ss:Type=\"String\">" + drLineCategory["standardlinecategoryname"] + "</Data></Cell>"); //strOutput.Append("</Row>"); //int nLength = dsStandard.Tables["StandardLineCategoryComponents"].Select("StandardLineCategoryId = " + drLineCategory["StandardLineCategoryId"]).Length; foreach(DataRow drLineComponent in dsStandard.Tables["StandardLineCategoryComponents"].Select("StandardLineCategoryId = " + drLineCategory["StandardLineCategoryId"]))
{
//strOutput.Append("<Row" + (bAlternateRow ? " ss:StyleID=\"s50\"" : "")+ ">"); strOutput.Append("<Row>");
//BOC - marefin - 20050929 - encode special chars in xml strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "47" : "25")+ "\"><Data ss:Type=\"String\">" + (bDisplayLineCategoryName ? Server.HtmlEncode(drLineCategory["standardlinecategoryname"].ToString()) : "") + "</Data></Cell>");
//EOC - marefin - 20050929 - encode special chars in xml bDisplayLineCategoryName = false;
//BOC - marefin - 20050929 - encode special chars in xml strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "48" : "26")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["descriptiontext"].ToString()) + "</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "48" : "26")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["catalogname"].ToString()) + "</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "48" : "26")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["mfgname"].ToString()) + "</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "48" : "26")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["mfgpartnumber"].ToString()) + "</Data></Cell>");
strOutput.Append("<Cell ss:StyleID=\"s" + (bAlternateRow ? "49" : "27")+ "\"><Data ss:Type=\"String\">" + Server.HtmlEncode(drLineComponent["statusdesc"].ToString()) + "</Data></Cell>");
//EOC - marefin - 20050929 - encode special chars in xml strOutput.Append("</Row>");
bAlternateRow = !bAlternateRow;
}
}
strOutput.Append("<Row ss:Height=\"13.5\">");
strOutput.Append("<Cell ss:MergeAcross=\"5\" ss:StyleID=\"m52388212\"><Data ss:Type=\"String\"></Data></Cell>");
strOutput.Append("</Row>");
strOutput.Append("<Row ss:Height=\"13.5\">");
strOutput.Append("<Cell ss:MergeAccross=\"5\"><Data ss:Type=\"String\"></Data></Cell>");
strOutput.Append("</Row>");
}

strOutput.Append("</Table>");
strOutput.Append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"><Selected/><Panes><Pane><Number>1</Number><ActiveRow>1</ActiveRow><ActiveCol>1</ActiveCol></Pane></Panes><ProtectObjects>False</ProtectObjects><ProtectScenarios>False</ProtectScenarios></WorksheetOptions></Worksheet></Workbook>");
//write the HTML back to the browser. Response.Write(strOutput);
//end the response. Response.End();

2) TO save your STring that contain Excel string into Excel file ,the code is given below,above code open the excel file into browser

string path;
path=System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "Adeel.xls";
System.Xm.XmDocument doc =new System.Xml.XmlDocument();
System.IO.FileInfo fileName=new System.IO.FileInfo(path);
try {
if (!fileName.Exists)
{
System.IO.File.Delete(path);
System.IO.StreamWriter sw = new System.IO.StreamWriter(path, true);
sw.WriteLine(strOutput.ToString());
sw.Flush();
sw.Close();
}

}
catch { }

Check/uncheck GridView Header checkbox using Javascript Code

2011-07-12T01:28:00.000-07:00

<script type="text/javascript">
     function SelectAll(id)
        {
          var grid =

document.getElementById("<%= GridView1.ClientID %>");
            variable to contain the cell of the grid
            var cell;
            
            if (grid.rows.length > 0)
            {
            
             for (i=1; i<grid.rows.length; i++)
              {
              cell = grid.rows[i].cells[0];
              for (j=0; j<cell.childNodes.length; j++)
               {           
                if (cell.childNodes[j].type =="checkbox")
                 {
                  cell.childNodes[j].checked =

document.getElementById(id).checked;
                 }
                }
              }
            }
        }
    </script>

Server side binding for checkbox is as follow:
 
    protected void GridView1_RowDataBound(object sender,

GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        { 
         ((CheckBox)e.Row.FindControl("cbSelectAll")).

Attributes.Add("onclick", "javascript:SelectAll('" + 
          ((CheckBox)e.Row.FindControl("cbSelectAll")).ClientID + "')");
        }
    }