public List <Log> getLogsDates(DateTime from, DateTime to) { List <Log> logs = new List <Log>(); string initDate = ToUnixTime(from).ToString(); string endDate = ToUnixTime(to).ToString(); string query = Query.GetLogs(initDate, endDate); SQLiteCommand command; connection.Open(); command = new SQLiteCommand(query, connection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { logs.Add(new Log() { User = reader["username"].ToString(), Fecha = FromUnixTime(reader.GetInt64(0)).ToShortDateString(), Hora = FromUnixTime(reader.GetInt64(1)).ToLongTimeString(), Text = reader["log"].ToString() }); } reader.Close(); connection.Close(); return(logs); }
public List <ClrTypeStats> LoadTypeStat() { var list = new List <ClrTypeStats>(); SQLiteCommand cmd = new SQLiteCommand(); cmd.Connection = cxion; cmd.CommandText = "SELECT Id, Name, MethodTable, Count, TotalSize FROM Types"; SQLiteDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { int id = dr.GetInt32(0); var name = dr.GetString(1); ulong methodTable = (ulong)dr.GetInt64(2); var count = dr.GetInt64(3); var totalSize = (ulong)dr.GetInt64(4); ClrType type = ClrDump.GetType(methodTable); if (type == null) { type = ClrDump.GetType(name); } if (type == null) { type = new ClrTypeError(name); } var clrTypeStats = new ClrTypeStats(id, type, count, totalSize); list.Add(clrTypeStats); } return(list); }
public void loadMateriaux() { listeMateriauxDatagrid.Rows.Clear(); SQLiteConnection conn = lite.getConnector(); String request = @"SELECT * FROM F_MATERIEL"; String[] rows; using (SQLiteCommand cmd = new SQLiteCommand(request, conn)) { using (SQLiteDataReader sd = cmd.ExecuteReader()) { while (sd.Read()) { Double[] result = getMaterielStock(sd.GetInt64(0)); rows = new string[5]; rows[0] = sd.GetInt64(0).ToString(); rows[1] = sd.GetString(1); if (result[0] > 0) { rows[2] = result[0].ToString(); } if (result[1] > 0) { rows[3] = result[1].ToString(); } if (result[2] > 0) { rows[4] = result[2].ToString(); } listeMateriauxDatagrid.Rows.Add(rows); } } } }
public static Messages GetMessage(ulong serverid, bool isWelcome) { if (NoServer(serverid)) { NewServer(serverid); } Messages mess = new Messages(); using var con = new SQLiteConnection(path); con.Open(); string stm; if (isWelcome) { stm = "SELECT * FROM WelcomeMessages WHERE id=" + serverid; } else { stm = "SELECT * FROM ByeMessages WHERE id=" + serverid; } using var cmd = new SQLiteCommand(stm, con); using SQLiteDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { mess.id = Convert.ToUInt64(rdr.GetInt64(0)); mess.ismessagesonline = rdr.GetString(1); mess.text = rdr.GetString(2); mess.channelid = Convert.ToUInt64(rdr.GetInt64(3)); } return(mess); }
/// <summary> /// Чтения документов из выбранного контейнера. /// </summary> /// <param name="ct_id">ID контейнера</param> /// <returns>Коллекцию DocumentMap</returns> public List <DocumentMap> GetDocumentsL(long ct_id = -1) { var reslist = new List <DocumentMap>(); try { var stmnt = String.Format("SELECT doc_id, ct_id, created_at, name, parent_id FROM mDocuments WHERE ct_id = {0}", ct_id); m_sqlCmd.CommandText = stmnt; SQLiteDataReader r = m_sqlCmd.ExecuteReader(); while (r.Read()) { var dt = DateTime.Now; var created = r["created_at"].ToString(); if (!String.IsNullOrEmpty(created)) { dt = DateTime.Parse(created); } reslist.Add(new DocumentMap(r.GetInt64(0), r.GetInt64(1), r["name"].ToString(), dt)); } r.Close(); } catch (SQLiteException ex) { //MessageBox.Show("Error: " + ex.Message); } return(reslist); }
//Učitavanje aktivnosti korisnika sa baze po pripadajucem id-u i datumu public static List <AktivnostKorisnika> DbUcitajAktivnostiKorisnikaPoDatumu(int id, DateTime datum) { konekcija.Open(); string ucitajAktivnostiKorisnika = "SELECT * FROM aktivnostikorisnika WHERE k_id =" + id + " AND datum = " + datum.ToFileTime(); SQLiteCommand createcmd = new SQLiteCommand(ucitajAktivnostiKorisnika, konekcija); SQLiteDataReader reader = createcmd.ExecuteReader(); List <AktivnostKorisnika> listaAktivnostiKorisnika = new List <AktivnostKorisnika>(); string getTip = @"SELECT tipoviaktivnosti.naziv FROM tipoviaktivnosti, aktivnostikorisnika WHERE tipoviaktivnosti.id = aktivnostikorisnika.ta_id AND aktivnostikorisnika.id=" + id; SQLiteCommand getTipCmd = new SQLiteCommand(getTip, konekcija); var tip = getTipCmd.ExecuteNonQuery().ToString(); while (reader.Read()) { AktivnostKorisnika ak = new AktivnostKorisnika(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), DateTime.FromFileTime(reader.GetInt64(4)), DateTime.FromFileTime(reader.GetInt64(5)), DateTime.FromFileTime(reader.GetInt64(6)), reader.GetDouble(7)); listaAktivnostiKorisnika.Add(ak); } konekcija.Close(); createcmd.Dispose(); return(listaAktivnostiKorisnika); }
public string GetSchedulerEvents(Users.NewUser user, int room, string uid) { try { db.CreateDataBase(user.userGroupId, db.scheduler); List <Event> xx = new List <Event>(); using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(user.userGroupId, dataBase))) { connection.Open(); string sql = string.Format(@" SELECT rowid, room, clientId, content, startDate, endDate, userId FROM scheduler WHERE room = {0} {1}" , room, user.adminType == 0 && uid == null ? "" : string.Format(" AND userId = '{0}'", uid == null ? user.userId : uid)); using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Event x = new Event(); x.id = reader.GetValue(0) == DBNull.Value ? 0 : reader.GetInt32(0); x.room = reader.GetValue(1) == DBNull.Value ? 0 : reader.GetInt32(1); x.clientId = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2); x.content = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3); x.startDate = reader.GetValue(4) == DBNull.Value ? 0 : reader.GetInt64(4); x.endDate = reader.GetValue(5) == DBNull.Value ? 0 : reader.GetInt64(5); x.userId = reader.GetValue(6) == DBNull.Value ? "" : reader.GetString(6); xx.Add(x); } } } connection.Close(); } return(JsonConvert.SerializeObject(xx, Formatting.None)); } catch (Exception e) { return(JsonConvert.SerializeObject(e.Message, Formatting.None)); } }
public static List <Zapisnik> Zapisnik_Get_All() { var lista = new List <Zapisnik>(); SQLiteCommand com = DB_connection.conn.CreateCommand(); com.CommandText = String.Format(@"SELECT * FROM ZAPISNIK"); SQLiteDataReader reader = com.ExecuteReader(); while (reader.Read()) { Zapisnik z = new Zapisnik(); z.Id = Convert.ToInt32(reader["ID"]); if (DateTime.FromFileTime(reader.GetInt64(1)).GetType() != typeof(DBNull)) { z.Datum = DateTime.FromFileTime(reader.GetInt64(1)); } if (reader["Ekipa_ID"].GetType() != typeof(DBNull)) { z.Ekipa_id = Convert.ToInt32(reader["Ekipa_ID"]); } z.Ekipa_gost = (string)reader["Ekipa_G"]; z.Rez_dom = Convert.ToInt32(reader["Rez_d"]); z.Rez_gost = Convert.ToInt32(reader["Rez_g"]); lista.Add(z); } com.Dispose(); return(lista); }
internal static Transaction CreateFromDatabaseEntry(SQLiteDataReader reader) { TransactionCurrency currency = (TransactionCurrency)reader.GetInt32(2); Transaction t = new Transaction(currency); t.Id = (ulong)reader.GetInt64(0); t.Status = (TransactionStatus)reader.GetInt32(1); t.PaymentAmount = reader.GetInt64(3); t.MinConfirmations = reader.GetInt32(4); if (!reader.IsDBNull(5)) { t.Note = reader.GetString(5); } if (!reader.IsDBNull(6)) { t.SuccessUrl = reader.GetString(6); } if (!reader.IsDBNull(7)) { t.FailureUrl = reader.GetString(7); } return(t); }
// Loads All Ledger data (this is public) public void LoadStudentLedger(SQLiteConnection conn) { SQLiteCommand sqlite_cmd; sqlite_cmd = new SQLiteCommand("SELECT * FROM Student_tbl", conn); // SQLiteDataReader read = sqlite_cmd.ExecuteReader(); StudentFlowPanel.SuspendLayout(); StudentFlowPanel.Controls.Clear(); while (read.Read()) { sc = new StudentItemControl(); sc.StudentLrn = read.GetInt64(0).ToString(); // LRN sc.StudentName = read.GetString(1) + " " + read.GetString(2) + " " + read.GetString(3); // fullname sc.StudentIDLabel.Text = read.GetInt64(0).ToString(); // LRN sc.StudentNameLabel.Text = read.GetString(1) + " " + read.GetString(2) + " " + read.GetString(3); // fullname sc.StudentSectionLabel.Text = "Section: " + read.GetString(5); // section sc.StudentLevelLabel.Text = "Level: " + read.GetInt32(6).ToString(); // level StudentFlowPanel.Controls.Add(sc); } StudentFlowPanel.ResumeLayout(); }
// Filters Ledgers by LRN (uses student_id for testing purposes -> change this to LRN later) private void LoadStudentLedgerByLRN(SQLiteConnection conn) { SQLiteCommand sqlite_cmd; sqlite_cmd = new SQLiteCommand("SELECT * FROM Enrolment_tbl WHERE lrn = @lrn", conn); sqlite_cmd.Parameters.AddWithValue("@lrn", FilterTextBox.Text); SQLiteDataReader read = sqlite_cmd.ExecuteReader(); StudentFlowPanel.SuspendLayout(); StudentFlowPanel.Controls.Clear(); while (read.Read()) { sc = new StudentItemControl(); sc.StudentLrn = read.GetInt64(read.GetOrdinal("lrn")).ToString(); // LRN sc.StudentName = read.GetString(read.GetOrdinal("first_name")) + " " + read.GetString(read.GetOrdinal("middle_name")) + " " + read.GetString(read.GetOrdinal("surname")); // fullname sc.StudentIDLabel.Text = read.GetInt64(read.GetOrdinal("lrn")).ToString(); // LRN sc.StudentNameLabel.Text = read.GetString(read.GetOrdinal("first_name")) + " " + read.GetString(read.GetOrdinal("middle_name")) + " " + read.GetString(read.GetOrdinal("surname")); // fullname sc.StudentSectionLabel.Text = "Section: " + read.GetString(read.GetOrdinal("section")); // section sc.StudentLevelLabel.Text = "Level: " + read.GetInt32(read.GetOrdinal("level")).ToString(); // level sc.StudentSection = read.GetString(read.GetOrdinal("section")); // section gettersetter sc.StudentLevel = read.GetInt32(read.GetOrdinal("level")).ToString(); // level gettersetter StudentFlowPanel.Controls.Add(sc); } StudentFlowPanel.ResumeLayout(); }
static public Node Find(UInt64 id) { string nodesSql = "SELECT * from Nodes WHERE ID = @id;"; try { SQLiteCommand command = new SQLiteCommand(nodesSql, Server.Database.Connection); command.Parameters.AddWithValue("@id", id); SQLiteDataReader reader = command.ExecuteReader(); if (reader.Read()) { return(new Node( (UInt64)reader.GetInt64(reader.GetOrdinal("ID")), new Address(reader.GetString(reader.GetOrdinal("Address"))), (Common.NodeType)reader.GetInt64(reader.GetOrdinal("Type")), reader.GetBoolean(reader.GetOrdinal("Terminated")))); } } catch (Exception) { throw; } return(null); }
public List <Sale> getIncomes(DateTime initDate, DateTime endDate) { List <Sale> incomes = new List <Sale>(); string from = ToUnixTime(initDate).ToString(); string to = ToUnixTime(endDate).ToString(); string query = Query.GetIncomes(from, to); SQLiteCommand command; connection.Open(); command = new SQLiteCommand(query, connection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { incomes.Add(new Sale() { Fecha = FromUnixTime(reader.GetInt64(0)).ToShortDateString(), Hora = FromUnixTime(reader.GetInt64(1)).ToLongTimeString(), User = reader.GetString(2), Product = reader.GetString(3), Quantity = reader.GetDouble(4), Price = reader.GetDouble(5) }); } reader.Close(); connection.Close(); return(incomes); }
public List <Sale> getAllSales() { List <Sale> sales = new List <Sale>(); string query = Query.GET_ALL_SALES; SQLiteCommand command; connection.Open(); command = new SQLiteCommand(query, connection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { sales.Add(new Sale() { Fecha = FromUnixTime(reader.GetInt64(0)).ToShortDateString(), Hora = FromUnixTime(reader.GetInt64(1)).ToLongTimeString(), User = reader.GetString(2), Product = reader.GetString(3), Quantity = reader.GetDouble(4), Price = reader.GetDouble(5) }); } reader.Close(); connection.Close(); return(sales); }
public string Get(string userId, string id) { Event x = new Event(); try { //db.CreateDataBase(userId, db.scheduler); using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) { connection.Open(); string sql = string.Format(@"SELECT rowid, room, clientId, content, startDate, endDate, userId FROM scheduler WHERE rowid = '{0}'", id); using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { x.id = reader.GetValue(0) == DBNull.Value ? 0 : reader.GetInt32(0); x.room = reader.GetValue(1) == DBNull.Value ? 0 : reader.GetInt32(1); x.clientId = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2); x.content = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3); x.startDate = reader.GetValue(4) == DBNull.Value ? 0 : reader.GetInt64(4); x.endDate = reader.GetValue(5) == DBNull.Value ? 0 : reader.GetInt64(5); x.userId = reader.GetValue(6) == DBNull.Value ? "" : reader.GetString(6); } } } } return(JsonConvert.SerializeObject(x, Formatting.None)); } catch (Exception e) { L.SendErrorLog(e, id, userId, "Scheduler", "Get"); return(JsonConvert.SerializeObject(x, Formatting.None)); } }
private SimpleItem[] SimpleItemList(string table) { List <SimpleItem> itemList = new List <SimpleItem>(); string queryString = "select * from " + table + " order by position"; // Get status list from database. using (SQLiteConnection connection = new SQLiteConnection(strConnection)) using (SQLiteCommand command = new SQLiteCommand(queryString, connection)) { connection.Open(); SQLiteDataReader reader = command.ExecuteReader(); int idColumn = reader.GetOrdinal("id"); int positionColumn = reader.GetOrdinal("position"); int titleColumn = reader.GetOrdinal(table); int noteColumn = reader.GetOrdinal("note"); while (reader.Read()) { long id = reader.GetInt64(idColumn); long position = reader.GetInt64(positionColumn); string title = reader.GetString(titleColumn); string note = reader.GetString(noteColumn); itemList.Add(new SimpleItem(id, position, title, note)); } } return(itemList.ToArray()); }
static public Account Find(string username) { string accountsSql = "SELECT * from Accounts WHERE Username = @username;"; try { SQLiteCommand command = new SQLiteCommand(accountsSql, Server.Database.Connection); command.Parameters.AddWithValue("@username", username); SQLiteDataReader reader = command.ExecuteReader(); if (reader.Read()) { return(new Account( reader.GetString(reader.GetOrdinal("Username")), reader.GetString(reader.GetOrdinal("Email")), reader.GetString(reader.GetOrdinal("Password")), (UInt64)reader.GetInt64(reader.GetOrdinal("NodeID")), reader.GetInt32(reader.GetOrdinal("Reputation")), reader.GetInt64(reader.GetOrdinal("Credits")), reader.GetBoolean(reader.GetOrdinal("Banned")))); } } catch (Exception) { throw; } return(null); }
protected override Task ConvertReaderToObject(SQLiteDataReader reader) { //log.Debug("in reader with task: "+reader.GetString(0)+" "+ reader.GetInt64(1) + " " + reader.GetString(2) + " " + reader.GetString(3) + " " + reader.GetValue(4)); Task result = new Task(reader.GetInt64(0), reader.GetInt64(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.IsDBNull(5) ? null : reader.GetString(5), reader.GetInt64(6), reader.GetInt64(7)); return(result); }
// Statistika za razdoblje po potrosnji public static List <AktivnostKorisnika> StatistikaRazdobljaPoPotrosnji(DateTime datumOd, DateTime datumDo, int id) { datumDo.AddHours(23); datumDo.AddMinutes(59); datumDo.AddSeconds(59); konekcija.Open(); string statistikaRazdoblja = @"SELECT * FROM aktivnostikorisnika WHERE k_id=" + id + " AND datum>=" + datumOd.ToFileTime() + " AND datum<=" + datumDo.ToFileTime() + " ORDER BY potrosnja"; SQLiteCommand cmd = new SQLiteCommand(statistikaRazdoblja, konekcija); SQLiteDataReader reader = cmd.ExecuteReader(); List <AktivnostKorisnika> listaAktivnostiKorisnika = new List <AktivnostKorisnika>(); while (reader.Read()) { AktivnostKorisnika ak = new AktivnostKorisnika(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetString(3), DateTime.FromFileTime(reader.GetInt64(4)), DateTime.FromFileTime(reader.GetInt64(5)), DateTime.FromFileTime(reader.GetInt64(6)), reader.GetDouble(7)); listaAktivnostiKorisnika.Add(ak); } konekcija.Close(); cmd.Dispose(); return(listaAktivnostiKorisnika); }
private static void ReadPlayerInfo(SQLiteDataReader reader, Player player) { player.Id = (uint)reader.GetInt32(0); player.Name = reader.GetString(1); player.Gender = (Gender)reader.GetByte(2); player.Vocation = (Vocation)reader.GetByte(3); player.Level = (ushort)reader.GetInt16(4); player.MagicLevel = reader.GetByte(5); player.Experience = (uint)reader.GetInt32(6); player.MaxHealth = (ushort)reader.GetInt16(7); player.MaxMana = (ushort)reader.GetInt16(8); player.Capacity = (uint)reader.GetInt32(9); player.Outfit.LookType = (ushort)reader.GetInt16(10); player.Outfit.Head = reader.GetByte(11); player.Outfit.Body = reader.GetByte(12); player.Outfit.Legs = reader.GetByte(13); player.Outfit.Feet = reader.GetByte(14); player.Outfit.Addons = reader.GetByte(15); if (reader.GetInt64(20) > 0) { int x = reader.GetInt32(16); int y = reader.GetInt32(17); int z = reader.GetInt32(18); player.SavedLocation = new Location(x, y, z); player.Direction = (Direction)reader.GetByte(19); player.LastLogin = new DateTime(reader.GetInt64(20)); } player.Speed = (ushort)(220 + (2 * (player.Level - 1))); }
public static List <Tag> getTagList(long plc_id) { try { using (SQLiteConnection conn = SQLiteDBMS.getConnection()) { conn.Open(); SQLiteCommand cmd = new SQLiteCommand( "SELECT * FROM Tag WHERE plc_id =" + plc_id, conn); List <Tag> list = new List <Tag>(); using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { list.Add(new Tag(reader.GetInt64(0), reader.GetString(1), reader.GetInt32(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetInt64(6) )); } } return(list); } } catch (SQLiteException ex) { // table not exist if (ex.ErrorCode == 1) { createTagTable(); } } return(new List <Tag>()); }
public string getLatestVersionFormDB(string startTime, string endTime) { int recordSerial = 0; string result = "{\"versions\" : [";; using (SQLiteConnection conn = new SQLiteConnection("Data source = versions.db")) { conn.Open(); using (SQLiteCommand cmd = new SQLiteCommand(conn)) { cmd.CommandText = string.Format( "SELECT startTime, endTime, version FROM TimeNameMap where startTime >= {0} and startTime <= {1} or endTime >= {0} and endTime <= {1} order by startTime", startTime, endTime); SQLiteDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { long start = reader.GetInt64(0); long end = reader.GetInt64(1); long version = reader.GetInt64(2); if (recordSerial > 0) { result += ","; } result += "{\"Version\" :" + version; result += ", \"start\" : " + start; result += ", \"end\" : " + end + "}"; recordSerial++; } result += "], \"length\" :" + recordSerial + "}"; } } return(result); }
private DatabaseStarSystem ReadStarSystemEntry(SQLiteCommand cmd) { string systemName = string.Empty; long? systemAddress = null; long? edsmId = null; string starSystemJson = string.Empty; string comment = string.Empty; DateTime lastUpdated = DateTime.MinValue; DateTime?lastVisit = null; int totalVisits = 0; using (SQLiteDataReader rdr = cmd.ExecuteReader()) { if (rdr.Read()) { for (int i = 0; i < rdr.FieldCount; i++) { if (SCHEMA_VERSION >= 2 && rdr.GetName(i) == "systemaddress") { systemAddress = rdr.IsDBNull(i) ? null : (long?)rdr.GetInt64(i); } if (SCHEMA_VERSION >= 2 && rdr.GetName(i) == "edsmid") { edsmId = rdr.IsDBNull(i) ? null : (long?)rdr.GetInt64(i); } if (rdr.GetName(i) == "name") { systemName = rdr.IsDBNull(i) ? null : rdr.GetString(i); } if (rdr.GetName(i) == "starsystem") { starSystemJson = rdr.IsDBNull(i) ? null : rdr.GetString(i); } if (rdr.GetName(i) == "comment") { comment = rdr.IsDBNull(i) ? null : rdr.GetString(i); } if (rdr.GetName(i) == "starsystemlastupdated") { lastUpdated = rdr.IsDBNull(i) ? DateTime.MinValue : rdr.GetDateTime(i).ToUniversalTime(); } if (rdr.GetName(i) == "lastvisit") { lastVisit = rdr.IsDBNull(i) ? null : (DateTime?)rdr.GetDateTime(i).ToUniversalTime(); } if (rdr.GetName(i) == "totalvisits") { totalVisits = rdr.IsDBNull(i) ? 0 : rdr.GetInt32(i); } } } } return(new DatabaseStarSystem(systemName, systemAddress, edsmId, starSystemJson) { comment = comment, lastUpdated = lastUpdated, lastVisit = lastVisit, totalVisits = totalVisits }); }
public static int InsertResultItem(Simulation.GameRecord record, SQLiteCommand command) { string tableStr = $"Tbl{record.holeUniquePrime}"; command.CommandText = $"SELECT * FROM {tableStr} where Flop = {record.flopUniquePrime}"; SQLiteDataReader dr = command.ExecuteReader(); if (record.winFlag == 1) { // read integer in Wins column dr.Read(); long updatedWinCount = dr.GetInt64(1) + 1; // update integer in Wins column dr.Close(); command.CommandText = $"UPDATE {tableStr} SET W = {updatedWinCount} WHERE Flop = {record.flopUniquePrime};"; } else if (record.winFlag == 0) { // read integer in Losses column dr.Read(); long updatedLossCount = dr.GetInt64(2) + 1; // update integer in Losses column dr.Close(); command.CommandText = $"UPDATE {tableStr} SET L = {updatedLossCount} WHERE Flop = {record.flopUniquePrime};"; } return(command.ExecuteNonQuery()); }
public string Load(string userGroupId, string userId) { db.CreateDataBase(userGroupId, db.scheduler); List <Event> xx = new List <Event>(); try { using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(dataBase))) { connection.Open(); string sql = "SELECT rowid, room, clientId, content, startDate, endDate, userId FROM scheduler"; using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { using (SQLiteDataReader reader = command.ExecuteReader()) { xx = new List <Event>(); while (reader.Read()) { Event x = new Event(); x.id = reader.GetValue(0) == DBNull.Value ? 0 : reader.GetInt32(0); x.room = reader.GetValue(1) == DBNull.Value ? 0 : reader.GetInt32(1); x.clientId = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2); x.content = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3); x.startDate = reader.GetValue(4) == DBNull.Value ? 0 : reader.GetInt64(4); x.endDate = reader.GetValue(5) == DBNull.Value ? 0 : reader.GetInt64(5); x.userId = reader.GetValue(6) == DBNull.Value ? "" : reader.GetString(6); xx.Add(x); } } } connection.Close(); } return(JsonConvert.SerializeObject(xx, Formatting.None)); } catch (Exception e) { return(e.Message); } }
internal static float CalculatePreFlopPercentage(long holeId, SQLiteCommand cmd) { /********************************************** * Each table corresponds to a specific set of hole * cards.Add up all the wins and losses of in a given * table and take the ratio of wins to the total games * that the holecards have participated in (wins + losses) * to get the pre-flop probability of winning. ***********************************************/ Trace.WriteLine($"{nameof(CalculatePreFlopPercentage)} method running with HoleId = {holeId}"); long _winTotal = 0; long _lossTotal = 0; // need to test this command in Sqlite DB Viewer cmd.CommandText = $"SELECT W, L FROM Tbl{holeId};"; using (SQLiteDataReader dr = cmd.ExecuteReader()) { Trace.Assert(dr.HasRows == true); while (dr.Read()) { // note the indices start at 0 because we only // selected the win and loss columns _winTotal += dr.GetInt64(0); _lossTotal += dr.GetInt64(1); } } // return odds of winning as a percentage of 100 return((float)(100.0 * (float)_winTotal / (float)(_winTotal + _lossTotal))); }
public void Execute() { var unicodeEntryList = new List <UnicodeEntry>(); try { SQLiteConnection connection = new SQLiteConnection("Data Source=unicode.sqlite;"); connection.Open(); var command = new SQLiteCommand("SELECT * FROM unicode;", connection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { string resultString = reader.GetString(1); unicodeEntryList.Add(new UnicodeEntry( reader.IsDBNull(0) ? 0 : reader.GetInt64(0), resultString.Length > 0 ? resultString[0] : ' ', reader.IsDBNull(2) ? 0 : reader.GetInt64(2), reader.IsDBNull(3) ? 0 : reader.GetInt64(3) )); } } catch (SQLiteException e) { Console.WriteLine(e); } }
internal static float CalculatePostFlopPercentage(long holeId, long flopId, SQLiteCommand cmd) { /********************************************** * Each table corresponds to a specific set of hole * cards. Each FlopId corresponds to a specific * set of flop cards. Take the ratio of wins * to the total games (wins + losses) that hole & flop combination * has participated in to get the post-flop * probability of winning. ***********************************************/ long _winTotal = 0; long _lossTotal = 0; // need to test this command in Sqlite DB Viewer cmd.CommandText = $"SELECT W, L FROM Tbl{holeId} WHERE Flop = {flopId};"; using (SQLiteDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { // note the indices start at 0 because we only // selected the win and loss columns _winTotal += dr.GetInt64(0); _lossTotal += dr.GetInt64(1); } } // return odds of winning as a percentage of 100 return((float)(100.0 * (float)_winTotal / (float)(_winTotal + _lossTotal))); }
public IList <DotNetMetric> GetByPeriod(DateTimeOffset fromTime, DateTimeOffset toTime) { using var connection = new SQLiteConnection(_connector.GetStringConnection()); using var cmd = new SQLiteCommand(connection); cmd.Connection.Open(); // прописываем в команду SQL запрос на получение всех данных из таблицы cmd.CommandText = $"SELECT id, value, time FROM {Table} WHERE time BETWEEN @fromTime AND @toTime;"; cmd.Parameters.AddWithValue("@fromTime", fromTime.ToUnixTimeSeconds()); cmd.Parameters.AddWithValue("@toTime", toTime.ToUnixTimeSeconds()); cmd.Prepare(); var returnList = new List <DotNetMetric>(); using (SQLiteDataReader reader = cmd.ExecuteReader()) { // пока есть что читать -- читаем while (reader.Read()) { // добавляем объект в список возврата returnList.Add(new DotNetMetric { Id = reader.GetInt64(0), Value = reader.GetInt32(1), // налету преобразуем прочитанные секунды в метку времени Time = DateTimeOffset.FromUnixTimeSeconds(reader.GetInt64(2)) }); } } return(returnList); }
public static List <Dogadaj> DohavtiSve() { BP.otvoriKonekciju(); List <Dogadaj> listaDogadaja = new List <Dogadaj>(); SQLiteCommand command = BP.konekcija.CreateCommand(); //Umetanje podataka u tablicu command.CommandText = "Select * from dogadaj"; SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { Dogadaj temp = new Dogadaj(); //temp.Datum = DateTime.FromFileTime(reader.GetInt64(2)); temp.Id = (int)(Int64)reader["id"]; temp.Datum = DateTime.FromFileTime(reader.GetInt64(1)); temp.Opis = (string)reader["opis"]; temp.Datum = DateTime.FromFileTime(reader.GetInt64(1)); temp.Mjesto = (string)reader["mjesto"]; listaDogadaja.Add(temp); } reader.Dispose(); command.Dispose(); BP.zatvoriKonekciju(); return(listaDogadaja); }