Welcome to MSDN Blogs Sign in | Join | Help

Error Synching BCS External list to Outlook or WorkSpace

"The server must be running MOSS2010 to synchronize external list with SharePoint workspace"  

 

I have faced this issue both in Escrow and Beta builds so thought to share it with others to make it easier. If you get the error message or having issues while synching your external list to client tools like WorkSpace or Outlook, then you might need this fix.

 

This issue prevents connecting external lists to Office client applications on x64 client machines where VS2010 Beta 2 is installed. The issue is due to the mismatching versions of SQLCE installed by Office and VS2010. Office installs x64 and x86 SQLCE and VS2010 upgrades the x86 SQLCE to a later version however it doesn’t upgrade x64 version. When the client machine is in this state, you cannot connect external lists to Office client applications. To resolve the issue, x64 SQLCE should be upgraded to the same version by downloading and installing SSCERuntime-ENU-x64.msi from http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=411ba1c5-ba57-45b6-9148-91bed6e7a9f1#tm.

 

Most probably this issue should be gone with the later builds of VS2010.

 

Provision an External List using feature (BCS)

Eric shared the xml schema to provision an external list using features. I believe everyone will find it useful so sharing it.

           <?xml version="1.0" encoding="utf-8"?>

            <Elements xmlns="http://schemas.microsoft.com/sharepoint/">

              <ListInstance Title="title of list"

                            OnQuickLaunch="TRUE"

                            TemplateType="104"

                            FeatureId="SharePointFeatureId"

                            Url="lists/Url"

                            Description="Description">

                <DataSource>

                  <Property Name="LobSystemInstance" Value="LobSystemInstance" />

                  <Property Name="EntityNamespace" Value="EntityNamespace" />

                  <Property Name="Entity" Value="EntityName" />

                  <Property Name="SpecificFinder" Value="SpecificFinder" />

                  <Property Name="Finder" Value="Finder" />

                </DataSource>

              </ListInstance>

            </Elements>

 

Client SSO in BCS

As all of you guys must have seen by now that BCS let's you use BCS OM from client Office applications like Outlook/SharePoint Workspace etc. One interesting scenario which comes up is how things will behave when external system will be accessed using SSO( or SSS in SharePoint2010) service. I am sharing my findings based on a test SSO implementation and inputs from Product team.

In the new architecture, when you synchronize the list to client computer using browser, Office client applications leverage the Windows Credential Manager available on client to store the credentials used to access the External System. During synchronization, if it finds based on metadata that the BCS ECT is using Single Sign-On feature, it pops up the dialog for end user to provide credentails to connect to External System. It then stores the credentials in Credential Manager Secure Store and every time application need to do a SSO to the external system, it reads the credentials from store and connect to external system using SSO.

This helps to securely store the credentails using built-in features of Windows OS and also provides interesting scneario where each user can provide his/her own credentials to connect to external system.

Windows Credential Manager can be accessed from Control Panel->User Accounts->CredentialManager on Windows 7.

Dynamically changing master pages in SharePoint Server 2007

Summary: Learn how to dynamically change master pages in a SharePoint publishing site on the fly.

Introduction to Master Pages in SharePoint Server 2007

Microsoft Office SharePoint Server 2007 provides flexible support for publishing sites and features for creating master pages, content types and content pages using object model and declarative syntax. Many organizations require the flexibility to change the master page in same site based on the profile of the user who is connecting. Out of the box, SharePoint provides facility to specify a single default master page for the site which is used by all the page layouts.

This article will provide guidance around how to change the master page for each content page based on target master page specified in the content page. This will allow end users to select the master page while making the pages. Using similar concept users can implement logic for changing master page on each request.

Steps for implementing

  • Create a Publishing Site
  • Create Site Column This site column will be used to store the name of the master page which needs to be applied to the appropriate page. This will be of type choice and users will select the master page while creating a new page.
  • Create Content Type This will be used to create the layout pages. The content type will contain the site column created in previous step.
  • Create Page layout base class The class coded in .NET C# language will implement the logic to change the master page in OnInit event of the PublishingPageLayout class
  • Create Page Layouts derived from Page Layout base class Actual Page layouts on which the content pages will be created
  • Creating pages based on the page layout In this step actual pages will be created and  master pages specified for them

To create a new Publishing Site

1. Using SharePoint 3.0 Central Administration, click Application Management.

2. In the SharePoint Web Application Management section, choose Create or extend Web application.

3. On the Create or extended Web application page, click Create a new web application.

4. Enter the configuration options that you want for IIS Web Site, Security Configuration, Load Balanced URL, Application Pool, and Database Name and Authentication.

5. Click OK, and then wait while the new Web application is created.

6. On the Application Created page, click Create Site Collection.

7. On the Create Site Collection page, type a Name and Description and select the root path (/).

8. Within the Template Selection section, select the Publishing Portal template from the Publishing tab.

9. Enter a Site Collection Administrator, and then click OK to create the site collection.

