Archive for September 27, 2013

SharePoint: Create an administration page in SharePoint central administration

Custom administration pages can be used in Central Administration for a variety of purposes. One such scenario can be creating a page in CA for storing database connection string and using it in timer jobs. In this post we will create an administration page and use persisted objects to store data in SharePoint configuration database.

Create an empty SharePoint 2010 project with the following structure:

Copy this markup to the SampleCAAdminpage.aspx page.

<%@ Assembly Name="Microsoft.SharePoint.ApplicationPages.Administration, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Import Namespace="Microsoft.SharePoint.ApplicationPages" %>
<%@ 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" %>
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register TagPrefix="wssuc" TagName="InputFormSection" src="~/_controltemplates/InputFormSection.ascx" %>
<%@ Register TagPrefix="wssuc" TagName="ButtonSection" src="~/_controltemplates/ButtonSection.ascx" %>
<%@ Page Language="C#" AutoEventWireup="true" MasterPageFile="~/_admin/admin.master" Inherits="SampleCAAdminPage.Pages.SampleCAAdminPage, SampleCAAdminPage, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a6ed85d3cc39e3bd" %>

EncodeMethod='HtmlEncode' />

runat="server">
EncodeMethod='HtmlEncode' />


EncodeMethod='HtmlEncodeAllowSimpleTextFormatting' />



Title="Database Connectionstring"
Description="Identify the connection string used for accessing the the database." >







Title="SharePoint List"
Description="Identify the list for data import." >





















Change the public key token in Page directive line to your value.

<%@ Page Language="C#" AutoEventWireup="true" MasterPageFile="~/_admin/admin.master" Inherits="SampleCAAdminPage.Pages.SampleCAAdminPage, SampleCAAdminPage, Version=1.0.0.0, Culture=neutral, PublicKeyToken=a6ed85d3cc39e3bd" %> 

Use the following code in connection.cs file:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.Administration;

namespace SampleCAAdminPage.Configuration
{
public class Connection : SPPersistedObject
{
private readonly static string NAME = typeof(Connection).FullName;

[Persisted]
private string mConnectionString;

[Persisted]
private string mListName;

///
/// Initializes a new instance of the class.
///

public Connection()
: base()
{
}

///
/// Initializes a new instance of the class.
///

/// The parent.
public Connection(SPPersistedObject parent)
: base(Connection.NAME, parent)
{
}

public string ListName
{
get
{
return this.mListName;
}
set
{
this.mListName = value;
}
}

public string ConnectionString
{
get
{
return this.mConnectionString;
}
set
{
this.mConnectionString = value;
}
}

///
/// Gets the ClaimsConnection details from local.
///

public static Connection Local
{
get
{
Connection result = SPFarm.Local.GetChild(Connection.NAME);
if (null == result)
{
try
{
result = new Connection(SPFarm.Local);
result.Update();
}
catch (Exception ex)
{
return null;
}
}

return result;

}
}
}
}

Use following code in SampleCAAdminPage.cs class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint.ApplicationPages;
using System.Web.UI.WebControls;
using Microsoft.SharePoint.WebControls;
using SampleCAAdminPage.Configuration;
using Microsoft.SharePoint.Utilities;
using System.Web;
using System.Web.UI;
using System.Data.SqlClient;

namespace SampleCAAdminPage.Pages
{
public class SampleCAAdminPage : ApplicationsManagementPage
{
protected InputFormTextBox txtConnectionString;
protected InputFormTextBox txtListName;
protected Button btnConnectionTest;
protected Button btnSubmit;

protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
if (!this.IsPostBack)
{
Connection connection = Connection.Local;

this.txtConnectionString.Text = connection.ConnectionString;
this.txtListName.Text = connection.ListName;
}
}

protected void btnSubmit_Click(object sender, EventArgs e)
{
Connection connection = Connection.Local;
if (null != connection)
{
connection.ConnectionString = this.txtConnectionString.Text.Trim();
connection.ListName = this.txtListName.Text.Trim();
connection.Update();
SPUtility.Redirect(PageToRedirectOnOK, SPRedirectFlags.Static, HttpContext.Current);
}
}

protected void btnConnectionTest_Click(object sender, EventArgs e)
{
try
{
using (SqlConnection connection = new SqlConnection(this.txtConnectionString.Text))
{
connection.Open();
connection.Close();
ShowMessage(this.Page, "Connection succeeded.");
}
}
catch (Exception ex)
{
ShowMessage(this.Page, ex.Message);
}

}

public override string PageToRedirectOnCancel
{
get
{
return "/applications.aspx";
}
}

public override string PageToRedirectOnOK
{
get
{
return "/applications.aspx";
}
}

public static void ShowMessage(Page page, string text)
{
// build up javascript to inject at the tail end of the page
StringBuilder stringBuilder = new StringBuilder();

stringBuilder.AppendLine("");

// add to the page
page.Controls.Add(new LiteralControl(stringBuilder.ToString()));
}

}
}

Use follwoing xml in Elements file of CustomAction element:



Id="XYZConfiguration"
Location="Microsoft.SharePoint.Administration.Applications"
Title="XYZ Configuration"
Sequence="100"
ImageUrl="/_layouts/images/CentralAdmin_Security_GeneralSecurity_32x32.png"
/>
Id="XYZConfiguration.AdminPage"
GroupId="XYZConfiguration"
Location="Microsoft.SharePoint.Administration.Applications"
Sequence="6"
Title="Configure XYZ Project"
Description="Use this to configure XYZ Project connection options"
UIVersion="4">


Ensure that the feature has Farm level scope:

Finally deploy the solution and go to CA and click on Application management. Notice the section for configuring XYZ project.

Enter and save database connection string and list name in the page.

Inside the timer job or any SharePoint code the above saved values can be retrieved as follows:

Connection con = Connection.Local;

string connectionString = con.ConnectionString;
string listName = con.ListName;

JS Link for SharePoint 2013 Web Parts–A Quick Functional Primer

JS Link is a new web part property in many SharePoint 2013 web parts.  It allows users and developers to create Client-Side Rendering (CSR) solutions within SharePoint 2013.  In other words, it allows alternate formatting, data and functionality to be manipulated within a web part.  It is one approach that will help replace the data view web part (DVWP) functionality that was used in SharePoint 2010 and SharePoint Designer 2010.

For example, CSR scenarios can edit how specific fields are displayed, how whole web parts are formatted and what functionality is available.  And these are just a few simple examples.  There is a lot of potential for what can be done.  My session at SPC12 just scratched the surface while others dug much deeper.

Something to note is that using JS Link to implement CSR functionality is easy and reversible.  If you don’t like what it’s doing to your web part you can easily change the JS Link property back (blank it out) and your web part will go back to it’s default format.

The CSR approach is more developer-focused than power user-focused.  There are, however, a few simple things that can be done that I think will be accessible to power-users that have some comfort level with code, specifically JavaScript, HTML and CSS.

So, here are the main components you need to make CSR work with out-of-box web parts – nothing fancy – using JS Link in SharePoint 2013:

  • Create a JavaScript override file
  • Upload the JavaScript file to the Master Page Gallery
    • Set the content type and properties of the file in the Master Page Gallery
  • Set the JS Link property of the web part to point to the JavaScript file

Sounds easy enough, but there are a few details to sort out to get everything synced up.  Additional details for each step are listed below:

Create the JavaScript file 
In these examples, JavaScript is being used to override different things – how data is displayed and what content is displayed.  For starters we’ll override a specific field in a view.

During my session at the SharePoint Conference (SPC12) I used a few simple examples that are a good starting point.  EXAMPLE JS FILES

In the first example, we get everything in place and make sure that it’s working.  We start with a standard view (AllItems) that shows text fields, and then use the JS Link functionality to override a single column – displaying different text than came with the view.  We are replacing whatever the ‘MoreText’ column contained with the word ‘Animal’.

image

As the notes in the sample script mention, the Fields override works specifically with individual fields.  You can specify a single field, or multiple fields.  For each field (column) override, you need to identify the field being overridden and the replacement value.

When identifying the field name, take note that you need to use the internal name for the column, which may not be the same name that is displayed.  In some cases this is true, but not all.  See my post on finding the internal column name.

The ‘View’ component of the command is in reference to the type of template being overridden – in our case the view.  There are also options for the NewItem, EditItem and DisplayItem forms, but we won’t cover them in this article.

The last piece of the command for each field is the override value for the field.  This can be a static value – as in the first example – a variable, or HTML and the value, allowing you to style the value.  You can also call a function within the script to do more complicated logic – such as conditional formatting – but for the field.

Note: The following example code is shown with the Before and After output of the sample web part.  The changes will not go into effect until you’ve put all the pieces in place – the steps which are listed after the sample images.

Example 1:  Uses a static value to override the field value.  ‘Animal’ will replace the ‘MoreText’ field value for each list item in the view.

image

Before:

image

After:

image

Example 2:  Displays the true value of the MoreText field, but formats it (bold) by wrapping it in an HTML tag.

image

Before:

image

After:

image

Example 3:  Calls a function that formats the value that overrides the field data.  In this case it uses a conditional to determine what data is displayed.  This could just as easily be used to implement conditional formatting for the field by keeping the field data as in Example 2, but formatting it differently using HTML tagging.

image

image

Before:

image

After:

image

