Operations related to SharePoint's Column type Person or Group

In this post I discussed how we can fetch records from SPListItem's column type Person or Group and stored information back into that column.

Here in following method first I am creating the SPUser by using EnsureUser method of SPSite and then adding that SPUser in SPListItem of SPList. Then I am fetching that SPUser from the SPListItem.

(In Folloing method codes with comments are self-explainatory)

    /// <summary>
    /// Working with with column of type [Person OR Group]
    /// </summary>
    void user()
    {
        //SPListItem spListItem;

        string loginName = "DOMAIN\\USERNAME";
        //Assign SPUser in your custom column and 
        //also its Login name (string) in seperate column 
        //(for ease in development operation)
        spListItem["User"] = oWebsite.EnsureUser(loginName);
        spListItem["UserLoginName"] = loginName;


        //Fetch SPUser values from SPColumn
        SPFieldUserValueCollection values 
              = (SPFieldUserValueCollection)spListItem["User"];
        loginName = values[0].User.LoginName;
    }

Note : From UI control point of view, we can use SharePoint's PeoplePicker control to enter and validate SPUser.

SharePoint's ListViewWebPart --- Part 2

In continuation with previous post SharePoint's ListViewWebPart.

In this post I discussed how we can apply complex filter in SharePoint's ListView WebPart.

In one of appliation I was using SharePoint's ListView webpart in an aspx page to display the records from SPList. In that page I am also providing a search funtionality. Based on the applied search I am changing the records displayed in ListView webpart.

So the challenge was to apply the complex search filter on ListView webpart programmatically and dynamically. ListView webpart exposes FilterString property but in that we can apply simple filter and that only to one column (as per my knowledge).

So to apply complex filter, first we have to get the SPView from which we are populating data in ListViewWebPart. Then change the Query property of SPView as per our requirement.

In following method I implemented the above mentioned logic (codes with comments are self-explainatory).

    /// <summary>
    /// Search functionality OR Apply complex filter in SharePoint's ListView WebPart
    /// </summary>
    /// <param name="eventArgument"></param>
    void searchRecord(string flag)
    {
        try
        {
            SPSite oWebsite = SPContext.Current.Web;
            SPList oList = oWebsite.Lists["LISTNAME"];
            //oListViewWP  is ListView Webpart's ID

            oWebsite.AllowUnsafeUpdates = true;

            //Clear Search filter OR revert back SPView modification.
            if (flag == "false")
            {
                //Listview webpart related settings
                oListViewWP.ListName = oList.ID.ToString("B").ToUpper();
                
                //Set SPView for Listview WP
                SPView view = oList.Views["SPView_NAME"];
                view.Query = string.Empty;
                view.Update();

                oListViewWP.ViewGuid = view.ID.ToString("B").ToUpper();
                oListViewWP.GetDesignTimeHtml();
            }
            else
            {
                //Build the SPQuery
                StringBuilder strbPreQuery 
                = new StringBuilder("<Where><Contains>");
               
                StringBuilder strbPostQuery 
                = new StringBuilder("</Value></Contains></Where>");

                string strQueryKeyword = string.Empty;

                switch (drpSearchKeywordType.SelectedValue)
                {
                    case "Contact Number":
                        strQueryKeyword 
                          = "<FieldRef Name='Contact_x0020_Number'/>
                            <Value Type='Text'>";
                        break;

                    case "Email ID":
                        strQueryKeyword 
                          = "<FieldRef Name='Email_x0020_ID'/>
                            <Value Type='Text'>";
                        break;
                }

                //Build SPQuery
                SPQuery oQuery = new SPQuery();
                oQuery.Query 
                  = strbPreQuery.ToString() + strQueryKeyword +
                    txtSearchKeyword.Text + strbPostQuery.ToString();
                SPListItemCollection itemCol = oWebsite.Lists[spListName].GetItems(oQuery);

                if (itemCol.Count > 0)
                {
                    //Listview webpart related settings
                    oListViewWP.ListName = oList.ID.ToString("B").ToUpper();
                    
                    //Modifying SPView as per required SPQuery.
                    SPView view = oList.Views["SPView_NAME"];
                    view.Query = oQuery.Query;
                    view.Update();

                    //Modifying ListView WebPart as changed SPView
                    oListViewWP.ViewGuid = view.ID.ToString("B").ToUpper();
                    oListViewWP.GetDesignTimeHtml();
                }
            }

            oWebsite.AllowUnsafeUpdates = false;
        }
        catch (Exception ex)
        {
        }
    }