To create a Site Column

1.     Navigate to the site collection created above. Choose Site Actions dropdown, select Site Settings and from the fly-out select Modify All Site Settings

2.     In Site Settings page, Select Site columns under the section Galleries

3.     In Site Column Gallery page, select the Create option from toolbar

4.     Create a site column with name MasterPageName and of type choice. Provide following choices in the dropdown:

a.     BlueBand.master

b.     BlackBand.master

Users will be able to choose out of these two master pages while creating the site.

5.     Use OK button to create the site column

To create a Site Content Type

1.     Navigate to the site collection created above. Choose Site Actions dropdown, select Site Settings and from the fly-out select Modify All Site Settings

2.     In Site Settings page, Select Site content types under the section Galleries

3.     In Site Content Type Gallery page, select the Create option from toolbar

4.     Create a site content type with name CustomMasterPageContentType. In Select parent content type from: dropdown, choose Publishing Content Types. In Parent Content Type: dropdown, choose Page Layout

5.     Use OK button to create the site content type

Add Site column to Content Type

1. In the Site Content Type information of CustomMasterPageContentType, choose the option Add from existing site columns

2. Select MasterPageName from Available columns and add to Columns to add

3. Select OK to add the column

Create Page Layout base class

1. Create a class library project in Visual Studio with name DynamicMasterPageLayout.

2. Create a class with name BasePageLayout and copy the following code.

namespace DynamicMasterPageLayout

{

    using System;

    using Microsoft.SharePoint;

    using Microsoft.SharePoint.Publishing;

 

    /// <summary>

    /// Custom Page Layout class from which all the page layouts should derive.

    /// This class will take care of changing the masterpage. The master page name

    /// is read from the site column which is used to create the site content type.

    /// The site content type is used to create the page layout.

    /// </summary>

    public class BasePageLayout : PublishingLayoutPage

    {

        /// <summary>

        /// Name of the site column which contains the master page name

        /// </summary>

        private const string MasterPageSiteColumn = "MasterPageName";

 

        /// <summary>

        /// master page location directory

        /// </summary>

        private const string MasterPageDirectory = "/_catalogs/masterpage/";

 

        /// <summary>

        /// In PreInit, get the context of the page which is getting rendered,

        /// containing the master page name and set that as the master page.

       /// read the column

        /// </summary>

        /// <param name="e"></param>

        protected override void OnPreInit(EventArgs e)

        {

            base.OnPreInit(e);

 

            SPContext current = SPContext.Current;

            if (current != null)

            {

                if (current.ListItem != null &&

current.ListItem[MasterPageSiteColumn] != null)

                {

this.MasterPageFile = MasterPageDirectory +

       current.ListItem[MasterPageSiteColumn].ToString();

                }

            }

        }

    }

}   

The OnPreInit method has been overridden to change the master page. This gives a chance to set the custom master page which overrides the setting set in Master Page settings of the site.

3. The SPContext.Current.ListItem gives access to the instance of the publishing content page which is based on this Page Layout. The master page column value is read to find the value of Master Page selected for this page. This page is then set as the master page for this request.

Compile and copy the dll into the bin directory of the website hosting the site collection, path for which should be of format c:/InetPub/wwwroot/wss/<website name>/bin

Add the following safe control entry to the web.config of the website. It will treat the custom class as safe control.

<SafeControl Assembly="DynamicMasterPageLayout, Version=1.0.0.0, Culture=neutral" Namespace="DynamicMasterPageLayout" TypeName="BasePageLayout" Safe="True"  />

In this sample, extra privileges will be required to change the master page; you should change the trust level to full in web.config

<trust level="Full" originUrl="" />

Create Page Layouts derived from Page Layout base class

1.  Use SharePoint Designer/ Visual Studio to create the Page Layout with name CustomMasterPageLayout.aspx

2.  Copy the following code in the file

<%@ Page language="C#"   Inherits="DynamicMasterPageLayout.BasePageLayout" %>

<%@ Register Tagprefix="SharePointWebControls" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="PublishingWebControls" Namespace="Microsoft.SharePoint.Publishing.WebControls" Assembly="Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<%@ Register Tagprefix="PublishingNavigation" Namespace="Microsoft.SharePoint.Publishing.Navigation" Assembly="Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>

<asp:Content ContentPlaceholderID="PlaceHolderPageTitle" runat="server">

      <SharePointWebControls:FieldValue id="PageTitle" FieldName="Title" runat="server"/>

</asp:Content>

<asp:Content ContentPlaceholderID="PlaceHolderMain" runat="server">

<SharePointWebControls:DropDownChoiceField FieldName="MasterPageName" runat="server"></SharePointWebControls:DropDownChoiceField>

</asp:Content>

3.  Notice the Inherits from attribute in the code. The page layout now derives from our custom page layouts base class instead of default PublishingLayoutBase class.

4.  The MasterPagename column has been dropped on the page, which will let user select the master page while creating the actual page. It's not hidden and will show on actual page when user will view this page. But this can be configured so that it's not shown to end users and only shown in edit mode.

