How Do I Connect to That?

1. How Do I Connect to That? Becky Bertram SharePoint MVP, Microsoft Certified Trainer MCSD, MCTS, MSPD, MSITP www.beckybertram.com Twitter: @beckybertram

2. 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

3. Options for Data Connection from SharePoint ADO.NET LINQ to SQL ADO.NET Entity Model SharePoint BCS

4. 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

5. 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(); DataTabledt = new DataTable(); dt.Load(reader); reader.Close();

6. 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

7. 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

8. LINQ to SQL Sample CustomersDataContextdc = new CustomersDataContext(“DataSource=SERVER;Initial Catalog=OrderMgmt;IntegratedSecurity=SSPI”); IQueryable<Customer> customers = from Customer c in dc.Customers where c.Name == "Hot Dog Heaven" select c; Customer customer = customers.First<Customer>(); intcustomerId = customer.ID;

9. 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

10. SQL Diagram vs Entity Model

11. 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 de-coupled model)

12. 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

13. 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.

14. SPWebConfigModification SPConfigModificationconfigMod = 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();

15. 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.

16. 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.

17. 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

18. BCS Authentication Choices

19. 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.

20. 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.

21. 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

22. 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>

23. Sample Entity Cont’d <Parameters> <Parameter Direction="In" Name="@ProductID"> <TypeDescriptorTypeName="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>

24. 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.

25. 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>

26. 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

27. Connectors Web Service/WCF Service SQL Server .NET Connectivity Component Custom

28. 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

29. .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

30. 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 .NETclasses which use an Entity Model you’ve created to return values BCS to .NET classes that use LINQ to SQL to return values

31. 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

32. 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