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

Business Connectivity Services (BCS) with Oracle using Visual Studio 2010 – Part 1

In my last post I’ve explained steps to connect SQL Server using BCS and SharePoint Designer 2010. However, SPD 2010 doesn’t provide such option to connect Oracle. Here we require Visual Studio 2010 to create BDC model which connects Oracle. Here are the steps,

  • Create new project selecting Business Data Connectivity Model template .

  • Provide location of your SharePoint server

  • Rename the entity model with appropriate name ,

  • Open Solution Explorer and rename ( optional ) Entity.cs to Training.cs and add your properties ,

  • In ReadItem section, Modify identifier1 to DOCID and update returnParameter section with actual properties added in above step. Make sure to update Name and TypeName properties as per Training.cs.

  • In ReadList section, Remove existing Entity1 section and copy ReadItemEntity section from ReadItem section.

  • Make sure to change the Identifier1 to DOCID, here is my complete BDC model

  • Open Solution Explorer and add Oracle.DataAccess dll in References.

  • Open TrainingEntityService.cs file and add your code for ReadItem and ReadList functions . Here is my code for the same,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;

namespace BdcTrainingModel.BdcModel1
{
    /// <summary>
    /// All the methods for retrieving, 
    ///updating and deleting data are implemented in this class file.
    /// The samples below show the finder and specific finder method for Entity1.
    /// </summary>
    public class TrainingEntityService
    {
        /// <summary>
        /// This is a sample specific finder method for Entity1.
        /// If you want to delete or rename the method think 
        ///about changing the xml in the BDC model file as well.
        /// </summary>
        /// <param name="id"></param>
        /// <returns>Entity1</returns>
        public static Training ReadItem(string id)
        {
            Training evt = null;
            string connectionstr= "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
                                  (HOST=server_name)(PORT=1569))
                                  (CONNECT_DATA=(SERVICE_NAME=service_name)));
                                  user id=user;password=password;";
            OracleConnection con = new OracleConnection(connectionstr);

            string sqlQuery = "SELECT * FROM T_TRAINING WHERE DOCID='"+id+"'";

            try
            {
                evt = new Training();
                OracleCommand thisCommand = new OracleCommand(sqlQuery, con);
                con.Open();
                thisCommand.ExecuteNonQuery();

                using (OracleDataReader thisReader = thisCommand.ExecuteReader())
                {
                    if (thisReader.Read())
                    {
                        evt.DOCID = thisReader.GetValue
                                  (thisReader.GetOrdinal("DOCID")).ToString();
                        evt.DOCAUTHOR = thisReader.GetValue
                                  (thisReader.GetOrdinal("DOCAUTHOR")).ToString();
                        evt.RKPI_CONDUCTEDLEDBY = thisReader.GetValue
                                  (thisReader.GetOrdinal("RKPI_CONDUCTEDLEDBY")).ToString();
                        evt.RKPI_TYPE = thisReader.GetValue
                                  (thisReader.GetOrdinal("RKPI_TYPE")).ToString();
                        evt.RKPI_SUMMARY = thisReader.GetValue
                                  (thisReader.GetOrdinal("RKPI_SUMMARY")).ToString();
                        evt.RKPI_DATE = DateTime.Parse(thisReader.GetValue
                                  (thisReader.GetOrdinal("RKPI_DATE")).ToString());
                    }

                }

                return (evt);
            }
            catch (Exception ex)
            {
                evt.DOCID = id;
                evt.DOCAUTHOR = ex.Message;
                evt.RKPI_TYPE = ex.Message;
                evt.RKPI_SUMMARY = ex.Message;
                evt.RKPI_DATE = DateTime.MinValue;
                evt.RKPI_CONDUCTEDLEDBY = sqlQuery;
                return (evt);
            }
            finally
            {
                con.Dispose();
            }
        }
        /// <summary>
        /// This is a sample finder method for Entity1.
        /// If you want to delete or rename the method think
      /// about changing the xml in the BDC model file as well.
        /// </summary>
        /// <returns>IEnumerable of Entities</returns>
        public static IEnumerable<Training> ReadList()
        {
            string connectionstr= "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
                                  (HOST=server_name)(PORT=1569))
                                  (CONNECT_DATA=(SERVICE_NAME=service_name)));
                                  user id=user;password=password;";

            OracleConnection con = new OracleConnection(connectionstr);

            string sqlQuery = "SELECT * FROM T_TRAINING";

            List<Training> allEvents;
            try
            {
                allEvents = new List<Training>();
                OracleCommand thisCommand = new OracleCommand(sqlQuery, con);
                con.Open();
                thisCommand.ExecuteNonQuery();

                using (OracleDataReader thisReader = thisCommand.ExecuteReader())
                {
                    // Always call Read before accessing data.
                    while (thisReader.Read())
                    {
                        Training evt = new Training();

                        evt.DOCID = thisReader.GetValue
                                   (thisReader.GetOrdinal("DOCID")).ToString();
                        evt.DOCAUTHOR = thisReader.GetValue
                                   (thisReader.GetOrdinal("DOCAUTHOR")).ToString();
                        evt.RKPI_CONDUCTEDLEDBY = thisReader.GetValue
                                   (thisReader.GetOrdinal("RKPI_CONDUCTEDLEDBY")).ToString();
                        evt.RKPI_TYPE = thisReader.GetValue
                                   (thisReader.GetOrdinal("RKPI_TYPE")).ToString();
                        evt.RKPI_SUMMARY = thisReader.GetValue
                                   (thisReader.GetOrdinal("RKPI_SUMMARY")).ToString();
                        evt.RKPI_DATE = DateTime.Parse(thisReader.GetValue
                                    (thisReader.GetOrdinal("RKPI_DATE")).ToString());
                        allEvents.Add(evt);
                    }
                }

                //thisReader.Close();

                Training[] eventList = new Training[allEvents.Count];
                for (int evtCounter = 0;
                  evtCounter <= allEvents.Count - 1;
                  evtCounter++)
                {
                    eventList[evtCounter] = allEvents[evtCounter];
                }
                return (eventList);
            }
            catch (Exception ex)
            {
                Training[] errEventList = new Training[1];

                Training errEvt = new Training();
                errEvt.DOCID = ex.Message;
                errEvt.DOCAUTHOR = ex.Message;
                errEvt.RKPI_TYPE = ex.Message;
                errEvt.RKPI_SUMMARY = ex.Message;
                errEvt.RKPI_CONDUCTEDLEDBY = ex.Message;
                errEvt.RKPI_DATE = DateTime.MinValue;
                errEventList[0] = errEvt;

                return (errEventList);
            }
            finally
            {
                con.Dispose();
            }
        }
    }
}
  • Build and deploy the project . If you get following error while deployment ,

Error occurred in deployment step ‘Add Solution’: The default web application could not be determined. Set the SiteUrl property in feature BdcTrainingModel_Feature1 to the URL of the desired site and retry activation.

Add following line in Feature1.Template.xml file

 <Property Key=’SiteUrl’ Value=’your_site_url’/>

  • Once successfully deployed, Go to SharePoint central administration and set required permissions for your newly deployed Business Data Connectivity Service Application

  • Finally create new External list and verify the solution . Here is the result,

You may notice Edit item button is disabled, It’s because I haven’t added any Update method. I will add create,update and delete methods in next part of this series.

Drop me an e-mail if anyone is interested in project source files.