Transcript How Do I Connect to That

How Do I Connect to That?
Becky Bertram
SharePoint MVP, Microsoft Certified Trainer
MCSD, MCTS, MSPD, MSITP
www.beckybertram.com
Twitter: @beckybertram
Some Reasons for Connecting to
External Data
 Display information from external systems in UI, such
as web parts, application pages, etc.
 Allow users to interact with external data in
SharePoint list format
 Crawl back-end data sources so they show up in
search results
 Import external data into user profiles
Options for Data Connection from
SharePoint
 ADO.NET
 LINQ to SQL
 ADO.NET Entity Model
 SharePoint BCS
ADO.NET
 Use ADO.NET object model to connect to
back-end data sources:
 Connection, Command, Parameter objects
 ExecuteScalar(), DataReader.Read() methods
 DataTable, DataSet, DataAdapter objects
Sample Code
SqlConnection conn = new SqlConnection()
conn.ConnectionString = “DataSource=SERVER;Initial
Catalog=OrderMgmt;Integrated Security=SSPI”;
SqlCommand command = new SqlCommand(“sp_GetOrders”, conn);
conn.Open();
SqlDataReader reader = command.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
reader.Close();
What is LINQ?
 A way of using strongly typed classes to query
back-end data sources using a T-SQL-like syntax
 Examples of LINQ:
 LINQ to SQL
 LINQ to XML
 LINQ to SharePoint
What is LINQ to SQL?
 Strongly typed classes generated by the
SqlMetal.exe tool
 Visual Studio allows you to add LINQ to SQL
project types to your project
 VS gives you a visual interface that dynamically
generates classes behind the scenes for you
 DataContext class provides entry point for code
LINQ to SQL Sample
CustomersDataContext dc = new
CustomersDataContext(“DataSource=SERVER;Initial
Catalog=OrderMgmt;Integrated Security=SSPI”);
IQueryable<Customer> customers = from Customer c
in dc.Customers
where c.Name == "Hot Dog Heaven"
select c;
Customer customer = customers.First<Customer>();
int customerId = customer.ID;
What is the ADO.NET Entity Model?
 An entity is a generic term for something that has properties
and instructions for performing actions
 A database table has columns (properties) and uses SQL
statements or stored procedures to carry out CRUD operations
 An Excel spreadsheet has rows and columns and uses functions
to carry out operations
 An object model has classes with properties and methods to
carry out operations
 The ADO.NET Entity model allows you to create an object
model that mirrors your application logic and not necessarily
your data source
SQL Diagram vs Entity Model
Entity Models and Visual Studio
 VS 2010 gives you a nice interface for creating Entity Models
 VS gives you two automatic options:
 Create an entity model by hand, then have VS create a SQL
script that will generate a database that mirrors your
entity model
 Connect to a database and have an entity model created
automatically that reflects the DB design
 You also can create an entity model and manually wire
entities up to stored procedures (thus creating a more decoupled model)
LINQ and Entity Models
 It is possible to use LINQ with an Entity Model,
i.e. the LINQ statements returns Entity Model
objects
 The Entity Model itself isn’t usually tied to
LINQ to SQL
Managing Application Configuration
 All three of these models require connection strings to connect
to the backend database
 In typical ASP.NET applications, connection strings are stored in
the web.config file so they can be swapped out easily in each
new environment (Dev, QA, Prod, etc.)
 SharePoint WFE can be load balanced, so it’s not recommended
to modify the web.config by hand
 SharePoint object model provides a way of programmatically
modifying the web.config file on multiple WFEs.
SPWebConfigModification
SPConfigModification configMod = new SPWebConfigModification();
configMod.Name = “add”;
configMod.Owner = “sp_serviceAccount”;
configMod.Sequence = 1;
configMod.Type = SPWebConfigModification.SPWebConfigModificationType.EnsureChildNode;
configMod.Value = “DataSource=SERVER;Initial Catalog=OrderMgmt;Integrated Security=SSPI”;
webApp.WebConfigModifications.Add(configMod);
webApp.WebService.ApplyWebConfigModifications();
Environment-specific Connection
Strings
 SPWebConfigModification solution requires writing your connection
string in code; defeats the purpose of using the web.config
 Other storage option: store the connection string in SharePoint itself
 Store in the property bag of a SPWebApplication, SPSite, or SPWeb
