ICalendarFile Interface

IT Hit WebDAV Classes Reference

Represents calendar file.

Namespace: ITHit.WebDAV.Server.CalDav
Assembly: ITHit.WebDAV.Server (in ITHit.WebDAV.Server.dll) Version: 13.3.13068

Syntax

C++

Copy

public interface ICalendarFile : ICalendarItem, 
	IFile, IHierarchyItem, IHierarchyItemBase, IContent

Public Interface ICalendarFile
	Inherits ICalendarItem, IFile, IHierarchyItem, IHierarchyItemBase, 
	IContent

public interface class ICalendarFile : ICalendarItem, 
	IFile, IHierarchyItem, IHierarchyItemBase, IContent

type ICalendarFile =  
    interface
        interface ICalendarItem
        interface IFile
        interface IHierarchyItem
        interface IHierarchyItemBase
        interface IContent
    end

The ICalendarFile type exposes the following members.

Properties

	Name	Description
	ContentLength	Gets the size of the file content in bytes. (Inherited from IContent.)
	ContentType	Gets the media type of the file. (Inherited from IContent.)
	Created	Gets the creation date of the item in repository expressed as the coordinated universal time (UTC). (Inherited from IHierarchyItemBase.)
	Etag	Gets entity tag - string that identifies current state of resource's content. (Inherited from IContent.)
	Modified	Gets the last modification date of the item in repository expressed as the coordinated universal time (UTC). (Inherited from IHierarchyItemBase.)
	Name	Gets the name of the item in repository. (Inherited from IHierarchyItemBase.)
	Path	Unique item path in the repository relative to storage root. (Inherited from IHierarchyItemBase.)

Top

Methods

	Name	Description
	CopyToAsync	Creates a copy of this item with a new name in the destination folder. (Inherited from IHierarchyItem.)
	DeleteAsync	Deletes this item. (Inherited from IHierarchyItem.)
	GetPropertiesAsync	Gets values of all properties or selected properties for this item. (Inherited from IHierarchyItem.)
	GetPropertyNamesAsync	Gets names of all properties for this item. (Inherited from IHierarchyItem.)
	MoveToAsync	Moves this item to the destination folder under a new name. (Inherited from IHierarchyItem.)
	ReadAsync	Reads the file content from the repository and writes it to the specified stream. (Inherited from IContent.)
	UpdatePropertiesAsync	Adds, modifies and removes properties for this item. (Inherited from IHierarchyItem.)
	WriteAsync	Saves the content of the file from the specified stream to the storage repository. (Inherited from IContent.)

Top

Remarks

Items of this type will return content returned from ReadAsync(Stream, Int64, Int64) method in calendar-data CalDAV property. This is the marker interface, it does not provide any methods or properties in addition to methods and properties provided by IFile and ICalendarItem interfaces. Calendar file contains definition of the calendar resource in the iCalendar format.

Examples

The code below is part of 'CalDAVServer.SqlStorage.AspNet' C# & VB samples provided with the SDK.

C++

Copy

// +--- Calendar file [UID1].ics ---------+
// |                                      |
// |  +-- Time zone component ---------+  | -- Time zones are are not stored in DB, they are generated automatically 
// |  | TZID: Zone X                   |  |    during serialization, based on TZIDs found in event or to-do.
// |  | ...                            |  |
// |  +--------------------------------+  |
// |                                      |
// |  +-- Time zone component ---------+  |
// |  | TZID: Zone Y                   |  | -- Time zone IDs could be either IANA (Olson) IDs or System (Windows) IDs.
// |  | ...                            |  |
// |  +--------------------------------+  |
// |  ...                                 |
// |                                      |
// |                                      |
// |  +-- Event component -------------+  | -- Event / do-do components are stored in [cal_EventComponent] table.
// |  | UID: [UID1]                    |  |    
// |  | RRULE: FREQ=DAILY              |  | 
// |  | SUMMARY: Event A               |  |
// |  | ...                            |  |
// |  +--------------------------------+  |
// |                                      |
// |  +-- Event component -------------+  | -- In case of recurring events/to-dos there could be more than one component
// |  | UID: [UID1]                    |  |    per file. All event/to-do components within a single calendar file share
// |  | RECURRENCE-ID: 20151028        |  |    the same UID but have different RECURRENCE-IDs. 
// |  | SUMMARY: Instance 5 of Event A |  |    
// |  | ...                            |  |    iOS / OS X UIDs are case sensitive (uppercase GUIDs).
// |  +--------------------------------+  |    Bynari WebDAV Collaborator for MS Outlook UIDs are over 100 chars in length.
// |  ...                                 |
// |                                      |
// |                                      |
// +--------------------------------------+
// 
// 
// 
//    +-- Event component -------------+
//    |                                |
//    | UID: [UID1]                    | 
//    | SUMMARY: Event A               |
//    | START: 20151016T080000         |
//    | RRULE: FREQ=DAILY              |
//    | ...                            |
//    |                                |
//    | EXDATE: 20151018T080000        | -- Recurrence exception dates are stored in [cal_RecurrenceException] table.
//    | EXDATE: 20151020T080000        |
//    | ...                            |
//    |                                |
//    | ATTENDEE: mail1@server.com     | -- Attendees are stored in [cal_Attendee] table.
//    | ATTENDEE: mail2@srvr.com       |
//    | ...                            |
//    |                                |
//    | ATTACH: /9j/4VGuf+Sw...        | -- Attachments are stored in [cal_Attachment] table.
//    | ATTACH: https://serv/file.docx |
//    | ...                            |
//    |                                |
//    |  +-- Alarm Component -------+  | -- Alarms are stored in [cal_Alarm] table.
//    |  | ACTION: DISPLAY          |  |
//    |  | ...                      |  |
//    |  +--------------------------+  |
//    |                                |
//    |  +-- Alarm Component -------+  |
//    |  | ACTION: EMAIL            |  |
//    |  | ...                      |  |
//    |  +--------------------------+  |
//    |  ...                           |
//    |                                |
//    +--------------------------------+

public class CalendarFile : DavHierarchyItem, ICalendarFile
{
    public static string Extension = ".ics";

    public static async Task<IEnumerable<ICalendarFile>> LoadByCalendarFolderIdAsync(DavContext context, Guid calendarFolderId, PropsToLoad propsToLoad)
    {
        // propsToLoad == PropsToLoad.Minimum -> Typical GetChildren call by iOS, Android, eM Client, etc CalDAV clients
        // [Summary] is typically not required in GetChildren call, 
        // they are extracted for demo purposes only, to be displayed in Ajax File Browser.

        // propsToLoad == PropsToLoad.All -> Bynari call, it requires all props in GetChildren call.

        if (propsToLoad != PropsToLoad.Minimum)
            throw new NotImplementedException("LoadByCalendarFolderIdAsync is implemented only with PropsToLoad.Minimum.");

        string sql = @"SELECT * FROM [cal_CalendarFile] 
                       WHERE [CalendarFolderId] = @CalendarFolderId
                       AND [CalendarFolderId] IN (SELECT [CalendarFolderId] FROM [cal_Access] WHERE [UserId]=@UserId)

                      ;SELECT [UID], [Summary] FROM [cal_EventComponent] 
                       WHERE [UID] IN (SELECT [UID] FROM [cal_CalendarFile] 
                                       WHERE [CalendarFolderId] = @CalendarFolderId)";

        sql = string.Format(sql, GetScPropsToLoad(propsToLoad));

        return await LoadAsync(context, sql,
              "@UserId"             , context.UserId
            , "@CalendarFolderId"   , calendarFolderId);
    }

    public static async Task<IEnumerable<ICalendarFile>> LoadByUidsAsync(DavContext context, IEnumerable<string> uids, PropsToLoad propsToLoad)
    {
        // Get IN clause part with list of file UIDs for SELECT.
        string selectIn = string.Join(", ", uids.Select(a => string.Format("'{0}'", a)).ToArray());

        string sql = @"SELECT * FROM [cal_CalendarFile] 
                       WHERE [UID] IN ({1})
                       AND [CalendarFolderId] IN (SELECT [CalendarFolderId] FROM [cal_Access] WHERE [UserId]=@UserId)";

        if(propsToLoad==PropsToLoad.All)
        {
            // Here we do not select attachments content because it could be very large,
            // we only set [ContentExists] flag marking that it should be loaded during IContent.ReadAsync call.
            sql += @";SELECT * FROM [cal_EventComponent]      WHERE [UID] IN ({1})
                     ;SELECT * FROM [cal_RecurrenceException] WHERE [UID] IN ({1})
                     ;SELECT * FROM [cal_Alarm]               WHERE [UID] IN ({1})
                     ;SELECT * FROM [cal_Attendee]            WHERE [UID] IN ({1})
                     ;SELECT * FROM [cal_CustomProperty]      WHERE [UID] IN ({1})
                     ;SELECT [AttachmentId], [EventComponentId], [UID], [MediaType], [ExternalUrl], 
                         (CASE WHEN [Content] IS NULL THEN 0 ELSE 1 END) AS [ContentExists] 
                               FROM [cal_Attachment]          WHERE [UID] IN ({1})";
        }

        sql = string.Format(sql, GetScPropsToLoad(propsToLoad), selectIn);

        return await LoadAsync(context, sql, "@UserId", context.UserId);
    }

    private static async Task<IEnumerable<ICalendarFile>> LoadAsync(DavContext context, string sql, params object[] prms)
    {
        IList<ICalendarFile> items = new List<ICalendarFile>();

        Stopwatch stopWatch = Stopwatch.StartNew();

        using (SqlDataReader reader = await context.ExecuteReaderAsync(sql, prms))           
        {
            DataTable calendarFiles = new DataTable();
            calendarFiles.Load(reader);

            DataTable eventComponents = new DataTable();
            if (!reader.IsClosed)
                eventComponents.Load(reader);

            DataTable recurrenceExceptions = new DataTable();
            if (!reader.IsClosed)
                recurrenceExceptions.Load(reader);

            DataTable alarms = new DataTable();
            if (!reader.IsClosed)
                alarms.Load(reader);

            DataTable attendees = new DataTable();
            if (!reader.IsClosed)
                attendees.Load(reader);

            DataTable customProperties = new DataTable();
            if (!reader.IsClosed)
                customProperties.Load(reader);

            DataTable attachments = new DataTable();
            if (!reader.IsClosed)
                attachments.Load(reader);


            stopWatch.Stop();
            context.Engine.Logger.LogDebug(string.Format("SQL took: {0}ms", stopWatch.ElapsedMilliseconds));


            foreach (DataRow rowCalendarFile in calendarFiles.Rows)
            {
                DataRow[] rowsEventComponents      = new DataRow[0];
                DataRow[] rowsRecurrenceExceptions = new DataRow[0];
                DataRow[] rowsAlarms               = new DataRow[0];
                DataRow[] rowsAttendees            = new DataRow[0];
                DataRow[] rowsCustomProperties     = new DataRow[0];
                DataRow[] rowsAttachments          = new DataRow[0];

                string uid = rowCalendarFile.Field<string>("UID");

                string filter = string.Format("UID = '{0}'", uid);

                if (eventComponents.Columns["UID"] != null)
                    rowsEventComponents = eventComponents.Select(filter);
                if (recurrenceExceptions.Columns["UID"] != null)
                    rowsRecurrenceExceptions = recurrenceExceptions.Select(filter);
                if (alarms.Columns["UID"] != null)
                    rowsAlarms = alarms.Select(filter);
                if (attendees.Columns["UID"] != null)
                    rowsAttendees = attendees.Select(filter);
                if (customProperties.Columns["UID"] != null)
                    rowsCustomProperties = customProperties.Select(filter);
                if (attachments.Columns["UID"] != null)
                    rowsAttachments = attachments.Select(filter);

                items.Add(new CalendarFile(context, uid, rowCalendarFile, rowsEventComponents, rowsRecurrenceExceptions, rowsAlarms, rowsAttendees, rowsCustomProperties, rowsAttachments));
            }
        }

        return items;
    }

    private static string GetScPropsToLoad(PropsToLoad propsToLoad)
    {
        switch (propsToLoad)
        {
            case PropsToLoad.None:
                return "[UID]";
            case PropsToLoad.Minimum:
                // [Summary] is typically not required in GetChildren call, 
                // they are extracted for demo purposes only, to be displayed in Ajax File Browser as a file display name.
                return "[UID], [Summary]";
            case PropsToLoad.All:
                return "*";
        }
        throw new Exception("Should never come here.");
    }

    public static CalendarFile CreateCalendarFile(DavContext context, Guid calendarFolderId)
    {
        CalendarFile calendarFile = new CalendarFile(context, null, null, null, null, null, null, null, null);
        calendarFile.calendarFolderId = calendarFolderId;
        return calendarFile;
    }

    private readonly string uid = null;

    private readonly DataRow rowCalendarFile = null;

    private readonly DataRow[] rowsEventComponents = null;

    private readonly DataRow[] rowsRecurrenceExceptions = null;

    private readonly DataRow[] rowsAlarms = null;

    private readonly DataRow[] rowsAttendees = null;

    private readonly DataRow[] rowsCustomProperties = null;

    private readonly DataRow[] rowsAttachments = null;

    private bool isNew
    {
        get { return calendarFolderId != Guid.Empty; }
    }

    private int paramIndex = 0;

    private Guid calendarFolderId = Guid.Empty;

    public override string Name
    {
        get
        {
            // Show all components summaries contained in this file.
            return string.Join(", ", rowsEventComponents.Select(x => string.Format("[{0}]", x.Field<string>("Summary"))).ToArray());
        }
    }

    public override string Path
    {
        get
        {
            Guid calendarFolderId = rowCalendarFile.Field<Guid>("CalendarFolderId");
            string uid              = rowCalendarFile.Field<string>("UID");
            return string.Format("{0}{1}/{2}{3}", CalendarsRootFolder.CalendarsRootFolderPath, calendarFolderId, uid, Extension);
        }
    }

    public string Etag
    {
        get
        {
            byte[] bETag = rowCalendarFile.Field<byte[]>("ETag");
            return BitConverter.ToUInt64(bETag.Reverse().ToArray(), 0).ToString(); // convert timestamp value to number
        }
    }

    public override DateTime Created
    {
        get { return rowCalendarFile.Field<DateTime>("CreatedUtc"); }
    }

    public override DateTime Modified
    {
        get { return rowCalendarFile.Field<DateTime>("ModifiedUtc"); }
    }

    public long ContentLength
    {
        get { return -1; }
    }

    public string ContentType
    {
     get { return "text/calendar"; }
    }

    private CalendarFile(DavContext context, string uid,
        DataRow rowCalendarFile, DataRow[] rowsEventComponents, DataRow[] rowsRecurrenceExceptions, DataRow[] rowsAlarms,
        DataRow[] rowsAttendees, DataRow[] rowsCustomProperties, DataRow[] rowsAttachments)
        : base(context)
    {
        this.uid                      = uid;
        this.rowCalendarFile          = rowCalendarFile;
        this.rowsEventComponents      = rowsEventComponents;
        this.rowsRecurrenceExceptions = rowsRecurrenceExceptions;
        this.rowsAlarms               = rowsAlarms;
        this.rowsAttendees            = rowsAttendees;
        this.rowsCustomProperties     = rowsCustomProperties;
        this.rowsAttachments          = rowsAttachments;
    }

