Example #1
0
        /// <summary>
        /// Connects to a SQLite3-Database
        /// </summary>
        /// <param name="path">Path of the Database</param>
        public Database(string path)
        {
            connection = new SQLiteConnection();
            connection.ConnectionString = "Data Source=" + path + ";Version=3;New=True;Compress=True";
            connection.Open();

            // Create required databases
            createTable("pictures", "path VARCHAR(255) PRIMARY KEY NOT NULL, up INT NOT NULL DEFAULT 0, down INT NOT NULL DEFAULT 0");

            // Prepare prepared statements, duh.
            qRandomPicture = new SQLiteCommand(connection);
            qRandomPicture.CommandText = "SELECT path FROM pictures WHERE (up - down) > (SELECT AVG(up-down)-2 FROM pictures) ORDER BY RANDOM() LIMIT 1";

            qInsertPicture = new SQLiteCommand(connection);
            qInsertPicture.CommandText = "INSERT OR IGNORE INTO pictures (path) VALUES (@path)";
            qInsertPath = qInsertPicture.CreateParameter();
            qInsertPath.ParameterName = "@path";
            qInsertPicture.Parameters.Add(qInsertPath);

            qVoteUp = new SQLiteCommand(connection);
            qVoteUp.CommandText = "UPDATE pictures SET up = up + 1 WHERE path = @path";
            qVoteUpPath = qVoteUp.CreateParameter();
            qVoteUpPath.ParameterName = "@path";
            qVoteUp.Parameters.Add(qVoteUpPath);

            qVoteDown = new SQLiteCommand(connection);
            qVoteDown.CommandText = "UPDATE pictures SET down = down + 1 WHERE path = @path";
            qVoteDownPath = qVoteDown.CreateParameter();
            qVoteDownPath.ParameterName = "@path";
            qVoteDown.Parameters.Add(qVoteDownPath);
        }
    public static int Insert(SQLiteConnection connection, String tableName, Dictionary<String, String> data)
    {
        String columns = "";
        String values = "";
        foreach (KeyValuePair<String, String> val in data)
        {
            columns += String.Format(" {0},", val.Key.ToString());
            values += String.Format(" ?,");
        }
        columns = columns.Substring(0, columns.Length - 1);
        values = values.Substring(0, values.Length - 1);
        try
        {
            string sql = String.Format("insert into {0}({1}) values({2});", tableName, columns, values);
            SQLiteCommand dbCommand = new SQLiteCommand(connection);

            foreach (KeyValuePair<String, String> val in data)
            {
                SQLiteParameter para = dbCommand.CreateParameter();
                para.Value = val.Value;
                dbCommand.Parameters.Add(para);
            }

            dbCommand.CommandText = sql;
            int rowsUpdated = dbCommand.ExecuteNonQuery();

            dbCommand.CommandText = "SELECT last_insert_rowid();";
            return int.Parse(dbCommand.ExecuteScalar().ToString()); // returned index
        }
        catch (Exception fail)
        {
            MessageBox.Show(fail.Message);
        }
        return 0;
    }
 internal override bool IsTablePresent(string tableName)
 {
     SQLiteCommand command = new SQLiteCommand((SQLiteConnection)Connection);
     command.CommandText = "SELECT * FROM sqlite_master WHERE type='table' AND [email protected]";
     SQLiteParameter nameParametr = command.CreateParameter();
     nameParametr.ParameterName = "@name";
     nameParametr.Value = tableName;
     command.Parameters.Add("@name", System.Data.DbType.String);
     command.Parameters["@name"].Value = tableName;
     bool result = command.ExecuteScalar() != null;
     command.Dispose();
     return result;
 }
Example #4
0
        public void Import()
        {
            //see if we hava a database, if not, create one
              if (!File.Exists(_dataFile))
              {
            _InitDatabase();
              }

              var raw = File.ReadLines(_file).GetEnumerator();
              //prime the data, so the first pull gets a value
              raw.MoveNext();

              var count = 0;
              var data = _GetData(raw);

              using (var conn = new SQLiteConnection(_connString))
              {
            conn.Open();

            do
            {
              using (var cmd = new SQLiteCommand(conn))
              {
            using (var trans = conn.BeginTransaction())
            {
              cmd.CommandText = "insert into domains (name) values(?)";
              var param = cmd.CreateParameter();
              cmd.Parameters.Add(param);

              foreach (var itm in data)
              {
                param.Value = itm;
                cmd.ExecuteNonQuery();
              }

              trans.Commit();
            }
              }

              count += data.Count();
              Console.WriteLine(string.Format("Imported {0:#,#} items...", count));

              data = _GetData(raw);
            } while (data.Count() > 0);

            conn.Close();
              }
        }
 public void InsertMetadata(String configurationFile)
 {
     SQLiteConnection conn = new SQLiteConnection(String.Format("Data source={0}", fileName));
     conn.Open();
     using (conn)
     {
         using (SQLiteCommand cmd = new SQLiteCommand("INSERT INTO ___Metadata([Data]) VALUES(@Data)", conn))
         {
             SQLiteParameter p = cmd.CreateParameter();
             p.ParameterName = "@Data";
             p.DbType = System.Data.DbType.Xml;
             p.Value = System.IO.File.ReadAllText(configurationFile);
             cmd.Parameters.Add(p);
             cmd.ExecuteNonQuery();
         }
     }
 }
        /// <summary>
        /// Reads all rows out of the local flat-files for the specified day and table
        /// </summary>
        /// <param name="day">The day to grab rows from.</param>
        /// <param name="tableName">Name of the table to grab data from.</param>
        /// <param name="getDataAfterTime">Determines if data should be pulled from all rows before the timestamp in the day parameter or after</param>
        /// <returns>Returns a DataTable that contains all rows from for a specified day starting at the specified time</returns>
        public static DataTable ReadDay(DateTime day, TableName tableName, bool getDataAfterTime)
        {
            string path = Path.Combine(Directory.GetCurrentDirectory(), string.Format("analysis {0}{1}{2}.db", day.Year, day.Month.ToString().PadLeft(2, '0'), day.Day.ToString().PadLeft(2, '0')));
            if (!File.Exists(path)) {
                return null;
            }
            DataTable dataTable = new DataTable();
            using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + path + ";Version=3")) {
                string format = (((day.Hour == 0) && (day.Minute == 0)) && (day.Second == 0)) ? "Select * From {0}" : ("Select * From {0} Where CreateTime " + (getDataAfterTime ? "> " : "< ") + "@cDate"); //day.ToString("G"));
                using (SQLiteCommand command = new SQLiteCommand(string.Format(format, tableName.ToString()), connection)) {
                    var param = command.CreateParameter();
                    param.Value = day.ToString("G");
                    param.ParameterName = "@cDate";
                    command.Parameters.Add(param);

                    using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(command)) {
                        adapter.Fill(dataTable);
                    }
                }
            }
            return dataTable;
        }
Example #7
0
        //Takes SQLCOMMAND as parameter and run the command aginst the Database.
        public static void insetToDatabase( Message mail)
        {
            dbConnection.Open();
             //SQLiteCommand command = new SQLiteCommand(SQLCOMMAND, dbConnection);
             //command.ExecuteNonQuery();
             //dbConnection.Close();

             SQLiteCommand cmd = new SQLiteCommand(dbConnection);
             SQLiteParameter param = cmd.CreateParameter();

             string SQLCOMMAND = @"INSERT INTO MailList (MessageId, Receiver, Sender, Date, Subject, Message) VALUES ('" + mail.Headers.MessageId + "','" + mail.Headers.To + "','" + mail.Headers.From + "','" + mail.Headers.DateSent + "','" + mail.Headers.Subject + "', @param)";

             cmd.CommandText = SQLCOMMAND;
             param.ParameterName = "param";
             MessagePart html = mail.FindFirstHtmlVersion();
             MessagePart plainText = mail.FindFirstPlainTextVersion();
            StringBuilder builder = new StringBuilder();

            if (html != null)
                param.Value = builder.Append(html.GetBodyAsText()).ToString();

            else
                param.Value = plainText.GetBodyAsText();

             cmd.Parameters.Add(param);
             try
             {
                 cmd.ExecuteNonQuery();
             }
             catch (Exception)
             {

             }
                 //always close the connection!!
             finally { dbConnection.Close(); }
        }
Example #8
0
        private void WriteBlobRow(UUID uuid, MemoryStream ms)
        {
            const string INSERT_CMD = "INSERT OR REPLACE INTO StateData(item_id, state_data) VALUES(@itemId, @stateBlob)";
            using (SQLiteCommand cmd = new SQLiteCommand(INSERT_CMD, _connection))
            {
                SQLiteParameter itemIdParam = cmd.CreateParameter();
                itemIdParam.ParameterName = "@itemId";
                itemIdParam.Value = uuid.Guid.ToString("N");

                SQLiteParameter stateBlobParam = cmd.CreateParameter();
                stateBlobParam.ParameterName = "@stateBlob";
                stateBlobParam.Value = ms.ToArray();

                cmd.Parameters.Add(itemIdParam);
                cmd.Parameters.Add(stateBlobParam);

                cmd.ExecuteNonQuery();
            }
        }
