Business Data Catalog (BDC) Part - 5

Hi All,

I suggest to go and read below articles on BDC before proceeding here.

Business Data Catalog (BDC) Part - 1

Business Data Catalog (BDC) Part - 2

Business Data Catalog (BDC) Part - 3

Business Data Catalog (BDC) Part - 4

Ok, let’s continue the series of BDC.

Now we have the complete XML and example in our hand, we can play with it. Search is something very important nowadays.

Here we are going to integrate the search in our BDC Example. Let’s directly play with it to see it in action.

Open the XML, locate to the in the XML. You must find it almost at the end of the XML.

So after that , write down the following Tags.

<Actions>
<Action Name="Seach in Windows Live!" Position="1" IsOpenedInNewWindow="true"
Url="http://search.live.com/results.aspx?q={0}" ImageUrl="">
<ActionParameters>
<ActionParameter Name="au_fname" Index="0" />
</ActionParameters>
</Action>
</Actions>

This will add one more option in toolbar of the detailed view profile page. Let’s add one more Action in the toolbar. We used this query string because if you search in Live., you should know that this is the query string that is used for search in Live.

Add this tag inside <Actions></Actions> tag only. That means we will have two actions. Let’s this action search it from Google. So we use its Query string and pass our parameter.

<Action Name="Seach in Google!" Position="2" IsOpenedInNewWindow="true"
Url="http://www.google.com/search?q={0}" ImageUrl="">
<ActionParameters>
<ActionParameter Name="au_fname" Index="0" />
</ActionParameters>
</Action>


Perform the same steps again to import the Application Definition XML File from central administrator and from your SSP.

Check out the actions now.

Business Data Catalog (BDC) Part - 4

Hi All,

I suggest to go and read below articles on BDC before proceeding here.

Business Data Catalog (BDC) Part - 1

Business Data Catalog (BDC) Part - 2

Business Data Catalog (BDC) Part - 3

you can also read part 5 after reading part 4.

Business Data Catalog (BDC) Part - 5

Let’s continue our series of BDC Further.

Now as we are ready with our BDC, we can proceed and use this BDC in a list or document library. Go to any list or library and create column.

Ok, give the name of the column. I’ll give “Authors”.



And select type Business Data and select Authors definition as we did in our previous part. So as you do this, you will get the clear idea because now you have four columns available that we had in our XML. There is an option to select the selected column, let’s keep it address and press OK.



Now, when you will try to insert a record, just like people picker, it will give you an option to select the au_fname and city as we have these parameters in search in our BDC XML. As you see, when you search, you get result from database and it can be seen in the box.



Because we have kept the address column in display, it shows the address of that contact. If you want to see all the parameters that we have used in BDC just click on that arrow and click on View Profile.



See, you can see profile like this.



One very important thing to note. As you can see the refresh button available in the BDC column. This is given because once the data is changed in underlying database table, it will be fetched there directly and you can see the updated data there.

Have a look at the current value of Address.


Let's make a change in underlying datasource.

we are going to change in the address column, change to "New Adress".



And then go back to the list and hit the refresh button and see the change. It will give you one message in one page saying that it will update the datasource and it will take time and so on. just go ahead and press OK.



That’s it. Now explore the BDC in terms of Field in your own way. You will love it.


I’ll continue the series on BDC in my next article as well.

Business Data Catalog (BDC) Part - 3

Hi All,

Here i am back with one more part of BDC.

I suggest to go and read below articles on BDC before proceeding here.

Business Data Catalog (BDC) Part - 1

Business Data Catalog (BDC) Part - 2

After reading part3, you can proceed further from part 4.

Business Data Catalog (BDC) Part - 4

Business Data Catalog (BDC) Part - 5

Ok, let’s continue our series of BDC.

Now we will take a closer look at BDC Application and going to make connectable BDC Web parts.

To do that we have to modify our existing BDC XML. Open that XML and find this line.

SELECT au_fname, city, state,address FROM authors WHERE City = @city.

Replace the line with the following line.

SELECT au_fname, city, state,address FROM authors WHERE (City LIKE @city) AND (fname
LIKE @au_fname)

Now add one more FileDescriptor entry in <FileDescriptors> Tag

<FilterDescriptor Type="Wildcard" Name="au_fname"/>

Add MethodInstance Tag in XML, you will find it at the very bottom of our XML.

<MethodInstance Name="AuthorSpecificFinderInstance" Type="SpecificFinder" ReturnParameterName="Authors" />

Now, add one more default value Tag in Parameter City and Direction IN tag.

<DefaultValue MethodInstanceName="AuthorSpecificFinderInstance" Type="System.String">%</DefaultValue>

