Creating a Data Povider That Reads From an SQL Server Database

Let’s create a data provider that exposes data to C1 CMS from tables in a database (e.g. Northwind) on Microsoft SQL Server.

First of all, create a brand new project, add required assembly references and hook it up with a database:

1. 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).
2. Add a LINQ to SQL file to the project (Add New Item | LINQ to SQL).
3. Create a connection in Server Explorer and add the needed database (e.g. Northwind).
4. Drag and drop the needed tables (for example, Suppliers and Products).

IData Interfaces

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

1. Add a type interface inherited from the IData interface (e.g. ISupplier and IProduct)
2. Add needed properties to represent actual columns in the database. (Properties that you would like to expose in C1 CMS could be a subset of the actual columns in the database, but the key column should be one of them.)
3. Add the required attributes to the type interface and to its properties (see below).
4. Add a class that implements IDataId to hold the value of the key column in the table (e.g. DataId). The class can be shared between tables if the key column is the same type in all tables (see the sample code).
5. Add a base class (e.g. SupplierBase and ProductBase) implementing the type interface from Step 1 (e.g. 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.

Please 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 C1 CMS, please refer to Data Types Using C#.)

DataContextClasses

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

So now do the following:

1. Close the visual part of the LINQ to SQL file (.dbml).
2. Edit the DataContextClasses.
3. 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: Please note that when you open the visual part of the LINQ to SQL file (.dbml), Visual Studio regenerates DataContextClasses, so all your changes here 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:

1. Create a custom data provider class (e.g. NorthwindDataProvider) and have it implement the IDataProvider interface.
2. Add an instance of the DataContextClasses (generated when you add the database to the Server Explorer).
3. 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 C1 CMS

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

1. Copy the project’s DLL to /Bin folder of your website.
2. Edit the /App_Data/Composite/Composite.config file and locate the element: Composite.Data.Plugins.DataProviderConfiguration/DataProviderPlugins
3. 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" />

4. Log in to the CMS Administrative Console and then execute Tools | Restart Server.

(You can use the exposed data types 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 C1NorthwindIntegration project  

The sample C1 CMS Northwind Integration project demonstrates how a read-only data provider exposes two tables (Suppliers and Products) to C1 CMS 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 С1 Administrative console.

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

1. Copy C1NorthwindIntegration.dll to your C1 CMS's /Bin folder.
2. Edit /App_Data/Composite/Composite.config and locate the following element: configuration/Composite.Data.Plugins.DataProviderConfiguration/DataProviderPlugin
3. Add the following configuration element below (just before its end tag </DataProviderPlugins>):

   <add type="C1NorthwindIntegration.NorthwindDataProvider, C1NorthwindIntegration" name="NorthwindDataProvider" />

4. Copy Northwind.xml from the project to your C1 CMS's /App_Data/Composite/TreeDefinitions folder
5. Log in to the CMS Administrative Console and then execute Tools | Restart Server.

To see how it works:

1. Log in to the Administrative console.
2. Switch to the Data perspective.
3. 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.