Friday, December 10, 2010

Working with SPQuery

As a sharepoint developer most of the people might have used SPQuery and CAML atleast once.

Writing a Basic Query

SPContext ctx = SPContext.Current;
using (SPSite site = new SPSite(ctx.Site.ID))
      using (SPWeb web = site.OpenWeb(webid))
         SPList list = web.Lists["MyTestList];
         SPQuery query = new SPQuery();
         //You have to call GetItems function of SPlist to execute the CAML query
         SPListItemCollection items = list.GetItems(query);      

Here it will return All the Items.But in real time we need to specify the conditions and attributes to fine tune the Results. We need to have basic understanding of CAML syntax inorder to take full advantage of SPQuery Object.If you have a CAMLBuilder tool then it will be always handy in writing CAML queries.You could douwnload CAML builder from here

Understanding SPQuery Properties

1. SPQuery.RowLimit
   This Property allows you to set how many rows you need in your result.Its quiet common that your Query could return more than 500 rows so its better to limit the number of rows in a result and use paging to retrive the rest of the result.I will explain paging below.

2. spquery.ViewAttributes

View attribute is one of the important attribute which is veryusefull to set how you are going to retrieve the Data.
  Scenario 1: You want retrieve all the items including items in folder and subfolders then set it like the following

 //If you want to return only file
          SPQuery.ViewAttributes = "Scope='Recursive'";   //If you want to return All items
          SPQuery.ViewAttributes = "Scope='RecursiveAll'";

  Scenerio 2 : You want retrive only files
          spquery.ViewAttributes = "Scope=\"FilesOnly\"";

  Scenario 3: Only Approved Items
         spquery.ViewAttributes = "ModerationType='HideUnapproved'"

3. SPQuery.Folder
   This property is usefull when you want to retrieve the data only from specific folder.In real world scenario you need to retrive the items from specific folder rather than retrieving all items in the list.
    SPQuery.Folder = folder; //It should be a SPFolder Object

This property helps to set which are the fields /columns you want in the result.There is no point in returning all the coloumns.So its better practice to specify the fields you want to return.

     //Name is the coloumn name / internal name of the field
     SPQuery.ViewFields = "";

5. SPQuery.Query

This property used to set the real CAML query.Build your CAML string and assign to this property.

SPQuery.Query = String.Format("<Where>
                                                              <FieldRef Name=\'FileLeafRef\'/>
                                                              <Value Type=\"Text\"></Value>
                                                  </Where>", searchText);

//searchText is name of the file you want to search

Note : One thing here we have to remember is no need to add query tag in your XML.

If you are using Date time in your query then set includetimevalue = true. See Below

Query = "<Where><Eq><FieldRef Name='Modified'/><Value Type='DateTime' IncludeTimeValue='TRUE'>"+ LastCheckedDate + "</Value></Eq>

CAML Notations

•Eq = equal to
•Neq = not equal to
•BeginsWith = begins with
•Contains = contains
•Lt = less than
•Leq = less than or equal to
•Gt = greater than
•Geq = greater than or equal to
•IsNull = is null
•IsNotNull = is not null

Paging In SPQuery

Using paging with SPQuery is good practice.Its better to write code that will perform efficient way than giving bad user experience.

      using (SPSite site = new SPSite(SiteCollectionURL))
           using (SPWeb web = site.OpenWeb(WebName))

               SPQuery query = new SPQuery();
               query.ViewAttributes = "Scope=\"Recursive\"";
               query.Folder = list.RootFolder;


                   SPListItemCollection listItems = list.GetItems(query);
//Perform the operations you want
                   query.ListItemCollectionPosition = listItems.ListItemCollectionPosition;
//If you are showing in a grid view then stor the listItems.ListItemCollectionPosition in
                  viewstate or something like that
               } while (query.ListItemCollectionPosition != null);

Limitation of SPQuery Object
You cannot use the SPquery object to query across multiple site collection instead use SPSiteDataQuery

Wednesday, December 8, 2010

Usage Report Sharepoint 2007

Sharepoint has usage reporting feature , this will be handy for the basic usage reporting.This feature is there in sharepoint but we need to enable them to start reporting.One of the drwaback of this reporting it supports only 30 days.

This report could be filtered by the following criteria.
  • Page
  • Browser
  • Referrer URL
  • User
  • Operating System
If you are sharepoint admin you will be intrested in following information from Usage Report

  • Total hits and recent bandwidth usage across all sites.
  •  Maximum storage space allowed.
  • Total amount of storage used by the site collection.
  • Percent of storage space used by Web Discussions.
  • Number of users for all sites in the hierarchy.
Let see how we will enable this feature

1. Go To Central Administrative Site of Sharepoint Farm

2. Go To Operations Tab
3. In Operations Page Go To Logging And Reporting Section
4. Click On Usage Analysis Processing Link

5. In Usage Analyisis Page Enable Loging and set the Log Path

6. Enable Usage Analysis Processing and Set the time
7. Click OK and Save the settings

Now you are enabled analysis in you Farm, So the next step is to enable it in Shared Service level.

1. Go To Shared Service Home Page

2. Go To Office SharePoint Usage Reporting section, click Usage reporting
3. In Configure Advanced Usage Analysis Processing page, in the Processing Settings section, click Enable advanced usage analysis processing.

4. Search Query Logging section, select Enable search query logging

Now you could enable it in the Site Collection.
1. Go to the Root Site (Home page of your site)
2.Click on the settings ->Modify All settings

3. In Settings Page go to SiteCollection Administrative Section and click Site Collectin Features

4. In Feature Listing page Activate Reporting Feature

Let see How we can view this reports

1. Site administrators   -  Site Settings -> Site Administration section -> Site usage reports
2. SSP administration site - Site Settings -> Site Administration section -> Usage summary

Monday, December 6, 2010

Installing MOSS 2007

MOSS Installation is straight forwar but at times, it could take you to a position where you need reinstall everything.Here i just trying to explain the option available at each step so you could install it with ease.

1. Double click on the MOSS Installer.

2. Click on RUN
3.Enter the product key and you could see tick mark changed to Green (If you entered right product key)
4. Click on Continue
5.Accept kBthe license agreement by checking the CheckBox

6. Click on Continue

7. In this screen You have two option BASIC and ADVANCED

Basic - If you are installing in a developer box or a system you could use for DEMO purpose choose the BASIC installation.It will install the SQL Server Express Edition and Sharepoint into the BOX.It will not allow you to configure much , more equivalent to a one click installation.

ADVANCED - Choose advanced option whenever you are installing in standared environment (DIT,SIT OR Production).This option allow you to configure Sharepoint installation properly.

8. Click on ADVANCED

9. Above screen prescent you with important option tabs in your installation
Server Type Tab -  prescent you with three options

1.Complete :- If  you are installing for any of the standared environment choose complete option.So it will install all  the component into the system.There only two scenario i could think if you are not choosing this option  you are adding a additional server to your Sharepoint Farm or you are setting up a development environment.

2. Web Front End - This option you will choose only when adding web front end server to your sharepointfarm , means you already have complete installation and sharepoint central admin site is running in that server and want to aad additional server for scaling you farm.If you setting up the first BOX(Server) in a your farm then you should go with Complete option.

3. Stand alone - If you are setting up a developer box or demo box you coul choose this option.Because you dont have any plan to scale this by adding another server.Dont use this option for any of your standared environments.

File Location Tab prescent you with the option

 In File location Tab you could specify where all the physical files of sharepoint installation goes to.Its equivalent any installation package which allow you to specify the physical location of the files.Normaly sharepoint installation will take around 8GB so if you prefer to keep the installation other than c: drive you could specify that and you could also isolate the sharepoint installation to a specific drive so it will easy for the maintainence.

Feed Back :- Good to keep this enabled so microsoft could improve from feedback.

10. Choos Complete Option
11. Click on Install Now
12. You will the screen with a progress Bar. Once installation Complete it will present with following screen

13.If you want to run the Sharepoint configuration Wizard then keep the check box checked.If you want to run it later (After a cup of tea) uncheck the option.


Now we installed the sharepoint but we need to configure the sharepoint .So sharepoint could store all the configuration information in its configuration Database and run based on the configuration.

You could strat the configuration wizard at any point of time by going to Administrative Tools-> Sharepoint Poduct and Technologies Configuration Wizard.

1. Click On Next
2.Click on Yes

3.This step you could executing in different scenario

Executing Config Wizard First Time
In this case it will ask you the DataBase Sever Name , Config DB name and User Name and Password to connect. Provide those information according to your environment

Executing Config Wizard Again (Re running the config)
 Inthis case it will prescent you with above screen with DataBase Sever Name , Config DB name prepopulated and greyed.  you could select do not disconnect and run the wizard.If you want to create every thing from sratch choose disconnect.

Executing Config Wizard To disconnect from a farm.
  Inthis case it will prescent you with above screen  with DataBase Sever Name , Config DB name prepopulated and greyed. So you could select  Disconnect and run the wizard.

Executing Config Wizard After Service Pack or Patch Installation
Inthis case it will prescent you with above screen with DataBase Sever Name , Config DB name prepopulated and greyed. you could select do not disconnect and run the wizard.

4. Click on Next

5. Above screen allow you to set the port number in which you are going to run the Central admin site.Give a port number which you could remember easily.

6.Above screen also allows you to choose what kind of authentication you are going to use for Sharepoint.If you didnt have any specific need to use Kerberose go with NTLM.If you are going to use Kerberose then you are going to make the configuration more complex.Here i am not going to cover the Kerberos because it requires seperate article for that.

7. Click On Next

8. Verify the information provided are correct and click Next

9.You will see 8 stage  configuration Going on something like below

10.After success full completion you will see the following screen

Now the time to Go to you central admin Site and Do the rest of Configuration.

Friday, December 3, 2010

Minimal Permission required to execute the code in Sharepoint

I always faced the access permission issue in consuming the Sharepoint object model from a exe or webservice or windows service with a user who have limited access.Each time i figured out the permission required then forget blog it so all the time i have to Dig again to find the permission.So let see what should we do in each type of application.

User Permission
SharePoint SiteMinimum Read Permission
Sharepoint ServerAdd to WSS_ADMIN_WPG group
DatabaseSharepoint Content DB (Site collection database) - db_owner permission
Sharepoint Config DB (Config DB of sharepoint installation) - - db_owner permission

Web Service / WCF Service
Most of  sharepoint developers could have created a webservice/wcf service which uses sharepoint object model to do some operation in a sharepoint site.

The Web application at http://localhost/mysite/content could not be found. Verify that you have typed the URL correctly. If the URL should be serving existing content, the system administrator may need to add a new request URL mapping to the intended application.

We are bound to solve the issue within time limit so forced to use the same application pool identity used by the Sharepoint Site and leave the issue behind.But if you want to know more about what should be the permission required for the user look at the table below.Make sure you application pool identity user have following preveleges.

Sharepoint Site
Add the IIS Application Identity user to Sharepoint Read Group in Sharepoint Site (minimal permission).If you need to do write operation or content type changes give the higher permissio to the user (Content Owner).This is to make sure that

SharePoint Server
Add the IIS Application Pool Identity User into WSS_ADMIN_WPG User Group of the physical server where Sharepoint is installed.

Database Server
IIS Application Pool Identity User should have DB_OWNER access in Sharepoint Configuration DataBase (Configuration DB created during the installation of sharepoint farm) and Sharepoint Content DataBase(Sharepoint Site Collection DataBase of the site you are trying to access).

Windows Service
In windows service case, the user account under which Window Service is running should have the similar access to application pool identity user case said above.

Sharepoint Site
Add the Window Service user account to Sharepoint Read Group in Sharepoint Site (minimal permission).If you need to do write operation or content type changes give the higher permissio to the user (Content Owner).This is to make sure that

SharePoint Server
Add the Window Service user account into WSS_ADMIN_WPG User Group of the physical server where Sharepoint is installed.

Database Server
Window Service user account should have DB_OWNER access in Sharepoint Configuration DataBase (Configuration DB created during the installation of sharepoint farm) and Sharepoint Content DataBase(Sharepoint Site Collection DataBase of the site you are trying to access).