public static bool OpenDatabase(string dbFileName)
{
try
{
Log.log("Opening sqlite database connection: "+dbFileName);
string cs = string.Format("Version=3,uri=file:{0}", dbFileName);
connection = new SqliteConnection(cs);
connection.Open();
trans = connection.BeginTransaction();
return true;
}
catch (Exception e)
{
return false;
}
}
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 (DbTransaction 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 (DbTransaction 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 (DbTransaction 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 (DbTransaction 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 (DbTransaction 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;
}
private static void T6_ThreadStart(object data)
{
string commandt = String.Empty;
int i = (int)data;
try
{
int aValue = 100 + i;
int op = aValue % 2;
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T6;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
if (op == 0)
{
SqliteTransaction trans = (SqliteTransaction)con.BeginTransaction();
for (int j = 0; j < 5000; j++)
{
int rows;
int retry = 0;
cmd.CommandText = String.Format("INSERT INTO BTABLE ( A, B, C ) VALUES ({0},'threader', '1' )", (aValue * 10000) + j);
commandt = cmd.CommandText;
do
{
rows = cmd.ExecuteNonQuery();
if (rows == 0)
{
retry += 1; // Insert Failed
Console.WriteLine("retry {0}:{1}:{2}", retry, ((SqliteCommand)cmd).GetLastError(), cmd.CommandText);
Thread.Sleep(rnd.Next(50, 1000));
}
} while (rows == 0 && retry < 10);
}
trans.Commit();
}
else
{
cmd.CommandText = String.Format("Select * FROM ATABLE");
commandt = cmd.CommandText;
cmd.ExecuteReader();
}
}
catch (Exception ex)
{
Console.WriteLine(String.Format("Command {0} threw exception {1}", commandt, ex.Message));
}
}
protected override void ExportMethod()
{
using (Utils.ProgressBlock fixscr = new Utils.ProgressBlock(this, STR_EXPORTINGGPX, STR_CREATINGFILE, 1, 0))
{
System.Collections.Hashtable logTypes = new System.Collections.Hashtable();
logTypes.Add(2,"Found it");
logTypes.Add(3,"Didn't find it");
logTypes.Add(4,"Write note");
logTypes.Add(5,"Archive");
logTypes.Add(7,"Needs Archived");
logTypes.Add(9,"Will Attend");
logTypes.Add(10,"Attended");
logTypes.Add(11,"Webcam Photo Taken");
logTypes.Add(12,"Unarchive");
logTypes.Add(22,"Temporarily Disable Listing");
logTypes.Add(23,"Enable Listing");
logTypes.Add(24,"Publish Listing");
logTypes.Add(25,"Retract Listing");
logTypes.Add(45,"Needs Maintenance");
logTypes.Add(46,"Owner Maintenance");
logTypes.Add(47,"Update Coordinates");
logTypes.Add(68,"Post Reviewer Note");
logTypes.Add(74, "Announcement");
if (System.IO.File.Exists(_filename))
{
System.IO.File.Delete(_filename);
}
SqliteConnection dbconFiles = null;
string basePath = null;
int imgFolderIndex = 0;
int imgInFolderCount = 0;
if (PluginSettings.Instance.ExportGrabbedImages)
{
basePath = System.IO.Path.GetDirectoryName(_filename);
basePath = System.IO.Path.Combine(basePath, "GrabbedImages");
if (!System.IO.Directory.Exists(basePath))
{
System.IO.Directory.CreateDirectory(basePath);
}
if (PluginSettings.Instance.MaxFilesInFolder > 0)
{
string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex));
while (System.IO.Directory.Exists(imgSubFolder))
{
imgFolderIndex++;
imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex));
}
}
dbconFiles = new SqliteConnection(string.Format("data source=file:{0}", System.IO.Path.Combine(basePath,"files.db3")));
dbconFiles.Open();
using (SqliteCommand cmd = new SqliteCommand("", dbconFiles))
{
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='fdone'";
object o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "CREATE TABLE fdone (dlink text)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX ifdone on fdone (dlink)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='files'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "CREATE TABLE files (Link text collate nocase, Fname text collate nocase, Found integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX ilink on files (Link)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE INDEX ifname on files (Fname)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='purge'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "CREATE TABLE purge (pfile text)";
cmd.ExecuteNonQuery();
}
}
}
using (SqliteConnection dbcon = new SqliteConnection(string.Format("data source=file:{0}", _filename)))
{
dbcon.Open();
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
{
foreach (string s in SQLCREATEDBTABLES)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
foreach (string s in SQLCREATEDBINDEXES)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
foreach (string s in SQLCREATEDBTRIGGERS)
{
cmd.CommandText = s;
cmd.ExecuteNonQuery();
}
cmd.CommandText = "PRAGMA user_version = 5003";
cmd.ExecuteNonQuery();
}
DbParameter par;
using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_SAVING, STR_SAVINGGEOCACHES, _gcList.Count, 0))
{
using (SqliteCommand cmd = new SqliteCommand("", dbcon))
using (SqliteCommand cmd2 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd3 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd4 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd5 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd6 = new SqliteCommand("", dbcon))
using (SqliteCommand cmd7 = new SqliteCommand("", dbcon))
{
cmd.CommandText = "insert into Caches (Code, Name, PlacedBy, Archived, CacheId, CacheType, Container, Country, Difficulty, Found, HasCorrected, HasUserNote, Latitude, LongHtm, Longitude, OwnerName, PlacedDate, ShortHtm, State, Terrain, UserFlag, IsOwner, LatOriginal, LonOriginal, Status, GcNote, IsPremium, FavPoints, IsLite) values (@Code, @Name, @PlacedBy, @Archived, @CacheId, @CacheType, @Container, @Country, @Difficulty, @Found, @HasCorrected, @HasUserNote, @Latitude, @LongHtm, @Longitude, @OwnerName, @PlacedDate, @ShortHtm, @State, @Terrain, @UserFlag, @IsOwner, @LatOriginal, @LonOriginal, @Status, @GcNote, @IsPremium, @FavPoints, @IsLite)";
cmd2.CommandText = "insert into CacheMemo (Code, LongDescription, ShortDescription, Url, Hints, UserNote) values (@Code, @LongDescription, @ShortDescription, @Url, @Hints, @UserNote)";
cmd3.CommandText = "insert into Attributes (aCode, aId, aInc) values (@aCode, @aId, @aInc)";
cmd4.CommandText = "insert into LogMemo (lParent, lLogId, lText) values (@lParent, @lLogId, @lText)";
cmd5.CommandText = "insert into Logs (lParent, lLogId, lType, lBy, lDate, lLat, lLon, lEncoded, lownerid, lHasHtml, lIsowner, lTime) values (@lParent, @lLogId, @lType, @lBy, @lDate, @lLat, @lLon, @lEncoded, @lownerid, @lHasHtml, @lIsowner, @lTime)";
cmd6.CommandText = "insert into WayMemo (cParent, cCode, cComment, cUrl) values (@cParent, @cCode, @cComment, @cUrl)";
cmd7.CommandText = "insert into Waypoints (cParent, cCode, cPrefix, cName, cType, cLat, cLon, cByuser, cDate, cFlag, sB1) values (@cParent, @cCode, @cPrefix, @cName, @cType, @cLat, @cLon, @cByuser, @cDate, @cFlag, @sB1)";
par = cmd7.CreateParameter();
par.ParameterName = "@cParent";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cCode";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cPrefix";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cName";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cType";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cLat";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cLon";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cByuser";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cDate";
par.DbType = DbType.String;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@cFlag";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd7.CreateParameter();
par.ParameterName = "@sB1";
par.DbType = DbType.Boolean;
cmd7.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cParent";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cCode";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cComment";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd6.CreateParameter();
par.ParameterName = "@cUrl";
par.DbType = DbType.String;
cmd6.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lParent";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLogId";
par.DbType = DbType.Int32;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lType";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lBy";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lDate";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLat";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lLon";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lEncoded";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lownerid";
par.DbType = DbType.Int32;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lHasHtml";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lIsowner";
par.DbType = DbType.Boolean;
cmd5.Parameters.Add(par);
par = cmd5.CreateParameter();
par.ParameterName = "@lTime";
par.DbType = DbType.String;
cmd5.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lParent";
par.DbType = DbType.String;
cmd4.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lLogId";
par.DbType = DbType.Int32;
cmd4.Parameters.Add(par);
par = cmd4.CreateParameter();
par.ParameterName = "@lText";
par.DbType = DbType.String;
cmd4.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aCode";
par.DbType = DbType.String;
cmd3.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aId";
par.DbType = DbType.Int32;
cmd3.Parameters.Add(par);
par = cmd3.CreateParameter();
par.ParameterName = "@aInc";
par.DbType = DbType.Int32;
cmd3.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Code";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@LongDescription";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@ShortDescription";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Url";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@Hints";
par.DbType = DbType.String;
cmd2.Parameters.Add(par);
par = cmd2.CreateParameter();
par.ParameterName = "@UserNote";
par.DbType = DbType.String;
cmd2.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 = "@PlacedBy";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Archived";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@CacheId";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@CacheType";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Container";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Country";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Difficulty";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Found";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@HasCorrected";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@HasUserNote";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Latitude";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LongHtm";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Longitude";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@OwnerName";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@PlacedDate";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@ShortHtm";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@State";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Terrain";
par.DbType = DbType.Double;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@UserFlag";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsOwner";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LatOriginal";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@LonOriginal";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@Status";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@GcNote";
par.DbType = DbType.String;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsPremium";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@FavPoints";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
par = cmd.CreateParameter();
par.ParameterName = "@IsLite";
par.DbType = DbType.Int32;
cmd.Parameters.Add(par);
cmd.Prepare();
cmd2.Prepare();
cmd3.Prepare();
cmd4.Prepare();
cmd5.Prepare();
cmd6.Prepare();
cmd7.Prepare();
using (DbTransaction trans = dbcon.BeginTransaction())
{
int index = 0;
int procStep = 0;
foreach (Framework.Data.Geocache gc in _gcList)
{
cmd2.Parameters["@Code"].Value = gc.Code;
cmd2.Parameters["@LongDescription"].Value = gc.LongDescription ?? "";
cmd2.Parameters["@ShortDescription"].Value = gc.ShortDescription ?? "";
cmd2.Parameters["@Url"].Value = gc.Url ?? "";
cmd2.Parameters["@Hints"].Value = gc.EncodedHints ?? "";
cmd2.Parameters["@UserNote"].Value = string.IsNullOrEmpty(gc.Notes) ? "" : System.Web.HttpUtility.HtmlDecode(gc.Notes);
cmd.Parameters["@Code"].Value = gc.Code;
cmd.Parameters["@Name"].Value = gc.Name??"";
cmd.Parameters["@PlacedBy"].Value = gc.PlacedBy ?? "";
cmd.Parameters["@Archived"].Value = gc.Archived ? 1:0;
cmd.Parameters["@CacheId"].Value = gc.ID ?? "1";
cmd.Parameters["@CacheType"].Value = getCacheType(gc.GeocacheType);
cmd.Parameters["@Container"].Value = getContainer(gc.Container);
cmd.Parameters["@Country"].Value = gc.Country ?? "";
cmd.Parameters["@Difficulty"].Value = gc.Difficulty;
cmd.Parameters["@Found"].Value = gc.Found ? 1 : 0;
cmd.Parameters["@HasCorrected"].Value = (gc.CustomCoords || gc.ContainsCustomLatLon) ? 1 : 0;
cmd.Parameters["@HasUserNote"].Value = gc.ContainsNote ? 1 : 0;
cmd.Parameters["@LatOriginal"].Value = gc.Lat.ToString().Replace(',', '.');
cmd.Parameters["@LonOriginal"].Value = gc.Lon.ToString().Replace(',', '.');
if (gc.ContainsCustomLatLon)
{
cmd.Parameters["@Latitude"].Value = gc.CustomLat.ToString().Replace(',', '.');
cmd.Parameters["@Longitude"].Value = gc.CustomLon.ToString().Replace(',', '.');
}
else
{
cmd.Parameters["@Latitude"].Value = gc.Lat.ToString().Replace(',', '.');
cmd.Parameters["@Longitude"].Value = gc.Lon.ToString().Replace(',', '.');
}
cmd.Parameters["@LongHtm"].Value = gc.LongDescriptionInHtml ? 1 : 0;
cmd.Parameters["@OwnerName"].Value = gc.Owner ?? "";
cmd.Parameters["@PlacedDate"].Value = gc.PublishedTime.ToString("yyyy-MM-dd");
cmd.Parameters["@ShortHtm"].Value = gc.ShortDescriptionInHtml ? 1 : 0;
cmd.Parameters["@State"].Value = gc.State ?? "";
cmd.Parameters["@Terrain"].Value = gc.Terrain;
cmd.Parameters["@UserFlag"].Value = gc.Flagged ? 1 : 0;
cmd.Parameters["@IsOwner"].Value = gc.IsOwn ? 1 : 0;
cmd.Parameters["@Status"].Value = gc.Available ? "A" : gc.Archived ? "X" : "T";
cmd.Parameters["@GcNote"].Value = gc.PersonaleNote ?? "";
cmd.Parameters["@IsPremium"].Value = gc.MemberOnly ? 1 : 0;
cmd.Parameters["@FavPoints"].Value = gc.Favorites;
cmd.Parameters["@IsLite"].Value = 0;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
List<int> attr = gc.AttributeIds;
foreach (int att in attr)
{
cmd3.Parameters["@aCode"].Value = gc.Code;
cmd3.Parameters["@aId"].Value = Math.Abs(att);
cmd3.Parameters["@aInc"].Value = att < 0 ? 0 : 1;
cmd3.ExecuteNonQuery();
}
List<Framework.Data.Log> logs = Utils.DataAccess.GetLogs(Core.Logs, gc.Code).Take(PluginSettings.Instance.MaxLogs).ToList();
foreach (Framework.Data.Log l in logs)
{
try
{
int logid = 0;
if (!int.TryParse(l.ID, out logid))
{
logid = Utils.Conversion.GetCacheIDFromCacheCode(l.ID);
}
cmd4.Parameters["@lLogId"].Value = logid;
cmd4.Parameters["@lText"].Value = l.Text ?? "";
cmd4.Parameters["@lParent"].Value = gc.Code;
cmd4.ExecuteNonQuery();
cmd5.Parameters["@lLogId"].Value = logid;
cmd5.Parameters["@lParent"].Value = gc.Code;
object o = logTypes[l.LogType.ID];
if (o == null)
{
cmd5.Parameters["@lType"].Value = 4;
}
else
{
cmd5.Parameters["@lType"].Value = (string)o;
}
cmd5.Parameters["@lBy"].Value = l.Finder??"";
cmd5.Parameters["@lDate"].Value = l.Date.ToString("yyyy-MM-dd HH:mm:ss");
cmd5.Parameters["@lLat"].Value = DBNull.Value;
cmd5.Parameters["@lLon"].Value = DBNull.Value;
cmd5.Parameters["@lEncoded"].Value =l.Encoded;
try
{
cmd5.Parameters["@lownerid"].Value = int.Parse(l.FinderId);
}
catch
{
}
cmd5.Parameters["@lHasHtml"].Value = false;
cmd5.Parameters["@lIsowner"].Value = (l.Finder==Core.GeocachingAccountNames.GetAccountName(gc.Code));
cmd5.Parameters["@lTime"].Value = "";
cmd5.ExecuteNonQuery();
}
catch
{
}
}
List<Framework.Data.Waypoint> wps = Utils.DataAccess.GetWaypointsFromGeocache(Core.Waypoints, gc.Code);
foreach (Framework.Data.Waypoint w in wps)
{
try
{
cmd6.Parameters["@cParent"].Value = gc.Code;
cmd6.Parameters["@cCode"].Value = w.Code;
cmd6.Parameters["@cComment"].Value = w.Comment;
cmd6.Parameters["@cUrl"].Value = w.Url;
cmd7.Parameters["@cParent"].Value = gc.Code;
cmd7.Parameters["@cCode"].Value = w.Code;
cmd7.Parameters["@cPrefix"].Value = w.Code.Substring(0,2);
cmd7.Parameters["@cName"].Value = w.Name??"";
cmd7.Parameters["@cType"].Value = getWPType(w.WPType);
cmd7.Parameters["@cLat"].Value = w.Lat == null ? "0.0" : w.Lat.ToString().Replace(',', '.');
cmd7.Parameters["@cLon"].Value = w.Lon == null ? "0.0" : w.Lon.ToString().Replace(',', '.');
cmd7.Parameters["@cByuser"].Value = false;
cmd7.Parameters["@cDate"].Value = w.Time.ToString("yyyy-MM-dd");
cmd7.Parameters["@cFlag"].Value = false;
cmd7.Parameters["@sB1"].Value = false;
cmd7.ExecuteNonQuery();
cmd6.ExecuteNonQuery();
}
catch
{
}
}
if (dbconFiles != null && (gc.LongDescriptionInHtml || gc.ShortDescriptionInHtml))
{
try
{
List<string> linksInDescr = Utils.ImageSupport.GetImageUrlsFromGeocache(gc);
foreach (string link in linksInDescr)
{
string p = Utils.ImageSupport.Instance.GetImagePath(link);
if (!string.IsNullOrEmpty(p) && IsLocalFile(p))
{
using (SqliteCommand filescmd = new SqliteCommand("", dbconFiles))
{
filescmd.CommandText = string.Format("SELECT Fname FROM files WHERE Link='{0}'", link.Replace("'", "''"));
object o = filescmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
filescmd.CommandText = string.Format("insert into files (Link, Fname, Found) values ('{0}', '{1}', 1)", link.Replace("'", "''"), System.IO.Path.GetFileName(p).Replace("'", "''"));
filescmd.ExecuteNonQuery();
}
}
if (PluginSettings.Instance.MaxFilesInFolder > 0)
{
imgInFolderCount++;
if (imgInFolderCount > PluginSettings.Instance.MaxFilesInFolder)
{
imgFolderIndex++;
imgInFolderCount = 1;
}
string imgSubFolder = System.IO.Path.Combine(basePath, string.Format("batch{0}", imgFolderIndex));
if (imgInFolderCount == 1)
{
if (!System.IO.Directory.Exists(imgSubFolder))
{
System.IO.Directory.CreateDirectory(imgSubFolder);
}
}
string dst = System.IO.Path.Combine(imgSubFolder, System.IO.Path.GetFileName(p));
if (!System.IO.File.Exists(dst))
{
System.IO.File.Copy(p, dst, true);
}
}
else
{
string dst = System.IO.Path.Combine(basePath, System.IO.Path.GetFileName(p));
if (!System.IO.File.Exists(dst))
{
System.IO.File.Copy(p, dst, true);
}
}
}
}
}
catch
{
}
}
index++;
procStep++;
if (procStep >= 200)
{
progress.UpdateProgress(STR_SAVING, STR_SAVINGGEOCACHES, _gcList.Count, index);
procStep = 0;
}
}
trans.Commit();
}
}
}
}
if (dbconFiles != null)
{
dbconFiles.Dispose();
dbconFiles = null;
}
}
}
// Constructor
public MainPage()
{
InitializeComponent();
IsolatedStorageFile isf = IsolatedStorageFile.GetUserStoreForApplication();
isf.DeleteFile("test.db");
using (SqliteConnection conn = new SqliteConnection("Version=3,uri=file:test.db"))
{
conn.Open();
using (SqliteCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "CREATE TABLE test ( [id] INTEGER PRIMARY KEY, [col] INTEGER UNIQUE, [col2] INTEGER, [col3] REAL, [col4] TEXT, [col5] BLOB)";
cmd.ExecuteNonQuery();
cmd.Transaction = conn.BeginTransaction();
cmd.CommandText = "INSERT INTO test(col, col2, col3, col4, col5) VALUES(@col, @col2, @col3, @col4, @col5);SELECT last_insert_rowid();";
cmd.Parameters.Add("@col", null);
cmd.Parameters.Add("@col2", null);
cmd.Parameters.Add("@col3", null);
cmd.Parameters.Add("@col4", null);
cmd.Parameters.Add("@col5", null);
DateTime start = DateTime.Now;
this.lstResult.Items.Add("Inserting 100 Rows with transaction");
for (int i = 0; i < 100; i++)
{
cmd.Parameters["@col"].Value = i;
cmd.Parameters["@col2"].Value = i;
cmd.Parameters["@col3"].Value = i * 0.515;
cmd.Parameters["@col4"].Value = "สวัสดี な. あ · か · さ · た · な · は · ま · や · ら · わ. 形容詞 hello " + i;
cmd.Parameters["@col5"].Value = Encoding.UTF8.GetBytes("สวัสดี");
object s = cmd.ExecuteScalar();
}
cmd.Transaction.Commit();
cmd.Transaction = null;
this.lstResult.Items.Add("Time taken :" + DateTime.Now.Subtract( start ).TotalMilliseconds + " ms.");
cmd.CommandText = "SELECT * FROM test";
using (SqliteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var bytes = (byte[])reader.GetValue(5);
this.lstResult.Items.Add(string.Format("{0},{1},{2},{3},{4}, {5}",
reader.GetInt32(0),
reader.GetInt32(1),
reader.GetInt32(2),
reader.GetDouble(3),
reader.GetString(4),
Encoding.UTF8.GetString(bytes, 0, bytes.Length)));
}
}
conn.Close();
}
}
}