object
 Plus: easily accessible via code; can be different for each environment
 Minus: No user interface out of the box; UI for updating those values must be
written
 Store in a SharePoint list
 Plus: UI already built; security, change management, etc. there already
 Minus: Not so good performance; list couple be deleted more easily than
accidentally deleting a property in the property bag, potentially
 SharePoint Patterns and Practices:
 Variation of property bag
 Allows you to set config values higher up in the hierarchy and then query
them within a lower context; no hardcoded to setting values specifically in an
object such as a web application, site collection, etc.
Connection Context
 Best practice is to connect to SQL using a trusted
connection (as opposed to using SQL authentication)
 This means users will be connecting to the back-end
database using the SharePoint web application’s
application pool process identity. You must grant that
account the proper permissions in the external
database.
What are Business Connectivity
Services (BCS)?
 A means of connecting SharePoint 2010 and Office
applications to external data sources.
 The term BCS refers to the connectivity components, tools,
and user interface components such as web parts, that
combine to make integration with external systems possible.
 Business Data Connectivity (BDC) is refers to just the
connectivity runtime. (Business Data Catalog is the
SharePoint 2007 term for the BCS.)
 Unlike SP2007 that only allowed Read functionality, BCS
allows standard CRUD operations on the external system.
 Can be used to search external data sources
 Can be used as a data source for the User Profile Service
BCS Authentication Choices
Authentication Type
Kerberos
or NTLM
Description
Passthrough
Kerberos
Uses credentials of current user
Passthrough
NTLM
Uses credentials of app pool account or anonymous user account
RevertToSelf
Uses credentials of the app pool
WindowsCredentials
BCS uses SSS credentials as Windows credentials. Must pass in
Target Application name.
RdbCredentials
Passes in username and password to a DB, appended to the
connection string (such as when using SQL Authentication). Must
pass in Target Application name
Credentials
Used to access web services that don’t support Windows
authentication, but use Basic or Digest authentication. Must pass
in Target Application name
Application-Level
Authentication
Passes credentials along with parameters when carrying out a
Read operation on external data source.
Secure Store Service
 Provides the ability to map credentials of SharePoint users or
groups to user accounts used for accessing external systems.
 Can specify if each user gets mapped to an individual account,
or if all the users in a given group map to a single account.
 Each SSS entry has an “Application Name”. It’s possible to
create BCS connections that use the credentials stored with a
particular Application Name. This means you could create the
same Application Name in your different environments, but
use different credentials. Your BCS model wouldn’t need to
change.
External Content Types (ECT)
 Also known as an Entity in Visual Studio, to be consistent with the
BCS object model.
 Similar to an object with properties when referring to object
models, or tables with columns when referring to databases.
 ECT could be something like “Customer”, “Product”, etc.
 ECT is like a traditional content type in that includes a collection
of metadata to describe an item. Storage mechanism for ECT is
totally different than a traditional SharePoint content type.
 ECTs can have Associations, which is the equivalent of Entities
having Foreign Keys with each other in a relational database.
Metadata Model
 Contains:
 ECT definitions
 ECT associations
 ECT security permissions
 Created using:





SPD
Visual Studio
3rd Party Tools
XML Editor
Programmatically using BCS Administration object model
Sample Entity from BDC Model
<Entities>
<Entity Namespace="http://sandbox.sp2010.com/sites/bdc" Version="1.0.0.0"
EstimatedInstanceCount="10000" Name="Product" DefaultDisplayName="Product">
<Properties>
<Property Name="Title" Type="System.String">Name</Property>
<Property Name="ExcludeFromOfflineClientForList" Type="System.Boolean">true</Property>
</Properties>
<Identifiers>
<Identifier TypeName="System.Int32" Name="ProductID" />
</Identifiers>
<Methods>
<Method IsStatic="false" Name="DeleteProduct">
<Properties>
<Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">StoredProcedure</Property>
<Property Name="RdbCommandText"
Type="System.String">[dbo].[DeleteProduct]</Property>
<Property Name="BackEndObjectType" Type="System.String">SqlServerRoutine</Property>
<Property Name="BackEndObject" Type="System.String">DeleteProduct</Property>
<Property Name="Schema" Type="System.String">dbo</Property>
</Properties>
Sample Entity Cont’d
<Parameters>
<Parameter Direction="In" Name="@ProductID">
<TypeDescriptor TypeName="System.Nullable`1[[System.Int32, mscorlib,
Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]"
IdentifierName="ProductID" Name="@ProductID" />
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Type="Deleter" Name="DeleteProduct" DefaultDisplayName="Delete
Product">
</MethodInstance>
</MethodInstances>
</Method>
</Entity>
</Entities>
BCS Resource Files
 Resource files can be used to upload unique language
