ICardFile Interface

public interface ICardFile : IAddressbookItem, 
	IFile, IHierarchyItem, IHierarchyItemBase, IContent

Public Interface ICardFile
	Inherits IAddressbookItem, IFile, IHierarchyItem, IHierarchyItemBase, 
	IContent

public interface class ICardFile : IAddressbookItem, 
	IFile, IHierarchyItem, IHierarchyItemBase, IContent

type ICardFile =  
    interface
        interface IAddressbookItem
        interface IFile
        interface IHierarchyItem
        interface IHierarchyItemBase
        interface IContent
    end

public class CardFile : DavHierarchyItem, ICardFile
{
    public static string Extension = ".vcf";

    public static async Task<IEnumerable<ICardFile>> LoadByAddressbookFolderIdAsync(DavContext context, Guid addressbookFolderId, PropsToLoad propsToLoad)
    {
        // propsToLoad == PropsToLoad.Minimum -> Typical GetChildren call by iOS, Android, eM Client, etc CardDAV 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("LoadByAddressbookFolderIdAsync is implemented only with PropsToLoad.Minimum.");

        string sql = @"SELECT * FROM [card_CardFile] 
                       WHERE [AddressbookFolderId] = @AddressbookFolderId
                       AND [AddressbookFolderId] IN (SELECT [AddressbookFolderId] FROM [card_Access] WHERE [UserId]=@UserId)";

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

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

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

        string sql = @"SELECT * FROM [card_CardFile] 
                       WHERE [FileName] IN ({0})
                       AND [AddressbookFolderId] IN (SELECT [AddressbookFolderId] FROM [card_Access] WHERE [UserId]=@UserId)";

        if(propsToLoad==PropsToLoad.All)
        {
            sql += @";SELECT * FROM [card_Email]             WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_Address]           WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_InstantMessenger]  WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_Telephone]         WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_Url]               WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_CustomProperty]    WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0})) AND [ClientAppName] = @ClientAppName";
        }

        sql = string.Format(sql, selectIn);

        return await LoadAsync(context, sql,
              "@UserId", context.UserId
            , "@ClientAppName", AppleCardInteroperability.GetClientAppName(context.Request.UserAgent));
    }

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

        Stopwatch stopWatch = Stopwatch.StartNew();

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

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

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

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

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

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

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

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


