/// <summary>
/// Execute command on the database, commands are serialized
/// </summary>
/// <param name="database"></param>
/// <param name="commandText"></param>
/// <returns></returns>
public static IEnumerable<SqliteDataReader> ExecuteCommand(string database, string commandText)
{
SqliteConnection conn;
if (!_Connection.TryGetValue(database, out conn))
{
conn = new SqliteConnection(SqliteConnectionManager.GetConnectionString(database));
conn.Open();
_Connection.Add(database, conn);
}
lock (conn)
{
var command = conn.CreateCommand();
command.CommandText = commandText;
using (var result = command.ExecuteReader())
{
while (result.Read())
{
yield return result;
}
}
command.Dispose();
}
}
public CodeProjectDatabase()
{
dbPath = "items.db3";
IsolatedStorageFile isf = IsolatedStorageFile.GetUserStoreForApplication();
bool exists = isf.FileExists(dbPath);
var connection = new SqliteConnection("data source=" + dbPath);
connection.Open();
if (!exists)
{
var commands = new[]{
"CREATE TABLE [Member] (Key integer, Name ntext, ArticleCnt integer, BlogCnt integer, Reputation ntext, IsMe integer);"
};
foreach (var command in commands)
{
using (var c = connection.CreateCommand())
{
c.CommandText = command;
c.ExecuteNonQuery();
}
}
}
}
protected override ICSDbConnection CreateConnection()
{
SqliteConnection conn = new SqliteConnection(ConnectionString);
conn.Open();
return new CSSqliteConnection(conn);
}
public static int ExecuteNonQuery(string sql)
{
using (SqliteConnection connection = new SqliteConnection())
{
connection.ConnectionString = ConnectionString;
System.Data.IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
connection.Open();
return cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Open a connection to the database.
/// </summary>
private void Open()
{
if (db == null)
{
try
{
db = new SqliteConnection(Constants.Database.connectionString);
db.Open();
}
catch (Exception e)
{
System.Windows.MessageBox.Show("Error while connecting to Database: \n" + e.Message + "\n" + e.StackTrace);
}
}
}
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;
}
}
/// <summary>
/// Shortcut to ExecuteNonQuery with SqlStatement and object[] param values
/// </summary>
/// <param name="connectionString">SQLite Connection String</param>
/// <param name="commandText">Sql Statement with embedded "@param" style parameters</param>
/// <param name="paramList">object[] array of parameter values</param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString, string commandText, object[] paramList)
{
SqliteConnection cn = new SqliteConnection(connectionString);
IDbCommand cmd = cn.CreateCommand();
cmd.CommandText = commandText;
if (paramList != null)
{
cmd.CommandText = string.Format(commandText, paramList);
}
if (cn.State == ConnectionState.Closed)
cn.Open();
int result = cmd.ExecuteNonQuery();
cmd.Dispose();
cn.Close();
return result;
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
_connectionString = connectionString;
string[] s1 = _connectionString.Split(new[] {"Data Source=", ","}, StringSplitOptions.RemoveEmptyEntries);
s1[0] = s1[0].Remove(0, 7);
_fileName = Path.GetFileName(s1[0]);
if (_fileName == s1[0]) //Only add this if we arn't an absolute path already
_connectionString = string.Format("Data Source=file://{0}", Path.Combine(Util.BasePathCombine(""), _fileName));
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
var migrationManager = new MigrationManager(this, migratorName, validateTables);
migrationManager.DetermineOperation();
migrationManager.ExecuteOperation();
connection.Close();
}
public bool AddMember(CodeProjectMember member)
{
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key] FROM [Member] WHERE [Key]=" + member.Id;
var r = command.ExecuteReader();
if (r.HasRows)
{
using (var uc = connection.CreateCommand())
{
uc.CommandText = "UPDATE [Member] SET " +
" [Name] = '" + member.Name + "'," +
" [ArticleCnt] = '" + member.ArticleCount + "'," +
" [BlogCnt] = '" + member.BlogCount + "'," +
" [Reputation] = '" + member.Reputation + "'," +
" WHERE [Key]=" + member.Id;
uc.ExecuteNonQuery();
}
}
else
{
using (var ic = connection.CreateCommand())
{
ic.CommandText = "INSERT INTO [Member] ([Key], [Name], [ArticleCnt], [BlogCnt], [Reputation])"
+ " VALUES(" + member.Id + ", '" + member.Name + "', '" + member.ArticleCount + "', '" + member.BlogCount + "', '" + member.Reputation + "')";
ic.ExecuteNonQuery();
}
}
}
connection.Close();
FileStorageService storage = new FileStorageService();
if (member.Avatar != null)
{
storage.WriteBytes(member.Avatar, member.Id.ToString());
}
return true;
}
public void Apply(SqliteConnection connection)
{
_connection = connection;
_log.Info("Start updating db");
_connection.Open();
try
{
CreateVcard();
SetupVersion();
}
catch (Exception e)
{
_log.Fatal("Unexpected error", e);
throw;
}
finally
{
_connection.Close();
}
}
protected void MainPage_Loaded(object sender, EventArgs e)
{
//SQLiteClientTests.SQLiteClientTestDriver.Main(null);
IDbConnection cnn;
try
{
System.IO.IsolatedStorage.IsolatedStorageFile.GetUserStoreForApplication().DeleteFile("test.db3");
}
catch { }
using (cnn = new SqliteConnection())
{
TestCases tests = new TestCases();
cnn.ConnectionString = "data source=test.db3,password=0x01010101010101010101010101010101";
cnn.Open();
tests.Run(cnn, this);
}
}
private int QueryVersion(SqliteConnection connection)
{
connection.Open();
try
{
var tableExists = connection.CreateCommand();
tableExists.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='version';";
var versionNumber = connection.CreateCommand();
versionNumber.CommandText = "SELECT num FROM version;";
_log.Info("Query version table");
using (var reader = tableExists.ExecuteReader())
{
if (!reader.HasRows)
{
_log.Info("Database is empty");
return 0;
}
}
_log.Info("Query version number");
using (var reader = versionNumber.ExecuteReader())
{
reader.Read();
var numb = (int) reader.GetValue(0);
_log.Info(string.Format("Database version is {0}", numb));
return numb;
}
}
catch (Exception e)
{
_log.Fatal("Unexpected exception", e);
throw;
}
finally
{
connection.Close();
}
}
public void Issue_119()
{
Console.WriteLine("Test Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"=SqliteTest3=.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TEST_TABLE ( COLA INTEGER, COLB TEXT, COLC DATETIME )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (123,'ABC','2008-12-31 18:19:20' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TEST_TABLE ( COLA, COLB, COLC ) VALUES (124,'DEF', '2009-11-16 13:35:36' )";
cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TEST_TABLE...");
cmd.CommandText = "SELECT RowID, COLA, COLB, COLC FROM TEST_TABLE";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int rowid = reader.GetInt32(reader.GetOrdinal("RowID"));
Console.WriteLine(" RowID: {0}", rowid);
int i = reader.GetInt32(reader.GetOrdinal("COLA"));
Console.WriteLine(" COLA: {0}", i);
string s = reader.GetString(reader.GetOrdinal("COLB"));
Console.WriteLine(" COLB: {0}", s);
DateTime dt = reader.GetDateTime(reader.GetOrdinal("COLC"));
Console.WriteLine(" COLB: {0}", dt.ToString("MM/dd/yyyy HH:mm:ss"));
r++;
}
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test Done.");
}
public List<CodeProjectMember> GetMembers()
{
List<CodeProjectMember> memberList = new List<CodeProjectMember>();
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key], [Name], [ArticleCnt], [BlogCnt], [Reputation], [IsMe] FROM [Member]";
var r = command.ExecuteReader();
while (r.Read())
{
CodeProjectMember member = new CodeProjectMember();
FillMemberFromDataReader(member, r);
memberList.Add(member);
}
}
connection.Close();
return memberList;
}
public CodeProjectMember GetMember(int memberId)
{
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
CodeProjectMember member = new CodeProjectMember();
member.Id = memberId;
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT [Key], [Name], [ArticleCnt], [BlogCnt], [Reputation], [IsMe] FROM [Member] WHERE [Key]=" + memberId;
var r = command.ExecuteReader();
while (r.Read())
{
FillMemberFromDataReader(member, r);
}
}
connection.Close();
return member;
}
protected override void ImportMethod()
{
System.Collections.Hashtable logTypes = new System.Collections.Hashtable();
using (Utils.ProgressBlock fixpr = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGDATA, 1, 0))
{
String connect = String.Format("data source=file:{0}", _filename);
using (SqliteConnection dbcon = new SqliteConnection(connect))
{
//System.Diagnostics.Debugger.Break();
logTypes.Add("Found it", 2);
logTypes.Add("Didn't find it", 3);
logTypes.Add("Write note", 4);
logTypes.Add("Archive", 5);
logTypes.Add("Needs Archived", 7);
logTypes.Add("Will Attend", 9);
logTypes.Add("Attended", 10);
logTypes.Add("Webcam Photo Taken", 11);
logTypes.Add("Unarchive", 12);
logTypes.Add("Temporarily Disable Listing", 22);
logTypes.Add("Enable Listing", 23);
logTypes.Add("Publish Listing", 24);
logTypes.Add("Retract Listing", 25);
logTypes.Add("Needs Maintenance", 45);
logTypes.Add("Owner Maintenance", 46);
logTypes.Add("Update Coordinates", 47);
logTypes.Add("Post Reviewer Note", 68);
logTypes.Add("Announcement", 74);
int index = 0;
int procStep = 0;
dbcon.Open();
SqliteCommand lookup = new SqliteCommand("select aId, aInc from attributes where aCode = @Code", dbcon);
lookup.CommandType = CommandType.Text;
DbParameter par = lookup.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = "@Code";
lookup.Parameters.Add(par);
lookup.Prepare();
SqliteCommand import = new SqliteCommand("select count(1) from caches", dbcon);
import.CommandType = CommandType.Text;
int gcCount = (int)(long)import.ExecuteScalar();
if (gcCount > 0)
{
using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGGEOCACHES, gcCount, 0))
{
bool isPremiumAvailable = false;
bool isFavPointAvailable = false;
bool isGCNoteAvailable = false;
try
{
import.CommandText = "select IsPremium from Caches limit 1";
using (SqliteDataReader checkdr = import.ExecuteReader())
{
isPremiumAvailable = true;
}
}
catch
{
}
try
{
import.CommandText = "select FavPoints from Caches limit 1";
using (SqliteDataReader checkdr = import.ExecuteReader())
{
isFavPointAvailable = true;
}
}
catch
{
}
try
{
import.CommandText = "select gcnote from Caches limit 1";
using (SqliteDataReader checkdr = import.ExecuteReader())
{
isGCNoteAvailable = true;
}
}
catch
{
}
import.CommandText = "select caches.Code, Name, LastGPXDate, PlacedDate, Latitude, Longitude, Status, " +
"Archived, Country, State, CacheType, PlacedBy, OwnerName, OwnerId, Container, Terrain, Difficulty, ShortHTM" +
", LongHTM, " +
string.Format("{0}", isPremiumAvailable? "IsPremium, ":"") +
" HasCorrected, LatOriginal, LonOriginal, UserFlag, Found, " +
string.Format("{0}", isFavPointAvailable? "FavPoints, ":"") +
" ShortDescription, LongDescription, Hints, Url, UserNote" +
string.Format("{0}", isGCNoteAvailable ? ", gcnote" : "") +
" from caches" +
" inner join cachememo on cachememo.code = caches.code";
SqliteDataReader dr = import.ExecuteReader();
while (dr.Read())
{
Framework.Data.Geocache gc = new Framework.Data.Geocache();
int cacheType;
try
{
cacheType = getCacheType(((String)dr["CacheType"])[0]);
}
catch (ArgumentOutOfRangeException)
{
continue;
}
int container = getContainer(((String)dr["Container"])[0]);
gc.Code = (string)dr["code"];
gc.Name = (string)dr["name"];
gc.DataFromDate = DateTime.Parse((string)dr["LastGPXDate"]);
gc.Available = ((String)dr["Status"]).Equals("A");
gc.Archived = (int)dr["archived"] != 0;
gc.Country = (string)dr["country"];
gc.State = (string)dr["state"];
gc.GeocacheType = Utils.DataAccess.GetGeocacheType(Core.GeocacheTypes, cacheType);
gc.PlacedBy = (string)dr["placedby"];
gc.Owner = (string)dr["OwnerName"];
gc.OwnerId = dr["ownerid"].GetType() == typeof(DBNull) ? "" : dr["ownerid"].ToString();
gc.Container = Utils.DataAccess.GetGeocacheContainer(Core.GeocacheContainers, container);
gc.Terrain = (double)dr["terrain"];
gc.Difficulty = (double)dr["difficulty"];
gc.ShortDescription = (string)dr["ShortDescription"];
gc.ShortDescriptionInHtml = (int)dr["ShortHTM"] != 0;
gc.LongDescription = (string)dr["LongDescription"];
gc.LongDescriptionInHtml = (int)dr["LongHTM"] != 0;
gc.EncodedHints = (string)dr["Hints"];
gc.Url = (string)dr["url"];
if (isPremiumAvailable)
{
gc.MemberOnly = (int)dr["IsPremium"] != 0;
}
else
{
gc.MemberOnly = false;
}
gc.CustomCoords = (int)dr["HasCorrected"] != 0;
if (gc.CustomCoords)
{
gc.CustomLat = Utils.Conversion.StringToDouble(dr["Latitude"] as String);
gc.CustomLon = Utils.Conversion.StringToDouble(dr["Longitude"] as String);
gc.Lat = Utils.Conversion.StringToDouble(dr["LatOriginal"] as string);
gc.Lon = Utils.Conversion.StringToDouble(dr["LonOriginal"] as string);
}
else
{
gc.Lat = Utils.Conversion.StringToDouble(dr["Latitude"] as string);
gc.Lon = Utils.Conversion.StringToDouble(dr["Longitude"] as string);
}
par.Value = gc.Code;
DbDataReader attrs = lookup.ExecuteReader();
List<int> attrList = new List<int>();
while (attrs.Read())
{
int attr = (int)(int)attrs["aId"];
if (attrs["aInc"].ToString() == "0")
{
attr *= -1;
}
attrList.Add(attr);
}
attrs.Close();
gc.AttributeIds = attrList;
gc.Notes = (string)dr["UserNote"];
gc.PublishedTime = DateTime.Parse((string)dr["PlacedDate"]);
if (isGCNoteAvailable)
{
gc.PersonaleNote = (string)dr["gcnote"];
}
else
{
gc.PersonaleNote = "";
}
gc.Flagged = (int)dr["UserFlag"] != 0;
gc.Found = (int)dr["Found"] != 0;
if (isFavPointAvailable)
{
gc.Favorites = (int)(int)dr["FavPoints"];
}
else
{
gc.Favorites = 0;
}
gc.Selected = false;
Calculus.SetDistanceAndAngleGeocacheFromLocation(gc, Core.CenterLocation);
AddGeocache(gc, null);
index++;
procStep++;
if (procStep >= 500)
{
progress.UpdateProgress(STR_IMPORTING, STR_IMPORTINGGEOCACHES, gcCount, index);
procStep = 0;
}
}
dr.Close();
}
import.CommandText = "select count(1) from logs";
int logCount = (int)(long)import.ExecuteScalar();
if (logCount > 0)
{
using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGLOGS, logCount, 0))
{
index = 0;
procStep = 0;
import.CommandText = "select l.lLogId, l.lParent, lDate, lTime, lBy, lownerid, lEncoded, lType, lText " +
" from logs l" +
" inner join logmemo m on m.lLogId = l.lLogId and m.lParent = l.lParent";
DbDataReader dr = import.ExecuteReader();
while (dr.Read())
{
Framework.Data.Log lg = new Framework.Data.Log();
String type = (String)dr["lType"];
int logType = (int)logTypes[type];
//id text, gccode text, tbcode text, date text, finder text, finderid text, logtext text, encoded integer, datafromdate text, logtype integer
lg.ID = dr["lLogiD"].ToString();
lg.GeocacheCode = (string)dr["lParent"];
lg.TBCode = "";
lg.Date = (DateTime)dr["lDate"];
lg.Finder = (string)dr["lBy"];
lg.FinderId = dr["lownerid"].ToString();
lg.Text = (string)dr["lText"];
lg.Encoded = (long)dr["lEncoded"]!=0;
lg.DataFromDate = DateTime.Now;
lg.LogType = Utils.DataAccess.GetLogType(Core.LogTypes, logType);
AddLog(lg);
index++;
procStep++;
if (procStep >= 1000)
{
progress.UpdateProgress(STR_IMPORTING, STR_IMPORTINGLOGS, logCount, index);
procStep = 0;
}
}
dr.Close();
}
}
import.CommandText = "select count(1) from logimages";
int logimgCount = 0;
try
{
logimgCount = (int)(long)import.ExecuteScalar();
}
catch
{
//table does not exists
}
if (logimgCount > 0)
{
using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGLOGIMAGES, logimgCount, 0))
{
index = 0;
procStep = 0;
import.CommandText = "select iCode, iLogId, iImage, iName from logimages";
DbDataReader dr = import.ExecuteReader();
while (dr.Read())
{
Framework.Data.LogImage lg = new Framework.Data.LogImage();
lg.ID = (string)dr["iCode"];
lg.LogID = dr["iLogID"].ToString();
lg.Url = (string)dr["iImage"];
lg.Name = (string)dr["iName"];
AddLogImage(lg);
index++;
procStep++;
if (procStep >= 500)
{
progress.UpdateProgress(STR_IMPORTING, STR_IMPORTINGLOGIMAGES, logimgCount, index);
procStep = 0;
}
}
dr.Close();
}
}
//id text, code text, geocachecode text, name text, datafromdate text, comment text, description text, url text, urlname text, wptype integer, lat real, lon real, time text
import.CommandText = "select count(1) from waypoints";
int wptCount = (int)(long)import.ExecuteScalar();
if (wptCount > 0)
{
using (Utils.ProgressBlock progress = new Utils.ProgressBlock(this, STR_IMPORTING, STR_IMPORTINGWAYPOINTS, wptCount, 0))
{
index = 0;
procStep = 0;
import.CommandText = "select w.cParent, w.cCode, cName, cDate, cType, cLat, cLon," +
" cComment, cUrl" +
" from waypoints w" +
" inner join wayMemo m on w.cParent = m.cParent and w.cCode=m.cCode";
DbDataReader dr = import.ExecuteReader();
while (dr.Read())
{
Framework.Data.Waypoint wp = new Framework.Data.Waypoint();
int wpType = getWPType(((string)dr["cType"])[0]);
wp.ID = (string)dr["cCode"];
wp.Code = (string)dr["cCode"];
wp.Url = (string)dr["cUrl"];
//wp.UrlName = (string)dr["urlname"];
wp.Name = (string)dr["cName"];
wp.DataFromDate = (DateTime)dr["cDate"];
wp.Comment = (string)dr["cComment"];
wp.GeocacheCode = (string)dr["cParent"];
//wp.Description = (string)dr["description"];
wp.WPType = Utils.DataAccess.GetWaypointType(Core.WaypointTypes, wpType);
double lat = Utils.Conversion.StringToDouble(dr["clat"] as string);
double lon = Utils.Conversion.StringToDouble(dr["clon"] as string);
if (Math.Abs(lat) < 0.00001)
{
wp.Lat = null;
}
else
{
wp.Lat = lat;
}
if (Math.Abs(lon) < 0.00001)
{
wp.Lon = null;
}
else
{
wp.Lon = lon;
}
wp.Time = (DateTime)dr["cDate"];
wp.Description = wp.WPType.Name;
wp.UrlName = wp.WPType.Name;
AddWaypoint(wp);
index++;
procStep++;
if (procStep >= 500)
{
progress.UpdateProgress(STR_IMPORTING, STR_IMPORTINGWAYPOINTS, wptCount, index);
procStep = 0;
}
}
dr.Close();
}
}
try
{
//import corrected if table exists
import.CommandText = "select kCode, kAfterLat, kAfterLon from Corrected";
DbDataReader dr = import.ExecuteReader();
while (dr.Read())
{
string gcCode = dr["kCode"] as string ?? "";
Framework.Data.Geocache gc = Utils.DataAccess.GetGeocache(Core.Geocaches, gcCode);
if (gc != null)
{
object oLat = dr["kAfterLat"];
object oLon = dr["kAfterLon"];
if (oLat != null && oLat.GetType() != typeof(DBNull) &&
oLon != null && oLon.GetType() != typeof(DBNull))
{
string sLat = oLat as string;
string sLon = oLon as string;
if (sLat.Length > 0 && sLon.Length > 0)
{
gc.CustomLat = Utils.Conversion.StringToDouble(sLat);
gc.CustomLon = Utils.Conversion.StringToDouble(sLon);
}
}
}
}
}
catch
{
}
}
}
}
}
private void Setup_T7()
{
SqliteConnection con = new SqliteConnection();
con.ConnectionString = connstring_T7;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd = con.CreateCommand();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS ATABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE IF NOT EXISTS BTABLE(A integer primary key , B varchar (50), C integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = String.Format("INSERT INTO BTABLE ( A, B, C ) VALUES (6,'threader', '1' )");
cmd.ExecuteNonQuery();
}
protected SqliteCommand PrepReader(string query)
{
try
{
SqliteConnection connection = new SqliteConnection(m_connectionString);
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = query;
return cmd as SqliteCommand;
}
catch (SqliteException ex)
{
MainConsole.Instance.WarnFormat ("[Sqlite]: Exception prepping reader command: {0}, Exception: {1}",
query, ex);
}
catch (Exception ex)
{
MainConsole.Instance.WarnFormat ("[Sqlite]: Exception prepping reader command: {0}, Exception: {1}",
query, ex);
}
return null;
}
public void Issue_65()
{
//alxwest: causes error "Unable to open database" as TempDirectory.ToString() is set to "B:/TEMP/"
//string datasource = "file://" + TempDirectory.ToString() + "myBigDb.s3db";
string datasource = "file://" + "myBigDb.s3db";
using (IDbConnection conn = new SqliteConnection("uri=" + datasource))
{
long targetFileSize = (long)Math.Pow(2, 32) - 1;
int rowLength = 1024; // 2^10
long loopCount = (int)(targetFileSize / rowLength) + 10000;
char[] chars = new char[rowLength];
for (int i = 0; i < rowLength; i++)
{
chars[i] = 'A';
}
string row = new string(chars);
conn.Open();
IDbCommand cmd = conn.CreateCommand();
try
{
cmd.CommandText = "PRAGMA cache_size = 16000; PRAGMA synchronous = OFF; PRAGMA journal_mode = MEMORY;";
cmd.ExecuteNonQuery();
cmd.CommandText = "drop table if exists [MyTable]";
cmd.ExecuteNonQuery();
cmd.CommandText = "create table [MyTable] ([MyField] varchar(" + rowLength + ") null)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into [MyTable] ([MyField]) VALUES ('" + row + "')";
for (int i = 0; i < loopCount; i++)
{
cmd.ExecuteNonQuery();
}
}
catch
{
Console.WriteLine(((SqliteCommand)cmd).GetLastError());
}
finally
{
cmd.Cancel();
conn.Close();
conn.Dispose();
}
}
}
//Issue 76 Encryption is not implemented in C#SQLite client connection and command objects
public void Issue_76()
{
Console.WriteLine("Test for Issue_76 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.CommandText = "create table a (b); insert into a values ('row 1');select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
Console.WriteLine("Close & Reopen Connection");
con.Close();
con.Open();
cmd.CommandText = "select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
Console.WriteLine("Close & Reopen Connection");
con.Close();
con.Open();
cmd.CommandText = "pragma hexkey='0x73656372657470617373776F72640f11'";
Console.WriteLine(cmd.CommandText);
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
Console.WriteLine("Close & Reopen Connection with password");
con.Close();
con.ConnectionString = cs + ",Password=0x73656372657470617373776F72640f11";
con.Open();
cmd.CommandText = "select * from a;";
Console.WriteLine(cmd.CommandText);
Console.WriteLine("Result {0}", cmd.ExecuteScalar());
con = null;
Console.WriteLine("Issue_76 Done.");
}
public void DeleteMember(int memberId)
{
var connection = new SqliteConnection("Data Source=" + dbPath);
connection.Open();
using (var ic = connection.CreateCommand())
{
ic.CommandText = "DELETE FROM [Member] " +
" WHERE [Key]=" + memberId;
ic.ExecuteNonQuery();
}
connection.Close();
FileStorageService storage = new FileStorageService();
storage.DeleteFile(memberId.ToString());
}
public void Test2()
{
Console.WriteLine("Test2 Start.");
Console.WriteLine("Create connection...");
SqliteConnection con = new SqliteConnection();
string dbFilename = @"SqliteTest3.db";
string cs = string.Format("Version=3,uri=file:{0}", dbFilename);
Console.WriteLine("Set connection String: {0}", cs);
if (File.Exists(dbFilename))
File.Delete(dbFilename);
con.ConnectionString = cs;
Console.WriteLine("Open database...");
con.Open();
Console.WriteLine("create command...");
IDbCommand cmd = con.CreateCommand();
Console.WriteLine("create table TEST_TABLE...");
cmd.CommandText = "CREATE TABLE TBL ( ID NUMBER, NAME TEXT)";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 1...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (1, 'ONE' )";
cmd.ExecuteNonQuery();
Console.WriteLine("insert row 2...");
cmd.CommandText = "INSERT INTO TBL ( ID, NAME ) VALUES (2, 'ä¸Âæ–‡' )";
cmd.ExecuteNonQuery();
//Console.WriteLine("commit...");
//cmd.CommandText = "COMMIT";
//cmd.ExecuteNonQuery();
Console.WriteLine("SELECT data from TBL...");
cmd.CommandText = "SELECT id,NAME FROM tbl WHERE name = 'ä¸Âæ–‡'";
IDataReader reader = cmd.ExecuteReader();
int r = 0;
Console.WriteLine("Read the data...");
while (reader.Read())
{
Console.WriteLine(" Row: {0}", r);
int i = reader.GetInt32(reader.GetOrdinal("ID"));
Console.WriteLine(" ID: {0}", i);
string s = reader.GetString(reader.GetOrdinal("NAME"));
Console.WriteLine(" NAME: {0} = {1}", s, s == "ä¸Âæ–‡");
r++;
}
Console.WriteLine("Rows retrieved: {0}", r);
//alxwest: DataTable & SqliteDataAdapter currently unavailable for Silverlight
#if !SQLITE_SILVERLIGHT
SqliteCommand command = new SqliteCommand("PRAGMA table_info('TEST_TABLE')", con);
DataTable dataTable = new DataTable();
SqliteDataAdapter dataAdapter = new SqliteDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
DisplayDataTable(dataTable, "Columns");
#endif
Console.WriteLine("Close and cleanup...");
con.Close();
con = null;
Console.WriteLine("Test1 Done.");
}
protected SqliteCommand PrepReader(string query)
{
try
{
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = query;
return cmd as SqliteCommand;
}
catch (SqliteException)
{
//throw ex;
}
catch (Exception ex)
{
throw ex;
}
return null;
}
private void Init()
{
_dbConn = new SqliteConnection(DDPhotoGalleryDataManager.DBInfo);
_dbConn.Open();
}
protected void PrepReader(ref SqliteCommand cmd)
{
int retries = 0;
restart:
try
{
SqliteConnection connection = new SqliteConnection(m_connectionString);
connection.Open();
cmd.Connection = connection;
}
catch (SqliteBusyException ex)
{
if (retries++ > 5)
MainConsole.Instance.WarnFormat("[Sqlite]: Exception processing command: {0}, Exception: {1}",
cmd.CommandText, ex);
else
goto restart;
}
catch (SqliteException ex)
{
MainConsole.Instance.WarnFormat("[Sqlite]: Exception processing command: {0}, Exception: {1}",
cmd.CommandText, ex);
}
catch (Exception ex)
{
MainConsole.Instance.WarnFormat("[Sqlite]: Exception processing command: {0}, Exception: {1}",
cmd.CommandText, ex);
}
}
private bool InitDatabase(SqliteConnection dbcon)
{
bool result = false;
try
{
if (dbcon.State == ConnectionState.Closed)
{
dbcon.Open();
}
using (SqliteCommand cmd = new SqliteCommand("",dbcon))
{
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='project_info'";
object o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'project_info' (item_name text, item_value text)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='geocache_cfields'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'geocache_cfields' (field_name text)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='geocache'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'geocache' (id text, code text, name text, datafromdate text, lat real, lon real, disttocent integer, angletocent integer, available integer, archived integer, country text, state text, cachetype integer, placedby text, owner text, ownerid text, container integer, terrain real, difficulty real, shortdescr text, shortdescrhtml integer, longdescr text, longdescrhtml integer, encodedhints text, url text, memberonly integer, customcoords integer, attrids text, favorites integer, selected integer, municipality text, city text, customlat real, customlon real, notes text, publiceddate text, personalnote text, flagged integer, found integer, locked integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_geocache on geocache (code)";
cmd.ExecuteNonQuery();
}
else if (!ColumnExists(dbcon,"geocache","locked"))
{
cmd.CommandText = "alter table geocache add locked integer";
cmd.ExecuteNonQuery();
cmd.CommandText = "update geocache set locked=0";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='log'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'log' (id text, gccode text, tbcode text, date text, finder text, finderid text, logtext text, encoded integer, datafromdate text, logtype integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_log on log (id)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='logimage'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'logimage' (id text, logid text, url text, name text, datafromdate text)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_logimage on logimage (id)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='waypoint'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'waypoint' (id text, code text, geocachecode text, name text, datafromdate text, comment text, description text, url text, urlname text, wptype integer, lat real, lon real, time text)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_waypoint on waypoint (code)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='userwaypoint'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'userwaypoint' (id integer, geocachecode text, description text, lat real, lon real, time text)";
cmd.ExecuteNonQuery();
cmd.CommandText = "create unique index idx_userwaypoint on userwaypoint (id)";
cmd.ExecuteNonQuery();
}
cmd.CommandText = "SELECT name FROM sqlite_master WHERE type='table' AND name='counter'";
o = cmd.ExecuteScalar();
if (o == null || o.GetType() == typeof(DBNull))
{
cmd.CommandText = "create table 'counter' (geocache integer, log integer, waypoint integer, logimage integer)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into counter (geocache, log, waypoint, logimage) values (0, 0, 0, 0)";
cmd.ExecuteNonQuery();
}
}
result = true;
}
catch
{
}
return result;
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
// connection string in the format...
// Data Source=File:<db_filename>
m_connectionString = connectionString;
string[] s1 = m_connectionString.Split(new[] {"Data Source=", ";", ","}, StringSplitOptions.RemoveEmptyEntries);
// first element should be file:<db_filename>
s1[0] = s1[0].Remove(0, 5);
m_fileName = s1 [0];
// some sanity checks
string filePath = Path.GetDirectoryName (s1[0]);
string fileName = Path.GetFileName (s1[0]);
if (filePath == "") //Only add this if we aren't an absolute path already eg file:data.db
{
m_connectionString = string.Format ("Data Source=file://{0}", Path.Combine (m_defaultDataPath, fileName));
filePath = m_defaultDataPath;
m_fileName = Path.Combine (m_defaultDataPath, fileName);
}
if (!Directory.Exists (filePath))
Directory.CreateDirectory (filePath); // directory does not exist!
if (!File.Exists(m_fileName))
File.Create(m_fileName).Dispose(); // database file does not exist, create an empty one to use
SqliteConnection connection = new SqliteConnection(m_connectionString);
try {
connection.Open ();
var migrationManager = new MigrationManager (this, migratorName, validateTables);
migrationManager.DetermineOperation ();
migrationManager.ExecuteOperation ();
} catch {
MainConsole.Instance.Warn ("[Sqlite]: Unable to connect to database ("+m_connectionString+")");
}
connection.Close();
}
protected void PrepReader(ref SqliteCommand cmd)
{
int retries = 0;
restart:
try
{
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
cmd.Connection = connection;
}
catch (SqliteBusyException ex)
{
if (retries++ > 5)
MainConsole.Instance.Warn("[SqliteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " +
ex);
else
goto restart;
}
catch (SqliteException ex)
{
MainConsole.Instance.Warn("[SqliteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " +
ex);
//throw ex;
}
catch (Exception ex)
{
MainConsole.Instance.Warn("[SqliteDataManager]: Exception processing command: " + cmd.CommandText + ", Exception: " +
ex);
throw ex;
}
}
protected override void ExportMethod()
{
try
{
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);
}
using (var strm = Assembly.GetExecutingAssembly().GetManifestResourceStream("GlobalcachingApplication.Plugins.Locus.sqlite.db3"))
{
byte[] data = new byte[strm.Length];
strm.Read(data, 0, data.Length);
File.WriteAllBytes(_filename, data);
}
SqliteConnection dbconFiles = null;
string basePath = null;
int imgFolderIndex = 0;
int imgInFolderCount = 0;
if (Properties.Settings.Default.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 (Properties.Settings.Default.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();
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))
using (SqliteCommand cmd8 = new SqliteCommand("", dbcon))
{
cmd.CommandText = "drop index CachesSmart";
cmd.ExecuteNonQuery();
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) 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)";
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)";
cmd8.CommandText = "insert into Corrected (kCode, kBeforeLat, kBeforeLon, kAfterLat, kAfterLon) values (@kCode, @kBeforeLat, @kBeforeLon, @kAfterLat, @kAfterLon)";
par = cmd8.CreateParameter();
par.ParameterName = "@kCode";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kBeforeLat";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kBeforeLon";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kAfterLat";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
par = cmd8.CreateParameter();
par.ParameterName = "@kAfterLon";
par.DbType = DbType.String;
cmd8.Parameters.Add(par);
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);
cmd.Prepare();
cmd2.Prepare();
cmd3.Prepare();
cmd4.Prepare();
cmd5.Prepare();
cmd6.Prepare();
cmd7.Prepare();
cmd8.Prepare();
//using (DbTransaction trans = dbcon.BeginTransaction())
//{
int index = 0;
int procStep = 0;
foreach (Framework.Data.Geocache gc in _gcList)
{
string notes = "";
if (!string.IsNullOrEmpty(gc.Notes))
{
notes = System.Web.HttpUtility.HtmlDecode(gc.Notes);
}
if (!string.IsNullOrEmpty(gc.PersonaleNote))
{
notes = string.Concat(notes, gc.PersonaleNote);
}
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 = 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 = notes;
cmd.Parameters["@IsPremium"].Value = gc.MemberOnly ? 1 : 0;
cmd.Parameters["@FavPoints"].Value = gc.Favorites;
cmd.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
if (gc.ContainsCustomLatLon)
{
cmd8.Parameters["@kCode"].Value = gc.Code;
cmd8.Parameters["@kBeforeLat"].Value = gc.Lat.ToString().Replace(',', '.');
cmd8.Parameters["@kBeforeLon"].Value = gc.Lon.ToString().Replace(',', '.');
cmd8.Parameters["@kAfterLat"].Value = gc.CustomLat.ToString().Replace(',', '.');
cmd8.Parameters["@kAfterLon"].Value = gc.CustomLon.ToString().Replace(',', '.');
cmd8.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(Properties.Settings.Default.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 (Properties.Settings.Default.MaxFilesInFolder > 0)
{
imgInFolderCount++;
if (imgInFolderCount > Properties.Settings.Default.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;
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, Utils.LanguageSupport.Instance.GetTranslation(Utils.LanguageSupport.Instance.GetTranslation(STR_ERROR)), MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public override void ConnectToDatabase(string connectionString, string migratorName, bool validateTables)
{
_connectionString = connectionString;
string[] s1 = _connectionString.Split(new[] { "Data Source=", "," }, StringSplitOptions.RemoveEmptyEntries);
bool needsUTFConverted = false;
_fileName = Path.GetFileName(s1[0]);
if (s1[0].EndsWith(";"))
{
_fileName = Path.GetFileNameWithoutExtension(s1[1].Substring(7, s1[1].Length - 7)) + "utf8.db";
_connectionString = "Data Source=file://" + _fileName;
s1 = new string[1] { "file://" + _fileName };
needsUTFConverted = true;
_hadToConvert = true;
}
if (_fileName == s1[0]) //Only add this if we arn't an absolute path already
_connectionString = _connectionString.Replace("Data Source=", "Data Source=" + Util.BasePathCombine("") + "\\");
SqliteConnection connection = new SqliteConnection(_connectionString);
connection.Open();
var migrationManager = new MigrationManager(this, migratorName, validateTables);
migrationManager.DetermineOperation();
migrationManager.ExecuteOperation();
connection.Close();
if (needsUTFConverted && _hadToConvert)
{
string file = connectionString.Split(new[] { "Data Source=", "," }, StringSplitOptions.RemoveEmptyEntries)[1].Substring(7);
if (File.Exists(file))
{
//UTF16 db, gotta convert it
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + file + ";version=3;UseUTF16Encoding=True");
conn.Open();
var RetVal = new List<string>();
using (var cmd = new System.Data.SQLite.SQLiteCommand("SELECT name FROM Sqlite_master", conn))
{
using (IDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
for (int i = 0; i < rdr.FieldCount; i++)
{
RetVal.Add(rdr.GetValue(i).ToString());
}
}
}
}
foreach (string table in RetVal)
{
if (TableExists(table) && !table.StartsWith("sqlite") && !table.StartsWith("idx_") && table != "aurora_migrator_version")
{
var retVal = new List<object[]>();
using (var cmd = new System.Data.SQLite.SQLiteCommand("SELECT * FROM " + table, conn))
{
using (IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
List<object> obs = new List<object>();
for (int i = 0; i < reader.FieldCount; i++)
{
Type r = reader[i].GetType();
if (r == typeof(DBNull))
obs.Add(null);
else
obs.Add(reader[i].ToString());
}
retVal.Add(obs.ToArray());
}
}
}
try
{
if(retVal.Count > 0)
InsertMultiple(table, retVal);
}
catch { }
}
}
}
}
}