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