5.  In Site Settings page, Select Master pages and page layouts under the section Galleries

6.  Use the Upload option to upload the page layout. Associate it to CustomMasterPageContentType as shown below.

7.  Check-in and publish the page so that it can be used by end users to create new pages.

Create Content Pages based on Page Layout

1. Choose Site Actions dropdown, select Create Page

2. In Title specify the name as BlueBand. This page will use the blueband.master page as master page.

3. Select CustomMasterPageLayout.aspx as the page layout.

4. Select Create button to create the page

5. In the page which got create in previous step, from the master page dropdown field, choose the option BlueBand.master

6. Submit the page for approval, approve and page is ready for viewing.

Repeat step 1-5 above to create another page which will be using the BlackBand.master as the master page. In step 2, replace BlackBand as page title and in step 4, choose the option BlackBand.master

Test the solution

1. Navigate to the page Pages/Blueband.aspx. You will observe blueband master page styles applied to the page

2. Navigate to the page Pages/Blackband.aspx. You will observe blackband master page styles applied to the page

Conclusion

SharePoint Server publishing provides great flexibility and extensible points for end users to extend the out of box features provided by product and implement different master pages for different pages in the same site based on the requested page. Many more such scenarios can be realized based on user profile to change the UI dynamically.

Consuming External data using SharePoint 2010 Business Connectivity Services(BCS) and Office Excel 2010 Add-In

In this walkthrough you will learn how to use Sharepoint Server 2010 Business Connectivity Services(BCS) feature to access external business data using Microsoft Excel 2010 as a client. This simple step-by-step will help you understand how to setup a BCS External list and then interact with it using browser or new OM available via Office clients. The code is not production ready and some changes may be required. I wrote this as a proof of concept while learning the client OM and have started liking this feature. I was talking to Arpan sometime back and he was very excited about this feature and his enthusiasm forced me to explore it. I am sure many products inside MS and from other vendors will start using the client OM support including caching and clubbed with Secure credentials storage on the client to make compelling applications.

Introduction

BCS is the new version of MOSS 2007 Business Data Catalog functionality. New features have been added which helps to interact with external data using rich interface provided by Microsoft Office 2010 client applications like OutLook, Access, SharePoint Workspace and Excel. This article demonstrates the client object model of BCS to consume External Data using Excel 2010 Add-In. After reading this, readers will understand how to set up metadata on the server to consume external data and show in Excel 2010 using an add-in.

Applies to: Microsoft SharePoint Server 2010 Beta 1, Microsoft Office Excel 2010 Beta 1

Contents

Overview

Set up Server Environment

Create Model using SharePoint Designer

Define Operations on External System

Create External List based on External Content Type

Synchronize List using SharePoint Workspace 2010

Create Excel Add-In

Deploy and Test the solution

Additional Resources

Overview

In this scenario, Adventure Works (AW) database hosted in SQL Server 2008 is used as an External System. An application definition is created using SharePoint Designer 2010(SPD) which include features to create External Content Type (ECT) metadata model. Function calls to the backend system are modeled as an xml and imported into Business Data Connectivity Service metadata store. A SharePoint external list is created based on ECT defined in application metadata. This list is synchronized to the client machine using SharePoint Workspace 2010.  Workspace provides features to connect to a list and take content offline. On client side, Excel add-in is created which uses the BCS client object model to execute methods to get data from external system or read from cache available on client.

Set up Server Environment

Software Requirements

The list of software required to run this scenario is as follows:

1. Microsoft SQL Server 2008 x64

2. Microsoft SharePoint Server 2010 Beta1

3. Microsoft SharePoint Designer 2010

4. Microsoft SharePoint Workspace 2010

5. Microsoft Office Excel 2010

6. Microsoft Visual Studio 2010 Beta1

Create AdventureWorks Database

AdventureWorks sample database available on Codeplex site is used to represent the external system. x64 version can be downloaded from link below. Run the setup to create the database.

http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx?ReleaseId=18407

Create Team site to host External List

1. Create a web application with name intranet.contoso.com to host the site. It can be any URL based on your environment. The steps will have to be altered based on URL you choose for the site.

2. Create a Site Collection based on Team Site template.

Create Model using SharePoint Designer

SPD includes functionality to design the application definition model visually. Based on the options selected on UI, it generates the xml metadata in the background. Using ECT Designer in SPD you can discover database, point to the table, view, or stored procedure that will perform the operations, and then return the required data and use it to create external content type without writing any code or XML. Follow the steps below to create the ECT:

Define the general ECT settings

1. On the client computer, start SharePoint Designer, and open the site you created above.

2. To create an ECT, start by clicking Entities.

3. On Ribbon, click the External Content Type button, as shown in figure 1.

Fig 1 - External Content Type List

4. On New External Content Type page, specify the Name, Display Name, and the Office Item Type, as shown in the figure 2.

