Wednesday, September 25, 2013

Sharepoint - Create Quick Launch menu programmatically

Quick Launch is navigation located on the left side of the screen and it is important part of almost every SharePoint site (marked in red square on the image). 

Quick Launch in SharePoint 2013


Links to the Quick Launch can be added manually in Site Settings, but, if you want to automate the process of adding the links, you can do it from c# code.

The code is very simple, it consists of reading the Quick Launch object, deleting the existing links in it. And then, after the existing Quick Launch is cleared, putting the new links which we have read from some text file.

C# CODE:



SPSecurity.RunWithElevatedPrivileges(delegate()
{
     using (SPSite oSiteCollection = new SPSite("http://mySiteUrl"))
     {
           using (SPWeb oWeb = oSiteCollection.OpenWeb())
           {
                 // Create the node.
                 SPNavigationNodeCollection _quickLaunchNav = oWeb.Navigation.QuickLaunch;
                 // Delete all existing items in Quick Launch
                 int _counter = 0;
                 while (_quickLaunchNav.Count != _counter && _quickLaunchNav.Count > 0)
                 {
                       _counter++;
                       SPNavigationNode _item = _quickLaunchNav[0];
                       _item.Delete();
                 }

                 // Here we read links that will go in Quick Launch from text file
                 // Let's say that in "abc.txt" file we have links written as (title|link):
                 //
                 // My project |/sites/MyTestSite1
                 // Project documents|/sites/MyDocLib2
                 // Reports|/sites/MyCustomReports
                 //...
   
                 string _line;

                 System.IO.StreamReader _file = new System.IO.StreamReader("abc.txt");
                 while ((_line = _file.ReadLine()) != null)
                 {
                       string[] _splitStr = _line.Split('|'); 
                       string _title = _splitStr[0];
                       string _url = _splitStr[1]; 


                       SPNavigationNode _SPNode = new SPNavigationNode(_title, _url, true);
 

                       _quickLaunchNav.AddAsLast(_SPNode);
                       

                 }
                 _file.Close();
           }
     }
});


In my next post, I'll show how to create custom links in Top Navigation of SharePoint. 

Thursday, September 19, 2013

SharePoint - Setting Site Title programmatically not working

Recently, I got request to make a small script that could change name of SharePoint Site programmatically from c# code.

Well, the code for doing that is extremely simple, I found it on Microsoft's web page:



using(SPWeb oWebsite = SPContext.Current.Site.OpenWeb("Website_URL"))
{
    oWebsite.Title = "New Website Name";
    oWebsite.Update();
}

The code executed with no errors, but no change to the site title was made.

It is annoying how such small portion of code can cause trouble.

This code works perfectly if your site's language is default language - English for example. But, if you have more than one language installed and you created your site using different language template - for example Croatian, then this code will not work.

To change you site title, you must change title for all languages:

SPUserResource userRes = web.TitleResource;
foreach (CultureInfo item in web.SupportedUICultures)
{
     userRes.SetValueForUICulture(culture, "My custom site title");
}


Friday, September 13, 2013

SharePoint & Project Server - Finding data in database

I have already written a couple of posts on how to find a specified type of data in SharePoint database.

This is the post I wrote on how to find documents stored in Document libraries in SharePoint and this is the post on how to find the items stored in SharePoint lists.

The fact is that, both SharePoint and Project Server databases are very tricky to work with. SharePoint has only one database, and all data is located in that single database and you need some time to figure out its schema and how everything works.


Project Server, on the other hand, has four databases and data is really scattered in these databases.

If you need to make a Report of some sort (for example in Reporting Services), you will need to connect to these databases and fetch data directly from them.

But, sometimes, there is data you see in SharePoint or Project Server that you just can't find in database.

Let's say you have defined new text column on your SharePoint list that contains text "My Custom Text" and you need it for your report and you can't find it in the database.

In that case, the best way is to search the entire database for this specified string. This is the SQL query that searches the entire database for string "My Custom Text". This isn't my query, I found it in on Stackoverflow forum, I am using it here only just as an example:



DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%My Custom Text%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

SELECT ColumnName, ColumnValue FROM @Results


This query will return table name and column name in that table where data is stored.