Example 4:  Uses a different override.  This example uses the Item override instead of the Field override.  Using the Item completely overrides the web part and requires the Header and Footer overrides as well.  In the example here, the data is displayed in an HTML list where the open tag is in the header and the closing tag is in the footer.  Each item is then an item in the list.

image

image

Before:

image

After:

image

One other note on the code.  We aren’t getting into details with these settings, but a few of the sample scripts also include settings for BaseViewID and ListTemplateType.  These settings are not required, but if you are using JS Link on a page that has more than one view, these properties control which web parts are affected by the JavaScript code.

BaseViewID is the view ID for the list with 1 being the AllItems view.

ListTemplateType is the ID for the type of list template is used such as Links List, Task List, Custom List, etc.  A list of these types in 2013 can be found here: 
http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.client.listtemplatetype(v=office.15).aspx

Upload it to the Master Page Gallery

The script files need to be uploaded to a place where they can be accessed by the web part.  Depending on what version of SharePoint you’re using and what your scope is, you will need to determine the best location for the scripts.  In our case we used Office 365 for a test bed and only want to use the scripts within our site collection, so we are uploading the files to the Master Page Gallery.

As each file is uploaded, the content type of the file will need to be changed to ‘JavaScript Display Template’ and the required fields will need to be completed.

In our examples, the Target Control Type is ‘View’.

The Standalone value is ‘Override’.

And the Target Scope is the relative URL of the site collection we’re customizing in the format: ‘/sites/d1’.

image

For our example, upload each of the 4 sample files so that you can see the different examples at work.

Add the JS Link property that points to the JS file

Finally, edit the web part properties you are trying to edit.  This may be web parts on a web part page, or a web part on a standard view page.  Edit the page, then the web part.  Expand the Miscellaneous section and edit the JS Link property using the following sample format to point to the location of the JavaScript file you are using for the CSR override:

~site/_catalogs/masterpage/csr_overridable_demo1.js

Save your changes.  If everything is working, the changes should be immediately visible.  If you still need to edit the JavaScript to match your specific column or example, edit the file using SharePoint Designer.

Edit the files using SharePoint Designer 2013

Developers have a number of code editing tools at their disposal and can use Visual Studio or others.  Non-developers can use SharePoint Designer 2013 to view and edit the JavaScript once it has been uploaded to the site.

If you haven’t already edited the files to match your specific list column names, start SharePoint Designer and open the site collection where you are doing your testing.

From SPD you can edit and save your JavaScript files live.  After you make a change to the JavaScript file, refresh the browser page and your changes should be reflected – assuming everything is correct.  Smile 

Wrap-up

Sorry for such a short and unpolished post – there is a LOT more to talk about regarding JS Link and SharePoint 2013 –  but I wanted to get this out for people that attended my #SPC064 session at #SPC12 or who started hearing the rumblings about JS Link this week while at the conference. Hopefully this allows you to get started playing around with the power of JS Link.

Lots more to come!

Notes:

  • JS Link is not only used in web parts, but can also be set and used when creating custom apps, etc.  This post is just covering the specific example when using the web part property.
  • Be sure to take a look at the HTML that is produced by the page once you have your CSR in place.  Some approaches in the CSR may not jive well with the existing page code. You  want to avoid conflicting tags, etc.
  • JS Link override will not work if the form has been edited in SPD

 

Source : http://www.idubbs.com/blog/2012/js-link-for-sharepoint-2013-web-partsa-quick-functional-primer/

SharePoint 2013 Search – How to Crawl Large External Data using BCS/WCF Service.

Scenario:

I had a hard time finding an end to end solution to this problem. We had to index external content using SharePoint search via BCS. There are plenty of blogs that described how to create an External Content Type, how to create a SharePoint WCF service, how to create a BCS Service, and how to use that connection to crawl content using SharePoint Search. The issue arose, where the number of rows returned went above some thresholds that SharePoint had when it comes to WCF Services. Say you have a data source that has millions of rows… How can you safely crawl such large data without:

  1. bringing the Search Server to a “crawl” (no pun intended).
  2. Putting undo stress on the SQL server and network lines for bringing such a large data over the wire.

More than likely you will run into error messages such as the following in the SharePoint Logs.

  • Timeout expired. BDC Runtime Object Model has throttled the response. The response from the service took more than ‘180000’ milliseconds. The limit can be changed via the “Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.
  • WCF Service Connector has throttled the response. The response from the WCF service contains more than ‘3000000’ bytes. The maximum amount of data that can be read through WCF Service Connector is ‘3000000’ bytes. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.
  • Web Service Connector has throttled the response. The response from the web service contains more than ‘3000000’ bytes. The maximum amount of data that can be read through Web Service Connector is ‘3000000’ bytes. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.
  • Opening of a new connection is throttled. Maximum enabled connections ‘200’ for proxy are already been opened.
  • Database response throttled. Maximum number of rows that can be read through the database system utility is 2000.