How to programmatically attach a document or file in a SPListItem

How to programmatically attach a document or file in a SPListItem

In this post I discussed, how we can programmatically attach a document or file in a SPListItem of SPList (SharePoint's List).

Following method contains the logic to attach the document / file (codes with comments are self-explainatory).

    /// <summary>
    /// Programmatically Attach document in SPDocument Library 
    /// </summary>
    void documentAttachmentInSPList()
    {
        //SPListItem spListItem = null; //write code to get the SPListItem.
        //fileUpload is control ID of ASP.Net FileUpload control        
        if (!string.IsNullOrEmpty(fileUpload.FileName))
        {
            //Get file extension ( *.doc OR *.docx )
            string fileExtension 
             = fileUpload.FileName.Substring(fileUpload.FileName.IndexOf("."));

            //FILENAME is file name visible in SPListItem
            //Check file is already added or not. If added then delete it.
            for (int i = 0; i < spListItem.Attachments.Count; i++)
            {
                if ((spListItem.Attachments[i] != null) 
                    && (spListItem.Attachments[i].Equals("FILENAME" + fileExtension)))
                {
                    spListItem.Attachments.Delete("FILENAME" + fileExtension);
                    break;
                }
            }

            //Attach the file.
            spListItem.Attachments.Add("FILENAME" + fileExtension, fileUpload.FileBytes);
            
            //LISTNAME is SPList's name
            //See the attached file as link in user-created custom column.
            string attachmentURL = Request.Url.Scheme + "://" + Request.Url.Authority
                   + "/Lists/" + "LISTNAME" + "/Attachments/" + spListItem.ID + "/";

            spListItem["Attached File"] 
              = attachmentURL + "FILENAME" + fileExtension + ", View FILE";
        }
    }

How to programmatically upload a document in a SPDocumentLibrary

In this post I discussed, how we can programmatically upload a document in a SPDocumentLibrary (SharePoint's document library).

Following method contains the logic to upload the document (codes with comments are self-explainatory).

    /// <summary>
    /// Programmatically UPLoad document in SPDocument Library 
    /// </summary>
    void documentUploadInSPDocumentLibrary()
    {        
        //fileUpload is ASP.Net FileUpload control.
        if (!string.IsNullOrEmpty(fileUpload.FileName))
        {
            SPSite oWebsite = SPContext.Current.Web;
            oWebsite.AllowUnsafeUpdates = true;
            SPDocumentLibrary docLib 
                = oWebsite.Lists["DOCLIB_NAME"] as SPDocumentLibrary;

            //Get file extension ( *.doc OR *.docx )
            string fileExtension 
                = fileUpload.FileName.Substring(fileUpload.FileName.IndexOf("."));
            byte[] fileBytes = fileUpload.FileBytes;
            string destUrl 
                = docLib.RootFolder.Url + "/" + "MyFileName" + fileExtension;
            SPFile destFile = docLib.RootFolder.Files.Add(destUrl, fileBytes, true);
            destFile.Update();

            oWebsite.AllowUnsafeUpdates = true;
        }
    }

Check Record or FileName in a SPDocument Library

In this post I discussed how we can check whether a particular record / filename exist in a SPDocumentLibrary (SharePoint's document library) or not.

In the following method, first I discussed the 4 approaches suggested in following blog/forum, then the 5th apporach (in which I used SPQuery).

According to me the 5th and last approach is good-one because it would't raise an Exception.

    /// <summary>
    /// Check FileName/Records in SPDocumentLibrary
    /// </summary>
    void docLibFileExits()
    {
        SPWeb currentSite = SPContext.Current.Web;
        SPDocumentLibrary docLib 
            = (SPDocumentLibrary)currentSite.GetList(currentSite.Url + "/Documents");

        //APPROACH 1
        if (docLib.RootFolder.Files[fileName] != null)
        {
            // do something 
            //This throws an Argument Exception
        }

        //APPROACH 2
        if (docLib.RootFolder.Files[fileName].Exists == true)
        {
            // do something 
            //Again this throws a Argument Exception
        }

        //APPROACH 3
        if (currentSite.GetFile(fileName).Exists == true)
        {
            // do something
            //work
        }

        //APPROACH 4
        bool fileExist = true;

        try { docLib.RootFolder.Files[fileName]; }
        catch (ArgumentException) { fileExist = false; }

        if (fileExists)
        {
            //...
        }

        

        //APPROACH 5
        SPSite oWebsite = SPContext.Current.Web;
        SPList docLib = oWebsite.Lists["docLib"] as SPDocumentLibrary;
        SPListItemCollection itemcolCVs = null;
        SPQuery oQueryFileCheck = new SPQuery();

        string FILENAME = ""; //FileName that you want to search.

        //You can use EQUALS or CONTAINS operation as per your requirement.
        //
        /*
        StringBuilder strBFileCheckQuery = new StringBuilder(
            "<Where>" +
                "<Eq>" +
                    "<FieldRef Name='FileLeafRef' />" +
                    "<Value Type='Text'>FILENAME</Value>" +
                "</Eq>" +
            "</Where>");
        */

        StringBuilder strBFileCheckQuery = new StringBuilder(
            "<Where>" +
                "<Contains>" +
                    "<FieldRef Name='FileLeafRef' />" +
                    "<Value Type='Text'>FILENAME</Value>" +
                "</Contains>" +
            "</Where>");

        oQueryFileCheck.Query = strBFileCheckQuery.ToString();
        if (docLib.GetItems(oQueryFileCheck).Count > 0)
        {
            //To do
        }
        
    }

P.S. Here CAML Query Builder helped me a lot to identify and build above SPQuery.

CAML Tips n Tricks --- Part 2

CAML --- Collaborative Application Markup Language

In continuation with my previous post ---
CAML Tips n Tricks

In this post I am going to discuss about CAML query for batch Update and Delete.

1) BATCH DELETE

    
    /// <summary>
    /// Get the CAML query for BATCH DELETION
    /// </summary>
    /// <param name="spList">SPList instance</param>
    /// <returns>SPQuery</returns>
    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()));
        }
        sbDelete.Append("</Batch>");
        return sbDelete;
    }

    /// <summary>
    /// Multiple records Deletion with SPQuery
    /// </summary>
    void BatchDelete(Object sender, EventArgs e)
    {
        //Get the SPQuery
        StringBuilder finalDeleteAllQuery = buildBatchDeleteCommand(oList);
        
        //Get the SPSite and Allow unsafe updates
        SPSite oWebsite = SPContext.Current.Web;
        oWebsite.AllowUnsafeUpdates = true;

        //Run the Batch command
        oWebsite.ProcessBatchData(finalDeleteAllQuery.ToString());

        //Disable unsafe updates
        oWebsite.AllowUnsafeUpdates = false;
    }    

