public virtual bool CreateDatabase( string sFile, bool bKeepOpen = false )
{
myDatabase = new SqliteConnection();
try {
if( System.IO.File.Exists(sFile) ) {
if( bKeepOpen == true ) {
myDatabase.ConnectionString = "Data Source=" + sFile + ";";
myDatabase.Open();
}
return false;
}
myDatabase.ConnectionString = "Data Source=" + sFile + ";";
myDatabase.Open();
if( bKeepOpen == false ) {
myDatabase.Close();
myDatabase.Dispose();
}
return true;
} catch {
return false;
}
}
public void CreateDataBase(string dbPath, string pwd)
{
if (!File.Exists(dbPath))
{
SqliteConnection.CreateFile(dbPath);
if (string.IsNullOrEmpty(pwd))
{
using (SqliteConnection conn = new SqliteConnection("Data Source=" + dbPath))
{
try
{
conn.SetPassword(pwd);
}
catch
{
conn.Close();
throw;
}
finally
{
conn.Close();
}
}
}
}
else
throw new Exception("已经存在名叫：" + dbPath + "的数据库！");
}
public List<dateData> LoadHistory_DB(Quote q)
{
List<dateData> dataList = new List<dateData>();
string tblNm = GetTableNameHistory_DB(q);
if (!TableExist(tblNm))
return dataList;
SqliteConnection conn = null;
string sql = "";
try {
conn = new SqliteConnection(souce);
conn.Open();
SqliteCommand cmd = conn.CreateCommand();
sql = "select date, volume, open, high, low, close from " + tblNm + " order by date desc;";
cmd.CommandText = sql;
SqliteDataReader rdr = cmd.ExecuteReader();
while (rdr.Read()) {
dateData dd = new dateData();
dd._date = rdr.GetInt64(0);
dd._indic._volume = rdr.GetInt64(1);
dd._price._open = rdr.GetDouble(2);
dd._price._high = rdr.GetDouble(3);
dd._price._low = rdr.GetDouble(4);
dd._price._close = rdr.GetDouble(5);
dataList.Add(dd);
}
} catch (Exception e) {
Output.LogException(" sql (" + sql + ") error : " + e.Message);
}
if (conn != null)
conn.Close();
return dataList;
}
/// <summary>
/// Loads all empty quotes.
/// </summary>
/// <returns>
/// The all quotes only have basic information like code.
/// </returns>
public List<Quote> LoadQuotesInfo_DB()
{
List<Quote> allQ = new List<Quote>();
SqliteConnection conn = null;
string sql = "";
try {
conn = new SqliteConnection(souce);
conn.Open();
SqliteCommand cmd = conn.CreateCommand();
sql = "select si_market, si_code, si_name from " + TableNameInfo + ";";
cmd.CommandText = sql;
SqliteDataReader rdr = cmd.ExecuteReader();
information info = information.EMPTY;
int count = 0;
while (rdr.Read()) {
info._market._value = (market.type)rdr.GetInt32(0);
info.CodeInt = rdr.GetInt32(1);
info._name = rdr.GetString(2);
Quote q = new Quote(info);
allQ.Add(q);
Output.Log("" + ++count + " quotes loaded - " + q.Describe);
}
} catch (Exception e) {
Output.LogException("sql(" + sql + ") error : " + e.Message);
}
if (conn != null)
conn.Close();
return allQ;
}
//constructor
static folios()
{
try {
//conexion a DB
var connection = new SqliteConnection(dbSource);
connection.Open();
//crear query
var query = connection.CreateCommand();
query.CommandText = "select consecutivo from folios where fecha='" + fecha + "'";
//inicia en el folio de la DB
generarConsecutivo=Convert.ToInt16(query.ExecuteScalar());
//si el valor es 0, entonces creamos un nuevo renglon para ese dia
//esto del 0 no necesariamente funciona en VistaDB
if(generarConsecutivo.Equals(0))
{
generarConsecutivo=1;
query = connection.CreateCommand();
query.CommandText = "insert into folios (fecha,consecutivo) values ('" + fecha + "'," + generarConsecutivo.ToString() + ")";
query.ExecuteNonQuery();
}
//cerrar conexion a DB
connection.Close();
} catch(Exception ex) {
//en caso de error en la base de datos empezamos en 1
generarConsecutivo=1;
}
}
public int DeletePhrase(Phrase item)
{
int r = 0;
lock (locker) {
if (item.id != 0) {
var connection = new SqliteConnection ("Data Source=" + path);
connection.Open ();
using (var command = connection.CreateCommand ()) {
command.CommandText = "Select * from [Phrase] where [id] = ?";
command.Parameters.Add (new SqliteParameter (DbType.String) { Value = item.id });
var result = command.ExecuteReader ();
if (result.HasRows) {
command.Dispose ();
command.CommandText = "Delete from [Phrase] where [id] = ?";
command.Parameters.Add(new SqliteParameter (DbType.String) { Value = item.categoryId });
r = command.ExecuteNonQuery ();
}
connection.Close ();
}
}
}
return r;
}
public static SqliteConnection GetConnection()
{
var documents = Environment.GetFolderPath (
Environment.SpecialFolder.Personal);
string db = Path.Combine (documents, "mydb.db3");
bool exists = File.Exists (db);
if (!exists)
SqliteConnection.CreateFile (db);
var conn = new SqliteConnection ("Data Source=" + db);
if (!exists) {
var commands = new[] {
"CREATE TABLE People (Id INTEGER NOT NULL, FirstName ntext, LastName ntext, DateCreated datetime)",
"INSERT INTO People (Id, FirstName, LastName, DateCreated) VALUES (1, 'Homer', 'Simpson', '2007-01-01 10:00:00')",
};
foreach (var cmd in commands){
using (var c = conn.CreateCommand()) {
c.CommandText = cmd;
c.CommandType = CommandType.Text;
conn.Open ();
c.ExecuteNonQuery ();
conn.Close ();
}
}
}
return conn;
}
public void UpdateAllAthleteResults(int athleteID, List <Result> results)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "DELETE FROM [Result] WHERE AthleteID=" + athleteID;
command.ExecuteNonQuery();
command.Dispose();
foreach (var result in results)
{
result.AthleteID = athleteID;
addResult(conn, trans, result);
}
trans.Commit();
}
catch
{
trans.Rollback();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public string proDataExc(string[] strTT)
{
try
{
if (strTT.Length < 1) { return "No SQL"; }
string DatabaseName = "PUB.db3";
string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
string db = System.IO.Path.Combine(documents, DatabaseName);
var conn = new SqliteConnection("Data Source=" + db);
var sqlitecmd = conn.CreateCommand();
conn.Open();
sqlitecmd.CommandType = CommandType.Text;
for (int j = 0; j < strTT.Length; j++)
{
if (strTT[j] == "") { continue; }
sqlitecmd.CommandText = strTT[j];
sqlitecmd.ExecuteNonQuery();
}
conn.Close();
conn.Dispose();
return "";
}
catch (Exception Ex)
{
return Ex.ToString();
}
}
public List <int> GetAthleteNamesFromResults(bool includeDeleted)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
string sql = "SELECT AthleteID FROM [Result]";
if (includeDeleted == false)
{
sql += " WHERE IsDeleted=0";
}
sql += " ORDER BY [Date] DESC";
List <int> ids = new List <int>();
var command = new SqliteCommand(sql, conn);
var reader = command.ExecuteReader();
while (reader.Read())
{
int id = (int)reader["AthleteID"];
ids.Add(id);
}
return(ids);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
internal static void DatabaseSQLAdd(string query) //삽입이라고 썼지만 삭제도 가능
{
IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());
dbConnection.Open(); // DB 열기
dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = query; // 쿼리 입력
try
{
dbCommand.ExecuteNonQuery(); // 쿼리 실행
}
catch (Exception e)
{
Debug.LogError(e);
//test.text = e.ToString();
}
dbCommand.Dispose();
dbCommand = null;
dbConnection.Close();
dbConnection = null;
Debug.Log("데이터 삽입 완료");
//test.text = "결과 데이터 삽입 완료";
}
public void DatabaseSQLAdd(string query) //삽입이라고 썼지만 삭제도 가능
{
IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());
dbConnection.Open(); // DB 열기
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = query; // 쿼리 입력
try
{
dbCommand.ExecuteNonQuery(); // 쿼리 실행
}
catch (Exception e)
{
Debug.LogError(e);
test.text = e.ToString();
// 빌드시 에러확인을 위해 넣음 결과패널에 Game Over대신 에러 나오게 하는 코드
test_Result.GetComponent <Text>().fontSize = 30; // 폰트 사이즈 작게 변경 - 이유 : 오류가 길어서
test_Result.text = e.ToString();
}
dbCommand.Dispose();
dbCommand = null;
dbConnection.Close();
dbConnection = null;
Debug.Log("데이터 삽입 완료");
test.text = "결과 데이터 삽입 완료";
}
// Use this for initialization
void Start()
{
string conn = "URI=file:" + Application.dataPath + "/sqlite_userinfo";
IDbConnection dbconn = new SqliteConnection(conn);
dbconn.Open();
IDbCommand dbcmd = dbconn.CreateCommand();
string query = "SELECT idx, name, email FROM userinfo";
dbcmd.CommandText = query;
IDataReader reader = dbcmd.ExecuteReader();
while(reader.Read())
{
int idx = reader.GetInt32(0);
string name = reader.GetString(1);
string email = reader.GetString(2);
Debug.Log("idx : " + idx + ", name : " + name + ", email : " + email);
}
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbconn.Close();
dbconn = null;
}
public void SaveUserTest()
{
var sut = new UserPersister();
var user = new User { Email = "*****@*****.**", FirstName = "Louis",
LastName = "Salin", Password = "******" };
sut.Save(user);
using (var connection = new SqliteConnection("URI=file:Users.db3"))
{
var query = "SELECT * FROM Users WHERE Email='*****@*****.**';";
using (var cmd = connection.CreateCommand())
{
connection.Open();
cmd.CommandText = query;
using (var reader = cmd.ExecuteReader())
{
reader.Read();
reader["FirstName"].ShouldEqual("Louis");
reader["LastName"].ShouldEqual("Salin");
reader["Email"].ShouldEqual("*****@*****.**");
reader["Password"].ShouldEqual("pass123");
reader.Close();
}
connection.Close();
}
}
}
public SqliteVsMonoDSSpeedTests()
{
// create path and filename to the database file.
var documents = Environment.GetFolderPath (
Environment.SpecialFolder.Personal);
_db = Path.Combine (documents, "mydb.db3");
if (File.Exists (_db))
File.Delete (_db);
SqliteConnection.CreateFile (_db);
var conn = new SqliteConnection("URI=" + _db);
using (var c = conn.CreateCommand()) {
c.CommandText = "CREATE TABLE DataIndex (SearchKey INTEGER NOT NULL,Name TEXT NOT NULL,Email Text NOT NULL)";
conn.Open ();
c.ExecuteNonQuery ();
conn.Close();
}
conn.Dispose();
// create path and filename to the database file.
var documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments);
var libraryPath = Path.Combine (documentsPath, "..", "Library");
_dataDirectory = Path.Combine (libraryPath, "MonoDS");
_entity = "PersonEntity";
_serializer = new Serializer();
}
public bool ExcuteTransaction(string sql)
{
var cmds = sql.Split(';');
using (SqliteConnection conn = new SqliteConnection(this.SqlConfig.ConnectionString))
{
conn.Open();
SqliteCommand cmd = new SqliteCommand(conn);
SqliteTransaction tran = conn.BeginTransaction();
try
{
foreach (var cmdSql in cmds)
{
cmd.CommandText = cmdSql;
cmd.ExecuteNonQuery();
}
tran.Commit();
conn.Close();
return true;
}
catch (Exception e)
{
tran.Rollback();
conn.Close();
throw new Exception(e.Message + " sql:" + sql);
}
finally
{
conn.Close();
}
}
}
public static SqliteConnection GetConnection()
{
var documents = Environment.GetFolderPath (
Environment.SpecialFolder.Personal);
string db = Path.Combine (documents, dbName);
bool exists = File.Exists (db);
if (!exists)
SqliteConnection.CreateFile (db);
var conn = new SqliteConnection ("Data Source=" + db);
if (!exists) {
var commands = new[] {
"CREATE TABLE Movie (Id INTEGER NOT NULL, Title ntext, Rating ntext, DateCreated datetime)",
"INSERT INTO Movie (Id, Title, Rating, DateCreated) VALUES (1, 'Traffic', 'R', '2007-01-01 10:00:00')",
"INSERT INTO Movie (Id, Title, Rating, DateCreated) VALUES (2, 'The Breakfast Club', 'R', '2007-01-01 10:00:00')",
};
foreach (var cmd in commands) {
using (var c = conn.CreateCommand()) {
c.CommandText = cmd;
c.CommandType = CommandType.Text;
conn.Open ();
c.ExecuteNonQuery ();
conn.Close ();
}
}
}
return conn;
}
public StepEntryDatabase (string dbPath)
{
var output = "";
path = dbPath;
// create the tables
bool exists = File.Exists (dbPath);
if (!exists) {
using (connection = new SqliteConnection ("Data Source=" + dbPath)) {
connection.Open ();
var commands = new[] {
"CREATE TABLE [Items] (_id INTEGER PRIMARY KEY ASC, Steps BIGINT, Date NTEXT);"
};
foreach (var command in commands) {
using (var c = connection.CreateCommand ()) {
c.CommandText = command;
var i = c.ExecuteNonQuery ();
}
}
connection.Close ();
}
} else {
// already exists, do nothing.
}
Console.WriteLine (output);
}
void Start ()
{
string connectionString = "URI=file:" + Application.dataPath + "/GameMaster"; //Path to database.
IDbConnection dbcon = new SqliteConnection(connectionString) as IDbConnection;
dbcon.Open(); //Open connection to the database.
IDbCommand dbcmd = dbcon.CreateCommand();
dbcmd.CommandText = "SELECT firstname, lastname " + "FROM addressbook";
IDataReader reader = dbcmd.ExecuteReader();
while(reader.Read())
{
string FirstName = reader.GetString (0);
string LastName = reader.GetString (1);
Console.WriteLine("Name: " + FirstName + " " + LastName);
UnityEngine.Debug.LogWarning("Name: " + FirstName + " " + LastName);
}
reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;
}
public void GotoColorfulScene()
{
SceneName = "Colorfull";
playGameUI.SetActive(true);
string connection = "URI=file:" + Application.dataPath + "/StreamingAssets/User.db";
Debug.Log(connection);
IDbConnection dbcon = new Mono.Data.Sqlite.SqliteConnection(connection);
dbcon.Open();
IDbCommand cmnd_read = dbcon.CreateCommand();
IDataReader reader;
string query = "SELECT * FROM my_user";
cmnd_read.CommandText = query;
reader = cmnd_read.ExecuteReader();
int index = 0;
while (reader.Read())
{
//Debug.Log(reader[0].ToString());
GameObject tempGO = Instantiate(rowSample) as GameObject;
tempGO.transform.Find("Name").GetComponent <Text>().text = reader[1].ToString();
tempGO.transform.Find("ID").GetComponent <Text>().text = reader[0].ToString();
tempGO.transform.GetComponent <PlayGame_Button_Control>().id = reader[0].ToString();
tempGO.transform.GetComponent <PlayGame_Button_Control>().sceneName = "Colorfull";
tempGO.transform.SetParent(rowParent.transform);
data.Add(tempGO);
}
dbcon.Close();
reader.Close();
cmnd_read.Dispose();
}
public void DataBaseRead(String query) //DB 읽어오기 - 인자로 쿼리문을 받는다.
{
IDbConnection dbConnection = new SqliteConnection(GetDBFilePath());
dbConnection.Open(); // DB 열기
IDbCommand dbCommand = dbConnection.CreateCommand();
dbCommand.CommandText = query; // 쿼리 입력
IDataReader dataReader = dbCommand.ExecuteReader(); // 쿼리 실행
while (dataReader.Read()) // 쿼리로 돌아온 레코드 읽기
{
Debug.Log(dataReader.GetInt32(5)); // 5번 점수 필드 읽기
int maxScore = dataReader.GetInt32(5);
MaxScore_Text.text = "최고점수 : " + maxScore;
break; // 내림차순 정렬이므로 처음에 한 번만 레코드값을 가져오면 된다.
}
dataReader.Dispose(); // 생성순서와 반대로 닫아줍니다.
dataReader = null;
dbCommand.Dispose();
dbCommand = null;
// DB에는 1개의 쓰레드만이 접근할 수 있고 동시에 접근시 에러가 발생한다. 그래서 Open과 Close는 같이 써야한다.
dbConnection.Close();
dbConnection = null;
}
public void UpdateAllScores(List <Score> scores)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "DELETE FROM [Score]";
command.ExecuteNonQuery();
command.Dispose();
foreach (var score in scores)
{
this.addScore(conn, trans, score);
}
trans.Commit();
}
catch (Exception exc)
{
trans.Rollback();
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public DataStore()
{
#if WHACKDB
File.Delete("GSharp.db3");
#endif
SqliteConnection conn;
if ( !File.Exists("GSharp.db3"))
{
SqliteConnection.CreateFile("GSharp.db3");
using ( conn = new SqliteConnection ("DbLinqProvider=Sqlite;Data Source=GSharp.db3") )
{
var s = Assembly.GetExecutingAssembly().GetManifestResourceStream("GSharp.Data.GSharp.sql");
var sr = new StreamReader(s);
using ( var cmd = conn.CreateCommand() )
{
cmd.CommandText = sr.ReadToEnd();
cmd.CommandType = CommandType.Text;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
conn = new SqliteConnection ("DbLinqProvider=Sqlite;Data Source=GSharp.db3");
database = new Main (conn);
}
public void GetSnookerBests(int athleteID, out int?bestBreakPoints, out int?bestBreakBalls, out int?bestFrameScore)
{
bestBreakPoints = null;
bestBreakBalls = null;
bestFrameScore = null;
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
string sql = "SELECT Count FROM [Result] WHERE AthleteID=" + athleteID + " ORDER BY Count DESC";
var obj1 = this.executeScalarCommand(sql, conn, null);
if (obj1 != null && obj1 != DBNull.Value)
{
bestBreakPoints = (int)obj1;
}
sql = "SELECT Count2 FROM [Result] WHERE AthleteID=" + athleteID + " ORDER BY Count2 DESC";
var obj2 = this.executeScalarCommand(sql, conn, null);
if (obj2 != null && obj2 != DBNull.Value)
{
bestBreakBalls = (int)obj2;
}
bestFrameScore = null;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void DeleteAllData()
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
trans = conn.BeginTransaction();
this.executeNonqueryCommand("DELETE FROM [Athlete]", conn, trans);
this.executeNonqueryCommand("DELETE FROM [Result]", conn, trans);
this.executeNonqueryCommand("DELETE FROM [Score]", conn, trans);
trans.Commit();
}
catch (Exception exc)
{
if (trans != null)
{
trans.Rollback();
}
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
new DatabaseSetup().CreateEmptyAthleteRecord();
}
// Выполняет запрос, и в случае если это был INSERT, возвратит rowid добавленной записи
private int ExecuteSQLQuery(string query, out int lastInsertedRowId)
{
using (var connection = new SqliteConnection($"Data Source={m_fileName}"))
{
connection.Open();
int rowCount;
using (var command = connection.CreateCommand())
{
command.CommandText = query;
rowCount = command.ExecuteNonQuery();
}
// Получаем rowid последней добавленной записи. Важно чтобы он был в пределах того же соединения
// что и INSERT запрос (который предположительно идет прямо перед этим), иначе всегда возвращается 0
using (var command = connection.CreateCommand())
{
command.CommandText = "SELECT last_insert_rowid();";
using (var reader = command.ExecuteReader())
{
Debug.Assert(reader.HasRows);
reader.Read();
lastInsertedRowId = reader.GetInt32(0);
}
}
connection.Close();
return rowCount;
}
}
// http://bugzilla.xamarin.com/show_bug.cgi?id=233
public void Bug233_MonoPInvokeCallback ()
{
var c = new SqliteConnection ("Data Source=:memory:");
c.Open ();
c.Update += (sender, e) => {};
// the above should not crash
c.Close ();
}
protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
DataTable dtArtists = new DataTable();
#region fetch data for artists
Mono.Data.Sqlite.SqliteConnection cn = new Mono.Data.Sqlite.SqliteConnection("library.sqlite");
Mono.Data.Sqlite.SqliteCommand comm = new Mono.Data.Sqlite.SqliteCommand(cn);
Mono.Data.Sqlite.SqliteDataAdapter adapter = new Mono.Data.Sqlite.SqliteDataAdapter(comm);
comm.CommandText = @"
SELECT name, id, fetched
FROM artists
";
adapter.Fill(dtArtists);
#endregion
if (dtArtists.Rows.Count == 0)
{
List <SubsonicItem> artists = Subsonic.GetIndexes();
foreach (SubsonicItem artist in artists)
{
DataRow dr = dtArtists.NewRow();
dr["name"] = artist.name;
dr["id"] = artist.id;
dr["feteched"] = DateTime.Now.ToString();
dtArtists.Rows.Add(dr);
comm = new Mono.Data.Sqlite.SqliteCommand(cn);
comm.CommandText = @"
INSERT INTO artists (name, id, fetched)
VALUES(@name, @id, @fetched);
";
comm.Parameters.AddWithValue("@name", artist.name);
comm.Parameters.AddWithValue("@id", artist.id);
comm.Parameters.AddWithValue("@fetched", DateTime.Now.ToString());
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
comm.ExecuteNonQuery();
}
if (cn.State != ConnectionState.Closed)
{
cn.Close();
}
}
rptArtists.DataSource = dtArtists;
rptArtists.DataBind();
}
void Awake()
{
Debug.Log("ASd");
SqliteConnection connection = new SqliteConnection(string.Format("Data Source={0}", "Test.db"));
connection.Open();
Debug.Log("Connected DB");
//Debug.Log(connection.ConnectionString);
SqliteCommand sqlCmd = new SqliteCommand(connection);
sqlCmd.CommandText = "SELECT * FROM Position";
SqliteDataReader reader = sqlCmd.ExecuteReader();
string[] readArray = new string[reader.RecordsAffected];
//Debug.Log(reader.RecordsAffected);
//Debug.Log(reader.FieldCount);
//Debug.Log(reader.HasRows);
//Debug.Log(reader.VisibleFieldCount);
posList = new List<Vector3>();
while (reader.Read())
{
//Debug.Log("(" + reader.GetFloat(0) + ", " + reader.GetFloat(1) + ", " + reader.GetFloat(2) + ")");
posList.Add(new Vector3(reader.GetFloat(0), reader.GetFloat(1), reader.GetFloat(2)));
}
reader.Close();
connection.Close();
// reading database code
//connection.ConnectionString = "URI=file:" + "EventDB.db";
//connection.Open();
//SqliteCommand sqlCmd2 = new SqliteCommand(connection);
//sqlCmd2.CommandText = "SELECT * FROM Event";
//SqliteDataReader reader2 = sqlCmd2.ExecuteReader();
//string eventName = "";
//while (reader2.Read())
//{
// eventName = reader2.GetString(0);
// Contents content = new Contents(reader2.GetString(1), reader2.GetString(2));
// //Debug.Log("Event Name : " + eventName);
// //Debug.Log("Character Name : " + content.charName);
// //Debug.Log("Description : " + content.description);
// contentsList.Add(content);
//}
//eventMap.Add(eventName, contentsList);
//reader2.Close();
//connection.Close();
text = this.GetComponent<GUIText>();
}
public string proDataCreate()
{
string DatabaseName = "PUB.db3";
string documents = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
string db = System.IO.Path.Combine(documents, DatabaseName);
bool BFile = File.Exists(db);
if (!BFile) { SqliteConnection.CreateFile(db); }
var conn = new SqliteConnection("Data Source=" + db);
string[] commands = proDataCreateFmt();
try
{
foreach (var cmd in commands)
{
var sqlitecmd = conn.CreateCommand();
sqlitecmd.CommandText = cmd;
sqlitecmd.CommandType = CommandType.Text;
conn.Open();
sqlitecmd.ExecuteNonQuery();
conn.Close();
}
if (!BFile) // 初始化
{
var sqlitecmd = conn.CreateCommand();
conn.Open();
sqlitecmd.CommandType = CommandType.Text;
sqlitecmd.CommandText = "INSERT INTO T_PUB_USERID (USERID) VALUES (0);";
sqlitecmd.ExecuteNonQuery();
sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTELNO (USERTELNO) VALUES ('');";
sqlitecmd.ExecuteNonQuery();
sqlitecmd.CommandText = "INSERT INTO T_PUB_USERTYPE (USERTYPE) VALUES (0);";
sqlitecmd.ExecuteNonQuery();
conn.Close();
}
conn.Dispose();
return "";
}
catch (System.Exception sysExc)
{
return "创建数据出错: " + sysExc.Message;
}
}
public bool VerifyDb()
{
using (var conn = new SqliteConnection(ConnectionString))
{
conn.Open();
var schema = conn.GetSchema("Tables");
Console.WriteLine("Tables = " + schema.Rows.Count);
conn.Close();
return schema.Rows.Count == 2;
}
}
public static bool CreateDB(string dbfile)
{
SqliteConnection.CreateFile(dbfile);
string dbConnection = String.Format("Data Source={0}", dbfile);
SqliteConnection cnn = new SqliteConnection(dbConnection);
cnn.Open();
string[] commands = new string[5];
//CreateTable
SqliteCommand cmd = cnn.CreateCommand();
int output = 0;
try
{
cmd.CommandText = "CREATE TABLE accounts (username string primary key, password string, id integer)";
output = cmd.ExecuteNonQuery();
Logger.LogMsg(("SQL>Creating Table 'accounts' returned " + output), 2);
cmd.CommandText = "CREATE TABLE players (ids integer primary key autoincrement, name text,level integer,attacklevel integer, attackxp integer,defencelevel integer, defencexp integer,magiclevel integer, magicxp integer, kills integer, health integer, admin integer, banned integer, inventory string)";
output = cmd.ExecuteNonQuery();
Logger.LogMsg(("SQL>Creating Table 'players' returned " + output), 2);
}
catch (SqliteException ex)
{
Logger.LogMsg("SQL> Failed last action, database not created. Destroying attempt. Exception data " + ex.Message, 1);
File.Delete(dbfile);
cnn.Close();
return false;
throw;
}
//CreateAdmin
try
{
cmd = cnn.CreateCommand();
cmd.CommandText = "INSERT INTO players VALUES(0,'Administrator',1,1,0,1,0,1,0,0,10,1,0,'')";
output = cmd.ExecuteNonQuery();
Logger.LogMsg(("SQL>Creating new player 'Administrator' returned " + output), 2);
cmd.CommandText = "INSERT INTO accounts VALUES('admin','admin',0)";
output = cmd.ExecuteNonQuery();
Logger.LogMsg(("SQL>Creating new account 'admin' returned " + output), 2);
}
catch (SqliteException ex)
{
Logger.LogMsg("SQL> Failed last action, database not created. Destroying attempt. Exception data " + ex.Message, 1);
cnn.Close();
File.Delete(dbfile);
return false;
throw;
}
return true;
}
public void deleteCert(){
using (SqliteConnection con = new SqliteConnection ("Data Source=" + firstSetup.mainDir + "/" + mainWindow.selectedCA + "-ca/certsdb/certDB.sqlite")) {
con.Open ();
string stm = "DELETE FROM certs WHERE certNr = '" + certNum + "'";
using (SqliteCommand cmd = new SqliteCommand (stm, con)) {
cmd.ExecuteNonQuery ();
}
con.Close ();
}
caHandling.callProc ("/bin/rm", certPath, "Cert: " + certNum + "removed");
}
public DataSet Get(string command)
{
DataSet dataSet = new DataSet ();
using (SqliteConnection sql_connection=new SqliteConnection(connectionString)) {
SqliteDataAdapter slda=new SqliteDataAdapter(command,sql_connection);
sql_connection.Open();
slda.Fill(dataSet);
sql_connection.Close();
}
return dataSet;
}
public void CollationTest()
{
var builder = new SqliteConnectionStringBuilder();
builder.DataSource = uri;
var connectionString = builder.ToString();
using (var connection = new SqliteConnection (connectionString)) {
connection.Open ();
connection.Close ();
}
}
private void forcaCreateTable(){
using (IDbConnection dbConnection = new SqliteConnection (connectionString)) {
dbConnection.Open ();
using (IDbCommand dbCmd = dbConnection.CreateCommand ()) {
string sqlQuery = String.Format ("CREATE TABLE IF NOT EXISTS forcaGame_table (idpergunta INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , palavra TEXT NOT NULL , dica1 TEXT NOT NULL , dica2 TEXT NOT NULL , dica3 TEXT NOT NULL , dica4 TEXT NOT NULL , tempo_gasto_segundos INTEGER NOT NULL ,idtema INTEGER NOT NULL , tema TEXT NOT NULL , data DATETIME NOT NULL DEFAULT CURRENT_TIME);");
dbCmd.CommandText = sqlQuery;
dbCmd.ExecuteScalar();
dbConnection.Close();
}
}
}
private void voufCreateTable(){
using (IDbConnection dbConnection = new SqliteConnection (connectionString)) {
dbConnection.Open ();
using (IDbCommand dbCmd = dbConnection.CreateCommand ()) {
string sqlQuery = String.Format ("CREATE TABLE IF NOT EXISTS \"main\".\"voufGame_table\" (\"idpergunta\" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE , \"pergunta\" TEXT NOT NULL ,\"verdadeiro_ou_falso\" TEXT NOT NULL , \"idtema\" INTEGER NOT NULL , \"tema\" TEXT NOT NULL , \"data\" DATETIME NOT NULL DEFAULT CURRENT_TIME)");
dbCmd.CommandText = sqlQuery;
dbCmd.ExecuteScalar();
dbConnection.Close();
}
}
}
private string NewConnection(string strConnectionString)
{
//<summary><summary/>
//<params><params/>
//<output><output/>
#region NewConnection_declarations
string strDBPath = null;
#endregion
#region NewConnection_validaton
if (string.IsNullOrWhiteSpace(strConnectionString))
{
strDBPath = _strConnection;
}
else
{
strDBPath = strConnectionString;
}
if (string.IsNullOrWhiteSpace(strDBPath))
{
throw new NullReferenceException(message: "Both strConnectionString and _strConnection are NULL.", innerException: new FileNotFoundException(message: "Default database doesn't exist.", fileName: Path.GetDirectoryName(Environment.GetCommandLineArgs()[0]) + _localDBName));
}
if (ConnectionState == "Open")
{
_dbConn.Close();
}
#endregion
#region NewConnection_procedure
// Create connection to the database
_dbConn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + strDBPath);
_dbConn.Open();
_strState = _dbConn.State.ToString();
return(strDBPath);
#endregion
}
public void CreateDB(string databaseFile)
{
try
{
// Check if database already exists
if (!File.Exists (databaseFile))
{
// Create the database
SqliteConnection.CreateFile (databaseFile);
// Connect to the database
using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0};", databaseFile)))
{
sqlCon.Open ();
// Create a table
using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
{
sqlCom.CommandText = "CREATE TABLE Personel (ID INTEGER PRIMARY KEY, UserName VARCHAR(20), Password VARCHAR(20))";
//veri Ekleme
//Update
// sqlCom.CommandText = "UPDATE Customers SET FirstName= 'Haluk' WHERE LastName = @lastName";
// sqlCom.Parameters.Add(new SqliteParameter("@lastName","Haluky"));
sqlCom.ExecuteNonQuery ();
Console.WriteLine(sqlCom.ExecuteNonQuery());
}
//end using sqlCom
sqlCon.Close ();
}
//end using sqlCon
this.durumGostericiT.Text = "Database hazır!";
} else
{
this.durumGostericiT.Text = "Database Mevcut!";
}//end if else
} catch (Exception ex)
{
this.durumGostericiT.Text = String.Format ("Sqlite error: {0}", ex.Message);
}//end try catch
}
/// <summary>
/// Deletes the category with the specified id.
/// </summary>
/// <param name="categoryId">Category identifier.</param>
public void Delete(int categoryId)
{
using (var connection = new SqliteConnection("Data Source=" + dbPath))
using (var query = new SqliteCommand("DELETE FROM Categories WHERE Id = @id", connection))
{
query.Parameters.AddWithValue("@id", categoryId);
connection.Open();
query.ExecuteNonQuery();
connection.Close();
}
}
/// <summary>
/// Allows programmer to run a query against the Database.
/// </summary>
/// <param name="sql">The SQL to run</param>
/// <returns>A DataTable containing the result set.</returns>
public DataTable GetDataTable(string sql)
{
DataTable dt = new DataTable();
SqliteConnection cnn = new SqliteConnection(dbConnection);
cnn.Open();
SqliteCommand cmd = new SqliteCommand(cnn) { CommandText = sql };
SqliteDataReader reader = cmd.ExecuteReader();
dt.Load(reader);
reader.Close();
cnn.Close();
return dt;
}
private string DBCreate()
{
//<summary><summary/>
//<params><params/>
//<output><output/>
#region DBCreate_declarations
string strPath = Path.GetDirectoryName(Environment.GetCommandLineArgs()[0]);
string strDBPath = strPath + "/" + _localDBName;
var testStr = Path.GetPathRoot(strPath);
#endregion
#region DBCreate_validaton
// Create the database if it doesn't already exist
if (!File.Exists(strDBPath))
{
SqliteConnection.CreateFile(strDBPath);
}
else
{
return(strDBPath); //db exists so exit now and return default path
}
#endregion
#region DBCreate_procedure
// Create connection to the database
var sqliteConn = new Mono.Data.Sqlite.SqliteConnection("Data Source=" + strDBPath);
// Set the structure of the database
if (File.Exists(strDBPath))
{
var sqliteCMD = new[] { "CREATE TABLE LocalSettings (Type TEXT, StringValue1 TEXT, StringValue2 TEXT, StringValue3 TEXT, StringValue4 TEXT, StringValue5 TEXT)" };
sqliteConn.Open();
foreach (var cmd in sqliteCMD)
{
using (var c = sqliteConn.CreateCommand())
{
c.CommandText = cmd;
c.CommandType = System.Data.CommandType.Text;
c.ExecuteNonQuery();
}
}
}
sqliteConn.Close();
return(strDBPath);
#endregion
}
public void MakeCvsThenSend()
{
/*Make CVS file*/
//Filepath for the data.csv file
string fileName = Application.persistentDataPath + "/" + "data.csv";
//timestamp, page, date, feeling, urge, intensity, thoughts
using (StreamWriter writer = new StreamWriter(fileName, false, Encoding.UTF8))
{
//Write the each columns title, for each field in pateint_info
writer.Write("Timestamp | Page | Date | Feeling | Urge | Intensity | Thoughts");
writer.Write(Environment.NewLine);
//Write data from the the db
conn = new sqliteConnection("URI=file:" + Application.persistentDataPath + "/" + dbName + ";Version=3;FailIfMissing=True;mode=cvs");
conn.Open();
var Sqlcommand = new sqliteCommand("select * from patient_info", conn);
var reader = Sqlcommand.ExecuteReader();
while (reader.Read())
{
writer.Write(reader["timestamp"] + "|" + reader["page"] + "|" + reader["date"] + "|" + reader["feeling"] + "|" + reader["urge"] + "|" + reader["intensity"] + "|" + reader["thoughts"]);
writer.Write(Environment.NewLine);
}
conn.Close();
}
/*SendMail*/
MailMessage mail = new MailMessage();
mail.From = new MailAddress("*****@*****.**");
mail.To.Add(email.text);
mail.Subject = "Patients Data from App";
mail.Body = "The File attached is the patients data (every entry is seperated by '|' when importing into excel or google sheets that custom seporator should be used) ";
//Add the data.csv file to the email
mail.Attachments.Add(new Attachment(fileName));
SmtpClient smtpServer = new SmtpClient();
smtpServer.Host = "smtp.gmail.com";
smtpServer.Port = 587; //Has to be 587 so it works on Networks with port protections.
smtpServer.DeliveryMethod = SmtpDeliveryMethod.Network;
smtpServer.Credentials = new System.Net.NetworkCredential("*****@*****.**", "HeartRateAppFxu2018") as ICredentialsByHost;
smtpServer.EnableSsl = true;
ServicePointManager.ServerCertificateValidationCallback =
delegate(object s, X509Certificate certificate, X509Chain chain, SslPolicyErrors sslPolicyErrors)
{ return(true); };
smtpServer.Send(mail);
}
public static void ModifyPlayerWinCount(uint ID, int value)
{
for (int i = 0; i < players.Count; i++)
{
if (players [i].ID == ID)
{
//Confirm log in credentials through database
string databasePath = "";
Platform platform = RunningPlatform();
if (platform == Platform.Windows)
{
databasePath = "Data Source=" + System.Environment.CurrentDirectory + "\\database.db; FailIfMissing=True";
}
else if (platform == Platform.Mac)
{
databasePath = "Data Source=" + System.Environment.CurrentDirectory + "/data/database.db; FailIfMissing=True";
}
SQLiteConnection db_connection = new SQLiteConnection(databasePath);
db_connection.Open();
string query = "select * from Users where ID = @id";
SQLiteCommand command = new SQLiteCommand(query, db_connection);
command.Parameters.AddWithValue("@id", players[i].ID);
SQLiteDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
uint currentWinCount = Convert.ToUInt32(reader ["WINCOUNT"]);
players [i] = SetPlayerWinCount(players [i], currentWinCount + 1);
string sql = "update Users set WINCOUNT = @wincount where ID = @id";
command = new SQLiteCommand(sql, db_connection);
command.Parameters.AddWithValue("@wincount", currentWinCount + 1);
command.Parameters.AddWithValue("@id", players[i].ID);
command.ExecuteNonQuery();
}
}
db_connection.Close();
break;
}
}
}
public void UpdateResult(Result result)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
this.updateResult(conn, null, result);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void AddScore(Score score)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
this.addScore(conn, null, score);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public int GetMyAthleteID()
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
return(getMyAthleteID(conn));
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void DeleteResult(int resultID)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
this.executeNonqueryCommand("DELETE FROM [Result] WHERE [ResultID]=" + resultID, conn, null);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
void Start() //When the Emergency Contacts Scene is opened Should display Contacts if they have been saved in the Database
{
conn = new sqliteConnection("URI=file:" + Application.persistentDataPath + "/" + dbName + ";Version=3;FailIfMissing=True");
conn.Open();
try
{
//Read from contact1 table and put that into text fields on the Emergency Contacts Screen
var Sqlcommand = new sqliteCommand("select * from contact1", conn);
var reader = Sqlcommand.ExecuteReader();
reader.Read();
if (reader.HasRows == true)
{
name1.text = reader["name"] as String;
relationship1.text = reader["relationship"] as String;
mobile1.text = reader["mobile"] as String;
home1.text = reader["home"] as String;
}
else
{
Debug.Log("There are no rows in contact1");
}
//Read from contact2 table and put that into text fields on the Emergency Contacts Screen
Sqlcommand = new sqliteCommand("select * from contact2", conn);
reader = Sqlcommand.ExecuteReader();
reader.Read();
if (reader.HasRows == true)
{
name2.text = reader["name"] as String;
relationship2.text = reader["relationship"] as String;
mobile2.text = reader["mobile"] as String;
home2.text = reader["home"] as String;
}
else
{
Debug.Log("There are no rows in contact2");
}
}
catch (Exception ex)
{
Debug.Log("Failed to read from Database " + ex);
}
conn.Close();
}
public static void Init(string absolutePath = "cards.cdb")
{
m_cards = new Dictionary <int, Card>();
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + absolutePath)){
connection.Open();
using (SQLiteCommand command = new SQLiteCommand("SELECT datas.id, ot, alias, "
+ "setcode, type, level, race, attribute, atk, def ,"
+ " name , desc FROM datas,texts WHERE datas.id=texts.id",
connection)){
using (SQLiteDataReader reader = command.ExecuteReader()){
while (reader.Read())
{
int id = reader.GetInt32(0);
int ot = reader.GetInt32(1);
int levelinfo = reader.GetInt32(5);
int level = levelinfo & 0xff;
int lscale = (levelinfo >> 24) & 0xff;
int rscale = (levelinfo >> 16) & 0xff;
Card.CardData data = new Card.CardData
{
Code = id,
Alias = reader.GetInt32(2),
Setcode = reader.GetInt64(3),
Type = reader.GetInt32(4),
Level = level,
LScale = lscale,
RScale = rscale,
Race = reader.GetInt32(6),
Attribute = reader.GetInt32(7),
Attack = reader.GetInt32(8),
Defense = reader.GetInt32(9)
};
string name = reader.GetString(10);
string desc = reader.GetString(11);
m_cards.Add(id, new Card(data, ot, name, desc));
}
reader.Close();
}
}
connection.Close();
}
}
public void SetIsDeletedOnResult(int resultID, bool isDeleted)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
var command = conn.CreateCommand();
command.CommandText = "UPDATE [Result] SET TimeModified=@TimeModified,IsDeleted=" + (isDeleted ? "1" : "0") + " WHERE ResultID=" + resultID;
command.Parameters.Add(new SqliteParameter("@TimeModified", DateTimeHelper.GetUtcNow()));
command.ExecuteNonQuery();
command.Dispose();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void AddResult(Result result)
{
if (result.Guid == null || result.Guid == Guid.Empty)
{
result.Guid = Guid.NewGuid();
}
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
this.addResult(conn, null, result);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
/// <summary>
/// Executing SQL statements
/// </summary>
/// <param name="DB">Database</param>
/// <param name="SQLs">SQL statement</param>
/// <returns>Returns the number of rows affected</returns>
public static int Command(string DB, params string[] SQLs)
{
int result = 0;
if (File.Exists(DB) && SQLs != null)
{
using (SQLiteConnection con = new SQLiteConnection(@"Data Source=" + DB))
{
con.Open();
using (SQLiteTransaction trans = con.BeginTransaction())
{
try
{
using (SQLiteCommand cmd = new SQLiteCommand(con))
{
foreach (string SQLstr in SQLs)
{
cmd.CommandText = SQLstr;
result += cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
trans.Rollback(); //There was an error, roll back
result = -1;
}
finally
{
try{
trans.Commit();
}catch { }
}
}
con.Close();
}
}
return(result);
}
// Runs when the app is opened on the first scene
void Start()
{
//Path where the database is/is going to be
string filePath = Application.persistentDataPath + "/" + dbName;
//make the database If one doesn't exist already
if (!File.Exists(filePath))
{
try
{
Debug.Log("A database doesn't exist. Creating one...");
//Create the database file in the persisten data path of the application
sqliteConnection.CreateFile(filePath);
//Open the database
conn = new sqliteConnection("URI=file:" + filePath + ";Version=3;FailIfMissing=True");
conn.Open();
//create table for Contacts1
var command = new sqliteCommand("create table contact1 (name varchar(20), relationship varchar(20), mobile varchar(20), home varchar(20) )", conn);
command.ExecuteNonQuery();
//create table for Contacts2
command = new sqliteCommand("create table contact2 (name varchar(20), relationship varchar(20), mobile varchar(20), home varchar(20) )", conn);
command.ExecuteNonQuery();
//create table for PatientInfo
command = new sqliteCommand("create table patient_info (timestamp varchar(20), page varchar(20), date varchar(20), feeling varchar(20), urge varchar(20), intensity varchar(20), thoughts TEXT)", conn);
command.ExecuteNonQuery();
//Close Db
conn.Close();
}
catch (Exception ex)
{
Debug.Log("Failed to add to DB " + ex);
}
}
else
{
Debug.Log(" A database exists.");
}
}
public void UpdateScore(Score score)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
string sql = @"
UPDATE [Score]
SET [AthleteAID]=@AthleteAID,[AthleteBID]=@AthleteBID,[Date]=@Date,[IsUnfinished]=@IsUnfinished,[TimeModified]=@TimeModified,[Guid]=@Guid,[IsDeleted]=@IsDeleted,[SportID]=@SportID,[VenueID]=@VenueID,[Type1]=@Type1,[PointsA]=@PointsA,[PointsB]=@PointsB,[OpponentConfirmation]=@OpponentConfirmation,[ExtraData]=@ExtraData,
[InnerPoints1A]=@InnerPoints1A,[InnerPoints1B]=@InnerPoints1B,[InnerPoints2A]=@InnerPoints2A,[InnerPoints2B]=@InnerPoints2B,[InnerPoints3A]=@InnerPoints3A,[InnerPoints3B]=@InnerPoints3B,[InnerPoints4A]=@InnerPoints4A,[InnerPoints4B]=@InnerPoints4B,[InnerPoints5A]=@InnerPoints5A,[InnerPoints5B]=@InnerPoints5B,[InnerPoints6A]=@InnerPoints6A,[InnerPoints6B]=@InnerPoints6B,[InnerPoints7A]=@InnerPoints7A,[InnerPoints7B]=@InnerPoints7B,[InnerPoints8A]=@InnerPoints8A,[InnerPoints8B]=@InnerPoints8B,[InnerPoints9A]=@InnerPoints9A,[InnerPoints9B]=@InnerPoints9B,[InnerPoints10A]=@InnerPoints10A,[InnerPoints10B]=@InnerPoints10B
WHERE ScoreID=" + score.ScoreID;
var command = createCommandForScore(sql, conn, null, score);
command.ExecuteNonQuery();
command.Dispose();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
internal static void Init()
{
m_cards = new Dictionary<int, Card>();
string absolutePath = PathManager.GetCardsDb();
SQLiteConnection connection = new SQLiteConnection("Data Source=" + absolutePath);
connection.Open();
SQLiteCommand command = new SQLiteCommand("SELECT id, ot, alias, setcode, type, level, race, attribute, atk, def FROM datas", connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
int id = reader.GetInt32(0);
int ot = reader.GetInt32(1);
int levelinfo = reader.GetInt32(5);
int level = levelinfo & 0xff;
int lscale = (levelinfo >> 24) & 0xff;
int rscale = (levelinfo >> 16) & 0xff;
Card.CardData data = new Card.CardData
{
Code = id,
Alias = reader.GetInt32(2),
Setcode = reader.GetInt64(3),
Type = reader.GetInt32(4),
Level = level,
LScale = lscale,
RScale = rscale,
Race = reader.GetInt32(6),
Attribute = reader.GetInt32(7),
Attack = reader.GetInt32(8),
Defense = reader.GetInt32(9)
};
m_cards.Add(id, new Card(data, ot));
}
reader.Close();
connection.Close();
}
public static List <string> GetColumns(string db, string table)
{
List <string> cols = new List <string>();
if (!File.Exists(db))
{
return(cols);
}
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db)){
connection.Open();
using (SQLiteCommand command = new SQLiteCommand("PRAGMA table_info(" + table + ")", connection)){
using (SQLiteDataReader reader = command.ExecuteReader()){
while (reader.Read())
{
string name = reader.GetString(1);
cols.Add(name);
}
reader.Close();
}
}
connection.Close();
}
return(cols);
}
private List <Score> getScores(string sql)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
try
{
conn = openDbConnection();
var command = conn.CreateCommand();
command.CommandText = sql;
var reader = command.ExecuteReader();
List <Score> scores = new List <Score>();
while (reader.Read())
{
Score score = new Score();
score.ScoreID = int.Parse(reader["ScoreID"].ToString());
score.AthleteAID = int.Parse(reader["AthleteAID"].ToString());
score.AthleteBID = int.Parse(reader["AthleteBID"].ToString());
score.Date = (DateTime)reader["Date"];
score.IsUnfinished = ((int)reader["IsUnfinished"]) > 0;
score.TimeModified = (DateTime)reader["TimeModified"];
string guid = reader["Guid"].ToString();
score.Guid = Guid.Parse(guid);
string isDeleted = reader["IsDeleted"].ToString().ToLower();
if (isDeleted == "true")
{
score.IsDeleted = true;
}
else if (isDeleted == "false")
{
score.IsDeleted = false;
}
else
{
score.IsDeleted = int.Parse(isDeleted) > 0;
}
score.SportID = int.Parse(reader["SportID"].ToString());
if (reader["VenueID"] != DBNull.Value)
{
score.VenueID = int.Parse(reader["VenueID"].ToString());
}
if (reader["Type1"] != DBNull.Value)
{
score.Type1 = int.Parse(reader["Type1"].ToString());
}
score.AthleteBConfirmation = int.Parse(reader["OpponentConfirmation"].ToString());
score.PointsA = int.Parse(reader["PointsA"].ToString());
score.PointsB = int.Parse(reader["PointsB"].ToString());
score.InnerPoints1A = int.Parse(reader["InnerPoints1A"].ToString());
score.InnerPoints1B = int.Parse(reader["InnerPoints1B"].ToString());
score.InnerPoints2A = int.Parse(reader["InnerPoints2A"].ToString());
score.InnerPoints2B = int.Parse(reader["InnerPoints2B"].ToString());
score.InnerPoints3A = int.Parse(reader["InnerPoints3A"].ToString());
score.InnerPoints3B = int.Parse(reader["InnerPoints3B"].ToString());
score.InnerPoints4A = int.Parse(reader["InnerPoints4A"].ToString());
score.InnerPoints4B = int.Parse(reader["InnerPoints4B"].ToString());
score.InnerPoints5A = int.Parse(reader["InnerPoints5A"].ToString());
score.InnerPoints5B = int.Parse(reader["InnerPoints5B"].ToString());
score.InnerPoints6A = int.Parse(reader["InnerPoints6A"].ToString());
score.InnerPoints6B = int.Parse(reader["InnerPoints6B"].ToString());
score.InnerPoints7A = int.Parse(reader["InnerPoints7A"].ToString());
score.InnerPoints7B = int.Parse(reader["InnerPoints7B"].ToString());
score.InnerPoints8A = int.Parse(reader["InnerPoints8A"].ToString());
score.InnerPoints8B = int.Parse(reader["InnerPoints8B"].ToString());
score.InnerPoints9A = int.Parse(reader["InnerPoints9A"].ToString());
score.InnerPoints9B = int.Parse(reader["InnerPoints9B"].ToString());
score.InnerPoints10A = int.Parse(reader["InnerPoints10A"].ToString());
score.InnerPoints10B = int.Parse(reader["InnerPoints10B"].ToString());
object extraData = reader["ExtraData"];
if (extraData != DBNull.Value)
{
score.ExtraData = (string)extraData;
}
scores.Add(score);
}
return(scores);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void SaveSecurity(int myAthleteID, bool userWantsToBeGuest, DateTime timeAthleteCreated)
{
Mono.Data.Sqlite.SqliteConnection conn = null;
Mono.Data.Sqlite.SqliteTransaction trans = null;
try
{
conn = openDbConnection();
int myOldAthleteID = this.getMyAthleteID(conn);
bool myOldAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myOldAthleteID, conn, null).ToString()) == 1;
bool myNewAthleteRecordAlreadyExists = int.Parse(this.executeScalarCommand("SELECT COUNT(*) FROM Athlete WHERE AthleteID=" + myAthleteID, conn, null).ToString()) == 1;
trans = conn.BeginTransaction();
// update Singular row
var command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Singular SET MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.CommandText = "UPDATE Singular SET AccessToken=@AccessToken, MyAthleteID=" + myAthleteID + ", UserWantsToBeGuest=" + (userWantsToBeGuest ? "1" : "0");
//command.Parameters.Add(new SqliteParameter() { ParameterName = "@AccessToken", Value = Crypto.Encrypt(accessToken, "$EFK#$RF!#$#SDFwefasdWE@") });
command.ExecuteNonQuery();
command.Dispose();
if (myAthleteID != myOldAthleteID)
{
// create Athlete row
if (myNewAthleteRecordAlreadyExists == false)
{
createAhlete(myAthleteID, timeAthleteCreated, conn, trans);
}
// move results and scores
command = conn.CreateCommand();
command.Transaction = trans;
command.CommandText = "UPDATE Result SET AthleteID=" + myAthleteID + " WHERE AthleteID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteAID=" + myAthleteID + " WHERE AthleteAID=" + myOldAthleteID;
command.ExecuteNonQuery();
command.CommandText = "UPDATE Score SET AthleteBID=" + myAthleteID + " WHERE AthleteBID=" + myOldAthleteID;
command.ExecuteNonQuery();
if (myOldAthleteRecordAlreadyExists == true)
{
this.executeNonqueryCommand("DELETE FROM [Athlete] WHERE AthleteID=" + myOldAthleteID, conn, trans);
}
}
trans.Commit();
}
catch (Exception exc)
{
if (trans != null)
{
trans.Rollback();
}
throw exc;
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
public void SaveThenExit()
{
//Prepare the data for insertion in the database, fields that are not on the page are null and should say "N/A"
//Date (Only on the self harmed page)
if (Date == null)
{
DateClean = "N/A";
}
else
{
DateClean = Date.text;
}
//Urge
if (Urge == null)
{
UrgeClean = "N/A";
}
else
{
if (Urge.isOn)
{
UrgeClean = "Yes";
}
else
{
UrgeClean = "No";
}
}
//Intensity
if (Intensity_L == null)//Doesn't matter which Intensity is checked here
{
IntensityClean = "N/A";
}
else
{
if (Intensity_L.isOn)
{
IntensityClean = "Low";
}
if (Intensity_M.isOn)
{
IntensityClean = "Moderate";
}
if (Intensity_H.isOn)
{
IntensityClean = "High";
}
}
conn = new sqliteConnection("URI=file:" + Application.persistentDataPath + "/" + dbName + ";Version=3;FailIfMissing=True");
conn.Open();
try
{ //insert into contact1
var sql = "insert into " + "patient_info" + " (timestamp, page, date, feeling, urge, intensity, thoughts) values ";
sql += "('" + DateTime.Now.ToString() + "'"; //Timestamp
sql += ",";
sql += "'" + Page + "'"; //Page name from which the submission is happening
sql += ",";
sql += "@date"; //Date of "When did you last self harm?"
sql += ",";
sql += "'" + Feeling.value.ToString() + "'"; //Slider value of "How are you feeling from 1-10"
sql += ",";
sql += "'" + UrgeClean + "'"; //Answer to "Do you have an urge to self-harm?"(Yes/No)
sql += ",";
sql += "'" + IntensityClean + "'"; //Answer to "The intensity of your urge to self harm"
sql += ",";
sql += "@thoughts"; //Patients Thoughts and Feelings they have at this time
sql += ")";
var Sqlcommand = new sqliteCommand(sql, conn);
//Prevent sql injection and just allow someone to do apostrpohes for example typing "Don't"
Sqlcommand.Parameters.AddWithValue("@date", DateClean);
Sqlcommand.Parameters.AddWithValue("@thoughts", Thoughts.text);
Sqlcommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Debug.Log("Failed to add to DB " + ex);
}
conn.Close();
}
