Simple SQL 2005 MOSS 2007 ADF example

Posted: June 18, 2007 in MOSS 2007

MOSS 2007 ADF (application Definition File)

                One of the most compelling features of MOSS is the search capability. Of those, the ability to hook into any ODBC compliant data store via the business data catalog (BDC) stands WAY out.  While this is an awesome feature, the issue comes in when you try to implement it.  In order to load an external data source into the BDC, you first need to define how to hook into it, how to retrieve data, how to update data, and various other information about the application. Now here is where it gets real fun.

                The problem with this is, while I was able to find a HUGE number of links for ADF, trying to find a simple, “ADF for idiots” type of entry was not so easy.  Generally, if you can show me a simple example of something I can pick it up and run with it like an expert in no time. However such a simple example eluded me. 

                Finally I found a cool ADF generation application. This allowed me to generate a simple ADF file for  extracting data from a single data table in a SQL 2005 database which gave me the insight I needed to now create my own going forward. I will go through this sample ADF file here. To be certain this is a VERY simple example and does not go through everything you can do by any means. Hopefully, if you are new to ADF files, this will give you the boost to get you going.

                See the document at the end of this post for the sample ADF doc. There really are  2 key sections to focus on.

1.       LobSystemInstance – this is located in the LobSystemainstances node and contains the definitions for what and how we are going to connect to the external datasource. In this sample we are hooking into a SQL Server data source. We define the server name, database name, SQL user name, and password. This sample uses SQL Auth but this can also be used for integrated or other authentication methods

2.       Entity – this high level item first defines the name of the table we are accessing “dbo.TableName”. Entity has 2 other sub-sections that are critical

A.      Identifiers

          This section defines that the primary keys is for the table we are accessing.

B.      Methods

          The methods section defines the actions that we can use to perform specific tasks against the data store.  The high level method node defines the name of the task. The Properties and Parameters sub sections further define the information on each task.