2) BATCH UPDATE

    /// <summary>
    /// Get the CAML query for BATCH UPDATION
    /// Here building SPQuery to change "Final Status" column's value
    /// </summary>
    /// <param name="spList">SPList instance</param>
    /// <returns>SPQuery</returns>
    StringBuilder buildBatchUpdateCommand(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\">Save</SetVar>" +
                            "<SetVar 
                                 Name=\"urn:schemas-microsoft-com:office:office
                                 #Final_x0020_Status\">{1}</SetVar>" +
                        "</Method>";

        //SPView specific SPListItems, you can skip following 3 lines 
        //and directly run foreach on SPList. 
        //E.g, foreach (SPListItem item in spList.Items)
        SPView spView = spList.Views["SPView_NAME"];
        SPViewFieldCollection collViewFields = spView.ViewFields;
        SPListItemCollection collItemsSrc = spList.GetItems(spView);

        foreach (SPListItem item in collItemsSrc)
        {
            sbDelete.Append(string.Format(command, item.ID.ToString(), "Completed"));
        }

        sbDelete.Append("</Batch>");
        return sbDelete;
    }

    /// <summary>
    /// Multiple records Update with SPQuery
    /// </summary>
    void BatchUpdate()
    {
        //Get the SPQuery
        StringBuilder finalUpdateAllQuery = buildBatchUpdateCommand(oList);
        
        //Get the SPSite and Allow unsafe updates
        SPSite oWebsite = SPContext.Current.Web;
        oWebsite.AllowUnsafeUpdates = true;

        //Run the Batch command
        oWebsite.ProcessBatchData(finalUpdateAllQuery.ToString());

        //Disable unsafe updates
        oWebsite.AllowUnsafeUpdates = false;
    }

