Adding a custom data source to Aggregated Business Calendar

With Feature Pack 1 for Duet Enterprise 1.0 and Duet Enterprise 2.0, you can add a custom data source to your Aggregated Business Calendar Web Part along with the other types of data sources. In this post, we walk you through the steps to create a SQL Server-based data source and configure the Aggregated Business Calendar Web Part to display the data from this data source.

If Feature Pack 1 for Duet Enterprise 1.0 or Duet Enterprise 2.0 is installed on your server, you will have the OBA.Server.Extensions.dll assembly in the global assembly cache (GAC). You will need this assembly for the development of the SQL Server data source.

Create a data source

First, we need to create a data source accessor, which will work as a back end and provide the data in the correct format when the aggregate calendar requests it.

Here’s how you do this:

1. Open Visual Studio, and create a new class library project—for example, Project1.

2. Now add a reference to OBA.Server.Extensions.dll in your project, and use the OBA.Server.Extensions namespace.

3. Now your DataSourceAccessor class must implement the ICalendarAccessor interface. So create a class in your project—for example, SQLDataSource : ICalendarAccessor.

4. This class needs to have the following override methods and properties:

• 1. • Initialize()
     • RetrieveItems()
     • Bool CanDelete
     • String ViewFormURL
     • String CreateFormURL
     • Delete()
     • String UpdateFormURL

5. Here, the most important method is RetrieveItems. This method should return a list of calendarItems.

6. In the Initialize() method, you can perform various tasks specific to the data source, like setting some data source properties—for example, the OWA URL for an Exchange data source, or the SQL Server address for a SQL Server data source.

7. CanDelete represents whether the item can be deleted in the current context or not.

8. ViewFormUrl, CreateFormURL, and EditFormURL will be the URLs for different forms that will be used for create, read, and update operations. You can create your custom forms also. These forms are invoked with two parameters—ID (item ID) and source (URL from where we are invoking the operation).
For reference, see the Custom Data Source project. This project shows how to create a SQL Server data source accessor.

SQL table

For this project, the following table schema is used to store the time entries:

Table 1: Schema for the Time table

Code

Here is the SQLDataAccessor code:

 using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Xml;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using OBA.Server.Extensions;

namespace MS.Internal.Motif.Office.DataSource
{
    public class SQLDataSource : ICalendarAccessor
    {
        // This is the method that will pick the time entries from SQL and 
        //return them as a list of calendar items.

        public IEnumerable<ICalendarItem> RetrieveItems(
            DateTime startTime, 
            DateTime endTime)
        {
            string connectionString = @"Data Source=<data-source>;" + 
                @"Initial Catalog=master;user id=<user>;password=<password>";
            var list = new List<ICalendarItem>();

            // This is the data structure that we will be returning.
            string query = 
                @"SELECT * from Time where (Time.[Start Time] >= '" 
                + startTime + @"' AND Time.[Start Time] <='" + endTime + 
                @"') OR (Time.[End Time] >= '" + startTime + 
                @"' AND Time.[End Time] <='" + endTime + @"')";

            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = connectionString;
            connection.Open();

            // Construct a new SqlCommand object 
            // (links the connection with the query).
            SqlCommand command = new SqlCommand(query, connection);
            // Execute the query and get a data reader.
            SqlDataReader data = command.ExecuteReader();

            // Check if no results come back.
            if (data.HasRows == false)
            {
                data.Close();
                connection.Close();
                return list;
            }

            while (data.Read()) //the .Read method automatically moves
            // The data cursor to the next 'row'.
            {
                var item = new CalendarItem();
                item.Title = HttpUtility.HtmlDecode(data.GetString(0));
                item.Location = HttpUtility.HtmlDecode(data.GetString(1));
                item.StartDate = data.GetDateTime(2);
                item.EndDate = data.GetDateTime(3);
                item.ItemID = data.GetInt32(4).ToString();
                list.Add(item);
            }

            // Close the data reader.
            data.Close();

            // Close the connection.
            connection.Close();

            return list;
        }

