Sample BDC Model: Connecting to a SQL Server Database

Applies to: SharePoint Server 2010

This is an example of a simple model of an external system of type Database. It demonstrates how to use a set of LobSystem and LobSystemInstance properties to configure connection to a database.

Example

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Model Name="ExampleApplicationDefinition" xmlns="https://schemas.microsoft.com/windows/2007/BusinessDataCatalog">
  <LobSystems>
    <LobSystem Name="ExampleCRM" Type="Database">
      <Properties>
        <!-- This database uses % symbol for the LIKE operator. -->
        <Property Name="WildcardCharacter" Type="System.String">%</Property>
      </Properties>
      <LobSystemInstances>
        <LobSystemInstance Name="ExampleServer">
          <Properties>
            <Property Name="AuthenticationMode" Type="System.String">PassThrough</Property>
            <!-- The database instance is named ExampleSQL and is running on the same computer as the application using this Model. -->
            <Property Name="RdbConnection Data Source" Type="System.String">(local)\ExampleSQL</Property>
            <!-- The database to be used is named ExampleDB. -->
            <Property Name="RdbConnection Initial Catalog" Type="System.String">ExampleDB</Property>
            <!-- The database instance is using integrated security. -->
            <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
      <Entities>
        <Entity Name="Customer" Namespace="example.com" Version="1.0.0.0">
          <Identifiers>
            <Identifier Name="CustomerIdentifier" TypeName="System.Int32" />
          </Identifiers>
          <Methods>
            <Method Name="GetCustomers">
              <Properties>
                <!-- sp_getCustomer is the name of the stored procedure that implements this method. -->
                <Property Name="RdbCommandText" Type="System.String">sp_GetCustomers</Property>
                <!-- Using stored procedures is a best practice when connecting to database systems. -->
              <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">StoredProcedure</Property>
              </Properties>
              <Parameters>
                <!-- The parameter name @CustomerId as expected by the stored procedure. -->
                <Parameter Name="@CustomerId" Direction="In">
                  <!-- The IdentifierName attribute is required here. -->
                  <TypeDescriptor Name="Id" TypeName="System.Int32" IdentifierName="CustomerIdentifier" />
                </Parameter>
                <Parameter Name="Customers" Direction="Return">
                <!-- This SQL command returns a result set containing precisely one row, but it is still wrapped by a data reader and appears as a collection. -->
                  <TypeDescriptor Name="CustomerDataReader" TypeName="System.Data.SqlClient.SqlDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true">
                    <TypeDescriptors>
                      <!-- This is a data record corresponding to a single row of the result set. -->
                      <TypeDescriptor Name="Customer" TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">
                        <TypeDescriptors>
                          <!-- The IdentifierName attribute is required here to associate the field with the Identifier. -->
                          <TypeDescriptor Name="Id" TypeName="System.Int32" IdentifierName="CustomerIdentifier" />
                          <TypeDescriptor Name="FirstName" TypeName="System.String" />
                          <TypeDescriptor Name="LastName" TypeName="System.String" />
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </Parameter>
              </Parameters>
              <MethodInstances>
                <!-- The ReturnParameterName and ReturnTypeDescriptor are pointing to the data record to be returned. The result set that is wrapping it is not important and can be ignored when processing results. -->
                <MethodInstance Name="GetCustomer" Type="SpecificFinder" ReturnParameterName="Customers" ReturnTypeDescriptorPath="CustomerDataReader[0]"/>
              </MethodInstances>
            </Method>
          </Methods>
        </Entity>
      </Entities>
    </LobSystem>
  </LobSystems>
</Model>