Archive for January 31, 2014

Creating Entities from Dynamic SQL

When working with a LINQ to SQL model (dbml) and you try to add a custom stored procedure that uses dynamic SQL, you will likely see an error similar to the following:

The return type for the following stored procedure could not be detected. Set the return type for each stored procedure in the Properties window.

Visual Studio will detect dynamic SQL as the return type of a stored procedure when you return columns from temporary tables, for example.

How to resolve? Relatively easy actually. At the start of your stored procedure, add the following line:

SET FMTONLY OFF

Update the procedure to include the above command and back in Visual Studio, refresh your database connection before trying to add the procedure to the model. This time, Visual Studio is able to work out the return columns and their type. Once you’ve added it to the model, remove the command from the stored procedure in SQL.

What does setting FMTONLY to off do? Well, while this set to off, running the procedure only returns the metadata, no rows are returned. By doing this Visual Studio is able to determine the columns. Simple! 🙂

Get SearchServiceApplication in PowerShell

In C# when retrieving the default SearchServiceApplication object you might do something like the following, if you were trying to initiate a new Schema object, for example:

SPServiceContext serviceContext = SPServiceContext.GetContext(SPContext.Current.Site);
 
SearchServiceApplicationProxy searchApplicationProxy = serviceContext.GetDefaultProxy(typeof(SearchServiceApplicationProxy)) as SearchServiceApplicationProxy;
 
Guid applicationId = searchApplicationProxy.GetSearchServiceApplicationInfo().SearchServiceApplicationId;
 
SearchServiceApplication searchApplication = SearchService.Service.SearchApplications.GetValue<SearchServiceApplication>(applicationId);
 
Schema schema = new Schema(searchApplication);

If you’re looking to implement something similar in PowerShell, you don’t have to write quite as much code. Take a look at the following script, which achieves the same goal:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint");
 
$searchApplication = Get-SPEnterpriseSearchServiceApplication "Search Service Application"
 
$schema = New-Object Microsoft.Office.Server.Search.Administration.Schema($searchApplication);

The call to Get-SPEnterpriseSearchServiceApplication is all that’s required to retrieve the default SearchServiceApplication object.

Simples!

Custom_AddDocLibMenuItems

SharePoint Short #19

When implementing your own Custom_AddDocLibMenuItems, which allows you to add custom menu items to the ECB of SharePoint items, remember to check for other implementations of this method before your own version.

For example:

function YourImplementationOf_AddDocLibMenuItems(m, ctx, url) {
    if (typeof Custom_AddDocLibMenuItems != 'undefined') {
        var CurrentCustom_AddDocLibMenuItems = Custom_AddDocLibMenuItems;
    }
 
    Custom_AddDocLibMenuItems = function (m, ctx, url) {
        // Custom code to add ECB menu items....
 
        if (CurrentCustom_AddDocLibMenuItems === undefined) {
            return false;
        }
 
        return CurrentCustom_AddDocLibMenuItems(m, ctx, url);
    };
}

By checking if Custom_AddDocLibMenuItems is not Undefined and referencing the method at the start of your YourImplementationOf_AddDocLibMenuItems method, you are able to ensure any other implementation of this method is called once you are finished with it.

To have your YourImplementationOf_AddDocLibMenuItems method initiated when a page loads, add the following:

$(document).ready(function () {
    _spBodyOnLoadFunctionNames.push('YourImplementationOf_AddDocLibMenuItems()');
});

Here we use jQuery’s ready function, to add the custom method YourImplementationOf_AddDocLibMenuItems to a SharePoint array that is processed when the DOM is loaded. What this means is that the method will be executed when the page loads and subsequently the custom overload of Custom_AddDocLibMenuItems is called whenever the ECB is opened.

Field Order for External Content Type

Working with external content types isn’t as straight forward as it should be, especially within a Visual Studio project. Take trying to define the display order of your columns as an example.

You define your task entity by adding your required Type Descriptors in BDC Explorer. The order the columns will display in external views is dependant on the order they are listed in the BDC Explorer. That’s all well and good but there’s no way through the UI to change this order.

The easiest way to change the order is to directly edit the dbcm file in your favourite text editor, mine is Notepad++. Other text editors are available 🙂

In Visual Studio, right click your model in the Solution Explorer pane and click Open Folder in File Explorer. In the folder that loads, open the bdcm file in your editor of choice.
Read more

Follow

Get every new post delivered to your Inbox

Join other followers: