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(); }