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