Fig 2 - External Content Type Properties

The Office Item Type selected determines the Outlook behavior you want to attach to the ECT. For e.g., if the behavior selected is of type contact, the record will show up as a contact while viewed in Outlook. The Offline Sync for external list field determines if lists based on this ECT can be taken offline in Microsoft Outlook or SharePoint Workspace. Accept the default Enabled setting.

5. Click on the link Click here to discover external data sources and define operations. This will open up the windows to define the connection to AW database and operations for the ECT.

6. Click Add Connection under External Data Source section and choose Data Source Type as SQL Server. This brings up the SQL connection properties dialog. In this we are connecting using SQL Server provider to get data. Provide connection details as shown in Figure 3. Since the environment on which the sample was created had WFE and database on same machine, we used localhost as server name. Press OK to create the connection. You will see AdventureWorks2008DB connection under the Data Explorer section.

Fig 3 - SQL Server Connection Details

Define Operations on External System

Once the connection is setup, operations are defined on the view which exposes sales by fiscal year for each user. You can explore the connection created in previous step. It shows database artifacts like tables, views etc. Create operations as follows:

1. We will query sales data using view vSalesPersonSalesByFiscalYears. Right click on view name to see options for creating methods as shown in Fig 4.

Fig 4 - Available operations to create using SPD

SPD provides option to create the view for all common operations available in BCS or it can create operations for specific operation.

Following two minimum operations are required to fetch data from backend using BCS:

a. Query Item List method which gets the list of records and work as finder method

b. Read Item method which gets data for specific record and work as SpecificFinder method

2. Click Create Query List operation to open the wizard to define method. Specify Operation Parameters as specified in figure 5. Click next

Fig 5 - Operation Parameters

3. Skip the step to provide filter parameters as we will not define any operation to get filtered data. Click Next. In the Return Parameter Configuration step choose the options as shown in figure below

Fig 6 - Operation Return parameters

4. Repeat step 2 and 3 to create specific finder operation named SalesPersonSalesByFiscalYearsReadItem. After this, the configuration should like figure 7, showing an entity named SalesTrend with two operations.

Fig 7 - SalesTrend ECT Definition

Create External List based on External Content Type

You can create an external content type by using Microsoft SharePoint Designer 2010 or the browser. Follow the steps given below to create list using browser.

1. Open intranet.contoso.com in browser.

2. Go to Site Actions, View All Site Content.

3. Click the Create button. In the Custom Lists section, click External List.

4. On the New page, type Sales as the list name and description for the new external list.

5. The Data source configuration section displays a text box and an external content type picker. Use the picker to choose the external content type. Select SalesTrend and then click OK.

6. Click Create.

This creates the external list. You can now navigate to the new list in the SharePoint site and view/edit items.

Synchronize List using SharePoint Workspace 2010

Rich Client Composites for SharePoint Server and Office allows you to connect to external list in SharePoint site to SPD to surface external data in SharePoint Workspace 2010. You need to first sync the list with client machine using office client tools. After this the ECT metadata model becomes available on client. BCS client object model can be used to query the data from backend system using this metadata.

1. From client computer, navigate to the Sales external list using browser.

2. To take the list offline in SPD, on the SharePoint site ribbon, go to List tab; click Sync to Computer as shown in figure 8.

Fig 8 - Sync list to client

This step will create the site structure and list on the client in SharePoint Workspace. It will also copy the metadata associated with the entity to the client BCS metadata store.

3. Open the list in Workspace to check if all the records from the view are shown as list items. A detail about each selected record is shown below the list. Users can work on this data when they are offline and changes made to the data are synchronized back to the backend system if update operations are defined in ECT.

Create Excel Add-In

  1. Open Visual Studio 2010, select New Project and choose Excel 2007 Add-In template under Office Project Types
  2. Right click SalesFromECTAddin project in solution explorer, select Add, New Item, and select Ribbon (XML) as template for creating the ribbon. Name the file as SalesRibbon.cs
  3. Remove the auto-generated code and add the following xml in the SalesRibbon.xml file generated by Visual Studio. This XML adds a tab with label BCS, changes the label of the default control group to Sales and adds a button with label Show Sales Data. OnAction method of the SalesRibbon class is called when the button is clicked. The logic in which fetches the data from external system.

 

<?xml version="1.0" encoding="UTF-8"?>

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

  <ribbon>

    <tabs>

      <tab idMso="TabAddIns" label="BCS">

        <group id="ctlGroup" label="Sales">

          <button id="btnFetch" label="Show Sales Data" visible="true" imageMso="SharePointListsWorkOffline" size="large" onAction="OnAction" />

        </group>

      </tab>

    </tabs>

  </ribbon>

</customUI>

For step by step to create custom tab using Ribbon XML refer to the following link:http://msdn.microsoft.com/en-us/library/aa942955(VS.100).aspx

4. Remove the auto-generated code and add the following code in SalesRibbon.cs. OnAction method implements logic for creating instance of Sales class and showing data in excel sheet.

 

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Reflection;

