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"">
<TypeDescriptors>
<TypeDescriptor Name=""Tables"" TypeName=""System.Data.DataTableCollection, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"" IsCollection=""true"">
<TypeDescriptors>
<TypeDescriptor Name=""Table"" TypeName=""System.Data.DataTable, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"" >
<TypeDescriptors>
<TypeDescriptor Name=""Rows"" TypeName=""System.Data.DataRowCollection, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"" IsCollection=""true"">
<TypeDescriptors>
<TypeDescriptor Name=""DataRow"" TypeName=""System.Data.DataRow, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"">
<TypeDescriptors>
<TypeDescriptor Name=""field1"" TypeName=""System.String"" IdentifierName=""f1"" />
<TypeDescriptor Name=""field2"" TypeName=""System.String"" />
<TypeDescriptor Name=""field3"" TypeName=""System.String"" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>

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">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="entityDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Int32" IdentifierName="entityPK" Name="entityPK" />
<TypeDescriptor TypeName="System.DateTime" Name="timestamp" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>

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">
<Properties>
<Property Name="Title" Type="System.String">entityName</Property> <Property Name="__BdcLastModifiedTimestamp" Type="System.String">timestamp</Property>
</Properties>

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:

<LobSystemInstances>
<LobSystemInstance Name="LOBSystemInstanceName">
<Properties>
<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>
</Properties>
</LobSystemInstance>

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