// Выполняет запрос, и в случае если это был 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; } }
public override List <String> SelectQueryString(string strQuery) { ///<summary> /// same as SelectQuery but single parameter of string. Allows for more complex select query. /// </summary> ///<params> /// </params> ///<output> /// </output> #region SelectQueryString_declarations int intNewConnectionCount = 0; var lstQueryResults = new List <string>(); var lstReader = new List <string>(); #endregion #region SelectQueryString_validaton //valiate strTable parameter while (this.ConnectionState != "Open") { if (intNewConnectionCount < 1) { NewConnection(); } else { throw new InvalidOperationException("Unable to obtain active connection to server."); } } #endregion #region SelectQueryString_procedure // Execute query using (var dbCMD = _dbConn.CreateCommand()) { // Create new command dbCMD.CommandText = strQuery; // Get the results from the database using (var dbRS = dbCMD.ExecuteReader()) { // Read the display field from the query while (dbRS.Read()) { // Read result from query for (int i = 0; i < dbRS.FieldCount; i++) { lstReader.Add(dbRS[i].ToString()); } lstQueryResults.Add(string.Join("|", lstReader.ToArray())); lstReader.Clear(); } } } return(lstQueryResults); #endregion }
public static void Main(string[] args) { var bookmarks = new Bookmarks(); using (var dbConn = new Mono.Data.Sqlite.SqliteConnection("Data Source=/mnt/ramdisk/test.db")) { dbConn.Open(); var cmdGetGroups = dbConn.CreateCommand(); cmdGetGroups.CommandText = "SELECT gid, name, IFNULL(pid,0) FROM rel JOIN groups ON rel.gid=groups.rowid WHERE depth=1"; using (IDataReader groups = cmdGetGroups.ExecuteReader()) { ApplyRow(groups, (g) => bookmarks.GroupAdd(g.GetInt32(0), new Group { Id = g.GetInt32(0), Name = g.GetString(1), Pid = g.GetInt32(2) })); } var cmdGetRows = dbConn.CreateCommand(); cmdGetRows.CommandText = "SELECT IFNULL(gid,0), key, value FROM data"; using (IDataReader rows = cmdGetRows.ExecuteReader()) { ApplyRow(rows, (r) => bookmarks.LinkAdd(r.GetInt32(0), new LinkItem { Title = r.GetString(1), Target = r.GetString(2) })); } } var document = new XDocument(); document.Add(bookmarks.ToXml()); document.Save("test.xml"); }
//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 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; }
public static User getDatabase() { User result = new User(); using (SqliteConnection co = new SqliteConnection("Data Source=" + databasePath)) { co.Open(); SqliteCommand cmd = co.CreateCommand(); cmd.CommandText = "Select * From User"; try { SqliteDataReader read = cmd.ExecuteReader(); if(read.Read()) { try { result.usrID = (string)read["ID"]; } catch { result.usrID = ""; } try { result.phrase = (string)read["Name"]; } catch { throw new Exception("NO Passphrase"); } try { result.usrCH = (string)read["Chor"]; } catch { result.usrCH = ""; } try { result.storage = (List<Appointment>)read["Appointments"]; } catch { } } } catch(Exception ex) { Console.Write(ex); } } return result; }
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; }
/// <summary> /// Constructs the transaction object, binding it to the supplied connection /// </summary> /// <param name="connection">The connection to open a transaction on</param> /// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param> internal SqliteTransaction(SqliteConnection connection, bool deferredLock) { _cnn = connection; _version = _cnn._version; _level = (deferredLock == true) ? IsolationLevel.ReadCommitted : IsolationLevel.Serializable; if (_cnn._transactionLevel++ == 0) { try { using (SqliteCommand cmd = _cnn.CreateCommand()) { if (!deferredLock) cmd.CommandText = "BEGIN IMMEDIATE"; else cmd.CommandText = "BEGIN"; cmd.ExecuteNonQuery(); } } catch (SqliteException) { _cnn._transactionLevel--; _cnn = null; throw; } } }
/// <summary> /// Initializes a new instance of the <see cref="Tasky.DL.TaskDatabase"/> TaskDatabase. /// if the database doesn't exist, it will create the database and all the tables. /// </summary> public ADODatabase(string dbPath) { var output = ""; path = dbPath; // create the tables bool exists = File.Exists (dbPath); if (!exists) { connection = new SqliteConnection ("Data Source=" + dbPath); connection.Open (); var commands = new[] { "CREATE TABLE [Items] (_id INTEGER PRIMARY KEY ASC, Name NTEXT, Notes NTEXT, Done INTEGER);" }; foreach (var command in commands) { using (var c = connection.CreateCommand ()) { c.CommandText = command; var i = c.ExecuteNonQuery (); } } } else { // already exists, do nothing. } Console.WriteLine (output); }
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 CodeProjectDatabase () { dbPath = Path.Combine ( Environment.GetFolderPath (Environment.SpecialFolder.Personal), "items.db3"); bool exists = File.Exists (dbPath); if (!exists) { SqliteConnection.CreateFile (dbPath); } var connection = new SqliteConnection ("Data Source=" + dbPath); connection.Open (); if (!exists) { var commands = new[]{ "CREATE TABLE [Member] (Key integer, Name ntext, ArticleCnt integer, BlogCnt integer, Reputation ntext, IsMe integer);" }; foreach (var command in commands) { using (var c = connection.CreateCommand ()) { c.CommandText = command; c.ExecuteNonQuery (); } } } }
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 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 = "결과 데이터 삽입 완료"; }
public DrinkDatabase(string dbPath) { path = dbPath; // create the tables bool exists = File.Exists(dbPath); if (!exists) { connection = new SqliteConnection("Data Source=" + dbPath); connection.Open(); var commands = new[] { "CREATE TABLE [Items] (_id INTEGER PRIMARY KEY ASC, Name NTEXT, About NTEXT, Volume REAL, AlcoholByVolume REAL, IconNumber INTEGER);" }; foreach (var command in commands) { using (var c = connection.CreateCommand()) { c.CommandText = command; c.ExecuteNonQuery(); } } initializeValues(); } }
public Bill LoadFavoriteBill(int id) { Bill favBill; using (var connection = new SqliteConnection (connectionString)) { using (var cmd = connection.CreateCommand ()) { connection.Open (); cmd.CommandText = "SELECT * FROM FavoriteBills WHERE id = @id"; var idParam = new SqliteParameter ("@id", id); cmd.Parameters.Add (idParam); using (var reader = cmd.ExecuteReader ()) { reader.Read (); favBill = new Bill { Id = Convert.ToInt32 (reader ["id"]), Title = (string)reader ["title"], ThomasLink = (string)reader ["thomas_link"], Notes = reader["notes"] == DBNull.Value ? "" : (string)reader["notes"] }; } } } return favBill; }
/// <summary> /// This code will handle creating the database and a table. /// </summary> void HandleTouchUpInside(object sender, EventArgs e) { // Create the database var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal); var pathToDatabase = Path.Combine(documents, "db_adonet.db"); SqliteConnection.CreateFile(pathToDatabase); var msg = "Created new database at " + pathToDatabase; // Create a table var connectionString = String.Format("Data Source={0};Version=3;", pathToDatabase); using (var conn= new SqliteConnection(connectionString)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE TABLE People (PersonID INTEGE" + "R PRIMARY KEY AUTOINCREMENT , FirstName ntext, LastName ntext)"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } // Let the user know that the database was created, and disable the button // to prevent double clicks. _txtView.Text = msg; _btnCreateDatabase.Enabled = false; }
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 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 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 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); }
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 ClickGameStart() { del_InsertNewUserInfo InsertInfo = () => { string conn = "URI=file:" + Application.dataPath + "/StreamingAssets/GameUserDB/userDB.db"; using (IDbConnection dbconn = new SqliteConnection(conn)) { dbconn.Open(); //Open connection to the database. using (IDbCommand dbcmd = dbconn.CreateCommand()) { try { string sqlQuery = "INSERT INTO USER_INFO(name, level, type) " + "VALUES(" + "'" + chName.text + "'" + ", " + "'" + chLevel.text + "'" + ", " + "'" + chType.text + "'" + ")"; dbcmd.CommandText = sqlQuery; dbcmd.ExecuteNonQuery(); } catch(SqliteException e) { // 이미 등록된 캐릭터이다. Debug.Log(e.Message); } } dbconn.Close(); } }; InsertInfo(); GameLoadingProcess(); }
public List<Politician> LoadAllPoliticans() { var politicians = new List<Politician> (); using (var connection = new SqliteConnection (connectionString)) { using (var cmd = connection.CreateCommand ()) { connection.Open (); cmd.CommandText = String.Format ("SELECT bioguide_id, first_name, last_name, govtrack_id, phone, party, state FROM Politician ORDER BY last_name"); using (var reader = cmd.ExecuteReader ()) { while (reader.Read ()) { politicians.Add (new Politician { FirstName = reader ["first_name"].ToString (), LastName = reader ["last_name"].ToString (), BioGuideId = reader ["bioguide_id"].ToString (), GovTrackId = reader ["govtrack_id"].ToString (), Phone = reader ["phone"].ToString (), State = reader ["state"].ToString (), Party = reader ["party"].ToString () }); } } } } return politicians; }
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 = "결과 데이터 삽입 완료"; }
private void CreateDatabase() { logger.Info ("Initializing config database at " + databasepath); using (var conn= new SqliteConnection(connection)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE TABLE Config (ConfigId INTEGER PRIMARY KEY AUTOINCREMENT, Setting VARCHAR(20), SettingValue varchar(200))"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Config(Setting, SettingValue) VALUES('outputdevice', 'Built-in Output')"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Config(Setting, SettingValue) VALUES('localonly', '0')"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE Voices (VoiceId INTEGER PRIMARY KEY AUTOINCREMENT, voice VARCHAR(255), lang varchar(2), id integer(4))"; cmd.ExecuteNonQuery(); for(var i=0; i<=19; i++) { cmd.CommandText = string.Format ("INSERT INTO Voices(voice, lang, id) VALUES('{0}', 'en', {1})", DefaultVoice, i); cmd.ExecuteNonQuery(); } for(var i=0; i<=19; i++) { cmd.CommandText = string.Format ("INSERT INTO Voices(voice, lang, id) VALUES('{0}', 'fr', {1})", DefaultVoice, i); cmd.ExecuteNonQuery(); } } } }
public DbUtils.Core.Api.IDbServerConnection[] RestoreSessions(SqliteConnection connection) { InitializeStateDb (connection); IList<IDbServerConnection> sessions = new List<IDbServerConnection> (); using (SqliteCommand cmd = connection.CreateCommand ()) { cmd.CommandText = "select * from dbstate"; using (SqliteDataReader reader = cmd.ExecuteReader ()) { while (reader.Read ()) { string providerName = reader.GetString (reader.GetOrdinal ("provider")); string state = reader.GetString (reader.GetOrdinal ("state")); try { Type providerType = Type.GetType(providerName); ISessionStateProvider sessionProvider = (ISessionStateProvider) providerType.GetConstructor(new Type[]{}).Invoke(new object[]{}); IDbServerConnection session = sessionProvider.restoreSessionFromState(state); sessions.Add(session); } catch( Exception e) { // todo, log error and continue throw e; } } } } return sessions.ToArray (); }
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(); } } }
// 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; }
private void SQLTest() { var dataFolder = System.IO.Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.Personal), "FLangDictionary"); var dbName = "testDB.db"; var dbFilePath = System.IO.Path.Combine(dataFolder, dbName); if (System.IO.File.Exists(dbFilePath)) System.IO.File.Delete(dbFilePath); if (!System.IO.Directory.Exists(dataFolder)) System.IO.Directory.CreateDirectory(dataFolder); SqliteConnection.CreateFile(dbFilePath); string output = ""; using (var connection = new SqliteConnection("Data Source=" + dbFilePath)) { connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = "CREATE TABLE [Items] ([_id] int, [Symbol] ntext, [Name] ntext);"; var rowcount = command.ExecuteNonQuery(); } using (var command = connection.CreateCommand()) { command.CommandText = "INSERT INTO [Items] ([_id], [Symbol]) VALUES ('1', 'APPL')"; var rowcount = command.ExecuteNonQuery(); // rowcount will be 1 } using (var command = connection.CreateCommand()) { command.CommandText = "SELECT * FROM [Items]"; SqliteDataReader reader = command.ExecuteReader(); output += "\nReading data"; while (reader.Read()) output += $"\n\tKey={reader["_id"]}; Value={reader["Symbol"]}"; } connection.Close(); } MessageBox.Show(output); }
private void CreateOrOpenDatabase() { lock (_lock) { this.DBfileFullPath = System.IO.Path.Combine(AmazonHookedPlatformInfo.Instance.PersistentDataPath, dbFileName); string vacuumCommand = "PRAGMA auto_vacuum = 1"; string sqlCommand = string.Format(CultureInfo.InvariantCulture, "CREATE TABLE IF NOT EXISTS {0} ({1} TEXT NOT NULL,{2} TEXT NOT NULL UNIQUE,{3} TEXT NOT NULL, {4} INTEGER NOT NULL DEFAULT 0 )", TABLE_NAME, EVENT_COLUMN_NAME, EVENT_ID_COLUMN_NAME, MA_APP_ID_COLUMN_NAME, EVENT_DELIVERY_ATTEMPT_COUNT_COLUMN_NAME); if (!File.Exists(this.DBfileFullPath)) { string directory = Path.GetDirectoryName(this.DBfileFullPath); if (!Directory.Exists(directory)) { Directory.CreateDirectory(directory); } SqliteConnection.CreateFile(this.DBfileFullPath); } try { connection = new SqliteConnection("URI=file:" + this.DBfileFullPath); connection.Open(); using (var command = connection.CreateCommand()) { command.CommandText = vacuumCommand; command.ExecuteNonQuery(); } using (var command = connection.CreateCommand()) { command.CommandText = sqlCommand; command.ExecuteNonQuery(); } } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } } }
public void AddNewUploadEntry(UploadEntry uploadentry) { using (SqliteConnection conn = new SqliteConnection(_connectionString)) { conn.Open(); using (SqliteTransaction trans = conn.BeginTransaction()) { using (SqliteCommand cmd = conn.CreateCommand()) { cmd.Transaction = trans; cmd.CommandText = @"UPDATE UploadEntries SET Cancelled = 1 WHERE Name = @name"; cmd.Parameters.Add(new SqliteParameter("@name", uploadentry.Name)); cmd.ExecuteNonQuery(); //TODO: log here how many other entries were cancelled. cmd.CommandText = @"INSERT INTO UploadEntries( Name, Size, CleanedName, ObscuredName, RemoveAfterVerify, CreatedAt, UploadedAt, NotifiedIndexerAt, SeenOnIndexerAt, Cancelled) VALUES( @name, @size, @cleanedName, @ObscuredName, @removeAfterVerify, @createdAt, @uploadedAt, @notifiedIndexerAt, @seenOnIndexerAt, @cancelled)"; cmd.Parameters.Add(new SqliteParameter("@name", uploadentry.Name)); cmd.Parameters.Add(new SqliteParameter("@size", uploadentry.Size)); cmd.Parameters.Add(new SqliteParameter("@cleanedName", uploadentry.CleanedName)); cmd.Parameters.Add(new SqliteParameter("@ObscuredName", uploadentry.ObscuredName)); cmd.Parameters.Add(new SqliteParameter("@removeAfterVerify", uploadentry.RemoveAfterVerify)); cmd.Parameters.Add(new SqliteParameter("@createdAt", GetDbValue(uploadentry.CreatedAt))); cmd.Parameters.Add(new SqliteParameter("@uploadedAt", GetDbValue(uploadentry.UploadedAt))); cmd.Parameters.Add(new SqliteParameter("@notifiedIndexerAt", GetDbValue(uploadentry.NotifiedIndexerAt))); cmd.Parameters.Add(new SqliteParameter("@seenOnIndexerAt", GetDbValue(uploadentry.SeenOnIndexAt))); cmd.Parameters.Add(new SqliteParameter("@cancelled", GetDbValue(uploadentry.Cancelled))); cmd.ExecuteNonQuery(); cmd.CommandText = "select last_insert_rowid()"; cmd.Parameters.Clear(); uploadentry.ID = (Int64)cmd.ExecuteScalar(); } trans.Commit(); } } }
public void NewCoreDB(string authorName, string rulesSetVersion, int startVersion) { SqliteConnection connection = new SqliteConnection ("Data Source=databases/cores/core-"+rulesSetVersion+"-by_"+authorName+".db"); connection.Open (); IDbCommand command = connection.CreateCommand (); command.CommandText = "CREATE TABLE dbInfos(" + "version varchar(32));"; command.ExecuteNonQuery (); }
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; } }
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 SqliteCommand createCommandForScore(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Score score) { var command = conn.CreateCommand(); command.Transaction = trans; command.CommandText = sql; command.Parameters.Add(new SqliteParameter("@AthleteAID", score.AthleteAID)); command.Parameters.Add(new SqliteParameter("@AthleteBID", score.AthleteBID)); command.Parameters.Add(new SqliteParameter("@Date", score.Date)); command.Parameters.Add(new SqliteParameter("@IsUnfinished", (int)(score.IsUnfinished ? 1 : 0))); command.Parameters.Add(new SqliteParameter("@TimeModified", score.TimeModified)); command.Parameters.Add(new SqliteParameter("@Guid", score.Guid.ToString())); command.Parameters.Add(new SqliteParameter("@IsDeleted", score.IsDeleted)); command.Parameters.Add(new SqliteParameter("@SportID", score.SportID)); if (score.VenueID == null) { command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value)); } else { command.Parameters.Add(new SqliteParameter("@VenueID", score.VenueID)); } if (score.Type1 == null) { command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value)); } else { command.Parameters.Add(new SqliteParameter("@Type1", score.Type1)); } command.Parameters.Add(new SqliteParameter("@PointsA", score.PointsA)); command.Parameters.Add(new SqliteParameter("@PointsB", score.PointsB)); for (int i = 1; i <= 10; ++i) { command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "A", score.InnerPointsA[i - 1])); command.Parameters.Add(new SqliteParameter("@InnerPoints" + i.ToString() + "B", score.InnerPointsB[i - 1])); } command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", score.AthleteBConfirmation)); if (score.ExtraData == null) { command.Parameters.Add(new SqliteParameter("@ExtraData", DBNull.Value)); } else { command.Parameters.Add(new SqliteParameter("@ExtraData", score.ExtraData)); } return(command); }
private void executeNonqueryCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans) { var command = conn.CreateCommand(); if (trans != null) { command.Transaction = trans; } command.CommandText = sql; command.ExecuteNonQuery(); command.Dispose(); }
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 }
private object executeScalarCommand(string sql, Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans) { var command = conn.CreateCommand(); if (trans != null) { command.Transaction = trans; } command.CommandText = sql; object result = command.ExecuteScalar(); command.Dispose(); return(result); }
//建立SQLite 資料庫 void btnSQLite_TouchUpInside(object sender, EventArgs e) { var documents = Environment.GetFolderPath(Environment.SpecialFolder.Personal); var pathToDatabase = Path.Combine(documents, "normal.db"); Mono.Data.Sqlite.SqliteConnection.CreateFile(pathToDatabase); var msg = "資料庫路徑為: " + pathToDatabase; //建立Table var connectionString = String.Format("Data Source={0};Version=3;", pathToDatabase); using (var conn = new Mono.Data.Sqlite.SqliteConnection(connectionString)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "CREATE TABLE People (PersonID INTEGER PRIMARY KEY AUTOINCREMENT , FirstName ntext, LastName ntext)"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } //新增資料 using (var conn = new Mono.Data.Sqlite.SqliteConnection(connectionString)) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "Insert into People(FirstName,LastName) Values('Terry','Lin') ;"; cmd.CommandText += "Insert into People(FirstName,LastName) Values('Ben','Lu') "; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } } // 訊息輸出並停用Button lblmsg.Text = msg; this.btnSQLite.Enabled = false; }
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 Athlete GetAthlete(int athleteID) { Mono.Data.Sqlite.SqliteConnection conn = null; try { conn = openDbConnection(); var command = conn.CreateCommand(); command.CommandText = "SELECT * FROM [Athlete] WHERE AthleteID=" + athleteID; var reader = command.ExecuteReader(); if (!reader.Read()) { throw new Exception("no records for this athleteID: " + athleteID); } Athlete athlete = new Athlete(); athlete.AthleteID = int.Parse(reader["AthleteID"].ToString()); athlete.UserName = reader["EMail"].ToString(); athlete.Name = reader["Name"].ToString(); athlete.Gender = (int)reader["Gender"]; var country = reader["Country"]; if (country != null && country != DBNull.Value) { athlete.Country = country.ToString(); } athlete.MetroID = (int)reader["MetroID"]; athlete.DOB = null; var dob = reader["DOB"]; if (dob != DBNull.Value) { athlete.DOB = (DateTime)dob; } athlete.TimeCreated = (DateTime)reader["TimeCreated"]; athlete.TimeModified = (DateTime)reader["TimeModified"]; var facebookId = reader["FacebookId"]; if (facebookId != DBNull.Value) { athlete.FacebookId = (string)facebookId; } var picture = reader["Picture"]; if (picture != DBNull.Value) { athlete.Picture = (string)picture; } var snookerAbout = reader["SnookerAbout"]; if (snookerAbout != DBNull.Value) { athlete.SnookerAbout = (string)snookerAbout; } return(athlete); } finally { if (conn != null) { conn.Close(); } } }
public void UpdateAthlete(Athlete athlete) { Mono.Data.Sqlite.SqliteConnection conn = null; try { conn = openDbConnection(); if (athlete.Name == null) { athlete.Name = ""; } if (athlete.UserName == null) { athlete.UserName = ""; } var command = conn.CreateCommand(); command.CommandText = "UPDATE [Athlete] SET " + " Name=@Name,EMail=@EMail,Gender=" + ((int)athlete.Gender).ToString() + ",DOB=@DOB,Picture=@Picture,FacebookId=@FacebookId,TimeModified=@TimeModified" + ",Country=@Country,MetroID=" + athlete.MetroID + ",SnookerAbout=@SnookerAbout" + " WHERE AthleteID=" + athlete.AthleteID; command.Parameters.Add(new SqliteParameter() { ParameterName = "@Name", Value = athlete.Name }); command.Parameters.Add(new SqliteParameter() { ParameterName = "@EMail", Value = athlete.UserName }); if (athlete.Country == null) { command.Parameters.Add(new SqliteParameter() { ParameterName = "@Country", Value = DBNull.Value }); } else { command.Parameters.Add(new SqliteParameter() { ParameterName = "@Country", Value = athlete.Country }); } if (athlete.DOB == null) { command.Parameters.Add(new SqliteParameter() { ParameterName = "@DOB", Value = DBNull.Value }); } else { command.Parameters.Add(new SqliteParameter() { ParameterName = "@DOB", Value = athlete.DOB.Value }); } if (string.IsNullOrEmpty(athlete.FacebookId)) { command.Parameters.Add(new SqliteParameter() { ParameterName = "@FacebookId", Value = DBNull.Value }); } else { command.Parameters.Add(new SqliteParameter() { ParameterName = "@FacebookId", Value = athlete.FacebookId }); } if (string.IsNullOrEmpty(athlete.Picture)) { command.Parameters.Add(new SqliteParameter() { ParameterName = "@Picture", Value = DBNull.Value }); } else { command.Parameters.Add(new SqliteParameter() { ParameterName = "@Picture", Value = athlete.Picture }); } if (athlete.SnookerAbout == null) { command.Parameters.Add(new SqliteParameter() { ParameterName = "@SnookerAbout", Value = DBNull.Value }); } else { command.Parameters.Add(new SqliteParameter() { ParameterName = "@SnookerAbout", Value = athlete.SnookerAbout }); } command.Parameters.Add(new SqliteParameter("@TimeModified", athlete.TimeModified)); command.ExecuteNonQuery(); } finally { if (conn != null) { conn.Close(); } } }
public void DataBaseRead_Chart(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()) // 쿼리로 돌아온 레코드 읽기 { string date = dataReader.GetString(0).Substring(0, 13); float maxRotation = (float)dataReader.GetDouble(3); if (Dic.Keys.Count == 0) { Dic.Add(date, maxRotation); } if (Dic.ContainsKey(date).Equals(true)) // date값이 이미 키값으로 들어가있다면 Add 하지 않고 뒤에 벨류값만 비교함. { if (Dic[date] < maxRotation) { Dic[date] = maxRotation; } } else { Dic.Add(date, maxRotation); } Debug.Log("date : " + date + ", Dic[date] : " + Dic[date]); } dataReader.Dispose(); // 생성순서와 반대로 닫아줍니다. dataReader = null; dbCommand.Dispose(); dbCommand = null; // DB에는 1개의 쓰레드만이 접근할 수 있고 동시에 접근시 에러가 발생한다. 그래서 Open과 Close는 같이 써야한다. dbConnection.Close(); dbConnection = null; // 디비 확인 코드 foreach (KeyValuePair <string, float> item in Dic) { Debug.Log(item.Key + " : " + item.Value); if (item.Value != 0) { if (item.Key.Substring(10, 1) == "0") { series1Data2.Add(item.Key.Substring(7, 1) + "/" + item.Key.Substring(11, 1) + ", " + item.Value.ToString("N0")); } else { series1Data2.Add(item.Key.Substring(7, 1) + "/" + item.Key.Substring(10, 2) + ", " + item.Value.ToString("N0")); } } } }
private List <Result> getResults(string sql) { Mono.Data.Sqlite.SqliteConnection conn = null; try { conn = openDbConnection(); var command = conn.CreateCommand(); command.CommandText = sql; var reader = command.ExecuteReader(); List <Result> results = new List <Result>(); while (reader.Read()) { Result result = new Result(); result.AthleteID = (int)reader["AthleteID"]; result.ResultID = int.Parse(reader["ResultID"].ToString()); result.ResultTypeID = (int)reader["ResultTypeID"]; result.Date = null; if (reader["Date"] != DBNull.Value) { result.Date = (DateTime)reader["Date"]; } result.Time = null; if (reader["Time"] != DBNull.Value) { result.Time = (double)reader["Time"]; } result.Count = null; if (reader["Count"] != DBNull.Value) { result.Count = (int)reader["Count"]; } result.Count2 = null; if (reader["Count2"] != DBNull.Value) { result.Count2 = (int)reader["Count2"]; } result.Notes = (string)reader["Notes"]; result.TimeModified = (DateTime)reader["TimeModified"]; result.Guid = Guid.Parse(reader["Guid"].ToString()); string isDeleted = reader["IsDeleted"].ToString().ToLower(); if (isDeleted == "true") { result.IsDeleted = true; } else if (isDeleted == "false") { result.IsDeleted = false; } else { result.IsDeleted = int.Parse(isDeleted) > 0; } if (reader["OpponentAthleteID"] != DBNull.Value) { result.OpponentAthleteID = (int)reader["OpponentAthleteID"]; } result.OpponentConfirmation = (int)reader["OpponentConfirmation"]; if (reader["Type1"] != DBNull.Value) { result.Type1 = (int)reader["Type1"]; } if (reader["Details1"] != DBNull.Value) { result.Details1 = (string)reader["Details1"]; } if (reader["VenueID"] != DBNull.Value) { result.VenueID = (int)reader["VenueID"]; } results.Add(result); } return(results); } 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(); } } }
private void addResult(Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Result result) { var command = conn.CreateCommand(); command.Transaction = trans; command.CommandText = @"INSERT INTO [Result] ([AthleteID],[ResultTypeID],[Time],[Distance],[Count],[Count2],[Date],[Notes],[TimeModified],[Guid],[IsDeleted],[VenueID],[OpponentAthleteID],[Type1],[Details1],[OpponentConfirmation]) VALUES (@AthleteID,@ResultTypeID,@Time,@Distance,@Count,@Count2,@Date,@Notes,@TimeModified,@Guid,@IsDeleted,@VenueID,@OpponentAthleteID,@Type1,@Details1,@OpponentConfirmation)"; command.Parameters.Add(new SqliteParameter("@AthleteID", result.AthleteID)); command.Parameters.Add(new SqliteParameter("@ResultTypeID", result.ResultTypeID)); if (result.Time != null) { command.Parameters.Add(new SqliteParameter("@Time", result.Time.Value)); } else { command.Parameters.Add(new SqliteParameter("@Time", DBNull.Value)); } if (result.Distance != null) { command.Parameters.Add(new SqliteParameter("@Distance", result.Distance.Value)); } else { command.Parameters.Add(new SqliteParameter("@Distance", DBNull.Value)); } if (result.Count != null) { command.Parameters.Add(new SqliteParameter("@Count", result.Count.Value)); } else { command.Parameters.Add(new SqliteParameter("@Count", DBNull.Value)); } if (result.Count2 != null) { command.Parameters.Add(new SqliteParameter("@Count2", result.Count2.Value)); } else { command.Parameters.Add(new SqliteParameter("@Count2", DBNull.Value)); } if (result.Date != null) { command.Parameters.Add(new SqliteParameter("@Date", result.Date.Value)); } else { command.Parameters.Add(new SqliteParameter("@Date", DBNull.Value)); } command.Parameters.Add(new SqliteParameter("@Notes", result.Notes ?? "")); command.Parameters.Add(new SqliteParameter("@TimeModified", result.TimeModified)); command.Parameters.Add(new SqliteParameter("@Guid", result.Guid.ToString())); command.Parameters.Add(new SqliteParameter("@IsDeleted", result.IsDeleted)); if (result.VenueID != null) { command.Parameters.Add(new SqliteParameter("@VenueID", result.VenueID.Value)); } else { command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value)); } if (result.OpponentAthleteID != null) { command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", result.OpponentAthleteID.Value)); } else { command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", DBNull.Value)); } if (result.Type1 != null) { command.Parameters.Add(new SqliteParameter("@Type1", result.Type1.Value)); } else { command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value)); } if (result.Details1 != null) { command.Parameters.Add(new SqliteParameter("@Details1", result.Details1)); } else { command.Parameters.Add(new SqliteParameter("@Details1", DBNull.Value)); } command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", result.OpponentConfirmation)); command.ExecuteNonQuery(); command.Dispose(); }
private void updateResult(Mono.Data.Sqlite.SqliteConnection conn, Mono.Data.Sqlite.SqliteTransaction trans, Result result) { var command = conn.CreateCommand(); command.Transaction = trans; command.CommandText = @"UPDATE [Result] SET [AthleteID]=@AthleteID,[ResultTypeID]=@ResultTypeID,[Time]=@Time,[Distance]=@Distance,[Count]=@Count,[Count2]=@Count2,[Date]=@Date,[Notes]=@Notes,[TimeModified]=@TimeModified,[Guid]=@Guid,[VenueID]=@VenueID,[OpponentAthleteID]=@OpponentAthleteID,[Type1]=@Type1,[Details1]=@Details1,[OpponentConfirmation]=@OpponentConfirmation WHERE ResultID=" + result.ResultID; command.Parameters.Add(new SqliteParameter("@AthleteID", result.AthleteID)); command.Parameters.Add(new SqliteParameter("@ResultTypeID", result.ResultTypeID)); if (result.Time != null) { command.Parameters.Add(new SqliteParameter("@Time", result.Time.Value)); } else { command.Parameters.Add(new SqliteParameter("@Time", DBNull.Value)); } if (result.Distance != null) { command.Parameters.Add(new SqliteParameter("@Distance", result.Distance.Value)); } else { command.Parameters.Add(new SqliteParameter("@Distance", DBNull.Value)); } if (result.Count != null) { command.Parameters.Add(new SqliteParameter("@Count", result.Count.Value)); } else { command.Parameters.Add(new SqliteParameter("@Count", DBNull.Value)); } if (result.Count2 != null) { command.Parameters.Add(new SqliteParameter("@Count2", result.Count2.Value)); } else { command.Parameters.Add(new SqliteParameter("@Count2", DBNull.Value)); } if (result.Date != null) { command.Parameters.Add(new SqliteParameter("@Date", result.Date.Value)); } else { command.Parameters.Add(new SqliteParameter("@Date", DBNull.Value)); } command.Parameters.Add(new SqliteParameter("@Notes", result.Notes ?? "")); command.Parameters.Add(new SqliteParameter("@TimeModified", result.TimeModified)); command.Parameters.Add(new SqliteParameter("@Guid", result.Guid.ToString())); if (result.VenueID != null) { command.Parameters.Add(new SqliteParameter("@VenueID", result.VenueID.Value)); } else { command.Parameters.Add(new SqliteParameter("@VenueID", DBNull.Value)); } if (result.OpponentAthleteID != null) { command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", result.OpponentAthleteID.Value)); } else { command.Parameters.Add(new SqliteParameter("@OpponentAthleteID", DBNull.Value)); } if (result.Type1 != null) { command.Parameters.Add(new SqliteParameter("@Type1", result.Type1.Value)); } else { command.Parameters.Add(new SqliteParameter("@Type1", DBNull.Value)); } if (result.Details1 != null) { command.Parameters.Add(new SqliteParameter("@Details1", result.Details1)); } else { command.Parameters.Add(new SqliteParameter("@Details1", DBNull.Value)); } command.Parameters.Add(new SqliteParameter("@OpponentConfirmation", result.OpponentConfirmation)); command.ExecuteNonQuery(); command.Dispose(); }
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(); } } }