/// <summary>
        /// Inserts a new item into the database
        /// </summary>
        /// <param name="con">
        /// The database connection.
        /// </param>
        /// <param name="description">
        /// The source description.
        /// </param>
        /// <param name="currentItem">
        /// The item to be inserted.
        /// </param>
        private void InsertNewItemToDatabase(OleDbConnection con, SourceDescription description, StdContact currentItem)
        {
            var insertColumns = from x in description.ColumnDefinitions where !x.IsAutoValue select x.Title;

            var values = from x in description.ColumnDefinitions
                         where x.IsAutoValue == false
                         select FormatForDatabase(Tools.GetPropertyValue(currentItem, x.Selector), x);

            using (var cmd = con.CreateCommand())
            {
                cmd.CommandText = string.Format(
                    CultureInfo.InvariantCulture,
                    SqlStatementInsertRow,
                    description.MainTable,
                    "[" + string.Join("],[", insertColumns) + "]",
                    string.Join(",", values));
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (OleDbException ex)
                {
                    this.LogProcessingEvent(currentItem, "error writing element: " + ex.Message);
                }
            }
        }
        /// <summary>
        /// Performs a lookup for the PK value of a given entity by querying the table using a specific field mapping
        /// </summary>
        /// <param name="connection">
        /// The database connection to the microsoft access database file.
        /// </param>
        /// <param name="description">
        /// The source description including the table name to be used.
        /// </param>
        /// <param name="fieldMapping">
        /// The field mapping.
        /// </param>
        /// <param name="contact">
        /// The contact for find.
        /// </param>
        /// <returns>
        /// the primary key value of the entity or null if not in database
        /// </returns>
        private static string GetPrimaryKeyForEntity(
            OleDbConnection connection, SourceDescription description, ColumnDefinition fieldMapping, StdContact contact)
        {
            var value = FormatForDatabase(Tools.GetPropertyValue(contact, fieldMapping.Selector), fieldMapping);

            if (value == SqlDatabaseNullString)
            {
                return(null);
            }

            using (var cmd = connection.CreateCommand())
            {
                var text = string.Format(
                    CultureInfo.InvariantCulture,
                    SqlStatementSelectPk,
                    description.GetPrimaryKeyName(),
                    description.MainTable,
                    fieldMapping.Title,
                    FormatForDatabase(Tools.GetPropertyValue(contact, fieldMapping.Selector), fieldMapping));

                cmd.CommandText = text;
                var result = cmd.ExecuteScalar();
                return((result ?? string.Empty).ToString());
            }
        }
        /// <summary>
        /// Generates a SQL statement from the table and column description
        /// </summary>
        /// <param name="description">
        /// The description of the source.
        /// </param>
        /// <returns>
        /// a SQL statement string
        /// </returns>
        private static string GenerateSelectStatement(SourceDescription description)
        {
            var addComma   = false;
            var sqlBuilder = new StringBuilder(1024);

            sqlBuilder.Append("SELECT ");
            foreach (var item in description.ColumnDefinitions)
            {
                var tableLink = item as TableLink;
                if (tableLink == null)
                {
                    if (addComma)
                    {
                        sqlBuilder.Append(",");
                    }
                    else
                    {
                        addComma = true;
                    }

                    sqlBuilder.Append("[").Append(item.Title).Append("]");
                }
            }

            sqlBuilder.Append(" FROM ").Append(description.MainTable);
            return(sqlBuilder.ToString());
        }
        /// <summary>
        /// Read mapping description from file - create a sample file if it does not exist
        /// </summary>
        /// <param name="clientFolderName">
        /// The file that does contain the database mapping description
        /// </param>
        /// <returns>
        /// a deserialized mapping description
        /// </returns>
        private static SourceDescription GetDescription(string clientFolderName)
        {
            if (!File.Exists(clientFolderName))
            {
                Tools.SaveToFile(SourceDescription.GetDefaultSourceDescription(), clientFolderName);
            }

            return(Tools.LoadFromFile <SourceDescription>(clientFolderName));
        }
        /// <summary>
        /// parses the database path and checks if it's already a complete connection string or just a file name.
        ///   If the database path does not contain a "=" character it's identified as an OLEDB connection string.
        ///   In case of being just a database file name, it's interpreted as a path to a Microsoft Access database file.
        /// </summary>
        /// <param name="description">
        /// The source description containing the database path.
        /// </param>
        /// <returns>
        /// a connection string to open the database
        /// </returns>
        private static string GetConnectionString(SourceDescription description)
        {
            if (!description.DatabasePath.Contains("="))
            {
                return(string.Format(
                           CultureInfo.InvariantCulture,
                           "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Persist Security Info=False",
                           description.DatabasePath));
            }

            return(description.DatabasePath);
        }