    public async Task<bool> WriteAsync(Stream stream, string contentType, long startIndex, long totalFileSize)
    {
        //Set timeout to maximum value to be able to upload iCalendar files with large file attachments.
        System.Web.HttpContext.Current.Server.ScriptTimeout = int.MaxValue;
        string iCalendar;
        using (StreamReader reader = new StreamReader(stream))
        {
            iCalendar = await reader.ReadToEndAsync();
        }

        // Typically the stream contains a single iCalendar that contains one or more event or to-do components.
        IEnumerable<IComponent> calendars = new vFormatter().Deserialize(iCalendar);
        ICalendar2 calendar = calendars.First() as ICalendar2;

        IEnumerable<IEventBase> components = calendar.Events.Cast<IEventBase>();
        if (!components.Any())
        {
            components = calendar.ToDos.Cast<IEventBase>();
        }

        if (components == null)
            throw new DavException("Event or to-do was expected in the input stream, no events or to-dos were found.", DavStatus.UNSUPPORTED_MEDIA_TYPE);

        // All components inside calendar file has the same UID which is equal to file name.
        string uid = components.First().Uid.Text;

        // Save data to [cal_CalendarFile] table.
        await WriteCalendarFileAsync(Context, uid, calendarFolderId, isNew);

        foreach (IEventBase component in components)
        {
            Guid eventComponentId = Guid.NewGuid();

            // Save data to [cal_EventComponent] table.
            await WriteEventComponentAsync(Context, component, eventComponentId, uid);

            // Save recurrence days exceptions for recurring events and to-dos. 
            await WriteRecurrenceExceptionsAsync(Context, component.ExceptionDateTimes, eventComponentId, uid);

            // Save alarms.
            await WriteAlarmsAsync(Context, component.Alarms, eventComponentId, uid);

            // Save attendees.
            await WriteAttendeesAsync(Context, component.Attendees, eventComponentId, uid);

            // Save attachments.
            await WriteAttachmentsAsync(Context, component.Attachments, eventComponentId, uid);
        }

        // Notify attendees that event is created or modified.
        calendar.Method = calendar.CreateMethodProp(MethodType.Request);
        await iMipEventSchedulingTransport.NotifyAttendeesAsync(Context, calendar);

        return true;
    }

    private static async Task WriteCalendarFileAsync(DavContext context, string uid, Guid calendarFolderId, bool isNew)
    {
        string sql;
        if (isNew)
        {
            sql =
              @"IF EXISTS (SELECT 1 FROM [cal_Access] WHERE [CalendarFolderId]=@CalendarFolderId AND [UserId]=@UserId AND [Write]=1)
                INSERT INTO [cal_CalendarFile] (
                      [UID]
                    , [CalendarFolderId]
                ) VALUES (
                      @UID
                    , @CalendarFolderId
                )";
        }
        else
        {
            // We can only update record in [cal_CalendarFile] table.
            // There is no way to update [cal_EventComponent], [cal_RecurrenceException], [cal_Alarm], [cal_Attendee], 
            // [cal_Attachment] and [cal_CustomProperty] for existing event, we must delete all records for this UID and recreate.

            // [ModifiedUtc] field update triggers [ETag] field update which is used for synchronyzation.
            sql =
              @"IF EXISTS (SELECT 1 FROM [cal_CalendarFile]
                    WHERE UID = @UID
                    AND [CalendarFolderId] IN (SELECT [CalendarFolderId] FROM [cal_Access] WHERE [UserId] = @UserId AND [Write] = 1))
                BEGIN
                    UPDATE [cal_CalendarFile] SET 
                        [ModifiedUtc] = @ModifiedUtc
                    WHERE [UID] = @UID

                    ; DELETE FROM [cal_EventComponent]      WHERE [UID] = @UID
                    ; DELETE FROM [cal_RecurrenceException] WHERE [UID] = @UID
                    ; DELETE FROM [cal_Alarm]               WHERE [UID] = @UID
                    ; DELETE FROM [cal_Attendee]            WHERE [UID] = @UID
                    ; DELETE FROM [cal_Attachment]          WHERE [UID] = @UID
                    ; DELETE FROM [cal_CustomProperty]      WHERE [UID] = @UID
                END";
        }