values, security settings, and connection values
 You can export a BCS metadata model to XML, import
that model to a different model, then import an
additional resource file in each new environment.
That resource file could contain server-specific
connection info.
Sample Resource File
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Model xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog BDCMetadata.xsd" Name="BCS Product"
xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog/Resources">
<AccessControlList>
<AccessControlEntry Principal="sp2010\administrator">
<Right BdcRight="Edit" />
<Right BdcRight="Execute" />
<Right BdcRight="SetPermissions" />
<Right BdcRight="SelectableInClients" />
</AccessControlEntry>
</AccessControlList>
<LobSystems>
<LobSystem Name="BCS Test">
<LobSystemInstances>
<LobSystemInstance Name="BCS Test">
<Properties>
<Property Name="AuthenticationMode" Type="System.String">WindowsCredentials</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">WIN-28QOC9KSJHL</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">BCSTest</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
<Property Name="RdbConnection Pooling" Type="System.String">True</Property>
<Property Name="SsoProviderImplementation"
Type="System.String">Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService,
Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
<Property Name="SsoApplicationId" Type="System.String">BDC</Property>
<Property Name="ShowInSearchUI" Type="System.String"></Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
</LobSystem>
</LobSystems>
</Model>
External List
 A SharePoint list that was created using the definition
defined in an ECT.
 Not every ECT needs to have an External List. An EL is simply
an embodiment of the ECT in SharePoint, but it’s not
necessary to have an EL to access an ECT. It’s also possible to
use the BDC object model to access External Data directly,
without having an EL.
 EL forms can either use a standard List View Web Part or they
can be generated using InfoPath.
 Once data is in an EL, programmers can program against list
items using the standard SharePoint object model
Connectors




Web Service/WCF Service
SQL Server
.NET Connectivity Component
Custom
SQL Connector
 You can use SharePoint Designer’s tools to create
your BDC model
 You can auto-generate SQL queries or specify stored
procedures that need to be executed
.NET Connectors
 Your BCS model can connect to .NET objects in an assembly, and
those objects can handle doing the CRUD operations
 Visual Studio allows you to create a BCS model using a project
item template
 Your BCS model is tied to .NET classes in VS. You can write
custom code in the methods you define (typically CRUD
methods) that access your ADO.NET entities or your LINQ to
SQL DataContext objects.
 You could create a web service that exposes your entity model
or LINQ to SQL classes and connect your BCS model to it
Summary: Connection Options
 ADO.NET object model connecting directly to DB
 ADO.NET Data classes that use LINQ to retrieve content
from DB
 ADO.NET Entity Model that’s configured to connect to a
back-end DB
 BCS to SQL
 BCS to Web Service/WCF
 BCS to .NET classes which use an Entity Model you’ve
created to return values
 BCS to .NET classes that use LINQ to SQL to return values
Configuration Options
 ADO.NET, Entity Model, and LINQ to SQL:
 Connects to back-end database using SharePoint Web
Application IIS application pool identity
 Store connection string in web.config using
SPWebConfigModification or within SharePoint itself (using
property bag, with or without the aid of the Patterns and
Practices code library; or in a SharePoint list)
 BCS:
 Store credentials right in the model or store credentials using
the Secure Store Service
 Change configuration in each environment by uploading
Resource files associated with the BCS model
Resources
 ADO.NET Entity Framework on MSDN:
http://msdn.microsoft.com/en-us/library/bb399572.aspx

Wrox Professional Business Connectivity
Services in SharePoint 2010
 Secure Store Service on MSDN:
http://msdn.microsoft.com/en-us/library/ee557754.aspx
 LINQ to SQL on MSDN:
http://msdn.microsoft.com/en-us/library/bb386976.aspx
 Managing Application Configuration on MSDN:
http://msdn.microsoft.com/en-us/library/ee413935.aspx
 BCS Meta Man by Lightning Tools:
 http://lightningtools.com/bcs/bcs-meta-man.aspx
 SharePoint Property Bag Settings 2010:
http://pbs2010.codeplex.com