public PrepareEventGraphRunSimple(double time, double speed, int sessionID, int personID, string table, string type)
{
Sqlite.Open();
//obtain data
runsAtSQL = SqliteRun.SelectRuns(true, sessionID, personID, type,
Sqlite.Orders_by.ID_DESC, 10); //select only last 10
string sqlSelect = "distance/time";
personMAXAtSQLAllSessions = SqliteSession.SelectMAXEventsOfAType(true, -1, personID, table, type, sqlSelect);
personMAXAtSQL = SqliteSession.SelectMAXEventsOfAType(true, sessionID, personID, table, type, sqlSelect);
sessionMAXAtSQL = SqliteSession.SelectMAXEventsOfAType(true, sessionID, -1, table, type, sqlSelect);
//distancePersonAVGAtSQL = SqliteSession.SelectAVGEventsOfAType(true, sessionID, personID, table, type, "distance");
//distanceSessionAVGAtSQL = SqliteSession.SelectAVGEventsOfAType(true, sessionID, -1, table, type, "distance");
//better to know speed like:
//SELECT AVG(distance/time) from run; than
//SELECT AVG(distance) / SELECT AVG(time)
//first is ok, because is the speed AVG
//2nd is not good because it tries to do an AVG of all distances and times
personAVGAtSQL = SqliteSession.SelectAVGEventsOfAType(true, sessionID, personID, table, type, sqlSelect);
sessionAVGAtSQL = SqliteSession.SelectAVGEventsOfAType(true, sessionID, -1, table, type, sqlSelect);
this.time = time;
this.speed = speed;
Sqlite.Close();
}
/*
* ReactionTime class methods
*/
public static int Insert(bool dbconOpened, string tableName, string uniqueID, int personID, int sessionID, string type, double time, string description, int simulated)
{
if (!dbconOpened)
{
Sqlite.Open();
}
if (uniqueID == "-1")
{
uniqueID = "NULL";
}
dbcmd.CommandText = "INSERT INTO " + tableName +
" (uniqueID, personID, sessionID, type, time, description, simulated)" +
" VALUES (" + uniqueID + ", "
+ personID + ", " + sessionID + ", \"" + type + "\", "
+ Util.ConvertToPoint(time) + ", \"" + description + "\", " + simulated + ")";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
//int myLast = dbcon.LastInsertRowId;
//http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
string myString = @"select last_insert_rowid()";
dbcmd.CommandText = myString;
int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
if (!dbconOpened)
{
Sqlite.Close();
}
return(myLast);
}
/*
* class methods
*/
public static void Insert(bool dbconOpened, ExecuteAutoSQL eaSQL)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "INSERT INTO " + Constants.ExecuteAutoTable +
" (uniqueID, name, mode, description, " +
" serie1IDs, serie2IDs, serie3IDs, " +
" future1, future2, future3)" +
" VALUES ( NULL, \"" +
eaSQL.name + "\", \"" + eaSQL.Mode.ToString() + "\", \"" + eaSQL.Description + "\", \"" +
eaSQL.SerieIDsToStr(eaSQL.Serie1IDs) + "\", \"" +
eaSQL.SerieIDsToStr(eaSQL.Serie2IDs) + "\", \"" +
eaSQL.SerieIDsToStr(eaSQL.Serie3IDs) + "\", " +
"\"\", \"\", \"\")"; //future1, future2, future3
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
if (!dbconOpened)
{
Sqlite.Close();
}
}
//Called from new methods were dbcon is opened
public static string Select(string myName, bool dbconOpened)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "SELECT value FROM " + Constants.PreferencesTable +
" WHERE name == \"" + myName + "\"";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
//SqliteDataReader reader;
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
string myReturn = "0";
if (reader.Read())
{
myReturn = reader[0].ToString();
}
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(myReturn);
}
public static int Insert1RM(bool dbconOpened, int personID, int sessionID, int exerciseID, double load1RM)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "INSERT INTO " + Constants.Encoder1RMTable +
" (uniqueID, personID, sessionID, exerciseID, load1RM, future1, future2, future3)" +
" VALUES (NULL, " + personID + ", " + sessionID + ", " +
exerciseID + ", " + Util.ConvertToPoint(load1RM) + ", \"\",\"\",\"\")";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
//int myLast = dbcon.LastInsertRowId;
//http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
string myString = @"select last_insert_rowid()";
dbcmd.CommandText = myString;
int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
if (!dbconOpened)
{
Sqlite.Close();
}
return(myLast);
}
//checks if there are Rjs with different number of TCs than TFs
//then repair database manually, and look if the jump is jumpLimited, and how many jumps there are defined
public static void FindBadRjs()
{
Sqlite.Open();
dbcmd.CommandText = "SELECT uniqueID, tcstring, tvstring, jumps, limited FROM jumpRj";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
reader.Read();
while (reader.Read())
{
if (Util.GetNumberOfJumps(reader[1].ToString(), true) != Util.GetNumberOfJumps(reader[2].ToString(), true))
{
LogB.Error(string.Format("Problem with jumpRj: {0}, tcstring{1}, tvstring{2}, jumps{3}, limited{4}",
reader[0].ToString(),
Util.GetNumberOfJumps(reader[1].ToString(), true).ToString(),
Util.GetNumberOfJumps(reader[2].ToString(), true).ToString(),
reader[3].ToString(), reader[4].ToString()));
}
}
reader.Close();
Sqlite.Close();
}
public static void Update(bool dbconOpened, ForceSensorExercise ex)
{
if (!dbconOpened)
{
Sqlite.Open();
}
/*
* string uniqueIDStr = "NULL";
* if(ex.UniqueID != -1)
* uniqueIDStr = ex.UniqueID.ToString();
*/
dbcmd.CommandText = "UPDATE " + table + " SET " +
" name = \"" + ex.Name +
"\", percentBodyWeight = " + ex.PercentBodyWeight +
", resistance = \"" + ex.Resistance +
"\", angleDefault = " + ex.AngleDefault +
", description = \"" + ex.Description +
"\" WHERE uniqueID = " + ex.UniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
if (!dbconOpened)
{
Sqlite.Close();
}
}
//public static int InsertPing(ServerPing ping)
public static int InsertPing(bool dbconOpened, int evaluatorID, string cjVersion, string osVersion, string ip, DateTime date)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string uniqueID = "NULL";
string myString = "INSERT INTO " + Constants.ServerPingTable +
" (uniqueID, evaluatorID, cjVersion, osVersion, IP, date) VALUES (" +
uniqueID + ", " + evaluatorID + ", \"" +
cjVersion + "\", \"" + osVersion + "\", \"" +
ip + "\", \"" + UtilDate.ToSql(date) + "\")";
dbcmd.CommandText = myString;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
//int myLast = dbcon.LastInsertRowId;
//http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
myString = @"select last_insert_rowid()";
dbcmd.CommandText = myString;
int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
if (!dbconOpened)
{
Sqlite.Close();
}
return(myLast);
}
public static int Insert(bool dbconOpened, SqliteCommand mycmd, int sportID, string speciallityName)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string myString = "INSERT INTO " + Constants.SpeciallityTable +
" (uniqueID, sportID, name) VALUES (NULL, " + sportID + ", \"" + speciallityName + "\")";
mycmd.CommandText = myString;
LogB.SQL(mycmd.CommandText.ToString());
mycmd.ExecuteNonQuery();
//int myLast = dbcon.LastInsertRowId;
//http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
myString = @"select last_insert_rowid()";
mycmd.CommandText = myString;
int myLast = Convert.ToInt32(mycmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
if (!dbconOpened)
{
Sqlite.Close();
}
return(myLast);
}
public static string [] SelectEvaluators(bool addAnyString)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT " +
Constants.ServerEvaluatorTable + ".uniqueID, " +
Constants.ServerEvaluatorTable + ".name " +
" FROM " + Constants.ServerEvaluatorTable + ", " + Constants.SessionTable +
" WHERE " + Constants.ServerEvaluatorTable + ".uniqueID = " + Constants.SessionTable + ".evaluatorID" +
" GROUP BY " + Constants.ServerEvaluatorTable + ".uniqueID";
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ArrayList evals = new ArrayList();
if (addAnyString)
{
evals.Add(Constants.AnyID.ToString() + ":" + Constants.Any);
}
while (reader.Read())
{
evals.Add(reader[0].ToString() + ":" + reader[1].ToString());
}
reader.Close();
Sqlite.Close();
return(Util.ArrayListToString(evals));
}
/*
* this is only called on client
*/
public static string [] StatsMine()
{
ArrayList stats = new ArrayList();
Sqlite.Open();
/*
* is good to add the string stuff like "Pings"
* because then client will show this data or not
* depending if it matches what want to show.
* Maintain the ':' as separator
*/
stats.Add("Sessions:" + Sqlite.CountCondition(Constants.SessionTable, true, "serverUniqueID", ">", "0").ToString());
stats.Add("Persons:" + Sqlite.CountCondition(Constants.PersonTable, true, "serverUniqueID", ">", "0").ToString());
stats.Add("Jumps:" + Sqlite.CountCondition(Constants.JumpTable, true, "simulated", ">", "0").ToString());
stats.Add("JumpsRj:" + Sqlite.CountCondition(Constants.JumpRjTable, true, "simulated", ">", "0").ToString());
stats.Add("Runs:" + Sqlite.CountCondition(Constants.RunTable, true, "simulated", ">", "0").ToString());
stats.Add("RunsInterval:" + Sqlite.CountCondition(Constants.RunIntervalTable, true, "simulated", ">", "0").ToString());
stats.Add("ReactionTimes:" + Sqlite.CountCondition(Constants.ReactionTimeTable, true, "simulated", ">", "0").ToString());
stats.Add("Pulses:" + Sqlite.CountCondition(Constants.PulseTable, true, "simulated", ">", "0").ToString());
stats.Add("MultiChronopic:" + Sqlite.CountCondition(Constants.MultiChronopicTable, true, "simulated", ">", "0").ToString());
Sqlite.Close();
string [] statsString = Util.ArrayListToString(stats);
return(statsString);
}
//when client selects in it's DB, there's only a row with uniqueID: 1
//if confiable is read on client, it will be also checked on server
public static ServerEvaluator SelectEvaluator(int myUniqueID)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT * FROM " + Constants.ServerEvaluatorTable + " WHERE uniqueID == " + myUniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
ServerEvaluator myEval = new ServerEvaluator();
//will return a -1 on uniqueID to know that evaluator data is not in the database
myEval.UniqueID = -1;
while (reader.Read())
{
myEval.UniqueID = Convert.ToInt32(reader[0].ToString());
myEval.Code = reader[1].ToString();
myEval.Name = reader[2].ToString();
myEval.Email = reader[3].ToString();
myEval.DateBorn = UtilDate.FromSql(reader[4].ToString());
myEval.CountryID = Convert.ToInt32(reader[5].ToString());
myEval.Chronometer = reader[6].ToString();
myEval.Device = reader[7].ToString();
myEval.Comments = reader[8].ToString();
myEval.Confiable = Util.IntToBool(Convert.ToInt32(reader[9].ToString()));
}
reader.Close();
Sqlite.Close();
return(myEval);
}
public static void UpdateEvaluator(bool dbconOpened, int uniqueID, string code, string name, string email, DateTime dateBorn,
int countryID, string chronometer, string device, string comments, bool confiable)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "UPDATE " + Constants.ServerEvaluatorTable + " " +
" SET code = \"" + code +
"\" , name = \"" + name +
"\" , email = \"" + email +
"\" , dateBorn = \"" + UtilDate.ToSql(dateBorn) +
"\" , countryID = " + countryID +
", chronometer = \"" + chronometer +
"\", device = \"" + device +
"\", comments = \"" + comments +
//"\", confiable = " + Util.BoolToInt(confiable) + //security: update cannot change confiable
"\" WHERE uniqueID == " + uniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
if (!dbconOpened)
{
Sqlite.Close();
}
}
public PrepareEventGraphReactionTime(double time, int sessionID, int personID, string table, string type)
{
Sqlite.Open();
//obtain data
rtsAtSQL = SqliteReactionTime.SelectReactionTimes(true, sessionID, personID, type,
Sqlite.Orders_by.ID_DESC, 10); //select only last 10
personMAXAtSQL = SqliteSession.SelectMAXEventsOfAType(
true, sessionID, personID, table, type, "time");
sessionMAXAtSQL = SqliteSession.SelectMAXEventsOfAType(
true, sessionID, -1, table, type, "time");
personMINAtSQL = SqliteSession.SelectMINEventsOfAType(
true, sessionID, personID, table, type, "time");
sessionMINAtSQL = SqliteSession.SelectMINEventsOfAType(
true, sessionID, -1, table, type, "time");
personAVGAtSQL = SqliteSession.SelectAVGEventsOfAType(
true, sessionID, personID, table, type, "time");
sessionAVGAtSQL = SqliteSession.SelectAVGEventsOfAType(
true, sessionID, -1, table, type, "time");
Sqlite.Close();
this.time = time;
}
public static bool ExistsAndItsNotMe(int uniqueID, string personName)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT uniqueID FROM " + Constants.PersonTable +
" WHERE LOWER(" + Constants.PersonTable + ".name) == LOWER(\"" + personName + "\")" +
" AND uniqueID != " + uniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
bool exists = new bool();
exists = false;
if (reader.Read())
{
exists = true;
//LogB.SQL("valor {0}", reader[0].ToString());
}
//LogB.SQL("exists = {0}", exists.ToString());
reader.Close();
Sqlite.Close();
return(exists);
}
public static string Select(bool dbconOpened, int uniqueID)
{
if (uniqueID == -1)
{
return("");
}
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "SELECT name FROM " + Constants.SpeciallityTable + " WHERE uniqueID == " + uniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
reader.Read();
string speciallityName = reader[0].ToString(); //name
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(Catalog.GetString(speciallityName));
}
public static JumpRj SelectJumpData(string tableName, int uniqueID, bool dbconOpened)
{
//tableName is jumpRj or tempJumpRj
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "SELECT * FROM " + tableName + " WHERE uniqueID == " + uniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
reader.Read();
JumpRj myJump = new JumpRj(DataReaderToStringArray(reader, 18));
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(myJump);
}
//tableName is jumpType or jumpRjType
public static bool HasWeight(string tableName, string typeName)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT weight " +
" FROM " + tableName +
" WHERE name == \"" + typeName + "\"";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
bool hasWeight = false;
while (reader.Read())
{
if (reader[0].ToString() == "1")
{
hasWeight = true;
LogB.SQL("found type: hasWeight");
}
else
{
LogB.SQL("found type: NO hasWeight");
}
}
reader.Close();
Sqlite.Close();
return(hasWeight);
}
//undefined defaultAngle will be 1000
//note execution can have a different angle than the default angle
public static void Insert(bool dbconOpened, int uniqueID, string name, int percentBodyWeight,
string resistance, int angleDefault, string description)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string uniqueIDStr = "NULL";
if (uniqueID != -1)
{
uniqueIDStr = uniqueID.ToString();
}
dbcmd.CommandText = "INSERT INTO " + table +
" (uniqueID, name, percentBodyWeight, resistance, angleDefault, description)" +
" VALUES (" + uniqueIDStr + ", \"" + name + "\", " + percentBodyWeight + ", \"" +
resistance + "\", " + angleDefault + ", \"" + description + "\")";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
if (!dbconOpened)
{
Sqlite.Close();
}
}
//we know if it has fall if it starts in
public static bool HasFall(string tableName, string typeName)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT startIn " +
" FROM " + tableName +
" WHERE name == \"" + typeName + "\"";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
bool hasFall = true;
while (reader.Read())
{
if (reader[0].ToString() == "1")
{
hasFall = false;
}
}
reader.Close();
Sqlite.Close();
return(hasFall);
}
public static RunInterval SelectRunData(string tableName, int uniqueID, bool dbconOpened)
{
//tableName can be runInterval or tempRunInterval
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "SELECT * FROM " + tableName + " WHERE uniqueID == " + uniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
reader.Read();
RunInterval myRun = new RunInterval(DataReaderToStringArray(reader, 13));
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(myRun);
}
//when a session is NOT KNOWN, then select atrribute of last session
//select doubles
public static double SelectAttribute(int personID, string attribute)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT " + attribute + ", sessionID FROM " + Constants.PersonSessionTable +
" WHERE personID == " + personID +
"ORDER BY sessionID DESC LIMIT 1";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
double myReturn = 0;
if (reader.Read())
{
myReturn = Convert.ToDouble(Util.ChangeDecimalSeparator(reader[0].ToString()));
}
reader.Close();
Sqlite.Close();
return(myReturn);
}
public static PulseType SelectAndReturnPulseType(string typeName)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT * " +
" FROM " + Constants.PulseTypeTable +
" WHERE name = \"" + typeName +
"\" ORDER BY uniqueID";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
PulseType myPulseType = new PulseType();
while (reader.Read())
{
myPulseType.Name = reader[1].ToString();
myPulseType.FixedPulse = Convert.ToDouble(reader[2].ToString());
myPulseType.TotalPulsesNum = Convert.ToInt32(reader[3]);
}
reader.Close();
Sqlite.Close();
return(myPulseType);
}
public static bool PersonSelectExistsInSession(int myPersonID, int mySessionID)
{
Sqlite.Open();
dbcmd.CommandText = "SELECT * FROM " + Constants.PersonSessionTable +
" WHERE personID == " + myPersonID +
" AND sessionID == " + mySessionID;
LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
bool exists = new bool();
exists = false;
while (reader.Read())
{
exists = true;
}
reader.Close();
Sqlite.Close();
return(exists);
}
public static ReactionTime SelectReactionTimeData(int uniqueID, bool dbconOpened)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "SELECT * FROM " + Constants.ReactionTimeTable + " WHERE uniqueID == " + uniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
reader.Read();
ReactionTime myRT = new ReactionTime(DataReaderToStringArray(reader, 7));
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(myRT);
}
//use this in the future:
public static List <PersonSession> SelectPersonSessionList(int sessionID)
{
string tps = Constants.PersonSessionTable;
Sqlite.Open();
dbcmd.CommandText = "SELECT " + tps + ".*" +
" FROM " + tps +
" WHERE " + tps + ".sessionID == " + sessionID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
List <PersonSession> list = new List <PersonSession>();
while (reader.Read())
{
PersonSession ps = new PersonSession(
Convert.ToInt32(reader[0].ToString()), //uniqueID
Convert.ToInt32(reader[1].ToString()), //personID
Convert.ToInt32(reader[2].ToString()), //sessionID
Convert.ToDouble(Util.ChangeDecimalSeparator(reader[3].ToString())), //height
Convert.ToDouble(Util.ChangeDecimalSeparator(reader[4].ToString())), //weight
Convert.ToInt32(reader[5].ToString()), //sportID
Convert.ToInt32(reader[6].ToString()), //speciallityID
Convert.ToInt32(reader[7].ToString()), //practice
reader[8].ToString() //comments
);
list.Add(ps);
}
reader.Close();
Sqlite.Close();
return(list);
}
public static int Insert(bool dbconOpened, string tableName, string uniqueID, string name, string place, DateTime date, int personsSportID, int personsSpeciallityID, int personsPractice, string comments, int serverUniqueID)
{
if (!dbconOpened)
{
Sqlite.Open();
}
if (uniqueID == "-1")
{
uniqueID = "NULL";
}
dbcmd.CommandText = "INSERT INTO " + tableName + " (uniqueID, name, place, date, personsSportID, personsSpeciallityID, personsPractice, comments, serverUniqueID)" +
" VALUES (" + uniqueID + ", \""
+ name + "\", \"" + place + "\", \"" + UtilDate.ToSql(date) + "\", " +
personsSportID + ", " + personsSpeciallityID + ", " +
personsPractice + ", \"" + comments + "\", " +
serverUniqueID + ")";
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
//int myLast = dbcon.LastInsertRowId;
//http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
string myString = @"select last_insert_rowid()";
dbcmd.CommandText = myString;
int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
if (!dbconOpened)
{
Sqlite.Close();
}
return(myLast);
}
public static bool PersonExistsInPS(bool dbconOpened, int personID)
{
if (!dbconOpened)
{
Sqlite.Open();
}
dbcmd.CommandText = "SELECT * FROM " + Constants.PersonSessionTable +
" WHERE personID == " + personID;
//LogB.SQL(dbcmd.CommandText.ToString());
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
bool exists = new bool();
exists = false;
if (reader.Read())
{
exists = true;
}
//LogB.SQL(string.Format("personID exists = {0}", exists.ToString()));
reader.Close();
if (!dbconOpened)
{
Sqlite.Close();
}
return(exists);
}
public static void Insert(bool dbconOpened, SqliteCommand mycmd, string code, string nameEnglish, string continent)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string myString = "INSERT INTO " + Constants.CountryTable +
//" (uniqueID, code, name, continent) VALUES (NULL, '" + code + "', '" +
//nameEnglish + "', '" + continent + "')";
//fix bad chars (') :
" (uniqueID, code, name, continent) VALUES (NULL, \"" + code + "\", \"" +
nameEnglish + "\", \"" + continent + "\")";
mycmd.CommandText = myString;
LogB.SQL(mycmd.CommandText.ToString());
mycmd.ExecuteNonQuery();
/*
* //int myLast = dbcon.LastInsertRowId;
* //http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
* myString = @"select last_insert_rowid()";
* mycmd.CommandText = myString;
* int myLast = Convert.ToInt32(dbcmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
*/
if (!dbconOpened)
{
Sqlite.Close();
}
}
//Called from initialize
public static int Insert(bool dbconOpened, SqliteCommand mycmd, string uniqueID, string name, bool userDefined, bool hasSpeciallities, string graphLink)
{
if (!dbconOpened)
{
Sqlite.Open();
}
if (uniqueID == "-1")
{
uniqueID = "NULL";
}
string myString = "INSERT INTO " + Constants.SportTable +
" (uniqueID, name, userDefined, hasSpeciallities, graphLink) VALUES (" + uniqueID + ", \"" + name + "\", " +
Util.BoolToInt(userDefined) + ", " + Util.BoolToInt(hasSpeciallities) + ", \"" + graphLink + "\")";
mycmd.CommandText = myString;
LogB.SQL(mycmd.CommandText.ToString());
mycmd.ExecuteNonQuery();
//int myLast = dbcon.LastInsertRowId;
//http://stackoverflow.com/questions/4341178/getting-the-last-insert-id-with-sqlite-net-in-c
myString = @"select last_insert_rowid()";
mycmd.CommandText = myString;
int myLast = Convert.ToInt32(mycmd.ExecuteScalar()); // Need to type-cast since `ExecuteScalar` returns an object.
if (!dbconOpened)
{
Sqlite.Close();
}
return(myLast);
}