        if(await context.ExecuteNonQueryAsync(sql,
              "@UID"                , uid
            , "UserId"              , context.UserId
            , "@CalendarFolderId"   , calendarFolderId
            , "@ModifiedUtc"        , DateTime.UtcNow) < 1)
        {
            throw new DavException("Item not found or you do not have enough permissions to complete this operation.", DavStatus.FORBIDDEN);
        }
    }

    private async Task WriteEventComponentAsync(DavContext context, IEventBase sc, Guid eventComponentId, string uid)
    {
        string sql =
                @"INSERT INTO [cal_EventComponent] (
                      [EventComponentId]
                    , [UID]
                    , [ComponentType]
                    , [DateTimeStampUtc]
                    , [CreatedUtc]
                    , [LastModifiedUtc]
                    , [Summary]
                    , [Description]
                    , [OrganizerEmail]
                    , [OrganizerCommonName]
                    , [Start]
                    , [StartTimeZoneId]
                    , [End]
                    , [EndTimeZoneId]
                    , [Duration]
                    , [AllDay]
                    , [Class]
                    , [Location]
                    , [Priority]
                    , [Sequence]
                    , [Status]
                    , [Categories]
                    , [RecurFrequency]
                    , [RecurInterval]
                    , [RecurUntil]
                    , [RecurCount]
                    , [RecurWeekStart]
                    , [RecurByDay]
                    , [RecurByMonthDay]
                    , [RecurByMonth]
                    , [RecurBySetPos]
                    , [RecurrenceIdDate]
                    , [RecurrenceIdTimeZoneId]
                    , [RecurrenceIdThisAndFuture]
                    , [EventTransparency]
                    , [ToDoCompletedUtc]
                    , [ToDoPercentComplete]
                ) VALUES (
                      @EventComponentId
                    , @UID
                    , @ComponentType
                    , @DateTimeStampUtc 
                    , @CreatedUtc
                    , @LastModifiedUtc
                    , @Summary
                    , @Description
                    , @OrganizerEmail
                    , @OrganizerCommonName
                    , @Start, @StartTimeZoneId
                    , @End, @EndTimeZoneId
                    , @Duration
                    , @AllDay
                    , @Class
                    , @Location
                    , @Priority
                    , @Sequence
                    , @Status
                    , @Categories
                    , @RecurFrequency
                    , @RecurInterval
                    , @RecurUntil
                    , @RecurCount
                    , @RecurWeekStart
                    , @RecurByDay
                    , @RecurByMonthDay
                    , @RecurByMonth
                    , @RecurBySetPos
                    , @RecurrenceIdDate
                    , @RecurrenceIdTimeZoneId
                    , @RecurrenceIdThisAndFuture
                    , @EventTransparency
                    , @ToDoCompletedUtc
                    , @ToDoPercentComplete
                )";


        bool isEvent = sc is IEvent;

        // Get END in case of event or DUE in case of to-do component. 
        ICalDate endProp = isEvent ? (sc as IEvent).End : (sc as IToDo).Due;

        await context.ExecuteNonQueryAsync(sql,
              "@EventComponentId"       , eventComponentId
            , "@UID"                    , uid                                                                   // UID value
            , "@ComponentType"          , isEvent
            , "@DateTimeStampUtc"       , sc.DateTimeStampUtc?.Value?.DateVal                                   // DTSTAMP value
            , "@CreatedUtc"             , sc.CreatedUtc?.Value?.DateVal                                         // CREATED value
            , "@LastModifiedUtc"        , sc.LastModifiedUtc?.Value?.DateVal                                    // LAST-MODIFIED value
            , "@Summary"                , sc.Summary?.Text                                                      // SUMMARY value
            , "@Description"            , sc.Description?.Text                                                  // DESCRIPTION value
            , "@OrganizerEmail"         , sc.Organizer?.Uri?.Replace("mailto:", "")                             // ORGANIZER value
            , "@OrganizerCommonName"    , sc.Organizer?.CommonName                                              // ORGANIZER CN param
            , "@Start"                  , sc.Start?.Value?.DateVal                                              // DTSTART value
            , "@StartTimeZoneId"        , sc.Start?.Value?.DateVal.Kind == DateTimeKind.Utc ? TimeZoneInfo.Utc.Id : sc.Start?.TimeZoneId  // DTSTART TZID param
            , "@End"                    , endProp?.Value?.DateVal                                               // DTEND or DUE value
            , "@EndTimeZoneId"          , endProp?.Value?.DateVal.Kind == DateTimeKind.Utc ? TimeZoneInfo.Utc.Id : endProp?.TimeZoneId    // DTEND or DUE TZID param
            , "@Duration"               , sc.Duration?.Value?.Ticks                                             // DURATION value
            , "@AllDay"                 , !sc.Start?.Value?.Components.HasFlag(DateComponents.Time) // Check if start contains the time part to determine if this is a all-day event/to-do.
            , "@Class"                  , sc.Class?.Value.Name                                                  // CLASS value
            , "@Location"               , sc.Location?.Text                                                     // LOCATION value
            , "@Priority"               , sc.Priority?.Value                                                    // PRIORITY value
            , "@Sequence"               , sc.Sequence?.Value                                                    // SEQUENCE value
            , "@Status"                 , sc.Status?.Value.Name                                                 // STATUS value
            , "@Categories"             , ListToString<string>(sc.Categories.Select(x => ListToString<string>(x.Categories, ",")), ";") // CATEGORIES value
            , "@RecurFrequency"         , sc.RecurrenceRule?.Frequency?.ToString()                              // RRULE FREQ value part
            , "@RecurInterval"          , (int?)sc.RecurrenceRule?.Interval                                     // RRULE INTERVAL value part
            , "@RecurUntil"             , sc.RecurrenceRule?.Until?.DateVal                                     // RRULE UNTIL value part
            , "@RecurCount"             , (int?)sc.RecurrenceRule?.Count                                        // RRULE COUNT value part
            , "@RecurWeekStart"         , sc.RecurrenceRule?.WeekStart?.ToString()                              // RRULE WKST value part
            , "@RecurByDay"             , ListToString<DayRule>(sc.RecurrenceRule?.ByDay)                       // RRULE BYDAY value part
            , "@RecurByMonthDay"        , ListToString<short>(sc.RecurrenceRule?.ByMonthDay)                    // RRULE BYMONTHDAY value part
            , "@RecurByMonth"           , ListToString<ushort>(sc.RecurrenceRule?.ByMonth)                      // RRULE BYMONTH value part
            , "@RecurBySetPos"          , ListToString<short>(sc.RecurrenceRule?.BySetPos)                      // RRULE BYSETPOS value part
            , "@RecurrenceIdDate"       , sc.RecurrenceId?.Value.DateVal                                        // RECURRENCE-ID value
            , "@RecurrenceIdTimeZoneId" , sc.RecurrenceId?.TimeZoneId                                           // RECURRENCE-ID TZID param
            , "@RecurrenceIdThisAndFuture", sc.RecurrenceId?.IsThisAndFuture                                    // RECURRENCE-ID RANGE param
            , "@EventTransparency"      , (sc as IEvent)?.Transparency?.IsTransparent                           // VEVENT TRANSP value
            , "@ToDoCompletedUtc"       , (sc as IToDo)?.CompletedUtc?.Value?.DateVal                           // VTODO COMPLETED value
            , "@ToDoPercentComplete"    , (sc as IToDo)?.PercentComplete?.Value                                 // VTODO PERCENT-COMPLETE value
            );

        // Save custom properties and parameters of this component to [cal_CustomProperty] table.
        string customPropsSqlInsert;
        List<object> customPropsParamsInsert;
        if (PrepareSqlCustomPropertiesOfComponentAsync(sc, eventComponentId, uid, out customPropsSqlInsert, out customPropsParamsInsert))
        {
            await context.ExecuteNonQueryAsync(customPropsSqlInsert, customPropsParamsInsert.ToArray());
        }
    }

    private static string ListToString<T>(IEnumerable<T> arr, string separator = ",")
    {
        if ((arr == null) || !arr.Any())
            return null;
        return string.Join<T>(separator, arr);
    }

    private static async Task WriteRecurrenceExceptionsAsync(DavContext context, IPropertyList<ICalDateList> recurrenceExceptions, Guid eventComponentId, string uid)
    {
        // Typically CalDAV clients pass a single date value per EXDATE property.

        string sql =
            @"INSERT INTO [cal_RecurrenceException] (
                  [EventComponentId]
                , [UID]
                , [ExceptionDate]
                , [TimeZoneId]
                , [AllDay]
            ) VALUES {0}";

        List<string> valuesSql = new List<string>();
        List<object> parameters = new List<object>(new object[] {
              "@EventComponentId", eventComponentId
            , "@UID", uid
        });

        int i = 0;
        foreach (ICalDateList dateListProp in recurrenceExceptions)
        {
            foreach (Date date in dateListProp.Dates)
            {
                if (date == null)
                    continue; // failed fo parse date

                valuesSql.Add(string.Format(@"(
                  @EventComponentId
                , @UID
                , @ExceptionDate{0}
                , @TimeZoneId{0}
                , @AllDay{0}
                )", i));

                parameters.AddRange(new object[] {
              //  "@EventComponentId"
              //  "@UID" added for performance optimization purposes
                  "@ExceptionDate" +i, date.DateVal                                                                         // EXDATE value
                , "@TimeZoneId"    +i, date.DateVal.Kind == DateTimeKind.Utc ? TimeZoneInfo.Utc.Id : dateListProp.TimeZoneId// EXDATE TZID param
                , "@AllDay"        +i, !date.Components.HasFlag(DateComponents.Time)                                        // EXDATE DATE or DATE-TIME
                });
            }
            i++;
        }

        if (i > 0)
        {
            await context.ExecuteNonQueryAsync(string.Format(sql, string.Join(", ", valuesSql.ToArray())), parameters.ToArray());
        }
    }

    private async Task WriteAlarmsAsync(DavContext context, IComponentList<IAlarm> alarms, Guid eventComponentId, string uid)
    {
        string sql =
            @"INSERT INTO [cal_Alarm] (
                  [AlarmId]
                , [EventComponentId]
                , [UID]
                , [Action]
                , [TriggerAbsoluteDateTimeUtc]
                , [TriggerRelativeOffset]
                , [TriggerRelatedStart]
                , [Summary]
                , [Description]
                , [Duration]
                , [Repeat]
            ) VALUES {0}";

        List<string> valuesSql = new List<string>();
        List<object> parameters = new List<object>(new object[] {
              "@EventComponentId", eventComponentId
            , "@UID", uid
        });

        int i = 0;
        foreach (IAlarm alarm in alarms)
        {
            Guid alarmId = Guid.NewGuid();

            valuesSql.Add(string.Format(@"(
                  @AlarmId{0}
                , @EventComponentId
                , @UID
                , @Action{0}
                , @TriggerAbsoluteDateTimeUtc{0}
                , @TriggerRelativeOffset{0}
                , @TriggerRelatedStart{0}
                , @Summary{0}
                , @Description{0}
                , @Duration{0}
                , @Repeat{0}
                )", i));

            parameters.AddRange(new object[] {
                  "@AlarmId"                    +i, alarmId
              //, "@EventComponentId"
              //, "@UID" added for performance optimization purposes
                , "@Action"                     +i, alarm.Action.Action.Name                                                        // Alarm ACTION property
                , "@TriggerAbsoluteDateTimeUtc" +i, alarm.Trigger?.AbsoluteDateTimeUtc                                              // Alarm TRIGGER property
                , "@TriggerRelativeOffset"      +i, alarm.Trigger?.RelativeOffset?.Ticks                                            // Alarm TRIGGER property
                , "@TriggerRelatedStart"        +i, alarm.Trigger==null ? (bool?)null : alarm.Trigger.Related == RelatedType.Start  // Alarm trigger RELATED param
                , "@Summary"                    +i, alarm.Summary?.Text                                                             // Alarm SUMMARY property
                , "@Description"                +i, alarm.Description?.Text                                                         // Alarm DESCRIPTION property
                , "@Duration"                   +i, alarm.Duration?.Value?.Ticks                                                    // Alarm DURATION property
                , "@Repeat"                     +i, alarm.Repeat?.Value                                                             // Alarm REPEAT property
            });

            // Create SQL to save custom properties of this component of this component to [cal_CustomProperty] table.
            string customPropsSqlInsert;
            List<object> customPropsParamsInsert;
            if (PrepareSqlCustomPropertiesOfComponentAsync(alarm, alarmId, uid, out customPropsSqlInsert, out customPropsParamsInsert))
            {
                sql += "; " + customPropsSqlInsert;
                parameters.AddRange(customPropsParamsInsert);
            }

            i++;
        }

        if (i > 0)
        {
            await context.ExecuteNonQueryAsync(string.Format(sql, string.Join(", ", valuesSql.ToArray())), parameters.ToArray());
        }
    }

    private async Task WriteAttendeesAsync(DavContext context, IPropertyList<IAttendee> attendees, Guid eventComponentId, string uid)
    {
        string sql =
            @"INSERT INTO [cal_Attendee] (
                  [AttendeeId]
                , [EventComponentId]
                , [UID]
                , [Email]
                , [CommonName]
                , [DirectoryEntryRef]
                , [Language]
                , [UserType]
                , [SentBy]
                , [DelegatedFrom]
                , [DelegatedTo]
                , [Rsvp]
                , [ParticipationRole]
                , [ParticipationStatus]
            ) VALUES {0}";

        List<string> valuesSql = new List<string>();
        List<object> parameters = new List<object>(new object[] {
              "@EventComponentId", eventComponentId
            , "@UID", uid
        });

        int i = 0;
        foreach (IAttendee attendee in attendees)
        {
            valuesSql.Add(string.Format(@"(
                  @AttendeeId{0}
                , @EventComponentId
                , @UID
                , @Email{0}
                , @CommonName{0}
                , @DirectoryEntryRef{0}
                , @Language{0}
                , @UserType{0}
                , @SentBy{0}
                , @DelegatedFrom{0}
                , @DelegatedTo{0}
                , @Rsvp{0}
                , @ParticipationRole{0}
                , @ParticipationStatus{0}
            )", i));

            Guid attendeeId = Guid.NewGuid();

            parameters.AddRange(new object[] {
                  "@AttendeeId"         +i, attendeeId
              //, "@EventComponentId"
              //, "@UID" added for performance optimization purposes
                , "@Email"              +i, attendee.Uri?.Replace("mailto:", "")    // Attendee value
                , "@CommonName"         +i, attendee.CommonName                     // Attendee CN parameter
                , "@DirectoryEntryRef"  +i, attendee.Dir                            // Attendee DIR parameter
                , "@Language"           +i, attendee.Language                       // Attendee LANGUAGE parameter
                , "@UserType"           +i, attendee.UserType?.Name                 // Attendee CUTYPE parameter
                , "@SentBy"             +i, attendee.SentBy                         // Attendee SENT-BY parameter
                , "@DelegatedFrom"      +i, attendee.DelegatedFrom.FirstOrDefault() // Attendee DELEGATED-FROM parameter, here we assume only 1 delegator for the sake of simplicity
                , "@DelegatedTo"        +i, attendee.DelegatedTo.FirstOrDefault()   // Attendee DELEGATED-TO parameter, here we assume only 1 delegatee for the sake of simplicity
                , "@Rsvp"               +i, attendee.Rsvp == RsvpType.True          // Attendee RSVP parameter
                , "@ParticipationRole"  +i, attendee.ParticipationRole?.Name        // Attendee ROLE parameter
                , "@ParticipationStatus"+i, attendee.ParticipationStatus?.Name      // Attendee PARTSTAT parameter
            });

            // Prepare SQL to save custom property parameters to [cal_CustomProperty] table.
            string customPropSqlInsert;
            List<object> customPropParametersInsert;
            if (PrepareSqlParamsWriteCustomProperty("ATTENDEE", attendee.RawProperty, attendeeId, uid, out customPropSqlInsert, out customPropParametersInsert))
            {
                sql += "; " + customPropSqlInsert;
                parameters.AddRange(customPropParametersInsert);
            }

            i++;
        }

        if (i > 0)
        {
            await context.ExecuteNonQueryAsync(string.Format(sql, string.Join(", ", valuesSql.ToArray())), parameters.ToArray());
        }
    }

    private async Task WriteAttachmentsAsync(DavContext context, IPropertyList<IMedia> attachments, Guid eventComponentId, string uid)
    {
        // It is recommended to keep attchment size below 256Kb. In case files over 1Mb should 
        // be stored, use SQL FILESTREAM, FileTable or store content in file system.

        string sqlAttachment =
            @"INSERT INTO [cal_Attachment] (
                  [AttachmentId]
                , [EventComponentId]
                , [UID]
                , [MediaType]
                , [ExternalUrl]
                , [Content]
            ) VALUES (
                  @AttachmentId
                , @EventComponentId
                , @UID
                , @MediaType
                , @ExternalUrl
                , @Content
            )";

        string customPropertiesSql = "";
        List<object> customPropertiesParameters = new List<object>();

        foreach (IMedia attachment in attachments)
        {
            // To insert NULL to VARBINARY column SqlParameter must be passed with Size=-1 and Value=DBNull.Value.
            SqlParameter contentParam = new SqlParameter("@Content", SqlDbType.VarBinary, -1);
            contentParam.Value = DBNull.Value;

            if (!attachment.IsExternal)
            {
                byte[] content = Convert.FromBase64String(attachment.Base64Data);
                contentParam.Size = content.Length;
                contentParam.Value = content;
            }

            Guid attachmentId = Guid.NewGuid();

            await context.ExecuteNonQueryAsync(sqlAttachment,
                  "@AttachmentId"       , attachmentId
                , "@EventComponentId"   , eventComponentId
                , "@UID"                , uid
                , "@MediaType"          , attachment.MediaType
                , "@ExternalUrl"        , attachment.IsExternal ? attachment.Uri : null
                , contentParam
                );

            // Prepare SQL to save custom property parameters to [].
            string customPropSqlInsert;
            List<object> customPropParametersInsert;
            if (PrepareSqlParamsWriteCustomProperty("ATTACH", attachment.RawProperty, attachmentId, uid, out customPropSqlInsert, out customPropParametersInsert))
            {
                customPropertiesSql += "; " + customPropSqlInsert;
                customPropertiesParameters.AddRange(customPropParametersInsert);
            }
        }

        if (!string.IsNullOrEmpty(customPropertiesSql))
        {
            await context.ExecuteNonQueryAsync(customPropertiesSql, customPropertiesParameters.ToArray());
        }
    }

    private bool PrepareSqlParamsWriteCustomProperty(string propName, IRawProperty prop, Guid parentId, string uid, out string sql, out List<object> parameters)
    {
        sql =
            @"INSERT INTO [cal_CustomProperty] (
                  [ParentId]
                , [UID]
                , [PropertyName]
                , [ParameterName]
                , [Value]
            ) VALUES {0}";

        List<string> valuesSql = new List<string>();
        parameters = new List<object>();

        int origParamsCount = parameters.Count();

        bool isCustomProp = propName.StartsWith("X-", StringComparison.InvariantCultureIgnoreCase);

        string paramName = null;

        // Save custom prop value.
        if (isCustomProp)
        {
            string val = prop.RawValue;
            valuesSql.Add(string.Format(@"(
                              @ParentId{0}
                            , @UID{0}
                            , @PropertyName{0}
                            , @ParameterName{0}
                            , @Value{0}
                            )", paramIndex));

            parameters.AddRange(new object[] {
                              "@ParentId"     +paramIndex, parentId
                            , "@UID"          +paramIndex, uid       // added for performance optimization purposes
                            , "@PropertyName" +paramIndex, propName
                            , "@ParameterName"+paramIndex, paramName // null is inserted into the ParameterName field to mark prop value
                            , "@Value"        +paramIndex, val
                            });
            paramIndex++;
        }

        // Save parameters and their values.
        foreach (Parameter param in prop.Parameters)
        {
            paramName = param.Name;

            // For standard properties we save only custom params (that start with 'X-'). All standard patrams go to their fields in DB.
            // For custom properies we save all params.
            if (!isCustomProp && !paramName.StartsWith("X-", StringComparison.InvariantCultureIgnoreCase))
                continue;

            foreach (string value in param.Values)
            {
                string val = value;

                valuesSql.Add(string.Format(@"(
                              @ParentId{0}
                            , @UID{0}
                            , @PropertyName{0}
                            , @ParameterName{0}
                            , @Value{0}
                            )", paramIndex));

                parameters.AddRange(new object[] {
                              "@ParentId"     +paramIndex, parentId
                            , "@UID"          +paramIndex, uid       // added for performance optimization purposes
                            , "@PropertyName" +paramIndex, propName
                            , "@ParameterName"+paramIndex, paramName
                            , "@Value"        +paramIndex, val
                            });
                paramIndex++;
            }
        }

        if (origParamsCount < parameters.Count())
        {
            sql = string.Format(sql, string.Join(", ", valuesSql.ToArray()));
            return true;
        }
        return false;
    }

    private bool PrepareSqlCustomPropertiesOfComponentAsync(IComponent component, Guid parentId, string uid, out string sql, out List<object> parameters)
    {
        sql = "";
        parameters = new List<object>();

        // We save only single custom props here, multiple props are saved in other methods.
        string[] multiProps = new string[] { "ATTACH", "ATTENDEE", "EXDATE" };

        // Properties in IComponent.Properties are grouped by name.
        foreach (KeyValuePair<string, IList<IRawProperty>> pair in component.Properties)
        {
            if (multiProps.Contains(pair.Key.ToUpper()) || (pair.Value.Count != 1))
                continue;

            string sqlInsert;
            List<object> parametersInsert;
            if (PrepareSqlParamsWriteCustomProperty(pair.Key, pair.Value.First(), parentId, uid, out sqlInsert, out parametersInsert))
            {
                sql += "; " + sqlInsert;
                parameters.AddRange(parametersInsert);
            }
        }

        return !string.IsNullOrEmpty(sql);
    }

    public override async Task DeleteAsync(MultistatusException multistatus)
    {
        ICalendar2 cal = await GetCalendarAsync();

        string sql = @"DELETE FROM [cal_CalendarFile] 
                       WHERE UID=@UID
                       AND [CalendarFolderId] IN (SELECT [CalendarFolderId] FROM [cal_Access] WHERE [UserId]=@UserId AND [Write]=1)";

        if(await Context.ExecuteNonQueryAsync(sql, 
              "@UID", uid
            , "@UserId", Context.UserId) < 1)
        {
            throw new DavException("Item not found or you do not have enough permissions to complete this operation.", DavStatus.FORBIDDEN);
        }

        // Notify attendees that event is canceled if deletion is successful.
        cal.Method = cal.CreateMethodProp(MethodType.Cancel);
        await iMipEventSchedulingTransport.NotifyAttendeesAsync(Context, cal);
    }

    public async Task ReadAsync(Stream output, long startIndex, long count)
    {
        ICalendar2 cal = await GetCalendarAsync();
        new vFormatter().Serialize(output, cal);
    }

    private async Task<ICalendar2> GetCalendarAsync()
    {
        ICalendar2 cal = CalendarFactory.CreateCalendar2();
        cal.ProductId = cal.CreateTextProp("-//IT Hit//Collab Lib//EN");

        // Recurrent event or to-do can contain more than one VEVENT/VTODO component in one file.
        foreach (DataRow rowEventComponent in rowsEventComponents)
        {

            // add either event or to-do to the calendar
            bool isEvent = rowEventComponent.Field<bool>("ComponentType");
            IEventBase sc;
            if (isEvent)
            {
                sc = cal.Events.CreateComponent();
                cal.Events.Add(sc as IEvent);
            }
            else
            {
                sc = cal.ToDos.CreateComponent();
                cal.ToDos.Add(sc as IToDo);
            }

            // Read component properties from previously loaded [cal_EventComponent] rows.
            ReadEventComponent(sc, rowEventComponent, cal);


            Guid eventComponentId = rowEventComponent.Field<Guid>("EventComponentId");

            // Get [cal_RecurrenceException] rows that belong to this event component only and read recurrence exceptions dates.
            IEnumerable<DataRow> rowsThisScRecurrenceExceptions = rowsRecurrenceExceptions.Where(x => x.Field<Guid>("EventComponentId") == eventComponentId);
            ReadRecurrenceExceptions(sc.ExceptionDateTimes, rowsThisScRecurrenceExceptions, cal);

            // Get [cal_Alarm] rows that belong to this event component only and read alarms.
            IEnumerable<DataRow> rowsThisScAlarms = rowsAlarms.Where(x => x.Field<Guid>("EventComponentId") == eventComponentId);
            ReadAlarms(sc.Alarms, rowsThisScAlarms, cal);

            // Get [cal_Attendee] rows that belong to this event component only and read attendees.
            IEnumerable<DataRow> rowsThisScAttendees = rowsAttendees.Where(x => x.Field<Guid>("EventComponentId") == eventComponentId);
            ReadAttendees(sc.Attendees, rowsThisScAttendees, cal);

            // Get [cal_Attachment] rows that belong to this event component only.
            // Read attachments, load [cal_Attachment].[Content] if required.
            IEnumerable<DataRow> rowsThisScAttachments = rowsAttachments.Where(x => x.Field<Guid>("EventComponentId") == eventComponentId);
            await ReadAttachmentsAsync(Context, sc.Attachments, rowsThisScAttachments, cal);
        }

        // Generate VTIMEZONE components based on TZID parameters.
        cal.AutoGenerateTimeZones = true;

        return cal;
    }

    private void ReadEventComponent(IEventBase sc, DataRow row, ICalendar2 cal)
    {
        bool isAllDay = row.Field<bool?>("AllDay").GetValueOrDefault();

        sc.Uid              = cal.CreateTextProp(row.Field<string>("UID"));                                     // UID property, iOS / OS X UID is case sensitive, Bynari WebDAV Collaborator UID is over 100 chars long
        sc.DateTimeStampUtc = cal.CreateDateProp(row.Field<DateTime?>("DateTimeStampUtc"), DateTimeKind.Utc);   // DTSTAMP property
        sc.CreatedUtc       = cal.CreateDateProp(row.Field<DateTime?>("CreatedUtc"), DateTimeKind.Utc);         // CREATED property
        sc.LastModifiedUtc  = cal.CreateDateProp(row.Field<DateTime?>("LastModifiedUtc"), DateTimeKind.Utc);    // LAST-MODIFIED property
        sc.Summary          = cal.CreateCalTextProp(row.Field<string>("Summary"));                              // SUMMARY property
        sc.Description      = cal.CreateCalTextProp(row.Field<string>("Description"));                          // DESCRIPTION property
        sc.Start            = cal.CreateCalDateProp(row.Field<DateTime?>("Start"), row.Field<string>("StartTimeZoneId"), isAllDay);                 // DTSTART property
        sc.Duration         = cal.CreateDurationProp(row.Field<long?>("Duration"));                             // DURATION property
        sc.Class            = cal.CreateClassProp(row.Field<string>("Class"));                                  // CLASS property
        sc.Location         = cal.CreateCalTextProp(row.Field<string>("Location"));                             // LOCATION property
        sc.Priority         = cal.CreateIntegerProp(row.Field<byte?>("Priority"));                              // PRIORITY property
        sc.Sequence         = cal.CreateIntegerProp(row.Field<int?>("Sequence"));                               // SEQ property
        sc.Status           = cal.CreateStatusProp(row.Field<string>("Status"));                                // STATUS property
        sc.Organizer        = cal.CreateCalAddressProp(EmailToUri(row.Field<string>("OrganizerEmail")), row.Field<string>("OrganizerCommonName"));  // ORGANIZER property

        // RECURRENCE-ID property
        sc.RecurrenceId     = cal.CreateRecurrenceIdProp(
            row.Field<DateTime?>("RecurrenceIdDate")                                                            // RECURRENCE-ID value
            , row.Field<string>("RecurrenceIdTimeZoneId")                                                       // RECURRENCE-ID TZID param
            , isAllDay                                                                                          // RECURRENCE-ID DATE or DATE-TIME
            , row.Field<bool?>("RecurrenceIdThisAndFuture").GetValueOrDefault());                                             // RECURRENCE-ID RANGE param

        // CATEGORIES property list
        string categories = row.Field<string>("Categories") as string;
        if (!string.IsNullOrEmpty(categories))
        {
            string[] strCatProp = categories.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
            foreach (string categoryList in strCatProp)
            {
                ICategories catProp = sc.Categories.CreateProperty();
                catProp.Categories = categoryList.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                sc.Categories.Add(catProp);
            }
        }

        // RRULE property
        string recurFrequency = row.Field<string>("RecurFrequency");
        if (!string.IsNullOrEmpty(recurFrequency))
        {
            sc.RecurrenceRule = cal.CreateProperty<IRecurrenceRule>();

            sc.RecurrenceRule.Frequency = ExtendibleEnum.FromString<FrequencyType>(recurFrequency);             // FREQ rule part
            sc.RecurrenceRule.Interval  = (uint?)row.Field<int?>("RecurInterval");                              // INTERVAL rule part
            sc.RecurrenceRule.Count     = (uint?)row.Field<int?>("RecurCount");                                 // COUNT rule part

            // WKST rule part
            string weekStart = row.Field<string>("RecurWeekStart");
            if (!string.IsNullOrEmpty(weekStart))
            {
                sc.RecurrenceRule.WeekStart = (DayOfWeek)Enum.Parse(typeof(DayOfWeek), weekStart);
            }

            // UNTIL rule part
            DateTime? until = row.Field<DateTime?>("RecurUntil");
            if (until != null)
            {
                // UNTIL must be in UTC if DTSTART contains time zone or DTSTART is UTC.
                // UNTIL must be 'floating' if DTSTART is 'floating'.
                // UNTIL must be 'all day' if the DTSTART is 'all day'.
                // https://tools.ietf.org/html/rfc5545#section-3.3.10
                sc.RecurrenceRule.Until = new Date(
                    DateTime.SpecifyKind(until.Value,
                    sc.Start.Value.DateVal.Kind != DateTimeKind.Local /*floating*/ ? DateTimeKind.Utc : DateTimeKind.Local),
                    sc.Start.Value.Components);
            }

            // BYDAY rule part
            string byDay = row.Field<string>("RecurByDay");
            if (!string.IsNullOrEmpty(byDay))
            {
                sc.RecurrenceRule.ByDay = byDay.Split(',').Select(x => DayRule.Parse(x)).ToArray();
            }

            // BYMONTHDAY rule part
            string byMonthDay = row.Field<string>("RecurByMonthDay");
            if (!string.IsNullOrEmpty(byMonthDay))
            {
                sc.RecurrenceRule.ByMonthDay = byMonthDay.Split(',').Select(x => short.Parse(x)).ToArray();
            }

            // BYMONTH rule part
            string byMonth = row.Field<string>("RecurByMonth");
            if (!string.IsNullOrEmpty(byMonth))
            {
                sc.RecurrenceRule.ByMonth = byMonth.Split(',').Select(x => ushort.Parse(x)).ToArray();
            }

            // BYSETPOS  rule part
            string bySetPos = row.Field<string>("RecurBySetPos");
            if (!string.IsNullOrEmpty(bySetPos))
            {
                sc.RecurrenceRule.BySetPos = bySetPos.Split(',').Select(x => short.Parse(x)).ToArray();
            }      
        }

        if (sc is IEvent)
        {
            // Properties specific for events only
            IEvent vEvent = sc as IEvent;
            vEvent.End          = cal.CreateCalDateProp(row.Field<DateTime?>("End"), row.Field<string>("EndTimeZoneId"), isAllDay); // DTEND property
            vEvent.Transparency = cal.CreateTransparencyProp(row.Field<bool?>("EventTransparency"));                                // TRANSP property
        }
        else
        {
            // Properties specific for to-dos only
            IToDo vToDo = sc as IToDo;
            vToDo.Due           = cal.CreateCalDateProp(row.Field<DateTime?>("End"), row.Field<string>("EndTimeZoneId"), isAllDay); // DUE property
            vToDo.CompletedUtc  = cal.CreateDateProp(row.Field<DateTime?>("ToDoCompletedUtc"), DateTimeKind.Utc);                   // COMPLETED property
            vToDo.PercentComplete = cal.CreateIntegerProp(row.Field<byte?>("ToDoPercentComplete"));                                 // PERCENT-COMPLETE
        }

        // Get custom properties and custom parameters
        Guid eventComponentId = row.Field<Guid>("EventComponentId");
        IEnumerable<DataRow> rowsEventCustomProperties = rowsCustomProperties.Where(x => x.Field<Guid>("ParentId") == eventComponentId);
        ReadCustomProperties(sc, rowsEventCustomProperties);
    }

    private static void ReadRecurrenceExceptions(IPropertyList<ICalDateList> recurrenceExceptions, IEnumerable<DataRow> rowsRecurrenceExceptions, ICalendar2 cal)
    {
        foreach (DataRow rowRecurrenceException in rowsRecurrenceExceptions)
        {
            // EXDATE property
            ICalDateList exdate = cal.CreateCalDateListProp(
                new DateTime[] { rowRecurrenceException.Field<DateTime>("ExceptionDate") }                          // EXDATE value
                , rowRecurrenceException.Field<string>("TimeZoneId")                                                // EXDATE TZID param
                , rowRecurrenceException.Field<bool?>("AllDay").GetValueOrDefault()                                 // EXDATE DATE or DATE-TIME
                );
            recurrenceExceptions.Add(exdate);
        }
    }

    private void ReadAlarms(IComponentList<IAlarm> alarms, IEnumerable<DataRow> rowsAlarms, ICalendar2 cal)
    {
        foreach (DataRow rowAlarm in rowsAlarms)
        {
            IAlarm alarm = alarms.CreateComponent();                                            // VALARM component

            alarm.Action        = cal.CreateActionProp(rowAlarm.Field<string>("Action"));       // Alarm ACTION property
            alarm.Summary       = cal.CreateCalTextProp(rowAlarm.Field<string>("Summary"));     // Alarm SUMMARY property
            alarm.Description   = cal.CreateCalTextProp(rowAlarm.Field<string>("Description")); // Alarm DESCRIPTION property
            alarm.Duration      = cal.CreateDurationProp(rowAlarm.Field<long?>("Duration"));    // Alarm DURATION property
            alarm.Repeat        = cal.CreateIntegerProp(rowAlarm.Field<int?>("Repeat"));        // Alarm REPEAT property

            // Alarm TRIGGER property
            alarm.Trigger = cal.CreateProperty<ITrigger>();

            DateTime? absolute = rowAlarm.Field<DateTime?>("TriggerAbsoluteDateTimeUtc");
            if (absolute != null)
            {
                alarm.Trigger.AbsoluteDateTimeUtc = DateTime.SpecifyKind(absolute.Value, DateTimeKind.Utc);
            }

            long? offset = rowAlarm.Field<long?>("TriggerRelativeOffset");
            if (offset != null)
            {
                alarm.Trigger.RelativeOffset = new TimeSpan(offset.Value);
            }

            // Alarm trigger RELATED param
            bool? related = rowAlarm.Field<bool?>("TriggerRelatedStart");
            if (related != null)
            {
                alarm.Trigger.Related = related.Value ? RelatedType.Start : RelatedType.End;
            }

            // Get custom properties and custom parameters
            Guid alarmId = rowAlarm.Field<Guid>("AlarmId");
            IEnumerable<DataRow> rowsEventCustomProperties = rowsCustomProperties.Where(x => x.Field<Guid>("ParentId") == alarmId);
            ReadCustomProperties(alarm, rowsEventCustomProperties);

            alarms.Add(alarm);
        }
    }

    private void ReadAttendees(IPropertyList<IAttendee> attendees, IEnumerable<DataRow> rowsAttendees, ICalendar2 cal)
    {
        foreach (DataRow rowAttendee in rowsAttendees)
        {                
            IAttendee attendee = attendees.CreateProperty();                                                // ATTENDEE property

            attendee.Uri            = EmailToUri(rowAttendee.Field<string>("Email"));                       // Attendee value
            attendee.CommonName     = rowAttendee.Field<string>("CommonName");                              // Attendee CN parameter
            attendee.Dir            = rowAttendee.Field<string>("DirectoryEntryRef");                       // Attendee DIR parameter
            attendee.Language       = rowAttendee.Field<string>("Language");                                // Attendee LANGUAGE parameter
            attendee.UserType       = StringToEnum<CalendarUserType>(rowAttendee.Field<string>("UserType"));// Attendee CUTYPE parameter
            attendee.SentBy         = EmailToUri(rowAttendee.Field<string>("SentBy"));                      // Attendee SENT-BY parameter
            attendee.DelegatedFrom  = new[] { EmailToUri(rowAttendee.Field<string>("DelegatedFrom")) };     // Attendee DELEGATED-FROM parameter, here we assume only 1 delegator for the sake of simplicity
            attendee.DelegatedTo    = new[] { EmailToUri(rowAttendee.Field<string>("DelegatedTo")) };       // Attendee DELEGATED-TO parameter, here we assume only 1 delegatee for the sake of simplicity

            // Attendee RSVP parameter
            bool? rsvp = rowAttendee.Field<bool?>("Rsvp");
            if (rsvp != null)
            {
                attendee.Rsvp       = rsvp.Value ? RsvpType.True : RsvpType.False;
            }

            attendee.ParticipationRole   = StringToEnum<ParticipationRoleType>(rowAttendee.Field<string>("ParticipationRole"));     // Attendee ROLE parameter
            attendee.ParticipationStatus = StringToEnum<ParticipationStatusType>(rowAttendee.Field<string>("ParticipationStatus")); // Attendee PARTSTAT parameter

            AddParamValues(rowAttendee.Field<Guid>("AttendeeId"), attendee.RawProperty); // Add custom parameters from [cal_CustomProperty] table.

            attendees.Add(attendee);
        }
    }

    private async Task ReadAttachmentsAsync(DavContext context, IPropertyList<IMedia> attachments, IEnumerable<DataRow> rowsAttachments, ICalendar2 cal)
    {
        // Find if any attachments content should be read from datatbase.
        bool loadContent = rowsAttachments.Any(x => (x.Field<int>("ContentExists") == 1));

        if (loadContent)
        {
            // Reading attachments content from database.

            // Set timeout to maximum value to be able to download iCalendar files with large file attachments.
            System.Web.HttpContext.Current.Server.ScriptTimeout = int.MaxValue;

            Guid eventComponentId = rowsAttachments.First().Field<Guid>("EventComponentId");
            string sql = "SELECT [AttachmentId], [MediaType], [ExternalUrl], [Content] FROM [cal_Attachment] WHERE [EventComponentId]=@EventComponentId";

            using (SqlDataReader reader = await context.ExecuteReaderAsync(CommandBehavior.SequentialAccess, sql, "@EventComponentId", eventComponentId))                
            {
                while(await reader.ReadAsync())
                {
                    IMedia attachment = attachments.CreateProperty();                   // ATTACH property
                    Guid attachmentId = await reader.GetFieldValueAsync<Guid>(reader.GetOrdinal("AttachmentId"));

                    // Attachment FMTTYPE parameter
                    int ordMediaType = reader.GetOrdinal("MediaType");
                    if (!await reader.IsDBNullAsync(ordMediaType))
                    {
                        attachment.MediaType = await reader.GetFieldValueAsync<string>(ordMediaType);
                    }

                    // Attachment value as URL
                    int ordExternalUrl = reader.GetOrdinal("ExternalUrl");
                    if (!await reader.IsDBNullAsync(ordExternalUrl))
                    {
                        attachment.Uri = await reader.GetFieldValueAsync<string>(ordExternalUrl);
                    }

                    // Attachment value as inline content
                    int ordContent = reader.GetOrdinal("Content");
                    if (!await reader.IsDBNullAsync(ordContent))
                    {
                        using (Stream stream = reader.GetStream(ordContent))
                        {
                            using(MemoryStream memory = new MemoryStream())
                            {
                                await stream.CopyToAsync(memory);
                                attachment.Base64Data = Convert.ToBase64String(memory.ToArray());
                            }
                        }
                    }

                    AddParamValues(attachmentId, attachment.RawProperty); // Add custom parameters from [cal_CustomProperty] table.

                    attachments.Add(attachment);
                }
            }
        }
        else
        {
            // Attachments contain only URLs to external files.
            foreach (DataRow rowAttachment in rowsAttachments)
            {
                IMedia attachment = attachments.CreateProperty();                   // ATTACH property

                attachment.MediaType = rowAttachment.Field<string>("MediaType");    // Attachment FMTTYPE parameter
                attachment.Uri       = rowAttachment.Field<string>("ExternalUrl");  // Attachment value

                AddParamValues(rowAttachment.Field<Guid>("AttachmentId"), attachment.RawProperty); // Add custom parameters from [cal_CustomProperty] table.

                attachments.Add(attachment);
            }
        }
    }

    private static void ReadCustomProperties(IComponent component, IEnumerable<DataRow> rowsCustomProperies)
    {
        foreach (DataRow rowCustomProperty in rowsCustomProperies)
        {
            string propertyName = rowCustomProperty.Field<string>("PropertyName");

            IRawProperty prop;
            if (!component.Properties.ContainsKey(propertyName))
            {
                prop = component.CreateRawProperty();
                component.AddProperty(propertyName, prop);
            }
            else
            {
                prop = component.Properties[propertyName].FirstOrDefault();
            }

            string paramName = rowCustomProperty.Field<string>("ParameterName");
            string value = rowCustomProperty.Field<string>("Value");
            if (paramName == null)
            {
                // If ParameterName is null the Value contains property value
                prop.RawValue = value;
            }
            else
            {
                AddParamValue(prop, paramName, value);
            }
        }
    }

    private void AddParamValues(Guid propertyId, IRawProperty prop)
    {
        IEnumerable<DataRow> rowsCustomParams = rowsCustomProperties.Where(x => x.Field<Guid>("ParentId") == propertyId);
        foreach (DataRow rowCustomParam in rowsCustomParams)
        {
            string paramName = rowCustomParam.Field<string>("ParameterName");
            string paramValue = rowCustomParam.Field<string>("Value");
            AddParamValue(prop, paramName, paramValue);
        }
    }

    private static void AddParamValue(IRawProperty prop, string paramName, string paramValue)
    {
        // There could be parameters with identical name withing one property.

        // This call returns all values from all properties with specified name.
        IEnumerable<string> paramVals = prop.Parameters[paramName];

        // Add value.
        List<string> paramNewVals = paramVals.ToList();
        paramNewVals.Add(paramValue);

        // This call removes any parameters with identical names if any and 
        // replaces it with a single parameter with a lost of specified values.
        prop.Parameters[paramName] = paramNewVals;
    }

    private static string EmailToUri(string email)
    {
        if (email == null)
            return null;

        if (email.IndexOf('@') > 0)
            return string.Format("mailto:{0}", email);

        return email;
    }

    private static T StringToEnum<T>(string value) where T : ExtendibleEnum, new()
    {
        if (value == null)
            return null;

        T res;
        if (!ExtendibleEnum.TryFromString<T>(value, out res))
        {
            // If no matching value is found create new ExtendibleEnum or type T 
            // with specified string value and default numeric value (-1).
            res = new T();
            res.Name = value;
        }

        return res;
    }
}

