Can I get all the fields in an item (in Sitecore)?

Question

I'm trying to write a sql query to get all the fields in a given item in Sitecore.

To say I am stuck is putting it mildly.

I'm guessing I have to do some self joining on the fields table, but I'm getting myself in knots.

Anyone have any ideas?

Highborn

Answer 1

In none of the cases you should ever try to query the Sitecore database yourself. The database changes over time and this would break your code. Rather, use the Item.Fields. This is a collection which contains all the necessary fields. If you want to make sure that all the fields are loaded(really loaded, not lazy loaded), than you can use Item.Fields.ReadAll().

Edit: Also, keep in mind that querying doesn't allow you to construct an Item, so you miss the behavior of default values and do not use the intelligent Sitecore caching at all.

Drayage

I want to do lazy loading. could you please tell me how to solve this?? – Braeunig

Answer 2

Try to call Sitecore.Context.Item.Fields.ReadAll() before looking up a field.

Immateriality

when i do this all the field names returned are system fields. I was hoping there's a way to get user defined template field names. – Musgrove

Answer 3

First Attempt, but does not return all fields

SELECT I2.Name FROM
 Items AS I 
 JOIN UnversionedFields AS UF ON I.ID = UF.ItemId
 JOIN VersionedFields AS V ON I.ID = V.ItemId
 JOIN SharedFields AS S ON I.ID = S.ItemId 
 JOIN Items AS I2 ON I2.ID = UF.FieldId OR I2.ID=V.FieldId OR I2.ID = S.FieldId    
 WHERE I.ID = '110D559F-DEA5-42EA-9C1C-8A5DF7E70EF9'
 GROUP BY I2.Name

Violoncello

This seems to work ok, but I still couldn't find the exact value I was looking for. select Value from Items I WITH (NOLOCK) inner join Items T on I.TemplateID = T.ID inner join Fields F on F.ItemId = I.ID inner join Items FN on F.FieldId = FN.ID where I.templateID = '{xxxxxxx}' and FN.Name = 'Description' – Alfeus

Answer 4

By calling item.Fields you get the item fields that you have designated in your templates as well as the Sitecore standard fields that exist on all items. Use the code below if you only want the fields that you have defined in your templates. Of course, this assumes your field names do not start with "__"

// Get Fields directly from the Item
List<string> fieldNames = new List<string>();
item.Fields.ReadAll();
FieldCollection fieldCollection = item.Fields;
foreach (Field field in fieldCollection)
{
    //Use the following check if you do not want 
    //the Sitecore Standard Fields 
    if (!field.Name.StartsWith("__"))
    {
        fieldNames.Add(field.Name);
    }
}

Thane

I've been looking at this problem myself and am using basically the same solution but I've pushed the check for "" into the foreach by using Linq like so foreach (Field field in fieldCollection.Where(x => !x.Name.StartsWith(""))) What do you think? Better, worse, faster execution? – Cholula