Search This Blog

Wednesday, June 30, 2010

Efficiently Delete / Purge All Items from a SharePoint List

private static
StringBuilder BuildBatchDeleteCommand(SPList spList)

StringBuilder sbDelete = new StringBuilder();

sbDelete.Append("<?xml version=\"1.0\" encoding=\"UTF-8\"?><Batch>");

string command = "<Method><SetList Scope=\"Request\">" + spList.ID +

"</SetList><SetVar Name=\"ID\">{0}</SetVar><SetVar Name=\"Cmd\">Delete</SetVar></Method>";


        foreach (SPListItem item in spList.Items)


           sbDelete.Append(string.Format(command, item.ID.ToString()));



       return sbDelete;


Paging through SharePoint list items with SPQuery

SPQuery query = new SPQuery();
query.RowLimit = 10; // that is our page size
SPListItemCollection items = SPContext.Current.List.GetItems(query);
// do something with the page result
// set the position cursor for the next iteration
query.ListItemCollectionPosition = items.ListItemCollectionPosition;
} while (query.ListItemCollectionPosition != null)

Tuesday, June 29, 2010

creating trigger in sql server 2005

creating trigger in sql server 2005 for an insert into a table after an insert to another table

Hope this will help you.......


after insert


set nocount on

DECLARE @empid=SELECT empid FROM inserted

insert into secondtableName(field1,field2,field3)
values (@empid, 'Not Set', 'Not Set')


Adding Validation to a Custom List Control in Sharepoint

Adding Validation to a Custom List Control in Sharepoint


Recently, one of the requirements was to put validation to a custom list control such as Email field validation.This validation can be achieved from inside the Sharepoint designer. Following is a step by step walkthrough to validate a list field.


You should know how to create a Sharepoint list and how to open the site in Sharepoint designer.

1. Following is a list with Email fields. Let's say we have to validate so that only a valid email can be entered.

If not valid, then the Error Message appears next to the field:

2. Now open the site in the Sharepoint designer and then expand the list and open the Newform.aspx of the list to be validated:

3. As you can see in the image above, the Newform.aspx of Validation list is being opened in the Sharepoint designer.

4. Now right click on this Validation list Web part in Sharepoint designer and choose Web Part properties:

5. The properties dialog box will open for the Validation list here. Choose Hidden under layout and say OK.

(This web part can be deleted as well, but due to some known problems because of deletion, it is best to hide this).

6. Now click exactly underneath the hidden list web part and go to Insert->Sharepoint Controls-> Custom List Form… and the Wizard will ask to choose a List or Document Library form based on existing list.

Choose your Validation list and select New item form under type of form to create option and hit Ok.

7. You will get the Validation List underneath the hidden list as shown as selected image below:

8. Now from here, we can customize all the controls based on requirement. As we are customizing the E-Mail field, right click on the email field box and choose Show common control tasks from the context menu:

9. This will give you Common Formfield tasks option as shown below next to the Email field. Here we can change the format of the data field selected (default is List form field). As we need Email field to be textbox where validation can be applied, Choose Textbox from Format as dropdown instead of List form field.

10. After the above step, List will look like this (custom formatted):

11. Now drop a RegularExpressionValidator control from the Validation option under ASP.NET controls from the toolbar as shown below:

12. Now specify the required properties (such as ControlToValidate, ErrorMessage, SetFocusOnError to true, Validate Expression) of the RegularExpressionValidator from the Tag Properties toolbar. For ControlToValidate, choose the ID of the Email field.

After selecting it and getting the ID from the Tag Properties toolbar as you do in Visual Studio, see below the set attributes in blue for the Email field:

13. Now, save the changes and go back to the list in Internet Explorer and try to type in an invalid email id and you will get the following:

Happy Sharepointing…

Audience Targeting for a web part

Note: Audience Targeting is available only in MOSS not in WSS

Steps to target a web part:

1) Create User Profile

2) Create Audience Group

3) Apply Audience Targeting to the web part

Steps to create User Profile:

1) Open Central Administration Page of SharePoint

2) Click on SharedService1 (default name. otherwise go to MOSS Administration Page)

3) Click on User Profile and Properties