using System.Runtime.InteropServices;

using System.Text;

using Office = Microsoft.Office.Core;

 

namespace SalesFromECTAddin

{

    [ComVisible(true)]

    public class SalesRibbon : Office.IRibbonExtensibility

    {

        private Office.IRibbonUI ribbon;

 

        public SalesRibbon()

        {

        }

 

        #region IRibbonExtensibility Members

 

        public string GetCustomUI(string ribbonID)

        {

            return GetResourceText("SalesFromECTAddin.SalesRibbon.xml");

        }

 

        #endregion

 

        #region Ribbon Callbacks               

 

        public void OnAction(Office.IRibbonControl button)

        {

            Sales salesData= new Sales();

            salesData.Show();

        }

 

        #endregion

 

        #region Helpers

        private static string GetResourceText(string resourceName)

        {

            Assembly asm = Assembly.GetExecutingAssembly();

            string[] resourceNames = asm.GetManifestResourceNames();

            for (int i = 0; i < resourceNames.Length; ++i)

            {

                if (string.Compare(resourceName, resourceNames[i],

                    StringComparison.OrdinalIgnoreCase) == 0)

                {

                    using (StreamReader resourceReader = new

                        StreamReader(                        asm.GetManifestResourceStream(resourceNames[i]))

                        )

                    {

                        if (resourceReader != null)

                        {

                            return resourceReader.ReadToEnd();

                        }

                    }

                }

            }

            return null;

        }

        #endregion

    }

}

5. Copy the following code in ThisAddIn.cs file. This code overrides the CreateRibbonExtensibilityObject method and returns the Ribbon XML class to the Office Excel application.

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Xml.Linq;

using Excel = Microsoft.Office.Interop.Excel;

using Office = Microsoft.Office.Core;

using Microsoft.Office.Tools.Excel;

using Microsoft.Office.Tools.Excel.Extensions;

 

namespace SalesFromECTAddin

{

    /// <summary>

    /// Add-In entry point class

    /// </summary>

    public partial class ThisAddIn

    {

        private void ThisAddIn_Startup(object sender,

System.EventArgs e)

        {

        }

 

        private void ThisAddIn_Shutdown(object sender,

System.EventArgs e)

        {

        }

 

        protected override Microsoft.Office.Core.IRibbonExtensibility

            CreateRibbonExtensibilityObject()

        {

            return new SalesRibbon();

        }

 

        #region VSTO generated code

 

        /// <summary>

        /// Required method for Designer support - do not modify

        /// the contents of this method with the code editor.

        /// </summary>

        private void InternalStartup()

        {

            this.Startup += new System.EventHandler(ThisAddIn_Startup);

            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);

        }       

        #endregion

    }

}

6. Create a new class called Sales and copy the following code in the file. Business Connectivity Services (BCS) uses a cache to store a copy of the external data that is synchronized to the client. The cache enables external data to be automatically copied on the client and managed for real-time access, with automatic data synchronization between the cache and the external application.The client cache is implemented as a per-user Microsoft SQL Server Compact database, which is encrypted with Encrypting File System (EFS). In this walkthrough, we will work online and execute method directly against the external system and will not use the cached(offline) copy. The last parameter OperationMode in api call entity.FindFiltered(in code below) determines if the call is made online or to the offline copy of data.

 

using System;

using System.Collections.Generic;

using System.Data;

using System.Windows.Forms;

using Microsoft.BusinessData.MetadataModel.Collections;

using Microsoft.Office.BusinessData.MetadataModel;

using Microsoft.BusinessData.MetadataModel;

using Microsoft.BusinessData.Runtime;

using Microsoft.Office.Interop.Excel;

 

namespace SalesFromECTAddin

{

    /// <summary>

    /// Connects to backend using BCS OM and fetches the data

    /// </summary>

    public  class Sales   {

        public Sales()

        {           

        }

 

        /// <summary>

        /// Executes the method to refresh data

        /// </summary>

        public void Show()

        {

            GetDataFromExternalSystem();

        }

 

        /// <summary>

        /// Reads the metadata from client store and executes Finder

        /// method against AdventureWorks DB using BCS client OM

        /// </summary>

        private void GetDataFromExternalSystem()

