Create Dynamic Visual Web Part in SharePoint

After a long time, I am going to discuss about creating a  dynamic visual web part in SharePoint 2010. Suppose, a company needs to create a sales forecasting web part on monthly basis. They want to display information in a web part of a page where the data will come from different lists from a web site and hence the result will be shown visually to highlight information based on conditions.

Objective:

Select a month from the drop down list of all months and based on the selected month, the grid view should change automatically. A LINQ will be executed in the background whenever you change a month from the drop down list and the data displayed inside the grid view should match the query results.

Solution:

First of all, Create a  visual studio project and select Visual Web Part from the SharePoint 2010 templates. This automatically adds a Feature with a Default Visual Web Part in your solution. Please note that, while creating a visual web part, you must choose as Farm Solution as the files of visual web parts (ASCX Files) need to be deployed physically inside 14 hive.

You will need to write LINQ to query your SharePoint Lists. So, create a Entity Class using SPMetal.exe. To know more about how to use SPMetal, go here. Create and add the entity class in your project. For my example; I named the class as MyEntity.cs.

To start off with the solution, let us drag and drop a drop down box and create SPGridView object. The ASCX code is given below:

  1. <%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
  2. <%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
  3. <%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
  4. <%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
  5. <%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
  6. <%@ Import Namespace="Microsoft.SharePoint" %>
  7. <%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
  8. <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="VisualWebPart1UserControl.ascx.cs" Inherits="SalesForecastWebPart.VisualWebPart1.VisualWebPart1UserControl" %>
  9.  
  10. <asp:dropdownlist runat="server" ID="MonthList" AutoPostBack="True" onselectedindexchanged="MonthList_SelectedIndexChanged"
  11.     >
  12.     <asp:ListItem Selected="True">–Select Month–</asp:ListItem>
  13.     <asp:ListItem Value="1">January</asp:ListItem>
  14.     <asp:ListItem Value="2">February</asp:ListItem>
  15.     <asp:ListItem Value="3">March</asp:ListItem>
  16.     <asp:ListItem Value="4">April</asp:ListItem>
  17.     <asp:ListItem Value="5">May</asp:ListItem>
  18.     <asp:ListItem Value="6">June</asp:ListItem>
  19.     <asp:ListItem Value="7">July</asp:ListItem>
  20.     <asp:ListItem Value="8">August</asp:ListItem>
  21.     <asp:ListItem Value="9">September</asp:ListItem>
  22.     <asp:ListItem Value="10">October</asp:ListItem>
  23.     <asp:ListItem Value="11">November</asp:ListItem>
  24.     <asp:ListItem Value="12">December</asp:ListItem>
  25. </asp:dropdownlist>
  26.  
  27. &nbsp;&nbsp;<b>Monthly Forecast</b>
  28. <SharePoint:SPGridView ID="spGridView" runat="server"
  29.     AutoGenerateColumns="false"
  30.     onprerender="spGridView_PreRender" ondatabound="spGridView_DataBound" onrowdatabound="spGridView_RowDataBound">
  31.     <HeaderStyle HorizontalAlign="Left" ForeColor="Navy" Font-Bold="true" />
  32.     <Columns>
  33.         <SharePoint:SPBoundField DataField="Title" HeaderText="Consultant">
  34.         </SharePoint:SPBoundField>
  35.           <asp:TemplateField HeaderText="Total ITS Ricoh Lab Revenue">
  36.             <ItemTemplate>
  37.         <asp:Label ID="lblSales" runat="server" Text='<%# String.Format("{0:f2}",DataBinder.Eval(Container.DataItem,"TotalSales")) %>' ></asp:Label>
  38.         </ItemTemplate>
  39.         </asp:TemplateField>
  40.          <asp:TemplateField HeaderText="Total ITS Ricoh Lab Revenue Quota">
  41.             <ItemTemplate>
  42.         <asp:Label ID="lblQuota" runat="server" Text='<%# String.Format("{0:f2}",DataBinder.Eval(Container.DataItem,"ITSRicLabRev")) %>' ></asp:Label>
  43.         </ItemTemplate>
  44.         </asp:TemplateField>
  45.         <asp:TemplateField HeaderText="Status">
  46.             <ItemTemplate>
  47.                 <asp:Label ID="Status" runat="server" Text='<%# Eval("Status") %>' />
  48.             </ItemTemplate>
  49.              
  50.         </asp:TemplateField>
  51.       
  52.         
  53.     </Columns>
  54. </SharePoint:SPGridView>

If you look inside the above code, you will find that I have created four columns inside the grid view to display Employee (Consultant) Name, Total Revenue, Total Revenue Quota and Status and a drop down box containing all months. Now, create the entity object for the lists inside your ascx.cs file. For my scenario, I had to query two different lists and I created the entity and data context object as below:

EntityList<SCTXPSITSVCsForecastingItem> SCTXPSITSVCsForecasting;

EntityList<SolnConsultantsItem> SolnConsultants;

