Click or drag to resize

ICardFileAsync Interface

IT Hit WebDAV Classes Reference
Represents business card in address book on a CardDAV server.

Namespace:  ITHit.WebDAV.Server.CardDav
Assembly:  ITHit.WebDAV.Server (in ITHit.WebDAV.Server.dll) Version: 11.3.10719
Syntax
public interface ICardFileAsync : IAddressbookItem, 
	IFileAsync, IHierarchyItemAsync, IHierarchyItemBaseAsync, IContentAsync

public interface ICardFileAsync : IAddressbookItem, 
	IFileAsync, IHierarchyItemAsync, IHierarchyItemBaseAsync, IContentAsync

The ICardFileAsync type exposes the following members.

Properties
  NameDescription
Public propertyCode exampleContentLength
Gets the size of the file content in bytes.
(Inherited from IContentAsync.)
Public propertyCode exampleContentType
Gets the media type of the file.
(Inherited from IContentAsync.)
Public propertyCode exampleCreated
Gets the creation date of the item in repository expressed as the coordinated universal time (UTC).
(Inherited from IHierarchyItemBaseAsync.)
Public propertyCode exampleEtag
Gets entity tag - string that identifies current state of resource's content.
(Inherited from IContentAsync.)
Public propertyCode exampleModified
Gets the last modification date of the item in repository expressed as the coordinated universal time (UTC).
(Inherited from IHierarchyItemBaseAsync.)
Public propertyCode exampleName
Gets the name of the item in repository.
(Inherited from IHierarchyItemBaseAsync.)
Public propertyCode examplePath
Unique item path in the repository relative to storage root.
(Inherited from IHierarchyItemBaseAsync.)
Top
Methods
  NameDescription
Public methodCode exampleCopyToAsync
Creates a copy of this item with a new name in the destination folder.
(Inherited from IHierarchyItemAsync.)
Public methodCode exampleDeleteAsync
Deletes this item.
(Inherited from IHierarchyItemAsync.)
Public methodCode exampleGetPropertiesAsync
Gets values of all properties or selected properties for this item.
(Inherited from IHierarchyItemAsync.)
Public methodCode exampleGetPropertyNamesAsync
Gets names of all properties for this item.
(Inherited from IHierarchyItemAsync.)
Public methodCode exampleMoveToAsync
Moves this item to the destination folder under a new name.
(Inherited from IHierarchyItemAsync.)
Public methodCode exampleReadAsync
Reads the file content from the repository and writes it to the specified stream.
(Inherited from IContentAsync.)
Public methodCode exampleUpdatePropertiesAsync
Adds, modifies and removes properties for this item.
(Inherited from IHierarchyItemAsync.)
Public methodCode exampleWriteAsync
Saves the content of the file from the specified stream to the storage repository.
(Inherited from IContentAsync.)
Top
Remarks
This is the marker interface, it does not provide any methods or properties in addition to provided by IFileAsync and IAddressbookItem interfaces. Items of this type will return content returned from [!:IContentAsync.ReadAsync] method in address-data CardDAV property.
Examples

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

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

    public static async Task<IEnumerable<ICardFileAsync>> 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<ICardFileAsync>> 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<ICardFileAsync>> LoadAsync(DavContext context, string sql, params object[] prms)
    {
        IList<ICardFileAsync> items = new List<ICardFileAsync>();

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