        // Method to implement delete functionality.
        public bool Delete(string itemId, out string failureMessage)
        {
            int id;
            if (CanDelete)
            {
                if (Int32.TryParse(itemId, out id))
                {
                    try
                    {
                        failureMessage = @"";
                        string connectionString = @"<connection string>";
                        string query = 
                            @"DELETE from Time WHERE Time.ItemID=" + id;
                        SqlConnection connection = new SqlConnection();
                        connection.ConnectionString = connectionString;
                        connection.Open();

                        // Construct a new SqlCommand object 
                        // (links the connection with the query).
                        SqlCommand command = new SqlCommand(
                                                query, 
                                                connection
                                                );

                        if (command.ExecuteNonQuery() != 1)
                        {
                            failureMessage = 
                                @"Fatal Error while deleting the item";
                            throw new Exception("Could not delete the item");
                        }
                        return true;
                    }

                    catch (Exception ex)
                    {
                        failureMessage = ex.Message;
                        return false;
                    }
                }
            }

            failureMessage = @"Item can't be deleted";
            return false;
        }

        // Represents whether or not delete is allowed 
        // in the current context.
        public bool CanDelete
        {
            get { return true; }
        }

        // URL of Edit Form. The property returns the 
        // relative URL to the edit page.
        public string EditFormUrl
        {
            get { return @"/_layouts/SQLEditForm.aspx"; }
        }

        public string CreateFormUrl
        {
            get { return @"/_layouts/SQLCreateForm.aspx"; }
        }

        public string ViewFormUrl
        {
            get { return @"/_layouts/SQLViewForm.aspx"; }
        }

        // This method can be used to set the additional calendar 
        // data source properties--for example, user credentials
        // to access the SQL data source.
        public void Initialize(IEnumerable<CalendarProperty> l)
        {
            // Nothing to do.
        }
    }
}

9. After building the project, deploy the solution on Web App. Or, you can manually add the DLL into the GAC on the server and add the layouts pages in the Layout folder.

For the demo project, you can just add the built WSPs into the Solutions Gallery and then deploy the solution globally.

Add the data source

The next step is to add this data source into the Aggregate Calendar:

1. Open the Aggregate Calendar page, and click Edit Web Part.

2. In the editor, choose to add a new custom data source.

3. Complete all the required fields. For the configuration XML, enter the following XML code:

 <?xml version="1.0" encoding="utf-8"?>
<!-- Please note that all the fields are mandatory here. 
     For a few fields, you might leave the value as blank,
     but the node must be present  -->
<CalendarDataSource 
    xmlns:i="https://www.w3.org/2001/XMLSchema-instance" 
    xmlns="https://schemas/oba/2010/extensions">
<!-- Here namespace name needs to be same as above. -->
<AssemblyName>
    MS.Internal.Motif.Office.DataSource, 
    Version=1.0.0.0, 
    Culture=neutral, 
    PublicKeyToken=38ed9c5bc913d77a
</AssemblyName>
<!-- Name of the assembly file that we created for the data source. 
     Assembly name should be fully qualified name. -->
<CalendarProperties>
</CalendarProperties>
<!-- You can have additional calendar properties here, 
     which you can use in the Initialize() method 
     of the data source accessor -->
<Color>2</Color>
<!-- Color for the data source items. -->
<DataSourceType>Custom</DataSourceType>
<!-- Please keep the value the same as above (case-sensitive) -->
<Description>custom calendar</Description>
<Id>94b8-4031-8e17-65933ce6ffbd</Id>
<ShowDefault>true</ShowDefault>
<Title>SQL DataSource</Title>
<TypeName>MS.Internal.Motif.Office.DataSource.SQLDataSource</TypeName>
<!-- Typename is the className of the data source which contains the 
     RetreiveItems and other methods. 
     Name is in format <assembly name>.<class name> -->
<Url>https://www.microsoft.com</Url>
<!-- URL to which user will navigate when clicking the data source name. -->
</CalendarDataSource>

4. After providing these values, save the configuration.

Now if all goes well, you should be able to see the items in the mash-up view, and you should be able to perform create, read, update, and delete (CRUD) operations on these items.

Troubleshooting

If you get an error message, you can try these troubleshooting steps:

1. If, while adding the data source, you get an error message stating that it could not access the DataSourceAccessor from the assembly '……', make sure that the fully qualified name of the assembly in the GAC matches the assembly specified in the XML schema.

2. If, while adding the data source, you get an error message that states it could not recognize 'Color', expected 'ID', or some similar message, make sure that the XML schema has the same sequence of the nodes.