4) Click Add User Profile

5) Add the details you want

6) Click Save and Close

Steps to create Audience Group:

1) Open MOSS Administration Page

2) Click Audience

3) Give the details like name, description etc.

4) Add rules for that Audience group

5) Click OK

6) Click Compile Audience

Steps to apply Audience to a web part:

1) Open your SharePoint site where you want to target web part

2) Click on the arrow at the right side of the web part

3) Click Modify Shared Web part

4) Now a panel will be opened. Expand Advanced tab (last one)

5) At the bottom, you can see Target Audience

6) Give the name of Audience group you have created

7) Click Apply

8) Click Ok

9) Exit Edit Mode

Now login as a member of audience group then as a non-member ......... U can see the magic :)

Custom Webpart in SharePoint

Custom Webpart in SharePoint
I am going to explain step by step process:

1. First of all we need to create a webpart in visual studio (2005, 2008). For that, Go to File>> New>>Project.

2. Under Templates section select WebPart (this option is available only in 2008). For VS 2005, select Class Library and add reference for System.Web.

3. Write code WebPart you want to create. Here, code is written for a label having text “Hello World”.

using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
namespace CustomWebPart
public class WebPart1 : System.Web.UI.WebControls.WebParts.WebPart
public WebPart1()
protected override void CreateChildControls()
Label label = new Label();
label.Text = "Hello World";

4. Now, Buid Webpart and generate an assembly for this webpart. For that GoTo>>Build>>Build Solution. And the GoTo>>Build>>CustomWebPart.

The WebPart is now Build, we need to deploy this assembly in sharepoint site. For deploying webpart we need to perform following steps.

5. Put the assembly in the bin folder of sharepoint site. The MOSS 2007 creates every portal in the inetpub\wwwroot\wss folder. To find the path of bin folder of the portal for which you want to deploy the webpart, identified with port number, run inetmgr(IIS).

Right Click the portal and click on Properties

6. Under HomeDirectory tab, Local Path describes the whole path, copy the path and verify it by opening it in a browser and see if the bin folder exists, if it does not exist then create a new folder and rename it bin.

7. Now copy the assembly from project output folder.
C:\Documents and Settings\Administrator\My Documents\Visual Studio 2008\Projects\CustomWebPart\CustomWebPart\bin\Debug.
Paste it in the portal bin folder. C:\Inetpub\wwwroot\wss\VirtualDirectories\80\bin

Now everytime we change the webpart we need to copy and paste the assembly in portal bin folder. Hence to avoid this stuff we can do the following step which will automatically replace the new assembly in the portal bin folder.

8. In VS .Net, Solution Explorer, Right Click the project name(CustomWebPart) Click on Properties, click on Build. Into the Output Path paste the path which we copied from inetmgr console.

9. Now to make the webpart usable we need to modify the web.config file of the portal. To declare the control safe we need to add a entry in web.config file.

10. For tag we require a parameter “PublicTokenKey” to generate this key drag the assembly from project folder and drop it in C:/Windows/assembly folder and it will generate a publictokenkey.

Right Click on CustomWebPart and copy Public Key Token.
Open web.config file and enter the following text :


Namespace="CustomWebPart" TypeName="*" Safe="True"
AllowRemoteDesigner="True" />

11. Now we need to Configure Portal to use CustomWebPart.
Till now the web part has been created and deployed to the site directory. Now the next part is how to use the web part on the Portal’s Site. The web part created can be placed on any site of the portal.
Open the portal site in the internet explorer, in my case its http://win2003, ensure that the logged in user has the administrative rights on the portal site, else it will not allow adding the web part.

12. Click on the Site Action>>Site Settings

13. On the site settings page under Galleries column click on the Web Parts.

14. On the Web Part Gallery Page click on the New button, to add the new web part assembly to the gallery.

15. On the New Web Parts page locate the CustomWebPart in the list, check the check box on the left and click on the Populate Gallery button the top of the page. This will result in the Web Part entry creation in the Web Part Gallery list, and hence it can be used from now on from the gallery.

Now the Web Part is ready to be added to the page.

16. Open a site created in the Portal. Click on Site Action>>Edit Page