Example #9
0
        /// <summary>
        /// Only called during startup, loads the state data for the given item id off the disk
        /// </summary>
        /// <param name="itemId"></param>
        /// <returns></returns>
        public VM.RuntimeState LoadStateFromDisk(UUID itemId)
        {
            try
            {
                lock (_connection)
                {
                    const string FIND_CMD = "SELECT state_data FROM StateData WHERE item_id = @itemId";
                    using (SQLiteCommand cmd = new SQLiteCommand(FIND_CMD, _connection))
                    {
                        SQLiteParameter itemIdParam = cmd.CreateParameter();
                        itemIdParam.ParameterName = "@itemId";
                        itemIdParam.Value = itemId.Guid.ToString("N");

                        cmd.Parameters.Add(itemIdParam);

                        using (SQLiteDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                //int bufSz = reader.GetBytes(0, 0, null, 0, 0);
                                byte[] buffer = (byte[])reader[0];//reader.GetBytes(

                                using (MemoryStream stateStream = new MemoryStream(buffer))
                                {
                                    SerializedRuntimeState runstate = ProtoBuf.Serializer.Deserialize<SerializedRuntimeState>(stateStream);
                                    if (runstate != null)
                                    {
                                        return runstate.ToRuntimeState();
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                _log.ErrorFormat("[Phlox]: Could not load state for {0} script will be reset. Error was {1}",
                    itemId, e);
            }

            return null;
        }
Example #10
0
 /// <summary>
 /// PreparedStatementを設定する
 /// </summary>
 /// <param name="cmd">パラメータを指定するオブジェクト</param>
 /// <param name="param">パラメータ</param>
 private void createParam(SQLiteCommand cmd,Object[] param)
 {
     if (param == null || cmd == null) { return; }
     foreach (Object p in param)
     {
         SQLiteParameter sp = cmd.CreateParameter();
         if (p is String)
         {
             sp.DbType = System.Data.DbType.String;
             sp.Value = (String)p;
         }
         else if (p is Int16)
         {
             sp.DbType = System.Data.DbType.Int16;
             sp.Value = (int)p;
         }
         else if (p is Int32)
         {
             sp.DbType = System.Data.DbType.Int32;
             sp.Value = (int)p;
         }
         cmd.Parameters.Add(sp);
     }
 }
Example #11
0
        private void InsertEntryToFAM(Object e)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = conn;

                cmd.CommandText = "INSERT OR IGNORE INTO FAM(@timestamp, @pid, @filehandle, @filename, @user, @action, @accessmask)";

                SQLiteParameter param = cmd.CreateParameter();
                param.ParameterName = "timestamp";
                param.DbType = DbType.DateTime;

                param = cmd.CreateParameter();
                param.ParameterName = "pid";
                param.DbType = DbType.Int32;

                param = cmd.CreateParameter();
                param.ParameterName = "filehandle";
                param.DbType = DbType.Int32;

                param = cmd.CreateParameter();
                param.ParameterName = "filename";
                param.DbType = DbType.String;

                param = cmd.CreateParameter();
                param.ParameterName = "user";
                param.DbType = DbType.String;

                param = cmd.CreateParameter();
                param.ParameterName = "action";
                param.DbType = DbType.String;

                param = cmd.CreateParameter();
                param.ParameterName = "accessmask";
                param.DbType = DbType.Int32;

                cmd.ExecuteNonQuery();
            }
        }
Example #12
0
        private void CreateTables()
        {
            // Table : Types
            RunCommand("CREATE TABLE Types (Id INTEGER, Name TEXT, MethodTable INTEGER, Count INT, TotalSize INTEGER)");

            cmdInsertType = cxion.PrepareCommand("INSERT INTO Types(Id, Name, MethodTable, Count, TotalSize) VALUES (@Id, @Name, @MethodTable, @Count, @TotalSize)");
            paramId_InsertType = cmdInsertType.CreateParameter("Id");
            paramName_InsertType = cmdInsertType.CreateParameter("Name");
            paramMethodTable_InsertType = cmdInsertType.CreateParameter("MethodTable");
            paramCount_InsertType = cmdInsertType.CreateParameter("Count");
            paramTotalSize_InsertType = cmdInsertType.CreateParameter("TotalSize");

            // Table : Instances
            RunCommand("CREATE TABLE Instances (TypeId INTEGER, Address INTEGER)");

            cmdInsertInstance = cxion.PrepareCommand("INSERT INTO Instances(TypeId, Address) VALUES (@TypeId, @Address)");
            paramTypeId_InsertInstance = cmdInsertInstance.CreateParameter("TypeId");
            paramAddress_InsertInstance = cmdInsertInstance.CreateParameter("Address");

            // Table: References
            RunCommand("CREATE TABLE InstanceReferences (InstanceAddress INTEGER, RefByAddress INTEGER)");
            cmdInsertReference = cxion.PrepareCommand("INSERT INTO InstanceReferences(InstanceAddress, RefByAddress ) VALUES (@InstanceAddress, @RefByAddress)");
            paramInstanceAddress_InsertReference = cmdInsertReference.CreateParameter("InstanceAddress");
            paramRefByAddress_InsertReference = cmdInsertReference.CreateParameter("RefByAddress");
    }
Example #13
0
        private static void PrepareStatement(ref SQLiteCommand command, params object[] args)
        {
            foreach (var arg in args)
            {
                SQLiteParameter Field = command.CreateParameter();

                command.Parameters.Add(Field);

                Field.Value = arg;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        void TrimOpenAccessTimer_Elapsed(object sender, ElapsedEventArgs e)
        {
            CLogger.WriteLog(ELogLevel.INFO, "In Timer fired");

            Win32ProcessInfo procInfo;
            try
            {
                Process[] explorerProcList = Process.GetProcessesByName("explorer.exe");
                Process explorerInfo;
                DateTime timestamp = DateTime.Now;
                timestamp = timestamp.AddMinutes(-TRIM_OPEN_ACCESS_INTERVAL);

                for (int i = 0; i < explorerProcList.Length; i++)
                {
                    explorerInfo = explorerProcList[i];
                    procInfo = new Win32ProcessInfo();
                    procInfo.processID = explorerInfo.Id;
                    procInfo.timeStamp = timestamp;
                    lock(killedProcessList)
                    {
                        killedProcessList.Add(procInfo);
                    }
                }
            }
            catch (Exception exc)
            {
                CLogger.WriteLog(ELogLevel.INFO, "Exception occurred while opening explorer.exe but its ok to proceed." +
                                                    " System error: " +exc.Message);
            }

            /* Get the list of processes killed from table
             * Delete the entries from OPENACCESS with those pids which have entries older by
             * TRIM_OPEN_ACCESS_INTERVAL minutes */

            // Make a copy of the killedProcessList
            List<Win32ProcessInfo> currentList;
            lock (killedProcessList)
            {
                currentList = new List<Win32ProcessInfo>(killedProcessList);
                killedProcessList.Clear();
            }

            try
            {
                for (int i = currentList.Count; i > 0; i--)
                {
                    procInfo = currentList[i-1];

                    DateTime timeStamp = DateTime.Now;
                    timeStamp = timeStamp.AddMinutes(-TRIM_OPEN_ACCESS_INTERVAL);

                    if (timeStamp >= procInfo.timeStamp)
                    {
                        using (SQLiteCommand cmd = new SQLiteCommand())
                        {
                            cmd.Connection = conn;
                            cmd.CommandText = "DELETE FROM OPENACCESS WHERE pid =" + procInfo.processID +
                                            " AND timestamp <= @TS";

                            SQLiteParameter param = cmd.CreateParameter();
                            param.ParameterName = "@TS";
                            param.DbType = DbType.DateTime;
                            param.Value = procInfo.timeStamp;
                            cmd.Parameters.Add(param);

                            int rowsAffected = cmd.ExecuteNonQuery();
                            CLogger.WriteLog(ELogLevel.DEBUG, "Removed pid: " + procInfo.processID);
                            currentList.RemoveAt(i-1);
                        }
                    }
                }

                /* Add the remaining elements in the current list to the killedProcessList
                 * which might have changed when were deleting the records
                 *
                 */
                lock (killedProcessList)
                {
                    killedProcessList.AddRange(currentList);
                }
            }
            catch (Exception exc)
            {
                CLogger.WriteLog(ELogLevel.ERROR, exc.Message + "\n" + exc.StackTrace);
            }
        }
Example #15
0
        private SQLiteParameter AddFilterParameter(SQLiteCommand command, string paramName, DbType paramType, object paramValue)
        {
            try
            {
                SQLiteParameter param = command.CreateParameter();
                param.ParameterName = paramName;

                if (paramType == DbType.String)
                    param.Value = string.Format("%{0}%", paramValue);
                else
                    param.Value = string.Format("{0}", paramValue);

                command.Parameters.Add(param);
                return param;
            }
            catch (Exception ex)
            {
                log.Fatal("Error fatal al agregar parametro de filtro.", ex);
                throw;
            }
        }
Example #16
0
        private SQLiteDataReader Select(
            SQLiteConnection conn,
            string query,
            params object[] args
            )
        {
            var command = new SQLiteCommand(query, conn);

            foreach (var arg in args)
            {
                var parameter = command.CreateParameter();
                parameter.Value = arg;
                command.Parameters.Add(parameter);
            }

            return command.ExecuteReader();
        }
Example #17
0
        private int NonQuery(SQLiteConnection conn, string query, params object[] args)
        {
            var command = new SQLiteCommand(conn);

            command.CommandText = query;

            foreach (var arg in args)
            {
                var parameter = command.CreateParameter();
                parameter.Value = arg;
                command.Parameters.Add(parameter);
            }

            return command.ExecuteNonQuery();
        }
Example #18
0
        private void InsertEntryToOpenAccess(Object e)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = conn;

                cmd.CommandText = "INSERT OR IGNORE INTO OPEN ACCESS(@timestamp, @pid, @filehandle, @filename)";

                SQLiteParameter param = cmd.CreateParameter();
                param.ParameterName = "timestamp";
                param.DbType = DbType.DateTime;

                param = cmd.CreateParameter();
                param.ParameterName = "pid";
                param.DbType = DbType.Int32;

                param = cmd.CreateParameter();
                param.ParameterName = "filehandle";
                param.DbType = DbType.Int32;

                param = cmd.CreateParameter();
                param.ParameterName = "filename";
                param.DbType = DbType.String;

                cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="allFilters"></param>
        /// <param name="requestedDataType"></param>
        /// <param name="lastTime"></param>
        /// <returns></returns>
        private static string getQueryForFilters(
            SQLiteCommand myCommand, List<InternalFilter> allFilters, Byte requestedDataType, 
            IPAddress reqIP, String lastTime)
        {
            string searchBox1 = "";
            string searchBox2 = "";

            DateTime date1 = DateTime.MinValue;
            DateTime date2 = DateTime.MinValue;
            string dateQuery = "";
            int dateCount = 0;

            /*
            string machine = "";
            string machineQuery = "";
            int machineCount = 0;
            */

            string filename = "";
            int filenameCount = 0;
            string filenameQuery = "";

            string user = "";
            int userCount = 0;
            string userQuery = "";

            string action = "";
            string actionQuery = "";
            int actionCount = 0;

            string queryCategory = "";
            string queryCategoryQuery = "";
            int queryCategoryCount = 0;

            string result = "";

            //pattern to trim trailing OR's from the query
            char[] trimOR = { ' ', 'O', 'R' };

            /**
             * Iterate over each of the selected filter. Populate the corresponding vars with their vals. Generate the query as per the filter
             * criteria and then fire the query to get only the required items from the db.
            */
            int emptyFilter = 0;
            foreach (InternalFilter f in allFilters)
            {
                if (!f.Empty())
                {
                    searchBox1 = f.Search1;
                    searchBox2 = f.Search2;

                    //check if category and searchbox1 contains a value
                    if ((f.Category == null) || (searchBox1.Equals("")))
                    {
                        CLogger.WriteLog(ELogLevel.ERROR, "Search parameters insufficient");
                        throw new Exception("Search parameters insufficient");
                    }

                    // Handle * for some sensible cases like: machine, user, filename
                    if (f.Category.Equals("Time") || f.Category.Equals("TimeWritten"))
                    {
                        //code to get date 1 and date2
                        if (searchBox1.EndsWith(":"))
                        {
                            searchBox1 = searchBox1.Substring(0, 10);
                        }
                        if (searchBox2.EndsWith(":"))
                        {
                            searchBox2 = searchBox2.Substring(0, 10);
                        }

                        DateTime start;
                        try
                        {
                            start = DateTime.Parse(searchBox1);
                        }
                        catch (FormatException ex)
                        {
                            CLogger.WriteLog(ELogLevel.ERROR, ex.Message + " Start Date");
                            return null;
                        }

                        DateTime end;
                        try
                        {
                            end = DateTime.Parse(searchBox2);
                        }
                        catch (FormatException ex)
                        {
                            CLogger.WriteLog(ELogLevel.ERROR, ex.Message + " End Date");
                            end = start;
                        }

                        if (start.CompareTo(end) > 0)
                        {
                            DateTime tmp = start;
                            start = end;
                            end = tmp;
                        }

                        //TODO: this is not 24 hour conversion. check how this can be done.
                        //date1 = start.ToString("G", DateTimeFormatInfo.InvariantInfo);
                        //date2 = end.ToString("G", DateTimeFormatInfo.InvariantInfo);
                        date1 = start;
                        date2 = end;

                        dateQuery += " (timestamp between @date1" + dateCount + " AND @date2" + dateCount + ") OR ";
                        myCommand.Parameters.AddWithValue("@date1" + dateCount, date1);
                        myCommand.Parameters.AddWithValue("@date2" + dateCount, date2);
                        ++dateCount;

                        continue;
                    }

                    /*
                    if ("Machine".Equals(f.Category))
                    {
                        machine = f.Search1;
                        if (!"".Equals(machine))
                        {
                            if (machine.EndsWith("*") || machine.StartsWith("*"))
                            {
                                machine = machine.Replace("*", "%");
                                machineQuery += "machine like @machine" + machineCount + " OR ";
                            }
                            else
                            {
                                machineQuery += "machine = @machine" + machineCount + " OR ";
                            }
                            myCommand.Parameters.AddWithValue("@machine" + machineCount, machine);
                            ++machineCount;
                        }
                        continue;
                    }*/

                    if (f.Category.Equals("Object Name"))
                    {
                        searchBox1 = searchBox1.ToLower();
                        filename = searchBox1;

                        if (!"".Equals(filename))
                        {
                            if (filename.EndsWith("*") || filename.StartsWith("*"))
                            {
                                filename = filename.Replace("*", "%");
                                filenameQuery += "filename like @filename" + filenameCount + " OR ";
                            }
                            else
                            {
                                filename = "%" + filename + "%";
                                filenameQuery += "filename like @filename" + filenameCount + " OR ";
                            }
                            myCommand.Parameters.AddWithValue("@filename" + filenameCount, filename);
                            ++filenameCount;
                        }
                        continue;
                    }

                    if (f.Category.Equals("User"))
                    {
                        user = searchBox1;

                        if (!"".Equals(user))
                        {
                            if (user.EndsWith("*") || user.StartsWith("*"))
                            {
                                user = user.Replace("*", "%");
                                userQuery += "user like @user" + userCount + " OR ";

                            }
                            else
                            {
                                user = "%" + user;
                                userQuery += "user like @user" + userCount + " OR ";
                                //userQuery += "user = @user" + userCount + " OR ";
                            }
                            myCommand.Parameters.AddWithValue("@user" + userCount, user);
                            ++userCount;
                        }
                        continue;
                    }

                    if ("Action".Equals(f.Category))
                    {
                        action = searchBox1;

                        if (!"".Equals(action))
                        {
                            if (action.EndsWith("*") || action.StartsWith("*"))
                            {
                                action = action.Replace("*", "%");
                                actionQuery += "action like @action" + actionCount + " OR ";
                            }
                            else
                            {
                                actionQuery += "action = @action" + actionCount + " OR ";
                            }
                            myCommand.Parameters.AddWithValue("@action" + actionCount, action);
                            ++actionCount;
                        }
                        continue;
                    }

                    if ("Category".Equals(f.Category))
                    {
                        queryCategory = searchBox1;
                        queryCategoryQuery += "filetype = @category" + queryCategoryCount + " OR ";
                        myCommand.Parameters.AddWithValue("@category" + queryCategoryCount, queryCategory);
                        ++queryCategoryCount;
                        continue;
                    }

                    if ("Result".Equals(f.Category))
                    {
                        result = searchBox1;
                        continue;
                    }
                }

                else
                {
                    emptyFilter++;
                }
            }

            string query;

            if ((allFilters.Count - emptyFilter) == 0)
            {
                if (requestedDataType == Common.UPDATE)
                {
                    //lastTime is in format
                    if (lastTime == Common.DEFAULT)
                    {
                        query = @"select distinct timestamp, filename, user, action, filetype, result from FAM
                                order by timestamp desc limit " + Common.MAX_NUM;
                    }
                    else
                    {
                        query = @"select distinct timestamp, filename, user, action, filetype, result from FAM
                                where timestamp > @TS order by timestamp desc";
                        SQLiteParameter param = myCommand.CreateParameter();
                        param.ParameterName = "@TS";
                        param.DbType = DbType.DateTime;
                        try
                        {
                            CLogger.WriteLog(ELogLevel.DEBUG, " Last Time is " + lastTime);
                            param.Value = DateTime.Parse(lastTime);
                        }
                        catch (Exception ex)
                        {
                            CLogger.WriteLog(ELogLevel.ERROR, ex.Message + "\n" + ex.StackTrace);
                            return null;
                        }
                        myCommand.Parameters.Add(param);
                    }
                }
                else
                {
                    query = @"select distinct timestamp, filename, user, action, filetype, result from FAM
                    order by timestamp desc limit " + Common.MAX_NUM;
                }
            }
            else
            {
                query = @"select distinct timestamp, filename, user, action, filetype, result from FAM where ";

                if (!date1.Equals(DateTime.MinValue) && !date2.Equals(DateTime.MinValue))
                {
                    dateQuery = dateQuery.TrimEnd(trimOR);
                    query += " (" + dateQuery + ") AND ";
                }

                /*
                if (!"".Equals(machine))
                {
                    machineQuery = machineQuery.TrimEnd(trimOR);
                    query += " (" + machineQuery + ") AND ";
                }
                */

                if (!"".Equals(filename))
                {
                    filenameQuery = filenameQuery.TrimEnd(trimOR);
                    query += " (" + filenameQuery + ") AND ";
                }

                if (!"".Equals(user))
                {
                    userQuery = userQuery.TrimEnd(trimOR);
                    query += " (" + userQuery + ") AND ";
                }

                if (!"".Equals(action))
                {
                    actionQuery = actionQuery.TrimEnd(trimOR);
                    query += " (" + actionQuery + ") AND ";
                }

                if (!"".Equals(queryCategory))
                {
                    queryCategoryQuery = queryCategoryQuery.TrimEnd(trimOR);
                    query += " (" + queryCategoryQuery + ") AND ";
                }

                if (!"".Equals(result))
                {
                    query += "result = @result AND ";
                    myCommand.Parameters.AddWithValue("@result", result);

                }

                char[] trim = { ' ', 'A', 'N', 'D' };
                query = query.TrimEnd(trim);
            }

            CLogger.WriteLog(ELogLevel.INFO, "Client: " + reqIP + " Query: " + query);
            return query;
        }
        private void InsertEntryToOpenAccess(EventEntry e)
        {
            using (SQLiteCommand cmd = new SQLiteCommand())
            {
                cmd.Connection = conn;

                cmd.CommandText = @"INSERT OR IGNORE INTO OPENACCESS values
                    (@timestamp, @pid, @filehandle, @filename, @filetype, @accessmask, @user)";

                SQLiteParameter param = cmd.CreateParameter();
                param.ParameterName = "timestamp";
                param.DbType = DbType.DateTime;
                param.Value = e.timestamp;
                cmd.Parameters.Add(param);

                param = cmd.CreateParameter();
                param.ParameterName = "pid";
                param.DbType = DbType.Int32;
                param.Value = e.processID;
                cmd.Parameters.Add(param);

                param = cmd.CreateParameter();
                param.ParameterName = "filehandle";
                param.DbType = DbType.Int32;
                param.Value = e.handleID;
                cmd.Parameters.Add(param);

                param = cmd.CreateParameter();
                param.ParameterName = "filename";
                param.DbType = DbType.String;
                param.Value = e.fileName;
                cmd.Parameters.Add(param);

                param = cmd.CreateParameter();
                param.ParameterName = "filetype";
                param.DbType = DbType.String;
                param.Value = e.fileType;
                cmd.Parameters.Add(param);

                param = cmd.CreateParameter();
                param.ParameterName = "accessmask";
                param.DbType = DbType.Int32;
                param.Value = e.accessMask;
                cmd.Parameters.Add(param);

                param = cmd.CreateParameter();
                param.ParameterName = "user";
                param.DbType = DbType.String;
                param.Value = e.userName;
                cmd.Parameters.Add(param);

                cmd.ExecuteNonQuery();
            }
        }
Example #21
0
        ///
        /// SQLite Functions.
        ///
        public int CheckSQLFileHash(string query)
        {
            TraceLogObj.WriteToLog(_ThreadName, ObjectName, GetCurrentMethod(), "Start Call: CheckSQLFileHash");
            SQLiteCommand cmd;
            cmd = new SQLiteCommand(WDBSQLiteObj.conn);
            int ret = 0;
            cmd.CommandText = @"SELECT `ID` FROM `ImportView` WHERE `FileHash` = ?";

            TraceLogObj.WriteToLog(_ThreadName, ObjectName, GetCurrentMethod(), "FileHash query: " + query);

            var filehash = cmd.CreateParameter();
            filehash.Value = query;
            cmd.Parameters.Add(filehash);

            SQLiteDataReader reader;
            reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Debug.WriteLine("CheckSQLFileHash: " + reader["ID"].ToString() );
                if(Int32.Parse(reader["ID"].ToString()) > 0)
                {
                    ret = 1;
                }
            }
            cmd.Dispose();
            TraceLogObj.WriteToLog(_ThreadName, ObjectName, GetCurrentMethod(), "End Call: CheckSQLFileHash");
            return ret;
        }
Example #22
0
        private static void ExportToSQLite()
        {

            var con = new MySQLWrapper("dbc_406", false);
            // Basic connection
            using (var consql = new SQLiteConnection())
            {
                var filename = "maximusradar.db";

                if (System.IO.File.Exists(filename)) System.IO.File.Delete(filename);
                consql.ConnectionString = "Data Source=" + filename;
                consql.Open();

                //create tables
                using (var command = new SQLiteCommand(consql))
                {
                    command.CommandText = "CREATE TABLE IF NOT EXISTS AreaTable ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, MapId INTEGER NOT NULL, ZoneId INTEGER NOT NULL, Name VARCHAR(100) NOT NULL);";
                    command.ExecuteNonQuery();


                    command.CommandText = "CREATE TABLE IF NOT EXISTS Map ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, Name VARCHAR(100) NOT NULL, LongName VARCHAR(100) NOT NULL);";
                    command.ExecuteNonQuery();


                    command.CommandText = "CREATE TABLE IF NOT EXISTS GameObjectDisplayInfo ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, FileName VARCHAR(100) NOT NULL);";
                    command.ExecuteNonQuery();
                }

                //AreaTable
                using (var trans = consql.BeginTransaction())
                {
                    using (var command = consql.CreateCommand())
                    {
                        command.CommandText = "insert into AreaTable (id, MapId, ZoneId, Name) VALUES (?,?,?,?);";

                        var id = command.CreateParameter();
                        id.DbType = System.Data.DbType.Int32;
                        command.Parameters.Add(id);

                        var MapId = command.CreateParameter();
                        MapId.DbType = System.Data.DbType.Int32;
                        command.Parameters.Add(MapId);

                        var ZoneId = command.CreateParameter();
                        ZoneId.DbType = System.Data.DbType.Int32;
                        command.Parameters.Add(ZoneId);

                        var Name = command.CreateParameter();
                        Name.DbType = System.Data.DbType.String;
                        command.Parameters.Add(Name);


                        var table = con.ExecuteGetDataTable("select * from areatable");
                        foreach (System.Data.DataRow row in table.Rows)
                        {
                            try
                            {
                                id.Value = int.Parse(row[0].ToString());
                                MapId.Value = int.Parse(row[1].ToString());
                                ZoneId.Value = int.Parse(row[2].ToString());
                                Name.Value = row[11].ToSQLite();

                                if (command.ExecuteNonQuery() <= 0)
                                {
                                    throw new Exception("record not inserted?");
                                }
                            }
                            catch (Exception exc)
                            {
                                Console.WriteLine("Error: {0}", exc.Message);
                            }
                        }
                    }

                    trans.Commit();
                }

                //Map
                using (var trans = consql.BeginTransaction())
                {
                    using (var command = consql.CreateCommand())
                    {
                        command.CommandText = "insert into Map (id, Name, LongName) VALUES (?,?,?)";

                        var id = command.CreateParameter();
                        id.DbType = System.Data.DbType.Int32;
                        command.Parameters.Add(id);
 
                        var Name = command.CreateParameter();
                        Name.DbType = System.Data.DbType.String;
                        command.Parameters.Add(Name);


                        var LongName = command.CreateParameter();
                        LongName.DbType = System.Data.DbType.String;
                        command.Parameters.Add(LongName);

                        var table = con.ExecuteGetDataTable("select * from map");
                        foreach (System.Data.DataRow row in table.Rows)
                        {
                            try
                            {
                                id.Value = int.Parse(row[0].ToString());
                                Name.Value = row[1].ToSQLite();
                                LongName.Value = row[2].ToSQLite();

                                if (command.ExecuteNonQuery() <= 0)
                                {
                                    throw new Exception("record not inserted?");
                                }
                            }
                            catch (Exception exc)
                            {
                                Console.WriteLine("Error: {0}", exc.Message);
                            }
                        }
                    }

                    trans.Commit();
                }

                //GameObjectDisplayInfo
                using (var trans = consql.BeginTransaction())
                {
                    using (var command = consql.CreateCommand())
                    {
                        command.CommandText = "insert into GameObjectDisplayInfo (id, FileName) VALUES (?,?)";

                        var id = command.CreateParameter();
                        id.DbType = System.Data.DbType.Int32;
                        command.Parameters.Add(id);

                        var FileName = command.CreateParameter();
                        FileName.DbType = System.Data.DbType.String;
                        command.Parameters.Add(FileName);
 
                        var table = con.ExecuteGetDataTable("select * from gameobjectdisplayinfo");
                        foreach (System.Data.DataRow row in table.Rows)
                        {
                            try
                            {
                                id.Value = int.Parse(row[0].ToString());
                                FileName.Value = row[1].ToSQLite();

                                if (command.ExecuteNonQuery() <= 0)
                                {
                                    throw new Exception("record not inserted?");
                                }
                            }
                            catch (Exception exc)
                            {
                                Console.WriteLine("Error: {0}", exc.Message);
                            }
                        }
                    }

                    trans.Commit();
                }
 

            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        private bool isRenamePartOfDelete(EventEntry e)
        {
            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "SELECT * FROM FAM WHERE pid = " + e.processID +
                                    " AND filename = '" + e.fileName + "'" +
                                    " AND user = '" + e.userName + "'" +
                                    " AND timestamp > @TS" +
                                    " AND action = 'Delete' ";

                    SQLiteParameter param = cmd.CreateParameter();
                    param.ParameterName = "@TS";
                    param.DbType = DbType.DateTime;
                    DateTime timestamp = e.timestamp.AddSeconds(-1);
                    param.Value = timestamp;
                    cmd.Parameters.Add(param);

                    CLogger.WriteLog(ELogLevel.DEBUG, cmd.CommandText);

                    SQLiteDataReader reader = cmd.ExecuteReader();
                    if (reader.HasRows && reader.Read())
                        return true;
                }
            }
            catch (SQLiteException ex)
            {
                CLogger.WriteLog(ELogLevel.ERROR, ex.Message + "\n" + ex.StackTrace);
            }
            return false;
        }
Example #24
0
        public void InsertImportRow(ImportRow ImportRowObj)
        {
            TraceLogObj.WriteToLog(_ThreadName, ObjectName, GetCurrentMethod(), "Start Call: InsertImportRow");
            SQLiteCommand cmd;
            cmd = new SQLiteCommand(WDBSQLiteObj.conn);
            cmd.CommandText = @"INSERT INTO `ImportView`
            (`Username`, `Date_Time`, `FileSize`, `FileName`, `FileHash`, `Status`, `Message`)
            VALUES ( ?, ?, ?, ?, ?, ?, ?)";
            var Username = cmd.CreateParameter();
            Username.Value = ImportRowObj.Username;
            cmd.Parameters.Add(Username);

            var Date_Time = cmd.CreateParameter();
            Date_Time.Value = ImportRowObj.DateTime;
            cmd.Parameters.Add(Date_Time);

            var FileSize = cmd.CreateParameter();
            FileSize.Value = ImportRowObj.FileSize;
            cmd.Parameters.Add(FileSize);

            var FileName = cmd.CreateParameter();
            FileName.Value = ImportRowObj.FileName;
            cmd.Parameters.Add(FileName);

            var FileHash = cmd.CreateParameter();
            FileHash.Value = ImportRowObj.FileHash;
            cmd.Parameters.Add(FileHash);

            var Status = cmd.CreateParameter();
            Status.Value = ImportRowObj.Status;
            cmd.Parameters.Add(Status);

            var Message = cmd.CreateParameter();
            Message.Value = ImportRowObj.Message;
            cmd.Parameters.Add(Message);

            TraceLogObj.WriteToLog(_ThreadName, ObjectName, GetCurrentMethod(), "cmd.ExecuteNonQuery(): " + cmd.ExecuteNonQuery().ToString());

            cmd.Dispose();
            TraceLogObj.WriteToLog(_ThreadName, ObjectName, GetCurrentMethod(), "End Call: InsertImportRow");
        }
        private void InsertEntryToFAM(EventEntry e)
        {
            try
            {
                using (SQLiteCommand cmd = new SQLiteCommand())
                {
                    cmd.Connection = conn;

                    cmd.CommandText = @"INSERT INTO FAM values
                                    (@timestamp, @pid, @filehandle, @filename, @user, @action, @accessmask, @filetype, @result)";

                    SQLiteParameter param = cmd.CreateParameter();
                    param.ParameterName = "timestamp";
                    param.DbType = DbType.DateTime;
                    param.Value = e.timestamp;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "pid";
                    param.DbType = DbType.Int32;
                    param.Value = e.processID;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "filehandle";
                    param.DbType = DbType.Int32;
                    param.Value = e.handleID;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "filename";
                    param.DbType = DbType.String;
                    param.Value = e.fileName;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "user";
                    param.DbType = DbType.String;
                    param.Value = e.userName;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "action";
                    param.DbType = DbType.String;
                    param.Value = e.action;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "accessmask";
                    param.DbType = DbType.Int32;
                    param.Value = e.accessMask;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "filetype";
                    param.DbType = DbType.String;
                    param.Value = e.fileType;
                    cmd.Parameters.Add(param);

                    param = cmd.CreateParameter();
                    param.ParameterName = "result";
                    param.DbType = DbType.String;
                    param.Value = e.result;
                    cmd.Parameters.Add(param);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (SQLiteException ex)
            {
                CLogger.WriteLog(ELogLevel.ERROR,"INSERT INTO FAM exception " + ex.Message + "\n" + ex.StackTrace);
            }
        }
Example #26
0
        public void UpdateImportRow(ImportRow ImportRowObj)
        {
            TraceLogObj.WriteToLog(_ThreadName, ObjectName, GetCurrentMethod(), "Start Call: UpdateImportRow");
            SQLiteCommand cmd;
            cmd = new SQLiteCommand(WDBSQLiteObj.conn);
            cmd.CommandText = @"UPDATE `ImportView` SET
            `ImportID` = ?,
            `ImportTitle` = ?,
            `Status` = ?,
            `Message` = ?
            WHERE `FileHash` = ?";

            var ImportIDParm = cmd.CreateParameter();
            ImportIDParm.Value = ImportRowObj.ImportID;
            cmd.Parameters.Add(ImportIDParm);

            var ImportTitleParm = cmd.CreateParameter();
            ImportTitleParm.Value = ImportRowObj.ImportTitle;
            cmd.Parameters.Add(ImportTitleParm);

            var StatusParm = cmd.CreateParameter();
            StatusParm.Value = ImportRowObj.Status;
            cmd.Parameters.Add(StatusParm);

            var MessageParm = cmd.CreateParameter();
            MessageParm.Value = ImportRowObj.Message;
            cmd.Parameters.Add(MessageParm);

            var FileHashParm = cmd.CreateParameter();
            FileHashParm.Value = ImportRowObj.FileHash.ToUpper();
            cmd.Parameters.Add(FileHashParm);

            TraceLogObj.WriteToLog(_ThreadName, ObjectName, GetCurrentMethod(), "cmd.ExecuteNonQuery(): " + cmd.ExecuteNonQuery().ToString());

            cmd.Dispose();
            TraceLogObj.WriteToLog(_ThreadName, ObjectName, GetCurrentMethod(), "End Call: UpdateImportRow");
        }
Example #27
0
        public bool Save(SQLiteConnection dbcon, bool forceFullData)
        {
            bool result = true;
            using (Utils.ProgressBlock fixpr = new ProgressBlock(this, STR_SAVING, STR_SAVINGDATA, 1, 0))
            {
                if (dbcon != null)
                {
                    string[] custAttr = Core.Geocaches.CustomAttributes;
                    List<string> activeAttr = new List<string>();
                    using (SQLiteCommand cmd = new SQLiteCommand("select field_name from geocache_cfields", dbcon))
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    while (dr.Read())
                    {
                        activeAttr.Add(string.Format("{0}", dr["field_name"]));
                    }

                    foreach (string s in activeAttr)
                    {
                        if (!custAttr.Contains(s) && ColumnExists(dbcon, "geocache", string.Format("_{0}", s)))
                        {
                            //drop column not supported!
                        }
                    }
                    //geocache_cfields
                    using (SQLiteCommand cmd = new SQLiteCommand("delete from geocache_cfields", dbcon))
                        cmd.ExecuteNonQuery();
                    foreach (string s in custAttr)
                    {
                        if (!activeAttr.Contains(s))
                        {
                            using (SQLiteCommand cmd = new SQLiteCommand(string.Format("insert into geocache_cfields (field_name) values ('{0}')", s), dbcon))
                                cmd.ExecuteNonQuery();
                        }
                        if (!ColumnExists(dbcon, "geocache", string.Format("_{0}", s)))
                        {
                            using (SQLiteCommand cmd = new SQLiteCommand(string.Format("alter table geocache add _{0} text)", s), dbcon))
                                cmd.ExecuteNonQuery();
                        }
                    }

                    //delete geoacches that are not in the list anymore.
                    string[] c = (from string a in _geocachesInDB.Keys select a).ToArray();
                    using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                        for (int i = 0; i < c.Length; i++)
                        {
                            if (Utils.DataAccess.GetGeocache(Core.Geocaches, c[i]) == null)
                            {
                                cmd.CommandText = string.Format("delete from geocache where code='{0}'", c[i]);
                                cmd.ExecuteNonQuery();
                                _geocachesInDB.Remove(c[i]);
                            }
                        }

                    //reset selection
                    using (SQLiteCommand cmd = new SQLiteCommand("update geocache set selected=0", dbcon))
                        cmd.ExecuteNonQuery();

                    //now get all the selected and data changed geocaches
                    List<Framework.Data.Geocache> gclist = (from Framework.Data.Geocache wp in Core.Geocaches
                                                            where wp.Selected || !wp.Saved
                                                            select wp).ToList();
                    if (gclist.Count > 0)
                    {
                        using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGGEOCACHES, gclist.Count, 0))
                        {
                            string updateSqlFull = "update geocache set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]";
                            string insertSqlFull = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, shortdescr, shortdescrhtml, longdescr, longdescrhtml, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @shortdescr, @shortdescrhtml, @longdescr, @longdescrhtml, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)";

                            string updateSqlShort = "update geocache set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]";
                            string insertSqlShort = "insert into geocache (id, code, name, datafromdate, lat, lon, disttocent, angletocent, available, archived, country, state, cachetype, placedby, owner, ownerid, container, terrain, difficulty, encodedhints, url, memberonly, customcoords, attrids, favorites, selected, municipality, city, customlat, customlon, notes, publiceddate, personalnote, flagged, found, locked) values (@id, @code, @name, @datafromdate, @lat, @lon, @disttocent, @angletocent, @available, @archived, @country, @state, @cachetype, @placedby, @owner, @ownerid, @container, @terrain, @difficulty, @encodedhints, @url, @memberonly, @customcoords, @attrids, @favorites, @selected, @municipality, @city, @customlat, @customlon, @notes, @publiceddate, @personalnote, @flagged, @found, @locked)";

                            using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                            {
                                cmd.CommandType = CommandType.Text;
                                DbParameter par = cmd.CreateParameter();
                                par.ParameterName = "@id";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@code";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@name";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@datafromdate";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@lat";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@lon";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@disttocent";
                                par.DbType = DbType.Int64;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@angletocent";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@available";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@archived";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@country";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@state";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@cachetype";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@placedby";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@owner";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@ownerid";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@container";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@terrain";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@difficulty";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@shortdescr";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@shortdescrhtml";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@longdescr";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@longdescrhtml";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@encodedhints";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@url";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@memberonly";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@customcoords";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@attrids";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@favorites";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@selected";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@municipality";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@city";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@customlat";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@customlon";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@notes";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@publiceddate";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@personalnote";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@flagged";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@found";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@locked";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);

                                foreach (string s in custAttr)
                                {
                                    par = cmd.CreateParameter();
                                    par.ParameterName = string.Format("@_{0}", s);
                                    par.DbType = DbType.String;
                                    cmd.Parameters.Add(par);
                                }

                                cmd.Prepare();

                                StringBuilder sb = new StringBuilder();

                                using (SQLiteTransaction trans = dbcon.BeginTransaction())
                                {
                                    int index = 0;
                                    int procStep = 0;
                                    foreach (Framework.Data.Geocache gc in gclist)
                                    {
                                        index++;
                                        procStep++;
                                        if (!gc.Saved)
                                        {
                                            cmd.Parameters["@id"].Value = gc.ID ?? "";
                                            cmd.Parameters["@code"].Value = gc.Code;
                                            cmd.Parameters["@name"].Value = gc.Name ?? "";
                                            cmd.Parameters["@datafromdate"].Value = gc.DataFromDate.ToUniversalTime().ToString("u");
                                            cmd.Parameters["@lat"].Value = gc.Lat;
                                            cmd.Parameters["@lon"].Value = gc.Lon;
                                            cmd.Parameters["@disttocent"].Value = gc.DistanceToCenter;
                                            cmd.Parameters["@angletocent"].Value = gc.AngleToCenter;
                                            cmd.Parameters["@available"].Value = gc.Available ? 1 : 0;
                                            cmd.Parameters["@archived"].Value = gc.Archived ? 1 : 0;
                                            cmd.Parameters["@country"].Value = gc.Country ?? "";
                                            cmd.Parameters["@state"].Value = gc.State ?? "";
                                            cmd.Parameters["@cachetype"].Value = gc.GeocacheType == null ? -1 : gc.GeocacheType.ID;
                                            cmd.Parameters["@placedby"].Value = gc.PlacedBy ?? "";
                                            cmd.Parameters["@owner"].Value = gc.Owner ?? "";
                                            cmd.Parameters["@ownerid"].Value = gc.OwnerId ?? "";
                                            cmd.Parameters["@container"].Value = gc.Container == null ? -1 : gc.Container.ID;
                                            cmd.Parameters["@terrain"].Value = gc.Terrain;
                                            cmd.Parameters["@difficulty"].Value = gc.Difficulty;
                                            if (forceFullData || gc.FullDataLoaded)
                                            {
                                                cmd.Parameters["@shortdescr"].Value = gc.ShortDescription ?? "";
                                                cmd.Parameters["@shortdescrhtml"].Value = gc.ShortDescriptionInHtml ? 1 : 0;
                                                cmd.Parameters["@longdescr"].Value = gc.LongDescription ?? "";
                                                cmd.Parameters["@longdescrhtml"].Value = gc.LongDescriptionInHtml ? 1 : 0;
                                            }
                                            cmd.Parameters["@encodedhints"].Value = gc.EncodedHints ?? "";
                                            cmd.Parameters["@url"].Value = gc.Url ?? "";
                                            cmd.Parameters["@memberonly"].Value = gc.MemberOnly ? 1 : 0;
                                            cmd.Parameters["@customcoords"].Value = gc.CustomCoords ? 1 : 0;
                                            sb.Length = 0;
                                            foreach (int attrId in gc.AttributeIds)
                                            {
                                                sb.AppendFormat("|{0}|", attrId);
                                            }
                                            cmd.Parameters["@attrids"].Value = sb.ToString();
                                            cmd.Parameters["@favorites"].Value = gc.Favorites;
                                            cmd.Parameters["@selected"].Value = gc.Selected ? 1 : 0;
                                            cmd.Parameters["@municipality"].Value = gc.Municipality ?? "";
                                            cmd.Parameters["@city"].Value = gc.City ?? "";
                                            if (gc.CustomLat == null)
                                            {
                                                cmd.Parameters["@customlat"].Value = DBNull.Value;
                                            }
                                            else
                                            {
                                                cmd.Parameters["@customlat"].Value = gc.CustomLat;
                                            }
                                            if (gc.CustomLon == null)
                                            {
                                                cmd.Parameters["@customlon"].Value = DBNull.Value;
                                            }
                                            else
                                            {
                                                cmd.Parameters["@customlon"].Value = gc.CustomLon;
                                            }
                                            cmd.Parameters["@notes"].Value = gc.Notes ?? "";
                                            cmd.Parameters["@publiceddate"].Value = gc.PublishedTime.ToUniversalTime().ToString("u");
                                            cmd.Parameters["@personalnote"].Value = gc.PersonaleNote ?? "";
                                            cmd.Parameters["@flagged"].Value = gc.Flagged ? 1 : 0;
                                            cmd.Parameters["@found"].Value = gc.Found ? 1 : 0;
                                            cmd.Parameters["@locked"].Value = gc.Locked ? 1 : 0;

                                            foreach (string s in custAttr)
                                            {
                                                object o = gc.GetCustomAttribute(s);
                                                if (o == null)
                                                {
                                                    cmd.Parameters[string.Format("@_{0}", s)].Value = DBNull.Value;
                                                }
                                                else
                                                {
                                                    cmd.Parameters[string.Format("@_{0}", s)].Value = o.ToString();
                                                }
                                            }

                                            bool indb = _geocachesInDB[gc.Code] != null;
                                            if (forceFullData || gc.FullDataLoaded)
                                            {
                                                cmd.CommandText = updateSqlFull;
                                                if (!indb || cmd.ExecuteNonQuery() == 0)
                                                {
                                                    cmd.CommandText = insertSqlFull;
                                                    cmd.ExecuteNonQuery();
                                                    if (!indb)
                                                    {
                                                        _geocachesInDB[gc.Code] = gc.Code;
                                                    }
                                                }
                                            }
                                            else
                                            {
                                                cmd.CommandText = updateSqlShort;
                                                if (!indb || cmd.ExecuteNonQuery() == 0)
                                                {
                                                    cmd.CommandText = insertSqlShort;
                                                    cmd.ExecuteNonQuery();
                                                    if (!indb)
                                                    {
                                                        _geocachesInDB[gc.Code] = gc.Code;
                                                    }
                                                }
                                            }
                                            gc.Saved = true;
                                        }
                                        else if (gc.Selected)
                                        {
                                            cmd.CommandText = string.Format("update geocache set selected=1 where code='{0}'", gc.Code);
                                            cmd.ExecuteNonQuery();
                                        }
                                        if (procStep >= 200)
                                        {
                                            progress.UpdateProgress(STR_SAVING, STR_SAVINGGEOCACHES, gclist.Count, index);
                                            procStep = 0;
                                        }
                                    }
                                    trans.Commit();
                                }
                            }
                        }
                    }

                    //delete logs that are not in the list anymore.
                    c = (from string a in _logsInDB.Keys select a).ToArray();
                    using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                        for (int i = 0; i < c.Length; i++)
                        {
                            if (Utils.DataAccess.GetLog(Core.Logs, c[i]) == null)
                            {
                                cmd.CommandText = string.Format("delete from log where id='{0}'", c[i]);
                                cmd.ExecuteNonQuery();
                                _logsInDB.Remove(c[i]);
                            }
                        }

                    //now get all the selected and data changed geocaches
                    List<Framework.Data.Log> lglist = (from Framework.Data.Log wp in Core.Logs
                                                       where !wp.Saved
                                                       select wp).ToList();
                    if (lglist.Count > 0)
                    {
                        using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGLOGS, lglist.Count, 0))
                        {
                            // tbcode, finderid, logtext, encoded
                            string updateSqlFull = "update log set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]";
                            string insertSqlFull = "insert into log (id, gccode, tbcode, date, finder, finderid, logtext, encoded, datafromdate, logtype) values (@id, @gccode, @tbcode, @date, @finder, @finderid, @logtext, @encoded, @datafromdate, @logtype)";

                            string updateSqlShort = "update log set [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]";
                            string insertSqlShort = "insert into log (id, gccode, date, finder, datafromdate, logtype) values (@id, @gccode, @date, @finder, @datafromdate, @logtype)";

                            using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                            {
                                cmd.CommandType = CommandType.Text;
                                cmd.Parameters.Clear();
                                DbParameter par = cmd.CreateParameter();
                                par.ParameterName = "@id";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@gccode";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@tbcode";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@date";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@finder";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@finderid";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@logtext";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@encoded";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@datafromdate";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@logtype";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);

                                cmd.Prepare();

                                using (SQLiteTransaction trans = dbcon.BeginTransaction())
                                {
                                    int index = 0;
                                    int procStep = 0;
                                    foreach (Framework.Data.Log lg in lglist)
                                    {
                                        index++;
                                        procStep++;

                                        cmd.Parameters["@id"].Value = lg.ID;
                                        cmd.Parameters["@gccode"].Value = lg.GeocacheCode;
                                        cmd.Parameters["@date"].Value = lg.Date.ToUniversalTime().ToString("u");
                                        cmd.Parameters["@finder"].Value = lg.Finder ?? "";
                                        cmd.Parameters["@datafromdate"].Value = lg.DataFromDate.ToUniversalTime().ToString("u");
                                        cmd.Parameters["@logtype"].Value = lg.LogType == null ? -1 : lg.LogType.ID;
                                        if (forceFullData || lg.FullDataLoaded)
                                        {
                                            cmd.Parameters["@tbcode"].Value = lg.TBCode ?? "";
                                            cmd.Parameters["@finderid"].Value = lg.FinderId ?? "";
                                            cmd.Parameters["@logtext"].Value = lg.Text ?? "";
                                            cmd.Parameters["@encoded"].Value = lg.Encoded ? 1 : 0;
                                        }

                                        bool indb = _logsInDB[lg.ID] != null;
                                        if (forceFullData || lg.FullDataLoaded)
                                        {
                                            cmd.CommandText = updateSqlFull;
                                            if (!indb || cmd.ExecuteNonQuery() == 0)
                                            {
                                                cmd.CommandText = insertSqlFull;
                                                cmd.ExecuteNonQuery();
                                                if (!indb)
                                                {
                                                    _logsInDB[lg.ID] = lg.ID;
                                                }
                                            }
                                        }
                                        else
                                        {
                                            cmd.CommandText = updateSqlShort;
                                            if (!indb || cmd.ExecuteNonQuery() == 0)
                                            {
                                                cmd.CommandText = insertSqlShort;
                                                cmd.ExecuteNonQuery();
                                                if (!indb)
                                                {
                                                    _logsInDB[lg.ID] = lg.ID;
                                                }
                                            }
                                        }
                                        lg.Saved = true;

                                        if (procStep >= 2000)
                                        {
                                            progress.UpdateProgress(STR_SAVING, STR_SAVINGLOGS, lglist.Count, index);
                                            procStep = 0;
                                        }
                                    }

                                    trans.Commit();

                                }
                            }
                        }
                    }



                    //delete log images that are not in the list anymore.
                    c = (from string a in _logimgsInDB.Keys select a).ToArray();
                    using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                        for (int i = 0; i < c.Length; i++)
                        {
                            if (Utils.DataAccess.GetLogImage(Core.LogImages, c[i]) == null)
                            {
                                cmd.CommandText = string.Format("delete from logimage where id='{0}'", c[i]);
                                cmd.ExecuteNonQuery();
                                _logimgsInDB.Remove(c[i]);
                            }
                        }

                    //now get all the selected and data changed geocaches
                    List<Framework.Data.LogImage> imglist = (from Framework.Data.LogImage wp in Core.LogImages
                                                             where !wp.Saved
                                                             select wp).ToList();
                    if (imglist.Count > 0)
                    {
                        using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGLOGIMAGES, imglist.Count, 0))
                        {
                            string updateSql = "update logimage set [email protected], [email protected], [email protected], [email protected] where [email protected]";
                            string insertSql = "insert into logimage (id, logid, url, name, datafromdate) values (@id, @logid, @url, @name, @datafromdate)";

                            using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                            {

                                cmd.CommandType = CommandType.Text;
                                cmd.Parameters.Clear();
                                DbParameter par = cmd.CreateParameter();
                                par.ParameterName = "@id";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@logid";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@url";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@name";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@datafromdate";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                cmd.Prepare();

                                using (SQLiteTransaction trans = dbcon.BeginTransaction())
                                {
                                    int index = 0;
                                    int procStep = 0;
                                    foreach (Framework.Data.LogImage lg in imglist)
                                    {
                                        index++;
                                        procStep++;

                                        cmd.Parameters["@id"].Value = lg.ID;
                                        cmd.Parameters["@logid"].Value = lg.LogID ?? "";
                                        cmd.Parameters["@url"].Value = lg.Url ?? "";
                                        cmd.Parameters["@name"].Value = lg.Name ?? "";
                                        cmd.Parameters["@datafromdate"].Value = lg.DataFromDate.ToUniversalTime().ToString("u");

                                        bool indb = _logimgsInDB[lg.ID] != null;
                                        cmd.CommandText = updateSql;
                                        if (!indb || cmd.ExecuteNonQuery() == 0)
                                        {
                                            cmd.CommandText = insertSql;
                                            cmd.ExecuteNonQuery();
                                            if (!indb)
                                            {
                                                _logimgsInDB[lg.ID] = lg.ID;
                                            }
                                        }
                                        lg.Saved = true;

                                        if (procStep >= 500)
                                        {
                                            progress.UpdateProgress(STR_SAVING, STR_SAVINGLOGIMAGES, imglist.Count, index);
                                            procStep = 0;
                                        }
                                    }
                                    trans.Commit();
                                }
                            }
                        }
                    }


                    c = (from string a in _wptsInDB.Keys select a).ToArray();
                    using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                    for (int i = 0; i < c.Length; i++)
                    {
                        if (Utils.DataAccess.GetWaypoint(Core.Waypoints, c[i]) == null)
                        {
                            cmd.CommandText = string.Format("delete from waypoint where code='{0}'", c[i]);
                            cmd.ExecuteNonQuery();
                            _wptsInDB.Remove(c[i]);
                        }
                    }

                    //now get all the selected and data changed geocaches
                    List<Framework.Data.Waypoint> wplist = (from Framework.Data.Waypoint wp in Core.Waypoints
                                                            where !wp.Saved
                                                            select wp).ToList();
                    if (wplist.Count > 0)
                    {
                        using (Utils.ProgressBlock progress = new ProgressBlock(this, STR_SAVING, STR_SAVINGWAYPOINTS, wplist.Count, 0))
                        {
                            string updateSql = "update waypoint set [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]";
                            string insertSql = "insert into waypoint (id, code, geocachecode, name, datafromdate, comment, description, url, urlname, wptype, lat, lon, time) values (@id, @code, @geocachecode, @name, @datafromdate, @comment, @description, @url, @urlname, @wptype, @lat, @lon, @time)";

                            using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                            {

                                cmd.CommandType = CommandType.Text;
                                cmd.Parameters.Clear();
                                DbParameter par = cmd.CreateParameter();
                                par.ParameterName = "@id";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@code";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@url";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@name";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@datafromdate";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@geocachecode";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@comment";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@description";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@urlname";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@wptype";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@lat";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@lon";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@time";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                cmd.Prepare();

                                using (SQLiteTransaction trans = dbcon.BeginTransaction())
                                {
                                    int index = 0;
                                    int procStep = 0;
                                    foreach (Framework.Data.Waypoint wp in wplist)
                                    {
                                        index++;
                                        procStep++;

                                        cmd.Parameters["@id"].Value = wp.ID;
                                        cmd.Parameters["@code"].Value = wp.Code ?? "";
                                        cmd.Parameters["@url"].Value = wp.Url ?? "";
                                        cmd.Parameters["@urlname"].Value = wp.UrlName ?? "";
                                        cmd.Parameters["@name"].Value = wp.Name ?? "";
                                        cmd.Parameters["@comment"].Value = wp.Comment ?? "";
                                        cmd.Parameters["@geocachecode"].Value = wp.GeocacheCode ?? "";
                                        cmd.Parameters["@description"].Value = wp.Description ?? "";
                                        cmd.Parameters["@datafromdate"].Value = wp.DataFromDate.ToUniversalTime().ToString("u");
                                        cmd.Parameters["@time"].Value = wp.Time.ToUniversalTime().ToString("u");
                                        cmd.Parameters["@wptype"].Value = wp.WPType.ID;
                                        if (wp.Lat == null)
                                        {
                                            cmd.Parameters["@lat"].Value = DBNull.Value;
                                        }
                                        else
                                        {
                                            cmd.Parameters["@lat"].Value = (double)wp.Lat;
                                        }
                                        if (wp.Lon == null)
                                        {
                                            cmd.Parameters["@lon"].Value = DBNull.Value;
                                        }
                                        else
                                        {
                                            cmd.Parameters["@lon"].Value = wp.Lon;
                                        }

                                        bool indb = _wptsInDB[wp.Code] != null;
                                        cmd.CommandText = updateSql;
                                        if (!indb || cmd.ExecuteNonQuery() == 0)
                                        {
                                            cmd.CommandText = insertSql;
                                            cmd.ExecuteNonQuery();
                                            if (!indb)
                                            {
                                                _wptsInDB[wp.Code] = wp.Code;
                                            }
                                        }
                                        wp.Saved = true;

                                        if (procStep >= 500)
                                        {
                                            progress.UpdateProgress(STR_SAVING, STR_SAVINGWAYPOINTS, wplist.Count, index);
                                            procStep = 0;
                                        }
                                    }
                                    trans.Commit();
                                }
                            }
                        }
                    }

                    int[] ci = (from int a in _usrwptsInDB.Keys select a).ToArray();
                    using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                        for (int i = 0; i < ci.Length; i++)
                        {
                            if (Utils.DataAccess.GetUserWaypoint(Core.UserWaypoints, ci[i]) == null)
                            {
                                cmd.CommandText = string.Format("delete from userwaypoint where id={0}", ci[i]);
                                cmd.ExecuteNonQuery();
                                _usrwptsInDB.Remove(ci[i]);
                            }
                        }

                    //now get all the selected and data changed geocaches
                    List<Framework.Data.UserWaypoint> usrwplist = (from Framework.Data.UserWaypoint wp in Core.UserWaypoints
                                                            where !wp.Saved
                                                            select wp).ToList();
                    if (usrwplist.Count > 0)
                    {
                            string updateSql = "update userwaypoint set [email protected], [email protected], [email protected], [email protected], [email protected] where [email protected]";
                            string insertSql = "insert into userwaypoint (id, geocachecode, description, lat, lon, time) values (@id, @geocachecode, @description, @lat, @lon, @time)";

                            using (SQLiteCommand cmd = new SQLiteCommand(dbcon))
                            {

                                cmd.CommandType = CommandType.Text;
                                cmd.Parameters.Clear();
                                DbParameter par = cmd.CreateParameter();
                                par.ParameterName = "@id";
                                par.DbType = DbType.Int32;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@geocachecode";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@description";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@lat";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@lon";
                                par.DbType = DbType.Double;
                                cmd.Parameters.Add(par);
                                par = cmd.CreateParameter();
                                par.ParameterName = "@time";
                                par.DbType = DbType.String;
                                cmd.Parameters.Add(par);
                                cmd.Prepare();

                                using (SQLiteTransaction trans = dbcon.BeginTransaction())
                                {
                                    foreach (Framework.Data.UserWaypoint wp in usrwplist)
                                    {
                                        cmd.Parameters["@id"].Value = wp.ID;
                                        cmd.Parameters["@geocachecode"].Value = wp.GeocacheCode ?? "";
                                        cmd.Parameters["@description"].Value = wp.Description ?? "";
                                        cmd.Parameters["@time"].Value = wp.Date.ToUniversalTime().ToString("u");
                                        cmd.Parameters["@lat"].Value = (double)wp.Lat;
                                        cmd.Parameters["@lon"].Value = wp.Lon;

                                        bool indb = _usrwptsInDB[wp.ID] != null;
                                        cmd.CommandText = updateSql;
                                        if (!indb || cmd.ExecuteNonQuery() == 0)
                                        {
                                            cmd.CommandText = insertSql;
                                            cmd.ExecuteNonQuery();
                                            if (!indb)
                                            {
                                                _usrwptsInDB[wp.ID] = wp.ID;
                                            }
                                        }
                                        wp.Saved = true;
                                    }
                                    trans.Commit();
                                }
                            }
                    }

                    using (SQLiteCommand cmd = new SQLiteCommand(string.Format("update counter set geocache={0}, log={1}, waypoint={2}, logimage={3}", _geocachesInDB.Count, _logsInDB.Count, _wptsInDB.Count, _logimgsInDB.Count), dbcon))
                        cmd.ExecuteNonQuery();
                }
            }
            return result;
        }
        /// <summary>
        /// Adds the parameters to a SQLite command
        /// </summary>
        /// <param name="commandText">The SQLite query to execute</param>
        /// <param name="parameters">Parameters to pass to the SQLite query</param>
        private static void AddParameters(SQLiteCommand command, Dictionary<string, object> parameters)
        {
            if (parameters == null)
            {
                return;
            }

            foreach (var param in parameters)
            {
                var parameter = command.CreateParameter();
                parameter.ParameterName = param.Key;
                parameter.Value = param.Value ?? DBNull.Value;
                command.Parameters.Add(parameter);
            }
        }