protected void Page_Load(object sender, EventArgs e)

{

    string spWebUrl = SPContext.Current.Web.Url;

    MyEntitiesDataContext dc = new MyEntitiesDataContext(spWebUrl);

    SCTXPSITSVCsForecasting = dc.GetList<SCTXPSITSVCsForecastingItem>(@"SCTX PS / ITSVCs Forecasting");

    SolnConsultants = dc.GetList<SolnConsultantsItem>("SolnConsultants");

 

}

 

From the above code, you can see I created two data context object where my entity class was “MyEntities”. I then called the two lists in the page load event by the GetList() Method.

Inside the SelectedIndexChanged() method of the drop down list for month, I called a method to create the LINQ and passed the selected item as the parameter. If you notice the LINQ below, you will see that I have used a join query to select data from two different lists. For my scenario, I had a list called “SCTXPSITSVCsForecasting” where there was a column named “ITSRicohLaborRevenue” in which all the employees (consultants) monthly forecast had to be summed up. So, I used an aggregated function (SUM) to sum up all the employees monthly forecast and divided by the standard monthly forecast to determine their status (if greater than 90%, “Green”, if greater than or equal to 75%, “Yellow” and if below 75%, “Red”).

  1. protected void MonthList_SelectedIndexChanged(object sender, EventArgs e)
  2.         {
  3.             var selected = MonthList.SelectedValue;
  4.             getConsultants(Convert.ToInt32(selected));
  5.            
  6.         }
  7.         private void getConsultants(int mo)
  8.         {
  9.  
  10.             var ConsultantQuery = from c in SolnConsultants.ToList()
  11.                                   join s in SCTXPSITSVCsForecasting.ToList()
  12.  
  13.                                on c.Title equals s.Consultant
  14.                                   where s.CloseDate.Value.Month.Equals(mo)
  15.                                   group s by new { s.Consultant, c.ITSRicLabRev } into result
  16.                                   select new
  17.                                   {
  18.                                       Title = result.Key.Consultant,
  19.                                       TotalSales = result.Sum(s => s.ITSRicohLaborRevenue),
  20.                                       ITSRicLabRev = result.Key.ITSRicLabRev / 12,
  21.                                       Status = (((result.Sum(s => s.ITSRicohLaborRevenue)) / (result.Key.ITSRicLabRev / 12) * 100) > 90 ? "Green" :
  22.                                       (((result.Sum(s => s.ITSRicohLaborRevenue)) / (result.Key.ITSRicLabRev / 12) * 100) >= 75 ? "Yellow" : "Red"))
  23.  
  24.                                   };
  25.  
  26.             spGridView.DataSource = ConsultantQuery;
  27.             spGridView.DataBind();
  28.  
  29.  
  30.         }

To display the status of the forecast visually, I had to add another method for the grid view on RowDataBound():

  1.  
  2.         protected void spGridView_RowDataBound(object sender, GridViewRowEventArgs e)
  3.         {
  4.             if (e.Row.RowType == DataControlRowType.DataRow)
  5.             {
  6.                 Label lblStatus = e.Row.FindControl("Status") as Label;
  7.                 string status = Convert.ToString(DataBinder.Eval(e.Row.DataItem, "Status"));
  8.                 if (status == "Red")
  9.                 {
  10.                     DataControlFieldCell d = lblStatus.Parent as DataControlFieldCell;
  11.                     // change the backcolor like this
  12.                     d.BackColor = System.Drawing.Color.Red;
  13.                     // change the row color like this
  14.                     e.Row.BackColor = System.Drawing.Color.LightBlue;
  15.                     // change the text color like this
  16.                     lblStatus.ForeColor = System.Drawing.Color.White;
  17.                 }
  18.                 else if (status == "Yellow")
  19.                 {
  20.                     DataControlFieldCell d1 = lblStatus.Parent as DataControlFieldCell;
  21.                     d1.BackColor = System.Drawing.Color.Yellow;
  22.                     e.Row.BackColor = System.Drawing.Color.Aqua;
  23.                     lblStatus.ForeColor = System.Drawing.Color.Blue;
  24.                 }
  25.                 else
  26.                 {
  27.                     DataControlFieldCell d2 = lblStatus.Parent as DataControlFieldCell;
  28.                     d2.BackColor = System.Drawing.Color.Green;
  29.                     e.Row.BackColor = System.Drawing.Color.LightCyan;
  30.                     lblStatus.ForeColor = System.Drawing.Color.WhiteSmoke;
  31.                 }
  32.             }
  33.         }

You can see from the above code that I am changing the Background color , Foreground color of the row based on the Status column’s value.

After deploying and adding the web part on a page, you will get a following screen:

ricohsaleswebpart

ricohsaleswebpart1

You can see from the above screenshots that the gird view is changing dynamically based on the selected month. Following this basic example, you can create more complex dynamic visual web parts in SharePoint.

How to create Dynamic Column in a List Event Receiver & set the Column Name with a Dynamic Value and set Value of that Column for Current List item?

