Reading SQL Server data

Creating a Data Provider That Reads From an SQL Server Database

EXPERIENCE MANAGEMENT > DATA

The following topic explains how to create a data provider that reads from an SQL server database, Create a brand-new project, add required assembly references and hook it up with a database.

In Visual Studio, create a new project and add a reference to Composite.dll, and other ones required in the project (most likely, Microsoft.Practices.EnterpriseLibrary.Common.dll and System.Configuration).

Add a LINQ to SQL file to the project (Add New Item | LINQ to SQL).

Create a connection in Server Explorer and add the needed database (for example, Northwind).

Drag and drop the required tables (for example, Suppliers and Products).

IData Interfaces

Next, for each table added to the project (Step 4 above):

Add a type interface inherited from the IData interface (for example. ISupplier and IProduct)

Add required properties to represent actual columns in the database. (Properties that you would like to expose in could be a subset of the actual columns in the database, but the key column should be one of them.)

Add the required attributes to the type interface and to its properties (see below).

Add a class that implements IDataId to hold the value of the key column in the table (for example, DataId). The class can be shared between tables if the key column is the same type in all tables (see the sample code).

Add a base class (for example. SupplierBase and ProductBase) implementing the type interface from Step 1 (for example ISupplier or IProduct) that only handles the DataSourceId property.

As to the base class for handling DataSourceId (Step 5), it is convenient to add such a class that implements the table’s IData interface. When or if the DataContextClasses are regenerated, it will be easy to add this functionality.

Note that this class should only implement the DataSourceId property using the provider (see further below) to get the DataContext instance (see the sample code).

For Step 3, these are the attributes you should add to the type interfaces and their properties:

Type attributes:

•

KeyPropertyName

•

ImmutableTypeId

•

DataScope

•

DataAncestorProvider

•

NotReferenceableAttribute

Property attributes:

•

StoreFieldType

•

ImmutableFieldId

For more information on creating type interfaces in Experience Management, see datatypes Using C#.

DataContextClasses

1. When you add tables to the LINQ to SQL file (.dbml), Visual Studio generates DataContextClasses (.designer.cs), one class for each added table.

Close the visual part of the LINQ to SQL file (.dbml).

Edit the DataContextClasses.

Make each class generated for every table to implement the corresponding IData-based interface and inherit from the corresponding base class.

For example: public partial class Supplier : SupplierBase, ISupplier, INotifyPropertyChanging, INotifyPropertyChanged

Important: When you open the visual part of the LINQ to SQL file (.dbml), Visual Studio regenerates DataContextClasses, so all your changes are lost.

If you do open the visual part, make sure that the DataContextClasses should implement the proper interfaces and inherit from the proper classes making changes as described above.

You should also remove the parameterless constructor from the generated DataContextClasses (and optionally delete app.config and settings from the project.)

Data Provider

Next, create the DataProvider class:

Create a custom data provider class (for example. NorthwindDataProvider) and have it implement the IDataProvider interface.

Add an instance of the DataContextClasses (generated when you add the database to the Server Explorer).

Implement the GetData<>() and GetData<>(IDataId)methods and use the DataContextClasses instance for that. This instance is also accessed from the base classes (see above: "IDate Interfaces", Step 5).

The provider should cast the Table<> on the DataContextClasses to IQueryable<IData> and return (see the sample code).

Adding Custom Data Provider to Experience Manager

Once you have created your custom data provider, build the project and do the following:

Copy the project’s DLL to /Bin folder of your website.

Edit the /App_Data/Composite/Composite.config file and locate the element: Composite.Data.Plugins.DataProviderConfiguration/DataProviderPlugins

Add the configuration element below (just before its end tag </DataProviderPlugins>) specifying its type and name. For example: <add type="C1NorthwindIntegration.NorthwindDataProvider, C1NorthwindIntegration" name="NorthwindDataProvider" />

(You can use the exposed datatypes in console applications via XML tree definition files. If so, create a tree definition file and copy it to /App_Data/Composite/TreeDefinitions.)

Sample Code

Download the sample code here.

The sample Experience Management Northwind Integration project demonstrates how a read-only data provider exposes two tables (Suppliers and Products) to Experience Management from the Microsoft's demo Northwind database. (If the Northwind database is not installed on your SQL server, search the web for "instnwnd.sql" and install it.)

The project also comes with the tree definition file that presents the exposed data in the CMS Administrative console.

Once the project is created, do the following (the naming from the sample project is used - change accordingly):

Copy C1NorthwindIntegration.dll to your CMS's /Bin folder.

Edit /App_Data/Composite/Composite.config and locate the following element: configuration/Composite.Data.Plugins.DataProviderConfiguration/DataProviderPlugin

Add the following configuration element below (just before its end tag </DataProviderPlugins>) <add type="C1NorthwindIntegration.NorthwindDataProvider, C1 CMSNorthwindIntegration" name="NorthwindDataProvider" />

Copy Northwind.xml from the project to your CMS's /App_Data/Composite/TreeDefinitions folder

To see how it works:

•

Switch to the Data module.

•

Locate Northwind in the tree and expand it.

You will see products from the demo Northwind database grouped by their suppliers. The items are read-only.