Example #29
0
        private void Open(string dbPath)
        {
            string cxionString = $"Data Source={dbPath};Version=3;Page Size=65536;journal_mode=OFF;synchronous=OFF;count_changes=OFF;temp_store=MEMORY";
            cxion = new SQLiteConnection(cxionString);
            cxion.Open();

            cmdCountReferences = cxion.PrepareCommand("SELECT count(*) FROM InstanceReferences WHERE InstanceAddress= @instanceAddress");
            paramInstanceAddress_CountReferences = cmdCountReferences.CreateParameter("@instanceAddress");
        }
Example #30
0
    /// <summary>
    /// Allows the programmer to quickly insert multiple items into a database
    /// </summary>
    /// <param name="tableName"></param>
    /// <param name="data"></param>
    /// <returns></returns>
    public int Insert(String tableName, IList<Dictionary<String, String>> allData)
    {
        int modified = 0;

        using (SQLiteConnection cnn = new SQLiteConnection(dbConnection))
        {
            cnn.Open();
            using (SQLiteTransaction dbTrans = cnn.BeginTransaction())
            {
                // start the loop

                foreach (Dictionary<String, String> data in allData)
                {
                    String columns = "";
                    String values = "";
                    foreach (KeyValuePair<String, String> val in data)
                    {
                        columns += String.Format(" {0},", val.Key.ToString());
                        values += String.Format(" ?,");
                    }
                    columns = columns.Substring(0, columns.Length - 1);
                    values = values.Substring(0, values.Length - 1);
                    try
                    {
                        string sql = String.Format("insert into {0}({1}) values({2});", tableName, columns, values);
                        SQLiteCommand dbCommand = new SQLiteCommand(cnn);

                        foreach (KeyValuePair<String, String> val in data)
                        {
                            SQLiteParameter para = dbCommand.CreateParameter();
                            para.Value = val.Value;
                            dbCommand.Parameters.Add(para);
                        }

                        dbCommand.CommandText = sql;
                        int rowsUpdated = dbCommand.ExecuteNonQuery();
                        modified += rowsUpdated;
                    }
                    catch (Exception fail)
                    {
                        MessageBox.Show(fail.Message);
                    }
                }
                dbTrans.Commit();
            }
        }
        return modified;
    }