There were some proposed solutions, such as increasing the default throttling by using a set of PowerShell commands. However, when it comes to large data, you’re still going to run into trouble.

Solution:

Implement Paging in your WCF service.

  1. Create a Method in WCF Service to call the database using some parameter to return row limits.
  2. Create a List Read Function for the WCF using SharePoint Designer.
  3. Set a Filter for that method in SharePoint Designer.
  4. Crawl.

Create a Method in WCF Service to call the database using some parameter to return row limits.

I will not get into the details of how to create a SharePoint WCF Service. We’re going to assume you know how to do that. We’re going to implement a method in the main WCF Service Class that returns IEnumerable of type “SPCrewData” and pass a parameter named PageNum.

 

In the above mentioned method, two things need to be handled:

       1.Get the total count of the items to be returns from sql.
       2. Page through by using that number to do a comparison against in SQL Query.

 

Get the total count of the items to be returns from sql.

    To return count simply use the select COUNT(*) function in sql. If your query is fairly simple then use something like this:

   

If you have a fairly complicated sql query then simply wrap that sql statement around your complicated query and also pass a ROWNUM column by using a ROW_NUMBER() sql function

   

Page through by using that number to do a comparison against in SQL Query

Here is the complete Code. 

//This method will page through items a 1000 at a time. 

public IEnumerable getAllItemsPaged(int PageNum)
{
List itemList = new List();
string SqlQueryTotalCountStr =
@"SELECT COUNT(*) AS TotalCount
FROM ( SELECT *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) "
;
//return the total count of the results e.g. 35000
int count = 0;
try
{
using (SqlConnection conn = new SqlConnection(""))
{
using (SqlCommand sqlComm = new SqlCommand(SqlQueryTotalCountStr, conn))
{
try
{
conn.Open();
using (SqlDataReader r = sqlComm.ExecuteReader())
{
itemList = new List();
while (r.Read())
{
count = Convert.ToInt32(r["TotalCount"]);
}
}
}
catch
{
}
}
}
}
catch
{
}
//if rowNumb is <= TotalCount then perform this
if ((1000 * PageNum) <= count && count > 0)
{
//if rownum +1001 is greater than total count (e.g. 35000)
//then simply use totalcount - (rownum +1) as the upper limit.
//this matter during the tail end of the paging. The if else is there to handle the last set of
                ​//items to be indexed.   

int upperlimit = 0;
if ((1000 * PageNum) + 1001 >= count) upperlimit = count - ((1000 * PageNum) + 1);
else upperlimit = (1000 * PageNum) + 1001;
string SqlQueryStr = @"SELECT *
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
FROM Orders
WHERE OrderDate >= '1980-01-01'
) AS RowConstrainedResult
WHERE RowNum >= " + ((1000 * RowNum) + 1) +"
AND RowNum < "+ upperlimit+"
ORDER BY RowNum"
;
try
{
using (SqlConnection conn = new SqlConnection(""))
{
using (SqlCommand sqlComm = new SqlCommand(SqlQueryStr, conn))
{
try
{
conn.Open();
using (SqlDataReader r = sqlComm.ExecuteReader())
{
itemList = new List();
while (r.Read())
{
SPCrewData itemObj = new SPCrewData();
itemObj.Author = r["Author"].ToString();
itemObj.BlogID = Convert.ToInt32(r["BlogId"].ToString());
itemObj.Description = r["Description"].ToString();
itemObj.Title = r["Title"].ToString();

itemList.Add(itemObj);
}
}
}
catch
{
}
finally
{
}
}//END USING
}//END USING
}
catch
{
}
finally
{
}
}
return itemList;

}

Set a Filter for that method in SharePoint Designer

   In SharePoint Designer, you will be Creating the External Content types, and mapping it to the WCF Service that you created. When It comes tocreating Operations for the service, right click in the Data Source Explorer in SharePoint Designer and select the “New Read List Operation”

 

 

  

 

Click Next where you will see the page displaying the Input Parameter

  

Click the “Click to Add” link which will take you to the “Filter Configuration” dialogue.

  

Select a “New Filter”

Choose Page Number as the filter type.

Choose “PageNum” as the Filter Field.

 

Here is what will happen. When Search calls this Method, it will call your method by initially passing a default PageNum value of 0. Your method will execute, and search will index those results. It will then call the method again, however, this time, it will increment the PageNum Value to 1, Execute, index, call method again, increment PageNum Value to 2, …etc,etc….

Click Finish.

Crawl

  • Create your Content Source in SharePoint Search Service Application as a Business Data Connectivity.      
  •      
  • Point it the WCF Service that you created .
  • Hit OK.                                                                                                    

      

     

  • Run a full crawl.
  • You should get all the items in the sql back end indexed via BCS.

 

Hope you found this blog useful.