17. This will modify the appearance of the page to enable the edit view. In this view Web Part Zones are highlighted so that a user can add a web part to the zone, Click on the Add a Web Part button in the left zone to add the Web Part.

18. Select the CustomWebPart from the web part list . It is found under the Miscellaneous section and then click on Add.

19. Click on the Exit Edit Mode link on the page and the site will return to the view mode.
20. At the end the page will appear like this.

Programmatically(C#) Adding, Deleting, Copying and Downloading Attachments in SPList


private void AddNewAttachment(int NodeID)
SPList myList = SPContext.Current.Web.Lists["Item List"];
SPListItem myNewItem = myList.GetItemById(NodeID);

if (fileUpload.PostedFile != null && fileUpload.HasFile)
Stream fStream = fileUpload.PostedFile.InputStream;

byte[] contents = new byte[fStream.Length];
fStream.Read(contents, 0, (int)fStream.Length);

SPAttachmentCollection attachments = myNewItem.Attachments;
string fileName = Path.GetFileName(fileUpload.PostedFile.FileName);
attachments.Add(fileName, contents);

myNewItem["Attached FileName"] = fileName; // store the name of the file in a column for future requirements
catch (Exception eAdd)
string errAdd = eAdd.Message;


private void DeleteAttachment(int NodeID)
SPList myList = SPContext.Current.Web.Lists["Item List"];
SPListItem delItem = myList.GetItemById(NodeID);
SPAttachmentCollection atCol = delItem.Attachments;
if (delItem["Attached FileName"] != null)
string strFileName = delItem["Attached FileName"].ToString();
delItem["Attached FileName"] = string.Empty;
catch (Exception eDel)
string errDel = eDel.Message;


Find the download link first then reedirect to another aspx page so that the response ending on the current page does not affect the functionalities on this :-

private void DownloadAttachment(int NodeID)
string AttachmentURL = string.Empty;
SPList myList = SPContext.Current.Web.Lists["Item List"];
SPListItem attItem = myList.GetItemById(NodeID);
if (attItem["Attached FileName"] != null)
AttachmentURL = “/Lists/Item%20List1/Attachments/” + NodeID.ToString() + “/” + attItem["Attached FileName"].ToString();

System.Web.HttpContext.Current.Session["FileName"] = attItem["Attached FileName"].ToString();
System.Web.HttpContext.Current.Session["Attachment"] = AttachmentURL.Trim();
lblReport.Text = GetMessage(110);
if (AttachmentURL != string.Empty)
catch (Exception eDwn)
string errDwn = eDwn.Message;
At the aspx page you need to run the code as :

if(System.Web.HttpContext.Current.Session["Attachment"] != null)
string strName = System.Web.HttpContext.Current.Session["FileName"].ToString();
string sbURL = System.Web.HttpContext.Current.Session["Attachment"].ToString();
System.Web.HttpResponse response;
response = System.Web.HttpContext.Current.Response;
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
response.AppendHeader(“Content-disposition”, “attachment; filename=” + strName);
response.AppendHeader(“Pragma”, “cache”);
response.AppendHeader(“Cache-control”, “private”);

Now set these sessions to null.


private void CopyAttachment(int FromID, int NodeID, string AttachedFile)
SPList myList = SPContext.Current.Web.ParentWeb.Lists["Item List"];
SPListItem myItem = myList.GetItemById(NodeID);
SPListItem myPrevItem = myList.GetItemById(FromID);

SPAttachmentCollection attColl = myPrevItem.Attachments;

SPFile attFile = myPrevItem.ParentList.ParentWeb.GetFile(myPrevItem.Attachments.UrlPrefix + AttachedFile);
string fileRead = myPrevItem.Attachments.UrlPrefix.ToString() + AttachedFile;

StreamReader fsReader = new StreamReader(attFile.OpenBinaryStream());
Stream fStream = fsReader.BaseStream;

byte[] contents = new byte[fStream.Length];
fStream.Read(contents, 0, (int)fStream.Length);

myItem.Attachments.Add(AttachedFile, contents);
catch (Exception eCopy)
string errCopy = eCopy.Message;


Already Exist from SPList item

When you are getting a record through CAML query in search, you can further add some logic on resultset to check case sensitive data. for example in this case your CAML has returned you one item so you are checking that if there is an item returned it means it already exist. so you can do it further like this

//assuming yourCamlReturnedItems is object having returned list item and your manage name to search is in a string //variable sManagerNameToCheck

int iCount=0;
foreach(SPListItem oItem in yourCamlReturnedItems)
//case Sensitive match here in code.

//Manager Name already exist.

this will not be slow as you will always have only very few items returned based on name. so just for case sensitivty you can write above line of code further to get the required results after CAML.

ASP.NET 2.0 VS Sharepoint 2007

Integration Level 0 (No Integration)

1) Simplest solution as there is no need to integrate with SharePoint 2007 or deployNOTE: This is based on requirements outlined by Pfizer (Document versioning is not required)
2) More reliable as there is no reliance on SharePoint 2007 services
3) Simpler to customise look and feel as no customisation of SharePoint is required.
4) Pages render faster in native ASP.NET rather than being hosted in SharePoint 2007.
5) Server requirements are reduced as SharePoint services are not running/not required.
6) Better control of caching than when hosted in SharePoint 2007
7) Simpler development and deployment model and debugging (no .webpart & GAC deployment required, don’t need SharePoint on development machines)