        {

            // Open the shared metadatacatalog to read LOB metadata

            // information. This class represents the Office clients

            // shared store for LOB metadata. Once on client it can be

            // accessed from any Office client app

            RemoteSharedFileBackedMetadataCatalog catalog = new

                RemoteSharedFileBackedMetadataCatalog();

 

            // Get all the LOB Systems in the catalog

            INamedLobSystemDictionary lobs = catalog.GetLobSystems("*");

 

            // Pick up our AdventureWorks app def from LOB system

            ILobSystem lob = lobs["AdventureWorks2008DB"];           

 

            // Find all the instances of AdventureWorks app def.

            // In this case it's only going to be one.

            INamedLobSystemInstanceDictionary lobInstances =

                lob.GetLobSystemInstances();           

            ILobSystemInstance instance = lobInstances[0].Value;           

 

            // Find all entities defined in the LOB instance

            INamespacedEntityDictionaryDictionary entities =

                instance.GetEntities();

 

            // Pick up the first entity definition.

            // It represents SalesTrend ECT              

            IEnumerator<IEntity> enumerator = entities.GetEnumerator();

            enumerator.MoveNext();

            IEntity entity = enumerator.Current;           

 

            // Get the Finder method instances

            INamedMethodInstanceDictionary methodInstances =

                entity.GetMethodInstances(MethodInstanceType.Finder);

 

            // Get the view for the Finder method

            IView vw = entity.GetFinderView(methodInstances[0].Key);

           

            // Create definition for data table to hold backend data

            System.Data.DataTable dt = new System.Data.DataTable();

            for (int count = 0; count < vw.Fields.Count; count++)

            {

                dt.Columns.Add(vw.Fields[count].TypeDescriptor.Name);

            }

 

 

            IFilterCollection fc = entity.GetDefaultFinderFilters();            

            // Execute Finder method to get data from backend.

            // Operation Mode is online, hence data will be fetched

            // from backend system and not read from cache

            IEntityInstanceEnumerator entitiesList =

                entity.FindFiltered(fc,

                methodInstances[0].Value.Name,

                instance,

                OperationMode.Online);

 

            IFieldCollection flds = vw.Fields;

            // Populate the data table

            while (entitiesList.MoveNext())

            {

                DataRow row = dt.NewRow();

                foreach (IField fld in flds)

                {

                    row[fld.Name] = entitiesList.Current[fld.Name];

                }

                dt.Rows.Add(row);

            }

 

            // Refresh excel sheet

            RefreshSheet(dt,vw);

        }

 

        /// <summary>

        /// Read the datatable and refreshes the Excel Sheet

        /// </summary>

        /// <param name="dt"></param>

        /// <param name="vw"></param>

        private void RefreshSheet(System.Data.DataTable dt, IView vw)

        {

            try

            {

                Worksheet xlSheet =

                    ((Worksheet)Globals.ThisAddIn.Application.

                    ActiveWorkbook.Worksheets["Sheet1"]);

 

                xlSheet.Cells.Clear();

 

                int numFlds = vw.Fields.Count;

                IFieldCollection flds = vw.Fields;

 

                int colCounter=0;

                // Render headers

                foreach (IField fld in flds)

                {

                    colCounter++;

                    xlSheet.Cells[1, colCounter] = fld.Name;                   

                }

                // Render data rows

                for (int rowCounter = 0; rowCounter < dt.Rows.Count;

                    rowCounter++)

                {

                    colCounter=0;

                    while(colCounter<numFlds)

                    {

                        xlSheet.Cells[rowCounter + 2, colCounter+1] =

                            dt.Rows[rowCounter][colCounter];

                        colCounter++;

                    }                   

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show(ex.StackTrace);

            }

        }

    }

}          

7. Compile this source and see if build is successful. Next task is to create a clickonce setup package to install the add-in. This is very easy and can be done in Visual Studio itself. Right click the project in solution explorer and choose publish option. This will bring up the dialog as shown in figure 9. Choose the location where you want to create the vsto package and select Finish. This will create the package at the specified location.

Fig 9 - Publishing Wizard

Deploy and Test the solution

  1. Double click the .vsto file created in the package above to install the add-in. The installer will run the appropriate customizations and install the add-in.
  2. Open the Excel application and you should see the tab with label BCS in the ribbon. Select the newly created tab and you will see a button Get Sales Data in the ribbon. This shows that our solution has successfully installed on the client.
  3. Click the button to execute Finder method on the ECT. The client object model executes the call against the external system since we choose the operationmode as online in the code. Code will fetch the sales information and update the sheet1 of excel file with data as shown in Fig 10. This successfully demonstrates the BCS features on the client.

Fig 10 - External data in Excel file

Conclusion

The new BCS feature in SharePoint 2010 provides numerous features and possibilities to consume data from external system and use in offline and connected fashion. Add-ins, task panes and other Office extensibility features can be created which can help interact with the external data using the user friendly Office Client applications.

Additional Resources

For more information, see the following resources:

 

SharePoint 2010 Introduced at SPC09

The most exciting release in SharePoint history has been introduced to the general community at SharePoint Conference 2009. The product has host of new features around scalability, maintainability, social and all and all a great enterprise product. Sneak peak vidoes give good overview about the investments in Developer and Infrastructure space. There are many features which I wanted to write on but I am not sure what's under NDA so just highlighting few which are applicable to any SharePoint deployment. They are:

  1. Developer Dashboard - Interactive way in which develoeprs can see what's happening on their pages. Why they are slow and they can get a trace on page itself about the time taken by different webparts to render themselves. Support has been added in base classes for spitting out this information during different events in page lifecycle. This will help to easily isloate the problem areas for slow rendering pages.
  2. Powershell support - PS is now first class citizen for managing the SharePoint Farms. Lot of scripts have been written by the Product team to automate the management of various activities in a farm.
  3. Scalability aspects - Service model has been introduced for various services which were earlier part of SSP. Now each service can be hosted individaully and managed in individual databases. This help in isolating the content, manage backup/restores and overall make the system robust
  4. Logging - New concept of co-relation guids have been introduced which log each message for a call in logs with a guid that can be used to correlate the messagge with the error. The corelation guid is shown on page when error occurs. This can be used to find the related messages.
  5. Business Connectivity Services (BCS) - This one is most exciting feature in v4. All those who have worked with BDC in MOSS2007 have enjoyed the capabilities of the integeration feature for fetching data and for search. This version renamed to BCS goes a step further and provide symmetric programming model on client as well. What this means is that you can connect to LOB systems using the BCS Api's even from client software like Outlook, SharePoint Worksapce, Excel etc. You have read/write capabilities and there are far more connectors now including new addition of WCF and .NET custom assemblies as out of box connectors. It also allows you to write your own custome connectors. That's a great capability and I am sure you will see some great apps made on this framework.

There are many more features which you will see people blogging and documented on official SharePoint sites. Get started and start playing with the bits. You can download the bits and get rolling. Here are some links to start with

Developer Content - http://msdn.microsoft.com/en-us/sharepoint/ee514561.aspx

IT Professional - http://technet.microsoft.com/en-us/default.aspx

Posted by Tajeshwar | 0 Comments
Filed under:

Introducing SharePoint 2010

Come monday and the biggest SharePoint mela is going to start in Vegas on Oct19th, the SharePoint Conference 2009. Keynote will be delivered by Steve Ballmer and one of the best releases in SharePoint history will be introduced to the general community. I have seen some feature bits and the sneak peak videos and found the whole stuff very promising.

It's about 4 years I have been playing with SharePoint, going back to 2005 when I joined Microsoft and landed in the Collaboration team. There was lot of demand for resources good in SharePoint 2003. I was always a middleware person(that's what I used to call myself) with some large engagements using BizTalk and SQL Server under my belt. Moving to SPS2003 was like moving to a UI centric product which was little difficult for me.

Now here comes the question, is SharePoint a product, server or platform? After doing many engagements and now seeeing the 3rd version rolled out, I can defintely say it's a platform. There are rules of game and to understand SharePoint you have to understand the platform. People who feel that they can master it in a day should not think that way. There are many things which you may write code for but the features may be available OOB. You just need to know know where and how.

Even after 4 years and being Microsoft Certified Master in this product, I feel there are many things I am not aware of. Daily in one or other blog, I find information which our MVP's, Masters and other top authorities find and share. It's just a great platform whiere you have amazing capabilities.

There are new feature areas where I may want to concentrate and will blog once the new version goes public beta post SPC. Just follow the blog and I will try to contribute some good stuff.

Meanwhile, to learn more about SharePoint history, engineering and platform, read these posts from Jeff Tepper, VP for SharePoint.

 http://blogs.msdn.com/sharepoint/archive/2009/10/05/sharepoint-history.aspx

 

 

 

 

 

Posted by Tajeshwar | 0 Comments

SharePoint 2010 Ignite Training for Partners

If you are interested in kick starting and be ahead of the curve then do attend the Ignite training happening in Banglore, India in Dec 2009. Its a deep dive into the new version of the product and some of the best instructors will be presenting. There are two tracks, one for IT Pros and others for developers. Go ahead and register!

More details at this entry by Arpan Shah.

Posted by Tajeshwar | 0 Comments

Getting the SharePoint MCM Certification

Couple of days back program manager for SharePoint MCM did a post on master blog about new Masters. It's good to see myself in that elite list of true SharePoint experts. It was a great experience attending the program which helped me know what challenges the product has and many things which I was not aware of. I had been working with the product for 4 years but still came out of training with knowledge which I was missing earlier.

Spending three weeks with smartest and brightest classmates and best SharePoint instructors was rewarding experience. It's tough for sure with 3 written exam and a cruel :-) lab. But you will enjoy each and every moment of those 3 weeks. Best SharePoint training on the planet. What the program is all about and how to enroll, fee etc can be found here.

I will strongly encourage all the SharePoint experts to take this training and certification as it will help them in their professional career and also enhance their knowledge and skills.

Tajeshwar

Problem mapping user profile properties

One of my colleague pinged me with a strange problem he was encountering in user profile property mapping. He wasn't able to add or edit user profile property import mappings because the 'Data source field to map' drop down and the 'Enter field to map' text box were missing from the 'User Profile Property' page in SSP. The page was coming without the dropdowns and showing 'The selection of directory service properties is disabled because the portal is in an untrusted domain or no directory service import is configured yet';

