SQL : Get date part from datetime

I have been doing a bit of SQL stuff lately for my current project and did a lot of datetime conversion. I have always used the following query to get the date part of a datetime (which works just fine for me)

Select Convert(datetime, Convert(varchar(10), getdate(), 101))

I stumbled upon the following query which returns the same result and I find it quite interesting, that is no conversion required… 🙂

Select Dateadd(dd, 0, DateDiff(dd, 0, getdate()))

I have not seen any difference in terms of performance and it works the same with the first query. Thoughts anyone?

Building custom OData service on SharePoint 2010

Recently I had a task to build a custom OData service for SharePoint, so here are the steps:

First, need to make sure that ADO.Net data service framework is installed on SharePoint server:
ADO.Net for Windows 2008
Or
ADO.Net for Windows 2008 R2

After that, add the following references on the project:

  • System.ServiceModel
  • System.ServiceModel.Web
  • System.ServiceModel
  • System.Runtime.Serialization
  • System.Data.Services
  • System.Data.Services.Client
  • Microsoft.SharePoint.Client.ServerRuntime

The next step is to create a SharePoint Mapped Folder and then select ISAPI. This is the folder where we are going to create our .svc file. Open the svc file and make sure it uses MultipleBaseAddressDataServiceHostFactory.

<%@ ServiceHost Language="C#" Debug="true" Factory="Microsoft.SharePoint.Client.Services.MultipleBaseAddressDataServiceHostFactory, Microsoft.SharePoint.Client.ServerRuntime, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c"
    Service="TestODataService.EmployeeService,$SharePoint.Project.AssemblyFullName$" %>

In my project, I have created the following classes

[Serializable]
    [DataContractAttribute(IsReference = true)]
    [DataServiceKey("EmployeeId")]
    public class Employee
    {
        [DataMemberAttribute]
        public int EmployeeId { get; set; }

        [DataMemberAttribute]
        public string FirstName { get; set; }

        [DataMemberAttribute]
        public string LastName { get; set; }
    }
public class DataContext
    {
        public IQueryable<Employee> Employees
        {
            get
            {
                List<Employee> employeeList = EmployeeStore.GetEmployees();

                return employeeList.AsQueryable();
            }
        }

        
    }

In this case the DataContext class will be used as the data source type.

The last one is the EmployeeService itself, which inherits from DataService class