1) (We are not planning on using ASP.NET 2.0 Web Parts) - No user customisation possible
2) Limited integration with existing SharePoint 2007 Portals
3) Outlook Task creation is more difficult

Integration Level 1 (Using ASP.NET 2.0 hosted inside SharePoint 2007 without any changes to make SharePoint 2007 aware)
It is possible to simply add aspx pages developed outside SharePoint to a Sharepoint 2007 site – and pointing to the Sharepoint 2007 master page.

1) Hosted inside SharePoint, but not integrated – so deployment model is the same as for other SharePoint 2007 sites currently in use.
2) Simpler development Model compared to higher levels of integration
3) SharePoint is aware of the page – so the link is natively visible to portals (ie the link doesn’t need to be added to SharePoint)
1) Dependency upon SharePoint 2007
Integration Level 2 (Using ASP.NET 2.0 and Web Parts hosted inside SharePoint 2007)
1) Integration of Web Parts is more complex than a standard ASP.NET 2.0 page.
2) Pages render slower in SharePoint 2007.
3) There is a reliance on more components – so reliability of system is lower.
4) Reduced control over Caching.
5) Creating the desired look and feel is more complicated if we are using Master pages in SharePoint 2007.
6) More powerful servers are required for SharePoint 2007.

1) Can leverage current look and feel of SharePoint 2007 sites if they already exist
2) Direct Access to SharePoint 2007 versioning control if we did need it (we don’t)
3) Outlook task integration is simpler to perform.
Integration Level 3 (Deep Integration with SharePoint 2007 using Lists)
1) All advantages of Level 2 integration PLUS
2) Lists are easily customised by users
3) Notifications when data Changes

1. All limitations of Level 2 integration PLUS
2. Problems with the performance and scalability limits of lists

Monday, June 28, 2010

Create custom SharePoint web part using Web User Control

Creating the Web Part
First Create New Project called "ProjectView" and select ASP.NET Web Application (Figure 1 & 2). Don’t use shortcuts.

Then Add new Web User Control called "ProjectViewControl" to your project (Figure 3 & 4).

Then Add reference to the “Microsoft.SharePoint.dll”. It can be found in your sharepoint server’s ISAPI folder (Figure 5 & 6).

You should copy this file along with “Microsoft.SharePoint.Search.dll” and “Microsoft.SharePoint.Search.xml” from the same directory to a directory on your local computer if you are developing your project on a machine not having SharePoint or MOSS.

Then go to your SharePoint site’s folder (You can find it in SharePoint server) and create a folder called “usercontroles” if does not exist (Figure 7).

Find your site’s “Web.config” file within the same folder and open it (Figure 7).

Then add new Class called “WebControl.cs” which inherits from “WebPart” Class to your project (Figure 8) and paste this code there.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebPartPages;

