Exemplo n.º 1
0
        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();
        }
Exemplo n.º 2
0
        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");
                }
            }
        }
Exemplo n.º 3
0
        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);
        }
Exemplo n.º 4
0
        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();
        }
Exemplo n.º 5
0
        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}");
            }
        }
Exemplo n.º 6
0
        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));
        }
Exemplo n.º 7
0
        public static SqliteParameter CreateParameter(this SqliteCommand @this, string name)
        {
            var param = @this.CreateParameter();

            param.ParameterName = name;
            @this.Parameters.Add(param);
            return(param);
        }
Exemplo n.º 8
0
 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();
     }
 }
Exemplo n.º 9
0
        /// <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);
        }
Exemplo n.º 10
0
        public SqliteParameter CreateSqlParameter(SqliteCommand command)
        {
            var parameter = command.CreateParameter();

            parameter.IsNullable    = true;
            parameter.ParameterName = ParameterName;
            parameter.SqliteType    = SqliteType;
            return(parameter);
        }
Exemplo n.º 11
0
        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);
        }
Exemplo n.º 12
0
        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);
        }
Exemplo n.º 13
0
        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();
        }
Exemplo n.º 14
0
        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);
            }
        }
Exemplo n.º 15
0
        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);
            }
        }
Exemplo n.º 17
0
        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);
        }
Exemplo n.º 20
0
        /// <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);
        }
Exemplo n.º 21
0
        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;
        }
Exemplo n.º 22
0
        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);
                    }
                }
            }
        }
Exemplo n.º 23
0
        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));
        }
Exemplo n.º 24
0
        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();
        }
Exemplo n.º 25
0
        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);
        }
Exemplo n.º 26
0
        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);
            }
        }
Exemplo n.º 27
0
        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();
        }
Exemplo n.º 28
0
        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);
        }