            foreach (DataRow rowCardFile in cards.Rows)
            {
                DataRow[] rowsEmails                = new DataRow[0];
                DataRow[] rowsAddresses             = new DataRow[0];
                DataRow[] rowsInstantMessengers     = new DataRow[0];
                DataRow[] rowsTelephones            = new DataRow[0];
                DataRow[] rowsUrls                  = new DataRow[0];
                DataRow[] rowsCustomProperties      = new DataRow[0];

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

                if (emails.Columns["UID"] != null)
                {
                    string filter = string.Format("UID = '{0}'", uid);

                    rowsEmails              = emails.Select(filter);
                    rowsAddresses           = addresses.Select(filter);
                    rowsInstantMessengers   = instantMessengers.Select(filter);
                    rowsTelephones          = telephones.Select(filter);
                    rowsUrls                = urls.Select(filter);
                    rowsCustomProperties = cardCustomProperties.Select(filter);
                }

                string fileName = rowCardFile.Field<string>("FileName");

                items.Add(new CardFile(context, fileName, rowCardFile, rowsEmails, rowsAddresses, rowsInstantMessengers, rowsTelephones, rowsUrls, rowsCustomProperties));
            }
        }

        return items;
    }

    public static CardFile CreateCardFile(DavContext context, Guid addressbookFolderId, string fileName)
    {
        CardFile cardFile = new CardFile(context, fileName, null, null, null, null, null, null, null);
        cardFile.addressbookFolderId = addressbookFolderId;
        return cardFile;
    }

    private readonly string fileName = null;

    private readonly DataRow rowCardFile = null;

    private readonly DataRow[] rowsEmails = null;

    private readonly DataRow[] rowsAddresses = null;

    private readonly DataRow[] rowsInstantMessengers = null;

    private readonly DataRow[] rowsTelephones = null;

    private readonly DataRow[] rowsUrls = null;

    private readonly DataRow[] rowsCustomProperties = null;

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

    private int paramIndex = 0;

    private Guid addressbookFolderId = Guid.Empty;

    public override string Name
    {
        get
        {
            return rowCardFile.Field<string>("FormattedName");
        }
    }

    public override string Path
    {
        get
        {
            Guid addressbookFolderId = rowCardFile.Field<Guid>("AddressbookFolderId");
            string fileName = rowCardFile.Field<string>("FileName");
            return string.Format("{0}{1}/{2}{3}", AddressbooksRootFolder.AddressbooksRootFolderPath, addressbookFolderId, fileName, Extension);
        }
    }

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

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

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

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

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

    private CardFile(DavContext context, string fileName,
        DataRow rowCardFile, DataRow[] rowsEmails, DataRow[] rowsAddresses, DataRow[] rowsInstantMessengers,
        DataRow[] rowsTelephones, DataRow[] rowsUrls, DataRow[] rowsCustomProperties)
        : base(context)
    {
        this.fileName               = fileName;
        this.rowCardFile            = rowCardFile;
        this.rowsEmails             = rowsEmails;
        this.rowsAddresses          = rowsAddresses;
        this.rowsInstantMessengers  = rowsInstantMessengers;
        this.rowsTelephones         = rowsTelephones;
        this.rowsUrls               = rowsUrls;
        this.rowsCustomProperties   = rowsCustomProperties;
    }

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

        // Typically the stream contains a single vCard.
        IEnumerable<IComponent> cards = new vFormatter().Deserialize(vCard);
        ICard2 card = cards.First() as ICard2;

        // Card file UID which is equal to file name (??).
        // The type is used to uniquely identify the object that the vCard represents. The "uuid" URN namespace defined in 
        // [RFC4122] is particularly well-suited to this task, but other URI schemes MAY be used.
        string uid = card.Uid?.Text ?? "UID:urn:uuid:" + Guid.NewGuid().ToString();

        // Check if this CardDAV client application requires properties conversion.
        if (AppleCardInteroperability.NeedsConversion(Context.Request.UserAgent))
        {
            AppleCardInteroperability.Normalize(card);
        }

        // The client app name is stored in DB to update and extract only custom props created by the client making a request.
        string clientAppName = AppleCardInteroperability.GetClientAppName(Context.Request.UserAgent);

        // Save data to [card_CardFile] table.
        await WriteCardFileAsync(Context, card, addressbookFolderId, isNew, uid, clientAppName);

        // Save emails.
        await WriteEmailsAsync(Context, card.Emails, uid, clientAppName);

        // Save addresses.
        await WriteAddressesAsync(Context, card.Addresses, uid, clientAppName);

        // Save telephones.
        await WriteTelephonesAsync(Context, card.Telephones, uid, clientAppName);

        // Save URLs
        await WriteUrlsAsync(Context, card.Urls, uid, clientAppName);

        // Save instant messengers. vCard 3.0+ only
        ICard3 card3 = card as ICard3;
        if (card3 != null)
        {
            await WriteInstantMessengersAsync(Context, card3.InstantMessengers, uid, clientAppName);
        }

        return true;
    }

    private async Task WriteCardFileAsync(DavContext context, ICard2 card, Guid addressbookFolderId, bool isNew, string uid, string clientAppName)
    {
        string sql;
        if (isNew)
        {
            sql =
                @"IF EXISTS (SELECT 1 FROM [card_Access] WHERE [AddressbookFolderId]=@AddressbookFolderId AND [UserId]=@UserId AND [Write]=1)
                  INSERT INTO [card_CardFile] (
                      [UID]
                    , [AddressbookFolderId]
                 , [FileName]
                 , [Version]
                 , [Product]
                 , [FormattedName]
                 , [FamilyName]
                 , [GivenName]
                 , [AdditionalNames]
                 , [HonorificPrefix]
                 , [HonorificSuffix]
                 , [Kind]
                 , [Nickname]
                 , [Photo]
                 , [PhotoMediaType]
                 , [Logo]
                 , [LogoMediaType]
                 , [Sound]
                 , [SoundMediaType]
                 , [Birthday]
                 , [Anniversary]
                 , [Gender]
                 , [RevisionUtc]
                 , [SortString]
                 , [Language]
                 , [TimeZone]
                 , [Geo]
                 , [Title]
                 , [Role]
                 , [OrgName]
                 , [OrgUnit]
                 , [Categories]
                 , [Note]
                 , [Classification]
                ) VALUES (
                      @UID
                    , @AddressbookFolderId
                    , @FileName
                    , @Version
                    , @Product
                 , @FormattedName
                 , @FamilyName
                 , @GivenName
                 , @AdditionalNames
                 , @HonorificPrefix
                 , @HonorificSuffix
                 , @Kind
                 , @Nickname
                 , @Photo
                 , @PhotoMediaType
                 , @Logo
                 , @LogoMediaType
                 , @Sound
                 , @SoundMediaType
                 , @Birthday
                 , @Anniversary
                 , @Gender
                 , @RevisionUtc
                 , @SortString
                 , @Language
                 , @TimeZone
                 , @Geo
                 , @Title
                 , @Role
                 , @OrgName
                 , @OrgUnit
                 , @Categories
                 , @Note
                 , @Classification
                )";
        }
        else
        {
            // We can only update record in [card_CardFile] table.
            // There is no way to update [card_Email], [card_Address], [card_InstantMessenger], [card_Telephone] and [card_Url]
            // tables for existing card, we must delete all records for this UID and recreate.

            // We can keep custom props in [card_CustomProperty] table if they were created by a different CardDAV client.

            // [ModifiedUtc] field update triggers [ETag] field update which is used for synchronyzation.
            sql =
                @"IF EXISTS (SELECT 1 FROM [card_CardFile] 
                       WHERE FileName=@FileName
                       AND [AddressbookFolderId] IN (SELECT [AddressbookFolderId] FROM [card_Access] WHERE [UserId]=@UserId AND [Write] = 1))
                  BEGIN
                      UPDATE [card_CardFile] SET 
                          [ModifiedUtc]     = @ModifiedUtc
                     , [Version]         = @Version
                     , [Product]         = @Product
                     , [FormattedName]   = @FormattedName
                     , [FamilyName]      = @FamilyName
                     , [GivenName]       = @GivenName
                     , [AdditionalNames] = @AdditionalNames
                     , [HonorificPrefix] = @HonorificPrefix
                     , [HonorificSuffix] = @HonorificSuffix
                     , [Kind]            = @Kind
                     , [Nickname]        = @Nickname
                     , [Photo]           = @Photo
                     , [PhotoMediaType]  = @PhotoMediaType
                     , [Logo]            = @Logo
                     , [LogoMediaType]   = @LogoMediaType
                     , [Sound]           = @Sound
                     , [SoundMediaType]  = @SoundMediaType
                     , [Birthday]        = @Birthday
                     , [Anniversary]     = @Anniversary
                     , [Gender]          = @Gender
                     , [RevisionUtc]     = @RevisionUtc
                     , [SortString]      = @SortString
                     , [Language]        = @Language
                     , [TimeZone]        = @TimeZone
                     , [Geo]             = @Geo
                     , [Title]           = @Title
                     , [Role]            = @Role
                     , [OrgName]         = @OrgName
                     , [OrgUnit]         = @OrgUnit
                     , [Categories]      = @Categories
                     , [Note]            = @Note
                     , [Classification]  = @Classification
                      WHERE 
                        [UID] = @UID

                      ; DELETE FROM [card_Email]              WHERE [UID] = @UID
                      ; DELETE FROM [card_Address]            WHERE [UID] = @UID
                      ; DELETE FROM [card_InstantMessenger]   WHERE [UID] = @UID
                      ; DELETE FROM [card_Telephone]          WHERE [UID] = @UID
                      ; DELETE FROM [card_Url]                WHERE [UID] = @UID
                      ; DELETE FROM [card_CustomProperty]     WHERE [UID] = @UID AND (([ClientAppName] = @ClientAppName) OR ([ParentId] != [UID]) OR ([ClientAppName] IS NULL))
                  END";

        }
        // [ClientAppName] = @ClientAppName -> delete all custom props created by this client.
        // [ParentId] != [UID]              -> delete all custom params from multiple props: EMAIL, ADR, TEL, IMPP. Keep custom params for any single props in [card_Card].
        // [ClientAppName] IS NULL          -> delete all custom props created by some unknown CardDAV client.


        if (await context.ExecuteNonQueryAsync(sql,
              "@UID"            , uid                                                                   // UID
            , "UserId"          , context.UserId
            , "@AddressbookFolderId", addressbookFolderId                                               // used only when inserting
            , "@FileName"       , fileName                                                              // In case of CardDAV a file name is sypically a GUID, but it is different from UID.
            , "@ModifiedUtc"    , DateTime.UtcNow
            , "@Version"        , card.Version.Text                                                     // VERSION
            , "@Product"        , card.ProductId?.Text                                                  // PRODID
            , "@FormattedName"  , card.FormattedNames.PreferedOrFirstProperty.Text                      // FN                           Here we assume only 1 prop for the sake of simplicity.
            , "@FamilyName"     , card.Name.FamilyName                                                  // N
            , "@GivenName"      , card.Name.GivenName                                                   // N
            , "@AdditionalNames", card.Name.AdditionalNamesList                                         // N
            , "@HonorificPrefix", card.Name.HonorificPrefix                                             // N
            , "@HonorificSuffix", card.Name.HonorificSuffix                                             // N
            , "@Kind"           , (card as ICard4)?.Kind?.Text                                          // KIND         (vCard 4.0)
            , "@Nickname"       , (card as ICard3)?.NickNames.PreferedOrFirstProperty?.Values.First()   // NICKNAME     (vCard 3.0+)    Here we assume only 1 prop with 1 value for the sake of simplicity.
            , CreateVarBinaryParam("@Photo", card.Photos.PreferedOrFirstProperty?.Base64Data)           // PHOTO                        Here we assume only 1 prop for the sake of simplicity.
            , "@PhotoMediaType" , card.Photos.PreferedOrFirstProperty?.MediaType                        // PHOTO TYPE param
            , CreateVarBinaryParam("@Logo",  card.Logos.PreferedOrFirstProperty?.Base64Data)            // LOGO                         Here we assume only 1 prop for the sake of simplicity.
            , "@LogoMediaType"  , card.Logos.PreferedOrFirstProperty?.MediaType                         // LOGO  TYPE param
            , CreateVarBinaryParam("@Sound", card.Sounds.PreferedOrFirstProperty?.Base64Data)           // SOUND                        Here we assume only 1 prop for the sake of simplicity.
            , "@SoundMediaType" , card.Sounds.PreferedOrFirstProperty?.MediaType                        // SOUND TYPE param
            , new SqlParameter("@Birthday"   , card.BirthDate?.Value?.DateVal ?? DBNull.Value as object)                { SqlDbType = SqlDbType.DateTime2 }   // BDAY
            , new SqlParameter("@Anniversary", (card as ICard4)?.Anniversary?.Value?.DateVal ?? DBNull.Value as object) { SqlDbType = SqlDbType.DateTime2 }   // ANNIVERSARY  (vCard 4.0)
            , "@Gender"         , (card as ICard4)?.Gender?.Sex                                         // GENDER       (vCard 4.0)
            , "@RevisionUtc"    , card.Revision?.Value.DateVal                                          // REV
            , "@SortString"     , card.SortString?.Text                                                 // SORT-STRING
            , "@Language"       , (card as ICard4)?.ContactLanguages.PreferedOrFirstProperty?.Text      // LANG         (vCard 4.0)     Here we assume only 1 prop for the sake of simplicity.
            , "@TimeZone"       , card.TimeZones.PreferedOrFirstProperty?.Text                          // TZ
            , "@Geo"            , null                                                                  // GEO
            , "@Title"          , card.Titles.PreferedOrFirstProperty?.Text                             // TITLE
            , "@Role"           , card.Roles.PreferedOrFirstProperty?.Text                              // ROLE
            , "@OrgName"        , card.Organizations.PreferedOrFirstProperty?.Name                      // ORG                          Here we assume only 1 prop for the sake of simplicity.
            , "@OrgUnit"        , card.Organizations.PreferedOrFirstProperty?.Units?.First()            // ORG                          Here we assume only 1 prop with 1 unit value for the sake of simplicity.
            , "@Categories"     , ListToString<string>((card as ICard3)?.Categories.Select(x => ListToString<string>(x.Values, ",")), ";") // CATEGORIES  (vCard 3.0+)
            , "@Note"           , card.Notes.PreferedOrFirstProperty?.Text                              // NOTE                         Here we assume only 1 prop for the sake of simplicity.
            , "@Classification" , (card as ICard3)?.Classes.PreferedOrFirstProperty?.Text               // CLASS                        Here we assume only 1 prop for the sake of simplicity.
            , "@ClientAppName"  , clientAppName                                                         // Used to keep custom props created by this CardDAV client when updated by other CardDAV clients.
            ) < 1)
        {
            throw new DavException("Item not found or you do not have enough permissions to complete this operation.", DavStatus.FORBIDDEN);
        }

        // Save custom properties and parameters of this component to [card_CustomProperty] table.
        string customPropsSqlInsert;
        List<object> customPropsParamsInsert;
        if (PrepareSqlCustomPropertiesOfComponentAsync(card, uid, uid, clientAppName, 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 SqlParameter CreateVarBinaryParam(string parameterName, string base64)
    {
        SqlParameter param = new SqlParameter(parameterName, SqlDbType.VarBinary);
        if (string.IsNullOrEmpty(base64))
        {
            // To insert NULL to VARBINARY column, SqlParameter must be passed with Size=-1 and Value=DBNull.Value.
            param.Size      = -1;
            param.Value     = DBNull.Value;
        }
        else
        {
            byte[] content  = Convert.FromBase64String(base64);
            param.Size      = content.Length;
            param.Value     = content;
        }
        return param;
    }

    private async Task WriteEmailsAsync(DavContext context, ITextPropertyList<IEmail2> emails, string uid, string clientAppName)
    {
        string sql =
            @"INSERT INTO [card_Email] (
                  [EmailId]
                , [UID]
                , [Type]
                , [Email]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}";

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

        int i = 0;
        foreach (IEmail2 email in emails)
        {
            valuesSql.Add(string.Format(@"(
                  @EmailId{0}
                , @UID
                , @Type{0}
                , @Email{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i));

            Guid emailId = Guid.NewGuid();

            parameters.AddRange(new object[] {
                  "@EmailId"            +i, emailId
              //, "@UID"
                , "@Type"               +i, ListToString<EmailType>(email.Types)    // EMAIL TYPE param
                , "@Email"              +i, email.Text                              // EMAIL VALUE
                , "@PreferenceLevel"    +i, GetPrefParameter(email)                 // EMAIL PREF param
                , "@SortIndex"          +i, email.RawProperty.SortIndex             // Property position in vCard.
            });

            // Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            string customPropSqlInsert;
            List<object> customPropParametersInsert;
            if (PrepareSqlParamsWriteCustomProperty("EMAIL", email.RawProperty, emailId.ToString(), uid, clientAppName, 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 WriteAddressesAsync(DavContext context, ICardPropertyList<IAddress2> addresses, string uid, string clientAppName)
    {
        string sql =
            @"INSERT INTO [card_Address] (
                  [AddressId]
                , [UID]
                , [Type]
                , [PoBox]
                , [AppartmentNumber]
                , [Street]
                , [Locality]
                , [Region]
                , [PostalCode]
                , [Country]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}";

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

        int i = 0;
        foreach (IAddress2 address in addresses)
        {
            valuesSql.Add(string.Format(@"(
                  @AddressId{0}
                , @UID
                , @Type{0}
                , @PoBox{0}
                , @AppartmentNumber{0}
                , @Street{0}
                , @Locality{0}
                , @Region{0}
                , @PostalCode{0}
                , @Country{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i));

            Guid addressId = Guid.NewGuid();

            parameters.AddRange(new object[] {
                  "@AddressId"            +i, addressId
              //, "@UID"
                , "@Type"               +i, ListToString<AddressType>(address.Types)    // ADR TYPE param
gma warning disable 0618
                , "@PoBox"              +i, address.PoBox.FirstOrDefault()
                , "@AppartmentNumber"   +i, address.AppartmentNumber.FirstOrDefault()
gma warning restore 0618
                , "@Street"             +i, address.Street.FirstOrDefault()
                , "@Locality"           +i, address.Locality.FirstOrDefault()
                , "@Region"             +i, address.Region.FirstOrDefault()
                , "@PostalCode"         +i, address.PostalCode.FirstOrDefault()
                , "@Country"            +i, address.Country.FirstOrDefault()
                , "@PreferenceLevel"    +i, GetPrefParameter(address)                   // ADR PREF param
                , "@SortIndex"          +i, address.RawProperty.SortIndex               // Property position in vCard.
            });

            // Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            string customPropSqlInsert;
            List<object> customPropParametersInsert;
            if (PrepareSqlParamsWriteCustomProperty("ADR", address.RawProperty, addressId.ToString(), uid, clientAppName, 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 WriteInstantMessengersAsync(DavContext context, ITextPropertyList<IInstantMessenger3> instantMessengers, string uid, string clientAppName)
    {
        string sql =
            @"INSERT INTO [card_InstantMessenger] (
                  [InstantMessengerId]
                , [UID]
                , [Type]
                , [InstantMessenger]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}";

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

        int i = 0;
        foreach (IInstantMessenger3 instantMessenger in instantMessengers)
        {
            valuesSql.Add(string.Format(@"(
                  @InstantMessengerId{0}
                , @UID
                , @Type{0}
                , @InstantMessenger{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i));

            Guid instantMessengerId = Guid.NewGuid();

            parameters.AddRange(new object[] {
                  "@InstantMessengerId" +i, instantMessengerId
              //, "@UID"
                , "@Type"               +i, ListToString<MessengerType>(instantMessenger.Types) // IMPP TYPE param
                , "@InstantMessenger"   +i, instantMessenger.Text                               // IMPP VALUE
                , "@PreferenceLevel"    +i, GetPrefParameter(instantMessenger)                  // IMPP PREF param
                , "@SortIndex"          +i, instantMessenger.RawProperty.SortIndex              // Property position in vCard.
            });

            // Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            string customPropSqlInsert;
            List<object> customPropParametersInsert;
            if (PrepareSqlParamsWriteCustomProperty("IMPP", instantMessenger.RawProperty, instantMessengerId.ToString(), uid, clientAppName, 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 WriteTelephonesAsync(DavContext context, ITextPropertyList<ITelephone2> telephones, string uid, string clientAppName)
    {
        string sql =
            @"INSERT INTO [card_Telephone] (
                  [TelephoneId]
                , [UID]
                , [Type]
                , [Telephone]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}";

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

        int i = 0;
        foreach (ITelephone2 telephone in telephones)
        {
            valuesSql.Add(string.Format(@"(
                  @TelephoneId{0}
                , @UID
                , @Type{0}
                , @Telephone{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i));

            Guid telephoneId = Guid.NewGuid();

            parameters.AddRange(new object[] {
                  "@TelephoneId"        +i, telephoneId
              //, "@UID"
                , "@Type"               +i, ListToString<TelephoneType>(telephone.Types)// TEL TYPE param
                , "@Telephone"          +i, telephone.Text                              // TEL VALUE
                , "@PreferenceLevel"    +i, GetPrefParameter(telephone)                 // TEL PREF param
                , "@SortIndex"          +i, telephone.RawProperty.SortIndex             // Property position in vCard.
            });

            // Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            string customPropSqlInsert;
            List<object> customPropParametersInsert;
            if (PrepareSqlParamsWriteCustomProperty("TEL", telephone.RawProperty, telephoneId.ToString(), uid, clientAppName, 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 WriteUrlsAsync(DavContext context, ICardPropertyList<ICardUriProperty2> urls, string uid, string clientAppName)
    {
        string sql =
            @"INSERT INTO [card_Url] (
                  [UrlId]
                , [UID]
                , [Type]
                , [Url]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}";

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

        int i = 0;
        foreach (ICardUriProperty2 url in urls)
        {
            valuesSql.Add(string.Format(@"(
                  @UrlId{0}
                , @UID
                , @Type{0}
                , @Url{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i));

            Guid urlId = Guid.NewGuid();

            parameters.AddRange(new object[] {
                  "@UrlId"              +i, urlId
              //, "@UID"
                , "@Type"               +i, ListToString<ExtendibleEnum>(url.Types) // TEL TYPE param 
                , "@Url"                +i, url.Text                                // URL VALUE
                , "@PreferenceLevel"    +i, GetPrefParameter(url)                   // URL PREF param
                , "@SortIndex"          +i, url.RawProperty.SortIndex               // Property position in vCard.
            });

            // Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            string customPropSqlInsert;
            List<object> customPropParametersInsert;
            if (PrepareSqlParamsWriteCustomProperty("URL", url.RawProperty, urlId.ToString(), uid, clientAppName, 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 static byte? GetPrefParameter(ICardMultiProperty prop)
    {
        ICardMultiProperty4 prop4 = prop as ICardMultiProperty4;
        if(prop4 == null)
        {
            return prop.IsPrefered ? (byte?)1 : null;
        }

        return (byte?)prop4.PreferenceLevel;
    }

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

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

        int origParamsCount = parameters.Count();

        // Custom properties are one of the following:
        //  - props that start with "X-". This is a standard-based approach to creating custom props.
        //  - props that has "." in its name. Typically "item1.X-PROP". Such props are created by iOS and OS X.
        bool isCustomProp =
            propName.StartsWith("X-", StringComparison.InvariantCultureIgnoreCase)
            || propName.Contains(".");


        string paramName = null;

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

            parameters.AddRange(new object[] {
                              "@ParentId"       + paramIndex, parentId
                            , "@UID"            + paramIndex, uid              // Added for performance optimization purposes.
                            , "@ClientAppName"  + paramIndex, clientAppName    // Client app name that created this custom property.
                            , "@PropertyName"   + paramIndex, propName
                            , "@ParameterName"  + paramIndex, paramName        // null is inserted to mark prop value.
                            , "@Value"          + paramIndex, val
                            , "@SortIndexParam" + paramIndex, prop.SortIndex   // Property position in vCard.
                            });
            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 params 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}
                            , @ClientAppName{0}
                            , @PropertyName{0}
                            , @ParameterName{0}
                            , @Value{0}
                            , @SortIndexParam{0}
                            )", paramIndex));

                parameters.AddRange(new object[] {
                              "@ParentId"       + paramIndex, parentId
                            , "@UID"            + paramIndex, uid          // added for performance optimization purposes
                            , "@ClientAppName"  + paramIndex, clientAppName// Client app name that created this custom parameter.
                            , "@PropertyName"   + paramIndex, propName
                            , "@ParameterName"  + paramIndex, paramName
                            , "@Value"          + paramIndex, val
                            , "@SortIndexParam" + paramIndex, null         // Property position in vCard. Null is inserted for parameter values.
                            });
                paramIndex++;
            }
        }

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

    private bool PrepareSqlCustomPropertiesOfComponentAsync(IComponent component, string parentId, string uid, string clientAppName, 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[] { "EMAIL", "ADR", "IMPP", "TEL", "URL" };

        // 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, clientAppName, out sqlInsert, out parametersInsert))
            {
                sql += "; " + sqlInsert;
                parameters.AddRange(parametersInsert);
            }
        }

        return !string.IsNullOrEmpty(sql);
    }

    public override async Task DeleteAsync(MultistatusException multistatus)
    {
        string sql = @"DELETE FROM [card_CardFile] 
                       WHERE FileName=@FileName
                       AND [AddressbookFolderId] IN (SELECT [AddressbookFolderId] FROM [card_Access] WHERE [UserId]=@UserId AND [Write] = 1)";

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

    public async Task ReadAsync(Stream output, long startIndex, long count)
    {
        string vCardVersion = rowCardFile.Field<string>("Version");
        ICard2 card = CardFactory.CreateCard(vCardVersion);

        ReadCard(card);

        ReadEmails(card.Emails, rowsEmails);
        ReadAddresses(card.Addresses, rowsAddresses);
        ReadTelephones(card.Telephones, rowsTelephones);
        ReadUrls(card.Urls, rowsUrls);

        // IMPP is vCard 3.0 & 4.0 prop
        ICard3 card3 = card as ICard3;
        if (card3 != null)
        {
            ReadMessengers(card3.InstantMessengers, rowsInstantMessengers);
        }

        // Check if this CardDAV client application requires properties conversion.
        if (AppleCardInteroperability.NeedsConversion(Context.Request.UserAgent))
        {
            // In case of iOS & OS X the props below must be converted to the following format:
            // item2.TEL:(222)222 - 2222
            // item2.X-ABLabel:Emergency
            AppleCardInteroperability.Denormalize(card);
        }

        new vFormatter().Serialize(output, card);
    }

    private void ReadCard(ICard2 card)
    {
        string uid = rowCardFile.Field<string>("UID");

        //UID
        card.Uid = card.CreateTextProp(uid);


        // PRODID
        card.ProductId = card.CreateTextProp("-//IT Hit//Collab Lib//EN");

        // FN
        card.FormattedNames.Add(rowCardFile.Field<string>("FormattedName"));

        // N
        card.Name = card.CreateNameProp(
            rowCardFile.Field<string>("FamilyName"),
            rowCardFile.Field<string>("GivenName"),
            rowCardFile.Field<string>("AdditionalNames"),
            rowCardFile.Field<string>("HonorificPrefix"),
            rowCardFile.Field<string>("HonorificSuffix"));

        // PHOTO
        if (!rowCardFile.IsNull("Photo"))
        {
            card.Photos.Add(Convert.ToBase64String(rowCardFile.Field<byte[]>("Photo")), rowCardFile.Field<string>("PhotoMediaType"), false);
        }

        // LOGO
        if (!rowCardFile.IsNull("Logo"))
        {
            card.Photos.Add(Convert.ToBase64String(rowCardFile.Field<byte[]>("Logo")), rowCardFile.Field<string>("LogoMediaType"), false);
        }

        // SOUND
        if (!rowCardFile.IsNull("Sound"))
        {
            card.Photos.Add(Convert.ToBase64String(rowCardFile.Field<byte[]>("Sound")), rowCardFile.Field<string>("SoundMediaType"), false);
        }

        // BDAY
        DateTime? birthday = rowCardFile.Field<DateTime?>("Birthday");
        if (birthday != null)
        {
            card.BirthDate = card.CreateDateProp(birthday.Value, DateComponents.Date);
        }

        // REV
        DateTime? revision = rowCardFile.Field<DateTime?>("RevisionUtc");
        if (revision != null)
        {
            card.Revision = card.CreateDateProp(revision.Value);
        }

        // SORT-STRING
        string sortString = rowCardFile.Field<string>("SortString");
        if (!string.IsNullOrEmpty(sortString))
        {
            ITextProperty2 propSortString = card.CreateProperty<ITextProperty2>();
            propSortString.Text = sortString;
            card.SortString = propSortString;
        }

        // TZ
        string timeZone = rowCardFile.Field<string>("TimeZone");
        if (!string.IsNullOrEmpty(timeZone))
        {
            card.TimeZones.Add(timeZone);
        }

        // GEO

        // TITLE
        string title = rowCardFile.Field<string>("Title");
        if (!string.IsNullOrEmpty(title))
        {
            card.Titles.Add(title);
        }

        // ROLE
        string role = rowCardFile.Field<string>("Role");
        if (!string.IsNullOrEmpty(role))
        {
            card.Roles.Add(role);
        }

        // ORG
        string orgName = rowCardFile.Field<string>("OrgName");
        string orgUnit = rowCardFile.Field<string>("OrgUnit");
        if (!string.IsNullOrEmpty(orgName) || !string.IsNullOrEmpty(orgUnit))
        {
            IOrganization2 propOrg = card.Organizations.CreateProperty();
            propOrg.Name = orgName;
            propOrg.Units = new[] { orgUnit };
            card.Organizations.Add(propOrg);
        }

        // NOTE
        string note = rowCardFile.Field<string>("Note");
        if (!string.IsNullOrEmpty(note))
        {
            card.Notes.Add(note);
        }

        // vCard v3.0 & v4.0 props
        if (card is ICard3)
        {
            ICard3 card3 = card as ICard3;

            // NICKNAME
            string nickname = rowCardFile.Field<string>("Nickname");
            if (!string.IsNullOrEmpty(nickname))
            {
                INickname3 propNickname = card3.NickNames.CreateProperty();
                propNickname.Values = new[] { nickname };
                card3.NickNames.Add(propNickname);
            }

            // CATEGORIES
            string categories = rowCardFile.Field<string>("Categories");
            if (!string.IsNullOrEmpty(categories))
            {
                string[] aCategories = categories.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                foreach (string categoryList in aCategories)
                {
                    ICategories3 catProp = card3.Categories.CreateProperty();
                    catProp.Values = categoryList.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    card3.Categories.Add(catProp);
                }
            }

            // CLASS
            string classification = rowCardFile.Field<string>("Classification");
            if (!string.IsNullOrEmpty(classification))
            {
                card3.Classes.Add(classification);
            }
        }

        // vCard v4.0 props
        if (card is ICard4)
        {
            ICard4 card4 = card as ICard4;

            // KIND
            string kind = rowCardFile.Field<string>("Kind");
            if (kind != null)
            {
                IKind4 propKind = card4.CreateProperty<IKind4>();
                propKind.Text = kind;
                card4.Kind = propKind;
            }

            // ANNIVERSARY
            DateTime? anniversary = rowCardFile.Field<DateTime?>("Anniversary");
            if (anniversary != null)
            {
                IAnniversary4 propAnniversary = card4.CreateProperty<IAnniversary4>();
                propAnniversary.Value = new Date(anniversary.Value, DateComponents.Month | DateComponents.Date);
                card4.Anniversary = propAnniversary;
            }


            // GENDER
            string gender = rowCardFile.Field<string>("Gender");
            if (!string.IsNullOrEmpty(gender))
            {
                IGender4 propGender = card4.CreateProperty<IGender4>();
                propGender.Text = gender;
                card4.Gender = propGender;
            }

            // LANG
            string language = rowCardFile.Field<string>("Language");
            if (!string.IsNullOrEmpty(language))
            {
                card4.ContactLanguages.Add(language);
            }
        }


        // Get custom properties and custom parameters
        IEnumerable<DataRow> rowsCardCustomProperties = rowsCustomProperties.Where(x => x.Field<string>("ParentId") == uid);
        ReadCustomProperties(card, rowsCardCustomProperties);
    }


    private void ReadEmails(ITextPropertyList<IEmail2> emails, IEnumerable<DataRow> rowsEmails)
    {
        foreach (DataRow rowEmail in rowsEmails)
        {
            IEmail2 email = emails.CreateProperty();
            email.Text  = rowEmail.Field<string>("Email");                              // EMAIL value
            email.Types = ParseType<EmailType>(rowEmail.Field<string>("Type"));         // TYPE param
            SetPrefParameter(email, rowEmail.Field<byte?>("PreferenceLevel"));          // PREF param
            email.RawProperty.SortIndex = rowEmail.Field<int>("SortIndex");             // Property position in vCard.
            AddParamValues(rowEmail.Field<Guid>("EmailId"), email.RawProperty);         // Add custom parameters from [card_CustomProperty] table.
            emails.Add(email);
        }
    }

    private void ReadAddresses(ICardPropertyList<IAddress2> addresses, IEnumerable<DataRow> rowsAddresses)
    {
        foreach (DataRow rowAddress in rowsAddresses)
        {
            IAddress2 address = addresses.CreateProperty();
            address.SetAddress(
                new[] { rowAddress.Field<string>("PoBox") },
                new[] { rowAddress.Field<string>("AppartmentNumber")},
                new[] { rowAddress.Field<string>("Street")},
                new[] { rowAddress.Field<string>("Locality")},
                new[] { rowAddress.Field<string>("Region")},
                new[] { rowAddress.Field<string>("PostalCode")},
                new[] { rowAddress.Field<string>("Country")},
                ParseType<AddressType>(rowAddress.Field<string>("Type")));              // ADR value and TYPE param                
            SetPrefParameter(address, rowAddress.Field<byte?>("PreferenceLevel"));      // PREF param
            address.RawProperty.SortIndex = rowAddress.Field<int>("SortIndex");         // Property position in vCard.
            AddParamValues(rowAddress.Field<Guid>("AddressId"), address.RawProperty);   // Add custom parameters from [card_CustomProperty] table.
            addresses.Add(address);
        }
    }

    private void ReadMessengers(ITextPropertyList<IInstantMessenger3> messengers, IEnumerable<DataRow> rowsMessengers)
    {
        foreach (DataRow rowMessenger in rowsMessengers)
        {
            IInstantMessenger3 messenger = messengers.CreateProperty();
            messenger.Text  = rowMessenger.Field<string>("InstantMessenger");                       // IMPP value
            messenger.Types = ParseType<MessengerType>(rowMessenger.Field<string>("Type"));         // TYPE param
            SetPrefParameter(messenger, rowMessenger.Field<byte?>("PreferenceLevel"));              // PREF param
            messenger.RawProperty.SortIndex = rowMessenger.Field<int>("SortIndex");                 // Property position in vCard.
            AddParamValues(rowMessenger.Field<Guid>("InstantMessengerId"), messenger.RawProperty);  // Add custom parameters from [card_CustomProperty] table.
            messengers.Add(messenger);
        }
    }

    private void ReadTelephones(ITextPropertyList<ITelephone2> telephones, IEnumerable<DataRow> rowsTelephones)
    {
        foreach (DataRow rowTelephone in rowsTelephones)
        {
            ITelephone2 telephone = telephones.CreateProperty();
            telephone.Text  = rowTelephone.Field<string>("Telephone");                      // TEL value
            telephone.Types = ParseType<TelephoneType>(rowTelephone.Field<string>("Type")); // TYPE param
            SetPrefParameter(telephone, rowTelephone.Field<byte?>("PreferenceLevel"));      // PREF param
            telephone.RawProperty.SortIndex = rowTelephone.Field<int>("SortIndex");         // Property position in vCard.
            AddParamValues(rowTelephone.Field<Guid>("TelephoneId"), telephone.RawProperty); // Add custom parameters from [card_CustomProperty] table.
            telephones.Add(telephone);
        }
    }

    private void ReadUrls(ICardPropertyList<ICardUriProperty2> urls, IEnumerable<DataRow> rowsUrls)
    {
        foreach (DataRow rowUrl in rowsUrls)
        {
            ICardUriProperty2 url = urls.CreateProperty();
            url.Text  = rowUrl.Field<string>("Url");                                    // URL value
            url.Types = ParseType<ExtendibleEnum>(rowUrl.Field<string>("Type"));        // TYPE param
            SetPrefParameter(url, rowUrl.Field<byte?>("PreferenceLevel"));              // PREF param
            url.RawProperty.SortIndex = rowUrl.Field<int>("SortIndex");                 // Property position in vCard.
            AddParamValues(rowUrl.Field<Guid>("UrlId"), url.RawProperty);               // Add custom parameters from [card_CustomProperty] table.
            urls.Add(url);
        }
    }

    private static void SetPrefParameter(ICardMultiProperty prop, byte? preferenceLevel)
    {
        if (preferenceLevel != null)
        {
            ICardMultiProperty4 prop4 = prop as ICardMultiProperty4;
            if (prop4 == null)
            {
                // vCard 2.1 & 3.0
                prop.IsPrefered = true;
            }
            else
            {
                // vCard 4.0
                prop4.PreferenceLevel = preferenceLevel.Value;
            }
        }
    }

    private static T[] ParseType<T>(string typesList) where T : ExtendibleEnum, new()
    {
        if (!string.IsNullOrEmpty(typesList))
        {
            string[] aStrTypes = typesList.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            return aStrTypes.Select(x => StringToEnum<T>(x)).ToArray();
        }

        return new T[] { };
    }

    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;
                prop.SortIndex = rowCustomProperty.Field<int>("SortIndex"); // Property position in vCard.
            }
            else
            {
                prop.Parameters.Add(new Parameter(paramName, value));
            }
        }
    }

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

    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;
    }
}

Public Class CardFile
    Inherits DavHierarchyItem
    Implements ICardFile

    Public Shared Extension As String = ".vcf"

    Public Shared Async Function LoadByAddressbookFolderIdAsync(context As DavContext, addressbookFolderId As Guid, propsToLoad As PropsToLoad) As Task(Of IEnumerable(Of ICardFile))
        ' propsToLoad == PropsToLoad.Minimum -> Typical GetChildren call by iOS, Android, eM Client, etc CardDAV 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("LoadByAddressbookFolderIdAsync is implemented only with PropsToLoad.Minimum.")
        Dim sql As String = "SELECT * FROM [card_CardFile] 
                       WHERE [AddressbookFolderId] = @AddressbookFolderId
                       AND [AddressbookFolderId] IN (SELECT [AddressbookFolderId] FROM [card_Access] WHERE [UserId]=@UserId)"
        'sql = string.Format(sql, GetScPropsToLoad(propsToLoad));
        Return Await LoadAsync(context, sql,
                              "@UserId", context.UserId,
                              "@AddressbookFolderId", addressbookFolderId)
    End Function

    Public Shared Async Function LoadByFileNamesAsync(context As DavContext, fileNames As IEnumerable(Of String), propsToLoad As PropsToLoad) As Task(Of IEnumerable(Of ICardFile))
        ' Get IN clause part with list of file UIDs for SELECT.
        Dim selectIn As String = String.Join(", ", fileNames.Select(Function(a) String.Format("'{0}'", a)).ToArray())
        Dim sql As String = "SELECT * FROM [card_CardFile] 
                       WHERE [FileName] IN ({0})
                       AND [AddressbookFolderId] IN (SELECT [AddressbookFolderId] FROM [card_Access] WHERE [UserId]=@UserId)"
        If propsToLoad = PropsToLoad.All Then
            sql += ";SELECT * FROM [card_Email]             WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_Address]           WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_InstantMessenger]  WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_Telephone]         WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_Url]               WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0}))
                     ;SELECT * FROM [card_CustomProperty]    WHERE [UID] IN (SELECT UID FROM [card_CardFile] WHERE [FileName] IN ({0})) AND [ClientAppName] = @ClientAppName"
        End If

        sql = String.Format(sql, selectIn)
        Return Await LoadAsync(context, sql,
                              "@UserId", context.UserId,
                              "@ClientAppName", AppleCardInteroperability.GetClientAppName(context.Request.UserAgent))
    End Function

    Private Shared Async Function LoadAsync(context As DavContext, sql As String, ParamArray prms As Object()) As Task(Of IEnumerable(Of ICardFile))
        Dim items As IList(Of ICardFile) = New List(Of ICardFile)()
        Dim stopWatch As Stopwatch = Stopwatch.StartNew()
        Using reader As SqlDataReader = Await context.ExecuteReaderAsync(sql, prms)
            Dim cards As DataTable = New DataTable()
            cards.Load(reader)
            Dim emails As DataTable = New DataTable()
            If Not reader.IsClosed Then emails.Load(reader)
            Dim addresses As DataTable = New DataTable()
            If Not reader.IsClosed Then addresses.Load(reader)
            Dim instantMessengers As DataTable = New DataTable()
            If Not reader.IsClosed Then instantMessengers.Load(reader)
            Dim telephones As DataTable = New DataTable()
            If Not reader.IsClosed Then telephones.Load(reader)
            Dim urls As DataTable = New DataTable()
            If Not reader.IsClosed Then urls.Load(reader)
            Dim cardCustomProperties As DataTable = New DataTable()
            If Not reader.IsClosed Then cardCustomProperties.Load(reader)
            stopWatch.Stop()
            context.Engine.Logger.LogDebug(String.Format("SQL took: {0}ms", stopWatch.ElapsedMilliseconds))
            For Each rowCardFile As DataRow In cards.Rows
                Dim rowsEmails As DataRow() = New DataRow(-1) {}
                Dim rowsAddresses As DataRow() = New DataRow(-1) {}
                Dim rowsInstantMessengers As DataRow() = New DataRow(-1) {}
                Dim rowsTelephones As DataRow() = New DataRow(-1) {}
                Dim rowsUrls As DataRow() = New DataRow(-1) {}
                Dim rowsCustomProperties As DataRow() = New DataRow(-1) {}
                Dim uid As String = rowCardFile.Field(Of String)("UID")
                If emails.Columns("UID") IsNot Nothing Then
                    Dim filter As String = String.Format("UID = '{0}'", uid)
                    rowsEmails = emails.Select(filter)
                    rowsAddresses = addresses.Select(filter)
                    rowsInstantMessengers = instantMessengers.Select(filter)
                    rowsTelephones = telephones.Select(filter)
                    rowsUrls = urls.Select(filter)
                    rowsCustomProperties = cardCustomProperties.Select(filter)
                End If

                Dim fileName As String = rowCardFile.Field(Of String)("FileName")
                items.Add(New CardFile(context, fileName, rowCardFile, rowsEmails, rowsAddresses, rowsInstantMessengers, rowsTelephones, rowsUrls, rowsCustomProperties))
            Next
        End Using

        Return items
    End Function

    Public Shared Function CreateCardFile(context As DavContext, addressbookFolderId As Guid, fileName As String) As CardFile
        Dim cardFile As CardFile = New CardFile(context, fileName, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing)
        cardFile.addressbookFolderId = addressbookFolderId
        Return cardFile
    End Function

    Private ReadOnly fileName As String = Nothing

    Private ReadOnly rowCardFile As DataRow = Nothing

    Private ReadOnly rowsEmails As DataRow() = Nothing

    Private ReadOnly rowsAddresses As DataRow() = Nothing

    Private ReadOnly rowsInstantMessengers As DataRow() = Nothing

    Private ReadOnly rowsTelephones As DataRow() = Nothing

    Private ReadOnly rowsUrls As DataRow() = Nothing

    Private ReadOnly rowsCustomProperties As DataRow() = Nothing

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

    Private paramIndex As Integer = 0

    Private addressbookFolderId As Guid = Guid.Empty

    Public Overrides ReadOnly Property Name As String Implements IHierarchyItemBase.Name
        Get
            Return rowCardFile.Field(Of String)("FormattedName")
        End Get
    End Property

    Public Overrides ReadOnly Property Path As String Implements IHierarchyItemBase.Path
        Get
            Dim addressbookFolderId As Guid = rowCardFile.Field(Of Guid)("AddressbookFolderId")
            Dim fileName As String = rowCardFile.Field(Of String)("FileName")
            Return String.Format("{0}{1}/{2}{3}", AddressbooksRootFolder.AddressbooksRootFolderPath, addressbookFolderId, fileName, Extension)
        End Get
    End Property

    Public ReadOnly Property Etag As String Implements IContent.Etag
        Get
            Dim bETag As Byte() = rowCardFile.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 rowCardFile.Field(Of DateTime)("CreatedUtc")
        End Get
    End Property

    Public Overrides ReadOnly Property Modified As DateTime Implements IHierarchyItemBase.Modified
        Get
            Return rowCardFile.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/vcard"
        End Get
    End Property

    Private Sub New(context As DavContext, fileName As String,
                   rowCardFile As DataRow, rowsEmails As DataRow(), rowsAddresses As DataRow(), rowsInstantMessengers As DataRow(),
                   rowsTelephones As DataRow(), rowsUrls As DataRow(), rowsCustomProperties As DataRow())
        MyBase.New(context)
        Me.fileName = fileName
        Me.rowCardFile = rowCardFile
        Me.rowsEmails = rowsEmails
        Me.rowsAddresses = rowsAddresses
        Me.rowsInstantMessengers = rowsInstantMessengers
        Me.rowsTelephones = rowsTelephones
        Me.rowsUrls = rowsUrls
        Me.rowsCustomProperties = rowsCustomProperties
    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 large card files.
        System.Web.HttpContext.Current.Server.ScriptTimeout = Integer.MaxValue
        Dim vCard As String
        Using reader As StreamReader = New StreamReader(stream)
            vCard = Await reader.ReadToEndAsync()
        End Using

        ' Typically the stream contains a single vCard.
        Dim cards As IEnumerable(Of IComponent) = New vFormatter().Deserialize(vCard)
        Dim card As ICard2 = TryCast(cards.First(), ICard2)
        ' Card file UID which is equal to file name (??).
        ' The type is used to uniquely identify the object that the vCard represents. The "uuid" URN namespace defined in 
        ' [RFC4122] is particularly well-suited to this task, but other URI schemes MAY be used.
        Dim uid As String = If(card.Uid?.Text, "UID:urn:uuid:" & Guid.NewGuid().ToString())
        ' Check if this CardDAV client application requires properties conversion.
        If AppleCardInteroperability.NeedsConversion(Context.Request.UserAgent) Then
            AppleCardInteroperability.Normalize(card)
        End If

        ' The client app name is stored in DB to update and extract only custom props created by the client making a request.
        Dim clientAppName As String = AppleCardInteroperability.GetClientAppName(Context.Request.UserAgent)
        ' Save data to [card_CardFile] table.
        Await WriteCardFileAsync(Context, card, addressbookFolderId, isNew, uid, clientAppName)
        ' Save emails.
        Await WriteEmailsAsync(Context, card.Emails, uid, clientAppName)
        ' Save addresses.
        Await WriteAddressesAsync(Context, card.Addresses, uid, clientAppName)
        ' Save telephones.
        Await WriteTelephonesAsync(Context, card.Telephones, uid, clientAppName)
        ' Save URLs
        Await WriteUrlsAsync(Context, card.Urls, uid, clientAppName)
        ' Save instant messengers. vCard 3.0+ only
        Dim card3 As ICard3 = TryCast(card, ICard3)
        If card3 IsNot Nothing Then
            Await WriteInstantMessengersAsync(Context, card3.InstantMessengers, uid, clientAppName)
        End If

        Return True
    End Function

    Private Async Function WriteCardFileAsync(context As DavContext, card As ICard2, addressbookFolderId As Guid, isNew As Boolean, uid As String, clientAppName As String) As Task
        Dim sql As String
        If isNew Then
            sql = "IF EXISTS (SELECT 1 FROM [card_Access] WHERE [AddressbookFolderId]=@AddressbookFolderId AND [UserId]=@UserId AND [Write]=1)
                  INSERT INTO [card_CardFile] (
                      [UID]
                    , [AddressbookFolderId]
                 , [FileName]
                 , [Version]
                 , [Product]
                 , [FormattedName]
                 , [FamilyName]
                 , [GivenName]
                 , [AdditionalNames]
                 , [HonorificPrefix]
                 , [HonorificSuffix]
                 , [Kind]
                 , [Nickname]
                 , [Photo]
                 , [PhotoMediaType]
                 , [Logo]
                 , [LogoMediaType]
                 , [Sound]
                 , [SoundMediaType]
                 , [Birthday]
                 , [Anniversary]
                 , [Gender]
                 , [RevisionUtc]
                 , [SortString]
                 , [Language]
                 , [TimeZone]
                 , [Geo]
                 , [Title]
                 , [Role]
                 , [OrgName]
                 , [OrgUnit]
                 , [Categories]
                 , [Note]
                 , [Classification]
                ) VALUES (
                      @UID
                    , @AddressbookFolderId
                    , @FileName
                    , @Version
                    , @Product
                 , @FormattedName
                 , @FamilyName
                 , @GivenName
                 , @AdditionalNames
                 , @HonorificPrefix
                 , @HonorificSuffix
                 , @Kind
                 , @Nickname
                 , @Photo
                 , @PhotoMediaType
                 , @Logo
                 , @LogoMediaType
                 , @Sound
                 , @SoundMediaType
                 , @Birthday
                 , @Anniversary
                 , @Gender
                 , @RevisionUtc
                 , @SortString
                 , @Language
                 , @TimeZone
                 , @Geo
                 , @Title
                 , @Role
                 , @OrgName
                 , @OrgUnit
                 , @Categories
                 , @Note
                 , @Classification
                )"
        Else
            ' We can only update record in [card_CardFile] table.
            ' There is no way to update [card_Email], [card_Address], [card_InstantMessenger], [card_Telephone] and [card_Url]
            ' tables for existing card, we must delete all records for this UID and recreate.
            ' We can keep custom props in [card_CustomProperty] table if they were created by a different CardDAV client.
            ' [ModifiedUtc] field update triggers [ETag] field update which is used for synchronyzation.
            sql = "IF EXISTS (SELECT 1 FROM [card_CardFile] 
                       WHERE FileName=@FileName
                       AND [AddressbookFolderId] IN (SELECT [AddressbookFolderId] FROM [card_Access] WHERE [UserId]=@UserId AND [Write] = 1))
                  BEGIN
                      UPDATE [card_CardFile] SET 
                          [ModifiedUtc]     = @ModifiedUtc
                     , [Version]         = @Version
                     , [Product]         = @Product
                     , [FormattedName]   = @FormattedName
                     , [FamilyName]      = @FamilyName
                     , [GivenName]       = @GivenName
                     , [AdditionalNames] = @AdditionalNames
                     , [HonorificPrefix] = @HonorificPrefix
                     , [HonorificSuffix] = @HonorificSuffix
                     , [Kind]            = @Kind
                     , [Nickname]        = @Nickname
                     , [Photo]           = @Photo
                     , [PhotoMediaType]  = @PhotoMediaType
                     , [Logo]            = @Logo
                     , [LogoMediaType]   = @LogoMediaType
                     , [Sound]           = @Sound
                     , [SoundMediaType]  = @SoundMediaType
                     , [Birthday]        = @Birthday
                     , [Anniversary]     = @Anniversary
                     , [Gender]          = @Gender
                     , [RevisionUtc]     = @RevisionUtc
                     , [SortString]      = @SortString
                     , [Language]        = @Language
                     , [TimeZone]        = @TimeZone
                     , [Geo]             = @Geo
                     , [Title]           = @Title
                     , [Role]            = @Role
                     , [OrgName]         = @OrgName
                     , [OrgUnit]         = @OrgUnit
                     , [Categories]      = @Categories
                     , [Note]            = @Note
                     , [Classification]  = @Classification
                      WHERE 
                        [UID] = @UID

                      ; DELETE FROM [card_Email]              WHERE [UID] = @UID
                      ; DELETE FROM [card_Address]            WHERE [UID] = @UID
                      ; DELETE FROM [card_InstantMessenger]   WHERE [UID] = @UID
                      ; DELETE FROM [card_Telephone]          WHERE [UID] = @UID
                      ; DELETE FROM [card_Url]                WHERE [UID] = @UID
                      ; DELETE FROM [card_CustomProperty]     WHERE [UID] = @UID AND (([ClientAppName] = @ClientAppName) OR ([ParentId] != [UID]) OR ([ClientAppName] IS NULL))
                  END"
        End If

        ' [ClientAppName] = @ClientAppName -> delete all custom props created by this client.
        ' [ParentId] != [UID]              -> delete all custom params from multiple props: EMAIL, ADR, TEL, IMPP. Keep custom params for any single props in [card_Card].
        ' [ClientAppName] IS NULL          -> delete all custom props created by some unknown CardDAV client.
        If Await context.ExecuteNonQueryAsync(sql,
                                             "@UID", uid,                                                                   ' UID
                                             "UserId", context.UserId,
                                             "@AddressbookFolderId", addressbookFolderId,                                               ' used only when inserting
                                             "@FileName", fileName,                                                              ' In case of CardDAV a file name is sypically a GUID, but it is different from UID.
                                             "@ModifiedUtc", DateTime.UtcNow,
                                             "@Version", card.Version.Text,                                                     ' VERSION
                                             "@Product", card.ProductId?.Text,                                                  ' PRODID
                                             "@FormattedName", card.FormattedNames.PreferedOrFirstProperty.Text,                      ' FN                           Here we assume only 1 prop for the sake of simplicity.
                                             "@FamilyName", card.Name.FamilyName,                                                  ' N
                                             "@GivenName", card.Name.GivenName,                                                   ' N
                                             "@AdditionalNames", card.Name.AdditionalNamesList,                                         ' N
                                             "@HonorificPrefix", card.Name.HonorificPrefix,                                             ' N
                                             "@HonorificSuffix", card.Name.HonorificSuffix,                                             ' N
                                             "@Kind", TryCast(card, ICard4)?.Kind?.Text,                                          ' KIND         (vCard 4.0)
                                             "@Nickname", TryCast(card, ICard3)?.NickNames.PreferedOrFirstProperty?.Values.First(),   ' NICKNAME     (vCard 3.0+)    Here we assume only 1 prop with 1 value for the sake of simplicity.
                                             CreateVarBinaryParam("@Photo", card.Photos.PreferedOrFirstProperty?.Base64Data),           ' PHOTO                        Here we assume only 1 prop for the sake of simplicity.
                                             "@PhotoMediaType", card.Photos.PreferedOrFirstProperty?.MediaType,                        ' PHOTO TYPE param
                                             CreateVarBinaryParam("@Logo", card.Logos.PreferedOrFirstProperty?.Base64Data),            ' LOGO                         Here we assume only 1 prop for the sake of simplicity.
                                             "@LogoMediaType", card.Logos.PreferedOrFirstProperty?.MediaType,                         ' LOGO  TYPE param
                                             CreateVarBinaryParam("@Sound", card.Sounds.PreferedOrFirstProperty?.Base64Data),           ' SOUND                        Here we assume only 1 prop for the sake of simplicity.
                                             "@SoundMediaType", card.Sounds.PreferedOrFirstProperty?.MediaType,                        ' SOUND TYPE param
                                             New SqlParameter("@Birthday", If(card.BirthDate?.Value?.DateVal, TryCast(DBNull.Value, Object))) With {.SqlDbType = SqlDbType.DateTime2}, New SqlParameter("@Anniversary", If(TryCast(card, ICard4)?.Anniversary?.Value?.DateVal, TryCast(DBNull.Value, Object))) With {.SqlDbType = SqlDbType.DateTime2}, "@Gender", TryCast(card, ICard4)?.Gender?.Sex,                                         ' GENDER       (vCard 4.0)
                                             "@RevisionUtc", card.Revision?.Value.DateVal,                                          ' REV
                                             "@SortString", card.SortString?.Text,                                                 ' SORT-STRING
                                             "@Language", TryCast(card, ICard4)?.ContactLanguages.PreferedOrFirstProperty?.Text,      ' LANG         (vCard 4.0)     Here we assume only 1 prop for the sake of simplicity.
                                             "@TimeZone", card.TimeZones.PreferedOrFirstProperty?.Text,                          ' TZ
                                             "@Geo", Nothing, "@Title", card.Titles.PreferedOrFirstProperty?.Text,                             ' TITLE
                                             "@Role", card.Roles.PreferedOrFirstProperty?.Text,                              ' ROLE
                                             "@OrgName", card.Organizations.PreferedOrFirstProperty?.Name,                      ' ORG                          Here we assume only 1 prop for the sake of simplicity.
                                             "@OrgUnit", card.Organizations.PreferedOrFirstProperty?.Units?.First(),            ' ORG                          Here we assume only 1 prop with 1 unit value for the sake of simplicity.
                                             "@Categories", ListToString(Of String)(TryCast(card, ICard3)?.Categories.Select(Function(x) ListToString(Of String)(x.Values, ",")), ";"), ' CATEGORIES  (vCard 3.0+)
                                             "@Note", card.Notes.PreferedOrFirstProperty?.Text,                              ' NOTE                         Here we assume only 1 prop for the sake of simplicity.
                                             "@Classification", TryCast(card, ICard3)?.Classes.PreferedOrFirstProperty?.Text,               ' CLASS                        Here we assume only 1 prop for the sake of simplicity.
                                             "@ClientAppName", clientAppName                                                         ' Used to keep custom props created by this CardDAV client when updated by other CardDAV clients.
                                             ) < 1 Then
            Throw New DavException("Item not found or you do not have enough permissions to complete this operation.", DavStatus.FORBIDDEN)
        End If

        ' Save custom properties and parameters of this component to [card_CustomProperty] table.
        Dim customPropsSqlInsert As String
        Dim customPropsParamsInsert As List(Of Object)
        If PrepareSqlCustomPropertiesOfComponentAsync(card, uid, uid, clientAppName, 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 Function CreateVarBinaryParam(parameterName As String, base64 As String) As SqlParameter
        Dim param As SqlParameter = New SqlParameter(parameterName, SqlDbType.VarBinary)
        If String.IsNullOrEmpty(base64) Then
            ' To insert NULL to VARBINARY column, SqlParameter must be passed with Size=-1 and Value=DBNull.Value.
            param.Size = -1
            param.Value = DBNull.Value
        Else
            Dim content As Byte() = Convert.FromBase64String(base64)
            param.Size = content.Length
            param.Value = content
        End If

        Return param
    End Function

    Private Async Function WriteEmailsAsync(context As DavContext, emails As ITextPropertyList(Of IEmail2), uid As String, clientAppName As String) As Task
        Dim sql As String = "INSERT INTO [card_Email] (
                  [EmailId]
                , [UID]
                , [Type]
                , [Email]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        Dim parameters As List(Of Object) = New List(Of Object)(New Object() {"@UID", uid
                                                                             })
        Dim i As Integer = 0
        For Each email As IEmail2 In emails
            valuesSql.Add(String.Format("(
                  @EmailId{0}
                , @UID
                , @Type{0}
                , @Email{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i))
            Dim emailId As Guid = Guid.NewGuid()
            parameters.AddRange(New Object() {"@EmailId" & i, emailId,
                                             "@Type" & i, ListToString(Of EmailType)(email.Types),    ' EMAIL TYPE param
                                             "@Email" & i, email.Text,                              ' EMAIL VALUE
                                             "@PreferenceLevel" & i, GetPrefParameter(email),                 ' EMAIL PREF param
                                             "@SortIndex" & i, email.RawProperty.SortIndex             ' Property position in vCard.
                                             })
            ' Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            Dim customPropSqlInsert As String
            Dim customPropParametersInsert As List(Of Object)
            If PrepareSqlParamsWriteCustomProperty("EMAIL", email.RawProperty, emailId.ToString(), uid, clientAppName, 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 WriteAddressesAsync(context As DavContext, addresses As ICardPropertyList(Of IAddress2), uid As String, clientAppName As String) As Task
        Dim sql As String = "INSERT INTO [card_Address] (
                  [AddressId]
                , [UID]
                , [Type]
                , [PoBox]
                , [AppartmentNumber]
                , [Street]
                , [Locality]
                , [Region]
                , [PostalCode]
                , [Country]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        Dim parameters As List(Of Object) = New List(Of Object)(New Object() {"@UID", uid
                                                                             })
        Dim i As Integer = 0
        For Each address As IAddress2 In addresses
            valuesSql.Add(String.Format("(
                  @AddressId{0}
                , @UID
                , @Type{0}
                , @PoBox{0}
                , @AppartmentNumber{0}
                , @Street{0}
                , @Locality{0}
                , @Region{0}
                , @PostalCode{0}
                , @Country{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i))
            Dim addressId As Guid = Guid.NewGuid()
            parameters.AddRange(New Object() {"@AddressId" & i, addressId,
                                             "@Type" & i, ListToString(Of AddressType)(address.Types),    ' ADR TYPE param
                                             "@PoBox" & i, address.PoBox.FirstOrDefault(),
                                             "@AppartmentNumber" & i, address.AppartmentNumber.FirstOrDefault(),
                                             "@Street" & i, address.Street.FirstOrDefault(),
                                             "@Locality" & i, address.Locality.FirstOrDefault(),
                                             "@Region" & i, address.Region.FirstOrDefault(),
                                             "@PostalCode" & i, address.PostalCode.FirstOrDefault(),
                                             "@Country" & i, address.Country.FirstOrDefault(),
                                             "@PreferenceLevel" & i, GetPrefParameter(address),                   ' ADR PREF param
                                             "@SortIndex" & i, address.RawProperty.SortIndex               ' Property position in vCard.
                                             })
            ' Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            Dim customPropSqlInsert As String
            Dim customPropParametersInsert As List(Of Object)
            If PrepareSqlParamsWriteCustomProperty("ADR", address.RawProperty, addressId.ToString(), uid, clientAppName, 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 WriteInstantMessengersAsync(context As DavContext, instantMessengers As ITextPropertyList(Of IInstantMessenger3), uid As String, clientAppName As String) As Task
        Dim sql As String = "INSERT INTO [card_InstantMessenger] (
                  [InstantMessengerId]
                , [UID]
                , [Type]
                , [InstantMessenger]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        Dim parameters As List(Of Object) = New List(Of Object)(New Object() {"@UID", uid
                                                                             })
        Dim i As Integer = 0
        For Each instantMessenger As IInstantMessenger3 In instantMessengers
            valuesSql.Add(String.Format("(
                  @InstantMessengerId{0}
                , @UID
                , @Type{0}
                , @InstantMessenger{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i))
            Dim instantMessengerId As Guid = Guid.NewGuid()
            parameters.AddRange(New Object() {"@InstantMessengerId" & i, instantMessengerId,
                                             "@Type" & i, ListToString(Of MessengerType)(instantMessenger.Types), ' IMPP TYPE param
                                             "@InstantMessenger" & i, instantMessenger.Text,                               ' IMPP VALUE
                                             "@PreferenceLevel" & i, GetPrefParameter(instantMessenger),                  ' IMPP PREF param
                                             "@SortIndex" & i, instantMessenger.RawProperty.SortIndex              ' Property position in vCard.
                                             })
            ' Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            Dim customPropSqlInsert As String
            Dim customPropParametersInsert As List(Of Object)
            If PrepareSqlParamsWriteCustomProperty("IMPP", instantMessenger.RawProperty, instantMessengerId.ToString(), uid, clientAppName, 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 WriteTelephonesAsync(context As DavContext, telephones As ITextPropertyList(Of ITelephone2), uid As String, clientAppName As String) As Task
        Dim sql As String = "INSERT INTO [card_Telephone] (
                  [TelephoneId]
                , [UID]
                , [Type]
                , [Telephone]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        Dim parameters As List(Of Object) = New List(Of Object)(New Object() {"@UID", uid
                                                                             })
        Dim i As Integer = 0
        For Each telephone As ITelephone2 In telephones
            valuesSql.Add(String.Format("(
                  @TelephoneId{0}
                , @UID
                , @Type{0}
                , @Telephone{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i))
            Dim telephoneId As Guid = Guid.NewGuid()
            parameters.AddRange(New Object() {"@TelephoneId" & i, telephoneId,
                                             "@Type" & i, ListToString(Of TelephoneType)(telephone.Types),' TEL TYPE param
                                             "@Telephone" & i, telephone.Text,                              ' TEL VALUE
                                             "@PreferenceLevel" & i, GetPrefParameter(telephone),                 ' TEL PREF param
                                             "@SortIndex" & i, telephone.RawProperty.SortIndex             ' Property position in vCard.
                                             })
            ' Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            Dim customPropSqlInsert As String
            Dim customPropParametersInsert As List(Of Object)
            If PrepareSqlParamsWriteCustomProperty("TEL", telephone.RawProperty, telephoneId.ToString(), uid, clientAppName, 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 WriteUrlsAsync(context As DavContext, urls As ICardPropertyList(Of ICardUriProperty2), uid As String, clientAppName As String) As Task
        Dim sql As String = "INSERT INTO [card_Url] (
                  [UrlId]
                , [UID]
                , [Type]
                , [Url]
                , [PreferenceLevel]
                , [SortIndex]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        Dim parameters As List(Of Object) = New List(Of Object)(New Object() {"@UID", uid
                                                                             })
        Dim i As Integer = 0
        For Each url As ICardUriProperty2 In urls
            valuesSql.Add(String.Format("(
                  @UrlId{0}
                , @UID
                , @Type{0}
                , @Url{0}
                , @PreferenceLevel{0}
                , @SortIndex{0}
            )", i))
            Dim urlId As Guid = Guid.NewGuid()
            parameters.AddRange(New Object() {"@UrlId" & i, urlId,
                                             "@Type" & i, ListToString(Of ExtendibleEnum)(url.Types), ' TEL TYPE param 
                                             "@Url" & i, url.Text,                                ' URL VALUE
                                             "@PreferenceLevel" & i, GetPrefParameter(url),                   ' URL PREF param
                                             "@SortIndex" & i, url.RawProperty.SortIndex               ' Property position in vCard.
                                             })
            ' Prepare SQL to save custom property parameters to [card_CustomProperty] table.
            Dim customPropSqlInsert As String
            Dim customPropParametersInsert As List(Of Object)
            If PrepareSqlParamsWriteCustomProperty("URL", url.RawProperty, urlId.ToString(), uid, clientAppName, 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 Shared Function GetPrefParameter(prop As ICardMultiProperty) As Byte?
        Dim prop4 As ICardMultiProperty4 = TryCast(prop, ICardMultiProperty4)
        If prop4 Is Nothing Then
            Return If(prop.IsPrefered, CType(1, Byte?), Nothing)
        End If

        Return CType(prop4.PreferenceLevel, Byte?)
    End Function

    Private Function PrepareSqlParamsWriteCustomProperty(propName As String, prop As IRawProperty, parentId As String, uid As String, clientAppName As String, ByRef sql As String, ByRef parameters As List(Of Object)) As Boolean
        sql = "INSERT INTO [card_CustomProperty] (
                  [ParentId]
                , [UID]
                , [ClientAppName]
                , [PropertyName]
                , [ParameterName]
                , [Value]
                , [SortIndex]
            ) VALUES {0}"
        Dim valuesSql As List(Of String) = New List(Of String)()
        parameters = New List(Of Object)()
        Dim origParamsCount As Integer = parameters.Count()
        ' Custom properties are one of the following:
        '  - props that start with "X-". This is a standard-based approach to creating custom props.
        '  - props that has "." in its name. Typically "item1.X-PROP". Such props are created by iOS and OS X.
        Dim isCustomProp As Boolean = propName.StartsWith("X-", StringComparison.InvariantCultureIgnoreCase) OrElse propName.Contains(".")
        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}
                            , @ClientAppName{0}
                            , @PropertyName{0}
                            , @ParameterName{0}
                            , @Value{0}
                            , @SortIndexParam{0}
                            )", paramIndex))
            parameters.AddRange(New Object() {"@ParentId" & paramIndex, parentId,
                                             "@UID" & paramIndex, uid,              ' Added for performance optimization purposes.
                                             "@ClientAppName" & paramIndex, clientAppName,    ' Client app name that created this custom property.
                                             "@PropertyName" & paramIndex, propName,
                                             "@ParameterName" & paramIndex, paramName,        ' null is inserted to mark prop value.
                                             "@Value" & paramIndex, val,
                                             "@SortIndexParam" & paramIndex, prop.SortIndex   ' Property position in vCard.
                                             })
            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 params 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}
                            , @ClientAppName{0}
                            , @PropertyName{0}
                            , @ParameterName{0}
                            , @Value{0}
                            , @SortIndexParam{0}
                            )", paramIndex))
                parameters.AddRange(New Object() {"@ParentId" & paramIndex, parentId,
                                                 "@UID" & paramIndex, uid,          ' added for performance optimization purposes
                                                 "@ClientAppName" & paramIndex, clientAppName,' Client app name that created this custom parameter.
                                                 "@PropertyName" & paramIndex, propName,
                                                 "@ParameterName" & paramIndex, paramName,
                                                 "@Value" & paramIndex, val,
                                                 "@SortIndexParam" & paramIndex, Nothing})
                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 String, uid As String, clientAppName 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() {"EMAIL", "ADR", "IMPP", "TEL", "URL"}
        ' 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, clientAppName, 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 sql As String = "DELETE FROM [card_CardFile] 
                       WHERE FileName=@FileName
                       AND [AddressbookFolderId] IN (SELECT [AddressbookFolderId] FROM [card_Access] WHERE [UserId]=@UserId AND [Write] = 1)"
        If Await Context.ExecuteNonQueryAsync(sql, 
                                             "@UserId", Context.UserId,
                                             "@FileName", fileName) < 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

    Public Async Function ReadAsync(output As Stream, startIndex As Long, count As Long) As Task Implements IContent.ReadAsync
        Dim vCardVersion As String = rowCardFile.Field(Of String)("Version")
        Dim card As ICard2 = CardFactory.CreateCard(vCardVersion)
        ReadCard(card)
        ReadEmails(card.Emails, rowsEmails)
        ReadAddresses(card.Addresses, rowsAddresses)
        ReadTelephones(card.Telephones, rowsTelephones)
        ReadUrls(card.Urls, rowsUrls)
        ' IMPP is vCard 3.0 & 4.0 prop
        Dim card3 As ICard3 = TryCast(card, ICard3)
        If card3 IsNot Nothing Then
            ReadMessengers(card3.InstantMessengers, rowsInstantMessengers)
        End If

        ' Check if this CardDAV client application requires properties conversion.
        If AppleCardInteroperability.NeedsConversion(Context.Request.UserAgent) Then
            ' In case of iOS & OS X the props below must be converted to the following format:
            ' item2.TEL:(222)222 - 2222
            ' item2.X-ABLabel:Emergency
            AppleCardInteroperability.Denormalize(card)
        End If

        Call New vFormatter().Serialize(output, card)
    End Function

    Private Sub ReadCard(card As ICard2)
        Dim uid As String = rowCardFile.Field(Of String)("UID")
        'UID
        card.Uid = card.CreateTextProp(uid)
        ' PRODID
        card.ProductId = card.CreateTextProp("-//IT Hit//Collab Lib//EN")
        ' FN
        card.FormattedNames.Add(rowCardFile.Field(Of String)("FormattedName"))
        ' N
        card.Name = card.CreateNameProp(rowCardFile.Field(Of String)("FamilyName"),
                                       rowCardFile.Field(Of String)("GivenName"),
                                       rowCardFile.Field(Of String)("AdditionalNames"),
                                       rowCardFile.Field(Of String)("HonorificPrefix"),
                                       rowCardFile.Field(Of String)("HonorificSuffix"))
        ' PHOTO
        If Not rowCardFile.IsNull("Photo") Then
            card.Photos.Add(Convert.ToBase64String(rowCardFile.Field(Of Byte())("Photo")), rowCardFile.Field(Of String)("PhotoMediaType"), False)
        End If

        ' LOGO
        If Not rowCardFile.IsNull("Logo") Then
            card.Photos.Add(Convert.ToBase64String(rowCardFile.Field(Of Byte())("Logo")), rowCardFile.Field(Of String)("LogoMediaType"), False)
        End If

        ' SOUND
        If Not rowCardFile.IsNull("Sound") Then
            card.Photos.Add(Convert.ToBase64String(rowCardFile.Field(Of Byte())("Sound")), rowCardFile.Field(Of String)("SoundMediaType"), False)
        End If

        ' BDAY
        Dim birthday As DateTime? = rowCardFile.Field(Of DateTime?)("Birthday")
        If birthday IsNot Nothing Then
            card.BirthDate = card.CreateDateProp(birthday.Value, DateComponents.[Date])
        End If

        ' REV
        Dim revision As DateTime? = rowCardFile.Field(Of DateTime?)("RevisionUtc")
        If revision IsNot Nothing Then
            card.Revision = card.CreateDateProp(revision.Value)
        End If

        ' SORT-STRING
        Dim sortString As String = rowCardFile.Field(Of String)("SortString")
        If Not String.IsNullOrEmpty(sortString) Then
            Dim propSortString As ITextProperty2 = card.CreateProperty(Of ITextProperty2)()
            propSortString.Text = sortString
            card.SortString = propSortString
        End If

        ' TZ
        Dim timeZone As String = rowCardFile.Field(Of String)("TimeZone")
        If Not String.IsNullOrEmpty(timeZone) Then
            card.TimeZones.Add(timeZone)
        End If

        ' GEO
        ' TITLE
        Dim title As String = rowCardFile.Field(Of String)("Title")
        If Not String.IsNullOrEmpty(title) Then
            card.Titles.Add(title)
        End If

        ' ROLE
        Dim role As String = rowCardFile.Field(Of String)("Role")
        If Not String.IsNullOrEmpty(role) Then
            card.Roles.Add(role)
        End If

        ' ORG
        Dim orgName As String = rowCardFile.Field(Of String)("OrgName")
        Dim orgUnit As String = rowCardFile.Field(Of String)("OrgUnit")
        If Not String.IsNullOrEmpty(orgName) OrElse Not String.IsNullOrEmpty(orgUnit) Then
            Dim propOrg As IOrganization2 = card.Organizations.CreateProperty()
            propOrg.Name = orgName
            propOrg.Units = {orgUnit}
            card.Organizations.Add(propOrg)
        End If

        ' NOTE
        Dim note As String = rowCardFile.Field(Of String)("Note")
        If Not String.IsNullOrEmpty(note) Then
            card.Notes.Add(note)
        End If

        ' vCard v3.0 & v4.0 props
        If TypeOf card Is ICard3 Then
            Dim card3 As ICard3 = TryCast(card, ICard3)
            ' NICKNAME
            Dim nickname As String = rowCardFile.Field(Of String)("Nickname")
            If Not String.IsNullOrEmpty(nickname) Then
                Dim propNickname As INickname3 = card3.NickNames.CreateProperty()
                propNickname.Values = {nickname}
                card3.NickNames.Add(propNickname)
            End If

            ' CATEGORIES
            Dim categories As String = rowCardFile.Field(Of String)("Categories")
            If Not String.IsNullOrEmpty(categories) Then
                Dim aCategories As String() = categories.Split({";"c}, StringSplitOptions.RemoveEmptyEntries)
                For Each categoryList As String In aCategories
                    Dim catProp As ICategories3 = card3.Categories.CreateProperty()
                    catProp.Values = categoryList.Split({","c}, StringSplitOptions.RemoveEmptyEntries)
                    card3.Categories.Add(catProp)
                Next
            End If

            ' CLASS
            Dim classification As String = rowCardFile.Field(Of String)("Classification")
            If Not String.IsNullOrEmpty(classification) Then
                card3.Classes.Add(classification)
            End If
        End If

        ' vCard v4.0 props
        If TypeOf card Is ICard4 Then
            Dim card4 As ICard4 = TryCast(card, ICard4)
            ' KIND
            Dim kind As String = rowCardFile.Field(Of String)("Kind")
            If kind IsNot Nothing Then
                Dim propKind As IKind4 = card4.CreateProperty(Of IKind4)()
                propKind.Text = kind
                card4.Kind = propKind
            End If

            ' ANNIVERSARY
            Dim anniversary As DateTime? = rowCardFile.Field(Of DateTime?)("Anniversary")
            If anniversary IsNot Nothing Then
                Dim propAnniversary As IAnniversary4 = card4.CreateProperty(Of IAnniversary4)()
                propAnniversary.Value = New [Date](anniversary.Value, DateComponents.Month Or DateComponents.[Date])
                card4.Anniversary = propAnniversary
            End If

            ' GENDER
            Dim gender As String = rowCardFile.Field(Of String)("Gender")
            If Not String.IsNullOrEmpty(gender) Then
                Dim propGender As IGender4 = card4.CreateProperty(Of IGender4)()
                propGender.Text = gender
                card4.Gender = propGender
            End If

            ' LANG
            Dim language As String = rowCardFile.Field(Of String)("Language")
            If Not String.IsNullOrEmpty(language) Then
                card4.ContactLanguages.Add(language)
            End If
        End If

        ' Get custom properties and custom parameters
        Dim rowsCardCustomProperties As IEnumerable(Of DataRow) = rowsCustomProperties.Where(Function(x) x.Field(Of String)("ParentId") = uid)
        ReadCustomProperties(card, rowsCardCustomProperties)
    End Sub

    Private Sub ReadEmails(emails As ITextPropertyList(Of IEmail2), rowsEmails As IEnumerable(Of DataRow))
        For Each rowEmail As DataRow In rowsEmails
            Dim email As IEmail2 = emails.CreateProperty()
            email.Text = rowEmail.Field(Of String)("Email")
            email.Types = ParseType(Of EmailType)(rowEmail.Field(Of String)("Type"))
            SetPrefParameter(email, rowEmail.Field(Of Byte?)("PreferenceLevel"))
            email.RawProperty.SortIndex = rowEmail.Field(Of Integer)("SortIndex")
            AddParamValues(rowEmail.Field(Of Guid)("EmailId"), email.RawProperty)
            emails.Add(email)
        Next
    End Sub

    Private Sub ReadAddresses(addresses As ICardPropertyList(Of IAddress2), rowsAddresses As IEnumerable(Of DataRow))
        For Each rowAddress As DataRow In rowsAddresses
            Dim address As IAddress2 = addresses.CreateProperty()
            address.SetAddress({rowAddress.Field(Of String)("PoBox")},
                              {rowAddress.Field(Of String)("AppartmentNumber")},
                              {rowAddress.Field(Of String)("Street")},
                              {rowAddress.Field(Of String)("Locality")},
                              {rowAddress.Field(Of String)("Region")},
                              {rowAddress.Field(Of String)("PostalCode")},
                              {rowAddress.Field(Of String)("Country")},
                              ParseType(Of AddressType)(rowAddress.Field(Of String)("Type")))
            SetPrefParameter(address, rowAddress.Field(Of Byte?)("PreferenceLevel"))
            address.RawProperty.SortIndex = rowAddress.Field(Of Integer)("SortIndex")
            AddParamValues(rowAddress.Field(Of Guid)("AddressId"), address.RawProperty)
            addresses.Add(address)
        Next
    End Sub

    Private Sub ReadMessengers(messengers As ITextPropertyList(Of IInstantMessenger3), rowsMessengers As IEnumerable(Of DataRow))
        For Each rowMessenger As DataRow In rowsMessengers
            Dim messenger As IInstantMessenger3 = messengers.CreateProperty()
            messenger.Text = rowMessenger.Field(Of String)("InstantMessenger")
            messenger.Types = ParseType(Of MessengerType)(rowMessenger.Field(Of String)("Type"))
            SetPrefParameter(messenger, rowMessenger.Field(Of Byte?)("PreferenceLevel"))
            messenger.RawProperty.SortIndex = rowMessenger.Field(Of Integer)("SortIndex")
            AddParamValues(rowMessenger.Field(Of Guid)("InstantMessengerId"), messenger.RawProperty)
            messengers.Add(messenger)
        Next
    End Sub

    Private Sub ReadTelephones(telephones As ITextPropertyList(Of ITelephone2), rowsTelephones As IEnumerable(Of DataRow))
        For Each rowTelephone As DataRow In rowsTelephones
            Dim telephone As ITelephone2 = telephones.CreateProperty()
            telephone.Text = rowTelephone.Field(Of String)("Telephone")
            telephone.Types = ParseType(Of TelephoneType)(rowTelephone.Field(Of String)("Type"))
            SetPrefParameter(telephone, rowTelephone.Field(Of Byte?)("PreferenceLevel"))
            telephone.RawProperty.SortIndex = rowTelephone.Field(Of Integer)("SortIndex")
            AddParamValues(rowTelephone.Field(Of Guid)("TelephoneId"), telephone.RawProperty)
            telephones.Add(telephone)
        Next
    End Sub

    Private Sub ReadUrls(urls As ICardPropertyList(Of ICardUriProperty2), rowsUrls As IEnumerable(Of DataRow))
        For Each rowUrl As DataRow In rowsUrls
            Dim url As ICardUriProperty2 = urls.CreateProperty()
            url.Text = rowUrl.Field(Of String)("Url")
            url.Types = ParseType(Of ExtendibleEnum)(rowUrl.Field(Of String)("Type"))
            SetPrefParameter(url, rowUrl.Field(Of Byte?)("PreferenceLevel"))
            url.RawProperty.SortIndex = rowUrl.Field(Of Integer)("SortIndex")
            AddParamValues(rowUrl.Field(Of Guid)("UrlId"), url.RawProperty)
            urls.Add(url)
        Next
    End Sub

    Private Shared Sub SetPrefParameter(prop As ICardMultiProperty, preferenceLevel As Byte?)
        If preferenceLevel IsNot Nothing Then
            Dim prop4 As ICardMultiProperty4 = TryCast(prop, ICardMultiProperty4)
            If prop4 Is Nothing Then
                ' vCard 2.1 & 3.0
                prop.IsPrefered = True
            Else
                ' vCard 4.0
                prop4.PreferenceLevel = preferenceLevel.Value
            End If
        End If
    End Sub

    Private Shared Function ParseType(Of T As {ExtendibleEnum, New})(typesList As String) As T()
        If Not String.IsNullOrEmpty(typesList) Then
            Dim aStrTypes As String() = typesList.Split({","c}, StringSplitOptions.RemoveEmptyEntries)
            Return aStrTypes.Select(Function(x) StringToEnum(Of T)(x)).ToArray()
        End If

        Return New T() {}
    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
                prop.SortIndex = rowCustomProperty.Field(Of Integer)("SortIndex")
            Else
                prop.Parameters.Add(New Parameter(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 String)("ParentId") = propertyId.ToString())
        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")
            prop.Parameters.Add(New Parameter(paramName, paramValue))
        Next
    End Sub

    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

No code example is currently available or this language may not be supported.

No code example is currently available or this language may not be supported.