Programmatically Export records from SPList to MS Excel

Programmatically export records from SPList to MS Excel in SharePoint.
What does above sentence mean???

Basically if you are a SharePoint developer OR user then you might know that we can do Import/Export between SPList and Microsoft Excel.
In this post I will discuss only about Export of records from SPList to MS Excel.
If you open a SharePoint's SPList, then in Action Toolbar, contains a link to open/export the SPList (in current applied SPView) in Excel Workbook and hence save it a Excel file (*.xls / *.iqv).

Now to achieve the above task programmatically we have to do some extra efforts:

1) Get the GUID of SPList - from where you want to export the records/SPListItem.

//SPList oList = ""; //Code to get the SPList

//Here I convert the GUID in Uppercase 
//and remove the special character hypen ('-') with required string.
string listGUID = oList.ID.ToString().ToUpper().Replace("-", "\u00252D");

2) Get the GUID of SPView - it can be default one (All Items) or custom SPView with required columns and settings like sorting, filter, etc.

//Here I convert the GUID in Uppercase 
//and remove the special character hypen ('-') with required string.
string viewGUID 
   = oList.Views["ExportRecords"].ID.ToString().ToUpper().Replace("-", "\u00252D");

3) Build the command, that will get executed on link's click.

//string spListName --- is SPList name. 
//I removed the special characters in that also.

//Here for readibility point of view I used string,
//you must use StringBuilder from performance point of view.
string strCommand = "javascript:EnsureSSImporter();"
                + "javaScript:ExportList('\u002f_vti_bin\u002fowssvr.dll?CS=65001\u0026"
                + "Using=_layouts\u002fquery.iqy\u0026"
                + "List=\u00257B" + listGUID + "\u00257D\u0026"
                + "View=\u00257B" + viewGUID + "\u00257D\u0026"
                + "RootFolder=\u00252FLists\u00252F" 
                + spListName.Replace("_", "\u00255F") + "\u0026"
                + "CacheControl=1')";

4) Consume/Call the above command via link's click.

<a id="linkExportData" onclick="<%=strCommand %>" 
      class="ms-sitetitle" style="font-size: 10px;">Export Records</a>

You can write the C# code of Step 1,2 and 3 in Page_Load event and HTML code of step 4 anywhere in you aspx page.

So when you click the "Export Records" link, it pop-up a dialog box to open the Records/SPList in Execl Workbook, that you can later on SAVE AS Excel file.

Once again IE Developer toolbar helped me to find the above solution/trick :)