[BasicHttpBindingServiceMetadataExchangeEndpoint]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Required)]
    [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
    public class EmployeeService : DataService<DataContext>
    {
        [WebGet]
        public IQueryable<Employee> GetEmployeeByName(string name)
        {
            var searchResult = EmployeeStore.GetEmployees()
                                        .Where(e => e.FirstName.Contains(name) || e.LastName.Contains(name));

            return searchResult.AsQueryable();
        }

        public static void InitializeService(DataServiceConfiguration config)
        {
            config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
            config.SetServiceOperationAccessRule("*", ServiceOperationRights.AllRead);
            config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
        }
    }

Please note that I have set access to all entities and service operations to AllRead since I only need to read the data.

To consume the above service, we can add a service reference in our test project and use the url to the service, e.g.: http://localhost/_vti_bin/EmployeeService.svc

To get access to the entities, we can use the data context as below:

var ctx =
              new ServiceReference1.DataContext(new Uri("http://localhost/_vti_bin/employeeService.svc"));
            ctx.Credentials = System.Net.CredentialCache.DefaultCredentials;

            ctx.Employees.ToList().ForEach(e => Console.WriteLine(e.FirstName + " " + e.LastName));

To use the service operations:

var ctx =
              new ServiceReference1.DataContext(new Uri("http://localhost/_vti_bin/employeeService.svc"));
            ctx.Credentials = System.Net.CredentialCache.DefaultCredentials;
var query = ctx.CreateQuery<Employee>("GetEmployeeByName").AddQueryOption("name", "'Blogg'");
            var result = query.Execute();

            result.ToList().ForEach(e => Console.WriteLine(e.FirstName + " " + e.LastName));

Using SharePoint PeopleEditor user control

I had the chance to play with PeopleEditor user control on my previous project and just thought would be good to put another blog about it out there, so here you go:

Started with creating a visual web part and the next step is adding the control to the ascx page

<SharePoint:PeopleEditor ID="peUser" ValidatorEnabled="true" runat="server"
                        SelectionSet="User" Width="350px" IsValid="true" AllowTypeIn="true"
                        MultiSelect="true"></SharePoint:PeopleEditor>
<SharePoint:InputFormRequiredFieldValidator ID="rfvUser" 
                        SetFocusOnError="true" runat="server" ControlToValidate="peUser" BreakBefore="true"
                        ErrorMessage="Required field." EnableClientScript="false"
                        Display="Dynamic"></SharePoint:InputFormRequiredFieldValidator>

To get the selected users we can iterate through its resovedEntities property

for (index = 0; index <= peControl.ResolvedEntities.Count - 1; ++index)
{
    PickerEntity entity = (PickerEntity)peopleEdControl.ResolvedEntities[index];
    SelectedUser selectedUser = new SelectedUser(){ Id = Convert.ToInt32(entity.EntityData["SPUserID"])
                                                    , Name = entity.DisplayText
                                                    , LoginName = entity.Key };
    selectedUserList.Add(selectedUser);
}

To assign a value to PeopleEditor user control (in my case I use a string of login name separated by a semicolon)

string[] delimiter = { ";" };
string[] loginNames = loginNameString.Split(delimiter, StringSplitOptions.RemoveEmptyEntries);

foreach(string loginName in loginNames)
{
     PickerEntity pe = new PickerEntity();
     pe.Key = loginName;

     pe = peUser.ValidateEntity(pe);
     ArrayList entityList = new ArrayList() { pe };

     peUser.UpdateEntities(entityList);
}

Hope that helps 🙂

SharePoint URL Field

One way to assign value to URL field is by joining the url string and its description e.g. https://akurniaga.wordpress.com, My Blog (Not so nice but it works).

Another way is to use SPFieldURLValue:

To get value from a URL Field:

SPFieldUrlValue urlField = new SPFieldUrlValue(item["MyUrl"].ToString());
string description = urlField.Description;
string url = urlField.Url;

To assign to a URL Field:

SPFieldUrlValue urlField = new SPFieldUrlValue();
urlField.Url = "https://akurniaga.wordpress.com";
urlField.Description = "My Blog";
item["MyUrl"] = urlField;

Simple right? 🙂

Create a new Wiki Page

The code below adds a new wiki page and then inserts a web part into the new page’s rich text editor

SPList pagesList = web.Lists["Pages"];

SPFolder rootFolder = pagesList.RootFolder;

SPFileCollection fileCollection = rootFolder.Files;

string pageUrl = rootFolder.ServerRelativeUrl + "/MyNewPage.aspx";

//Create the page
SPFile newFile = fileCollection.Add(pageUrl, SPTemplateFileType.WikiPage);

//Checkout and add custom web part to the newly created page
newFile.CheckOut();

//Use a new Guid as the web part id
Guid storageKey = Guid.NewGuid();

string wpId = String.Format("g_{0}", storageKey.ToString().Replace('-', '_'));

//Get web part manager
using (SPLimitedWebPartManager manager = newFile.GetLimitedWebPartManager(System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared))

{

//Initialize the web part and insert it into 'wpz' web part zone.
//Web part zone is hidden in Rich text editor and its name is fixed ('wpz')

MyWebPart webPart = new MyWebPart();

webPart.ID = wpId;

webPart.Title = "My New Page";

manager.AddWebPart(webPart, "wpz", 0);

string content = String.Format(CultureInfo.InvariantCulture
, "<div class=\"ms-rtestate-read ms-rte-wpbox\" contentEditable=\"false\"><div class=\"ms-rtestate-read {0}\" id=\"div_{0}\"></div><div style='display:none' id=\"vid_{0}\"></div></div>"

, new object[] { storageKey.ToString("D") });

SPListItem item = newFile.Item;

item["WikiField"] = content;

item.Update();

}
newFile.CheckIn(string.Empty);

Hope that helps 🙂

A quick and easy way to read RSS in SharePoint Foundation 2010

Thanks to George Tsiokos for his Data view web part http://sharepointrss.com/, we just have to submit the form on the site. This will produce a .DWP file. Save the .DWP file to your local drive and the next step is to upload this .DWP file to a web part page in SharePoint and you can view the RSS feed. No installation required 🙂

Super user for SharePoint Web Application

I found the following warning message in EventViewer :

“Object Cache: The super user account utilized by the cache is not configured. This can increase the number of cache misses, which causes the page requests to consume unneccesary system resources.”

Resolved it by creating 2 user policies for the web application, 1 for super user and 1 for super reader. These users should not be farm administrators nor Application pool accounts.

Steps:
1. Go to Central Administration
2. Click the Application Management
3. Click the Manage web applications
4. Click the Web application and select User Policy from the Policy ribbon bar
5. Click Add Users
6. Select All Zones and click the Next button
7. Type domain\spSuperUser and give the user Full Control permission
8. Click Finish
9. Repeat Step 4 -8 for domain\spSuperReader

After adding the user policies, run the following PowerShell script:

$webApp = Get-SPWebApplication "http://url/&quot;
$webApp.Properties["portalsuperuseraccount"] = "domain\spsuperuser"
$webApp.Properties["portalsuperreaderaccount"] = "domain\spsuperreader"
$webApp.Update()

Please note if you use Claim based authentication the username format will be different.

Hope that helps

Add Content Editor Web Part into Site Template

In Onet.xml, find the module(s) that need to have a content editor web part and add the following:


<AllUsersWebPart WebPartZoneID="Left" WebPartOrder="1">
          <![CDATA[
          <WebPart xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/WebPart/v2">
            <Title>My Web part</Title>
            <FrameType>None</FrameType>
            <Description>Say Hello Web Part</Description>
            <FrameState>Normal</FrameState>
            <Assembly>Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Assembly>
            <TypeName>Microsoft.SharePoint.WebPartPages.ContentEditorWebPart</TypeName>
            <ContentLink xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor" />
            <Content xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor">
                &lt;script&gt; 

                function SayHello() 
                { 
			alert('Hello');
                  	
                      //all done 
                      return; 
                    } 
                  } 
                }


                _spBodyOnLoadFunctionNames.push(&quot;SayHello&quot;) 

                &lt;/script&gt;
            </Content>
            <PartStorage xmlns="http://schemas.microsoft.com/WebPart/v2/ContentEditor" />
          </WebPart>
          ]]>

        </AllUsersWebPart>

Note some characters need to be encoded.

In case a web part is broken, use contents=1 query string, for example: http://myurladdress/default.aspx?contents=1. This will show a list of all the web parts on the page.

Cheers 🙂

Show subsite on top navigation

Showing subsite programmatically is not as straight forward as using the UI

Global navigation

I found out that this property is only available from PublishingWeb (instead of SPWeb object) which is contained in Microsoft.Sharepoint.Publishing.dll. By default VS 2010 does not include the dll in the reference. After adding the dll, use the code below to show sub sites

PublishingWeb publisher = PublishingWeb.GetPublishingWeb(myWeb);
                        
publisher.Navigation.GlobalIncludeSubSites = true;
publisher.Update();
publisher.Close();

            

Hope that helps

Exception from HRESULT: 0x8107026E0

I got that exeception when I tried to deploy my solution. The problem was because I renamed my web template but I didn’t change the name attribute of the web template itself.

Hope that can help 🙂