public string ToSQLInsertString()
{
return(uniqueID.ToString() + ", '" + name + "', '" + sex + "', '" +
UtilDate.ToSql(dateBorn) + "', " + race + ", " + countryID + ", '" +
description + "', '" + future1 + "', '', " + //future1, future2
serverUniqueID);
}
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 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);
}
//can be "Constants.PersonOldTable" or "Constants.ConvertTempTable"
//temp is used to modify table between different database versions if needed
public static int Insert(bool dbconOpened, string tableName, string uniqueID, string name, string sex, DateTime dateBorn, double height, double weight, int sportID, int speciallityID, int practice, string description, int race, int countryID, int serverUniqueID)
{
if (!dbconOpened)
{
dbcon.Open();
}
if (uniqueID == "-1")
{
uniqueID = "NULL";
}
string myString = "INSERT INTO " + tableName +
" (uniqueID, name, sex, dateBorn, height, weight, sportID, speciallityID, practice, description, race, countryID, serverUniqueID) VALUES (" + uniqueID + ", '" +
name + "', '" + sex + "', '" + UtilDate.ToSql(dateBorn) + "', " +
Util.ConvertToPoint(height) + ", " + "-1" + ", " + //"-1" is weight because it's defined in personSesionWeight for allow change between sessions
sportID + ", " + speciallityID + ", " + practice + ", '" + description + "', " +
race + ", " + countryID + ", " + serverUniqueID + ")";
dbcmd.CommandText = myString;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
int myReturn = -10000; //dbcon.LastInsertRowId;
if (!dbconOpened)
{
dbcon.Close();
}
return(myReturn);
}
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();
}
}
/* OLD STUFF */
/*
* don't do more like this, use Sqlite.convertTables()
*/
//change DB from 0.55 to 0.56
protected internal static void convertTableAddingSportStuff()
{
ArrayList myArray = new ArrayList(2);
//1st create a temp table
//createTable(Constants.ConvertTempTable);
SqliteSessionOld sqliteSessionObject = new SqliteSessionOld();
sqliteSessionObject.createTable(Constants.ConvertTempTable);
//2nd copy all data from session table to temp table
dbcmd.CommandText = "SELECT * " +
"FROM " + Constants.SessionTable + " ORDER BY uniqueID";
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
while (reader.Read())
{
Session mySession = new Session(reader[0].ToString(), reader[1].ToString(),
reader[2].ToString(), UtilDate.FromSql(reader[3].ToString()),
1, //sport undefined
-1, //speciallity undefined
-1, //practice level undefined
reader[4].ToString(), //comments
Constants.ServerUndefinedID
);
myArray.Add(mySession);
}
reader.Close();
foreach (Session mySession in myArray)
{
InsertOld(true, Constants.ConvertTempTable,
mySession.Name, mySession.Place, UtilDate.ToSql(mySession.Date),
mySession.PersonsSportID, mySession.PersonsSpeciallityID, mySession.PersonsPractice, mySession.Comments);
}
//3rd drop table sessions
Sqlite.dropTable(Constants.SessionTable);
//4d create table persons (now with sport related stuff
//createTable(Constants.SessionTable);
sqliteSessionObject.createTable(Constants.SessionTable);
//5th insert data in sessions (with sport related stuff)
foreach (Session mySession in myArray)
{
InsertOld(true, Constants.SessionTable,
mySession.Name, mySession.Place, UtilDate.ToSql(mySession.Date),
mySession.PersonsSportID, mySession.PersonsSpeciallityID, mySession.PersonsPractice, mySession.Comments);
}
//6th drop temp table
Sqlite.dropTable(Constants.ConvertTempTable);
}
public static void Update(int uniqueID, string name, string place, DateTime date, int personsSportID, int personsSpeciallityID, int personsPractice, string comments)
{
//TODO: serverUniqueID (but cannot be changed in gui/edit, then not need now)
Sqlite.Open();
dbcmd.CommandText = "UPDATE " + Constants.SessionTable + " " +
" SET name = \"" + name +
"\" , date = \"" + UtilDate.ToSql(date) +
"\" , place = \"" + place +
"\" , personsSportID = " + personsSportID +
", personsSpeciallityID = " + personsSpeciallityID +
", personsPractice = " + personsPractice +
", comments = \"" + comments +
"\" WHERE uniqueID == " + uniqueID;
dbcmd.ExecuteNonQuery();
Sqlite.Close();
}
public static void Update(Person myPerson)
{
Sqlite.Open();
dbcmd.CommandText = "UPDATE " + Constants.PersonTable +
" SET name = \"" + myPerson.Name +
"\", sex = \"" + myPerson.Sex +
"\", dateborn = \"" + UtilDate.ToSql(myPerson.DateBorn) +
"\", race = " + myPerson.Race +
", countryID = " + myPerson.CountryID +
", description = \"" + myPerson.Description +
"\", serverUniqueID = " + myPerson.ServerUniqueID +
" WHERE uniqueID == " + myPerson.UniqueID;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
Sqlite.Close();
}
public static int InsertEvaluator(bool dbconOpened, string code, string name, string email, DateTime dateBorn,
int countryID, string chronometer, string device, string comments, bool confiable)
{
if (!dbconOpened)
{
Sqlite.Open();
}
string uniqueID = "NULL";
string myString = "INSERT INTO " + Constants.ServerEvaluatorTable +
" (uniqueID, code, name, email, dateBorn, countryID, chronometer, device, comments, confiable) VALUES (" +
uniqueID + ", \"" +
code + "\", \"" + name + "\", \"" +
email + "\", \"" + UtilDate.ToSql(dateBorn) + "\", " +
countryID + ", \"" + chronometer + "\", \"" +
device + "\", \"" + comments + "\", " +
//Util.BoolToInt(confiable) +
Util.BoolToInt(false) + //security: cannot directly insert a confiable person
")";
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, string uniqueID, string name, string sex, DateTime dateBorn,
int race, int countryID, string description, string future1, int serverUniqueID)
{
LogB.SQL("going to insert");
if (!dbconOpened)
{
Sqlite.Open();
}
if (uniqueID == "-1")
{
uniqueID = "NULL";
}
// -----------------------
//ATTENTION: if this changes, change the Person.ToSQLInsertString()
// -----------------------
string myString = "INSERT INTO " + Constants.PersonTable +
" (uniqueID, name, sex, dateBorn, race, countryID, description, future1, future2, serverUniqueID) VALUES (" + uniqueID + ", \"" +
name + "\", \"" + sex + "\", \"" + UtilDate.ToSql(dateBorn) + "\", " +
race + ", " + countryID + ", \"" + description + "\", \"" + future1 + "\", \"\", " + serverUniqueID + ")";
dbcmd.CommandText = myString;
LogB.SQL(dbcmd.CommandText.ToString());
dbcmd.ExecuteNonQuery();
//int myLast = -10000; //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);
}