Add one entire new DefaultValue Tag also.

<Parameter Direction="In" Name="@au_fname">

<TypeDescriptor TypeName="System.String" AssociatedFilter="au_fname" Name="au_fname" IdentifierName="au_fname">

And perform the same steps as we performed in part 1. This time you will find out a new message coming indicating creating profile.



<DefaultValues>

<DefaultValue MethodInstanceName=" <DefaultValue MethodInstanceName="AuthorFinderInstance" Type="System.String">%</DefaultValue>
" Type="System.String">%</DefaultValue>

<DefaultValue MethodInstanceName="AuthorSpecificFinderInstance" Type="System.String">%</DefaultValue>

</DefaultValues>

</TypeDescriptor>

</Parameter>

Once you are done with uploading, you observe one thing. This time it didn’t give warning that it gave in last two parts.

Now go back to your site, import the application definition in business data list web part once again and then perform test once to make sure that web part works.

For example, I have written Oakland as city search and I got the result.



Ok, we have tested this one. Now it’s time to add one more web part in the page. Edit the page and this time adds Business Data Item web part.

Now, Edit the Business Data Item web part and select the same application definition file.

And perform the connection between two web parts.



That is it. Go out of Edit mode now and enjoy the relation between two tables with no code.



I will continue the series of BDC with yet another example.

FullTextSqlQuery with QueryText max length issue

Hi all,

As mentioned in one of our previous post for search part 2
we are using FullTextSqlQuery object from Microsoft.Office.Server.Search.Query.

But we are facing problem for while creating query and assigning to QueryText method.

QueryText method accepts string object but it has a limitation.

You can assign maximum 4096 character string to QueryText.

We came to know about this by disassembling this DLL.

What we found is strange thing.

While setting QueryText property it will check

If object is type of FullTextSqlQuery then QueryText max length equals to 4096 char

Else if object is type of KeywordQuery then QueryText max length equals to 1024 char.

If length is more that this limit then it will give ArgumentOutOfRangeException();

We don’t know why is it so?

But just guessing that may be this limitation for rank optimization.

So conclusion is

FullTextSqlQuery QueryText should be < 4096 char
KeywordQuery QueryText should be < 1024 char.

Business Data Catalog (BDC) Part - 2

Hi All,

Here i am back again with part 2 of Business Data Catalog.

See Business Data Catalog (BDC) Part - 1

Business Data Catalog (BDC) Part - 3

Business Data Catalog (BDC) Part - 4

Business Data Catalog (BDC) Part - 5

Read the above part1 to understand BDC concept, because many things in this part are in seqence. so before starting on part 2, i recommend you go through part 1 and then after reading part 2, you can readu further part of series.

Ok, let’s continue our discussion on BDC. Earlier in part 1, we discussed how single data can be returned based on query of au_fname.

Now we will change the query, so that we can enhance our example and expand it to return multiple result set.

Take the same XML that we have used in our first part, we will modify it.

Find the section where we have written.

SELECT au_fname, city, state,address FROM authors WHERE au_fname = @au_fname.

Change this line to

SELECT au_fname, city, state,address FROM authors WHERE City LIKE @City.

Notice the difference is parameter change and same way instead of = operator, I used LIKE so that it can return multiple result set.

Find the FileDescriptor by au_fname and replace it with the following file descriptor.

If you observe, we have changed Type from Comparator to Wildcard.


<FilterDescriptor Type="Wildcard" Name="City" >
<Properties>
<Property Name="UsedForDisambiguation" Type="System.Boolean">true</Property>
</Properties>
</FilterDescriptor>

Change the Parameter tag also where you have au_fname. Change it to City like below.

<Parameter Direction="In" Name="@City">
<TypeDescriptor TypeName="System.String" IdentifierName="au_fname" AssociatedFilter="City" Name="city">
<DefaultValues>
<DefaultValue MethodInstanceName="AuthorFinderInstance" Type="System.String">%</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>

Ok, we are ready with our changes; all you have to do is perform the same steps performed in part 1.

Go to your SSP, under Business Data Catalog, import business definition. Import the definition, you will find the same warning coming again. Ignore it for now too.

Go back to your site; add web part of type business data list. And use this new application and now you will find city as parameter instead of au_fname.



I will continue with part 3 of BDC where we will explore BDC in more detail.

Business Data Catalog (BDC) Part - 1

Hi All,

In a simplest manner if I explain you what BDC means that it can be nothing better than saying that fetching, displaying and interacting business data into the MOSS.

see Business Data Catalog (BDC) Part 2

and

