Obtain next id for a Sharepoint 2010 List

March 28, 2011

This is a very simple article about obtaining the next id for a Sharepoint list in Sharepoint 2010. The reason why I am writting this is because I have recently been involved in a upgrade project from 2007 to 2010. As part of their current SP2007 environment they have a few number of customizations that had to be migrated, one of them was a custom workflow that at a certain stage would require to calculate the next available id in a Sharepoint list….

Read the rest of the article at my new personal blog:
Obtain next id for a Sharepoint 2010 List


Bawego – Go software for windows phone 7

November 1, 2010

What is bawego ?

Bawego is an online application that allows users to manage their go game and problems files stored in sgf format and to review and replay those files using the bawego client for windows phone 7.

This is a project I started for fun and to learn how to develop in the new Microsoft mobile platform and also, I wanted to get a new device but as an experienced go player, couldn’t do so if there was no go software available.

Why bawego ?

Bawego name was created using the word for the game in the language of those countries where the game is most popular. In korea the game is known as Baduk, in China as Weiqi and in Japan as Go.

Some of the features of bawego

    • Upload games in sgf format to a bawego account and review them using the WP7 client
      Review collections of problems organized by problem difficulty
  • More info

    Visit bawego for more info !!


    Authenticate a service call from a Windows Phone 7 application using Forms Authentication

    September 29, 2010

    After some time developing for the Windows Phone 7 platform (initially with the Beta version and now with the RTM) I’ve come across several challenging scenarios and decided to document some of them. First of all and as a disclaimer I’d like to point out that this article is valid with the current RTM version of the WP7 development tools and with future releases, there might be better solutions to the problem exposed, but enough of this and let’s get into the action !

    To start with, I’d like to introduce the solution I was trying to build. This is a Windows Phone 7 application built on the Siverlight platform, this application will connect to a asp.net web site configured for FormsAuthentication to gather some data through a WCF Data Services layer and expose it to end users (Consuming WCF Data Services from a WP7 application is a topic on its own that hopefully I will cover in another article) but this one is about securing the service and authenticating the service calls.


    Do you want to read more ? I am moving to my new site ! Please follow the link to read all the content of this article: Authenticate a service call from a Windows Phone 7 application using Forms Authentication


    Redirect the user to a custom page when clicking to a document link in a Document Library

    February 2, 2010

    The normal behaviour of Sharepoint when a user click on a document in a Document Library is to download it. Depending on the document type, in will open the default download dialog or it will open it with a client application. However, it is not always like that. There is also, the scenario where the user clicks on a document link and the Sharepoint server calls a server page to perform a different action other than simply downloading the file. The best example of this is when a user clicks on a browser-enabled Infopath Form in a Forms Library, in that case, we can see that the user is redirected to a Forms Server page called _layouts/FormServer.aspx and that page is responsible for delivering an html view of the Infopath Form in the client browser…


    Do you want to read more ? I am moving to my new site ! Please follow the link to read all the content of this article: Redirect the user to a custom page when clicking to a document link in a Document Library


    Provide a custom ‘new’ page for a Sharepoint Document Library

    February 2, 2010

    When creating Sharepoint Lists, it is possible to customize the default forms associated with the list and provide our own implementation of the display, edit and new forms. However, that doesn’t work in the same way when we are trying to customize a Document Library. In that case, we can still customize the edit and display forms (used to display and update the metadata associated with each document), but we can’t provide a custom new form. The reason is that there is no new form associated with document libraries, instead, users are redirected to an upload page where they can upload documents….


    Do you want to read more ? I am moving to my new site ! Please follow the link to read all the content of this article: Provide a custom new page for a Sharepoint Document Library


    Change an item content type with a SQL Query

    December 8, 2008


    I’m moving to a new site ! Please visit this article to by clicking the following url: Change an item content type with a SQL Query

    Some months ago, I was involved in a document library migration from SP2001 to SP2007 project. One of the big problems I found was how to upload all the files to Sharepoint with the standard web services interface and set the content type of the items. You can read more about that issue and the solution implemented here

    The problem with that approach is that it won´t work well when thousands of files are migrated. At the final migration process to production that took place this week, we moved around 70k files to the new server. Although the files were successfully uploaded, the update content type method didn´t work for many of them.

    As we were in the middle of the migration process, I tried to find a different solution for bulk updating all the files and setting the correct content types. At first, I tried writing a console application that would update all the files via Sharepoint Object Model. The problem with this approach is that the process would take a lot of time to process all the 70k files.

    As we had not enough time to run that process, I finally decided to try a don´t-do-this-at-home strategy. I wanted to update directly the Sharepoint database to set all the appropiate content types.

    At the content database of the site collection where I was migrating all the documents, I opened the AllUserData table where all list items are stored. That table contains the fields tp_ContentType and tp_ContentTypeId that seemed like what I was looking for. Additionally, there is a ContentTypes table where all the Content Types for the SiteCollection are stored. I already knew the name of the content type I wanted to set so I only needed the Id. I tried to build a simple update query:

    UPDATE    
           AllUserData
    SET              
           tp_ContentType = '<content type name>', 
           tp_ContentTypeId =
                              (SELECT ContentTypeId
                                FROM   ContentTypes
                                WHERE  
                                           ResourceDir = '<content type name>')
    
    WHERE     (tp_DirName = '<document library name>/<folder>') 
    

    Some comments on the previous sql:

    I only had one web and one document library for this migration process. In a different scenario, you would have to filter by tp_WebIdand tp_ListIdto change only the items of a specific location

    I found the field ResourceDir in the Content Types table that had the name of the content type. You could also use directly the content type Id, converting it to varbinary which is the type of the tp_ContentTypeId field. However, there is a problem with that, as I’ll point out later.

    Use tp_DirName to filter for a specific folder in the document library. You could also use it to filter by document library name but I strongly suggest you to use the tp_ListId field instead.

    So far so good, everything looks pretty straightforward. The problem with this code, is that it won’t work as expected. The tp_ContentType field will be properly set but it won´t recognise the item with the new Content Type.

    After testing the query, I decided to manually set the content type of an item with the standard Sharepoint interface and see what changed at the data level. What I saw was that the tp_ContentTypeId field was set to the id of the new content type but it also appended a Guid value at the end i.e: My content type id was “0x01017013” but the value at the AllUserData table was “0x01017013”. I didn’t know where did that guid came from nor had enough time to research it (if anybody knows it please leave a comment !!!) so I tried a different approach. What I did is, after I manually changed one of the items content type with the Sharepoint interface, I run the following Sql Query:

    UPDATE    
         AllUserData
    SET              
         tp_ContentType = '<content type name>', 
         tp_ContentTypeId =
                              (SELECT TOP 1 tp_ContentTypeId
                                FROM          AllUserData
                                WHERE      tp_ContentType = '<content type name>' and 
                                tp_DirName = '<document library name>/<folder>')
    WHERE     (tp_DirName = '<document library name>/<folder>') 
    

    Basically what this sql does is update all the items of the list folder and assigning the Content Type Id from the previously manually changed item (with the value assigned by the Sharepoint Object Model, including the appropiate guid value at the end)

    After running that query, all the items of the list folder had the correct updated content type and in less than 1 second !

    That’s all, as always, comments will be appreciated 🙂


    Excel Services Deployment: Changing Data Connection Libraries url from code

    November 17, 2008

    Lately, I’ve been involved in a large Business Intelligence project where Sharepoint Excel Services was used as a front end for delivering Excel based BI dashboards. Although It wasn’t the first time that I worked with Excel Services, it was the biggest project of that kind I’ve ever been involved in. The problem I faced was with the deployment of the reports and I am really surprised that almost nobody has comed accross this issue before. Excel reports nice and fancy and every end user loves it, the problem comes when you need to set up a deployment strategy based on Sharepoint Features for different environments (Dev, Test & Prod) for delivering the reports. 

    The issue we found is that Excel Services reports are using .odc data connections stored in a  Trusted Data Connection library at a Sharepoint server. At design time, when the report is created, you select a connection and add it to the current workbook. The problem is that the absolute url of the .odc file is stored in the same excel file ! It’s easy to figure out all kind of deployment issues caused by this behaviour: What happens when we are moving excel files from one environment (Test) to another (Prod) ? All excel documents have to be opened and each connection refreshed to point to the new Data Connection Library.

    Ideally, I wanted to have a named data connections model just like the reporting services platform has. A reporting site with a fixed Data Connection Library would be created and connections could be refered by their name and centrally managed at that reporting site level. Unfortunately, this is not the behaviour so I had to come up with a different solution.

    What I did is add in a feature receiver class of my excel provisioning feature (responsible for adding all the excel reports to my reporting site) that would open each excel file with OpenXml libraries and manually change the urls of all the connections to the new url. Following is a description of that process and the code of my feature activated event where this code is triggered. As a restriction, I had to use .Net framework 3.0 instead of 3.5, this is why the code used is based on OpenXml SDK 1.0 and not 2.0 !!

    public override void FeatureActivated(SPFeatureReceiverProperties properties)
    {            
        SPWeb web = (SPWeb)properties.Feature.Parent;
    
        SPList list = web.Lists["<name_of_data_connection_library>"];
    
        //iterate every report of the document library
        foreach (SPListItem report in list.Items)
        {                
            Stream s = report.File.OpenBinaryStream();
    
            //open package
            using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(s, true))
            {
                ConnectionsPart connectionsPart = xlPackage.WorkbookPart.ConnectionsPart;
                Stream connectionsStr = connectionsPart.GetStream();
                XmlDocument doc = new XmlDocument();
                doc.Load(connectionsStr);
    
                XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
                nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI);
                XmlNodeList nodelist = doc.SelectNodes("//default:connections/default:connection", nsManager);
    
                //iterate connections part
                foreach (XmlNode node in nodelist)
                {
                    //set new url
                    node.Attributes["odcFile"].Value = "<new_connection_string>";
                }
    
                using (StreamWriter sw = new StreamWriter(connectionsPart.GetStream(FileMode.Create)))
                {
                    doc.Save(sw.BaseStream);
                }
    
                //save sharepoint item
                report.File.SaveBinary(s);
            }
        }
    }
    

    That’s all ! I hope you find it usefull and please leave a comment if so !