Sunday, April 20, 2008

Using Property Bag of SPWeb to store Metadata

On an SPWeb there is no out-of-the-box solution to store custom metadata, you only have the name (URL), the title and the description. If you need more (e.g. status (active/inactive) or location) you have to implement a custom solution.

A possible way is to create a list (with the fields you need) that has only one list entry: the metadata of the parent SPWeb. With versioning enabled you also versioned metadata for your SPWeb. But if you have many sites of this type this may be some overkill to have an extra list for the metadata in each site.
Another solution is to use the property bag of the SPWeb and store the additional metadata directly in the SPWeb. This needs some coding of course (Web Part or Layouts application) and you have to integrate it into your sites. But the code is very simple:

Writing values:

string strKey = "MyKey";
string strValue = "MyValue";
if (webCurrent.Properties.ContainsKey(strKey)) // property exists already -> update it

webCurrent.Properties[strKey] = strValue;
if (strValue.Length > 0) // property doesn't exist -> add it if there is value to set
webCurrent.Properties.Add(strKey, strValue);

Reading values:

string strKey = "MyKey";
string strValue = string.Empty;
if (webCurrent.Properties.ContainsKey(strKey))
strValue = webCurrent.Properties[strKey];

If you want to save some metadata about the lists in the current SPWeb you can also use the property bag of your SPWeb. Just add the Guid of the list to each name of the property:

string strKey = "MyKey_" + listCurrent.ID.ToString();

To remove a property use the following lines:

// attention: SPWeb.Properties.Remove(string pKey) doesn't work to remove
// a property. To remove a property clear the content.
webCurrent.Properties[strKey] = null;

Saturday, April 19, 2008


Controlling What Files to Index

When the indexing process is running, the Gatherer process will open the files found in the content sources. But exactly what file types will it open? This is controlled by a list of file types that you can manage by the link File types on the Configure Search Settings page. The information in this list shows two things:

  1. What file types the Gatherer will try to open.
  2. If there is any icon defined in SharePoint for this file type.

If you are missing one file type, you can add it now by clicking New File Type. But this will not be enough; the Gatherer also needs the specific IFilter for this file type. You can install these IFilters from third Parties, or Microsoft IFilter Pack.

Common IFilters

File TypeDownload Source
MS Project
MS Visio
OneNoteInstall MS OneNote on the SharePoint server
Audio/Video files: MP3, WMA, WMV, ASF
DWG oCad

BDC WebService and valid Return Types

You can use basic data types int, string, ... or dataset as a valid return type of method in WebService to be used with BDC
This is sample of return type dataset in definition file of BDC:

<Parameter Direction="Return" Name="Customers">
<TypeDescriptor Name=""DataSet"" TypeName=""System.Data.DataSet, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"">
<TypeDescriptor Name=""Tables"" TypeName=""System.Data.DataTableCollection, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"" IsCollection=""true"">

<TypeDescriptor Name=""Table"" TypeName=""System.Data.DataTable, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"" >
<TypeDescriptor Name=""Rows"" TypeName=""System.Data.DataRowCollection, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"" IsCollection=""true"">

<TypeDescriptor Name=""DataRow"" TypeName=""System.Data.DataRow, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"">
<TypeDescriptor Name=""field1"" TypeName=""System.String"" IdentifierName=""f1"" />
<TypeDescriptor Name=""field2"" TypeName=""System.String"" />
<TypeDescriptor Name=""field3"" TypeName=""System.String"" />

Note: You can use same definition when you use typed DataSet

BDC Lessons

The search results will not show related entity instances for the item you searched.
Keys must be identify a single row:ex:
if you have table:
Products: with primary key ProductId
Orders: with primary key OrderId
When we create entity for Orders joined Items and select OrderId as a primary keyOrderId will be accross many rows (This isn't acceptable)
Solution: Create Composite key, or create new field as a key
If a key or part of a key is null, the BDC ignores that entity instance, so you would need to pad your null keys, (in SQL use ISNULL or COALESCE).
Moving on to incremental crawls, the only reference to creating them in MSDN or the OSS SDK is a slightly confusing note on this page. To implement incremental crawls on the BDC you would need the following:

1. You would need some column on your table/view/or Stored-Proc to indicate the last modified time of that entity instance. Adding a timestamp column to your tables is the easier approach since it requires no change to any application logic to update the last modified time. Point to note is that if you are combining tables to create an un-normalized view then you would need to calculate the greater timestamp in SQL from your combined entity instances. Also note, if you are using a timestamp column, you would need to cast it to DateTime in SQL for it to be of any use to the BDC.

2. In your IdEnumerator method you would need to declare a type descriptor for this column in your return parameter like so.

<Parameter Direction="Return" Name="entityReturnParam">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="entityDataReader" IsCollection="true">
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="entityDataRecord">
<TypeDescriptor TypeName="System.Int32" IdentifierName="entityPK" Name="entityPK" />
<TypeDescriptor TypeName="System.DateTime" Name="timestamp" />

3. You will need to declare a propperty on your entity that refers to the timestamp column defined above, but this propperty needs to be called __BDCLastModifiedTimestamp and should be of type string

<Entity EstimatedInstanceCount="0" Name="entityName">
<Property Name="Title" Type="System.String">entityName</Property> <Property Name="__BdcLastModifiedTimestamp" Type="System.String">timestamp</Property>

BDC connection using SQLAuthentication to SQL Server (MOSS 2007)

I used many BDC editors, All of them didn't allow me to define connection to database using SQL Authentication. I found only way to do that update generated XML Defintion. to use SQL Authentication.Node need Modification:

<LobSystemInstance Name="LOBSystemInstanceName">
<Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">PassThrough</Property>
<Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">SqlServer</Property> <Property Name="RdbConnection Data Source" Type="System.String">sqlInstance</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">DataBase</Property>
<Property Name="rdbconnection Integrated Security" Type="System.Boolean">false</Property>
<Property Name="rdbconnection Password" Type="System.String">username</Property>
<Property Name="rdbconnection User ID" Type="System.String">password</Property>

Property rdbconnection Integrated Security is true by default, and we need to add this attribute to allow us define SQL Authentication.