public void Sync_CopyUpdatesFrom(string tableName, List <DatabaseColumn> columns, DbDataReader syncReader, SyncLogMessageCallback onMessage,
Queue <object> leftovers)
{
throw new NotImplementedException();
}
public void Sync_CopyUpdatesFrom(string tableName, List <DatabaseColumn> originalColumns, DbDataReader syncReader, SyncLogMessageCallback onMessage, Queue <object> leftovers)
{
List <DatabaseColumn> usableColumns = new List <DatabaseColumn>();
usableColumns.AddRange(originalColumns);
DatabaseColumn idColumn = Sync.GetIdColumn(usableColumns);
usableColumns.Remove(idColumn);
SQLiteConnection connection = GetConnectionForTable(tableName);
SQLiteCommand cmd = connection.CreateCommand();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("UPDATE {0} SET ", tableName);
for (int i = 0; i < usableColumns.Count; i++)
{
cmd.Parameters.Add(usableColumns[i].ParameterName, usableColumns[i].DbType);
sb.AppendFormat("{0} = {1}", usableColumns[i].ColumnName, usableColumns[i].ParameterName);
if (i != usableColumns.Count - 1)
{
sb.AppendFormat(", ");
}
}
int updatesDone = 0;
int nextProgress = 1;
cmd.Parameters.Add(idColumn.ParameterName, idColumn.DbType);
sb.AppendFormat(" WHERE {0} = {1}", idColumn.ColumnName, idColumn.ParameterName);
cmd.CommandText = sb.ToString();
while (syncReader.Read())
{
foreach (DatabaseColumn column in usableColumns)
{
if (column.DbType == DbType.Binary)
{
byte[] buffer = syncReader.GetByteArray(column.Ordingal);
if (buffer != null)
{
cmd.Parameters[column.ParameterName].Value = buffer;
}
}
else
{
object o = syncReader.GetValue(column.Ordingal);
cmd.Parameters[column.ParameterName].Value = o;
}
}
cmd.Parameters[idColumn.ParameterName].Value = syncReader.GetValue(idColumn.Ordingal);
int result = cmd.ExecuteNonQuery();
switch (result)
{
case 0:
leftovers.Enqueue(cmd.Parameters[idColumn.ParameterName].Value);
break;
case 1:
Console.WriteLine("update ok: {0} {1}", tableName, syncReader.GetValue(idColumn.Ordingal));
break;
default:
Console.WriteLine("update failed: {0} {1}", tableName, syncReader.GetValue(idColumn.Ordingal));
break;
}
if (updatesDone++ == nextProgress)
{
onMessage.Invoke(String.Format("{0} Zeilen in {1} aktualisiert.", nextProgress, tableName));
nextProgress *= 2;
}
}
cmd.Dispose();
syncReader.Dispose();
}
public void Sync_CopyFrom(string tableName, List <DatabaseColumn> columns, DbDataReader syncReader, SyncLogMessageCallback onMessage)
{
throw new NotImplementedException();
}
public void Sync_CopyFrom(string tableName, List <DatabaseColumn> columns, DbDataReader syncReader,
SyncLogMessageCallback onMessage)
{
tableName = tableName.MakeFullyQualifiedTableName();
SQLiteConnection connection = GetConnectionForTable(tableName);
SQLiteCommand cmd = connection.CreateCommand();
StringBuilder sb = new StringBuilder();
sb.AppendFormat("INSERT INTO {0} (", tableName);
for (int i = 0; i < columns.Count; i++)
{
sb.AppendFormat("{0}", columns[i].ColumnName);
if (i != columns.Count - 1)
{
sb.AppendFormat(", ");
}
}
sb.Append(") VALUES (");
for (int i = 0; i < columns.Count; i++)
{
sb.AppendFormat("{0}", columns[i].ParameterName);
cmd.Parameters.Add(columns[i].ParameterName, columns[i].DbType);
if (i != columns.Count - 1)
{
sb.AppendFormat(", ");
}
}
sb.Append(")");
cmd.CommandText = sb.ToString();
SQLiteTransaction transaction = connection.BeginTransaction();
DatabaseColumn idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("id"));
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("screenid"));
}
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("uid"));
}
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("scalerid"));
}
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("char_id"));
}
if (idColumn == null)
{
idColumn = columns.Find(x => x.ColumnName.ToLowerInvariant().Equals("serial"));
}
int successful = 0;
while (syncReader.Read())
{
for (int i = 0; i < columns.Count; i++)
{
DatabaseColumn currentColumn = columns[i];
if (currentColumn.DbType == DbType.Time)
{
TimeSpan copyTimespan = (TimeSpan)syncReader.GetValue(i);
DateTime copyTimespan2 = DateTime.MinValue;
copyTimespan2 += copyTimespan;
cmd.Parameters[currentColumn.ParameterName].Value = copyTimespan2;
}
else if (currentColumn.DbType == DbType.Binary)
{
cmd.Parameters[currentColumn.ParameterName].Value = DBNull.Value;
byte[] inBuffer = syncReader.GetByteArray(i);
if (inBuffer != null && inBuffer.Length > 0)
{
cmd.Parameters[currentColumn.ParameterName].Value = inBuffer;
}
}
else
{
object copyObject = syncReader.GetValue(i);
cmd.Parameters[currentColumn.ParameterName].Value = copyObject;
}
}
int result = cmd.ExecuteNonQuery();
if (result == 0)
{
throw new SyncException("insert failed");
}
if (successful++ % 100 == 0)
{
onMessage.Invoke(String.Format("{0} inserts into {1}", successful, tableName));
}
}
syncReader.Close();
syncReader.Dispose();
transaction.Commit();
transaction.Dispose();
}
