Merging document libraries using C#

To continue with my last post about using Content Query web part to merge libraries in same site collection. We didn’t find this solution a elegant one due to lack of control on functionality and styling. For example, My user asks to sort the merge library on document creation date and apply grouping.Though it’s possible if you are very good in XML/XSLT and willing to write ton of code.I have followed custom coding path using C# and here is the solution.

  • Create new Visual Web Part project in Visual Studio 2010 with C# and .NET Framework 3.5
  • Add SPGridView control on UserControl.ascx page,
<SharePoint:SPGridView
  runat="server"
  ID="spLibraryView"
  AutoGenerateColumns="false"
  RowStyle-BackColor="#DDDDDD"
  AlternatingRowStyle-BackColor="#EEEEEE" />
  • Add following code on UserControl.ascx.cs page
using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using System.Data;
using Microsoft.SharePoint.Utilities;

namespace WebPartDisplaySiteData.VisualWebPart1
{
    public partial class VisualWebPart1UserControl : UserControl
    {
         protected void Page_Load(object sender, EventArgs e)
        {
            getData();
        }

         public void getData()
         {
             DataTable dt = new DataTable();
             DataRow dr;
             DataColumn dc;

             SPSite curSite = SPContext.Current.Site;
             SPWebCollection subSites = curSite.AllWebs;

             dc = new DataColumn("Title", Type.GetType("System.String"));
             dt.Columns.Add(dc);
             dc = new DataColumn("ReferenceNo", Type.GetType("System.String"));
             dt.Columns.Add(dc);
             dc = new DataColumn("Domain", Type.GetType("System.String"));
             dt.Columns.Add(dc);
             dc = new DataColumn("Created", Type.GetType("System.DateTime"));
             dt.Columns.Add(dc);
             dc = new DataColumn("SiteUrl", Type.GetType("System.String"));
             dt.Columns.Add(dc);

             for (int i = 0; i < subSites.Count; i++)
             {
                 SPListCollection lists = subSites[i].Lists;

                 foreach (SPList list in lists)
                 {
                     if (list.Title == "Published Documents")
                     {
                         SPQuery myquery = new SPQuery();

                         myquery.Query = "<GroupBy><FieldRef Name='Domain' />
                                         </GroupBy><OrderBy><FieldRef Name='Domain' /></OrderBy>";

                         SPListItemCollection items = list.GetItems(myquery);

                         foreach (SPListItem item in items)
                         {
                             if (item != null)
                             {
                                 dr = dt.NewRow();

                                 dr["Title"] = item["Title"];
                                 dr["ReferenceNo"] = item["ReferenceNo"];
                                 dr["Domain"] = item["Domain"];
                                 dr["Created"] = item["Created"];
                                 dr["SiteUrl"] = subSites[i].Url + 
                                 "/Published documents/Forms/DispForm.aspx?ID=" 
                                 + item["ID"].ToString(); ;

                                 dt.Rows.Add(dr);
                             }
                         }
                     }
                 }
             }

             dt.DefaultView.Sort = "Domain";

             HyperLinkField colTitle = new HyperLinkField();
             colTitle.HeaderText = "Title";
             colTitle.DataTextField = "Title";
             string[] SiteUrl = { "SiteUrl" };
             colTitle.DataNavigateUrlFields = SiteUrl;
             colTitle.NavigateUrl = "SiteUrl";
             spLibraryView.Columns.Add(colTitle);

             BoundField colReferenceNo = new BoundField();
             colReferenceNo.DataField = "ReferenceNo";
             colReferenceNo.HeaderText = "ReferenceNo";
             spLibraryView.Columns.Add(colReferenceNo);

             BoundField colDomain = new BoundField();
             colDomain.DataField = "Domain";
             colDomain.HeaderText = "Domain";
             spLibraryView.Columns.Add(colDomain);

             BoundField colCreated= new BoundField();
             colCreated.DataField = "Created";
             colCreated.HeaderText = "Created";
             spLibraryView.Columns.Add(colCreated);

             spLibraryView.AllowGrouping = true;
             spLibraryView.AllowGroupCollapse = true;
             spLibraryView.GroupField = "Domain";

             spLibraryView.DataSource = dt;
             spLibraryView.DataBind();
         }
    }
}
  • Build and deploy the project. Add this web part and you should see the output like,

Note: I’m looping all sites in current site collection and reading only specific library which is “Published Documents” . Modify logic based on requirement.

Advertisements

Displaying Data from Oracle using Visual Web Part

This article going to demonstrate about creating visual web part which displays Oracle data on .NET Grid view.

  • Create new SharePoint web part project in Visual Studio 2010 and choose SharePoint site for deployment.

  • Add Reference of Oracle.DataAccess dll in the project.

  • Open “VisualWebPart1UserControl.ascx” and add Grid view with desire layout. So here is the my design,