I initially thought it to be some issue with security permissions on reading some specific AD attributes. But that was not the case as he has succesfully imported the profiles from AD. Later on it was realized that the account under which he was running the SSP was a local box account and not a domain account. While importing profile he was prompted for domain account for importing profiles, that's why he was able to succesfully import the profiles. But when the profile property page tried to connect using the SSP account, it failed and hence disabled the drop downs.

So if you are facing this issue, just make sure that the account used for import has read access to the directory store which generally the domain accounts will have.

Posted by Tajeshwar | 0 Comments
Filed under: ,

Update on FBA support with Office Client

If you have worked with SharePoint FBA and have seen the issues with Office client, there is some good news for you. In a new patch for client there is support added to make the client work properly with SharePoint and provide better user experience. Steve, the Master Ranger has blogged the process very nicely in sharepoint team blog here

Posted by Tajeshwar | 0 Comments
Filed under: ,

BlobCache Demystified

Just read Stefan's post on blob caching. Very interesting to know that it's not a publishing only feature. Other site types like team sites can also use it. This can help to squeeze out some extra perf benefit from these site types.

Posted by Tajeshwar | 0 Comments
Filed under: ,

Updating Content Types Custom Edit, View Displayforms

I spent whole day yesterday to update the custom edit forms for a content type. Well, I was writing a workflow and wanted to write custom UI for my tasks editing. There are couple of ways, using aspx forms or InfoPath. I tried to go via aspx route. To do it via aspx you need to basically create a content type for your task type and create the edit pages for the content type. When you click the task for editing, SharePoint opens the edit form for the content type. Neat! The process is well documented here.

Now the problem I was facing was that even though I specified a new task edit form aspx page, on clicking the edit link I was still getting the OOB dispform.aspx page while viewing the task. This was weird as I tried all different options including deactivate and uninstalling feature. I wrote a small console app to loop through the SPContentType and see whats the props set in that. It showed that the props for editformurl and displayformurl props are still having empty string as their value. I just wrote following lines of code to update them to the urls for my custom forms and updated the content type. And it all started working fine

SPContentType ct=web.Lists["ListName"].ContentTypes["ContentTypeName"];

ct.EditFormUrl="_layouts/youreditpage.aspx";

ct.Update();

What I interpreted is that deploying the task content type using feature is not properly associating the formurls with the contenttype properties. 

Posted by Tajeshwar | 1 Comments

SharePoint Exams..Passed!

Just passed the two new SharePoint IT Pro exams... 70-630 WSS 3.0 Configuring and 70-631 MOSS 2007 "Configuring" Technology Specialist (TS) exams.

It was a productive week at TR4. Since I was a speaker also so it was a little busy for me to prepare for the exams. But I really enjoyed the exams and fortunately was able to pass both of them. Though I found WSS a little tough compared to MOSS because of lot of deployment questions.

Tajeshwar Singh

MCSD.Net, MCTS: (WSS 3.0 Configuring, MOSS 2007 Configuring)

Posted by Tajeshwar | 0 Comments
Filed under: ,

Extending Stsadm Command

 

While going through the WSS v3 Object Model, I came across an important interface ISPStsAdmCommand. Initially, I wasn't able to find any documentation as product is about to RTM and doc is not complete. After some  research and thankfully to an excellent post by Tony Bierman at http://sharepointsolutions.blogspot.com/2006/09/extending-stsadmexe-with-custom.html, got lot of information about this Interface.

I am not going to dig much into the API as it has been well covererd by Tony's blog. At high level, this allows you to extend the operations provided by StsAdm command and use it to do common repetitive tasks like taking backups, giving rights etc.

ISPStsadmCommand has two interfaces

1. string GetHelpMessage ( string command )

which shows the help for how to use the operation.

Usage stsadm -help [operation]

2. int Run ( string command, StringDictionary keyValues, out string output )

which is called to run the logic of user implemented operation.

keyValues is the dictionary containing command line parameters and their value passed

output is of type out parameter and used to show the message to be shown on command line as a result of running the command.

 

To test and extend this interface, I created an operation to delete all Webs below a SPWeb recursively. There were some posts on forums that on deleting a child web (which is not a top level site), if it contains subwebs, deletion is not allowed. This becomes a pain as user has to delete all the subwebs individually and then only the child web can be deleted. I decided to provide this functionality as a stsadm command.

The implemented operation recursively get all the sites one by one and delete them, finally followed by the child site.

Command syntax

c:\> stsadm.exe -o DeleteWebRecursive -url http://sitename/webname

This will delete all the webs under web names "webname"

I am attaching the solution for this custom extended command without any warranties :).

Copy following content

- Copy the SiteDelete.dll present in bin directory to GAC.

- Copy the stsadmcommands.customsitedelete.xml file in root folder to "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\CONFIG" folder

 

That's all to start using the extended operation. Let me know in case of bugs or improvements.

Posted by Tajeshwar | 1 Comments
Attachment(s): CustomStsAdmCommands.zip
More Posts Next page »
 
Page view tracker