You are hereSharePoint

SharePoint

Get File Sizes of Document from Sharepoint Database

This is a simple query to get file sizes from a sharepoint database.  I know there are multiple ways of doing this, but I found this the easiest and also the fastest.

select [filename],
sum(CAST((CAST(CAST(filesize as decimal(38,2))/1024 As 
      decimal(38,2))/1024) AS Decimal(38,2))) AS 'Size in MB'
from (
    select dirname + '/' + leafname as [filename],
           size as filesize,
           siteid,
           webid,
             ExtensionForFile
    from alldocs
    union
    select d.dirname + '/' + d.leafname as [filename],
           v.size as filesize,
           d.siteid,
           d.webid,
             ExtensionForFile
    from alldocs d
    inner join alldocversions v on d.siteid = v.siteid
    and d.id = v.id
) as results
inner join webs s on s.siteid = results.siteid
and s.id = results.webid
where (filesize is not null
  and filesize > 0)
    and ExtensionForFile not like '%aspx%' -- Not Include Certian File Types
group by 
[Filename]
order by 2 desc

Add choices to Sharepoint Multi-Choice Field

Setting field values of a list item in SharePoint is easy.  But when it comes to Multi-Choice fields, the code is a big different.  The following code shows how values can be added to a multi-choice field.

SPFieldMultiChoice choiceField = (SPFieldMultiChoice)myWeb.Fields["choiceFieldName"];
choiceField.Choices.Add("Value");
//Set PushChangesToLists to true if automatic update of List or Content types is required
choiceField.PushChangesToLists = false; 
choiceField.Upate();

Use CAML Query to Read Data from lists or Libraries

One way to read the data from a list or a library is to iterate through it using the items collection of the list or library. But this is not the most efficient way to do it, specially if only few items are needed from this list.

Enter CAML Queries.

The following is the example query

<Where>
  <Eq>
    <FieldRef Name="Title" />
      <Value Type="Text">title</Value>
  </Eq>
</Where>

One thing to note in the CAML Query is that there is <query> start and end tag.  It is required that the query string does not have starting and ending <query> tag, otherwise the SPQuery will always return a empty dataset.

And here is the code:

SPList mylist = web.Lists["MyList"];
SPQuery spQuery = new SPQuery();
spQuery.Query = "" //CAML Query String;
SPListItemCollection queryitems = mylist.GetItems(spQuery);

That is it, as simple as that.

There as a great tool to build CAML Queries at http://www.u2u.info/SharePoint/U2U%20Community%20Tools/Forms/AllItems.aspx called U2U Caml Query Builder.  It is worth checking this tool out.

Create a SharePoint Content Type programmatically

Sometimes it is necessary to create a Sharepoint Content Type by code, specially when batch creating content types.  The following code snippet shows how a sharepoint content type can be created in C#.  I have also included some not-so-simple field types like choice and lookup.

using (SPSite p_site = new SPSite(urlToSite))
{
    using (SPWeb p_web = p_site.OpenWeb())
    {
        SPContentType CustomContentType = new
                SPContentType(p_web.AvailableContentTypes["ParentContentType"],
                p_web.ContentTypes, "MyContentType");
 
        // A string Field
        p_web.Fields.Add("NumberColumn", SPFieldType.Number);
        SPFieldLink fLink1 = new SPFieldLink(p_web.Fields["NumberColumn"]);
        CustomContentType.FieldLinks.Add(fLink1);
 
        // A required number field
        p_web.Fields.Add("StringColumn", SPFieldType.Text, true);
        SPFieldLink fLink2 = new SPFieldLink(p_web.Fields["StringColumn"]);
        CustomContentType.FieldLinks.Add(fLink2);
 
        //A Choice Field
        p_web.Fields.Add("ChoiceColumn", SPFieldType.Choice, false);
        SPFieldChoice choicefield = (SPFieldChoice)p_web.Fields["ChoiceColumn"];
        // Add a group to the filed
        choicefield.Group = "MyGroup";
        // Add choices
        choicefield.Choices.Add(string.Empty);
        choicefield.Choices.Add("Yes");
        choicefield.Choices.Add("No");
        // Set the default choice
        choicefield.DefaultValue = string.Empty;
        choicefield.Update();
        SPFieldLink fLink3 = new SPFieldLink(choicefield);
        CustomContentType.FieldLinks.Add(fLink3);
 
        p_web.Fields.Add("LookupColumn", SPFieldType.Lookup, false);
        SPFieldLookup lookupfield = (SPFieldLookup)p_web.Fields["LookupColumn"];
        // Set the remote lookup list
        lookupfield.LookupList = new Guid("guidofremotelist");
        // Set the remote field
        lookupfield.LookupField = "remotefieldname";
        lookupfield.Update();
        SPFieldLink fLink4 = new SPFieldLink(lookupfield);
        CustomContentType.FieldLinks.Add(fLink4);
        
 
        CustomContentType.Update();
    }
}

Navigation

Recent blog posts

more

Recent comments

Tags in Topics

more tags