Business Data Catalog (BDC) Part - 3

Business Data Catalog (BDC) Part - 4

Business Data Catalog (BDC) Part - 5

for further reading.

One important thing to keep in mind that it is not available in standard edition of MOSS, for this facility you have to buy very expensive Enterprise edition of MOSS.

Any database record can be brought in to MOSS with the help of BDC. The main advantage of using BDC is that it is no code solution. By no code, I mean you do not need to write functions that uses the stored procedure to fetch the data, returns you data reader or dataset and then you use them to bind it to grid or loop through them and bind it to any source control.

Yes, here you need not to write any code for that. All you need to write is one XML file that will be the bottom line for BDC to execute. Yes, of course it needs practice to work on that XML, but once you are comfortable with it, then really it is an easy task.

Being XML in the background process of BDC, you can connect almost with every source of database. You can connect you SQL Server, Oracle, My SQL or most databases. You can then have a facility to make a search out of it that displays the result set in the grid form that is the built in form of BDC.

Here we have to use BDC web part to display the data being fetched and executed by XML.

So without talking much let’s dive into BDC first part to understand very basic concept of it.

Here we will take Pubs database of SQL Server as an example to understand BDC. Consider Authors table in it. We have au_fname column as a text field. We will create a BDC XML that will connect to SQL Server database and fetch the record from the search query from BDC web part and then web part display the result.
Let’s start with the XML.

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

<LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd" Type="Database" Version="1.0.0.1" Name="Authors" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

<Properties>

<Property Name="WildcardCharacter" Type="System.String">%</Property>

</Properties>

<LobSystemInstances>

<LobSystemInstance Name="AuthorsTraders">

<Properties>

<!--AuthenticationMode can be set to PassThrough, RevertToSelf, RdbCredentials, or WindowsCredentials. -->

<Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>

<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>

<Property Name="RdbConnection Data Source" Type="System.String">I3punestn7
</Property>

<Property Name="RdbConnection Initial Catalog" Type="System.String">Pubs</Property>

<Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>

<Property Name="RdbConnection Pooling" Type="System.String">false</Property>

</Properties>

</LobSystemInstance>

</LobSystemInstances>

<Entities>

<Entity EstimatedInstanceCount="100" Name="Author">

<!-- EstimatedInstanceCount is an optional attribute-->

<Properties>

<Property Name="Address" Type="System.String">address</Property>

</Properties>

<Identifiers>

<Identifier Name="au_fname" TypeName="System.String" />

</Identifiers>

<Methods>

<!-- Defines a method that brings back Customer data from the back-end database.-->

<Method Name="GetAuthors">

<Properties>

<Property Name="RdbCommandText" Type="System.String">

SELECT au_fname, city, state,address FROM authors WHERE au_fname = @au_fname

</Property>

<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>

<!-- For database systems, can be Text, StoredProcedure, or TableDirect. -->

</Properties>

<FilterDescriptors>

<!-- Define the filters supported by the back-end method (or sql query) here. -->

<FilterDescriptor Type="Comparison" Name="au_fname" >

<Properties>

<Property Name="Comparator" Type="System.String">Equals</Property>

</Properties>

</FilterDescriptor>

</FilterDescriptors>

<Parameters>

<Parameter Direction="In" Name="@au_fname">

<TypeDescriptor TypeName="System.String" IdentifierName="au_fname" AssociatedFilter="au_fname" Name="au_fname">

<DefaultValues>

<DefaultValue MethodInstanceName="AuthorFinderInstance" Type="System.String">Dean</DefaultValue>

</DefaultValues>

</TypeDescriptor>

</Parameter>

<Parameter Direction="Return" Name="Authors">

<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="AuthorsDataReader">

<TypeDescriptors>

<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="AuthorsDataRecord">

<TypeDescriptors>

<TypeDescriptor TypeName="System.String" IdentifierName="au_fname" Name="au_fname">

<LocalizedDisplayNames>

<LocalizedDisplayName LCID="1033">au_fname</LocalizedDisplayName>

</LocalizedDisplayNames>

</TypeDescriptor>

<TypeDescriptor TypeName="System.String" Name="address" >

<!-- Do not use the AssociatedFilter attribute in return parameters.-->

<LocalizedDisplayNames>

<LocalizedDisplayName LCID="1033">address</LocalizedDisplayName>

</LocalizedDisplayNames>

<Properties>

<Property Name="DisplayByDefault" Type="System.Boolean">true</Property>

</Properties>

</TypeDescriptor>

<TypeDescriptor TypeName="System.String" Name="State">

<LocalizedDisplayNames>

