void Update(T data, SqliteCommand cmd)
{
var key = _pro.GetValue(data);
cmd.CommandText = $"update [main] set Content=@p1 where key=@p0";
var p = cmd.CreateParameter();
p.ParameterName = "p0";
p.Value = key;
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "p1";
try
{
p.Value = Newtonsoft.Json.JsonConvert.SerializeObject(data);
}
catch (Exception ex)
{
_logger?.LogError("Newtonsoft.Json.JsonConvert.SerializeObject Error. key value:{0} \r\n{1}", key, ex.Message);
p.Value = ex.Message;
}
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
}
void UpdateFullName(SqliteDataReader reader, string name)
{
using (SqliteCommand updateCmd = new SqliteCommand("update LostCousins set FullName=? Where CensusYear=? and CensusCountry=? and CensusRef=? and IndID=?", InstanceConnection))
{
SqliteParameter param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.Int32;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
param = updateCmd.CreateParameter();
param.DbType = DbType.String;
updateCmd.Parameters.Add(param);
updateCmd.Prepare();
updateCmd.Parameters[0].Value = name;
updateCmd.Parameters[1].Value = reader["CensusYear"];
updateCmd.Parameters[2].Value = reader["CensusCountry"];
updateCmd.Parameters[3].Value = reader["CensusRef"];
updateCmd.Parameters[4].Value = reader["IndID"];
int rowsaffected = updateCmd.ExecuteNonQuery();
if (rowsaffected != 1)
{
Console.WriteLine("Problem updating");
}
}
}
public static bool IgnoreCustomFact(string factType)
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
bool result = false;
using (SqliteCommand cmd = new SqliteCommand("SELECT EXISTS(SELECT ignore FROM CustomFacts where FactType=?)", InstanceConnection))
{
SqliteParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
cmd.Prepare();
cmd.Parameters[0].Value = factType;
using (SqliteDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
if (reader.Read())
{
result = reader[0].ToString() == "1";
}
}
}
return(result);
}
void Add(T data, SqliteCommand cmd)
{
var key = _pro.GetValue(data);
cmd.CommandText = $"insert into [main] (key,Content,CreateTime) values (@p0,@p1,@p2)";
var p = cmd.CreateParameter();
p.ParameterName = "p0";
p.Value = key;
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "p1";
try
{
p.Value = Newtonsoft.Json.JsonConvert.SerializeObject(data);
}
catch (Exception ex)
{
_logger?.LogError("Newtonsoft.Json.JsonConvert.SerializeObject Error. key value:{0} \r\n{1}", key, ex.Message);
p.Value = ex.Message;
}
cmd.Parameters.Add(p);
p = cmd.CreateParameter();
p.ParameterName = "p2";
p.Value = DateTime.Now;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
}
public static void StoreLostCousinsFact(CensusIndividual ind, IProgress <string> outputText)
{
try
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
SqliteParameter param;
using (SqliteCommand cmd = new SqliteCommand("insert into LostCousins (CensusYear, CensusCountry, CensusRef, IndID, FullName) values(?,?,?,?,?)", InstanceConnection))
{
param = cmd.CreateParameter();
param.DbType = DbType.Int32;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
cmd.Prepare();
if (ind.CensusReference != null)
{
cmd.Parameters[0].Value = ind.CensusDate.BestYear;
cmd.Parameters[1].Value = ind.CensusCountry;
cmd.Parameters[2].Value = ind.CensusReference;
cmd.Parameters[3].Value = ind.IndividualID;
cmd.Parameters[4].Value = ind.Name;
int rowsaffected = cmd.ExecuteNonQuery();
if (rowsaffected != 1)
{
outputText.Report($"\nProblem updating record in database update affected {rowsaffected} records.");
}
else
{
FactLocation location = FactLocation.GetLocation(ind.CensusCountry);
Fact f = new Fact(ind.CensusRef, Fact.LC_FTA, ind.CensusDate, location, string.Empty, true, true);
Individual person = FamilyTree.Instance.GetIndividual(ind.IndividualID); // get the individual not the census indvidual
person?.AddFact(f);
}
}
}
}
catch (Exception e)
{
outputText.Report($"\nFailed to save Lost Cousins record in database error was: {e.Message}");
}
}
protected override ICSDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters)
{
SqliteCommand sqlCommand = ((CSSqliteCommand)Connection.CreateCommand()).Command;
if (CurrentTransaction != null)
{
sqlCommand.Transaction = ((CSSqliteTransaction)CurrentTransaction).Transaction;
}
sqlCommand.CommandType = CommandType.Text;
sqlCommand.CommandText = Regex.Replace(sqlQuery, @"@(?<name>[a-z0-9A-Z_]+)", "@${name}");
if (parameters != null && !parameters.IsEmpty)
{
foreach (CSParameter parameter in parameters)
{
IDbDataParameter dataParameter = sqlCommand.CreateParameter();
dataParameter.ParameterName = "@" + parameter.Name.Substring(1);
dataParameter.Direction = ParameterDirection.Input;
dataParameter.Value = ConvertParameter(parameter.Value);
sqlCommand.Parameters.Add(dataParameter);
}
}
return(new CSSqliteCommand(sqlCommand));
}
public static SqliteParameter CreateParameter(this SqliteCommand @this, string name)
{
var param = @this.CreateParameter();
param.ParameterName = name;
@this.Parameters.Add(param);
return(param);
}
public static void IgnoreCustomFact(string factType, bool ignore)
{
using (SqliteCommand cmd = new SqliteCommand("insert or replace into CustomFacts(FactType,Ignore) values(?,?)", InstanceConnection))
{
SqliteParameter param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.Boolean;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
cmd.Prepare();
cmd.Parameters[0].Value = factType;
cmd.Parameters[1].Value = ignore;
int rowsaffected = cmd.ExecuteNonQuery();
}
}
/// <summary>
/// A helper method to generate a parameter
/// </summary>
/// <param name="match">String to match in command</param>
/// <param name="command">Command to use</param>
/// <returns>A parameter</returns>
public static SqliteParameter Parameter(string match, ref SqliteCommand command)
{
var parameter = command.CreateParameter();
parameter.ParameterName = match;
command.Parameters.Add(parameter);
return(parameter);
}
public SqliteParameter CreateSqlParameter(SqliteCommand command)
{
var parameter = command.CreateParameter();
parameter.IsNullable = true;
parameter.ParameterName = ParameterName;
parameter.SqliteType = SqliteType;
return(parameter);
}
public static bool LostCousinsExists(CensusIndividual ind)
{
if (InstanceConnection.State != ConnectionState.Open)
{
InstanceConnection.Open();
}
bool result = false;
using (SqliteCommand cmd = new SqliteCommand("SELECT EXISTS(SELECT 1 FROM LostCousins where CensusYear=? and CensusCountry=? and CensusRef=? and IndID=?)", InstanceConnection))
{
SqliteParameter param = cmd.CreateParameter();
param.DbType = DbType.Int32;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
param = cmd.CreateParameter();
param.DbType = DbType.String;
cmd.Parameters.Add(param);
cmd.Prepare();
cmd.Parameters[0].Value = ind.CensusDate.BestYear;
cmd.Parameters[1].Value = ind.CensusCountry;
cmd.Parameters[2].Value = ind.CensusReference;
cmd.Parameters[3].Value = ind.IndividualID;
using (SqliteDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
if (reader.Read())
{
result = reader[0].ToString() == "1";
}
}
}
return(result);
}
protected override IDbDataParameter CreateParameter(IDbCommand cmd, string name, DbType dbtype, object value)
{
SqliteCommand sCmd = (SqliteCommand)cmd;
SqliteParameter parameter = (SqliteParameter)sCmd.CreateParameter();
parameter.ParameterName = ":" + name;
parameter.DbType = dbtype;
parameter.Value = value;
sCmd.Parameters.Add(parameter);
return(parameter);
}
void Delete(T data, SqliteCommand cmd)
{
var key = _pro.GetValue(data);
cmd.CommandText = $"delete from [main] where key=@p0";
var p = cmd.CreateParameter();
p.ParameterName = "p0";
p.Value = key;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();
}
public async Task <List <ScopeInfo> > GetAllClientScopesAsync(string scopeName, DbConnection connection, DbTransaction transaction)
{
List <ScopeInfo> scopes = new List <ScopeInfo>();
var commandText =
$@"SELECT sync_scope_id
, sync_scope_name
, sync_scope_schema
, sync_scope_setup
, sync_scope_version
, scope_last_sync
, scope_last_server_sync_timestamp
, scope_last_sync_timestamp
, scope_last_sync_duration
FROM {scopeTableName.Unquoted().ToString()}
WHERE sync_scope_name = @sync_scope_name";
using (var command = new SqliteCommand(commandText, (SqliteConnection)connection, (SqliteTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeName;
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
// read only the first one
while (reader.Read())
{
var scopeInfo = new ScopeInfo();
scopeInfo.Name = reader["sync_scope_name"] as String;
scopeInfo.Schema = reader["sync_scope_schema"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSet>((string)reader["sync_scope_schema"]);
scopeInfo.Setup = reader["sync_scope_setup"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSetup>((string)reader["sync_scope_setup"]);
scopeInfo.Version = reader["sync_scope_version"] as String;
scopeInfo.Id = reader.GetGuid(reader.GetOrdinal("sync_scope_id"));
scopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value
? (DateTime?)reader.GetDateTime(reader.GetOrdinal("scope_last_sync"))
: null;
scopeInfo.LastServerSyncTimestamp = reader["scope_last_server_sync_timestamp"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_server_sync_timestamp")) : 0L;
scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_sync_timestamp")) : 0L;
scopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_sync_duration")) : 0L;
scopes.Add(scopeInfo);
}
}
}
return(scopes);
}
}
private static ParameterInfo[] CreateParameters(IEntityDataReader reader, SqliteCommand command)
{
var parameters = new ParameterInfo[reader.Properties.Count];
for (var i = 0; i < reader.Properties.Count; i++)
{
var property = reader.Properties[i];
var index = reader.GetPropertyIndex(property);
var parameter = command.CreateParameter();
parameter.ParameterName = $"$p{index}";
parameters[i] = new ParameterInfo(parameter, property.IsAutoIncrement());
command.Parameters.Add(parameter);
}
return(parameters);
}
private void SetupAssemblyDbCommands()
{
if (_assemblySelect == null)
{
_assemblySelect = new SqliteCommand();
_assemblySelect.CommandText = "select id from dbkeepernet_assembly where assembly = @assembly";
var assembly = _assemblySelect.CreateParameter();
assembly.ParameterName = "@assembly";
_assemblySelect.Parameters.Add(assembly);
}
if (_assemblyInsert == null)
{
_assemblyInsert = new SqliteCommand();
_assemblyInsert.CommandText = "insert into dbkeepernet_assembly(assembly, created) values(@assembly, CURRENT_TIMESTAMP); select last_insert_rowid()";
var assembly = _assemblySelect.CreateParameter();
assembly.ParameterName = "@assembly";
_assemblyInsert.Parameters.Add(assembly);
}
}
public override DbCommand GetExistsScopeInfoCommand(DbScopeType scopeType, DbConnection connection, DbTransaction transaction)
{
if (scopeType != DbScopeType.Client)
{
return(null);
}
var commandText = $@"Select count(*) from {ScopeInfoTableName.Unquoted().ToString()} where sync_scope_id = @sync_scope_id";
var scommand = new SqliteCommand(commandText, (SqliteConnection)connection, (SqliteTransaction)transaction);
var p0 = scommand.CreateParameter();
p0.ParameterName = "@sync_scope_id";
p0.DbType = DbType.String;
scommand.Parameters.Add(p0);
return(scommand);
}
protected override ICSDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters)
{
// The next line works around a bug in the C#-Sqlite implementation
// https://code.google.com/p/csharp-sqlite/issues/detail?id=152
sqlQuery = sqlQuery.Replace("count(*)", "sum(1)");
SqliteCommand sqlCommand = ((CSSqliteCommand)Connection.CreateCommand()).Command;
if (CurrentTransaction != null)
{
sqlCommand.Transaction = ((CSSqliteTransaction)CurrentTransaction).Transaction;
}
if (sqlQuery.ToUpper().StartsWith("DELETE ") || sqlQuery.ToUpper().StartsWith("SELECT ") || sqlQuery.ToUpper().StartsWith("UPDATE ") || sqlQuery.ToUpper().StartsWith("INSERT ") || sqlQuery.ToUpper().StartsWith("CREATE "))
{
sqlCommand.CommandType = CommandType.Text;
}
else
{
sqlCommand.CommandType = CommandType.StoredProcedure;
}
sqlCommand.CommandText = Regex.Replace(sqlQuery, @"@(?<name>[a-z0-9A-Z_]+)", "@${name}");
if (parameters != null && !parameters.IsEmpty)
{
foreach (CSParameter parameter in parameters)
{
var sqliteParameter = (SqliteParameter)sqlCommand.CreateParameter();
sqliteParameter.ParameterName = "@" + parameter.Name.Substring(1);
sqliteParameter.Direction = ParameterDirection.Input;
sqliteParameter.Value = ConvertParameter(parameter.Value);
sqlCommand.Parameters.Add(sqliteParameter);
}
}
return(new CSSqliteCommand(sqlCommand));
}
public SqliteCommand GetCommand(SqliteConnection connection)
{
SqliteCommand command = connection.CreateCommand();
command.CommandText = this.query;
command.CommandType = CommandType.Text;
if (this.arguments != null)
{
int paramCounter = 1;
foreach (var argument in this.arguments)
{
var param = command.CreateParameter();
param.ParameterName = paramCounter.ToString();
param.Value = argument;
command.Parameters.Add(param);
paramCounter++;
}
}
return(command);
}
/// <summary>
/// Лезем в базу за пододящими словами.
/// </summary>
/// <param name="letters">Буквы для поиска</param>
/// <returns>Возвращаем лист строк с найденными словами.</returns>
private static List <string> SelectFromDb(Letters letters)
{
List <string> result = new List <string>();
SqliteConnection dbConnection = new SqliteConnection(connectionString);
Console.WriteLine(connectionString);
dbConnection.Open();
SqliteCommand command = dbConnection.CreateCommand();
command.CreateParameter();
command.CommandText = $"Select * FROM words WHERE word like '{letters.SA}%{letters.SB}%{letters.SC}%'";
letters.Query = command.CommandText;
Console.WriteLine(command.CommandText);
var tbl = command.ExecuteReader();
while (tbl.Read())
{
result.Add(tbl.GetString(0));
}
dbConnection.Close();
return(result);
}
static private void PrepareSqlStatement(SqliteCommand cmd, string ATablename, TTable table, StringCollection AColumnNames)
{
string stmt = "INSERT INTO " + ATablename + " (";
bool first = true;
foreach (string columnname in AColumnNames)
{
if (!first)
{
stmt += ",";
}
first = false;
stmt += columnname;
SqliteParameter param = cmd.CreateParameter();
cmd.Parameters.Add(param);
}
stmt += ") VALUES (";
first = true;
for (int count = 0; count < AColumnNames.Count; count++)
{
if (!first)
{
stmt += ",";
}
first = false;
stmt += "?";
}
stmt += ")";
cmd.CommandText = stmt;
}
public static void SQLiteBlobSizesDatabaseHasEntry(string blobSizesDbPath, string blobSha, long blobSize)
{
string connectionString = $"data source={blobSizesDbPath}";
using (SqliteConnection readConnection = new SqliteConnection(connectionString))
{
readConnection.Open();
using (SqliteCommand selectCommand = readConnection.CreateCommand())
{
SqliteParameter shaParam = selectCommand.CreateParameter();
shaParam.ParameterName = "@sha";
selectCommand.CommandText = "SELECT size FROM BlobSizes WHERE sha = (@sha)";
selectCommand.Parameters.Add(shaParam);
shaParam.Value = StringToShaBytes(blobSha);
using (SqliteDataReader reader = selectCommand.ExecuteReader())
{
reader.Read().ShouldBeTrue();
reader.GetInt64(0).ShouldEqual(blobSize);
}
}
}
}
protected override ICSDbCommand CreateCommand(string sqlQuery, CSParameterCollection parameters)
{
SqliteCommand sqlCommand = ((CSSqliteCommand)Connection.CreateCommand()).Command;
if (CurrentTransaction != null)
{
sqlCommand.Transaction = ((CSSqliteTransaction)CurrentTransaction).Transaction;
}
if (sqlQuery.ToUpper().StartsWith("DELETE ") || sqlQuery.ToUpper().StartsWith("SELECT ") || sqlQuery.ToUpper().StartsWith("UPDATE ") || sqlQuery.ToUpper().StartsWith("INSERT ") || sqlQuery.ToUpper().StartsWith("CREATE "))
{
sqlCommand.CommandType = CommandType.Text;
}
else
{
sqlCommand.CommandType = CommandType.StoredProcedure;
}
sqlCommand.CommandText = Regex.Replace(sqlQuery, @"@(?<name>[a-z0-9A-Z_]+)", "@${name}");
if (parameters != null && !parameters.IsEmpty)
{
foreach (CSParameter parameter in parameters)
{
SqliteParameter dataParameter = sqlCommand.CreateParameter();
dataParameter.ParameterName = "@" + parameter.Name.Substring(1);
dataParameter.Direction = ParameterDirection.Input;
dataParameter.Value = ConvertParameter(parameter.Value);
sqlCommand.Parameters.Add(dataParameter);
}
}
return(new CSSqliteCommand(sqlCommand));
}
public void Test(bool v3, string encoding)
{
if (!v3)
{
System.Diagnostics.Debug.WriteLine("Testing Version 2" + (encoding != null ? " with " + encoding + " encoding" : ""));
}
else
{
System.Diagnostics.Debug.WriteLine("Testing Version 3");
}
SqliteConnection dbcon = new SqliteConnection();
// the connection string is a URL that points
// to a file. If the file does not exist, a
// file is created.
// "URI=file:some/path"
var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path,
"SqliteTest" + Environment.TickCount + ".db");
string connectionString = "URI=file://" + path;
if (v3)
{
connectionString += ",Version=3";
}
if (encoding != null)
{
connectionString += ",encoding=" + encoding;
}
dbcon.ConnectionString = connectionString;
dbcon.Open();
SqliteCommand dbcmd = new SqliteCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandText =
"CREATE TABLE MONO_TEST ( " +
"NID INT, " +
"NDESC TEXT, " +
"NTIME DATETIME); " +
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(1,'One (unicode test: \u05D0)', '2006-01-01')";
System.Diagnostics.Debug.WriteLine("Create & insert modified rows = 1: " + dbcmd.ExecuteNonQuery());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(:NID,:NDESC,:NTIME)";
dbcmd.Parameters.Add(new SqliteParameter("NID", 2));
dbcmd.Parameters.Add(new SqliteParameter(":NDESC", "Two (unicode test: \u05D1)"));
dbcmd.Parameters.Add(new SqliteParameter(":NTIME", DateTime.Now));
System.Diagnostics.Debug.WriteLine("Insert modified rows with parameters = 1, 2: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(3,'Three, quoted parameter test, and next is null; :NTIME', NULL)";
System.Diagnostics.Debug.WriteLine("Insert with null modified rows and ID = 1, 3: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(4,'Four with ANSI char: ü', NULL)";
System.Diagnostics.Debug.WriteLine("Insert with ANSI char ü = 1, 4: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(?,?,?)";
dbcmd.Parameters.Clear();
IDbDataParameter param1 = dbcmd.CreateParameter();
param1.DbType = DbType.Int32;
param1.Value = 5;
dbcmd.Parameters.Add(param1);
IDbDataParameter param2 = dbcmd.CreateParameter();
param2.Value = "Using unnamed parameters";
dbcmd.Parameters.Add(param2);
IDbDataParameter param3 = dbcmd.CreateParameter();
param3.DbType = DbType.DateTime;
param3.Value = DateTime.Parse("2006-05-11 11:45:00");
dbcmd.Parameters.Add(param3);
System.Diagnostics.Debug.WriteLine("Insert with unnamed parameters = 1, 5: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"SELECT * FROM MONO_TEST";
using (var reader = dbcmd.ExecuteReader())
{
System.Diagnostics.Debug.WriteLine("read and display data...");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
System.Diagnostics.Debug.WriteLine(
" Col {0}: {1} (type: {2}, data type: {3})",
i,
reader[i] == null ? "(null)" : reader[i].ToString(),
reader[i] == null ? "(null)" : reader[i].GetType().FullName,
reader.GetDataTypeName(i));
}
}
}
dbcmd.CommandText = "SELECT NDESC FROM MONO_TEST WHERE NID=2";
System.Diagnostics.Debug.WriteLine("read and display a scalar = 'Two': " + dbcmd.ExecuteScalar());
dbcmd.CommandText = "SELECT count(*) FROM MONO_TEST";
System.Diagnostics.Debug.WriteLine("read and display a non-column scalar = 3: " + dbcmd.ExecuteScalar());
try
{
dbcmd.CommandText = "SELECT NDESC INVALID SYNTAX FROM MONO_TEST WHERE NID=2";
dbcmd.ExecuteNonQuery();
Assert.Fail("Should not reach here.");
}
catch (Exception e)
{
System.Diagnostics.Debug.WriteLine("Testing a syntax error: " + e.GetType().Name + ": " + e.Message);
}
dbcmd.Dispose();
dbcon.Close();
}
public DbCommand GetSaveScopeInfoCommand(bool exist, DbConnection connection, DbTransaction transaction)
{
var stmtText = new StringBuilder();
stmtText.AppendLine(exist
? $"Update {ScopeInfoTableName.Unquoted().ToString()} set sync_scope_name=@sync_scope_name, sync_scope_schema=@sync_scope_schema, sync_scope_setup=@sync_scope_setup, sync_scope_version=@sync_scope_version, scope_last_sync=@scope_last_sync, scope_last_server_sync_timestamp=@scope_last_server_sync_timestamp, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id;"
: $"Insert into {ScopeInfoTableName.Unquoted().ToString()} (sync_scope_name, sync_scope_schema, sync_scope_setup, sync_scope_version, scope_last_sync, scope_last_sync_duration, scope_last_server_sync_timestamp, scope_last_sync_timestamp, sync_scope_id) values (@sync_scope_name, @sync_scope_schema, @sync_scope_setup, @sync_scope_version, @scope_last_sync, @scope_last_sync_duration, @scope_last_server_sync_timestamp, @scope_last_sync_timestamp, @sync_scope_id);");
stmtText.AppendLine(@$ "SELECT sync_scope_id
, sync_scope_name
, sync_scope_schema
, sync_scope_setup
, sync_scope_version
, scope_last_sync
, scope_last_server_sync_timestamp
, scope_last_sync_timestamp
, scope_last_sync_duration
FROM {ScopeInfoTableName.Unquoted().ToString()}
WHERE sync_scope_name = @sync_scope_name");
var command = new SqliteCommand(stmtText.ToString(), (SqliteConnection)connection, (SqliteTransaction)transaction);
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.DbType = DbType.String;
p.Size = 100;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_schema";
p.DbType = DbType.String;
p.Size = -1;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_setup";
p.DbType = DbType.String;
p.Size = -1;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_version";
p.DbType = DbType.String;
p.Size = 10;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_server_sync_timestamp";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.DbType = DbType.String;
p.Size = -1;
command.Parameters.Add(p);
return(command);
}
public async Task <ScopeInfo> InsertOrUpdateClientScopeInfoAsync(ScopeInfo scopeInfo, DbConnection connection, DbTransaction transaction)
{
var commandText = $@"Select count(*) from {scopeTableName.Unquoted().ToString()} where sync_scope_id = @sync_scope_id";
bool exist;
using (var command = new SqliteCommand(commandText, (SqliteConnection)connection, (SqliteTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
exist = ((long)await command.ExecuteScalarAsync().ConfigureAwait(false)) > 0;
}
string stmtText = exist
? $"Update {scopeTableName.Unquoted().ToString()} set sync_scope_name=@sync_scope_name, sync_scope_schema=@sync_scope_schema, sync_scope_setup=@sync_scope_setup, sync_scope_version=@sync_scope_version, scope_last_sync=@scope_last_sync, scope_last_server_sync_timestamp=@scope_last_server_sync_timestamp, scope_last_sync_timestamp=@scope_last_sync_timestamp, scope_last_sync_duration=@scope_last_sync_duration where sync_scope_id=@sync_scope_id"
: $"Insert into {scopeTableName.Unquoted().ToString()} (sync_scope_name, sync_scope_schema, sync_scope_setup, sync_scope_version, scope_last_sync, scope_last_sync_duration, scope_last_server_sync_timestamp, scope_last_sync_timestamp, sync_scope_id) values (@sync_scope_name, @sync_scope_schema, @sync_scope_setup, @sync_scope_version, @scope_last_sync, @scope_last_sync_duration, @scope_last_server_sync_timestamp, @scope_last_sync_timestamp, @sync_scope_id)";
using (var command = new SqliteCommand(stmtText, (SqliteConnection)connection, (SqliteTransaction)transaction))
{
var p = command.CreateParameter();
p.ParameterName = "@sync_scope_name";
p.Value = scopeInfo.Name;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_schema";
p.Value = scopeInfo.Schema == null ? DBNull.Value : (object)JsonConvert.SerializeObject(scopeInfo.Schema);
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_setup";
p.Value = scopeInfo.Setup == null ? DBNull.Value : (object)JsonConvert.SerializeObject(scopeInfo.Setup);
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_version";
p.Value = string.IsNullOrEmpty(scopeInfo.Version) ? DBNull.Value : (object)scopeInfo.Version;
p.DbType = DbType.String;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync";
p.Value = scopeInfo.LastSync.HasValue ? (object)scopeInfo.LastSync.Value : DBNull.Value;
p.DbType = DbType.DateTime;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_server_sync_timestamp";
p.Value = scopeInfo.LastServerSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_timestamp";
p.Value = scopeInfo.LastSyncTimestamp;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@scope_last_sync_duration";
p.Value = scopeInfo.LastSyncDuration;
p.DbType = DbType.Int64;
command.Parameters.Add(p);
p = command.CreateParameter();
p.ParameterName = "@sync_scope_id";
p.Value = scopeInfo.Id.ToString();
p.DbType = DbType.String;
command.Parameters.Add(p);
using (DbDataReader reader = await command.ExecuteReaderAsync().ConfigureAwait(false))
{
if (reader.HasRows)
{
while (reader.Read())
{
scopeInfo.Name = reader["sync_scope_name"] as string;
scopeInfo.Schema = reader["sync_scope_schema"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSet>((string)reader["sync_scope_schema"]);
scopeInfo.Setup = reader["sync_scope_setup"] == DBNull.Value ? null : JsonConvert.DeserializeObject <SyncSetup>((string)reader["sync_scope_setup"]);
scopeInfo.Version = reader["sync_scope_version"] as string;
scopeInfo.Id = reader.GetGuid(reader.GetOrdinal("sync_scope_id"));
scopeInfo.LastSync = reader["scope_last_sync"] != DBNull.Value
? (DateTime?)reader.GetDateTime(reader.GetOrdinal("scope_last_sync"))
: null;
scopeInfo.LastSyncTimestamp = reader["scope_last_sync_timestamp"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_sync_timestamp")) : 0L;
scopeInfo.LastServerSyncTimestamp = reader["scope_last_server_sync_timestamp"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_server_sync_timestamp")) : 0L;
scopeInfo.LastSyncDuration = reader["scope_last_sync_duration"] != DBNull.Value ? reader.GetInt64(reader.GetOrdinal("scope_last_sync_duration")) : 0L;
}
}
}
return(scopeInfo);
}
}
static void Test(bool v3, string encoding)
{
if (!v3)
{
Console.WriteLine("Testing Version 2" + (encoding != null ? " with " + encoding + " encoding" : ""));
}
else
{
Console.WriteLine("Testing Version 3");
}
System.IO.File.Delete("SqliteTest.db");
SqliteConnection dbcon = new SqliteConnection();
// the connection string is a URL that points
// to a file. If the file does not exist, a
// file is created.
// "URI=file:some/path"
string connectionString =
"URI=file:SqliteTest.db";
if (v3)
{
connectionString += ",Version=3";
}
if (encoding != null)
{
connectionString += ",encoding=" + encoding;
}
dbcon.ConnectionString = connectionString;
dbcon.Open();
SqliteCommand dbcmd = new SqliteCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandText =
"CREATE TABLE MONO_TEST ( " +
"NID INT, " +
"NDESC TEXT, " +
"NTIME DATETIME); " +
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(1,'One (unicode test: \u05D0)', '2006-01-01')";
Console.WriteLine("Create & insert modified rows = 1: " + dbcmd.ExecuteNonQuery());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(:NID,:NDESC,:NTIME)";
dbcmd.Parameters.Add(new SqliteParameter("NID", 2));
dbcmd.Parameters.Add(new SqliteParameter(":NDESC", "Two (unicode test: \u05D1)"));
dbcmd.Parameters.Add(new SqliteParameter(":NTIME", DateTime.Now));
Console.WriteLine("Insert modified rows with parameters = 1, 2: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(3,'Three, quoted parameter test, and next is null; :NTIME', NULL)";
Console.WriteLine("Insert with null modified rows and ID = 1, 3: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(4,'Four with ANSI char: ü', NULL)";
Console.WriteLine("Insert with ANSI char ü = 1, 4: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(?,?,?)";
dbcmd.Parameters.Clear();
IDbDataParameter param1 = dbcmd.CreateParameter();
param1.DbType = DbType.DateTime;
param1.Value = 5;
dbcmd.Parameters.Add(param1);
IDbDataParameter param2 = dbcmd.CreateParameter();
param2.Value = "Using unnamed parameters";
dbcmd.Parameters.Add(param2);
IDbDataParameter param3 = dbcmd.CreateParameter();
param3.DbType = DbType.DateTime;
param3.Value = DateTime.Parse("2006-05-11 11:45:00");
dbcmd.Parameters.Add(param3);
Console.WriteLine("Insert with unnamed parameters = 1, 5: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"SELECT * FROM MONO_TEST";
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
Console.WriteLine("read and display data...");
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine(" Col {0}: {1} (type: {2}, data type: {3})",
i, reader[i] == null ? "(null)" : reader[i].ToString(), reader[i] == null ? "(null)" : reader[i].GetType().FullName, reader.GetDataTypeName(i));
}
}
dbcmd.CommandText = "SELECT NDESC FROM MONO_TEST WHERE NID=2";
Console.WriteLine("read and display a scalar = 'Two': " + dbcmd.ExecuteScalar());
dbcmd.CommandText = "SELECT count(*) FROM MONO_TEST";
Console.WriteLine("read and display a non-column scalar = 3: " + dbcmd.ExecuteScalar());
Console.WriteLine("read and display data using DataAdapter/DataSet...");
SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
foreach (DataTable myTable in dataset.Tables)
{
foreach (DataRow myRow in myTable.Rows)
{
foreach (DataColumn myColumn in myTable.Columns)
{
Console.WriteLine(" " + myRow[myColumn]);
}
}
}
/*Console.WriteLine("read and display data using DataAdapter/DataTable...");
* DataTable dt = new DataTable();
* adapter.Fill(dt);
* DataView dv = new DataView(dt);
* foreach (DataRowView myRow in dv) {
* foreach (DataColumn myColumn in myRow.Row.Table.Columns) {
* Console.WriteLine(" " + myRow[myColumn.ColumnName]);
* }
* }*/
try {
dbcmd.CommandText = "SELECT NDESC INVALID SYNTAX FROM MONO_TEST WHERE NID=2";
dbcmd.ExecuteNonQuery();
Console.WriteLine("Should not reach here.");
} catch (Exception e) {
Console.WriteLine("Testing a syntax error: " + e.GetType().Name + ": " + e.Message);
}
/*try {
* dbcmd.CommandText = "SELECT 0/0 FROM MONO_TEST WHERE NID=2";
* Console.WriteLine("Should not reach here: " + dbcmd.ExecuteScalar());
* } catch (Exception e) {
* Console.WriteLine("Testing an execution error: " + e.GetType().Name + ": " + e.Message);
* }*/
dataset.Dispose();
adapter.Dispose();
reader.Close();
dbcmd.Dispose();
dbcon.Close();
}
private static bool Import(Database database, string filename)
{
try
{
using (Utils.ProgressBlock allprog = new ProgressBlock("ImportGSAKDatabase", "Importing", 6, 0))
{
System.Collections.Hashtable logTypes = new System.Collections.Hashtable();
String connect = String.Format("data source=file:{0}", filename);
using (SqliteConnection dbcon = new SqliteConnection(connect))
{
//System.Diagnostics.Debugger.Break();
logTypes.Add("Found it", 2);
logTypes.Add("Didn't find it", 3);
logTypes.Add("Write note", 4);
logTypes.Add("Archive", 5);
logTypes.Add("Needs Archived", 7);
logTypes.Add("Will Attend", 9);
logTypes.Add("Attended", 10);
logTypes.Add("Webcam Photo Taken", 11);
logTypes.Add("Unarchive", 12);
logTypes.Add("Temporarily Disable Listing", 22);
logTypes.Add("Temporarily Disable", 22);
logTypes.Add("Enable Listing", 23);
logTypes.Add("Enable", 23);
logTypes.Add("Publish Listing", 24);
logTypes.Add("Publish", 24);
logTypes.Add("Retract Listing", 25);
logTypes.Add("Retract", 25);
logTypes.Add("Needs Maintenance", 45);
logTypes.Add("Owner Maintenance", 46);
logTypes.Add("Update Coordinates", 47);
logTypes.Add("Post Reviewer Note", 68);
logTypes.Add("Announcement", 74);
dbcon.Open();
SqliteCommand lookup = new SqliteCommand("select aId, aInc from attributes where aCode = @Code", dbcon);
lookup.CommandType = CommandType.Text;
DbParameter par = lookup.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = "@Code";
lookup.Parameters.Add(par);
lookup.Prepare();
SqliteCommand import = new SqliteCommand("select count(1) from caches", dbcon);
import.CommandType = CommandType.Text;
int index = 0;
int gcCount = (int)(long)import.ExecuteScalar();
if (gcCount > 0)
{
DateTime progShow = DateTime.Now.AddSeconds(1);
using (Utils.ProgressBlock prog = new ProgressBlock("ImportingGeocaches", 1, 0))
{
bool isPremiumAvailable = false;
bool isFavPointAvailable = false;
bool isGCNoteAvailable = false;
try
{
import.CommandText = "select IsPremium from Caches limit 1";
using (SqliteDataReader checkdr = import.ExecuteReader())
{
isPremiumAvailable = true;
}
}
catch
{
}
try
{
import.CommandText = "select FavPoints from Caches limit 1";
using (SqliteDataReader checkdr = import.ExecuteReader())
{
isFavPointAvailable = true;
}
}
catch
{
}
try
{
import.CommandText = "select gcnote from Caches limit 1";
using (SqliteDataReader checkdr = import.ExecuteReader())
{
isGCNoteAvailable = true;
}
}
catch
{
}
import.CommandText = "select caches.Code, Name, LastGPXDate, PlacedDate, Latitude, Longitude, Status, " +
"Archived, Country, State, County, CacheType, PlacedBy, OwnerName, OwnerId, Container, Terrain, Difficulty, ShortHTM" +
", LongHTM, " +
string.Format("{0}", isPremiumAvailable ? "IsPremium, " : "") +
" HasCorrected, LatOriginal, LonOriginal, UserFlag, Found, " +
string.Format("{0}", isFavPointAvailable ? "FavPoints, " : "") +
" ShortDescription, LongDescription, Hints, Url, UserNote" +
string.Format("{0}", isGCNoteAvailable ? ", gcnote" : "") +
" from caches" +
" inner join cachememo on cachememo.code = caches.code";
SqliteDataReader dr = import.ExecuteReader();
while (dr.Read())
{
GeocacheData gc = new GeocacheData();
int cacheType;
try
{
cacheType = getCacheType(((String)dr["CacheType"])[0]);
}
catch (ArgumentOutOfRangeException)
{
continue;
}
int container = getContainer(((String)dr["Container"])[0]);
gc.Code = (string)dr["code"];
gc.Name = (string)dr["name"];
if (string.IsNullOrEmpty((string)dr["LastGPXDate"]))
{
gc.DataFromDate = DateTime.Now.Date;
}
else
{
gc.DataFromDate = DateTime.Parse((string)dr["LastGPXDate"]);
}
gc.Available = ((String)dr["Status"]).Equals("A");
gc.Archived = (int)dr["archived"] != 0;
gc.Country = (string)dr["country"];
gc.State = (string)dr["state"];
gc.Municipality = (string)dr["county"];
gc.GeocacheType = Utils.DataAccess.GetGeocacheType(cacheType);
gc.PlacedBy = (string)dr["placedby"];
gc.Owner = (string)dr["OwnerName"];
gc.OwnerId = dr["ownerid"].GetType() == typeof(DBNull) ? "" : dr["ownerid"].ToString();
gc.Container = Utils.DataAccess.GetGeocacheContainer(container);
gc.Terrain = (double)dr["terrain"];
gc.Difficulty = (double)dr["difficulty"];
gc.ShortDescription = (string)dr["ShortDescription"];
gc.ShortDescriptionInHtml = (int)dr["ShortHTM"] != 0;
gc.LongDescription = (string)dr["LongDescription"];
gc.LongDescriptionInHtml = (int)dr["LongHTM"] != 0;
gc.EncodedHints = (string)dr["Hints"];
gc.Url = (string)dr["url"];
if (isPremiumAvailable)
{
gc.MemberOnly = (int)dr["IsPremium"] != 0;
}
else
{
gc.MemberOnly = false;
}
bool customCoords = (int)dr["HasCorrected"] != 0;
if (customCoords)
{
gc.CustomLat = Utils.Conversion.StringToDouble(dr["Latitude"] as String);
gc.CustomLon = Utils.Conversion.StringToDouble(dr["Longitude"] as String);
gc.Lat = Utils.Conversion.StringToDouble(dr["LatOriginal"] as string);
gc.Lon = Utils.Conversion.StringToDouble(dr["LonOriginal"] as string);
}
else
{
gc.Lat = Utils.Conversion.StringToDouble(dr["Latitude"] as string);
gc.Lon = Utils.Conversion.StringToDouble(dr["Longitude"] as string);
}
par.Value = gc.Code;
DbDataReader attrs = lookup.ExecuteReader();
List <int> attrList = new List <int>();
while (attrs.Read())
{
int attr = (int)(int)attrs["aId"];
if (attrs["aInc"].ToString() == "0")
{
attr *= -1;
}
attrList.Add(attr);
}
attrs.Close();
gc.AttributeIds = attrList;
gc.Notes = (string)dr["UserNote"];
gc.PublishedTime = DateTime.Parse((string)dr["PlacedDate"]);
if (isGCNoteAvailable)
{
gc.PersonalNote = (string)dr["gcnote"];
}
else
{
gc.PersonalNote = "";
}
gc.Flagged = (int)dr["UserFlag"] != 0;
gc.Found = (int)dr["Found"] != 0;
if (isFavPointAvailable)
{
gc.Favorites = (int)(int)dr["FavPoints"];
}
else
{
gc.Favorites = 0;
}
DataAccess.AddGeocache(database, gc);
index++;
if (DateTime.Now >= progShow)
{
prog.Update("ImportingGeocaches", gcCount, index);
progShow = DateTime.Now.AddSeconds(1);
}
}
dr.Close();
}
}
allprog.Update("Importing", 5, 1);
import.CommandText = "select count(1) from logs";
int logCount = (int)(long)import.ExecuteScalar();
if (logCount > 0)
{
DateTime progShow = DateTime.Now.AddSeconds(1);
using (Utils.ProgressBlock progress = new Utils.ProgressBlock("ImportingLogs", logCount, 0))
{
index = 0;
import.CommandText = "select l.lLogId, l.lParent, lDate, lTime, lBy, lownerid, lEncoded, lType, lText " +
" from logs l" +
" inner join logmemo m on m.lLogId = l.lLogId and m.lParent = l.lParent";
DbDataReader dr = import.ExecuteReader();
while (dr.Read())
{
Core.Data.LogData lg = new Core.Data.LogData();
String type = (String)dr["lType"];
int logType = (int)logTypes[type];
//id text, gccode text, tbcode text, date text, finder text, finderid text, logtext text, encoded integer, datafromdate text, logtype integer
lg.ID = dr["lLogiD"].ToString();
lg.GeocacheCode = (string)dr["lParent"];
lg.TBCode = "";
lg.Date = (DateTime)dr["lDate"];
lg.Finder = (string)dr["lBy"];
lg.FinderId = dr["lownerid"].ToString();
lg.Text = (string)dr["lText"];
lg.Encoded = (long)dr["lEncoded"] != 0;
lg.DataFromDate = DateTime.Now;
lg.LogType = Utils.DataAccess.GetLogType(logType);
DataAccess.AddLog(database, lg);
index++;
if (DateTime.Now >= progShow)
{
progress.Update("ImportingLogs", logCount, index);
progShow = DateTime.Now.AddSeconds(1);
}
}
dr.Close();
}
}
allprog.Update("Importing", 5, 2);
import.CommandText = "select count(1) from logimages";
int logimgCount = 0;
try
{
logimgCount = (int)(long)import.ExecuteScalar();
}
catch
{
//table does not exists
}
if (logimgCount > 0)
{
DateTime progShow = DateTime.Now.AddSeconds(1);
using (Utils.ProgressBlock progress = new Utils.ProgressBlock("ImportingLogImages", logimgCount, 0))
{
index = 0;
import.CommandText = "select iCode, iLogId, iImage, iName from logimages";
DbDataReader dr = import.ExecuteReader();
while (dr.Read())
{
Core.Data.LogImageData lg = new Core.Data.LogImageData();
lg.ID = (string)dr["iCode"];
lg.LogId = dr["iLogID"].ToString();
lg.Url = (string)dr["iImage"];
lg.Name = (string)dr["iName"];
DataAccess.AddLogImage(database, lg);
index++;
if (DateTime.Now >= progShow)
{
progress.Update("ImportingLogImages", logimgCount, index);
progShow = DateTime.Now.AddSeconds(1);
}
}
dr.Close();
}
}
allprog.Update("Importing", 5, 3);
//id text, code text, geocachecode text, name text, datafromdate text, comment text, description text, url text, urlname text, wptype integer, lat real, lon real, time text
import.CommandText = "select count(1) from waypoints";
int wptCount = (int)(long)import.ExecuteScalar();
if (wptCount > 0)
{
DateTime progShow = DateTime.Now.AddSeconds(1);
using (Utils.ProgressBlock progress = new Utils.ProgressBlock("ImportingWaypoints", wptCount, 0))
{
index = 0;
import.CommandText = "select w.cParent, w.cCode, cName, cDate, cType, cLat, cLon," +
" cComment, cUrl" +
" from waypoints w" +
" inner join wayMemo m on w.cParent = m.cParent and w.cCode=m.cCode";
DbDataReader dr = import.ExecuteReader();
while (dr.Read())
{
Core.Data.WaypointData wp = new Core.Data.WaypointData();
int wpType = getWPType(((string)dr["cType"])[0]);
wp.ID = (string)dr["cCode"];
wp.Code = (string)dr["cCode"];
wp.Url = (string)dr["cUrl"];
//wp.UrlName = (string)dr["urlname"];
wp.Name = (string)dr["cName"];
wp.DataFromDate = (DateTime)dr["cDate"];
wp.Comment = (string)dr["cComment"];
wp.GeocacheCode = (string)dr["cParent"];
//wp.Description = (string)dr["description"];
wp.WPType = Utils.DataAccess.GetWaypointType(wpType);
double lat = Utils.Conversion.StringToDouble(dr["clat"] as string);
double lon = Utils.Conversion.StringToDouble(dr["clon"] as string);
if (Math.Abs(lat) < 0.00001)
{
wp.Lat = null;
}
else
{
wp.Lat = lat;
}
if (Math.Abs(lon) < 0.00001)
{
wp.Lon = null;
}
else
{
wp.Lon = lon;
}
wp.Time = (DateTime)dr["cDate"];
wp.Description = wp.WPType.Name;
wp.UrlName = wp.WPType.Name;
DataAccess.AddWaypoint(database, wp);
index++;
if (DateTime.Now >= progShow)
{
progress.Update("ImportingWaypoints", wptCount, index);
progShow = DateTime.Now.AddSeconds(1);
}
}
dr.Close();
}
}
allprog.Update("Importing", 5, 4);
try
{
//import corrected if table exists
import.CommandText = "select kCode, kAfterLat, kAfterLon from Corrected";
DbDataReader dr = import.ExecuteReader();
while (dr.Read())
{
string gcCode = dr["kCode"] as string ?? "";
Core.Data.Geocache gc = database.GeocacheCollection.GetGeocache(gcCode);
if (gc != null)
{
object oLat = dr["kAfterLat"];
object oLon = dr["kAfterLon"];
if (oLat != null && oLat.GetType() != typeof(DBNull) &&
oLon != null && oLon.GetType() != typeof(DBNull))
{
string sLat = oLat as string;
string sLon = oLon as string;
if (sLat.Length > 0 && sLon.Length > 0)
{
gc.CustomLat = Utils.Conversion.StringToDouble(sLat);
gc.CustomLon = Utils.Conversion.StringToDouble(sLon);
}
}
}
}
}
catch
{
}
allprog.Update("Importing", 5, 5);
}
}
}
catch (Exception e)
{
Core.ApplicationData.Instance.Logger.AddLog(new Importer(), e);
}
return(true);
}