Exemple #6
0
        /// <summary>
        /// Generates a standard sample for looking up the correct XML syntax
        /// </summary>
        /// <returns>
        /// a sample mapping definition file
        /// </returns>
        public static SourceDescription GetDefaultSourceDescription()
        {
            var returnValue = new SourceDescription
            {
                DatabasePath =
                    Path.Combine(
                        Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "AccessDatabaseSample.mdb"),
                MainTable         = "ContactInformation",
                ColumnDefinitions =
                    new List <ColumnDefinition>
                {
                    new ColumnDefinition
                    {
                        Selector     = "PersonalProfileIdentifiers.MicrosoftAccessId",
                        Title        = "Id",
                        IsPrimaryKey = true,
                        IsAutoValue  = true,
                    },
                    new ColumnDefinition {
                        Selector = "Name.FirstName", Title = "Vorname"
                    },
                    new ColumnDefinition {
                        Selector = "Name.LastName", Title = "Nachname"
                    },
                    new ColumnDefinition {
                        Selector = "DateOfBirth", Title = "Geburtstag"
                    },
                    new ColumnDefinition
                    {
                        Selector      = "PersonalProfileIdentifiers.ActiveDirectoryId",
                        Title         = "Some-ID",
                        IsLookupValue = true,
                    },
                    new ColumnDefinition
                    {
                        Selector = "BusinessAddressPrimary.Phone.DenormalizedPhoneNumber",
                        Title    = "Telefonnummer",
                    },
                    new ColumnDefinition {
                        Selector = "BusinessEmailPrimary", Title = "Mailadresse"
                    },
                    new ColumnDefinition {
                        Selector = "BusinessAddressPrimary.Room", Title = "Raum_local"
                    },
                    new ColumnDefinition
                    {
                        Selector = "PersonGender",
                        Title    = "Geschlecht",
                        TransformationToDatabase =
                            (ColumnDefinition, value) =>
                            ((Gender)value) == Gender.Male
                                                ? "'m'"
                                                : ((Gender)value) == Gender.Female ? "'f'" : "NULL",
                        TransformationFromDatabase =
                            (ColumnDefinition, value) =>
                            ((string)value) == "m"
                                                ? Gender.Male
                                                : ((string)value) == "f" ? Gender.Female : Gender.Unspecified,
                    },
                    new TableLink
                    {
                        TableName = "Firma",
                        JoinBy    =
                            new List <KeyValuePair> {
                            new KeyValuePair {
                                Key = "Firma", Value = "Id"
                            }
                        },
                        ColumnDefinitions =
                            new List <ColumnDefinition>
                        {
                            new ColumnDefinition
                            {
                                Title = "Firmenname", Selector = "BusinessCompanyName",
                            }
                        }
                    },
                    new TableLink
                    {
                        TableName = "Rolle",
                        JoinBy    =
                            new List <KeyValuePair>
                        {
                            new KeyValuePair {
                                Key = "Rolle", Value = "RolleId"
                            }
                        },
                        ColumnDefinitions =
                            new List <ColumnDefinition>
                        {
                            new ColumnDefinition
                            {
                                Title = "Rollenname", Selector = "BusinessPosition",
                            }
                        }
                    },
                }
            };

            return(returnValue);
        }