<%@ Assembly Name=”$SharePoint.Project.AssemblyFullName$” %>
<%@ Assembly Name=”Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Register Tagprefix=”SharePoint” Namespace=”Microsoft.SharePoint.WebControls” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Register Tagprefix=”Utilities” Namespace=”Microsoft.SharePoint.Utilities” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Register Tagprefix=”asp” Namespace=”System.Web.UI” Assembly=”System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35″ %>
<%@ Import Namespace=”Microsoft.SharePoint” %>
<%@ Register Tagprefix=”WebPartPages” Namespace=”Microsoft.SharePoint.WebPartPages” Assembly=”Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c” %>
<%@ Control Language=”C#” AutoEventWireup=”true” CodeBehind=”VisualWebPart1UserControl.ascx.cs” Inherits=”PrismAuditWebPart.VisualWebPart1.VisualWebPart1UserControl” %>

<asp:ScriptManagerProxy ID=”ScriptManagerProxy1″ runat=”server”>
</asp:ScriptManagerProxy>

<asp:UpdateProgress ID=”UpdateProgress1″ runat=”server” AssociatedUpdatePanelID=”UpdatePanel1″>
<progresstemplate>
<asp:Label id=”lblProgress” runat=”server” Text=”Loading,Please wait….” Width=”200px” BackColor=”#FFFF80″ ForeColor=”Maroon” Font-Bold=”True” Style=”padding: 5px”></asp:Label>
</progresstemplate>
</asp:UpdateProgress>
<div class=”clear”>
<br />
</div>
<asp:UpdatePanel ID=”UpdatePanel1″ runat=”server”>
<contenttemplate>
<asp:GridView ID=”GridView”
runat=”server”
CellPadding=”4″
PageSize=”15″
AllowPaging=”True”
onpageindexchanging=”GridView_PageIndexChanging”
AutoGenerateColumns=”False”
Width=”900px” EnableModelValidation=”True” ForeColor=”#333333″
GridLines=”None”>

<AlternatingRowStyle BackColor=”White” ForeColor=”#284775″ />

<Columns>

<asp:BoundField DataField=”RKPI_DATE” HeaderText=”Date”>
<HeaderStyle HorizontalAlign=”Left” />
</asp:BoundField>

<asp:hyperlinkfield DataTextField=”DOCAUTHORNAME” HeaderText=”Author” datanavigateurlfields=”DOCID” datanavigateurlformatstring=”show_detailse.aspx?DOCID={0}” >
<HeaderStyle HorizontalAlign=”Left” />
</asp:hyperlinkfield>

<asp:BoundField DataField=”RKPI_CONDUCTEDLEDBY” HeaderText=”Conducted By”>
<HeaderStyle HorizontalAlign=”Left” />
</asp:BoundField>

<asp:BoundField DataField=”RKPI_TYPE” HeaderText=”Type”>
<HeaderStyle HorizontalAlign=”Left” />
</asp:BoundField>

<asp:BoundField DataField=”RKPI_SUMMARY” HeaderText=”Summary”>
<HeaderStyle HorizontalAlign=”Left” Width=”200px” />
</asp:BoundField>
</Columns>
<EditRowStyle BackColor=”#999999″ />
<FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<HeaderStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<PagerStyle BackColor=”#284775″ ForeColor=”White” HorizontalAlign=”Center” />
<RowStyle BackColor=”#F7F6F3″ ForeColor=”#333333″ />
<SelectedRowStyle BackColor=”#E2DED6″ Font-Bold=”True” ForeColor=”#333333″ />
</asp:GridView>
</contenttemplate>
</asp:UpdatePanel>

  • Open “VisualWebPart1UserControl.ascx.cs” page and add following C# code to connect and fetch details from Oracle.
using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Oracle.DataAccess.Client;
using System.Data;

namespace AuditWebPart.VisualWebPart1
{
    public partial class VisualWebPart1UserControl : UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
               doBindGrid();

        }

        protected void GridView_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView.PageIndex = e.NewPageIndex;
            doBindGrid();
        }

        public void doBindGrid()
        {

            string connectiostr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server)
                                  (PORT=1569))(CONNECT_DATA=(SERVICE_NAME=service)))
                                  ;user id=user;password=pass;";

            OracleConnection oraCon = new OracleConnection(connectiostr);

            string sqlQuery = "SELECT * FROM T_AUDIT";

            try
            {
                oraCon.Open();

                OracleCommand oraCommand = new OracleCommand(sqlQuery, oraCon);

                OracleDataAdapter oraAdaptor = new OracleDataAdapter(oraCommand);

                DataSet oraDataSet = new DataSet();

                oraAdaptor.Fill(oraDataSet);

                GridView.DataSource = oraDataSet;

                GridView.DataBind();

                oraAdaptor.Dispose();
            }
            catch (Exception ex)
            {
                status.Text = ex.Message;
            }
            finally
            {
                oraCon.Dispose();
            }

        }
    }
}
  • Once done build and deploy the project.
  • On successful deployment of the web part , you should see your web part under “Custom” category,

  • Result should be something like that,