/// <summary>
/// This method executes a query using the given execution type and command
/// behavior and returns the results.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connection">
/// The connection used to create and execute the command.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
SQLiteConnection connection,
params object[] args
)
{
SQLiteConnection.Check(connection);
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
SQLiteParameter parameter = arg as SQLiteParameter;
if (parameter == null)
{
parameter = command.CreateParameter();
parameter.DbType = DbType.Object;
parameter.Value = arg;
}
command.Parameters.Add(parameter);
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return(command.ExecuteNonQuery(commandBehavior));
}
case SQLiteExecuteType.Scalar:
{
return(command.ExecuteScalar(commandBehavior));
}
case SQLiteExecuteType.Reader:
{
return(command.ExecuteReader(commandBehavior));
}
}
}
return(null);
}
/// <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 name=@name";
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;
}
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;
}
//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(); }
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type and command behavior, closes the connection unless a data
/// reader is created, and returns the results. If the connection string is
/// null, a temporary in-memory database connection will be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory databse will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
string connectionString,
params object[] args
)
{
SQLiteConnection connection = null;
try
{
if (connectionString == null)
{
connectionString = DefaultConnectionString;
}
using (connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
SQLiteParameter parameter = arg as SQLiteParameter;
if (parameter == null)
{
parameter = command.CreateParameter();
parameter.DbType = DbType.Object;
parameter.Value = arg;
}
command.Parameters.Add(parameter);
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return(command.ExecuteNonQuery(commandBehavior));
}
case SQLiteExecuteType.Scalar:
{
return(command.ExecuteScalar(commandBehavior));
}
case SQLiteExecuteType.Reader:
{
bool success = true;
try
{
//
// NOTE: The CloseConnection flag is being added here.
// This should force the returned data reader to
// close the connection when it is disposed. In
// order to prevent the containing using block
// from disposing the connection prematurely,
// the innermost finally block sets the internal
// no-disposal flag to true. The outer finally
// block will reset the internal no-disposal flag
// to false so that the data reader will be able
// to (eventually) dispose of the connection.
//
return(command.ExecuteReader(
commandBehavior | CommandBehavior.CloseConnection));
}
catch
{
success = false;
throw;
}
finally
{
//
// NOTE: If an exception was not thrown, that can only
// mean the data reader was successfully created
// and now owns the connection. Therefore, set
// the internal no-disposal flag (temporarily)
// in order to exit the containing using block
// without disposing it.
//
if (success)
{
connection._noDispose = true;
}
}
}
}
}
}
}
finally
{
//
// NOTE: Now that the using block has been exited, reset the
// internal disposal flag for the connection. This is
// always done if the connection was created because
// it will be harmless whether or not the data reader
// now owns it.
//
if (connection != null)
{
connection._noDispose = false;
}
}
return(null);
}
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();
}
}
/// <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;
}
/// <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);
}
}
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();
}
}
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;
}
}
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();
}
}
/// <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);
}
}
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;
}
}
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();
}
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();
}
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 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");
}
///
/// 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;
}
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 = '******'" +
" 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;
}
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);
}
}
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");
}
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 id=@id, name=@name, datafromdate=@datafromdate, lat=@lat, lon=@lon, disttocent=@disttocent, angletocent=@angletocent, available=@available, archived=@archived, country=@country, state=@state, cachetype=@cachetype, placedby=@placedby, owner=@owner, ownerid=@ownerid, container=@container, terrain=@terrain, difficulty=@difficulty, shortdescr=@shortdescr, shortdescrhtml=@shortdescrhtml, longdescr=@longdescr, longdescrhtml=@longdescrhtml, encodedhints=@encodedhints, url=@url, memberonly=@memberonly, customcoords=@customcoords, attrids=@attrids, favorites=@favorites, selected=@selected, municipality=@municipality, city=@city, customlat=@customlat, customlon=@customlon, notes=@notes, publiceddate=@publiceddate, personalnote=@personalnote, flagged=@flagged, found=@found, locked=@locked where code=@code";
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 id=@id, name=@name, datafromdate=@datafromdate, lat=@lat, lon=@lon, disttocent=@disttocent, angletocent=@angletocent, available=@available, archived=@archived, country=@country, state=@state, cachetype=@cachetype, placedby=@placedby, owner=@owner, ownerid=@ownerid, container=@container, terrain=@terrain, difficulty=@difficulty, encodedhints=@encodedhints, url=@url, memberonly=@memberonly, customcoords=@customcoords, attrids=@attrids, favorites=@favorites, selected=@selected, municipality=@municipality, city=@city, customlat=@customlat, customlon=@customlon, notes=@notes, publiceddate=@publiceddate, personalnote=@personalnote, flagged=@flagged, found=@found, locked=@locked where code=@code";
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 gccode=@gccode, tbcode=@tbcode, date=@date, finder=@finder, finderid=@finderid, logtext=@logtext, encoded=@encoded, datafromdate=@datafromdate, logtype=@logtype where id=@id";
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 gccode=@gccode, date=@date, finder=@finder, datafromdate=@datafromdate, logtype=@logtype where id=@id";
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 logid=@logid, url=@url, name=@name, datafromdate=@datafromdate where id=@id";
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 id=@id, geocachecode=@geocachecode, name=@name, datafromdate=@datafromdate, comment=@comment, description=@description, url=@url, urlname=@urlname, wptype=@wptype, lat=@lat, lon=@lon, time=@time where code=@code";
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 geocachecode=@geocachecode, description=@description, lat=@lat, lon=@lon, time=@time where id=@id";
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);
}
}
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");
}
/// <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;
}