' +--- Calendar file [UID1].ics ---------+
' |                                      |
' |  +-- Time zone component ---------+  | -- Time zones are are not stored in DB, they are generated automatically 
' |  | TZID: Zone X                   |  |    during serialization, based on TZIDs found in event or to-do.
' |  | ...                            |  |
' |  +--------------------------------+  |
' |                                      |
' |  +-- Time zone component ---------+  |
' |  | TZID: Zone Y                   |  | -- Time zone IDs could be either IANA (Olson) IDs or System (Windows) IDs.
' |  | ...                            |  |
' |  +--------------------------------+  |
' |  ...                                 |
' |                                      |
' |                                      |
' |  +-- Event component -------------+  | -- Event / do-do components are stored in [cal_EventComponent] table.
' |  | UID: [UID1]                    |  |    
' |  | RRULE: FREQ=DAILY              |  | 
' |  | SUMMARY: Event A               |  |
' |  | ...                            |  |
' |  +--------------------------------+  |
' |                                      |
' |  +-- Event component -------------+  | -- In case of recurring events/to-dos there could be more than one component
' |  | UID: [UID1]                    |  |    per file. All event/to-do components within a single calendar file share
' |  | RECURRENCE-ID: 20151028        |  |    the same UID but have different RECURRENCE-IDs. 
' |  | SUMMARY: Instance 5 of Event A |  |    
' |  | ...                            |  |    iOS / OS X UIDs are case sensitive (uppercase GUIDs).
' |  +--------------------------------+  |    Bynari WebDAV Collaborator for MS Outlook UIDs are over 100 chars in length.
' |  ...                                 |
' |                                      |
' |                                      |
' +--------------------------------------+
' 
' 
' 
'    +-- Event component -------------+
'    |                                |
'    | UID: [UID1]                    | 
'    | SUMMARY: Event A               |
'    | START: 20151016T080000         |
'    | RRULE: FREQ=DAILY              |
'    | ...                            |
'    |                                |
'    | EXDATE: 20151018T080000        | -- Recurrence exception dates are stored in [cal_RecurrenceException] table.
'    | EXDATE: 20151020T080000        |
'    | ...                            |
'    |                                |
'    | ATTENDEE: mail1@server.com     | -- Attendees are stored in [cal_Attendee] table.
'    | ATTENDEE: mail2@srvr.com       |
'    | ...                            |
'    |                                |
'    | ATTACH: /9j/4VGuf+Sw...        | -- Attachments are stored in [cal_Attachment] table.
'    | ATTACH: https:'serv/file.docx |
'    | ...                            |
'    |                                |
'    |  +-- Alarm Component -------+  | -- Alarms are stored in [cal_Alarm] table.
'    |  | ACTION: DISPLAY          |  |
'    |  | ...                      |  |
'    |  +--------------------------+  |
'    |                                |
'    |  +-- Alarm Component -------+  |
'    |  | ACTION: EMAIL            |  |
'    |  | ...                      |  |
'    |  +--------------------------+  |
'    |  ...                           |
'    |                                |
'    +--------------------------------+
Public Class CalendarFile
    Inherits DavHierarchyItem
    Implements ICalendarFile

    Public Shared Extension As String = ".ics"

    Public Shared Async Function LoadByCalendarFolderIdAsync(context As DavContext, calendarFolderId As Guid, propsToLoad As PropsToLoad) As Task(Of IEnumerable(Of ICalendarFile))
        ' propsToLoad == PropsToLoad.Minimum -> Typical GetChildren call by iOS, Android, eM Client, etc CalDAV clients
        ' [Summary] is typically not required in GetChildren call, 
        ' they are extracted for demo purposes only, to be displayed in Ajax File Browser.
        ' propsToLoad == PropsToLoad.All -> Bynari call, it requires all props in GetChildren call.
        If propsToLoad <> PropsToLoad.Minimum Then Throw New NotImplementedException("LoadByCalendarFolderIdAsync is implemented only with PropsToLoad.Minimum.")
        Dim sql As String = "SELECT * FROM [cal_CalendarFile] 
                       WHERE [CalendarFolderId] = @CalendarFolderId
                       AND [CalendarFolderId] IN (SELECT [CalendarFolderId] FROM [cal_Access] WHERE [UserId]=@UserId)

                      ;SELECT [UID], [Summary] FROM [cal_EventComponent] 
                       WHERE [UID] IN (SELECT [UID] FROM [cal_CalendarFile] 
                                       WHERE [CalendarFolderId] = @CalendarFolderId)"
        sql = String.Format(sql, GetScPropsToLoad(propsToLoad))
        Return Await LoadAsync(context, sql,
                              "@UserId", context.UserId,
                              "@CalendarFolderId", calendarFolderId)
    End Function

    Public Shared Async Function LoadByUidsAsync(context As DavContext, uids As IEnumerable(Of String), propsToLoad As PropsToLoad) As Task(Of IEnumerable(Of ICalendarFile))
        ' Get IN clause part with list of file UIDs for SELECT.
        Dim selectIn As String = String.Join(", ", uids.Select(Function(a) String.Format("'{0}'", a)).ToArray())
        Dim sql As String = "SELECT * FROM [cal_CalendarFile] 
                       WHERE [UID] IN ({1})
                       AND [CalendarFolderId] IN (SELECT [CalendarFolderId] FROM [cal_Access] WHERE [UserId]=@UserId)"
        If propsToLoad = PropsToLoad.All Then
            ' Here we do not select attachments content because it could be very large,
            ' we only set [ContentExists] flag marking that it should be loaded during IContent.ReadAsync call.
            sql += ";SELECT * FROM [cal_EventComponent]      WHERE [UID] IN ({1})
                     ;SELECT * FROM [cal_RecurrenceException] WHERE [UID] IN ({1})
                     ;SELECT * FROM [cal_Alarm]               WHERE [UID] IN ({1})
                     ;SELECT * FROM [cal_Attendee]            WHERE [UID] IN ({1})
                     ;SELECT * FROM [cal_CustomProperty]      WHERE [UID] IN ({1})
                     ;SELECT [AttachmentId], [EventComponentId], [UID], [MediaType], [ExternalUrl], 
                         (CASE WHEN [Content] IS NULL THEN 0 ELSE 1 END) AS [ContentExists] 
                               FROM [cal_Attachment]          WHERE [UID] IN ({1})"
        End If

        sql = String.Format(sql, GetScPropsToLoad(propsToLoad), selectIn)
        Return Await LoadAsync(context, sql, "@UserId", context.UserId)
    End Function

    Private Shared Async Function LoadAsync(context As DavContext, sql As String, ParamArray prms As Object()) As Task(Of IEnumerable(Of ICalendarFile))
        Dim items As IList(Of ICalendarFile) = New List(Of ICalendarFile)()
        Dim stopWatch As Stopwatch = Stopwatch.StartNew()
        Using reader As SqlDataReader = Await context.ExecuteReaderAsync(sql, prms)
            Dim calendarFiles As DataTable = New DataTable()
            calendarFiles.Load(reader)
            Dim eventComponents As DataTable = New DataTable()
            If Not reader.IsClosed Then eventComponents.Load(reader)
            Dim recurrenceExceptions As DataTable = New DataTable()
            If Not reader.IsClosed Then recurrenceExceptions.Load(reader)
            Dim alarms As DataTable = New DataTable()
            If Not reader.IsClosed Then alarms.Load(reader)
            Dim attendees As DataTable = New DataTable()
            If Not reader.IsClosed Then attendees.Load(reader)
            Dim customProperties As DataTable = New DataTable()
            If Not reader.IsClosed Then customProperties.Load(reader)
            Dim attachments As DataTable = New DataTable()
            If Not reader.IsClosed Then attachments.Load(reader)
            stopWatch.Stop()
            context.Engine.Logger.LogDebug(String.Format("SQL took: {0}ms", stopWatch.ElapsedMilliseconds))
            For Each rowCalendarFile As DataRow In calendarFiles.Rows
                Dim rowsEventComponents As DataRow() = New DataRow(-1) {}
                Dim rowsRecurrenceExceptions As DataRow() = New DataRow(-1) {}
                Dim rowsAlarms As DataRow() = New DataRow(-1) {}
                Dim rowsAttendees As DataRow() = New DataRow(-1) {}
                Dim rowsCustomProperties As DataRow() = New DataRow(-1) {}
                Dim rowsAttachments As DataRow() = New DataRow(-1) {}
                Dim uid As String = rowCalendarFile.Field(Of String)("UID")
                Dim filter As String = String.Format("UID = '{0}'", uid)
                If eventComponents.Columns("UID") IsNot Nothing Then rowsEventComponents = eventComponents.Select(filter)
                If recurrenceExceptions.Columns("UID") IsNot Nothing Then rowsRecurrenceExceptions = recurrenceExceptions.Select(filter)
                If alarms.Columns("UID") IsNot Nothing Then rowsAlarms = alarms.Select(filter)
                If attendees.Columns("UID") IsNot Nothing Then rowsAttendees = attendees.Select(filter)
                If customProperties.Columns("UID") IsNot Nothing Then rowsCustomProperties = customProperties.Select(filter)
                If attachments.Columns("UID") IsNot Nothing Then rowsAttachments = attachments.Select(filter)
                items.Add(New CalendarFile(context, uid, rowCalendarFile, rowsEventComponents, rowsRecurrenceExceptions, rowsAlarms, rowsAttendees, rowsCustomProperties, rowsAttachments))
            Next
        End Using

        Return items
    End Function

    Private Shared Function GetScPropsToLoad(propsToLoad As PropsToLoad) As String
        Select Case propsToLoad
            Case PropsToLoad.None
                Return "[UID]"
            Case PropsToLoad.Minimum
                ' [Summary] is typically not required in GetChildren call, 
                ' they are extracted for demo purposes only, to be displayed in Ajax File Browser as a file display name.
                Return "[UID], [Summary]"
            Case PropsToLoad.All
                Return "*"
        End Select

        Throw New Exception("Should never come here.")
    End Function

    Public Shared Function CreateCalendarFile(context As DavContext, calendarFolderId As Guid) As CalendarFile
        Dim calendarFile As CalendarFile = New CalendarFile(context, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)
        calendarFile.calendarFolderId = calendarFolderId
        Return calendarFile
    End Function

    Private ReadOnly uid As String = Nothing

    Private ReadOnly rowCalendarFile As DataRow = Nothing

    Private ReadOnly rowsEventComponents As DataRow() = Nothing

    Private ReadOnly rowsRecurrenceExceptions As DataRow() = Nothing

    Private ReadOnly rowsAlarms As DataRow() = Nothing

    Private ReadOnly rowsAttendees As DataRow() = Nothing

    Private ReadOnly rowsCustomProperties As DataRow() = Nothing

    Private ReadOnly rowsAttachments As DataRow() = Nothing

    Private ReadOnly Property isNew As Boolean
        Get
            Return calendarFolderId <> Guid.Empty
        End Get
    End Property

    Private paramIndex As Integer = 0

    Private calendarFolderId As Guid = Guid.Empty

    Public Overrides ReadOnly Property Name As String Implements IHierarchyItemBase.Name
        Get
            ' Show all components summaries contained in this file.
            Return String.Join(", ", rowsEventComponents.Select(Function(x) String.Format("[{0}]", x.Field(Of String)("Summary"))).ToArray())
        End Get
    End Property

    Public Overrides ReadOnly Property Path As String Implements IHierarchyItemBase.Path
        Get
            Dim calendarFolderId As Guid = rowCalendarFile.Field(Of Guid)("CalendarFolderId")
            Dim uid As String = rowCalendarFile.Field(Of String)("UID")
            Return String.Format("{0}{1}/{2}{3}", CalendarsRootFolder.CalendarsRootFolderPath, calendarFolderId, uid, Extension)
        End Get
    End Property

    Public ReadOnly Property Etag As String Implements IContent.Etag
        Get
            Dim bETag As Byte() = rowCalendarFile.Field(Of Byte())("ETag")
            Return BitConverter.ToUInt64(bETag.Reverse().ToArray(), 0).ToString()
        End Get
    End Property

    Public Overrides ReadOnly Property Created As DateTime Implements IHierarchyItemBase.Created
        Get
            Return rowCalendarFile.Field(Of DateTime)("CreatedUtc")
        End Get
    End Property

    Public Overrides ReadOnly Property Modified As DateTime Implements IHierarchyItemBase.Modified
        Get
            Return rowCalendarFile.Field(Of DateTime)("ModifiedUtc")
        End Get
    End Property

    Public ReadOnly Property ContentLength As Long Implements IContent.ContentLength
        Get
            Return -1
        End Get
    End Property

    Public ReadOnly Property ContentType As String Implements IContent.ContentType
        Get
            Return "text/calendar"
        End Get
    End Property

    Private Sub New(context As DavContext, uid As String,
                   rowCalendarFile As DataRow, rowsEventComponents As DataRow(), rowsRecurrenceExceptions As DataRow(), rowsAlarms As DataRow(),
                   rowsAttendees As DataRow(), rowsCustomProperties As DataRow(), rowsAttachments As DataRow())
        MyBase.New(context)
        Me.uid = uid
        Me.rowCalendarFile = rowCalendarFile
        Me.rowsEventComponents = rowsEventComponents
        Me.rowsRecurrenceExceptions = rowsRecurrenceExceptions
        Me.rowsAlarms = rowsAlarms
        Me.rowsAttendees = rowsAttendees
        Me.rowsCustomProperties = rowsCustomProperties
        Me.rowsAttachments = rowsAttachments
    End Sub

    Public Async Function WriteAsync(stream As Stream, contentType As String, startIndex As Long, totalFileSize As Long) As Task(Of Boolean) Implements IContent.WriteAsync
        'Set timeout to maximum value to be able to upload iCalendar files with large file attachments.
        System.Web.HttpContext.Current.Server.ScriptTimeout = Integer.MaxValue
        Dim iCalendar As String
        Using reader As StreamReader = New StreamReader(stream)
            iCalendar = Await reader.ReadToEndAsync()
        End Using

        ' Typically the stream contains a single iCalendar that contains one or more event or to-do components.
        Dim calendars As IEnumerable(Of IComponent) = New vFormatter().Deserialize(iCalendar)
        Dim calendar As ICalendar2 = TryCast(calendars.First(), ICalendar2)
        Dim components As IEnumerable(Of IEventBase) = calendar.Events.Cast(Of IEventBase)()
        If Not components.Any() Then
            components = calendar.ToDos.Cast(Of IEventBase)()
        End If

        If components Is Nothing Then Throw New DavException("Event or to-do was expected in the input stream, no events or to-dos were found.", DavStatus.UNSUPPORTED_MEDIA_TYPE)
        ' All components inside calendar file has the same UID which is equal to file name.
        Dim uid As String = components.First().Uid.Text
        ' Save data to [cal_CalendarFile] table.
        Await WriteCalendarFileAsync(Context, uid, calendarFolderId, isNew)
        For Each component As IEventBase In components
            Dim eventComponentId As Guid = Guid.NewGuid()
            ' Save data to [cal_EventComponent] table.
            Await WriteEventComponentAsync(Context, component, eventComponentId, uid)
            ' Save recurrence days exceptions for recurring events and to-dos. 
            Await WriteRecurrenceExceptionsAsync(Context, component.ExceptionDateTimes, eventComponentId, uid)
            ' Save alarms.
            Await WriteAlarmsAsync(Context, component.Alarms, eventComponentId, uid)
            ' Save attendees.
            Await WriteAttendeesAsync(Context, component.Attendees, eventComponentId, uid)
            ' Save attachments.
            Await WriteAttachmentsAsync(Context, component.Attachments, eventComponentId, uid)
        Next

        ' Notify attendees that event is created or modified.
        calendar.Method = calendar.CreateMethodProp(MethodType.Request)
        Await iMipEventSchedulingTransport.NotifyAttendeesAsync(Context, calendar)
        Return True
    End Function

    Private Shared Async Function WriteCalendarFileAsync(context As DavContext, uid As String, calendarFolderId As Guid, isNew As Boolean) As Task
        Dim sql As String
        If isNew Then
            sql = "IF EXISTS (SELECT 1 FROM [cal_Access] WHERE [CalendarFolderId]=@CalendarFolderId AND [UserId]=@UserId AND [Write]=1)
                INSERT INTO [cal_CalendarFile] (
                      [UID]
                    , [CalendarFolderId]
                ) VALUES (
                      @UID
                    , @CalendarFolderId
                )"
        Else
            ' We can only update record in [cal_CalendarFile] table.
            ' There is no way to update [cal_EventComponent], [cal_RecurrenceException], [cal_Alarm], [cal_Attendee], 
            ' [cal_Attachment] and [cal_CustomProperty] for existing event, we must delete all records for this UID and recreate.
            ' [ModifiedUtc] field update triggers [ETag] field update which is used for synchronyzation.
            sql = "IF EXISTS (SELECT 1 FROM [cal_CalendarFile]
                    WHERE UID = @UID
                    AND [CalendarFolderId] IN (SELECT [CalendarFolderId] FROM [cal_Access] WHERE [UserId] = @UserId AND [Write] = 1))
                BEGIN
                    UPDATE [cal_CalendarFile] SET 
                        [ModifiedUtc] = @ModifiedUtc
                    WHERE [UID] = @UID

                    ; DELETE FROM [cal_EventComponent]      WHERE [UID] = @UID
                    ; DELETE FROM [cal_RecurrenceException] WHERE [UID] = @UID
                    ; DELETE FROM [cal_Alarm]               WHERE [UID] = @UID
                    ; DELETE FROM [cal_Attendee]            WHERE [UID] = @UID
                    ; DELETE FROM [cal_Attachment]          WHERE [UID] = @UID
                    ; DELETE FROM [cal_CustomProperty]      WHERE [UID] = @UID
                END"
        End If

        If Await context.ExecuteNonQueryAsync(sql,
                                             "@UID", uid,
                                             "UserId", context.UserId,
                                             "@CalendarFolderId", calendarFolderId,
                                             "@ModifiedUtc", DateTime.UtcNow) < 1 Then
            Throw New DavException("Item not found or you do not have enough permissions to complete this operation.", DavStatus.FORBIDDEN)
        End If
    End Function

    Private Async Function WriteEventComponentAsync(context As DavContext, sc As IEventBase, eventComponentId As Guid, uid As String) As Task
        Dim sql As String = "INSERT INTO [cal_EventComponent] (
                      [EventComponentId]
                    , [UID]
                    , [ComponentType]
                    , [DateTimeStampUtc]
                    , [CreatedUtc]
                    , [LastModifiedUtc]
                    , [Summary]
                    , [Description]
                    , [OrganizerEmail]
                    , [OrganizerCommonName]
                    , [Start]
                    , [StartTimeZoneId]
                    , [End]
                    , [EndTimeZoneId]
                    , [Duration]
                    , [AllDay]
                    , [Class]
                    , [Location]
                    , [Priority]
                    , [Sequence]
                    , [Status]
                    , [Categories]
                    , [RecurFrequency]
                    , [RecurInterval]
                    , [RecurUntil]
                    , [RecurCount]
                    , [RecurWeekStart]
                    , [RecurByDay]
                    , [RecurByMonthDay]
                    , [RecurByMonth]
                    , [RecurBySetPos]
                    , [RecurrenceIdDate]
                    , [RecurrenceIdTimeZoneId]
                    , [RecurrenceIdThisAndFuture]
                    , [EventTransparency]
                    , [ToDoCompletedUtc]
                    , [ToDoPercentComplete]
                ) VALUES (
                      @EventComponentId
                    , @UID
                    , @ComponentType
                    , @DateTimeStampUtc 
                    , @CreatedUtc
                    , @LastModifiedUtc
                    , @Summary
                    , @Description
                    , @OrganizerEmail
                    , @OrganizerCommonName
                    , @Start, @StartTimeZoneId
                    , @End, @EndTimeZoneId
                    , @Duration
                    , @AllDay
                    , @Class
                    , @Location
                    , @Priority
                    , @Sequence
                    , @Status
                    , @Categories
                    , @RecurFrequency
                    , @RecurInterval
                    , @RecurUntil
                    , @RecurCount
                    , @RecurWeekStart
                    , @RecurByDay
                    , @RecurByMonthDay
                    , @RecurByMonth
                    , @RecurBySetPos
                    , @RecurrenceIdDate
                    , @RecurrenceIdTimeZoneId
                    , @RecurrenceIdThisAndFuture
                    , @EventTransparency
                    , @ToDoCompletedUtc
                    , @ToDoPercentComplete
                )"
        Dim isEvent As Boolean = TypeOf sc Is IEvent
        ' Get END in case of event or DUE in case of to-do component. 
        Dim endProp As ICalDate = If(isEvent, TryCast(sc, IEvent).End, TryCast(sc, IToDo).Due)
        Await context.ExecuteNonQueryAsync(sql,
                                          "@EventComponentId", eventComponentId,
                                          "@UID", uid,                                                                   ' UID value
                                          "@ComponentType", isEvent,
                                          "@DateTimeStampUtc", sc.DateTimeStampUtc?.Value?.DateVal,                                   ' DTSTAMP value
                                          "@CreatedUtc", sc.CreatedUtc?.Value?.DateVal,                                         ' CREATED value
                                          "@LastModifiedUtc", sc.LastModifiedUtc?.Value?.DateVal,                                    ' LAST-MODIFIED value
                                          "@Summary", sc.Summary?.Text,                                                      ' SUMMARY value
                                          "@Description", sc.Description?.Text,                                                  ' DESCRIPTION value
                                          "@OrganizerEmail", sc.Organizer?.Uri?.Replace("mailto:", ""),                             ' ORGANIZER value
                                          "@OrganizerCommonName", sc.Organizer?.CommonName,                                              ' ORGANIZER CN param
                                          "@Start", sc.Start?.Value?.DateVal,                                              ' DTSTART value
                                          "@StartTimeZoneId", If(sc.Start?.Value?.DateVal.Kind = DateTimeKind.Utc, TimeZoneInfo.Utc.Id, sc.Start?.TimeZoneId),  ' DTSTART TZID param
                                          "@End", endProp?.Value?.DateVal,                                               ' DTEND or DUE value
                                          "@EndTimeZoneId", If(endProp?.Value?.DateVal.Kind = DateTimeKind.Utc, TimeZoneInfo.Utc.Id, endProp?.TimeZoneId),    ' DTEND or DUE TZID param
                                          "@Duration", CType(sc.Duration?.Value, TimeSpan?)?.Ticks,                                             ' DURATION value
                                          "@AllDay", Not sc.Start?.Value?.Components.HasFlag(DateComponents.Time), ' Check if start contains the time part to determine if this is a all-day event/to-do.
                                          "@Class", sc.Class?.Value.Name,                                                  ' CLASS value
                                          "@Location", sc.Location?.Text,                                                     ' LOCATION value
                                          "@Priority", sc.Priority?.Value,                                                    ' PRIORITY value
                                          "@Sequence", sc.Sequence?.Value,                                                    ' SEQUENCE value
                                          "@Status", sc.Status?.Value.Name,                                                 ' STATUS value
                                          "@Categories", ListToString(Of String)(sc.Categories.Select(Function(x) ListToString(Of String)(x.Categories, ",")), ";"), ' CATEGORIES value
                                          "@RecurFrequency", sc.RecurrenceRule?.Frequency?.ToString(),                              ' RRULE FREQ value part
                                          "@RecurInterval", CType(sc.RecurrenceRule?.Interval, Integer?),                                     ' RRULE INTERVAL value part
                                          "@RecurUntil", sc.RecurrenceRule?.Until?.DateVal,                                     ' RRULE UNTIL value part
                                          "@RecurCount", CType(sc.RecurrenceRule?.Count, Integer?),                                        ' RRULE COUNT value part
                                          "@RecurWeekStart", CType(sc.RecurrenceRule?.WeekStart, DayOfWeek?)?.ToString(),                              ' RRULE WKST value part
                                          "@RecurByDay", ListToString(Of DayRule)(sc.RecurrenceRule?.ByDay),                       ' RRULE BYDAY value part
                                          "@RecurByMonthDay", ListToString(Of Short)(sc.RecurrenceRule?.ByMonthDay),                    ' RRULE BYMONTHDAY value part
                                          "@RecurByMonth", ListToString(Of UShort)(sc.RecurrenceRule?.ByMonth),                      ' RRULE BYMONTH value part
                                          "@RecurBySetPos", ListToString(Of Short)(sc.RecurrenceRule?.BySetPos),                      ' RRULE BYSETPOS value part
                                          "@RecurrenceIdDate", sc.RecurrenceId?.Value.DateVal,                                        ' RECURRENCE-ID value
                                          "@RecurrenceIdTimeZoneId", sc.RecurrenceId?.TimeZoneId,                                           ' RECURRENCE-ID TZID param
                                          "@RecurrenceIdThisAndFuture", sc.RecurrenceId?.IsThisAndFuture,                                    ' RECURRENCE-ID RANGE param
                                          "@EventTransparency", TryCast(sc, IEvent)?.Transparency?.IsTransparent,                           ' VEVENT TRANSP value
                                          "@ToDoCompletedUtc", TryCast(sc, IToDo)?.CompletedUtc?.Value?.DateVal,                           ' VTODO COMPLETED value
                                          "@ToDoPercentComplete", TryCast(sc, IToDo)?.PercentComplete?.Value                                 ' VTODO PERCENT-COMPLETE value
                                          )
        ' Save custom properties and parameters of this component to [cal_CustomProperty] table.
        Dim customPropsSqlInsert As String
        Dim customPropsParamsInsert As List(Of Object)
        If PrepareSqlCustomPropertiesOfComponentAsync(sc, eventComponentId, uid, customPropsSqlInsert, customPropsParamsInsert) Then
            Await context.ExecuteNonQueryAsync(customPropsSqlInsert, customPropsParamsInsert.ToArray())
        End If
    End Function

    Private Shared Function ListToString(Of T)(arr As IEnumerable(Of T), Optional separator As String = ",") As String
        If(arr Is Nothing) OrElse Not arr.Any() Then Return Nothing
        Return String.Join(Of T)(separator, arr)
    End Function

    Private Shared Async Function WriteRecurrenceExceptionsAsync(context As DavContext, recurrenceExceptions As IPropertyList(Of ICalDateList), eventComponentId As Guid, uid As String) As Task
        ' Typically CalDAV clients pass a single date value per EXDATE property.
        Dim sql As String = "INSERT INTO [cal_RecurrenceException] (
                  [EventComponentId]
                , [UID]
                , [ExceptionDate]
                , [TimeZoneId]
                , [AllDay]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        Dim parameters As List(Of Object) = New List(Of Object)(New Object() {"@EventComponentId", eventComponentId,
                                                                             "@UID", uid
                                                                             })
        Dim i As Integer = 0
        For Each dateListProp As ICalDateList In recurrenceExceptions
            For Each [date] As [Date] In dateListProp.Dates
                If [date] Is Nothing Then Continue For
                valuesSql.Add(String.Format("(
                  @EventComponentId
                , @UID
                , @ExceptionDate{0}
                , @TimeZoneId{0}
                , @AllDay{0}
                )", i))
                parameters.AddRange(New Object() {"@ExceptionDate" & i, [date].DateVal,                                                                         ' EXDATE value
                                                 "@TimeZoneId" & i, If([date].DateVal.Kind = DateTimeKind.Utc, TimeZoneInfo.Utc.Id, dateListProp.TimeZoneId),' EXDATE TZID param
                                                 "@AllDay" & i, Not [date].Components.HasFlag(DateComponents.Time)                                        ' EXDATE DATE or DATE-TIME
                                                 })
            Next

            i += 1
        Next

        If i > 0 Then
            Await context.ExecuteNonQueryAsync(String.Format(sql, String.Join(", ", valuesSql.ToArray())), parameters.ToArray())
        End If
    End Function

    Private Async Function WriteAlarmsAsync(context As DavContext, alarms As IComponentList(Of IAlarm), eventComponentId As Guid, uid As String) As Task
        Dim sql As String = "INSERT INTO [cal_Alarm] (
                  [AlarmId]
                , [EventComponentId]
                , [UID]
                , [Action]
                , [TriggerAbsoluteDateTimeUtc]
                , [TriggerRelativeOffset]
                , [TriggerRelatedStart]
                , [Summary]
                , [Description]
                , [Duration]
                , [Repeat]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        Dim parameters As List(Of Object) = New List(Of Object)(New Object() {"@EventComponentId", eventComponentId,
                                                                             "@UID", uid
                                                                             })
        Dim i As Integer = 0
        For Each alarm As IAlarm In alarms
            Dim alarmId As Guid = Guid.NewGuid()
            valuesSql.Add(String.Format("(
                  @AlarmId{0}
                , @EventComponentId
                , @UID
                , @Action{0}
                , @TriggerAbsoluteDateTimeUtc{0}
                , @TriggerRelativeOffset{0}
                , @TriggerRelatedStart{0}
                , @Summary{0}
                , @Description{0}
                , @Duration{0}
                , @Repeat{0}
                )", i))
            parameters.AddRange(New Object() {"@AlarmId" & i, alarmId,
                                             "@Action" & i, alarm.Action.Action.Name,                                                        ' Alarm ACTION property
                                             "@TriggerAbsoluteDateTimeUtc" & i, alarm.Trigger?.AbsoluteDateTimeUtc,                                              ' Alarm TRIGGER property
                                             "@TriggerRelativeOffset" & i, CType(alarm.Trigger?.RelativeOffset, TimeSpan?)?.Ticks,                                            ' Alarm TRIGGER property
                                             "@TriggerRelatedStart" & i, If(alarm.Trigger Is Nothing, CType(Nothing, Boolean?), alarm.Trigger.Related = RelatedType.Start),  ' Alarm trigger RELATED param
                                             "@Summary" & i, alarm.Summary?.Text,                                                             ' Alarm SUMMARY property
                                             "@Description" & i, alarm.Description?.Text,                                                         ' Alarm DESCRIPTION property
                                             "@Duration" & i, CType(alarm.Duration?.Value, TimeSpan?)?.Ticks,                                                    ' Alarm DURATION property
                                             "@Repeat" & i, alarm.Repeat?.Value                                                             ' Alarm REPEAT property
                                             })
            ' Create SQL to save custom properties of this component of this component to [cal_CustomProperty] table.
            Dim customPropsSqlInsert As String
            Dim customPropsParamsInsert As List(Of Object)
            If PrepareSqlCustomPropertiesOfComponentAsync(alarm, alarmId, uid, customPropsSqlInsert, customPropsParamsInsert) Then
                sql += "; " & customPropsSqlInsert
                parameters.AddRange(customPropsParamsInsert)
            End If

            i += 1
        Next

        If i > 0 Then
            Await context.ExecuteNonQueryAsync(String.Format(sql, String.Join(", ", valuesSql.ToArray())), parameters.ToArray())
        End If
    End Function

    Private Async Function WriteAttendeesAsync(context As DavContext, attendees As IPropertyList(Of IAttendee), eventComponentId As Guid, uid As String) As Task
        Dim sql As String = "INSERT INTO [cal_Attendee] (
                  [AttendeeId]
                , [EventComponentId]
                , [UID]
                , [Email]
                , [CommonName]
                , [DirectoryEntryRef]
                , [Language]
                , [UserType]
                , [SentBy]
                , [DelegatedFrom]
                , [DelegatedTo]
                , [Rsvp]
                , [ParticipationRole]
                , [ParticipationStatus]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        Dim parameters As List(Of Object) = New List(Of Object)(New Object() {"@EventComponentId", eventComponentId,
                                                                             "@UID", uid
                                                                             })
        Dim i As Integer = 0
        For Each attendee As IAttendee In attendees
            valuesSql.Add(String.Format("(
                  @AttendeeId{0}
                , @EventComponentId
                , @UID
                , @Email{0}
                , @CommonName{0}
                , @DirectoryEntryRef{0}
                , @Language{0}
                , @UserType{0}
                , @SentBy{0}
                , @DelegatedFrom{0}
                , @DelegatedTo{0}
                , @Rsvp{0}
                , @ParticipationRole{0}
                , @ParticipationStatus{0}
            )", i))
            Dim attendeeId As Guid = Guid.NewGuid()
            parameters.AddRange(New Object() {"@AttendeeId" & i, attendeeId,
                                             "@Email" & i, attendee.Uri?.Replace("mailto:", ""),    ' Attendee value
                                             "@CommonName" & i, attendee.CommonName,                     ' Attendee CN parameter
                                             "@DirectoryEntryRef" & i, attendee.Dir,                            ' Attendee DIR parameter
                                             "@Language" & i, attendee.Language,                       ' Attendee LANGUAGE parameter
                                             "@UserType" & i, attendee.UserType?.Name,                 ' Attendee CUTYPE parameter
                                             "@SentBy" & i, attendee.SentBy,                         ' Attendee SENT-BY parameter
                                             "@DelegatedFrom" & i, attendee.DelegatedFrom.FirstOrDefault(), ' Attendee DELEGATED-FROM parameter, here we assume only 1 delegator for the sake of simplicity
                                             "@DelegatedTo" & i, attendee.DelegatedTo.FirstOrDefault(),   ' Attendee DELEGATED-TO parameter, here we assume only 1 delegatee for the sake of simplicity
                                             "@Rsvp" & i, attendee.Rsvp = RsvpType.True,          ' Attendee RSVP parameter
                                             "@ParticipationRole" & i, attendee.ParticipationRole?.Name,        ' Attendee ROLE parameter
                                             "@ParticipationStatus" & i, attendee.ParticipationStatus?.Name      ' Attendee PARTSTAT parameter
                                             })
            ' Prepare SQL to save custom property parameters to [cal_CustomProperty] table.
            Dim customPropSqlInsert As String
            Dim customPropParametersInsert As List(Of Object)
            If PrepareSqlParamsWriteCustomProperty("ATTENDEE", attendee.RawProperty, attendeeId, uid, customPropSqlInsert, customPropParametersInsert) Then
                sql += "; " & customPropSqlInsert
                parameters.AddRange(customPropParametersInsert)
            End If

            i += 1
        Next

        If i > 0 Then
            Await context.ExecuteNonQueryAsync(String.Format(sql, String.Join(", ", valuesSql.ToArray())), parameters.ToArray())
        End If
    End Function

    Private Async Function WriteAttachmentsAsync(context As DavContext, attachments As IPropertyList(Of IMedia), eventComponentId As Guid, uid As String) As Task
        ' It is recommended to keep attchment size below 256Kb. In case files over 1Mb should 
        ' be stored, use SQL FILESTREAM, FileTable or store content in file system.
        Dim sqlAttachment As String = "INSERT INTO [cal_Attachment] (
                  [AttachmentId]
                , [EventComponentId]
                , [UID]
                , [MediaType]
                , [ExternalUrl]
                , [Content]
            ) VALUES (
                  @AttachmentId
                , @EventComponentId
                , @UID
                , @MediaType
                , @ExternalUrl
                , @Content
            )"
        Dim customPropertiesSql As String = ""
        Dim customPropertiesParameters As List(Of Object) = New List(Of Object)()
        For Each attachment As IMedia In attachments
            ' To insert NULL to VARBINARY column SqlParameter must be passed with Size=-1 and Value=DBNull.Value.
            Dim contentParam As SqlParameter = New SqlParameter("@Content", SqlDbType.VarBinary, -1)
            contentParam.Value = DBNull.Value
            If Not attachment.IsExternal Then
                Dim content As Byte() = Convert.FromBase64String(attachment.Base64Data)
                contentParam.Size = content.Length
                contentParam.Value = content
            End If

            Dim attachmentId As Guid = Guid.NewGuid()
            Await context.ExecuteNonQueryAsync(sqlAttachment,
                                              "@AttachmentId", attachmentId,
                                              "@EventComponentId", eventComponentId,
                                              "@UID", uid,
                                              "@MediaType", attachment.MediaType,
                                              "@ExternalUrl", If(attachment.IsExternal, attachment.Uri, Nothing), contentParam
                                              )
            ' Prepare SQL to save custom property parameters to [].
            Dim customPropSqlInsert As String
            Dim customPropParametersInsert As List(Of Object)
            If PrepareSqlParamsWriteCustomProperty("ATTACH", attachment.RawProperty, attachmentId, uid, customPropSqlInsert, customPropParametersInsert) Then
                customPropertiesSql += "; " & customPropSqlInsert
                customPropertiesParameters.AddRange(customPropParametersInsert)
            End If
        Next

        If Not String.IsNullOrEmpty(customPropertiesSql) Then
            Await context.ExecuteNonQueryAsync(customPropertiesSql, customPropertiesParameters.ToArray())
        End If
    End Function

    Private Function PrepareSqlParamsWriteCustomProperty(propName As String, prop As IRawProperty, parentId As Guid, uid As String, ByRef sql As String, ByRef parameters As List(Of Object)) As Boolean
        sql = "INSERT INTO [cal_CustomProperty] (
                  [ParentId]
                , [UID]
                , [PropertyName]
                , [ParameterName]
                , [Value]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        parameters = New List(Of Object)()
        Dim origParamsCount As Integer = parameters.Count()
        Dim isCustomProp As Boolean = propName.StartsWith("X-", StringComparison.InvariantCultureIgnoreCase)
        Dim paramName As String = Nothing
        ' Save custom prop value.
        If isCustomProp Then
            Dim val As String = prop.RawValue
            valuesSql.Add(String.Format("(
                              @ParentId{0}
                            , @UID{0}
                            , @PropertyName{0}
                            , @ParameterName{0}
                            , @Value{0}
                            )", paramIndex))
            parameters.AddRange(New Object() {"@ParentId" & paramIndex, parentId,
                                             "@UID" & paramIndex, uid,       ' added for performance optimization purposes
                                             "@PropertyName" & paramIndex, propName,
                                             "@ParameterName" & paramIndex, paramName, ' null is inserted into the ParameterName field to mark prop value
                                             "@Value" & paramIndex, val
                                             })
            paramIndex += 1
        End If

        ' Save parameters and their values.
        For Each param As Parameter In prop.Parameters
            paramName = param.Name
            ' For standard properties we save only custom params (that start with 'X-'). All standard patrams go to their fields in DB.
            ' For custom properies we save all params.
            If Not isCustomProp AndAlso Not paramName.StartsWith("X-", StringComparison.InvariantCultureIgnoreCase) Then Continue For
            For Each value As String In param.Values
                Dim val As String = value
                valuesSql.Add(String.Format("(
                              @ParentId{0}
                            , @UID{0}
                            , @PropertyName{0}
                            , @ParameterName{0}
                            , @Value{0}
                            )", paramIndex))
                parameters.AddRange(New Object() {"@ParentId" & paramIndex, parentId,
                                                 "@UID" & paramIndex, uid,       ' added for performance optimization purposes
                                                 "@PropertyName" & paramIndex, propName,
                                                 "@ParameterName" & paramIndex, paramName,
                                                 "@Value" & paramIndex, val
                                                 })
                paramIndex += 1
            Next
        Next

        If origParamsCount < parameters.Count() Then
            sql = String.Format(sql, String.Join(", ", valuesSql.ToArray()))
            Return True
        End If

        Return False
    End Function

    Private Function PrepareSqlCustomPropertiesOfComponentAsync(component As IComponent, parentId As Guid, uid As String, ByRef sql As String, ByRef parameters As List(Of Object)) As Boolean
        sql = ""
        parameters = New List(Of Object)()
        ' We save only single custom props here, multiple props are saved in other methods.
        Dim multiProps As String() = New String() {"ATTACH", "ATTENDEE", "EXDATE"}
        ' Properties in IComponent.Properties are grouped by name.
        For Each pair As KeyValuePair(Of String, IList(Of IRawProperty)) In component.Properties
            If multiProps.Contains(pair.Key.ToUpper()) OrElse (pair.Value.Count <> 1) Then Continue For
            Dim sqlInsert As String
            Dim parametersInsert As List(Of Object)
            If PrepareSqlParamsWriteCustomProperty(pair.Key, pair.Value.First(), parentId, uid, sqlInsert, parametersInsert) Then
                sql += "; " & sqlInsert
                parameters.AddRange(parametersInsert)
            End If
        Next

        Return Not String.IsNullOrEmpty(sql)
    End Function

    Public Overrides Async Function DeleteAsync(multistatus As MultistatusException) As Task Implements IHierarchyItem.DeleteAsync
        Dim cal As ICalendar2 = Await GetCalendarAsync()
        Dim sql As String = "DELETE FROM [cal_CalendarFile] 
                       WHERE UID=@UID
                       AND [CalendarFolderId] IN (SELECT [CalendarFolderId] FROM [cal_Access] WHERE [UserId]=@UserId AND [Write]=1)"
        If Await Context.ExecuteNonQueryAsync(sql, 
                                             "@UID", uid,
                                             "@UserId", Context.UserId) < 1 Then
            Throw New DavException("Item not found or you do not have enough permissions to complete this operation.", DavStatus.FORBIDDEN)
        End If

        ' Notify attendees that event is canceled if deletion is successful.
        cal.Method = cal.CreateMethodProp(MethodType.Cancel)
        Await iMipEventSchedulingTransport.NotifyAttendeesAsync(Context, cal)
    End Function

    Public Async Function ReadAsync(output As Stream, startIndex As Long, count As Long) As Task Implements IContent.ReadAsync
        Dim cal As ICalendar2 = Await GetCalendarAsync()
        Call New vFormatter().Serialize(output, cal)
    End Function

    Private Async Function GetCalendarAsync() As Task(Of ICalendar2)
        Dim cal As ICalendar2 = CalendarFactory.CreateCalendar2()
        cal.ProductId = cal.CreateTextProp("-//IT Hit//Collab Lib//EN")
        ' Recurrent event or to-do can contain more than one VEVENT/VTODO component in one file.
        For Each rowEventComponent As DataRow In rowsEventComponents
            ' add either event or to-do to the calendar
            Dim isEvent As Boolean = rowEventComponent.Field(Of Boolean)("ComponentType")
            Dim sc As IEventBase
            If isEvent Then
                sc = cal.Events.CreateComponent()
                cal.Events.Add(TryCast(sc, IEvent))
            Else
                sc = cal.ToDos.CreateComponent()
                cal.ToDos.Add(TryCast(sc, IToDo))
            End If

            ' Read component properties from previously loaded [cal_EventComponent] rows.
            ReadEventComponent(sc, rowEventComponent, cal)
            Dim eventComponentId As Guid = rowEventComponent.Field(Of Guid)("EventComponentId")
            ' Get [cal_RecurrenceException] rows that belong to this event component only and read recurrence exceptions dates.
            Dim rowsThisScRecurrenceExceptions As IEnumerable(Of DataRow) = rowsRecurrenceExceptions.Where(Function(x) x.Field(Of Guid)("EventComponentId") = eventComponentId)
            ReadRecurrenceExceptions(sc.ExceptionDateTimes, rowsThisScRecurrenceExceptions, cal)
            ' Get [cal_Alarm] rows that belong to this event component only and read alarms.
            Dim rowsThisScAlarms As IEnumerable(Of DataRow) = rowsAlarms.Where(Function(x) x.Field(Of Guid)("EventComponentId") = eventComponentId)
            ReadAlarms(sc.Alarms, rowsThisScAlarms, cal)
            ' Get [cal_Attendee] rows that belong to this event component only and read attendees.
            Dim rowsThisScAttendees As IEnumerable(Of DataRow) = rowsAttendees.Where(Function(x) x.Field(Of Guid)("EventComponentId") = eventComponentId)
            ReadAttendees(sc.Attendees, rowsThisScAttendees, cal)
            ' Get [cal_Attachment] rows that belong to this event component only.
            ' Read attachments, load [cal_Attachment].[Content] if required.
            Dim rowsThisScAttachments As IEnumerable(Of DataRow) = rowsAttachments.Where(Function(x) x.Field(Of Guid)("EventComponentId") = eventComponentId)
            Await ReadAttachmentsAsync(Context, sc.Attachments, rowsThisScAttachments, cal)
        Next

        ' Generate VTIMEZONE components based on TZID parameters.
        cal.AutoGenerateTimeZones = True
        Return cal
    End Function

    Private Sub ReadEventComponent(sc As IEventBase, row As DataRow, cal As ICalendar2)
        Dim isAllDay As Boolean = row.Field(Of Boolean?)("AllDay").GetValueOrDefault()
        sc.Uid = cal.CreateTextProp(row.Field(Of String)("UID"))
        sc.DateTimeStampUtc = cal.CreateDateProp(row.Field(Of DateTime?)("DateTimeStampUtc"), DateTimeKind.Utc)
        sc.CreatedUtc = cal.CreateDateProp(row.Field(Of DateTime?)("CreatedUtc"), DateTimeKind.Utc)
        sc.LastModifiedUtc = cal.CreateDateProp(row.Field(Of DateTime?)("LastModifiedUtc"), DateTimeKind.Utc)
        sc.Summary = cal.CreateCalTextProp(row.Field(Of String)("Summary"))
        sc.Description = cal.CreateCalTextProp(row.Field(Of String)("Description"))
        sc.Start = cal.CreateCalDateProp(row.Field(Of DateTime?)("Start"), row.Field(Of String)("StartTimeZoneId"), isAllDay)
        sc.Duration = cal.CreateDurationProp(row.Field(Of Long?)("Duration"))
        sc.Class = cal.CreateClassProp(row.Field(Of String)("Class"))
        sc.Location = cal.CreateCalTextProp(row.Field(Of String)("Location"))
        sc.Priority = cal.CreateIntegerProp(row.Field(Of Byte?)("Priority"))
        sc.Sequence = cal.CreateIntegerProp(row.Field(Of Integer?)("Sequence"))
        sc.Status = cal.CreateStatusProp(row.Field(Of String)("Status"))
        sc.Organizer = cal.CreateCalAddressProp(EmailToUri(row.Field(Of String)("OrganizerEmail")), row.Field(Of String)("OrganizerCommonName"))
        ' RECURRENCE-ID property
        sc.RecurrenceId = cal.CreateRecurrenceIdProp(row.Field(Of DateTime?)("RecurrenceIdDate"),                                                            ' RECURRENCE-ID value
                                                    row.Field(Of String)("RecurrenceIdTimeZoneId"),                                                       ' RECURRENCE-ID TZID param
                                                    isAllDay,                                                                                          ' RECURRENCE-ID DATE or DATE-TIME
                                                    row.Field(Of Boolean?)("RecurrenceIdThisAndFuture").GetValueOrDefault())
        ' CATEGORIES property list
        Dim categories As String = TryCast(row.Field(Of String)("Categories"), String)
        If Not String.IsNullOrEmpty(categories) Then
            Dim strCatProp As String() = categories.Split({";"c}, StringSplitOptions.RemoveEmptyEntries)
            For Each categoryList As String In strCatProp
                Dim catProp As ICategories = sc.Categories.CreateProperty()
                catProp.Categories = categoryList.Split({","c}, StringSplitOptions.RemoveEmptyEntries)
                sc.Categories.Add(catProp)
            Next
        End If

        ' RRULE property
        Dim recurFrequency As String = row.Field(Of String)("RecurFrequency")
        If Not String.IsNullOrEmpty(recurFrequency) Then
            sc.RecurrenceRule = cal.CreateProperty(Of IRecurrenceRule)()
            sc.RecurrenceRule.Frequency = ExtendibleEnum.FromString(Of FrequencyType)(recurFrequency)
            sc.RecurrenceRule.Interval = CType(row.Field(Of Integer?)("RecurInterval"), UInteger?)
            sc.RecurrenceRule.Count = CType(row.Field(Of Integer?)("RecurCount"), UInteger?)
            ' WKST rule part
            Dim weekStart As String = row.Field(Of String)("RecurWeekStart")
            If Not String.IsNullOrEmpty(weekStart) Then
                sc.RecurrenceRule.WeekStart = CType([Enum].Parse(GetType(DayOfWeek), weekStart), DayOfWeek)
            End If

            ' UNTIL rule part
            Dim until As DateTime? = row.Field(Of DateTime?)("RecurUntil")
            If until IsNot Nothing Then
                ' UNTIL must be in UTC if DTSTART contains time zone or DTSTART is UTC.
                ' UNTIL must be 'floating' if DTSTART is 'floating'.
                ' UNTIL must be 'all day' if the DTSTART is 'all day'.
                ' https:'tools.ietf.org/html/rfc5545#section-3.3.10
                sc.RecurrenceRule.Until = New [Date](DateTime.SpecifyKind(until.Value,
                                                                         If(sc.Start.Value.DateVal.Kind <> DateTimeKind.Local, DateTimeKind.Utc, DateTimeKind.Local)),
                                                    sc.Start.Value.Components)
            End If

            ' BYDAY rule part
            Dim byDay As String = row.Field(Of String)("RecurByDay")
            If Not String.IsNullOrEmpty(byDay) Then
                sc.RecurrenceRule.ByDay = byDay.Split(","c).Select(Function(x) DayRule.Parse(x)).ToArray()
            End If

            ' BYMONTHDAY rule part
            Dim byMonthDay As String = row.Field(Of String)("RecurByMonthDay")
            If Not String.IsNullOrEmpty(byMonthDay) Then
                sc.RecurrenceRule.ByMonthDay = byMonthDay.Split(","c).Select(Function(x) Short.Parse(x)).ToArray()
            End If

            ' BYMONTH rule part
            Dim byMonth As String = row.Field(Of String)("RecurByMonth")
            If Not String.IsNullOrEmpty(byMonth) Then
                sc.RecurrenceRule.ByMonth = byMonth.Split(","c).Select(Function(x) UShort.Parse(x)).ToArray()
            End If

            ' BYSETPOS  rule part
            Dim bySetPos As String = row.Field(Of String)("RecurBySetPos")
            If Not String.IsNullOrEmpty(bySetPos) Then
                sc.RecurrenceRule.BySetPos = bySetPos.Split(","c).Select(Function(x) Short.Parse(x)).ToArray()
            End If
        End If

        If TypeOf sc Is IEvent Then
            ' Properties specific for events only
            Dim vEvent As IEvent = TryCast(sc, IEvent)
            vEvent.End = cal.CreateCalDateProp(row.Field(Of DateTime?)("End"), row.Field(Of String)("EndTimeZoneId"), isAllDay)
            vEvent.Transparency = cal.CreateTransparencyProp(row.Field(Of Boolean?)("EventTransparency"))
        Else
            ' Properties specific for to-dos only
            Dim vToDo As IToDo = TryCast(sc, IToDo)
            vToDo.Due = cal.CreateCalDateProp(row.Field(Of DateTime?)("End"), row.Field(Of String)("EndTimeZoneId"), isAllDay)
            vToDo.CompletedUtc = cal.CreateDateProp(row.Field(Of DateTime?)("ToDoCompletedUtc"), DateTimeKind.Utc)
            vToDo.PercentComplete = cal.CreateIntegerProp(row.Field(Of Byte?)("ToDoPercentComplete"))
        End If

        ' Get custom properties and custom parameters
        Dim eventComponentId As Guid = row.Field(Of Guid)("EventComponentId")
        Dim rowsEventCustomProperties As IEnumerable(Of DataRow) = rowsCustomProperties.Where(Function(x) x.Field(Of Guid)("ParentId") = eventComponentId)
        ReadCustomProperties(sc, rowsEventCustomProperties)
    End Sub

    Private Shared Sub ReadRecurrenceExceptions(recurrenceExceptions As IPropertyList(Of ICalDateList), rowsRecurrenceExceptions As IEnumerable(Of DataRow), cal As ICalendar2)
        For Each rowRecurrenceException As DataRow In rowsRecurrenceExceptions
            ' EXDATE property
            Dim exdate As ICalDateList = cal.CreateCalDateListProp(New DateTime() {rowRecurrenceException.Field(Of DateTime)("ExceptionDate")}, rowRecurrenceException.Field(Of String)("TimeZoneId"),                                                ' EXDATE TZID param
                                                                  rowRecurrenceException.Field(Of Boolean?)("AllDay").GetValueOrDefault()                                 ' EXDATE DATE or DATE-TIME
                                                                  )
            recurrenceExceptions.Add(exdate)
        Next
    End Sub

    Private Sub ReadAlarms(alarms As IComponentList(Of IAlarm), rowsAlarms As IEnumerable(Of DataRow), cal As ICalendar2)
        For Each rowAlarm As DataRow In rowsAlarms
            Dim alarm As IAlarm = alarms.CreateComponent()
            alarm.Action = cal.CreateActionProp(rowAlarm.Field(Of String)("Action"))
            alarm.Summary = cal.CreateCalTextProp(rowAlarm.Field(Of String)("Summary"))
            alarm.Description = cal.CreateCalTextProp(rowAlarm.Field(Of String)("Description"))
            alarm.Duration = cal.CreateDurationProp(rowAlarm.Field(Of Long?)("Duration"))
            alarm.Repeat = cal.CreateIntegerProp(rowAlarm.Field(Of Integer?)("Repeat"))
            ' Alarm TRIGGER property
            alarm.Trigger = cal.CreateProperty(Of ITrigger)()
            Dim absolute As DateTime? = rowAlarm.Field(Of DateTime?)("TriggerAbsoluteDateTimeUtc")
            If absolute IsNot Nothing Then
                alarm.Trigger.AbsoluteDateTimeUtc = DateTime.SpecifyKind(absolute.Value, DateTimeKind.Utc)
            End If

            Dim offset As Long? = rowAlarm.Field(Of Long?)("TriggerRelativeOffset")
            If offset IsNot Nothing Then
                alarm.Trigger.RelativeOffset = New TimeSpan(offset.Value)
            End If

            ' Alarm trigger RELATED param
            Dim related As Boolean? = rowAlarm.Field(Of Boolean?)("TriggerRelatedStart")
            If related IsNot Nothing Then
                alarm.Trigger.Related = If(related.Value, RelatedType.Start, RelatedType.End)
            End If

            ' Get custom properties and custom parameters
            Dim alarmId As Guid = rowAlarm.Field(Of Guid)("AlarmId")
            Dim rowsEventCustomProperties As IEnumerable(Of DataRow) = rowsCustomProperties.Where(Function(x) x.Field(Of Guid)("ParentId") = alarmId)
            ReadCustomProperties(alarm, rowsEventCustomProperties)
            alarms.Add(alarm)
        Next
    End Sub

    Private Sub ReadAttendees(attendees As IPropertyList(Of IAttendee), rowsAttendees As IEnumerable(Of DataRow), cal As ICalendar2)
        For Each rowAttendee As DataRow In rowsAttendees
            Dim attendee As IAttendee = attendees.CreateProperty()
            attendee.Uri = EmailToUri(rowAttendee.Field(Of String)("Email"))
            attendee.CommonName = rowAttendee.Field(Of String)("CommonName")
            attendee.Dir = rowAttendee.Field(Of String)("DirectoryEntryRef")
            attendee.Language = rowAttendee.Field(Of String)("Language")
            attendee.UserType = StringToEnum(Of CalendarUserType)(rowAttendee.Field(Of String)("UserType"))
            attendee.SentBy = EmailToUri(rowAttendee.Field(Of String)("SentBy"))
            attendee.DelegatedFrom = {EmailToUri(rowAttendee.Field(Of String)("DelegatedFrom"))}
            attendee.DelegatedTo = {EmailToUri(rowAttendee.Field(Of String)("DelegatedTo"))}
            ' Attendee RSVP parameter
            Dim rsvp As Boolean? = rowAttendee.Field(Of Boolean?)("Rsvp")
            If rsvp IsNot Nothing Then
                attendee.Rsvp = If(rsvp.Value, RsvpType.True, RsvpType.False)
            End If

            attendee.ParticipationRole = StringToEnum(Of ParticipationRoleType)(rowAttendee.Field(Of String)("ParticipationRole"))
            attendee.ParticipationStatus = StringToEnum(Of ParticipationStatusType)(rowAttendee.Field(Of String)("ParticipationStatus"))
            AddParamValues(rowAttendee.Field(Of Guid)("AttendeeId"), attendee.RawProperty)
            attendees.Add(attendee)
        Next
    End Sub

    Private Async Function ReadAttachmentsAsync(context As DavContext, attachments As IPropertyList(Of IMedia), rowsAttachments As IEnumerable(Of DataRow), cal As ICalendar2) As Task
        ' Find if any attachments content should be read from datatbase.
        Dim loadContent As Boolean = rowsAttachments.Any(Function(x)(x.Field(Of Integer)("ContentExists") = 1))
        If loadContent Then
            ' Reading attachments content from database.
            ' Set timeout to maximum value to be able to download iCalendar files with large file attachments.
            System.Web.HttpContext.Current.Server.ScriptTimeout = Integer.MaxValue
            Dim eventComponentId As Guid = rowsAttachments.First().Field(Of Guid)("EventComponentId")
            Dim sql As String = "SELECT [AttachmentId], [MediaType], [ExternalUrl], [Content] FROM [cal_Attachment] WHERE [EventComponentId]=@EventComponentId"
            Using reader As SqlDataReader = Await context.ExecuteReaderAsync(CommandBehavior.SequentialAccess, sql, "@EventComponentId", eventComponentId)
                While Await reader.ReadAsync()
                    Dim attachment As IMedia = attachments.CreateProperty()
                    Dim attachmentId As Guid = Await reader.GetFieldValueAsync(Of Guid)(reader.GetOrdinal("AttachmentId"))
                    ' Attachment FMTTYPE parameter
                    Dim ordMediaType As Integer = reader.GetOrdinal("MediaType")
                    If Not Await reader.IsDBNullAsync(ordMediaType) Then
                        attachment.MediaType = Await reader.GetFieldValueAsync(Of String)(ordMediaType)
                    End If

                    ' Attachment value as URL
                    Dim ordExternalUrl As Integer = reader.GetOrdinal("ExternalUrl")
                    If Not Await reader.IsDBNullAsync(ordExternalUrl) Then
                        attachment.Uri = Await reader.GetFieldValueAsync(Of String)(ordExternalUrl)
                    End If

                    ' Attachment value as inline content
                    Dim ordContent As Integer = reader.GetOrdinal("Content")
                    If Not Await reader.IsDBNullAsync(ordContent) Then
                        Using stream As Stream = reader.GetStream(ordContent)
                            Using memory As MemoryStream = New MemoryStream()
                                Await stream.CopyToAsync(memory)
                                attachment.Base64Data = Convert.ToBase64String(memory.ToArray())
                            End Using
                        End Using
                    End If

                    AddParamValues(attachmentId, attachment.RawProperty)
                    attachments.Add(attachment)
                End While
            End Using
        Else
            ' Attachments contain only URLs to external files.
            For Each rowAttachment As DataRow In rowsAttachments
                Dim attachment As IMedia = attachments.CreateProperty()
                attachment.MediaType = rowAttachment.Field(Of String)("MediaType")
                attachment.Uri = rowAttachment.Field(Of String)("ExternalUrl")
                AddParamValues(rowAttachment.Field(Of Guid)("AttachmentId"), attachment.RawProperty)
                attachments.Add(attachment)
            Next
        End If
    End Function

    Private Shared Sub ReadCustomProperties(component As IComponent, rowsCustomProperies As IEnumerable(Of DataRow))
        For Each rowCustomProperty As DataRow In rowsCustomProperies
            Dim propertyName As String = rowCustomProperty.Field(Of String)("PropertyName")
            Dim prop As IRawProperty
            If Not component.Properties.ContainsKey(propertyName) Then
                prop = component.CreateRawProperty()
                component.AddProperty(propertyName, prop)
            Else
                prop = component.Properties(propertyName).FirstOrDefault()
            End If

            Dim paramName As String = rowCustomProperty.Field(Of String)("ParameterName")
            Dim value As String = rowCustomProperty.Field(Of String)("Value")
            If paramName Is Nothing Then
                ' If ParameterName is null the Value contains property value
                prop.RawValue = value
            Else
                AddParamValue(prop, paramName, value)
            End If
        Next
    End Sub

    Private Sub AddParamValues(propertyId As Guid, prop As IRawProperty)
        Dim rowsCustomParams As IEnumerable(Of DataRow) = rowsCustomProperties.Where(Function(x) x.Field(Of Guid)("ParentId") = propertyId)
        For Each rowCustomParam As DataRow In rowsCustomParams
            Dim paramName As String = rowCustomParam.Field(Of String)("ParameterName")
            Dim paramValue As String = rowCustomParam.Field(Of String)("Value")
            AddParamValue(prop, paramName, paramValue)
        Next
    End Sub

    Private Shared Sub AddParamValue(prop As IRawProperty, paramName As String, paramValue As String)
        ' There could be parameters with identical name withing one property.
        ' This call returns all values from all properties with specified name.
        Dim paramVals As IEnumerable(Of String) = prop.Parameters(paramName)
        ' Add value.
        Dim paramNewVals As List(Of String) = paramVals.ToList()
        paramNewVals.Add(paramValue)
        ' This call removes any parameters with identical names if any and 
        ' replaces it with a single parameter with a lost of specified values.
        prop.Parameters(paramName) = paramNewVals
    End Sub

    Private Shared Function EmailToUri(email As String) As String
        If email Is Nothing Then Return Nothing
        If email.IndexOf("@"c) > 0 Then Return String.Format("mailto:{0}", email)
        Return email
    End Function

    Private Shared Function StringToEnum(Of T As {ExtendibleEnum, New})(value As String) As T
        If value Is Nothing Then Return Nothing
        Dim res As T
        If Not ExtendibleEnum.TryFromString(Of T)(value, res) Then
            ' If no matching value is found create new ExtendibleEnum or type T 
            ' with specified string value and default numeric value (-1).
            res = New T()
            res.Name = value
        End If

        Return res
    End Function
End Class
Namespace

Reference

ITHit.WebDAV.Server.CalDav Namespace