//Überträgt die Namen von Properties vom Repository in die mobile Datenbank. Um doppelte Einträge zu vermeiden werden zunächst Einträge in der Mobilen Datenbank
//gelöscht. Kann mit Reflexion verallgemeinert und mit updateTaxonNames kombiniert werden.
public void updateProperties(string sourceTable, string targetTable)
{
if (MappingDictionary.Mapping.ContainsKey(typeof(PropertyNames)))
MappingDictionary.Mapping[typeof(PropertyNames)] = sourceTable;
else
MappingDictionary.Mapping.Add(typeof(PropertyNames), sourceTable);
//Neue Properties holen
IList<PropertyNames> properties = new List<PropertyNames>();
IDbCommand select = _connDefinitions.CreateCommand();
IDbCommand count = _connDefinitions.CreateCommand();
select.CommandText = String.Format("SELECT * FROM {0}", _prefix + sourceTable);
count.CommandText = String.Format("SELECT COUNT(*) FROM {0}", _prefix + sourceTable);
IDataReader reader = null;
int rowCount = 1;
float progressPerRow = 1;
try
{
_connDefinitions.Open();
rowCount = (int)count.ExecuteScalar();
progressPerRow = ((float)_progressPerProperty) / 2*rowCount;
reader = select.ExecuteReader();
while (reader.Read())
{
PropertyNames prop = new PropertyNames();
prop.PropertyID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
prop.PropertyURI = reader.GetString(1);
if (!reader.IsDBNull(2))
prop.DisplayText = reader.GetString(2);
if (!reader.IsDBNull(3))
prop.HierarchyCache = reader.GetString(3);
//primary key
prop.TermID = reader.GetInt32(4);
if (!reader.IsDBNull(5))
prop.BroaderTermID = reader.GetInt32(5);
properties.Add(prop);
}
}
catch (Exception e)
{
_Log.ErrorFormat("Exception reading Properties: [{0}]", e);
//TODO
_progress.failure("Services_Definitions_Error_ReadingProperties","");
return;
}
finally
{
_connDefinitions.Close();
}
IDbTransaction trans = null;
try
{
_connMobileDefinitions.Open();
trans = _connMobileDefinitions.BeginTransaction();
//Alte Taxa löschen
IDbCommand commandMobile = _connMobileDefinitions.CreateCommand();
commandMobile.CommandText = String.Format("DELETE FROM {0}", targetTable);
commandMobile.ExecuteNonQuery();
//Taxa eintragen
foreach (PropertyNames prop in properties)
{
var sb = new StringBuilder(); //Alternativ mobileDBSerializer.Connector.Save(taxon)
sb.Append("Insert INTO ").Append(targetTable).Append(" (PropertyID,PropertyURI,DisplayText,HierarchyCache,TermID,BroaderTermID) VALUES (");
sb.Append(SqlUtil.SqlConvert(prop.PropertyID)).Append(",");
sb.Append(SqlUtil.SqlConvert(prop.PropertyURI)).Append(",").Append(SqlUtil.SqlConvert(prop.DisplayText)).Append(",").Append(SqlUtil.SqlConvert(prop.HierarchyCache)).Append(",").Append(prop.TermID).Append(",").Append(prop.BroaderTermID).Append(")");
IDbCommand insert = _connMobileDefinitions.CreateCommand();
insert.CommandText = @sb.ToString();
insert.ExecuteNonQuery();
}
trans.Commit();
}
catch (Exception ex)
{
if (trans != null)
trans.Rollback();
_Log.ErrorFormat("Exception writing Properties to mobile DB: [{0}]", ex);
//TODO
_progress.failure("Services_Definitions_Error_WritingProperties","");
return;
}
finally
{
_connMobileDefinitions.Close();
}
}
//Überträgt die Namen von Properties vom Repository in die mobile Datenbank. Um doppelte Einträge zu vermeiden werden zunächst Einträge in der Mobilen Datenbank
//gelöscht. Kann mit Reflexion verallgemeinert und mit updateTaxonNames kombiniert werden.
public void updateProperties(string sourceTable, string targetTable)
{
if (MappingDictionary.Mapping.ContainsKey(typeof(PropertyNames)))
MappingDictionary.Mapping[typeof(PropertyNames)] = sourceTable;
else
MappingDictionary.Mapping.Add(typeof(PropertyNames), sourceTable);
//Neue Properties holen
IList<PropertyNames> properties = new List<PropertyNames>();
String praefix = ConnectionsAccess.RepositoryDefinitions.Praefix;
//IRestriction r = RestrictionFactory.TypeRestriction(typeof(PropertyNames));
//properties = taxonrepSerializer.Connector.LoadList<PropertyNames>(r);//geht nicht , weil auf der Sicht keine GUID definiert ist
DbConnection connRepository = ConnectionsAccess.RepositoryDefinitions.CreateConnection();
DbCommand select = connRepository.CreateCommand();
DbCommand count = connRepository.CreateCommand();
select.CommandText = String.Format("SELECT * FROM {0}", praefix+sourceTable);
count.CommandText = String.Format("SELECT COUNT(*) FROM {0}", praefix+sourceTable);
DbDataReader reader = null;
IAdvanceProgress localProgress = progress.startExternal(progressPerProperty / 2);
int propertyCount = 0;
try
{
connRepository.Open();
propertyCount = (int)count.ExecuteScalar();
localProgress.InternalTotal = propertyCount;
reader = select.ExecuteReader();
while (reader.Read())
{
PropertyNames prop = new PropertyNames();
prop.PropertyID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
prop.PropertyURI = reader.GetString(1);
if (!reader.IsDBNull(2))
prop.DisplayText = reader.GetString(2);
if (!reader.IsDBNull(3))
prop.HierarchyCache = reader.GetString(3);
//primary key
prop.TermID = reader.GetInt32(4);
if (!reader.IsDBNull(5))
prop.BroaderTermID = reader.GetInt32(5);
properties.Add(prop);
localProgress.advance();
}
}
catch (Exception e)
{
_Log.ErrorFormat("Exception reading Properties: [{0}]",e);
return;
}
finally
{
connRepository.Close();
}
DbConnection connMobile = ConnectionsAccess.MobileTaxa.CreateConnection();
connMobile.Open();
SqlCeTransaction trans = null;
try
{
trans = (SqlCeTransaction)connMobile.BeginTransaction();
localProgress = progress.startInternal(progressPerProperty / 2, propertyCount);
//Alte Taxa löschen
DbCommand commandMobile = connMobile.CreateCommand();
commandMobile.CommandText = String.Format("DELETE FROM {0}", targetTable);
commandMobile.ExecuteNonQuery();
//Taxa eintragen
foreach (PropertyNames prop in properties)
{
var sb = new StringBuilder(); //Alternativ mobileDBSerializer.Connector.Save(taxon)
sb.Append("Insert INTO ").Append(targetTable).Append(" (PropertyID,PropertyURI,DisplayText,HierarchyCache,TermID,BroaderTermID) VALUES (");
sb.Append(SqlUtil.SqlConvert(prop.PropertyID)).Append(",");
sb.Append(SqlUtil.SqlConvert(prop.PropertyURI)).Append(",").Append(SqlUtil.SqlConvert(prop.DisplayText)).Append(",").Append(SqlUtil.SqlConvert(prop.HierarchyCache)).Append(",").Append(prop.TermID).Append(",").Append(prop.BroaderTermID).Append(")");
DbCommand insert = connMobile.CreateCommand();
insert.CommandText = @sb.ToString();
insert.ExecuteNonQuery();
localProgress.advance();
}
trans.Commit();
}
catch (Exception)
{
if (trans != null)
trans.Rollback();
return;
}
finally
{
connMobile.Close();
}
}
private void findProperties()
{
if (this.textBoxSearch.Text.Trim().Length >= 1)
{
try
{
// Ausgabe des Zeichens verhindern
PropertyNames actual = DataFunctions.Instance.CreatePropertyNames();
String input = this.textBoxSearch.Text.Trim();
input += "%";
input = input.Replace(" ", "% ");
input = input.Replace("'", "''");
this.comboBoxDisplayText.Items.Clear();
String table = this.comboBoxPropertyGroup.Text.Replace(" ", "");
table = table.Replace("(", "");
table = table.Replace(")", "");
IList<String> Ilist = DataFunctions.Instance.RetrievePropertyNames(table, input);
foreach (String propn in Ilist)
{
if (propn != null)
{
comboBoxDisplayText.Items.Add(propn);
if (propn.Equals(comboBoxDisplayText.Text))//prüfen,ob der Arbeitsname bereits eine korrekte Bezeichnung ist. Falls ja diesen durch das vollständige Objekt ersetzen
{
actual = DataFunctions.Instance.RetrievePropertyName(table, actual.DisplayText);
pn = actual;
if (actual != null)
comboBoxDisplayText.SelectedItem = propn;
}
else
pn = null;
}
}
}
catch (ConnectionCorruptedException ex)
{
throw ex;
}
}
SetOKButton();
}
private void treeViewPropertyNames_AfterSelect(object sender, TreeViewEventArgs e)
{
try
{
Cursor.Current = Cursors.WaitCursor;
pn = DataFunctions.Instance.RetrievePropertyName(nameGroup, (int)this.treeViewPropertyNames.SelectedNode.Tag);
IList<PropertyNames> children = DataFunctions.Instance.RetrievePropertyNamesChildren(nameGroup, (int)this.treeViewPropertyNames.SelectedNode.Tag);
this.treeViewPropertyNames.SelectedNode.Nodes.Clear();
foreach (PropertyNames pname in children)
{
if (pname != null)
{
TreeNode tn = new TreeNode(pname.DisplayText);
tn.Tag = pname.TermID;
this.treeViewPropertyNames.SelectedNode.Nodes.Add(tn);
}
}
this.treeViewPropertyNames.SelectedNode.Expand();
Cursor.Current = Cursors.Default;
}
catch (ConnectionCorruptedException)
{
this.treeViewPropertyNames.SelectedNode.Nodes.Clear();
Cursor.Current = Cursors.Default;
}
}
private void comboBoxPropertyGroup_SelectedIndexChanged(object sender, EventArgs e)
{
treeViewPropertyHierarchy.Nodes.Clear();
this.comboBoxDisplayText.Items.Clear();
this.comboBoxDisplayText.Text = String.Empty;
this.textBoxSearch.Text = String.Empty;
this.buttonOk.Enabled = false;
this.treeViewPropertyHierarchy.Nodes.Clear();
this.pn = null;
String table;
table = this.comboBoxPropertyGroup.Text;
table = table.Replace("(", "");
table = table.Replace(")", "");
if(MappingDictionary.Mapping.ContainsKey(typeof(PropertyNames)))
MappingDictionary.Mapping[typeof(PropertyNames)]=table;
else
MappingDictionary.Mapping.Add(typeof(PropertyNames),table);
}
private void comboBoxDisplayText_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
PropertyNames actual = DataFunctions.Instance.CreatePropertyNames();
String table = this.comboBoxPropertyGroup.Text.Replace(" ", "");
table = table.Replace("(", "");
table = table.Replace(")", "");
actual = DataFunctions.Instance.RetrievePropertyName(table, this.comboBoxDisplayText.Text);
pn = actual;
this.treeViewPropertyHierarchy.Nodes.Clear();
this.treeViewPropertyHierarchy.Nodes.Add(buildBottomUp(pn));
this.treeViewPropertyHierarchy.ExpandAll();
SetOKButton();
}
catch (ConnectionCorruptedException)
{
this.treeViewPropertyHierarchy.Nodes.Clear();
}
}
private void buttonSearch_Click(object sender, EventArgs e)
{
String table = this.comboBoxPropertyGroup.Text.Replace(" ", "");
table = table.Replace("(", "");
table = table.Replace(")", "");
PropertyNamesSearchDialog dlg = new PropertyNamesSearchDialog(table);
dlg.Location = new Point((this.Size.Width) / 2 - (dlg.Size.Width) / 2, this.Location.Y);
if (dlg.ShowDialog() == DialogResult.OK)
{
Cursor.Current = Cursors.WaitCursor;
PropertyNames tmp = dlg.PropertyName;
this.comboBoxDisplayText.Items.Clear();
if(tmp != null)
this.comboBoxDisplayText.Text = tmp.DisplayText;
pn = tmp;
this.treeViewPropertyHierarchy.Nodes.Clear();
this.treeViewPropertyHierarchy.Nodes.Add(buildBottomUp(pn));
this.treeViewPropertyHierarchy.ExpandAll();
}
Cursor.Current = Cursors.Default;
SetOKButton();
}
private TreeNode buildBottomUp(PropertyNames pn, TreeNode childnode)
{
if (pn == null || childnode == null)
return new TreeNode();
TreeNode tn = new TreeNode(pn.DisplayText);
tn.Nodes.Add(childnode);
if (pn.ParentPropertyName != null)
{
TreeNode parentNode = buildBottomUp(pn.ParentPropertyName, tn);
return parentNode;
}
return tn;
}
//Überträgt die Namen von Properties vom Repository in die mobile Datenbank. Um doppelte Einträge zu vermeiden werden zunächst Einträge in der Mobilen Datenbank
//gelöscht. Kann mit Reflexion verallgemeinert und mit updateTaxonNames kombiniert werden.
public int updateProperties(string sourceTable, string targetTable, Serializer taxonrepSerializer, Serializer namesSerializer)
{
if (MappingDictionary.Mapping.ContainsKey(typeof(PropertyNames)))
MappingDictionary.Mapping[typeof(PropertyNames)] = sourceTable;
else
MappingDictionary.Mapping.Add(typeof(PropertyNames), sourceTable);
//Neue Properties holen
IList<PropertyNames> properties = new List<PropertyNames>();
this.setProgressValue(0);
this.setActionInformation("Get PropertyNames from Source-Table: " + sourceTable);
//IRestriction r = RestrictionFactory.TypeRestriction(typeof(PropertyNames));
//properties = taxonrepSerializer.Connector.LoadList<PropertyNames>(r);//geht nicht , weil auf der Sicht keine GUID definiert ist
DbConnection connRepository = taxonrepSerializer.CreateConnection();
DbCommand com = connRepository.CreateCommand();
StringBuilder sb = new StringBuilder();
sb.Append("Select * From ").Append(sourceTable);
com.CommandText = sb.ToString();
connRepository.Open();
DbDataReader reader = null;
try
{
reader = com.ExecuteReader();
}
catch (Exception e)
{
this.setProgressInformation("Exception while updating PropertyNames from: " + sourceTable);
connRepository.Close();
return -1;
}
int index = 0;//Index kann über Serializer nicht abgefragt werden
while (reader.Read())
{
PropertyNames prop = new PropertyNames();
prop.PropertyID = reader.GetInt32(0);
if (!reader.IsDBNull(1))
prop.PropertyURI = reader.GetString(1);
if (!reader.IsDBNull(2))
prop.DisplayText = reader.GetString(2);
if (!reader.IsDBNull(3))
prop.HierarchyCache = reader.GetString(3);
//primary key
prop.TermID = reader.GetInt32(4);
if (!reader.IsDBNull(5))
prop.BroaderTermID = reader.GetInt32(5);
properties.Add(prop);
index++;
this.setProgressInformation(index + " items already read");
}
this.setProgressValue(40);
connRepository.Close();
DbConnection connMobile = namesSerializer.CreateConnection();
connMobile.Open();
SqlCeTransaction trans = (SqlCeTransaction)connMobile.BeginTransaction();
//Alte Taxa löschen
this.setActionInformation("Delete old PropertyNames from Target-Table: " + targetTable);
this.setProgressInformation("");
DbCommand commandMobile = connMobile.CreateCommand();
sb = new StringBuilder();
sb.Append("Delete From ").Append(targetTable);
commandMobile.CommandText = sb.ToString();
commandMobile.ExecuteNonQuery();
this.setProgressValue(50);
//Taxa eintragen
this.setActionInformation("Save new PropertyNames in Target-Table: " + targetTable);
index = 0;
double part = ((double)50 / (double)properties.Count);
foreach (PropertyNames prop in properties)
{
sb = new StringBuilder(); //Alternativ mobileDBSerializer.Connector.Save(taxon)
sb.Append("Insert INTO ").Append(targetTable).Append(" (PropertyID,PropertyURI,DisplayText,HierarchyCache,TermID,BroaderTermID) VALUES (");
sb.Append(SqlUtil.SqlConvert(prop.PropertyID)).Append(",");
sb.Append(SqlUtil.SqlConvert(prop.PropertyURI)).Append(",").Append(SqlUtil.SqlConvert(prop.DisplayText)).Append(",").Append(SqlUtil.SqlConvert(prop.HierarchyCache)).Append(",").Append(prop.TermID).Append(",").Append(prop.BroaderTermID).Append(")");
DbCommand insert = connMobile.CreateCommand();
insert.CommandText = @sb.ToString();
insert.ExecuteNonQuery();
index++;
// Set ProgressInformation
this.setProgressInformation(index + " of " + properties.Count + " items already saved");
int offset = Convert.ToInt32(index * part);
this.setProgressValue(50 + offset);
}
trans.Commit();
connMobile.Close();
this.setActionInformation("Update PropertyNames");
return properties.Count;
}
