/** * Updates a Slot1 and Slot2 of any GEItem in the database instantly. * @param GEItem */ public void updateGEItemSlotsDatabase(GEItem geItem) { try { SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName); /** * Each time you do in GESession.cs [checkOffer or abortOffer] * A Slot1 and Slot2 is generated but those Slot's themselves are never saved, less data like this. * Only thing thats saved is how much to generate of those Slots next time you call [checkOffer]. * Even generated Slots check against how much they can truly take out. */ if (geItem is BuyOffer) { db.ExecuteNonQuery("UPDATE grandExchangeBuying SET collectedItem = " + geItem.getAmountCollectedItem() + ", collectedGold = " + geItem.getAmountCollectedGold() + " WHERE slot = " + geItem.getSlot() + " AND playerHash = " + geItem.getPlayerHash()); } else if (geItem is SellOffer) { db.ExecuteNonQuery("UPDATE grandExchangeSelling SET collectedItem = " + geItem.getAmountCollectedItem() + ", collectedGold = " + geItem.getAmountCollectedGold() + " WHERE slot = " + geItem.getSlot() + " AND playerHash = " + geItem.getPlayerHash()); } db.CloseDatabase(); } catch (Exception e) { misc.WriteError("GrandExchange Error]: " + e.Message); } }
public void CreateDirectory(FileSystemPath path) { DataTable parentDir = db.GetDataTable("SELECT * FROM directories WHERE fullpath = @path", new Dictionary <string, object>() { { "@path", path.ParentPath.Path } }); string sqlCreateDirectory = "INSERT INTO directories (dirname, fullpath) VALUES (@dirname, @fullpath)"; db.ExecuteNonQuery(sqlCreateDirectory, new Dictionary <string, object>() { { "@dirname", path.EntityName }, { "@fullpath", path.Path } }); double now = DateTime.Now.UnixTimeStamp(); string sqlCreateEntity = "INSERT INTO entities (fname, dir, cdate, mdate, isdir, accessdate, size) VALUES (@fname, @dir, @cdate, @mdate, @isdir, @accessdate, @size)"; db.ExecuteNonQuery(sqlCreateEntity, new Dictionary <string, object>() { { "@fname", path.EntityName }, { "@dir", parentDir.Rows[0]["id"] }, { "@cdate", now }, { "@mdate", now }, { "@isdir", 1 }, { "@accessdate", now }, { "@size", 0 } }); }
private void ReqOnResponseReceived(File obj) { switch (op) { case Operation.CREATE: db.ExecuteNonQuery( "INSERT INTO gdrive (filename, fileid, directory) VALUES (@fileName, @newFileId, 0)", new Dictionary <string, object>() { { "@fileName", fileName }, { "@newFileID", obj.Id } }); break; case Operation.UPDATE: db.ExecuteNonQuery( "UPDATE gdrive set fileid = @newFileId WHERE filename = @fileName and directory = 0", new Dictionary <string, object>() { { "@newFileId", obj.Id }, { "@fileName", fileName } }); break; case Operation.NONE: default: break; } }
private void InitializeTables(SQLiteDatabase db) { db.ExecuteNonQuery("BEGIN EXCLUSIVE"); for(int i = 0; i < CREATE_Commands.Length; i++) { db.ExecuteNonQuery(CREATE_Commands[i]); } }
private void InitializeTables(SQLiteDatabase db) { db.ExecuteNonQuery("BEGIN EXCLUSIVE"); for (int i = 0; i < CREATE_Commands.Length; i++) { db.ExecuteNonQuery(CREATE_Commands[i]); } }
private static void ParseRaceHistory(int heatNo) { HeatResult heat = ClubSpeedAustin.GetHeatResults(heatNo); if (heat == null) { return; } string racersQuery = "INSERT INTO Racers VALUES "; string racesQuery = "INSERT INTO Races VALUES"; string lapsQuery = "INSERT INTO Laps VALUES"; for (int i = 0; i < heat.Racers.Count; i++) { string racerName = heat.Racers[i].RacerName.Replace("'", "''"); racersQuery += string.Format("('{0}', '{1}'){2}", heat.Racers[i].CustId, racerName, (i < heat.Racers.Count - 1) ? "," : ";"); racesQuery += string.Format("('{0}', '{1}', '{2}', '{3}'){4}", heat.HeatNo, heat.Racers[i].CustId, heat.Racers[i].Kart, heat.DateTime.ToString("yyyy-MM-dd HH:mm:ss"), (i < heat.Racers.Count - 1) ? "," : ";"); } for (int i = 0; i < heat.Laps.Count; i++) { lapsQuery += string.Format("('{0}', '{1}', '{2}', '{3}'){4}", heat.HeatNo, heat.Laps[i].CustId, heat.Laps[i].LapNum, heat.Laps[i].LapTime, (i < heat.Laps.Count - 1) ? "," : ";"); } _database.ExecuteNonQuery(racersQuery + racesQuery + lapsQuery); }
static void SafeMain1(string[] args) { string name = "test.db"; if (File.Exists(name)) { File.Delete(name); } using (var db = new SQLiteDatabase(name)) { using (var tok = db.GetTokenizer("unicode61", "remove_diacritics=0", "tokenchars=.=", "separators=X")) { Console.WriteLine(string.Join(Environment.NewLine, tok.Tokenize("hello friends"))); //GC.Collect(1000, GCCollectionMode.Forced, true); } var sp = new StopWordTokenizer(db); Console.WriteLine(db.Configure(SQLiteDatabaseConfiguration.SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, true, 1)); db.SetTokenizer(sp); db.ExecuteNonQuery("CREATE VIRTUAL TABLE tok1 USING fts3tokenize('" + sp.Name + "');"); for (int i = 0; i < 10; i++) { var tokens = db.LoadRows(@"SELECT token, start, end, position FROM tok1 WHERE input=?;", "This is a test sentence."); Console.Write(tokens.ToArray().Length); //GC.Collect(1000, GCCollectionMode.Forced, true); } //Console.WriteLine(string.Join(Environment.NewLine, tokens.Select(t => t["token"]))); } }
static void CreateBooksTable() { string createTable = "create table Books (TITLE varchar(50), " + "AUTHOR varchar(50), PUBDATE varchar(10), ISBN int)"; db.ExecuteNonQuery(createTable); }
public SQLiteDataStorage(string database = ":memory:") { Db = new SQLiteDatabase(database); foreach (string command in PRAGMA_Commands) { Db.ExecuteNonQuery(command); } }
/// <summary> /// 创建数据库 /// </summary> /// <returns>true:创建成功,false:创建失败</returns> public override bool CreateDatabase() { int count = 0; string path = System.IO.Path.GetDirectoryName(this.file); if (!System.IO.Directory.Exists(path)) { System.IO.Directory.CreateDirectory(path); } using (var db = new SQLiteDatabase(this.connectionString)) { count += db.ExecuteNonQuery("create table _tb_create_db_temp(id int not null)"); count += db.ExecuteNonQuery("drop table _tb_create_db_temp"); } return(count > 0); }
//private DeDupeFileSystem fs; private void setUpDatabase(SQLiteDatabase db) { // Not that this matters - as input is not coming from externally.... // But I'm sure some junior cyber "expert" would bitch about it so.... db.ExecuteNonQuery(System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "/migrations/sqlite/1.sql")); //db.ExecuteNonQuery("INSERT INTO settings (`key`, `value`) VALUES ('version', '1.0')"); db.ExecuteNonQuery("INSERT INTO settings (`key`, `value`) VALUES (@setting, @value)", new Dictionary <string, object>() { { "@setting", "version" }, { "@value", "0.3b" } }); //db.ExecuteNonQuery("INSERT INTO settings (`key`, `value`) VALUES ('migration', '1')"); db.ExecuteNonQuery("INSERT INTO settings (`key`, `value`) VALUES (@setting, @value)", new Dictionary <string, object>() { { "@setting", "migration" }, { "@value", "1" } }); }
/// <summary> /// This tests the SQLite in-memory DB by creating some data and then selecting it /// </summary> public static void TestDatabase() { var watch = new DiagnosticTimer(); var db = new SQLiteDatabase(":memory:"); watch.Checkpoint("Create DB"); db.ExecuteNonQuery("CREATE TABLE Root (intIndex INTEGER PRIMARY KEY, strIndex TEXT, nr REAL)"); watch.Checkpoint("Create table 1"); db.ExecuteNonQuery("CREATE TABLE This (intIndex INTEGER PRIMARY KEY, strIndex TEXT, nr REAL)"); watch.Checkpoint("Create table 2"); db.ExecuteNonQuery("CREATE INDEX RootStrIndex ON Root (strIndex)"); const string insertCommand = "INSERT INTO Root VALUES (?,?,?)"; int i; var stmt = new SQLiteVdbe(db, insertCommand); long key = 1999; for (i = 0; i < 10000; i++) { key = (3141592621L * key + 2718281829L) % 1000000007L; stmt.Reset(); stmt.BindLong(1, key); stmt.BindText(2, key.ToString()); stmt.BindDouble(3, 12.34); stmt.ExecuteStep(); } stmt.Close(); watch.Checkpoint("Insert 10000 rows"); i = 0; var c1 = new SQLiteVdbe(db, "SELECT * FROM Root ORDER BY intIndex LIMIT 5000"); while (c1.ExecuteStep() != Sqlite3.SQLITE_DONE) { long intKey = c1.Result_Long(0); key = intKey; i += 1; } c1.Close(); var diagnostic = watch.LastCheckpoint("Select 5000 sorted rows"); MessageBox.Show(diagnostic); }
public static bool Delete(string userId, string profileId) { bool deleted = false; SQLiteDatabase db = new SQLiteDatabase(true); // Can only delete planned string sql = @"delete from cycli_profile_spots where ProfileId = @p"; db.ExecuteNonQuery(sql, "@p", profileId); sql = @"delete from cycli_profiles where ProfileId = @p"; db.ExecuteNonQuery(sql, "@p", profileId); try { db.CommitTransaction(); deleted = true; } // No need to close Db as this is done by CommitTranaction catch (Exception ex) { db.RollbackTransaction(); Console.WriteLine(ex); } return deleted; }
internal bool SaveAnswers(IList <Answer> answers) { SQLiteDatabase db = new SQLiteDatabase(); StringBuilder sql = new StringBuilder(Constants.SqlSaveAnswers); foreach (Answer answer in answers) { sql.AppendFormat("({0},{1},{2}),", answer.QuestionId, answer.ParticipantId, answer.IsCorrect ? 1: 0); } return(db.ExecuteNonQuery(sql.ToString().TrimEnd(',')) > 0); }
public static string ChangeFriendCode(string userId) { SQLiteDatabase db = new SQLiteDatabase(); // Some logic here - we only allow a new friend if // (a) The friendship does not already exist // (b) The friendCode is correct string g = Guid.NewGuid().ToString(); string sqlFriendCode = "update cycli_riders set friendCode=@g where UserId = @u"; db.ExecuteNonQuery(sqlFriendCode, "@g", g, "@u", userId); // This query checks that the user has not already been invited db.Close(); return g; }
private void runMigrations(SQLiteDatabase db) { string[] files = System.IO.Directory.GetFiles(System.IO.Directory.GetCurrentDirectory() + "/migrations/sqlite/").Select(Path.GetFileName).ToArray(); files = files.OrderByNatural(file => file).ToArray(); //int lastMigration = Int32.Parse(db.ExecuteScalar("SELECT value FROM settings WHERE key = 'migration'")); int lastMigration = Int32.Parse(db.ExecuteScalar("SELECT value FROM settings WHERE key = @migration", new Dictionary <string, object>() { { "@migration", "migration" } })); Log.Instance.Add($"lastMigration = {lastMigration}"); if (files.Length > 0) { int highestMigration = lastMigration; foreach (string file in files) { int migrationNumber = Int32.Parse(file.Split('.')[0]); Log.Instance.Add($"migrationNumber = {migrationNumber}"); if (migrationNumber > lastMigration) { db.ExecuteNonQuery(System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + $"/migrations/sqlite/{migrationNumber}.sql")); highestMigration = migrationNumber; Log.Instance.Add($"highestMigration = {highestMigration}"); } } //db.ExecuteNonQuery($"UPDATE settings set key = '{highestMigration}' WHERE value = 'migration'"); Log.Instance.Add($"key = {highestMigration}"); db.ExecuteNonQuery("UPDATE settings set value = @highestMigration WHERE key = @migration", new Dictionary <string, object>() { { "@highestMigration", highestMigration }, { "@migration", "migration" } }); } }
private SQLiteDatabase OpenDB(string fileName) { if (File.Exists(fileName)) { File.Delete(fileName); } var db = new SQLiteDatabase(fileName); for (int i = 0; i < PRAGMA_Commands.Length; i++) { db.ExecuteNonQuery(PRAGMA_Commands[i]); } return(db); }
public void AddFile(string file, byte[] data) { string check = _db.ExecuteScalar("SELECT name FROM filestorage WHERE name = @name", new Dictionary <string, object>() { { "@name", file } }); if (check != string.Empty) { throw new Exception("File already exists in storage - no support for overwriting"); } long start, end, size; size = data.Length; if (_file != string.Empty) { using (var fs = new FileStream(_file, FileMode.OpenOrCreate)) { long endPoint = fs.Length; fs.Seek(endPoint, SeekOrigin.Begin); start = fs.Position; end = start + size; fs.Write(data); } } else { long endPoint = _stream.Length; _stream.Seek(endPoint, SeekOrigin.Begin); start = _stream.Position; end = start + size; _stream.Write(data); } _db.ExecuteNonQuery( "INSERT INTO filestorage (name, start, end, size) VALUES (@name, @start, @end, @size)", new Dictionary <string, object>() { { "@name", file }, { "@start", start }, { "@end", end }, { "@size", size } }); }
public EventRecorder(string pPath, string pDatabaseFilename) { //check if file exists if (System.IO.File.Exists(pPath + pDatabaseFilename)) { _database = new SQLiteDatabase(pPath + pDatabaseFilename); Console.WriteLine("opened old database"); } else { if(!System.IO.Directory.Exists(pPath)) System.IO.Directory.CreateDirectory(pPath); SQLiteConnection.CreateFile(pPath + pDatabaseFilename); _database = new SQLiteDatabase(pPath + pDatabaseFilename); _database.ExecuteNonQuery( @"CREATE TABLE [events] ( [id] INTEGER NOT NULL PRIMARY KEY, [name] VARCHAR(64) NULL, [time_start] INTEGER NOT NULL, [time_end] INTEGER NOT NULL)"); } }
public static bool ReturnResults(string SQLStatement, string DatabaseLocation, ref DataSet ds, out string message) { //Add a call here to a parser that will //ensure the SQLStatement is properly formed if (SQLStatement.ToLower().StartsWith("select") || SQLStatement.ToLower().StartsWith("pragma")) { SQLiteDatabase db = new SQLiteDatabase(DatabaseLocation); ds = db.ExecuteQuery(SQLStatement); message = string.Format(ds != null ? "ExecuteQuery: ok" : "ExecuteQuery Failed"); return(ds == null); } else { int result = SQLiteDatabase.ExecuteNonQuery(SQLStatement); ds = null; message = string.Format("ExecuteNonQuery: Records Modified {0}", result); return(result > -1); } }
public void CustomTokenizer_tokenize() { using (var db = new SQLiteDatabase(":memory:")) { using (var tok = db.GetTokenizer("unicode61", "remove_diacritics=0", "tokenchars=.=", "separators=X")) { Assert.AreEqual("hellofriends", string.Join(string.Empty, tok.Tokenize("hello friends"))); GC.Collect(1000, GCCollectionMode.Forced, true); } var sp = new StopWordTokenizer(db); Assert.AreEqual(1, db.Configure(SQLiteDatabaseConfiguration.SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, true, 1)); db.SetTokenizer(sp); db.ExecuteNonQuery("CREATE VIRTUAL TABLE tok1 USING fts3tokenize('" + sp.Name + "');"); var tokens = db.LoadRows(@"SELECT token, start, end, position FROM tok1 WHERE input=?;", "This is a test sentence."); Assert.AreEqual("testsentence", string.Join(string.Empty, tokens.Select(t => t["token"]))); } }
/// <summary> /// Handles the OK button, adds a word /// </summary> private void btnOK_Click(object sender, EventArgs e) { // Checks whether the description and word are filled in if (txtDesc.Text != "" && txtWord.Text != "") { SQLiteDatabase db = new SQLiteDatabase(); /* Attempts an insert and if successful it clears the desc and word boxes * to allow further entry, else it shows a messagebox and since most errors * will be that the word is already there, the error is shown */ try { db.ExecuteNonQuery(string.Format("INSERT INTO words VALUES (\"{0}\", \"{1}\")", txtWord.Text, txtDesc.Text)); txtDesc.Clear(); txtWord.Clear(); } catch (Exception ex) { MessageBox.Show( string.Format("Something went wrong, the error is given below:\n\n{0}", ex.Message), "Error", MessageBoxButtons.OK, MessageBoxIcon.Warning); } } }
/// <summary> /// Adds a student to the system /// </summary> /// <param name="name">The student's full name</param> /// <param name="pass">The student's password</param> /// <param name="stuClass">The student's class</param> public static void addStudent(string name, string pass, string stuClass) { /* The following variables are * db - Instance of the SQLiteDatabase class * hash - The SHA-256 hash of the password to be added as a string * added - Used to avoid a bug with adding students if one has been deleted * attempts - See above * studentID - The ID of the student to be added */ SQLiteDatabase db = new SQLiteDatabase(); string hash = Password.hashAsString(pass); bool added = false; int attempts = 0; int studentID = Convert.ToInt16(db.ExecuteScalar("SELECT id FROM students ORDER BY id DESC")) + 1; // While the db call hasn't executed while (!added) { try { db.ExecuteNonQuery(string.Format("INSERT INTO students VALUES ({0}, \"{1}\", \"{2}\", \"{3}\");", studentID, name, stuClass, hash)); added = true; } // Checks the number of attempts and if they're less than 10 it adds one to the studentID catch { if (attempts > 9) { throw new Exception("Too many attempts"); } studentID++; attempts++; } } }
public void Schedule() { // Set race to scheduled string sqlRiderUpdate = @"update cycli_race_riders set Status='Scheduled' " + "where RaceId=@r and Status='Joined'"; string sqlRaceUpdate = @"update cycli_races set Status='Scheduled' " + "where RaceId=@r and Status='Planned'"; SQLiteDatabase db = new SQLiteDatabase(true); try { db.ExecuteNonQuery(sqlRaceUpdate, "@r", this.RaceId); db.ExecuteNonQuery(sqlRiderUpdate, "@r", this.RaceId); db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch (Exception ex1) { } } finally { // Don't need to close on a transaction } this.Status = @"Scheduled"; foreach (Participant p in this.Participants) { if (p.Status == @"Joined") { p.Status = @"Scheduled"; } } }
public void FinishRider(string userId) { SQLiteDatabase db = new SQLiteDatabase(); try { string sqlRiderUpdate = @"update cycli_race_riders set Status='Finished' " + "where RaceId=@r and UserId=@u and Status='Started'"; db.ExecuteNonQuery(sqlRiderUpdate, "@r", this.RaceId, "@u", userId); } finally { db.Close(); } // SHould always be here _Participants[userId].Status = @"Finished"; }
public static void SetRiderHandicap(string userId, string riderId, string raceId, int handicap) { SQLiteDatabase db = new SQLiteDatabase(); // Update with checks on race status and ownership string sql = @"update cycli_race_riders set Handicap=@h " + "where RaceId=@r and UserId=@rr and (Status='Invited' or Status='Joined') " + "and exists (select 1 from cycli_races where RaceId=@r1 and Status='Planned' and RaceDirectorId=@u)"; db.ExecuteNonQuery(sql, "@h", handicap, "@r", raceId, "@rr", riderId,"@r1",raceId, "@u", userId); db.Close(); }
public static Race New(string userId) { Race race = null; UserRider rider = UserRider.Load(userId); if (rider != null) { // Check we haven't exeeded our limit - this is a backstop test // First check prevents a user creating races Race[] existingRaces = Race.LoadPlannedOrRacing(userId); if (existingRaces.Length < rider.MaximumRaces) { string newRaceId = Guid.NewGuid().ToString(); long startDateTime = Utilities.DbTime.ToDbSecs(DateTime.UtcNow.AddDays(7)); SQLiteDatabase db = new SQLiteDatabase(true); string sql = "insert into cycli_races (RaceId, StartDateTime, RaceDirectorId) " + "values (@r, @s, @rd)"; db.ExecuteNonQuery(sql, "@r", newRaceId, "@s", startDateTime, "@rd", userId); sql = "insert into cycli_race_riders (UserId, RaceId) " + "values (@u, @r)"; db.ExecuteNonQuery(sql, "@r", newRaceId, "@u", userId); try { db.CommitTransaction(); try { race = Load(newRaceId); } catch (Exception ex1) { Console.WriteLine(ex1); } } // No need to close Db as this is done by CommitTranaction catch (Exception ex) { db.RollbackTransaction(); Console.WriteLine(ex); } } } return race; }
public static void JoinRace(string userId, string raceId) { // What is the race status SQLiteDatabase db = new SQLiteDatabase(); string sql = @"update cycli_race_riders set Status='Joined' where UserId=@u and RaceId In (" + "select r.RaceId from cycli_races r, cycli_race_riders rr where r.RaceId=@r and r.RaceId = rr.RaceId and r.Status='Planned')"; db.ExecuteNonQuery(sql, "@r", raceId, "@u", userId); db.Close(); Race race = Race.Load(raceId); if (race != null) { CycliManager.Instance.SendRiderJoin(race, userId); } }
public static void BailVirtualRider(string userId, string riderId, string raceId) { SQLiteDatabase db = new SQLiteDatabase(); string sql = "delete from cycli_race_riders " + "where Status='Joined' and RaceId=@r and UserId=@f " + "and exists (select 1 from cycli_races where RaceId=@r1 and Status='Planned' and RaceDirectorId=@u)"; db.ExecuteNonQuery(sql, "@r", raceId, "@f", riderId, "r1", raceId, "@u", userId); db.Close(); Race race = Race.Load(raceId); if (race != null) { CycliManager.Instance.SendRiderBail(race, riderId); } }
public void Save(string userId) { if (userId != this.UserId) { // stop anyone other than the owner changing return; } long timeNowDb = Utilities.DbTime.ToDbSecs(DateTime.UtcNow); // It's an update string sqlProfile = @"update cycli_profiles set Name=@n, Description=@d, Favourite=@f, ModifiedOn=@m where ProfileId=@p"; string sqlDeleteSpots = @"delete from cycli_profile_spots where ProfileId=@p"; string sqlSpot = @"insert into cycli_profile_spots (ProfileId, X, Y, PointOrder) values (@p, @x, @y, @i)"; SQLiteDatabase db = new SQLiteDatabase(true); try { db.ExecuteNonQuery(sqlProfile, "@n", this.Name, "@d", this.Description, "@f", this.Favourite.ToString(), "@m", timeNowDb, "@p", this.ProfileId); db.ExecuteNonQuery(sqlDeleteSpots,"@p", this.ProfileId); int i = 0; foreach (float[] pt in data) { db.ExecuteNonQuery(sqlSpot, "@p", this.ProfileId, "@x", pt[0], "@y", pt[1], "@i",i); i++; } db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch (Exception ex1) { } } finally { } }
public static void Remove(string userId, string friendId) { SQLiteDatabase db = new SQLiteDatabase(); // This query checks that the user has not already been invited string sql = "delete from cycli_friends where (userId=@u1 and friendId=@f1) or (friendId=@u2 and userId=@f2)"; db.ExecuteNonQuery(sql, "@u1", userId, "@f1", friendId, "@u2", userId, "@f2", friendId); db.Close(); }
public override void ExecuteNonQuery(string query) { Trace.TraceInformation($"SQLite ExecuteNonQuery '{query}'"); Db.ExecuteNonQuery(query); }
public void WebRequestPostJson(string url, string postData) { var httpWebRequest = (HttpWebRequest)WebRequest.Create(url); httpWebRequest.ContentType = "application/json"; httpWebRequest.Method = "POST"; using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream())) { string sql = ""; string apcode = "AP-0000"; streamWriter.Write(postData); streamWriter.Flush(); streamWriter.Close(); try { var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse(); using (var streamReader = new StreamReader(httpResponse.GetResponseStream())) { var result = streamReader.ReadToEnd(); // MessageBox.Show(result.ToString()); //using (SQLiteDatabase db = new SQLiteDatabase(@"DATA\bcapinvoicesub.db")) //{ if (result.ToString().StartsWith("[")) { sql = result.ToString().Replace("[", "").Replace("]", ""); } else { sql = result.ToString(); } // string strcon=@"Server=MACAIR\SQLEXPRESS;Database=minismart;User Id=sa;Password=password;"; if (sql != string.Empty) { var cuscode = JsonSerializer.DeserializeFromString <BCAPINVOICESUB>(sql); if (getMSSQLRows(string.Format("select docno from bcapinvoice where docno='{0}'", cuscode.DOCNO.Trim()), strcon).Rows.Count == 0) { DataTable dtap = getMSSQLRows(string.Format("select code from bcap where name1 like '{0}'", cuscode.APCODE), strcon); if (dtap.Rows.Count > 0) { apcode = dtap.Rows[0][0].ToString(); } else { DataTable dtt = getMSSQLRows("select max(code) from bcap", strcon); if (dtt.Rows.Count > 0) { apcode = (Convert.ToInt16(dtt.Rows[0][0].ToString().Substring(dtt.Rows[0][0].ToString().IndexOf("-") + 1, 4)) + 1).ToString(); for (int i = 0; i < 5; i++) { apcode = "0" + apcode; } apcode = "AP-" + apcode.Substring(apcode.Length - 4, 4); } insertMSSQL(string.Format("insert into bcap(code,name1,address)values('{0}','{1}','{2}')", apcode, _apcode, "..."), strcon); } sql = string.Format("insert into bcapinvoice(docno,docdate,apcode,iscompletesave,taxtype,billtype,grirbillstatus)values('{0}','{1}','{2}',1,1,1,1)", cuscode.DOCNO.Trim(), ConvertToDate(cuscode.DOCDATE), apcode, cuscode.ID); insertMSSQL(sql, strcon); } sql = string.Format("select itemcode,sum(qty) as qty from bcapinvoicesub where docno='{0}' and itemcode='{1}' group by docno,itemcode", cuscode.DOCNO, cuscode.ITEMCODE); if (getMSSQLRows(sql, strcon).Rows.Count > 0) { int _qty = Convert.ToInt16(getMSSQLRows(sql, strcon).Rows[0]["qty"]) + 1; sql = string.Format("update bcapinvoicesub set qty={0},cnqty={0},grremainqty={0},amount=price*({0}),netamount=amount/1.05,homeamount=amount/1.07 where docno='{1}' and itemcode='{2}'", _qty, cuscode.DOCNO, cuscode.ITEMCODE); insertMSSQL(sql, strcon); } else { sql = string.Format("insert into bcapinvoicesub(docno,docdate,apcode,itemcode,itemname,qty,cnqty,grremainqty,stocktype,unitcode,shelfcode,whcode,price,amount,netamount,homeamount)values('{5}','{6}','{8}','{0}','{1}',{2},{2},{2},2,'001','001','001',{3},{4},{7},{7})", cuscode.ITEMCODE, cuscode.ITEMNAME.ToString().Replace("'", "นิ้ว"), cuscode.QTY, cuscode.SALEPRICE, (Convert.ToDouble(cuscode.QTY) * Convert.ToDouble(cuscode.SALEPRICE)).ToString(), cuscode.DOCNO, ConvertToDate(cuscode.DOCDATE), ((Convert.ToDouble(cuscode.QTY) * Convert.ToDouble(cuscode.SALEPRICE)) / 1.07).ToString(), apcode); insertMSSQL(sql, strcon); db.ExecuteNonQuery(string.Format("insert into bcapinvoicesub (docno,docdate,apcode,itemcode,price,qty,amount,cost)values('{0}','{1}','{2}','{3}',{4},{5},{6},{7})", cuscode.DOCNO, ConvertToDate(cuscode.DOCDATE), "AP-0001", cuscode.ITEMCODE, cuscode.SALEPRICE, cuscode.QTY, (Convert.ToDouble(cuscode.QTY) * Convert.ToDouble(cuscode.SALEPRICE)), cuscode.SALEPRICE)); } sql = string.Format("update bcitem set lastbuyprice={0} where code='{1}'", cuscode.SALEPRICE, cuscode.ITEMCODE); db.ExecuteNonQuery(sql); if (getMSSQLRows(string.Format("select code from bcitem where code='{0}'", cuscode.ITEMCODE), strcon).Rows.Count == 0) { sql = string.Format("insert into bcitem(id,code,name1,defstkunitcode,vendorcode)values('{4}','{0}','{1}','{2}','{3}')", cuscode.ITEMCODE, cuscode.ITEMNAME, "001", cuscode.SUPPLIERCO); insertMSSQL(sql, strcon); } sql = string.Format("update bcapinvoice set AfterDiscount={0},BeforeTaxAmount={0}/1.07,TaxAmount={0}-({0}/1.07),TotalAmount={0},BillBalance={0},NetDebtAmount={0},HomeAmount={0}/1.07,SumOfItemAmount={0}", string.Format("(select sum(amount) from bcapinvoicesub where docno='{0}')", cuscode.DOCNO)); insertMSSQL(sql, strcon); sql = string.Format("insert into bcserialmaster(CtrlNo,serialno,ItemCode,ReceiveDate,RegisDate,ReceiveDocNo,ApCode,ItemStatus,RegisterNo,ShelfCode,WHCode,ActiveStatus)values('{0}','{0}','{1}','{2}','{2}','{3}','AP-0001',0,'{0}','001','001',1)", cuscode.SERIALNO, cuscode.ITEMCODE, ConvertToDate(cuscode.DOCDATE), cuscode.DOCNO); insertMSSQL(sql, strcon); sql = string.Format("insert into bcserialtrans(CtrlNo,serialno,ItemCode,DocDate,DocNo,ShelfCode,WHCode,savefrom)values('{0}','{0}','{1}','{2}','{3}','001','001',2)", cuscode.SERIALNO, cuscode.ITEMCODE, ConvertToDate(cuscode.DOCDATE), cuscode.DOCNO); insertMSSQL(sql, strcon); sql = string.Format("insert into bcserialprintform(StartCtrlNo,StopCtrlNo,serialtext,ItemCode,DocNo,savefrom)values('{0}','{0}','{0}','{1}','{2}',2)", cuscode.SERIALNO, cuscode.ITEMCODE, cuscode.DOCNO); insertMSSQL(sql, strcon); sql = string.Format("INSERT INTO ProcessStock (ItemCode,DocDate,ProcessFlag,FlowStatus,ProcessType,ProcessCase) VALUES('{0}','{1}',0,1,5,2)", cuscode.ITEMCODE, ConvertToDate(cuscode.DOCDATE)); insertMSSQL(sql, strcon); // db.ExecuteNonQuery(string.Format("update bcapinvoicesub set sending='1' where cuscode='{0}' and docno='{1}'", cuscode.CUSCODE, cuscode.DOCNO)); } else { MessageBox.Show("ไม่พบหมายเลขซีเรียลนี้!โปรดตรวจสอบ"); } } } catch (WebException ex) { MessageBox.Show(ex.Message); } } }
/// <summary> /// Allows deleting of student/teacher accounts /// </summary> /// <param name="id">The ID of the account to delete</param> /// <param name="stuteach">A string containing either "students" or "teachers"</param> /// <returns>1 if the delete was successful, 0 otherwise</returns> public static int deleteAccount(int id, string stuteach) { // Validates input if (stuteach.ToLower() == "students" || stuteach.ToLower() == "teachers") { try { SQLiteDatabase db = new SQLiteDatabase(); db.ExecuteNonQuery(string.Format( "DELETE FROM {0} WHERE id={1};", stuteach, id)); } // Returns 0 if error, else returns 1 catch { return 0; } return 1; } // This is if the input is invalid return 0; }
private static void TestCsharpSqlite() { SQLiteDatabase db; SQLiteVdbe stmt; SQLiteVdbe c1, c2; bool found; int i; string databaseName = "Benchmark_cs-SQLite.sqlite"; if ( File.Exists( databaseName ) ) File.Delete( databaseName ); db = new SQLiteDatabase( databaseName ); for ( i = 0; i < PRAGMA_Commands.Length; i++ ) { db.ExecuteNonQuery( PRAGMA_Commands[i] ); } db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); for ( i = 0; i < CREATE_Commands.Length; i++ ) { db.ExecuteNonQuery( CREATE_Commands[i] ); } stmt = new SQLiteVdbe( db, INSERT_Command ); long start = DateTime.Now.Ticks; long key = 1999; for ( i = 0; i < nRecords; i++ ) { key = ( 3141592621L * key + 2718281829L ) % 1000000007L; stmt.Reset(); stmt.BindLong( 1, key ); stmt.BindText( 2, key.ToString() ); stmt.ExecuteStep(); } stmt.Close(); db.ExecuteNonQuery( "END" ); timer[1, 0] = DateTime.Now.Ticks - start; db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); start = DateTime.Now.Ticks; c1 = new SQLiteVdbe( db, SELECT_Bind_i ); c2 = new SQLiteVdbe( db, SELECT_Bind_s ); key = 1999; for ( i = 0; i < nRecords; i++ ) { key = ( 3141592621L * key + 2718281829L ) % 1000000007L; c1.Reset(); c1.BindLong( 1, key ); c1.ExecuteStep(); c2.Reset(); c2.BindText( 1, key.ToString() ); c2.ExecuteStep(); long id = (long)c1.Result_Long( 0 ); Debug.Assert( id == (long)c2.Result_Long( 0 ) ); } c1.Close(); c2.Close(); db.ExecuteNonQuery( "END" ); timer[1, 1] = DateTime.Now.Ticks - start; db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); start = DateTime.Now.Ticks; key = Int64.MinValue; i = 0; c1 = new SQLiteVdbe( db, SELECT_Command_i ); while ( c1.ExecuteStep() != Sqlite3.SQLITE_DONE ) { long intKey = (long)c1.Result_Long( 0 ); Debug.Assert( intKey >= key ); key = intKey; i += 1; } c1.Close(); Debug.Assert( i == nRecords ); String strKey = ""; i = 0; c2 = new SQLiteVdbe( db, SELECT_Command_s ); while ( c2.ExecuteStep() != Sqlite3.SQLITE_DONE ) { string recStrKey = (string)c2.Result_Text( 1 ); Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 ); strKey = recStrKey; i += 1; } c2.Close(); Debug.Assert( i == nRecords ); timer[1, 2] = DateTime.Now.Ticks - start; db.ExecuteNonQuery( "END" ); db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); start = DateTime.Now.Ticks; key = 1999; stmt = new SQLiteVdbe( db, DELETE_Bind ); for ( i = 0; i < nRecords; i++ ) { key = ( 3141592621L * key + 2718281829L ) % 1000000007L; stmt.Reset(); stmt.BindLong( 1, key ); stmt.ExecuteStep(); } stmt.Close(); db.ExecuteNonQuery( "END" ); timer[1, 3] = DateTime.Now.Ticks - start; db.CloseDatabase(); #if NET_35 Sqlite3.Shutdown(); #else Sqlite3.sqlite3_shutdown(); #endif }
public void Flush() { byte[] buffer = new byte[4096]; int bytesRead = 0; int blockCount = 0; string fileId = ""; string directoryID = db.ExecuteScalar("SELECT id FROM directories WHERE fullpath = @path", new Dictionary <string, object>() { { "@path", vsrc.ParentPath.Path } }); DataTable fileInfo = db.GetDataTable("SELECT * from entities where isdir = @isdir and fname = @name and dir = @dir", new Dictionary <string, object>() { { "@name", vsrc.EntityName }, { "@isdir", 0 }, { "@dir", directoryID } }); // If file does not exist - create file record if (fileInfo.Rows.Count == 0) { string insertEntityQuery = "INSERT INTO entities (fname, dir, size, cdate, mdate, isdir, accessdate, filehash) VALUES (@fname, @dir, @size, @cdate, @mdate, @isdir, @access, @filehash)"; double ctime = DateTime.Now.UnixTimeStamp(); string fileHash = ""; using (FileStream fs = new FileStream(this.Path, FileMode.Open, FileAccess.Read)) { fileHash = fs.GetSHA512(); } db.ExecuteNonQuery(insertEntityQuery, new Dictionary <string, object>() { { "@fname", vsrc.EntityName }, { "@dir", directoryID }, { "@size", new FileInfo(Path).Length }, { "@cdate", ctime }, { "@mdate", ctime }, { "@access", ctime }, { "@isdir", 0 }, { "@filehash", fileHash } }); fileInfo = db.GetDataTable("SELECT * from entities where isdir = @isdir and fname = @name and dir = @dir", new Dictionary <string, object>() { { "@name", vsrc.EntityName }, { "@isdir", 0 }, { "@dir", directoryID } }); } fileId = fileInfo.Rows[0]["id"].ToString(); using (FileStream f = System.IO.File.Open(this.Path, FileMode.Open, FileAccess.Read)) { Int64 lenthOfFile = f.Length; Int64 totalRead = 0; using (ProgressBar pb = new ProgressBar(disableProgress)) { while ((bytesRead = f.Read(buffer, 0, buffer.Length)) > 0) { pb.Report((((double)totalRead) / lenthOfFile)); totalRead = totalRead + bytesRead; string hash1 = buffer.JenkinsOneAtATime(); string id = ""; string hash2 = ""; string hash2Check = ""; hash2 = buffer.GetSHA512(0, bytesRead); hash2Check = db.ExecuteScalar("SELECT id FROM blocks WHERE hash2 = @hash2 and hash1 = @hash1", new Dictionary <string, object>() { { "@hash2", hash2 }, { "@hash1", hash1 } }); id = hash2Check; if (id == "") { // need to create block Guid g = Guid.NewGuid(); string name = g.ToString(); string encName = AESWrapper.EncryptToString(name, key); encName = encName.Replace("/", "_"); encName = "/" + encName; encName = encName.Replace("//", "__"); /*byte[] compressed; * using (System.IO.MemoryStream instream = new MemoryStream(buffer)) * { * using (System.IO.MemoryStream outstream = new MemoryStream()) * { * using (GZipStream s = new GZipStream(outstream, CompressionMode.Compress)) * { * instream.CopyTo(s); * } * * compressed = outstream.ToArray(); * } * }*/ foreach (IFileSystem fs in fsdst) { /*try * { * using (Stream s = fs.CreateFile(FileSystemPath.Parse("/test"))) * { * * } * } * catch (Exception e) * { * Console.WriteLine("[Error]: file system - " + fs.ToString() + " is unreachable."); * continue; * }*/ //fs. using (var fstream = fs.OpenOrCreate(FileSystemPath.Parse($"/{DATAFILE}"), FileAccess.ReadWrite)) { //byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead); DeDupeStorage storage = new DeDupeStorage(fstream, db); byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead); storage.AddFile(encName, cipher); } /*using (var fstream = fs.OpenOrCreate(FileSystemPath.Parse($"/{DATAFILE}"), * FileAccess.ReadWrite)) * { * //byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead); * DeDupeStorage storage = new DeDupeStorage(fstream, db); * using (var zippedStream = new MemoryStream()) * { * using (var archive = new ZipArchive(zippedStream, ZipArchiveMode.Update)) * { * var ent = archive.CreateEntry("data.bin", CompressionLevel.Optimal); * using (var zipent = ent.Open()) * { * zipent.Write(buffer, 0, bytesRead); * } * } * * byte[] zipData = zippedStream.ToArray(); * byte[] cipher = AESWrapper.EncryptToByte(zipData, key, 0, zipData.Length); * storage.AddFile(encName, cipher); * * } * }*/ /*using (var fstream = fs.OpenOrCreate(FileSystemPath.Parse($"/{DATAFILE}"), FileAccess.ReadWrite)) * { * using (var zip = new ZipArchive(fstream, ZipArchiveMode.Update)) * { * var ent = zip.CreateEntry(encName, CompressionLevel.Optimal); * using (var entfs = ent.Open()) * { * byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead); * entfs.Write(cipher); * } * } * }*/ /*using (Stream s = fs.CreateFile(FileSystemPath.Parse($"{encName}"))) * { * byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead); * s.Write(cipher); * }*/ string blockInsertSQL = "INSERT INTO blocks (hash1, size, name, location, hash2) VALUES (@hash1, @size, @name, @location, @hash2)"; db.ExecuteNonQuery(blockInsertSQL, new Dictionary <string, object>() { { "@hash1", hash1 }, { "@size", bytesRead }, { "@name", encName }, { "@location", fs.ToString() }, { "@hash2", hash2 } }); hash2Check = db.ExecuteScalar( "SELECT id FROM blocks WHERE hash2 = @hash2 and hash1 = @hash1 and location = @location", new Dictionary <string, object>() { { "@hash2", hash2 }, { "@hash1", hash1 }, { "@location", fs.ToString() } }); id = hash2Check; string fileBlockInsertSQL = "INSERT INTO fileblocks (file_id, block_id, block_order) VALUES (@fileId, @blockId, @blockOrder)"; db.ExecuteNonQuery(fileBlockInsertSQL, new Dictionary <string, object>() { { "@fileId", fileId }, { "@blockId", id }, { "@blockOrder", blockCount } }); } } else { string fileBlockInsertSQL = "INSERT INTO fileblocks (file_id, block_id, block_order) VALUES (@fileId, @blockId, @blockOrder)"; db.ExecuteNonQuery(fileBlockInsertSQL, new Dictionary <string, object>() { { "@fileId", fileId }, { "@blockId", id }, { "@blockOrder", blockCount } }); } blockCount++; } } } }
public void Save(string userId) { SQLiteDatabase db = new SQLiteDatabase(); // Query forces identity string sql = @"update cycli_rider_turbos set Type=@t, " + "Power_Model_C1=@c1, Power_Model_C2=@c2, Power_Model_C3=@c3 " + "where UserId=@u"; int updated = db.ExecuteNonQuery(sql, "@t", this.Type, "@c1", this.Coefficients.Length == 0 ? 0 : this.Coefficients[0], "@c2", this.Coefficients.Length == 0 ? 0 : this.Coefficients[1], "@c3", this.Coefficients.Length == 0 ? 0 : this.Coefficients[2], "@u", userId); if (updated == 0) { // It's a new entry sql = @"insert into cycli_rider_turbos (UserId, Type, Power_Model_C1, Power_Model_C2, Power_Model_C3) " + "values (@u, @t, @c1, @c2, @c3)"; db.ExecuteNonQuery(sql, "@u", userId, "@t", this.Type, "@c1", this.Coefficients.Length == 0 ? 0 : this.Coefficients[0], "@c2", this.Coefficients.Length == 0 ? 0 : this.Coefficients[1], "@c3", this.Coefficients.Length == 0 ? 0 : this.Coefficients[2]); } db.Close(); }
protected void removeAddUpdateOffersToGrandExchangeDatabase() { while (true) { try { Thread.Sleep(100); //100 milliseconds. } catch (ThreadInterruptedException) { } lock (removeOffersLocker) { foreach (GEItem geItem in offersToRemove) { removingOffers.Add(geItem); } offersToRemove.Clear(); } lock (addOffersLocker) { foreach (GEItem geItem in offersToAdd) { addingOffers.Add(geItem); } offersToAdd.Clear(); } lock (abortOffersLocker) { foreach (GEItem geItem in offersToAbort) { abortingOffers.Add(geItem); } offersToAbort.Clear(); } try { //this check should go straight to Thread.Sleep. if (removingOffers.Count == 0 && addingOffers.Count == 0 && abortingOffers.Count == 0) { continue; } //Run this on seperate thread so it won't slow down the process. SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName); //opens new database connection. //Removes all the removingOffers from grandExchangeSelling table //This delayed offer deleter should always be ran before inserter (below) or else it's possible bug may occur. foreach (GEItem geItem in removingOffers) { if (geItem is BuyOffer) { db.ExecuteNonQuery("DELETE FROM grandExchangeBuying WHERE `playerHash` = " + geItem.getPlayerHash() + " AND `itemId` = " + geItem.getItem() + " AND `amount` = " + geItem.getTotalAmount() + " AND `price` = " + geItem.getPriceEach() + " AND `slot` = " + geItem.getSlot()); } else if (geItem is SellOffer) { db.ExecuteNonQuery("DELETE FROM grandExchangeSelling WHERE `playerHash` = " + geItem.getPlayerHash() + " AND `itemId` = " + geItem.getItem() + " AND `amount` = " + geItem.getTotalAmount() + " AND `price` = " + geItem.getPriceEach() + " AND `slot` = " + geItem.getSlot()); } } //Add all new addingOffers to grandExchangeBuying or grandExchangeSelling table. //This delayed offer inserter would cause a possible dupe, if both tables didn't have UNIQUE( slot, playerHash ), fixed now. foreach (GEItem geItem in addingOffers) { if (geItem is BuyOffer) { db.ExecuteNonQuery("INSERT INTO grandExchangeBuying(itemId, amount, price, bought, slot, playerHash) VALUES (" + geItem.getItem() + ", " + geItem.getTotalAmount() + ", " + geItem.getPriceEach() + ", " + geItem.getAmountTraded() + ", " + geItem.getSlot() + ", " + geItem.getPlayerHash() + ")"); } else if (geItem is SellOffer) { db.ExecuteNonQuery("INSERT INTO grandExchangeSelling(itemId, amount, price, sold, slot, playerHash) VALUES (" + geItem.getItem() + ", " + geItem.getTotalAmount() + ", " + geItem.getPriceEach() + ", " + geItem.getAmountTraded() + ", " + geItem.getSlot() + ", " + geItem.getPlayerHash() + ")"); } } //Aborts any Buying Offer or Selling Offer from grandExchangeBuying or grandExchangeSelling table. foreach (GEItem geItem in abortingOffers) { if (geItem is BuyOffer) { db.ExecuteNonQuery("UPDATE grandExchangeBuying SET aborted = 1 WHERE slot = " + geItem.getSlot() + " AND playerHash = " + geItem.getPlayerHash()); } else if (geItem is SellOffer) { db.ExecuteNonQuery("UPDATE grandExchangeSelling SET aborted = 1 WHERE slot = " + geItem.getSlot() + " AND playerHash = " + geItem.getPlayerHash()); } } db.CloseDatabase(); //closes the connection. removingOffers.Clear(); addingOffers.Clear(); abortingOffers.Clear(); } catch (Exception e) { misc.WriteError(e.Message); } } }
public static void Delete(string userId, string riderId) { // Has this rider been employed in a race string sqlPlayed = @"select count(*) from cycli_race_riders where userid=@u and (Status='Finished' or Status='Abandoned')"; string sqlRiderDelete = @"delete from cycli_virtual_riders where ownerid = @o and userid = @u"; string sqlRiderRaceDelete = @"delete from cycli_race_riders where userid = @u"; string sqlRiderInactive = @"update cycli_virtual_riders set status='Inactive' where ownerid = @o and userid = @u"; bool riderPlayed = false; SQLiteDatabase db = new SQLiteDatabase(); try { riderPlayed = (int.Parse(db.ExecuteScalar(sqlPlayed,"@u",riderId)) > 0); } finally { db.Close(); } // Now reopen for transactions db = new SQLiteDatabase(true); try { if (riderPlayed) { db.ExecuteNonQuery(sqlRiderInactive, "@o", userId, "@u", riderId); } else { // Get rid of rider from any pending races db.ExecuteNonQuery(sqlRiderRaceDelete, "@u", riderId); db.ExecuteNonQuery(sqlRiderDelete, "@o", userId, "@u", riderId); } // Commit closes db db.CommitTransaction(); } catch (Exception ex) { db.RollbackTransaction(); } }
private SQLiteDatabase OpenDB(string fileName) { if(File.Exists(fileName)) File.Delete(fileName); var db = new SQLiteDatabase(fileName); for(int i = 0; i < PRAGMA_Commands.Length; i++) { db.ExecuteNonQuery(PRAGMA_Commands[i]); } return db; }
// Returns invites, joins and finishes - in that order public static void Cancel(string userId, string raceId) { // Load the race as we need to check status - can only delete planned Race r = Load(raceId); // Protection - we can only cancel if we're the race director. Good try Mr, Mrs or Ms Hacker if (r != null && r.RaceDirectorId == userId && r.Status == @"Planned") { SQLiteDatabase db = new SQLiteDatabase(true); // Can only delete planned string sql = @"delete from cycli_race_riders where RaceId = @r"; db.ExecuteNonQuery(sql, "@r", r.RaceId); sql = @"delete from cycli_races where RaceId = @r"; db.ExecuteNonQuery(sql, "@r", r.RaceId); try { db.CommitTransaction(); } // No need to close Db as this is done by CommitTranaction catch (Exception ex) { db.RollbackTransaction(); Console.WriteLine(ex); } } }
public override void Write(byte[] buffer, int offset, int count) { base.Write(buffer, offset, count); // Someday I will separate it out and make a PR to SharpFileSystem //string directoryID = // db.ExecuteScalar("SELECT fileid FROM gdrive WHERE filename = 'distrodedup' AND directory = 1"); string directoryID = db.ExecuteScalar("SELECT fileid FROM gdrive WHERE filename = @fileName AND directory = @directory", new Dictionary <string, object>() { { "@fileName", "distrodedup" }, { "@directory", 1 } }); if (directoryID == "") { File fileData = new File(); fileData.Name = "DistroDeDup"; fileData.MimeType = "application/vnd.google-apps.folder"; var req = svc.Files.Create(fileData); req.Fields = "id"; var folder = req.Execute(); //db.ExecuteNonQuery( // $"INSERT INTO gdrive (filename, fileid, directory) VALUES ('distrodedup', '{folder.Id}', 1)"); db.ExecuteNonQuery( "INSERT INTO gdrive (filename, fileid, directory) VALUES (@fileName, @fileID, @directory)", new Dictionary <string, object>() { { "@fileName", "distrodedup" }, { "@fileID", folder.Id }, { "@directory", 1 } }); directoryID = folder.Id; } //string fileId = // db.ExecuteScalar($"SELECT fileid FROM gdrive WHERE filename = '{fileName}' AND directory = 0"); string fileId = db.ExecuteScalar("SELECT fileid FROM gdrive WHERE filename = @fileName AND directory = @directory", new Dictionary <string, object>() { { "@fileName", fileName }, { "@directory", 0 } }); if (fileId != "") { FilesResource.ListRequest reqExists = svc.Files.List(); reqExists.Q = String.Format("name = '{0}' and trashed=false and parents in '{1}'", fileName, directoryID); reqExists.Fields = "nextPageToken, files(id, name,parents,mimeType, trashed)"; var result = reqExists.Execute(); bool foundFile = false; foreach (var file in result.Files) { if (file.Id == fileId) { // I guess we need to create another file object? /*File uploadFile = new File(); * uploadFile.Name = fileName; * uploadFile.MimeType = mimeType; * var res = svc.Files.Update(uploadFile, fileId, this, mimeType); * var req = res.Upload();*/ GDriveFile f = new GDriveFile(this, fileName, mimeType, directoryID, db, GDriveFile.Operation.NONE); f.Update(svc, fileId); foundFile = true; break; } } if (!foundFile) { GDriveFile f = new GDriveFile(this, fileName, mimeType, directoryID, db, GDriveFile.Operation.UPDATE); f.Upload(svc); /*File body = new File(); * body.Name = fileName; * body.MimeType = mimeType; * body.Parents = new List<string>(); * body.Parents.Add(directoryID); * FilesResource.CreateMediaUpload req = svc.Files.Create(body, this, mimeType); * req.Fields = "id, parents"; * var res = req.Upload(); * string newFileId = req.ResponseBody.Id; * * //db.ExecuteNonQuery( * // $"UPDATE gdrive set fileid = '{newFileId}' WHERE filename = '{fileName}' and directory = 0"); * * db.ExecuteNonQuery( * "UPDATE gdrive set fileid = @newFileId WHERE filename = @fileName and directory = 0", * new Dictionary<string, object>() * { * {"@newFileId", newFileId}, * {"@fileName", fileName} * });*/ } } else { GDriveFile f = new GDriveFile(this, fileName, mimeType, directoryID, db, GDriveFile.Operation.CREATE); f.Upload(svc); /*File body = new File(); * body.Name = fileName; * body.MimeType = mimeType; * body.Parents = new List<string>(); * body.Parents.Add(directoryID); * FilesResource.CreateMediaUpload req = svc.Files.Create(body, this, mimeType); * req.Fields = "id, parents"; * req.ProgressChanged += ReqOnProgressChanged; * //req.UploadAsync() * var res = req.Upload(); * * string newFileId = req.ResponseBody.Id; * * //db.ExecuteNonQuery( * // $"INSERT INTO gdrive (filename, fileid, directory) VALUES ('{fileName}', '{newFileId}', 0)"); * db.ExecuteNonQuery( * "INSERT INTO gdrive (filename, fileid, directory) VALUES (@fileName, @newFileId, 0)", * new Dictionary<string, object>() * { * {"@fileName", fileName}, * {"@newFileID", newFileId} * });*/ } }
public static void JoinVirtualRider(string userId, string riderId, string raceId) { // What is the race status // Validation occurs first on the client - this is the backstop UserRider owner = UserRider.Load(userId); if (owner != null) { // Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded SQLiteDatabase db = new SQLiteDatabase(); // This query checks that the user has not already been invited string sql = "insert into cycli_race_riders (UserId, RaceId, Status, RiderType) " + "select @f, @r, 'Joined', 'VIRTUAL' where exists (select 1 from cycli_races where " + "RaceId=@r1 and Status='Planned' and RaceDirectorId=@u) " + "and not exists (select 1 from cycli_race_riders where RaceId=@r1 and UserId=@f1) " + "and (select count(*) from cycli_race_riders where raceId=@r2) < @c "; db.ExecuteNonQuery(sql, "@f", riderId, "@r", raceId, "@r1", raceId, "@u", userId, "@f1", riderId, "@r2", raceId, "@c", owner.MaximumRiders); db.Close(); Race race = Race.Load(raceId); if (race != null) { CycliManager.Instance.SendRiderJoin(race, riderId); } } }
public static void ProcessLibretroDb(string libretroDir, string outputDir, bool generateSql, bool generateYaml) { FileSystem.CreateDirectory(outputDir); Func <DatGame, string> libretroDbDefaultGroupSelectors = (g) => g.RomCrc; var libretroDbGroupSelectors = new Dictionary <string, Func <DatGame, string> >(StringComparer.OrdinalIgnoreCase) { { "Sony - PlayStation", (g) => g.Serial }, { "Sony - PlayStation 3", (g) => g.Serial }, { "Nintendo - GameCube", (g) => g.Serial }, { "Nintendo - Wii", (g) => g.Serial }, { "Sega - Dreamcast", (g) => g.Serial }, { "Sega - Mega-CD - Sega CD", (g) => g.Serial }, { "Sega - Saturn", (g) => g.Serial }, { "Sony - PlayStation 2", (g) => g.Serial }, { "Sony - PlayStation Portable", (g) => g.Serial }, { "Sony - PlayStation Vita", (g) => g.Serial }, { "Nintendo - Wii U", (g) => g.Serial } }; var datDirs = new List <string> { Path.Combine(libretroDir, @"dat"), Path.Combine(libretroDir, @"metadat\no-intro"), Path.Combine(libretroDir, @"metadat\redump"), Path.Combine(libretroDir, @"metadat\libretro-dats"), Path.Combine(libretroDir, @"metadat\fbneo-split"), Path.Combine(libretroDir, @"metadat\mame"), Path.Combine(libretroDir, @"metadat\mame-member"), Path.Combine(libretroDir, @"metadat\mame-split"), Path.Combine(libretroDir, @"metadat\mame-nonmerged"), Path.Combine(libretroDir, @"metadat\homebrew"), Path.Combine(libretroDir, @"metadat\hacks"), Path.Combine(libretroDir, @"metadat\headered"), Path.Combine(libretroDir, @"metadat\serial"), Path.Combine(libretroDir, @"metadat\releaseyear"), Path.Combine(libretroDir, @"metadat\origin") }; var ignoreList = new List <string> { "MAME 2000 XML.dat", "MAME BIOS.dat", "FBNeo_romcenter.dat", "System.dat", "MAME 2000 BIOS.dat", "Cannonball.dat", "Cave Story.dat", "Dinothawr.dat", "DOOM.dat", "Flashback.dat", "ChaiLove.dat", "LeapFrog - LeapPad.dat", "Lutro.dat", "Microsoft - Xbox One.dat", "Mobile - J2ME.dat", "Mobile - Palm OS.dat", "Mobile - Symbian.dat", "Mobile - Zeebo.dat", "MrBoom.dat", "Quake.dat", "Quake II.dat", "Quake III.dat", "Rick Dangerous.dat", "RPG Maker.dat", "Sony - PlayStation 4.dat", "Sony - PlayStation Portable (UMD Music).dat", "Sony - PlayStation Portable (UMD Video).dat", "Tiger - Game.com.dat", "Tiger - Gizmondo.dat", "Tomb Raider.dat", "Wolfenstein 3D.dat", "Microsoft - XBOX 360 (Title Updates).dat", "HBMAME.dat", "MAME 2000.dat", "MAME 2003.dat", "MAME 2003-Plus.dat", "MAME 2010.dat", "MAME 2015.dat", "MAME 2016.dat", "MAME.dat", "ScummVM.dat", "Atomiswave.dat", "Sony - PlayStation Minis.dat", "Thomson - MOTO.dat" }; var datFiles = new List <FileInfo>(); datDirs.ForEach(d => datFiles.AddRange( Directory.GetFiles(d, "*.dat", SearchOption.AllDirectories).Where(f => !ignoreList.Contains(Path.GetFileName(f), StringComparer.OrdinalIgnoreCase)). Select(f => new FileInfo(f)))); var databases = datFiles.GroupBy(a => a.Name, StringComparer.OrdinalIgnoreCase); Parallel.ForEach( databases, (db, _, __) => { var dbName = Path.GetFileNameWithoutExtension(db.Key); var selector = libretroDbDefaultGroupSelectors; if (libretroDbGroupSelectors.TryGetValue(dbName, out var sel)) { selector = sel; } var cons = DatParser.ConsolidateDatFiles(db.Select(a => a.FullName), selector); if (dbName == "Sony - PlayStation 3") { string ps3ResgionCharToRegion(char regionKey) { switch (regionKey) { case 'A': return("Asia"); case 'C': return("China"); case 'E': return("Europe"); case 'H': return("Hong Kong"); case 'J': return("Japan"); case 'K': return("Korea"); case 'P': return("Japan"); case 'U': return("USA"); default: return(null); } } foreach (var game in cons) { if (game.Region.IsNullOrEmpty() && !game.Serial.IsNullOrEmpty()) { game.Region = ps3ResgionCharToRegion(game.Serial[2]); } } } if (generateYaml) { var yamlPath = Path.Combine(outputDir, $"{dbName}.yaml"); FileSystem.DeleteFile(yamlPath); File.WriteAllText( yamlPath, Serialization.ToYaml(cons), Encoding.UTF8); } if (generateSql) { var dbPath = Path.Combine(outputDir, $"{dbName}.db"); FileSystem.DeleteFile(dbPath); using (var sqlDb = new SQLiteDatabase(dbPath)) { sqlDb.ExecuteNonQuery("PRAGMA journal_mode = OFF;"); sqlDb.Save(cons); } } }); }
public static Dictionary<string, int> SetHandicaps(string raceId) { Dictionary<string, int> handicaps = new Dictionary<string, int>(); Race race = Load(raceId); foreach (Participant p in race.Participants) { handicaps.Add(p.UserId, 100); } if (race != null) { SQLiteDatabase db = new SQLiteDatabase(); long yearAgo = DbTime.ToDbSecs(DateTime.UtcNow); string sql = "select rr1.UserId, " + "r.StartDateTime, " + "(1000 * cast(rr1.Energy as real)/ cast(rr1.Time as real)) as Power " + "From cycli_race_riders rr1, cycli_race_riders rr2, cycli_races r " + "where r.StartDateTime > @t " + "and r.RaceId = rr2.RaceId " + "and rr1.Time > 0 " + "and rr1.Energy > 0 " + "and (rr1.Status='Finished' or rr1.Status='Abandoned') " + "and rr1.UserId = rr2.UserId " + "and r.RaceId = @r " + "order by rr1.Time"; DataTable dt = db.GetDataTable(sql, "@t", yearAgo, "@r", raceId); db.Close(); // Weight the average power by (a) time in the past // (b) closeness to the selected distance Dictionary<string, double> userPowers = dt.AsEnumerable() .GroupBy(p => p.Field<string>("UserId")) .ToDictionary(p => p.Key, p => p.Sum(q => (double)(yearAgo - q.Field<long>("StartDateTime")) * q.Field<double>("Power"))/ p.Sum(q => yearAgo - q.Field<long>("StartDateTime"))); // Total AveragePower across all users double meanUserPower = userPowers.Values.Average(p => p); if (meanUserPower > 0) { foreach (KeyValuePair<string, double> k in userPowers) { int handicap = (int)Math.Round(100 * meanUserPower / k.Value); handicaps[k.Key] = handicap; } } string updateSql = "update cycli_race_riders set handicap=@h where raceid=@r and userid=@u"; db = new SQLiteDatabase(true); try { foreach (KeyValuePair<string, int> h in handicaps) { db.ExecuteNonQuery(updateSql, "@h", h.Value, "@r", raceId, "@u", h.Key); } try { db.CommitTransaction(); } catch (Exception ex) { db.RollbackTransaction(); } } finally { } } return handicaps; }
private static void TestcsSQLite() { SQLiteDatabase db; SQLiteVdbe stmt; SQLiteVdbe c1, c2; bool found; int i; string databaseName = "Benchmark_cs-SQLite.sqlite"; Console.WriteLine("\n\r" + databaseName); if (File.Exists(databaseName)) { File.Delete(databaseName); } db = new SQLiteDatabase(databaseName); for (i = 0; i < PRAGMA_Commands.Length; i++) { db.ExecuteNonQuery(PRAGMA_Commands[i]); } db.ExecuteNonQuery("BEGIN EXCLUSIVE"); for (i = 0; i < CREATE_Commands.Length; i++) { db.ExecuteNonQuery(CREATE_Commands[i]); } stmt = new SQLiteVdbe(db, INSERT_Command); long start = DateTime.Now.Ticks; long key = 1999; for (i = 0; i < nRecords; i++) { key = (3141592621L * key + 2718281829L) % 1000000007L; stmt.Reset(); stmt.BindLong(1, key); stmt.BindText(2, key.ToString()); stmt.ExecuteStep(); } stmt.Close(); db.ExecuteNonQuery("END"); Console.WriteLine("inserting " + nRecords + " records: " + ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds"); db.ExecuteNonQuery("BEGIN EXCLUSIVE"); start = DateTime.Now.Ticks; c1 = new SQLiteVdbe(db, SELECT_Bind_i); c2 = new SQLiteVdbe(db, SELECT_Bind_s); key = 1999; for (i = 0; i < nRecords; i++) { key = (3141592621L * key + 2718281829L) % 1000000007L; c1.Reset(); c1.BindLong(1, key); c1.ExecuteStep(); c2.Reset(); c2.BindText(1, key.ToString()); c2.ExecuteStep(); long id = (long)c1.Result_Long(0); Debug.Assert(id == ( long)c2.Result_Long(0)); } c1.Close(); c2.Close(); db.ExecuteNonQuery("END"); Console.WriteLine("performing " + nRecords * 2 + " index searches: " + ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds"); db.ExecuteNonQuery("BEGIN EXCLUSIVE"); start = DateTime.Now.Ticks; key = Int64.MinValue; i = 0; c1 = new SQLiteVdbe(db, SELECT_Command_i); while (c1.ExecuteStep() != csSQLite.SQLITE_DONE) { long intKey = (long)c1.Result_Long(0); Debug.Assert(intKey >= key); key = intKey; i += 1; } c1.Close(); Debug.Assert(i == nRecords); String strKey = ""; i = 0; c2 = new SQLiteVdbe(db, SELECT_Command_s); while (c2.ExecuteStep() != csSQLite.SQLITE_DONE) { string recStrKey = (string)c2.Result_Text(1); Debug.Assert(recStrKey.CompareTo(strKey) >= 0); strKey = recStrKey; i += 1; } c2.Close(); Debug.Assert(i == nRecords); Console.WriteLine("iterating through " + (nRecords * 2) + " records: " + ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds"); db.ExecuteNonQuery("END"); db.ExecuteNonQuery("BEGIN EXCLUSIVE"); start = DateTime.Now.Ticks; key = 1999; stmt = new SQLiteVdbe(db, DELETE_Bind); for (i = 0; i < nRecords; i++) { key = (3141592621L * key + 2718281829L) % 1000000007L; stmt.Reset(); stmt.BindLong(1, key); stmt.ExecuteStep(); } stmt.Close(); db.ExecuteNonQuery("END"); Console.WriteLine("deleting " + nRecords + " records: " + ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds"); db.CloseDatabase(); }
public void Finish(RaceSpot[] rss, Dictionary<string, Stack<RaceSpot>> history) { string sqlRiderUpdate = @"update cycli_race_riders set Position=@p, Distance=@d, Time=@t, Energy=@e, PedalStrokes=@k, Heartbeats=@b, TSS=@i " + "where RaceId=@r and UserId=@u"; string sqlRaceUpdate = @"update cycli_races set Status='Finished' " + "where RaceId=@r and Status='Started'"; SQLiteDatabase db = new SQLiteDatabase(true); try { db.ExecuteNonQuery(sqlRaceUpdate, "@r", this.RaceId); foreach (RaceSpot rs in rss) { db.ExecuteNonQuery(sqlRiderUpdate, "@p", rs.pos, "@d", rs.d, "@t", rs.t, "@e", (int)rs.e, "@k", (int)rs.k, "@b", (int)rs.b, "@i", (int)rs.i, "@r", this.RaceId, "@u", rs.id); } db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch (Exception ex1) { } } finally { // Don't need to close on a transaction } // Do points as a separate transaction string raceSpotSql = @"insert into cycli_race_spots (UserId, RaceId, Time, Distance, Speed, Cadence, HR, Power) " + "values (@u, @r, @t, @d, @s, @c, @h, @p)"; db = new SQLiteDatabase(true); try { foreach (string userId in history.Keys) { Stack<RaceSpot> userHistory = history[userId]; foreach (RaceSpot rs in userHistory) { db.ExecuteNonQuery(raceSpotSql, "@r", this.RaceId, "@u", userId, "@t", rs.t, "@d", rs.d, "@s", rs.s, "@c", rs.c, "@h", rs.h, "@p", rs.p); } } db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch (Exception ex1) { } } finally { } // Mark the race as Finished this.Status = @"Finished"; }
private static void TestCsharpSqlite() { SQLiteDatabase db; SQLiteVdbe stmt; SQLiteVdbe c1, c2; bool found; int i; string databaseName = "Benchmark_cs-SQLite.sqlite"; if (File.Exists(databaseName)) { File.Delete(databaseName); } db = new SQLiteDatabase(databaseName); for (i = 0; i < PRAGMA_Commands.Length; i++) { db.ExecuteNonQuery(PRAGMA_Commands[i]); } db.ExecuteNonQuery("BEGIN EXCLUSIVE"); for (i = 0; i < CREATE_Commands.Length; i++) { db.ExecuteNonQuery(CREATE_Commands[i]); } stmt = new SQLiteVdbe(db, INSERT_Command); long start = DateTime.Now.Ticks; long key = 1999; for (i = 0; i < nRecords; i++) { key = (3141592621L * key + 2718281829L) % 1000000007L; stmt.Reset(); stmt.BindLong(1, key); stmt.BindText(2, key.ToString()); stmt.ExecuteStep(); } stmt.Close(); db.ExecuteNonQuery("END"); timer[1, 0] = DateTime.Now.Ticks - start; db.ExecuteNonQuery("BEGIN EXCLUSIVE"); start = DateTime.Now.Ticks; c1 = new SQLiteVdbe(db, SELECT_Bind_i); c2 = new SQLiteVdbe(db, SELECT_Bind_s); key = 1999; for (i = 0; i < nRecords; i++) { key = (3141592621L * key + 2718281829L) % 1000000007L; c1.Reset(); c1.BindLong(1, key); c1.ExecuteStep(); c2.Reset(); c2.BindText(1, key.ToString()); c2.ExecuteStep(); long id = (long)c1.Result_Long(0); Debug.Assert(id == (long)c2.Result_Long(0)); } c1.Close(); c2.Close(); db.ExecuteNonQuery("END"); timer[1, 1] = DateTime.Now.Ticks - start; db.ExecuteNonQuery("BEGIN EXCLUSIVE"); start = DateTime.Now.Ticks; key = Int64.MinValue; i = 0; c1 = new SQLiteVdbe(db, SELECT_Command_i); while (c1.ExecuteStep() != Sqlite3.SQLITE_DONE) { long intKey = (long)c1.Result_Long(0); Debug.Assert(intKey >= key); key = intKey; i += 1; } c1.Close(); Debug.Assert(i == nRecords); String strKey = ""; i = 0; c2 = new SQLiteVdbe(db, SELECT_Command_s); while (c2.ExecuteStep() != Sqlite3.SQLITE_DONE) { string recStrKey = (string)c2.Result_Text(1); Debug.Assert(recStrKey.CompareTo(strKey) >= 0); strKey = recStrKey; i += 1; } c2.Close(); Debug.Assert(i == nRecords); timer[1, 2] = DateTime.Now.Ticks - start; db.ExecuteNonQuery("END"); db.ExecuteNonQuery("BEGIN EXCLUSIVE"); start = DateTime.Now.Ticks; key = 1999; stmt = new SQLiteVdbe(db, DELETE_Bind); for (i = 0; i < nRecords; i++) { key = (3141592621L * key + 2718281829L) % 1000000007L; stmt.Reset(); stmt.BindLong(1, key); stmt.ExecuteStep(); } stmt.Close(); db.ExecuteNonQuery("END"); timer[1, 3] = DateTime.Now.Ticks - start; db.CloseDatabase(); #if NET_35 Sqlite3.Shutdown(); #else Sqlite3.sqlite3_shutdown(); #endif }
public void Save(string userId) { // Check the time - only allow a minimum of 2 minutes long timeNow = Utilities.DbTime.ToDbSecs(DateTime.UtcNow); if (this.StartDateTime < timeNow - (timeNow % 60) + 120) { this.StartDateTime = timeNow - (timeNow % 60) + 120; } SQLiteDatabase db = new SQLiteDatabase(true); // Query will only allow the race director to save string sqlRace = @"update cycli_races set Name=@n, Description=@de, TargetType=@tt, Configuration=@c, StartDateTime=@d, PNS=@p, " + "Drafting=@dr, HandicapType=@h, " + "ProfileId=@pi, ProfileType=@pt, Minimum=@mn, Maximum=@mx " + "where RaceId=@r and Status='Planned' and RaceDirectorId=@u"; db.ExecuteNonQuery(sqlRace, "@n", this.Name, "@de", this.Description, "@tt", this.TargetType, "@c", this.Configuration, "@d", this.StartDateTime, "@p", this.PowerNormalisedSpeed.ToString(), "@dr", this.Drafting.ToString(), "@h", this.HandicapType.ToString(), "@pi", string.IsNullOrEmpty(this.ProfileId) ? "" : this.ProfileId, "@pt", string.IsNullOrEmpty(this.ProfileType) ? "" : this.ProfileType, "@mn", this.ProfileMin, "@mx", this.ProfileMax, "@r", this.RaceId, "@u", userId); // Delete any old riders string sqlDelete = "delete from cycli_race_riders where raceId = @r"; db.ExecuteNonQuery(sqlDelete, "@r", this.RaceId); // Add in the new riders string sqlParticipant = "insert into cycli_race_riders (UserId, RaceId, Status, RiderType, Handicap) " + "values (@u, @r, @s, @t, @h)"; foreach (Participant p in this.Participants) { db.ExecuteNonQuery(sqlParticipant, "@u", p.UserId, "@r", this.RaceId, "@s", p.Status, "@t", (p.RiderType == Rider.RiderType.User ? "USER" : "VIRTUAL"), "@h", p.Handicap); } try { db.CommitTransaction(); } // No need to close Db as this is done by CommitTranaction catch (Exception ex) { db.RollbackTransaction(); Console.WriteLine(ex); } }
public override void SaveDetails(string userId) { SQLiteDatabase db = new SQLiteDatabase(); // Query forces identity string sql = @"update cycli_virtual_riders set Username=@n, PowerMinimum=@p4, Power1Hr=@p3, " + "Power5Min=@p2, Power1Min=@p1, Power5Sec=@p0, Aggression=@a " + "where OwnerId=@u and UserId=@r "; db.ExecuteNonQuery(sql, "@n", this.UserName, "@p4", this.PowerMin, "@p3", this.Power1Hr, "@p2", this.Power5Min, "@p1", this.Power1Min, "@p0", this.Power5Sec,"@a", this.Aggression, "@u", userId, "@r", this.UserId); db.Close(); }
public void Start() { // If real person has joined - finish the race straight away if (Participants.Where(p => p.Status == @"Scheduled" && p.RiderType == Rider.RiderType.User).Count() == 0) { this.Status = @"Finished"; } else { this.Status = @"Started"; } System.Diagnostics.Debug.WriteLine("BANG! " + this.Name + " started at " + DateTime.UtcNow.ToString()); SQLiteDatabase db = new SQLiteDatabase(true); try { string sqlUpdate = @"update cycli_races set Status='" + this.Status + "' " + "where RaceId=@r"; db.ExecuteNonQuery(sqlUpdate, "@r", this.RaceId); // Next query won't set any unscheduled to started sqlUpdate = @"update cycli_race_riders set Status='Started' " + "where RaceId=@r and Status='Scheduled'"; db.ExecuteNonQuery(sqlUpdate, "@r", this.RaceId); // Tell the ride manager db.CommitTransaction(); } catch (Exception ex) { Console.WriteLine(ex.Message); db.RollbackTransaction(); } foreach (Participant p in Participants) { if (p.Status == @"Scheduled") { p.Status = @"Started"; } } }
public static Profile New(string userId) { Profile p = new Profile(); long timeNowDb = Utilities.DbTime.ToDbSecs(DateTime.UtcNow); p.ProfileId = Guid.NewGuid().ToString(); p.UserId = userId; p.Name = "New Profile"; p.Description = ""; p.Favourite = false; // It's a new profile string sqlProfile = @"insert into cycli_profiles (ProfileId, UserId, Name, Description, Favourite, CreatedOn, ModifiedOn) " + "values (@p, @u, @n, @d, @f, @c, @m)"; string sqlSpot = @"insert into cycli_profile_spots (ProfileId, X, Y) values (@p, @x, @y)"; SQLiteDatabase db = new SQLiteDatabase(true); try { db.ExecuteNonQuery(sqlProfile, "@p", p.ProfileId, "@u", p.UserId, "@n", p.Name, "@d", p.Description, "@f", p.Favourite.ToString(), "@c", timeNowDb, "@m", timeNowDb); db.ExecuteNonQuery(sqlSpot, "@p", p.ProfileId, "@x", 0, "@y", 50); db.ExecuteNonQuery(sqlSpot, "@p", p.ProfileId, "@x", 100, "@y", 50); db.CommitTransaction(); } catch (Exception ex) { try { db.RollbackTransaction(); } catch(Exception ex1) { } } finally { } return p; }
private static void TestcsSQLite() { SQLiteDatabase db; SQLiteVdbe stmt; SQLiteVdbe c1, c2; bool found; int i; string databaseName = "Benchmark_cs-SQLite.sqlite"; Console.WriteLine( "\n\r" + databaseName ); if ( File.Exists( databaseName ) ) File.Delete( databaseName ); db = new SQLiteDatabase( databaseName ); for (i = 0; i < PRAGMA_Commands.Length; i++) {db.ExecuteNonQuery( PRAGMA_Commands[i]);} db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); for (i = 0; i < CREATE_Commands.Length; i++) {db.ExecuteNonQuery( CREATE_Commands[i]);} stmt = new SQLiteVdbe( db, INSERT_Command ); long start = DateTime.Now.Ticks; long key = 1999; for ( i = 0 ; i < nRecords ; i++ ) { key = ( 3141592621L * key + 2718281829L ) % 1000000007L; stmt.Reset(); stmt.BindLong( 1, key ); stmt.BindText( 2, key.ToString() ); stmt.ExecuteStep(); } stmt.Close(); db.ExecuteNonQuery( "END" ); Console.WriteLine( "inserting " + nRecords + " records: " + ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" ); db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); start = DateTime.Now.Ticks; c1 = new SQLiteVdbe( db, SELECT_Bind_i ); c2 = new SQLiteVdbe( db, SELECT_Bind_s); key = 1999; for ( i = 0 ; i < nRecords ; i++ ) { key = ( 3141592621L * key + 2718281829L ) % 1000000007L; c1.Reset(); c1.BindLong( 1, key ); c1.ExecuteStep(); c2.Reset(); c2.BindText( 1, key.ToString() ); c2.ExecuteStep(); long id = (long)c1.Result_Long( 0 ); Debug.Assert( id == ( long)c2.Result_Long(0) ); } c1.Close(); c2.Close(); db.ExecuteNonQuery( "END" ); Console.WriteLine( "performing " + nRecords * 2 + " index searches: " + ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" ); db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); start = DateTime.Now.Ticks; key = Int64.MinValue; i = 0; c1 = new SQLiteVdbe( db, SELECT_Command_i ); while ( c1.ExecuteStep() != csSQLite.SQLITE_DONE ) { long intKey = (long)c1.Result_Long( 0 ); Debug.Assert( intKey>= key ); key = intKey; i += 1; } c1.Close(); Debug.Assert( i == nRecords ); String strKey = ""; i = 0; c2 = new SQLiteVdbe( db, SELECT_Command_s ); while ( c2.ExecuteStep() != csSQLite.SQLITE_DONE ) { string recStrKey = (string)c2.Result_Text( 1 ); Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 ); strKey = recStrKey; i += 1; } c2.Close(); Debug.Assert( i == nRecords ); Console.WriteLine( "iterating through " + ( nRecords * 2 ) + " records: " + ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" ); db.ExecuteNonQuery( "END" ); db.ExecuteNonQuery( "BEGIN EXCLUSIVE" ); start = DateTime.Now.Ticks; key = 1999; stmt = new SQLiteVdbe( db, DELETE_Bind); for ( i = 0 ; i < nRecords ; i++ ) { key = ( 3141592621L * key + 2718281829L ) % 1000000007L; stmt.Reset(); stmt.BindLong( 1, key ); stmt.ExecuteStep(); } stmt.Close(); db.ExecuteNonQuery( "END" ); Console.WriteLine( "deleting " + nRecords + " records: " + ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" ); db.CloseDatabase(); }
private void FormMain_KeyPress(object sender, KeyPressEventArgs e) { // vars int timeStampId; string firstName; // check for user/scanner pressing enter if (e.KeyChar.Equals('\r') || e.KeyChar.Equals('\n')) { // remove leading and trailing spaces input = input.Trim(); // check for existence of card if (Helper.EmployeeExists(input, sql)) { dt = sql.GetDataTable("select * from employees where employeeID=" + input + ";"); timeStampId = int.Parse(dt.Rows[0].ItemArray[6].ToString()); firstName = dt.Rows[0].ItemArray[1].ToString(); // check for clock-in or -out if (timeStampId > 0) { try { // clock out Dictionary <String, String> data = new Dictionary <String, String>(); data.Add("clockOut", DateTime.Now.ToString(StringFormats.sqlTimeFormat)); sql.Update("timeStamps", data, String.Format("timeStamps.id = {0}", timeStampId)); sql.ExecuteNonQuery("update employees set currentClockInId = 0 where employeeId=" + input + ";"); } catch (Exception err) { MessageBox.Show(this, "There was an error while trying to clock you out.\n\n" + err.Message, "Clock Out Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } // calculate time logged dt = sql.GetDataTable("select strftime('%s', clockOut) - strftime('%s', clockIn) from timeStamps where id=" + timeStampId + ";"); TimeSpan diff = TimeSpan.FromSeconds(long.Parse(dt.Rows[0].ItemArray[0].ToString())); // show confirmation LabelStatus.Text = "Goodbye " + firstName + ".\nYou logged " + GenerateClockedTime(diff) + "."; TimerInputTimeout.Interval = 3000; TimerInputTimeout.Enabled = true; } else { // clock in try { Dictionary <String, String> data = new Dictionary <String, String>(); data.Add("employeeID", input); data.Add("clockIn", DateTime.Now.ToString(StringFormats.sqlTimeFormat)); sql.Insert("timeStamps", data); dt = sql.GetDataTable("select seq from sqlite_sequence where name='timeStamps';"); data.Clear(); data.Add("currentClockInId", dt.Rows[0].ItemArray[0].ToString()); sql.Update("employees", data, String.Format("employees.employeeId = {0}", input)); } catch (Exception err) { MessageBox.Show(this, "There was an error while trying to clock you in.\n\n" + err.Message, "Clock In Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } // show confirmation LabelStatus.Text = "Hello " + firstName + "!\nYou're now clocked in."; TimerInputTimeout.Interval = 2500; TimerInputTimeout.Enabled = true; } } else { // notify user of unrecognized card LabelStatus.Text = "Unrecognized card!"; TimerInputTimeout.Interval = 2500; TimerInputTimeout.Enabled = true; } // reset input storage LabelInput.Text = "input: "; input = ""; } else if (Char.IsDigit(e.KeyChar) || e.KeyChar == (char)Keys.Back) { // add the input StoreInput(e.KeyChar); } }
protected void processSales() { OrderedDictionary soldBoughtItems = new OrderedDictionary(); /* * First of all the old system was not possible to do with SQL * Would of required to do a SQL Query for each itemId sold and bought by all players * That would of ment over 10,000 items x 2 (buyers+sellers) so 20,000 queries to process. * * The new system I created to fix this. * Works like this it everytime this runs matches up 1 GE Buying/Selling auction. * Then loops it 100 times or until no more matches are possible. * * TODO: Match Buyer's of larger item quantity with Sellers with larger quanity instead of Sellers with * any item quantity. (this requires editing the query below to a better suited query). */ BuyOffer buyer = null; SellOffer seller = null; int itemId; int itemAmount; int price; int sold; int bought; int collectedItem; int collectedGold; int overpaid; byte slot; long playerHash; SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName); for (int processNumSales = 0; processNumSales < 100; processNumSales++) { soldBoughtItems.Clear(); //Can't do more then 1 sale at a time, LIMIT 100 wont work since it will pick duplicate sellers. DataTable dt = db.ExecuteQuery("SELECT S.itemId AS sell_itemId, S.amount AS sell_amount, S.price AS sell_price, S.sold AS sell_sold, S.collectedItem AS sell_collectedItem, S.collectedGold AS sell_collectedGold, S.overpaid AS sell_overpaid, S.slot AS sell_slot, S.playerHash AS sell_playerHash, B.itemId AS buy_itemId, B.amount AS buy_amount, B.price AS buy_price, B.bought AS buy_bought, B.collectedItem AS buy_collectedItem, B.collectedGold AS buy_collectedGold, B.overpaid AS buy_overpaid, B.slot AS buy_slot, B.playerHash AS buy_playerHash FROM grandExchangeBuying AS B, grandExchangeSelling AS S ON B.itemId = S.itemId AND B.aborted = 0 AND S.aborted = 0 AND B.price >= S.price AND S.sold < S.amount AND B.bought < B.amount ORDER BY B.price DESC LIMIT 1"); if (dt.Rows.Count == 0) { db.CloseDatabase(); return; } else { itemId = Convert.ToInt32(dt.Rows[0]["sell_itemId"]); itemAmount = Convert.ToInt32(dt.Rows[0]["sell_amount"]); price = Convert.ToInt32(dt.Rows[0]["sell_price"]); sold = Convert.ToInt32(dt.Rows[0]["sell_sold"]); collectedItem = Convert.ToInt32(dt.Rows[0]["sell_collectedItem"]); collectedGold = Convert.ToInt32(dt.Rows[0]["sell_collectedGold"]); overpaid = Convert.ToInt32(dt.Rows[0]["sell_overpaid"]); slot = Convert.ToByte(dt.Rows[0]["sell_slot"]); playerHash = (long)dt.Rows[0]["sell_playerHash"]; seller = new SellOffer(itemId, itemAmount, price, sold, collectedItem, collectedGold, overpaid, slot, playerHash); itemId = Convert.ToInt32(dt.Rows[0]["buy_itemId"]); itemAmount = Convert.ToInt32(dt.Rows[0]["buy_amount"]); price = Convert.ToInt32(dt.Rows[0]["buy_price"]); bought = Convert.ToInt32(dt.Rows[0]["buy_bought"]); collectedItem = Convert.ToInt32(dt.Rows[0]["buy_collectedItem"]); collectedGold = Convert.ToInt32(dt.Rows[0]["buy_collectedGold"]); overpaid = Convert.ToInt32(dt.Rows[0]["buy_overpaid"]); slot = Convert.ToByte(dt.Rows[0]["buy_slot"]); playerHash = (long)dt.Rows[0]["buy_playerHash"]; buyer = new BuyOffer(itemId, itemAmount, price, bought, collectedItem, collectedGold, overpaid, slot, playerHash); } if (seller == null || buyer == null) { continue; } int amountToBuy = buyer.getTotalAmount() - buyer.getAmountTraded(); int amountToSell = seller.getTotalAmount() - seller.getAmountTraded(); // This check will never happen. SQL Query will not allow it.. Just old code I left in. if (amountToBuy <= 0 || amountToSell <= 0) { continue; } int amount = (amountToBuy > amountToSell) ? amountToSell : amountToBuy; // Buys from Seller a random amount of a item. amount = misc.random(1, amount); // Buyer will pay minimum what the seller wants. int amountBuyerOverpaid = (buyer.getPriceEach() - seller.getPriceEach()) * amount; // buyer is paying more than the seller wants, therefore MAY recieve this amount as a refund. bool buyerKeepsRefund = misc.random(1) == 0; // if 0, the buyer gets a refund, if its 1...the seller gets more. buyer.setAmountTraded(buyer.getAmountTraded() + amount); seller.setAmountTraded(seller.getAmountTraded() + amount); /** * How much refunded gold Buyer gets in addition to his previous refunded gold. * or * How much of the Buyer's overpaid gold that they couldn't keep goes as extra profit to the Seller. */ if (buyerKeepsRefund && amountBuyerOverpaid > 0) { buyer.setAmountOverpaid(buyer.getAmountOverpaid() + amountBuyerOverpaid); } else if (!buyerKeepsRefund && amountBuyerOverpaid > 0) { seller.setAmountOverpaid(seller.getAmountOverpaid() + amountBuyerOverpaid); } // Shows amount of Item Buyer bought in Slot 1 minus how much he already took out. if (buyer.getAmountItemsLeftToCollect() > 0) { buyer.setSlot1(new Item(buyer.getItem(), buyer.getAmountItemsLeftToCollect())); } //Shows amount of Gold Buyer has in Slot 2 from previous refunded money plus how much he maybe will be refunded this sale. if ((buyer.getAmountCollectedGold() < buyer.getAmountOverpaid()) && (buyer.getAmountOverpaid() - buyer.getAmountCollectedGold()) > 0) { buyer.setSlot2(new Item(995, (buyer.getAmountOverpaid() - buyer.getAmountCollectedGold()))); } //Shows amount of Gold Seller in Slot 2 has minus how much he already took out. if (seller.getAmountGoldLeftToCollect() > 0) { seller.setSlot2(new Item(995, seller.getAmountGoldLeftToCollect())); } Player buyerP = Server.getPlayerForName(buyer.getPlayerName()); Player sellerP = Server.getPlayerForName(seller.getPlayerName()); //Update both Buyer and Seller's Slots in the database try { db.ExecuteNonQuery("UPDATE grandExchangeBuying SET bought = " + buyer.getAmountTraded() + ", overpaid = " + buyer.getAmountOverpaid() + " WHERE slot = " + buyer.getSlot() + " AND playerHash = " + buyer.getPlayerHash()); db.ExecuteNonQuery("UPDATE grandExchangeSelling SET sold = " + seller.getAmountTraded() + ", overpaid = " + seller.getAmountOverpaid() + " WHERE slot = " + seller.getSlot() + " AND playerHash = " + seller.getPlayerHash()); } catch (Exception e) { //Instantly skip this sale if any database errors occurred. //If one of those queries works and other fails then you will have a dupe -_-. //TODO: A fix would be to keep retrying both queries until both successful. misc.WriteError("[GrandExchange SQL Error]: " + e.Message); continue; } /* * TODO: Fix When player is offline and logs in after item is bought. He won't see messages below.. * Add something to Packets.cs (sendLogin() method) to process this. * Maybe a (boolean in playerSaves for GE update?) */ if (buyerP != null) { buyerP.getPackets().sendMessage("One or more of your Grand Exchange offers has been updated."); buyerP.getPackets().updateGEProgress(buyer); if (buyerP.getGESession() != null) { Item[] items = { buyer.getSlot1(), buyer.getSlot2() }; buyerP.getPackets().sendItems(-1, -1757, 523 + buyer.getSlot(), items); } } /* * TODO: Fix When player is offline and logs in after item is bought. He won't see messages below.. * Add something to Packets.cs (sendLogin() method) to process this. * Maybe a (boolean in playerSaves for GE update?) */ if (sellerP != null) { sellerP.getPackets().sendMessage("One or more of your Grand Exchange offers has been updated."); sellerP.getPackets().updateGEProgress(seller); if (sellerP.getGESession() != null) { Item[] items = { seller.getSlot1(), seller.getSlot2() }; sellerP.getPackets().sendItems(-1, -1757, 523 + seller.getSlot(), items); } } } }
private void simpleButton1_Click(object sender, EventArgs e) { Dictionary <string, string> dic = new Dictionary <string, string>(); if (vGridControl3.Rows["editorRow6"].Properties.Value == null) { dic.Add("iscost", "0"); } else { dic.Add("iscost", vGridControl3.Rows["editorRow6"].Properties.Value.ToString() == "เตือน"?"1":"0"); } if (vGridControl3.Rows["editorRow7"].Properties.Value == null) { dic.Add("ismin", "0"); } else { dic.Add("ismin", vGridControl3.Rows["editorRow7"].Properties.Value.ToString() == "เตือน" ? "1" : "0"); } if (vGridControl3.Rows["editorRow8"].Properties.Value == null) { dic.Add("islowcost", "0"); } else { dic.Add("islowcost", vGridControl3.Rows["editorRow8"].Properties.Value.ToString() == "เตือน" ? "1" : "0"); } if (vGridControl3.Rows["editorRow9"].Properties.Value == null) { dic.Add("ispricechange", "0"); } dic.Add("ispricechange", vGridControl3.Rows["editorRow9"].Properties.Value.ToString() == "เตือน" ? "1" : "0"); if (ditem.Rows.Count > 0) { db.ExecuteNonQuery("delete from itemconfig"); } db.Insert("itemconfig", dic); dic = new Dictionary <string, string>(); dic.Add("formtype", vGridControl2.Rows["editorRow1"].Properties.Value.ToString()); dic.Add("billlock", vGridControl2.Rows["editorRow2"].Properties.Value.ToString() == "ทำงาน" ? "1" : "0"); dic.Add("saleprice", vGridControl2.Rows["editorRow3"].Properties.Value.ToString()); dic.Add("costprice", vGridControl2.Rows["editorRow4"].Properties.Value.ToString()); dic.Add("gp", vGridControl2.Rows["editorRow5"].Properties.Value.ToString()); dic.Add("autopurch", vGridControl2.Rows["row5"].Properties.Value.ToString() == "ทำงาน" ? "1" : "0"); dic.Add("docno", vGridControl2.Rows["row7"].Properties.Value.ToString()); dic.Add("customer", vGridControl2.Rows["row8"].Properties.Value.ToString()); if (dsale.Rows.Count > 0) { db.ExecuteNonQuery("delete from saleconfig"); } db.Insert("saleconfig", dic); dic = new Dictionary <string, string>(); if (vGridControl1.Rows["row"].Properties.Value == null) { dic.Add("companyname", ""); } else { dic.Add("companyname", vGridControl1.Rows["row"].Properties.Value.ToString()); } if (vGridControl1.Rows["row1"].Properties.Value == null) { dic.Add("address", ""); } else { dic.Add("address", vGridControl1.Rows["row1"].Properties.Value.ToString()); } // = dconfig.Rows[0]["address"].ToString(); if (vGridControl1.Rows["row2"].Properties.Value == null) { dic.Add("telephone", ""); } else { dic.Add("telephone", vGridControl1.Rows["row2"].Properties.Value.ToString()); } if (vGridControl1.Rows["row3"].Properties.Value == null) { dic.Add("province", ""); } else { dic.Add("province", vGridControl1.Rows["row3"].Properties.Value.ToString()); } if (vGridControl1.Rows["row4"].Properties.Value == null) { dic.Add("taxid", ""); } else { dic.Add("taxid", vGridControl1.Rows["row4"].Properties.Value.ToString()); } if (vGridControl1.Rows["row6"].Properties.Value == null) { dic.Add("taxrate", ""); } else { dic.Add("taxrate", vGridControl1.Rows["row6"].Properties.Value.ToString()); } if (dconfig.Rows.Count > 0) { db.ExecuteNonQuery("delete from configuration"); } db.Insert("configuration", dic); this.Close(); this.DialogResult = DialogResult.OK; }
public int newUniqueID(MediaStreamingRequest request, bool getIDOnly) { int newId = IdsAndInputFilesContains(request.InputFile + request.UniekClientID); if (request.KeepSameIDForSameInputFile && newId != 0) // != 0 means found as existing id that can be resumed { var ms = GetStreamerByID(newId); if (ms != null && !getIDOnly) { //mediaStreamers.Remove(newId); Functions.WriteLineToLogFile("Streamer newId=" + newId + " about to stop (in background), mediaStreamers.ContainsKey(newId) : " + mediaStreamers.ContainsKey(newId)); StopStreamer(newId, 2); Functions.WriteLineToLogFile("Streamer newId=" + newId + " stopped (in background), mediaStreamers.ContainsKey(newId) : " + mediaStreamers.ContainsKey(newId)); } if (!getIDOnly) { DeleteStreamingFiles(newId); //begin anew, s.t. if new quality, this will be used. } //bump up the id in the database db.Delete("IDANDINPUTFILE", String.Format("STREAMID = {0}", "" + newId)); var item = new Dictionary <string, string>(); item.Add("STREAMID", "" + newId); string i = request.InputFile.Replace("'", "''"); string u = request.UniekClientID.Replace("'", "''"); item.Add("INPUTFILE", i + u); db.Insert("IDANDINPUTFILE", item); return(newId); } //clean up if database is large const int maxFileToRememberResume = 1000; int count = 0; Int32.TryParse(db.ExecuteScalar("select COUNT(*) from IDANDINPUTFILE where STREAMID IS NOT NULL;"), out count); if (count > maxFileToRememberResume) { try { DataTable tabel; String query = "select STREAMID, INPUTFILE from IDANDINPUTFILE;"; tabel = db.GetDataTable(query); // The results can be directly applied to a DataGridView control // recipeDataGrid.DataSource = tabel; // Or looped through for some other reason var i = 0; foreach (DataRow r in tabel.Rows) { if (i < count / 2) { db.ExecuteNonQuery("delete from IDANDINPUTFILE where STREAMID=" + r["STREAMID"] + ";"); } else { } i++; } } catch (Exception fail) { String error = "The following error has occurred in cleaning up database : " + db.ToString() + "\n"; error += fail.Message; if (Settings.Default.DebugStreaming) { Functions.WriteLineToLogFile("StreamingManager: " + error); } } } do { var r = new Random(); //newId = (getIDOnly ? r.Next(100000, 999999) : r.Next(10000, 99999)); newId = r.Next(10000, 99999); } while (mediaStreamers.ContainsKey(newId) || !IdsAndInputFilesContains(newId).Equals("")); if (IdsAndInputFilesContains(request.InputFile + request.UniekClientID) == 0 && !request.InputFile.Contains("RMCLiveTV")) //live tv gets a new iD all the time anyway, due to randowm nr in inputfile string { var item = new Dictionary <string, string>(); item.Add("STREAMID", "" + newId); string i = request.InputFile.Replace("'", "''"); string u = request.UniekClientID.Replace("'", "''"); item.Add("INPUTFILE", i + u); db.Insert("IDANDINPUTFILE", item); // db.CommitTransaction(); } return(newId); }
public static VirtualRider New(string userId) { VirtualRider r = new VirtualRider(); UserRider u = UserRider.Load(userId); r.UserId = Guid.NewGuid().ToString(); r.OwnerId = userId; r.UserName = "******"; r.BikeWheelSizeMm = 700; r.EstimatedPower = false; r.TurboIsCalibrated = false; r.CurrentTurbo = ""; r.Aggression = 50; r.PowerMin = 150; r.Power1Hr = 200; r.Power5Min = 250; r.Power1Min = 300; r.Power5Sec = 500; // It's a new profile string sqlCount = @"select count(*) from cycli_virtual_riders where OwnerId=@o"; string sqlRider = @"insert into cycli_virtual_riders (UserId, OwnerId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression) " + "values (@u, @o, @n, @p4, @p3, @p2, @p1, @p0, @a) "; SQLiteDatabase db = new SQLiteDatabase(); try { // This is a backstop test - the front end should prevent extra riders if (int.Parse(db.ExecuteScalar(sqlCount, "@o", userId)) < u.MaximumVirtualRiders) { db.ExecuteNonQuery(sqlRider, "@u", r.UserId, "@o", r.OwnerId, "@n", r.UserName, "@p4", r.PowerMin, "@p3", r.Power1Hr, "@p2", r.Power5Min, "@p1", r.Power1Min, "@p0", r.Power5Sec, "@a", r.Aggression); } } finally { db.Close(); } return r; }
public static Friend MakeFriend(string userId, string friendCode) { Friend friend = null; UserRider rider = UserRider.Load(userId); if (rider != null) { Friend[] existingFriends = Load(userId); SQLiteDatabase db = new SQLiteDatabase(); // Some logic here - we only allow a new friend if // (a) The friendship does not already exist // (b) The friendCode is correct // (c) We haven't exeeded out friend limit string sqlFriend = "select UserId, UserName from cycli_riders where FriendCode = @c"; DataTable dt = db.GetDataTable(sqlFriend, "@c", friendCode); if (dt.Rows.Count > 0) { string friendId = dt.Rows[0].Field<string>("UserId"); string friendName = dt.Rows[0].Field<string>("UserName"); if (existingFriends.Count() < rider.MaximumFriends && existingFriends.Count(p => p.UserId == friendId) == 0) { string sql = "insert into cycli_friends (UserId, FriendId, Status) values (@u, @f, 'Accepted')"; if (db.ExecuteNonQuery(sql, "@u", userId, "@f", friendId) == 1) { friend = new Friend { UserName = friendName, UserId = friendId, Status = "Accepted" }; } } } // This query checks that the user has not already been invited db.Close(); } return friend; }
static void Main(string[] args) { // Initialize the Chrome Driver using (var driver = new ChromeDriver()) { // Go to the home page driver.Navigate().GoToUrl("http://www.amazon.com/s/ref=lp_2368343011_nr_n_0?rh=n%3A1036592%2Cn%3A%211036682%2Cn%3A1040660%2Cn%3A2368343011%2Cn%3A2368365011&bbn=2368343011&ie=UTF8&qid=1397584947&rnid=2368343011"); bool pagnNextLink = true; #region sample to input a form // Get User Name field, Password field and Login Button //var userNameField = driver.FindElementById("usr"); //var userPasswordField = driver.FindElementById("pwd"); //var loginButton = driver.FindElementByXPath("//input[@value='Login']"); //// Type user name and password //userNameField.SendKeys("admin"); //userPasswordField.SendKeys("12345"); //// and click the login button //loginButton.Click(); #endregion ReadOnlyCollection <IWebElement> products = driver.FindElementsByClassName("newaps"); SQLiteDatabase db = new SQLiteDatabase(); db.ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS products (id integer primary key, product varchar(1024), url varchar(1024), price varchar(16), sku varchar(64), description, attribute_key_value_json varchar(2048) , crawled_on datetime )"); int page = 1; Int64 total_products = 0; total_products += products.Count; Console.WriteLine("---------------------------------------------"); Console.WriteLine("------------------- Page {0} , Products in page {1}, Total Products {2} ", page, products.Count, total_products); Console.WriteLine("---------------------------------------------"); do { int index = 0; foreach (IWebElement product in products) { IList <IWebElement> prices = (IList <IWebElement>)driver.FindElementsByCssSelector(".bld.lrg.red,.price.bld"); IList <IWebElement> names = (IList <IWebElement>)driver.FindElementsByCssSelector(".prod.celwidget"); IWebElement anchor = product.FindElement(By.TagName("a")); Dictionary <String, String> data = new Dictionary <String, String>(); data.Add("product", product.Text.Replace("'", "''")); data.Add("price", prices[index].Text.Replace("'", "''")); data.Add("sku", names[index].GetAttribute("name").Replace("'", "''")); data.Add("url", anchor.GetAttribute("href").Replace("'", "''")); db.Insert("products", data); Console.WriteLine(names[index].GetAttribute("name").Replace("'", "''")); index++; } total_products += products.Count; if (driver.FindElementById("pagnNextLink") != null) { //driver.Manage().Timeouts().SetPageLoadTimeout(TimeSpan.FromSeconds(10)); string _nextUrl = driver.FindElementById("pagnNextLink").GetAttribute("href"); driver.Navigate().GoToUrl(_nextUrl); products = null; products = driver.FindElementsByClassName("newaps"); WebDriverWait wait = new WebDriverWait(driver, TimeSpan.FromSeconds(5)); wait.Until(i => i.FindElement(By.Id("footer"))); page++; } else { pagnNextLink = false; } Console.WriteLine("---------------------------------------------"); Console.WriteLine("------------------- Page {0} , Products in page {1}, Total Products {2} ", page, products.Count, total_products); Console.WriteLine("---------------------------------------------"); }while (pagnNextLink); Console.WriteLine("Total products : {0},", products.Count); // Extract resulting message and save it into result.txt //var result = driver.FindElementByXPath("//div[@id='case_login']/h3").Text; //sFile.WriteAllText("result.txt", result); // Take a screenshot and save it into screen.png //driver.GetScreenshot().SaveAsFile(@"screen.png", ImageFormat.Png); } Console.ReadKey(); }