namespace ProjectView
public class WebControl : WebPart
protected string UserControlPath = @"~/usercontrols/";
protected string UserControlName = @"ProjectViewControl.ascx";

private ProjectViewControl mycontrol;
private string exceptions = "";

protected override void CreateChildControls()
mycontrol = (ProjectViewControl)this.Page.LoadControl(UserControlPath + UserControlName);
catch (Exception CreateChildControls_Exception)
exceptions += "CreateChildControls_Exception: " + CreateChildControls_Exception.Message;
protected override void RenderContents(HtmlTextWriter writer)
catch (Exception RenderContents_Exception)
exceptions += "RenderContents_Exception: " + RenderContents_Exception.Message;
if (exceptions.Length > 0)
There don’t forget to add following two lines to the top, now your “WebControl.cs” Class should like above.

using Microsoft.SharePoint;
using Microsoft.SharePoint.WebPartPages;

Then drag and drop a text box and two buttons to ProjectViewControl.ascx. Double click those buttons to add custom code (Figure 9 & 10).

Signing your web part project
To deploy this Web Part and Web User Control to GAC and the Microsoft Office SharePoint Server, we should assign a Strong Name key and sign the control.

To do this, right click the “ProjectView” node in Solution Explorer and select Properties. Then select the Signing tab from the choices on the left. Check the "Sign the assembly" box and select from the "Choose a strong name key file" drop down list (Figure 11).

There give a key file name and click ok. Now you can build your project and ready to deploy.
Deploying the Web Part and Web User Control
Here is deploying steps;

1. Copy the "ProjectViewControl.ascx" file to the created usercontroles folder (Figure 7).

2. Drag and drop the compiled DLL (You can find it in your project folder's bin folder) into the Global Assembly Cache. The Global Assembly Cache is a special folder located at %WINDIR%\assembly where %WINDIR% is the full path to your Windows folder (e.g. C:\Windows or C:\Winnt).

3. Get the publicKeyToken property of our assembly. You can find it by right click on the file and select properties in "assembly" folder (Figure 12).

4. Update the "Web.config file"

4.1 Insert these three lines between SafeControls tags.
1<SafeControl Assembly="ProjectView, Version=, Culture=neutral, PublicKeyToken=9a73871474819663" Namespace="ProjectView" TypeName="WebControl" Safe="True" />
2<SafeControl Assembly="ProjectView, Version=, Culture=neutral, PublicKeyToken=9a73871474819663" Namespace="ProjectView" TypeName="ProjectViewControl" Safe="True" />
3<SafeControl Assembly="ProjectView, Version=, Culture=neutral, PublicKeyToken=9a73871474819663" Namespace="ProjectView" TypeName="*" Safe="True" />

4.2 Insert this line between assemblies tags.
1<add assembly="ProjectView, Version=, Culture=neutral,PublicKeyToken=9a73871474819663" />

5. Insert new XML file called "myprojectview.webpart" (Figure 13) and paste this code.

01<?xml version=
"1.0" encoding="utf-8" ?>
03    <webPart xmlns="">
04        <metaData>
05            <type name="ProjectView.WebControl, ProjectView, Version=,Culture=neutral,PublicKeyToken=9a73871474819663" />
06            <importErrorMessage>Cannot import this Web Part.</importErrorMessage>
07        </metaData>
08        <data>
09            <properties>
10                <property name="Title" type="string">My first custom Web Part</property>
11                <property name="Description" type="string">
12                    webpart created by using web user controles
13                </property>
14                <property name="ChromeType">TitleOnly</property>
15                <property name="ChromeState">Normal</property>
16                <property name="ItemLimit" type="int">15</property>
17                <property name="ItemStyle" type="string">Default</property>
18            </properties>
19        </data>
20    </webPart>

6. Upload the "myprojectview.webpart" file to Share Point's Web Part gallery. (You should have permission to see the Site Action tab and upload) (Figure 14, 15, 17, 18 & 19)

Use your Web Part

To use your Web Part in a page, follow these steps;

1. Edit a SharePoint page (Figure 19, 20 & 21)
2. Select a zone into which you want to place your Web Part
3. Add the Web Part to the zone
4. Exit from the Edit mode