Few days ago, I was working on a List where the necessity of creating dynamic column raised. Also, the need to initiate the column name inside an event receiver and setting the value of the newly column for the current item were needed. So, I created a custom (demo) List which is named as “DemoProjects”.

I created the following columns:

Title, Description, Comments, RadComments (for checking the event)

I needed the column to be created for an existing List item. So, I created a new item like the following keeping “Comments” and “RadComments” blank.

image

image

Now, in order to create the event receiver I created a new project of “Event Receiver” type in Visual Studio 2010.

I set the “ItemUpdated” method to True as I need to create the column while updating the list item.

image

After that, Visual Studio automatically creates the method name and parameters for ItemUpdated method

 public override void ItemUpdated(SPItemEventProperties properties)
 {
    base.ItemUpdated(properties);
 }

Now, this base.ItemUpdated runs will run for each custom list. In order to attain my objective, I have modified this method to the following: ( I have tried to clarify all the steps by commenting each line of code )

  1. public override void ItemUpdated(SPItemEventProperties properties)
  2.        {
  3.            if (properties.ListTitle == “DemoProjects”)
  4.            {
  5.                string colComments = “”; // Column Display name
  6.                string colCommentsStr = “”;  //  Internal name of the Column name
  7.                string curUser = “”;  // Current user name
  8.                using (SPSite site1 = new SPSite(properties.SiteId)) //Accessing the current site with current user permission
  9.                {
  10.                    using (SPWeb web1 = site1.OpenWeb(properties.RelativeWebUrl))
  11.                    {
  12.                        curUser = web1.CurrentUser.Name.ToString();  // Getting the current user display name
  13.                    }
  14.                }
  15.                SPSecurity.RunWithElevatedPrivileges(delegate()  // To Elevate the permisson of the current user which runs as a system acc to create the list column
  16.                {
  17.                    using (SPSite site = new SPSite(properties.SiteId))
  18.                    {
  19.                        using (SPWeb web = site.OpenWeb(properties.RelativeWebUrl))
  20.                        {
  21.                            EventFiringEnabled = false;   // Disabling the events firing to prevent unnecessary actions
  22.                            web.AllowUnsafeUpdates = true;  // To execute actions which are not permitted by default
  23.                            SPList radList = web.Lists[properties.ListId];  // Getting the current List
  24.                            SPListItem project = web.Lists[properties.ListId].GetItemById(properties.ListItem.ID); // Getting the Current List Item
  25.                            colComments = “Comments: “ + curUser; // Setting the column name with current user name
  26.                            if (!radList.Fields.ContainsField(colComments))
  27.                            {
  28.                                radList.Fields.Add(colComments, SPFieldType.Note, false); // Adding new column in current List
  29.                                radList.Update(); // Updating the current List after adding new column
  30.                                Guid guidViewID = radList.Views[“All Items”].ID; // Getting the Guid for “All items” view of current list
  31.                                SPView vw = radList.GetView(guidViewID);
  32.                                vw.ViewFields.Add(colComments);  // Adding the new column in All items view
  33.                                vw.Update();  // Updating the All items view
  34.                                project.Update(); // Updating the current List item
  35.                                base.ItemUpdated(properties);  // Attaching current event to current list item
  36.                                radList.Update();  // Updating the current list
  37.                            }
  38.                            colCommentsStr = radList.Fields[colComments].InternalName.ToString(); // Getting the internal name of newly created column
  39.                        }
  40.                    }
  41.                });
  42.                using (SPSite site2 = new SPSite(properties.SiteId))  // Again accessing the site with current user permission
  43.                {
  44.                    using (SPWeb web2 = site2.OpenWeb(properties.RelativeWebUrl))
  45.                    {
  46.                        SPListItem project1 = web2.Lists[properties.ListId].GetItemById(properties.ListItem.ID); // Getting current List item
  47.                        project1[colCommentsStr] = project1[“Comments”].ToString(); // Setting current item’s new column’s value to Comments column
  48.                        project1[“RadComments”] = “Done!”; // Settting RadComments column’s value to “Done”
  49.                        project1.Update();   // Updating the current list item
  50.                        web2.AllowUnsafeUpdates = false; // After completing the action setting unsafe updates to false
  51.                    }
  52.                }
  53.            }
  54.            EventFiringEnabled = true;  // Enabling event firing for user interaction
  55.            base.ItemUpdated(properties); // Attaching current event to current list item
  56.        }

Now, after deploying this Event Receiver, I edited the previously created new item.

image

Notice that, I kept “RadComment” blank and there are currently no other columns in this list except these four. So, what happened when I clicked “Save”.

See the following window:

image

So, my dynamic column got created now (Comments: Current User name) and the value is also set by the event receiver which made it possible to know who commented the item. Now, if another user edits this item, another “Comment: Username” will be automatically be created and thus I can track all the comments made by the item editors and find out what he commented for that item. That’s pretty cool, isn’t it? Enjoy!!