<LocalizedDisplayName LCID="1033">state</LocalizedDisplayName>

</LocalizedDisplayNames>

<Properties>

<Property Name="DisplayByDefault" Type="System.Boolean">true</Property>

</Properties>

</TypeDescriptor>

<TypeDescriptor TypeName="System.String" Name="city">

<LocalizedDisplayNames>

<LocalizedDisplayName LCID="1033">city</LocalizedDisplayName>

</LocalizedDisplayNames>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Name="AuthorFinderInstance" Type="Finder" ReturnParameterName="Authors" />

</MethodInstances>

</Method>

</Methods>

<!-- Enter your Action XML here -->

</Entity>

</Entities>

</LobSystem>

Now, you have to go to your central administration. Go there and go to your SSP created. There under Business Data Catalog section click on import application definition. Browse through your XML that we have just created, and import it.

When you import the application, if you find this message coming on the screen, ignore it for now. Else there is a problem with your XML, you need to correct it.



Once you are done with this, hit OK and come to a page where it shows a summary about your application that you have just uploaded.



Now open a page where you want to import this BDC web part. Open your site and click on edit this page. Add web part, search for Business Data List web part under Business Data Section of All Web parts. Once imported, click on tool pane.



And then search for your web part from there, and select the application that we had uploaded.



After importing it, it will look very similar to this.



Now query the data and you will see the result set.

When I worked with the BDC, I got in lot of troubles. I initially faced lot of problems because nothing was succeeding. Even my XML was right, at the last moment when I press retrieve data, it was giving me Error. Then I looked very carefully at XML then I realized that number of fields that you want to retrieve must be there in the type descriptor tag. Also keep in mind that if there are three fields in the query, you must have only three type descriptor in XML. This is my hard work. :)

So finally you have data in return.



This ends our first part. I will continue this with next part when we will achieve some more functionality.

Understanding Record Management Feature Part 5 – Auditing

Hi All,

You can read other articles of the series.

Understanding Record Management Feature Part 1 - Document Information Panel

Understanding Record Management Feature Part 2 - Labeling

Understanding Record Management Feature Part 3 - Barcode

Understanding Record Management Feature Part 4 –Expiration Policy

Let’s continue our discussion for records management features. Here we will take up one of the very important feature of record management and that is Auditing.

Auditing enables you to capture several information regarding the items in the document library and content types like who made the change, what change and at what time. Who opened the document, viewed the document, view item properties, editing the items, moving and copying the items and much more.

It actually allows the administrator to have a log for each user’s actions that he took. Administrator can retrieve the entire history of all the actions performed by all users.

These all things need to capture in terms of generating the report at any point of time. Once we enable the auditing option, we will have to go to the top site level to administer all the changes and make the audit of your document library.

The main thing to understand is what all information is captured by the audit log. We know that we can audit for several actions but if we do not know what kind of information it gathers that it is of no use. So first let us understand that what kind of information that audit log actually stores.

(1) Item name and Location
(2) Site from which that item change happened
(3) Type of the item, is it a list item or is it a document library item
(4) User ID for this event
(5) Date and time, when it happened
(6) Which action is actually taken on that item

These are the important parameters that log returns us and we need to analyze them.

But the most important thing to take in mind is auditing tells you that item is changed, it does not say anything about what is changed, so it should not be mislead with versioning of the item.

We will have an option from there to download the report of the audit. That report itself requires several practices to learn and understand what to read and how to read it. We also will see that once we generate report for each property.

Let’s start our practical tour to understand this feature in more detail.

Go to the document library. I am going to use my same document library which I used in my previous three posts. Go to Document Library Settings ->Information Management Policy feature->Define a Policy.

Select Enable auditing and check the first option as I am going to demonstrate you each option in it one by one. So for now check the first option which is Opening or downloading documents, viewing items in lists, or viewing item properties.



Before proceeding ahead I would like to introduce one more important thing is that apart from handling different auditing for individual items in document library, you can also audit some of the site level actions like editing content types and columns, search queries used to search the results, editing users and permission at site level. You can also keep track of these actions at a site level too.

Go to top level site action->site settings->modify all site settings->collection administration option-> under it site collection audit settings. But Make sure that this will enable auditing on each item on each list, so there is a maximum chance to run out of your disk space as it will potentially log large information.



Once, we have enabled it I created one document named it audit document and other users have updated its properties and viewed properties and afterwards I generated report from it and I got the following result. To view the report, go to site collection administration and under it view log report. Select content viewing report under first section titled “Content Activity Reports".



In the same way you can check for other items and check the log and analyze the data.


That’s it. This ends the series of Record management feature.