a.       Properties

          In the first method for this sample we have 2 properties. One defines the T-SQL text used to pull the data from the data store the other defines the type of command this is (similar to how you set command type in  ADO.Net code.

 

b.      Parameters

          First the individual parameter items define the direction data will be flowing

          Within the parameters section we have individual TypeDescriptor subsections which will define the specific data columns and their associated data types.

The sample I have listed below you can also see the additional method defined for retrieving a specific record from the data table as well as some additional details that are defined.  This sample is specifically for SQL 2005 retrieval using embedded SQL. It is a good start for that purpose.

Now for the sample ADF I created.

<LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance&quot; xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.XSD" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog&quot; Type="Database" Version="1.0.0.0" Name="CLIENTNAME_APPNAMELOBSystem">

  <Properties>

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

  </Properties>

  <LobSystemInstances>

    <LobSystemInstance Name="CLIENTNAME_APPNAMEInstance"><Properties>

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

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

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

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

      <Property Name="RdbConnection User ID" Type="System.String">sql user name</Property>

      <Property Name="RdbConnection Password" Type="System.String">sql password</Property>

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

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

    </Properties>

    </LobSystemInstance>

    </LobSystemInstances>

  <Entities>

    <Entity EstimatedInstanceCount="0" Name="dbo.TableName">

      <Identifiers>

        <Identifier Name="[ID]" TypeName="System.Int32" />

      </Identifiers>

      <Methods>

        <Method Name="Getdbo.[TableName]">

        <Properties>

          <Property Name="RdbCommandText" Type="System.String">Select [ID],[Name],[Description],[APP_Manufacturer],[APPOS],[AppVariant],[AppType],[AppStyle],[ColumnA],[ColumnB],[ColumnC],[ColumnD],[ColumnE],[ColumnF] From dbo.[TableName]</Property>

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

        </Properties>

        <Parameters>

          <Parameter Direction="Return" Name="dbo.[TableName]">

            <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[TableName]DataReader" IsCollection="true">

              <TypeDescriptors>

              <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[TableName]DataRecord">

                <TypeDescriptors>

                  <TypeDescriptor TypeName="System.Int32" IdentifierName="[ID]" Name="ID" />

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

                  <TypeDescriptor TypeName="System.String" Name="Description" />

                  <TypeDescriptor TypeName="System.String" Name="APP_Manufacturer" />

                  <TypeDescriptor TypeName="System.String" Name="APPOS" />

                  <TypeDescriptor TypeName="System.String" Name="AppVariant" />

                  <TypeDescriptor TypeName="System.String" Name="AppType" />

                  <TypeDescriptor TypeName="System.String" Name="AppStyle" />

                  <TypeDescriptor TypeName="System.Int32" Name="ColumnA" />

                  <TypeDescriptor TypeName="System.String" Name="ColumnB" />

                  <TypeDescriptor TypeName="System.String" Name="ColumnC" />

                  <TypeDescriptor TypeName="System.String" Name="ColumnD" />

                  <TypeDescriptor TypeName="System.Boolean" Name="ColumnE" />

                  <TypeDescriptor TypeName="System.Boolean" Name="ColumnF" />

                </TypeDescriptors>

              </TypeDescriptor>

              </TypeDescriptors>

            </TypeDescriptor>

          </Parameter>

        </Parameters>

        <MethodInstances>

          <MethodInstance Name="dbo.[TableName]Finder" Type="Finder" ReturnParameterName="dbo.[TableName]" ReturnTypeDescriptorName="dbo.[TableName]DataReader" ReturnTypeDescriptorLevel="0" /></MethodInstances>

      </Method>

        <Method Name="dbo.[TableName]SpecificFinder">

          <Properties>

            <Property Name="RdbCommandText" Type="System.String">Select [ID],[Name],[Description],[APP_Manufacturer],[APPOS],[AppVariant],[AppType],[AppStyle],[ColumnA],[ColumnB],[ColumnC],[ColumnD],[ColumnE],[ColumnF] From dbo.[TableName] Where ([ID]=@ID)</Property>

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

          </Properties>

          <Parameters>

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

              <TypeDescriptor TypeName="System.Int32" IdentifierName="[ID]" Name="[ID]" />

            </Parameter>

            <Parameter Direction="Return" Name="dbo.[TableName]">

              <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[TableName]DataReader" IsCollection="true">

                <TypeDescriptors>

                <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="dbo.[TableName]DataRecord">

                  <TypeDescriptors>

                    <TypeDescriptor TypeName="System.Int32" IdentifierName="[ID]" Name="ID" />

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

                    <TypeDescriptor TypeName="System.String" Name="Description" />

                    <TypeDescriptor TypeName="System.String" Name="APP_Manufacturer" />

                    <TypeDescriptor TypeName="System.String" Name="APPOS" />

                    <TypeDescriptor TypeName="System.String" Name="AppVariant" />

                    <TypeDescriptor TypeName="System.String" Name="AppType" />

                    <TypeDescriptor TypeName="System.String" Name="AppStyle" />

                    <TypeDescriptor TypeName="System.Int32" Name="ColumnA" />

                    <TypeDescriptor TypeName="System.String" Name="ColumnB" />

                    <TypeDescriptor TypeName="System.String" Name="ColumnC" />

                    <TypeDescriptor TypeName="System.String" Name="ColumnD" />

                    <TypeDescriptor TypeName="System.Boolean" Name="ColumnE" />

                    <TypeDescriptor TypeName="System.Boolean" Name="ColumnF" />

                  </TypeDescriptors>

                </TypeDescriptor>

                </TypeDescriptors>

              </TypeDescriptor>

            </Parameter>

          </Parameters>

          <MethodInstances>

            <MethodInstance Name="dbo.[TableName]SpecificFinder" Type="SpecificFinder" ReturnParameterName="dbo.[TableName]" ReturnTypeDescriptorName="dbo.[TableName]DataReader" ReturnTypeDescriptorLevel="0" />

          </MethodInstances>

        </Method>

      </Methods>

    </Entity>

  </Entities>

</LobSystem>

 

References:

Contagious Curiosity Blog – http://blogs.msdn.com/socaldevgal/archive/2007/03/02/use-the-moss-bdc-get-this-adf-generator.aspx

ADF References – http://msdn2.microsoft.com/en-us/library/ms145931(SQL.90).aspx

Complete ADF Template – http://msdn2.microsoft.com/en-us/library/ms145313(SQL.90).aspx

Minimal ADF Template – http://msdn2.microsoft.com/en-us/library/ms145232(SQL.90).aspx

ADF generation Tool – http://www.bdcmetaman.com/default.aspx

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s