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