public ArrayList GetAllKeyFromTable(string tableName) { ArrayList list = new ArrayList(); this.ConnectToDB(); string query = "SELECT * FROM '" + tableName + "';"; try { using (SQLiteCommand cmd = new SQLiteCommand(query, m_connection)) { using (SQLiteDataReader sqlite_datareader = cmd.ExecuteReader()) { while (sqlite_datareader.Read()) { KeyDetail detail = new KeyDetail(sqlite_datareader.GetString(0)); detail.keypid = sqlite_datareader.GetString(1); detail.eid = sqlite_datareader.GetString(2); detail.aid = sqlite_datareader.GetString(3); detail.edi = sqlite_datareader.GetString(4); detail.sub = sqlite_datareader.GetString(5); detail.lit = sqlite_datareader.GetString(6); detail.lic = sqlite_datareader.GetString(7); detail.cid = sqlite_datareader.GetString(8); detail.activationCount = sqlite_datareader.GetInt32(9); detail.errorCode = sqlite_datareader.GetString(10); detail.time = sqlite_datareader.GetString(11); detail.prd = tableName; list.Add(detail); } } } } catch (Exception e) { }; this.CloseDB(); return(list); }
/// <summary> /// Liest die Daten zu einer bestimmten Person aus der DB. /// </summary> /// <param name="id">Die Id der zu ladenden Person.</param> /// <returns>Eine <see cref="IPerson"/> mit den Daten aus der DB.</returns> public IVersorger GetById(int id) { var connection = new SQLiteConnection(SQL_CONNECTION_STRING); connection.Open(); IVersorger versorger = null; var statement = new SQLiteCommand(SQL_GET_BY_ID, connection); statement.Parameters.Add(new SQLiteParameter("@id", id)); SQLiteDataReader reader = statement.ExecuteReader(); while (reader.Read()) { versorger = VersorgerFactory.Create(reader.GetInt32(0), (EnumStammdatenTyp)reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6)); } connection.Close(); return(versorger); }
public StarSystem GetStarSystem(string name, bool refreshIfOutdated = true) { if (!File.Exists(DbFile)) { return(null); } StarSystem result = null; try { bool needToUpdate = false; using (var con = SimpleDbConnection()) { con.Open(); using (var cmd = new SQLiteCommand(con)) { cmd.CommandText = SELECT_BY_NAME_SQL; cmd.Prepare(); cmd.Parameters.AddWithValue("@name", name); using (SQLiteDataReader rdr = cmd.ExecuteReader()) { if (rdr.Read()) { result = JsonConvert.DeserializeObject <StarSystem>(rdr.GetString(2)); if (result == null) { Logging.Info("Failed to obtain system for " + name); } if (result != null) { if (result.visits < 1) { // Old-style system; need to update result.visits = rdr.GetInt32(0); result.lastvisit = rdr.GetDateTime(1); needToUpdate = true; } if (result.lastupdated == null) { result.lastupdated = rdr.GetDateTime(4); } if (result.comment == null) { if (!rdr.IsDBNull(4)) { result.comment = rdr.GetString(4); } } if (refreshIfOutdated && result.lastupdated < DateTime.Now.AddHours(-1)) { // Data is stale StarSystem updatedResult = DataProviderService.GetSystemData(name, null, null, null); updatedResult.visits = result.visits; updatedResult.lastvisit = result.lastvisit; updatedResult.lastupdated = DateTime.Now; result = updatedResult; needToUpdate = true; } } } } } con.Close(); } if (needToUpdate) { updateStarSystem(result); } } catch (Exception ex) { Logging.Warn("Problem obtaining data: " + ex); } return(result); }
private void POSCategotyFieldGeneration(string tableName) { SQLiteConnection conn = new SQLiteConnection(DATABASE); conn.Open(); var command = conn.CreateCommand(); consoleLabel.Content = tableName; command.CommandText = "SELECT id FROM platforms WHERE name = '" + tableName + "'"; platID = (int)command.ExecuteScalar(); Random rand = new Random(); Label gameColLabel = new Label(); Label count = new Label(); Label qty = new Label(); Label priceLabel = new Label(); Label add = new Label(); BG.Background = Brushes.CornflowerBlue; BG2.Background = MainWindow.colourArr[rand.Next() % MainWindow.colourArr.Length]; gameColLabel.Content = "Game Title"; count.Content = "Count"; qty.Content = "Qty."; priceLabel.Content = "Price"; add.Content = "Add To Cart"; gameColLabel.FontWeight = FontWeights.ExtraBold; count.FontWeight = FontWeights.ExtraBold; qty.FontWeight = FontWeights.ExtraBold; priceLabel.FontWeight = FontWeights.ExtraBold; add.FontWeight = FontWeights.ExtraBold; GameColumn.Children.Add(gameColLabel); Qty.Children.Add(qty); Price.Children.Add(priceLabel); AddToCart.Children.Add(add); // AddToCart.Children.Add(addCartButton); command.CommandText = "SELECT g.id, g.name, m.quantity, CASE WHEN m.price < 0 THEN 0 ELSE m.price END FROM games g INNER JOIN (" + " SELECT game_id, quantity, price FROM multiplat_games WHERE platform_id = (" + " SELECT id FROM platforms WHERE name = '" + tableName + "'" + "))m ON g.id = m.game_id WHERE m.quantity > 0 ORDER BY g.name ASC;"; String tmpPrice = "0"; SQLiteDataReader sdr = command.ExecuteReader(); int fieldcount = 0; while (sdr.Read()) { Button cartBtn = new Button(); Label gameLabel = new Label(); Label quantity = new Label(); Label price = new Label(); var res = this.FindResource("AddCart"); Style style = res as Style; gameLabel.Content = sdr.GetString(1); quantity.Content = sdr.GetInt32(2); price.Content = sdr.GetInt32(3); cartBtn.Style = style; cartBtn.Click += new RoutedEventHandler(confirmEvent); AddToCart.Children.Add(cartBtn); GameColumn.Children.Add(gameLabel); Qty.Children.Add(quantity); Price.Children.Add(price); addCartMap.Add(sdr.GetInt32(0), cartBtn); fieldcount++; } sdr.Close(); conn.Close(); }
public void RequestSubDirs(TreeViewFolderBrowserHelper helper, TreeNodePath parent, TreeViewCancelEventArgs e) { if (parent.Path == null) { return; } if (parent.Path == "") { rootFolder = RootFolder.None; } string sql = string.Empty; bool createDummyNode = true; string nodeTag; // We have a Special folder, when we are at the root level if (parent.IsSpecialFolder) { rootFolder = RootFolder.None; } if (rootFolder == RootFolder.None) { switch ((string)parent.Tag) { case "artist": rootFolder = RootFolder.Artist; sql = SQL_STMT_ARTIST; break; case "albumartist": rootFolder = RootFolder.AlbumArtist; sql = SQL_STMT_ALBUMARTIST; break; case "album": rootFolder = RootFolder.Album; sql = SQL_STMT_ALBUM; createDummyNode = false; break; case "genre": rootFolder = RootFolder.Genre; sql = SQL_STMT_GENRE; break; } } else if (rootFolder == RootFolder.Artist) { sql = string.Format(SQL_STMT_ARTISTSEARCH, Util.RemoveInvalidChars(parent.Path)); isRootFolder = false; createDummyNode = false; } else if (rootFolder == RootFolder.AlbumArtist) { sql = string.Format(SQL_STMT_ALBUMARTISTSEARCH, Util.RemoveInvalidChars(parent.Path)); isRootFolder = false; createDummyNode = false; } else if (rootFolder == RootFolder.Genre) { isRootFolder = false; string[] searchString = (parent.Tag as string).Split('\\'); if (searchString.GetLength(0) == 2) { sql = string.Format(SQL_STMT_GENREARTISTSEARCH, Util.RemoveInvalidChars(parent.Path)); createDummyNode = true; } else { sql = string.Format(SQL_STMT_GENREARTISTALBUMSEARCH, Util.RemoveInvalidChars(searchString[1]), Util.RemoveInvalidChars(parent.Path)); createDummyNode = false; } } string connection = string.Format(@"Data Source={0}", Options.MainSettings.MediaPortalDatabase); try { SQLiteConnection conn = new SQLiteConnection(connection); conn.Open(); using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; log.Debug("TreeViewBrowser: Executing sql: {0}", sql); using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { string dbValue = reader.GetString(0); TreeNodePath newNode = CreateTreeNode(helper, dbValue, dbValue, createDummyNode, false, false); if (isRootFolder) { nodeTag = (string)parent.Tag; } else { nodeTag = string.Format(@"{0}\{1}", parent.Tag, dbValue); } newNode.Tag = nodeTag; parent.Nodes.Add(newNode); } } } conn.Close(); } catch (Exception ex) { log.Error("TreeViewBrowser: Error executing sql: {0}", ex.Message); } }
//create new profile in database private void button_create_profile_Click(object sender, EventArgs e) { if (text_password1.Text == text_password2.Text && text_login.Text != "") { //enrycpt login and password Crypt cryptograph = new Crypt(); string login = cryptograph.Decrypt(text_login.Text); string password = cryptograph.Decrypt(text_password1.Text); //connect to database using (SQLiteConnection connection = new SQLiteConnection()) { connection.ConnectionString = "Data Source = " + Base_Form.db_name; connection.Open(); using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = "SELECT * FROM [profiles]"; command.CommandType = CommandType.Text; SQLiteDataReader reader = command.ExecuteReader(); Boolean flag_copy = false; //check if there is a profile with that name while (reader.Read()) { string text = $"{reader.GetString(1)}"; if (login == text) { flag_copy = true; break; } } reader.Close(); //if profile with that name exist then show MessageBox and clear all textBox if (flag_copy) { MessageBox.Show("Профиль с таким именем уже существует."); connection.Close(); text_login.Clear(); text_password1.Clear(); text_password2.Clear(); } else { command.CommandText = "INSERT INTO [profiles]([login], [password]) VALUES(@name, @pass)"; command.Parameters.AddWithValue("@name", login); command.Parameters.AddWithValue("@pass", password); command.ExecuteNonQuery(); connection.Close(); //if successful then show label "Профиль создан" and clear all textBox label_created.Visible = true; text_login.Clear(); text_password1.Clear(); text_password2.Clear(); } } } } else { //if failure then show label "Ошибка в логине или пароле" and clear textBox with password text_password1.Clear(); text_password2.Clear(); label_created.Text = "Ошибка"; label_created.Visible = true; label_created.ForeColor = Color.Red; } }
public List <LibraryFactory> GetAll() { List <LibraryFactory> lib = new List <LibraryFactory>(); using (SQLiteConnection cnn = new SQLiteConnection(LoadConnectionString())) { cnn.Open(); SQLiteCommand commandMovie = new SQLiteCommand("SELECT * FROM Movie", cnn); SQLiteDataReader readerMovie = commandMovie.ExecuteReader(); while (readerMovie.Read()) { int entryId = readerMovie.GetInt32(0); List <Cast> casts = new List <Cast>(); SQLiteCommand commandEntryCast = new SQLiteCommand("SELECT * FROM Entry_Cast WHERE entry_id = @entryid", cnn); commandEntryCast.Parameters.AddWithValue("@entryid", entryId); SQLiteDataReader readerEntryCast = commandEntryCast.ExecuteReader(); while (readerEntryCast.Read()) { int castId = readerEntryCast.GetInt32(1); Cast cast = new Cast(); SQLiteCommand commandCast = new SQLiteCommand("SELECT * FROM Cast WHERE cast_id = @castId", cnn); commandCast.Parameters.AddWithValue("@castId", castId); SQLiteDataReader readerCast = commandCast.ExecuteReader(); while (readerCast.Read()) { cast.Firstname = readerCast.GetString(1); cast.Role = readerCast.GetString(4); } casts.Add(cast); } LibraryFactory movie = LibraryFactory.GetLibrary(LibraryType.Movie, readerMovie.GetInt32(2), readerMovie.GetString(1)); SQLiteCommand commandEntry = new SQLiteCommand("SELECT * FROM Entry WHERE entry_id = @entryid", cnn); commandEntry.Parameters.AddWithValue("@entryid", entryId); SQLiteDataReader readerEntry = commandEntry.ExecuteReader(); while (readerEntry.Read()) { Entry entry = new Entry() { Name = readerEntry.GetString(1), Description = readerEntry.GetString(2), Release = readerEntry.GetDateTime(3), Poster = readerEntry.GetString(4), Cast = casts }; ((Movie)movie).SetEntry(entry); } lib.Add(movie); } SQLiteCommand commandSeries = new SQLiteCommand("SELECT * FROM Series", cnn); SQLiteDataReader readerSeries = commandSeries.ExecuteReader(); while (readerSeries.Read()) { LibraryFactory series = LibraryFactory.GetLibrary(LibraryType.Series, readerSeries.GetInt32(0), readerSeries.GetString(3)); ((Series)series).Description = readerSeries.GetString(2); ((Series)series).Poster = readerSeries.GetString(4); ((Series)series).Name = readerSeries.GetString(1); SQLiteCommand commandSeason = new SQLiteCommand("SELECT * FROM Season WHERE series_id = @seriesid ORDER BY season_id ASC", cnn); commandSeason.Parameters.AddWithValue("@seriesid", readerSeries.GetInt32(0)); SQLiteDataReader readerSeason = commandSeason.ExecuteReader(); while (readerSeason.Read()) { Season season = new Season(readerSeason.GetString(2), readerSeason.GetString(3)); SQLiteCommand commandEntrySeason = new SQLiteCommand("SELECT * FROM Entry_Season WHERE season_id = @seasonid ORDER BY 'index' ASC", cnn); commandEntrySeason.Parameters.AddWithValue("@seasonid", readerSeason.GetInt32(0)); SQLiteDataReader readerEntrySeason = commandEntrySeason.ExecuteReader(); while (readerEntrySeason.Read()) { int entryId = readerEntrySeason.GetInt32(0); List <Cast> casts = new List <Cast>(); SQLiteCommand commandEntryCast = new SQLiteCommand("SELECT * FROM Entry_Cast WHERE entry_id = @entryid", cnn); commandEntryCast.Parameters.AddWithValue("@entryid", entryId); SQLiteDataReader readerEntryCast = commandEntryCast.ExecuteReader(); while (readerEntryCast.Read()) { int castId = readerEntryCast.GetInt32(1); Cast cast = new Cast(); SQLiteCommand commandCast = new SQLiteCommand("SELECT * FROM Cast WHERE cast_id = @castId", cnn); commandCast.Parameters.AddWithValue("@castId", castId); SQLiteDataReader readerCast = commandCast.ExecuteReader(); while (readerCast.Read()) { cast.Firstname = readerCast.GetString(1); cast.Role = readerCast.GetString(4); } casts.Add(cast); } SQLiteCommand commandEntry = new SQLiteCommand("SELECT * FROM Entry WHERE entry_id = @entryid", cnn); commandEntry.Parameters.AddWithValue("@entryid", entryId); SQLiteDataReader readerEntry = commandEntry.ExecuteReader(); while (readerEntry.Read()) { Entry entry = new Entry() { Name = readerEntry.GetString(1), Description = readerEntry.GetString(2), Release = readerEntry.GetDateTime(3), Poster = readerEntry.GetString(4), Cast = casts }; season.AddEpisode(entry); } } ((Series)series).AddSeason(season); } lib.Add(series); } } return(lib); }
public string Load(string lang) { try { List <NewActivity> xx = new List <NewActivity>(); using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + Server.MapPath("~/App_Data/" + dataBase))) { connection.Open(); string sql = @"SELECT rowid, activity, factorKcal, isSport FROM activities ORDER BY activity ASC"; using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { string[] translations = T.Translations(lang); using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { NewActivity x = new NewActivity() { id = reader.GetValue(0) == DBNull.Value ? 0 : reader.GetInt32(0), activity = reader.GetValue(1) == DBNull.Value ? "" : T.Tran(reader.GetString(1), translations, string.IsNullOrEmpty(lang) ? "hr" : lang), factorKcal = reader.GetValue(2) == DBNull.Value ? 0.0 : Convert.ToDouble(reader.GetString(2)), isSport = reader.GetValue(3) == DBNull.Value ? 0 : reader.GetInt32(3) }; xx.Add(x); } } } connection.Close(); } return(JsonConvert.SerializeObject(xx, Formatting.None)); } catch (Exception e) { return(JsonConvert.SerializeObject(e.Message, Formatting.None)); } }
private void CountryControl_Load(object sender, EventArgs e) { //button1.Text = "Рашка"; //Конект с бд, открываем DB.Open(); List <Button> btns = new List <Button>(); btns.Add(button1); btns.Add(button2); btns.Add(button3); btns.Add(button4); SQLiteCommand command = new SQLiteCommand("SELECT Capital FROM CapitalDB", DB); SQLiteDataReader reader = command.ExecuteReader(); //string aa = reader[1].ToString(); string answerCapital; string questionCountry; Random rng = new Random(); List <string> capitalList = new List <string>(); while (reader.Read()) { capitalList.Add(reader.GetString(0)); } for (int j = 4; j != 0; j -= 1) { string rngCapital = capitalList[rng.Next(capitalList.Count)]; int btnsNumber; btnsNumber = rng.Next(0, j); btns[btnsNumber].Text = rngCapital; btns.RemoveAt(btnsNumber); if (j == 4) { SQLiteCommand Capital = new SQLiteCommand("SELECT Country FROM CapitalDB WHERE Capital = @rngCapital", DB); SQLiteParameter nameParam = new SQLiteParameter("@rngCapital", rngCapital); Capital.Parameters.Add(nameParam); SQLiteDataReader rdr = Capital.ExecuteReader(); rdr.Read(); questionCountry = rdr.GetString(0); rdr.Close(); answerCapital = rngCapital; label2.Text = questionCountry; } capitalList.Remove(rngCapital); } //string rngCountry = countryList[rng.Next(countryList.Count)]; //button1.Text = rngCountry; //countryList.Remove(rngCountry); //foreach (string record in reader) //{ // Random rng = new Random(); // //button1.Text = rng.Next(record); // button1.Text = record; //} }
private void textBox1_KeyPress(object sender, KeyPressEventArgs e) { int total = 0; string desc = ""; int harga = 0; string barc = ""; if (e.KeyChar == (char)Keys.Escape) { jumlah_barang = Int32.Parse(textBox1.Text); textBox1.Text = ""; } else if (e.KeyChar == (char)Keys.Enter) { try { SQLiteConnection sqlite_conn; sqlite_conn = CreateConnection(); SQLiteConnection tulis; tulis = CreateConnection(); SQLiteConnection update1; update1 = CreateConnection(); //Select Item -> Barcode string stm = "SELECT * FROM Inventory where barcode=@barcode"; var cmd = new SQLiteCommand(stm, sqlite_conn); cmd.Parameters.Add(new SQLiteParameter("@barcode", textBox1.Text)); SQLiteDataReader rdr = cmd.ExecuteReader(); if (!rdr.HasRows) { MessageBox.Show("Barcode tidak ditemukan!"); textBox1.Text = ""; textBox1.Focus(); return; } //Tambah ke Keranjang while (rdr.Read()) { //Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)} {rdr.GetString(3)}"); desc = rdr.GetString(1); harga = rdr.GetInt32(2); barc = rdr.GetString(3); } //Console.WriteLine($"{desc} {harga} {barc}"); //Console.WriteLine($"{jumlah_barang}"); string baca_keranjang = "SELECT * FROM Keranjang where barcode=@barcode"; var cmd_baca_keranjang = new SQLiteCommand(baca_keranjang, tulis); cmd_baca_keranjang.Parameters.Add(new SQLiteParameter("@barcode", textBox1.Text)); SQLiteDataReader rdr_keranjang = cmd_baca_keranjang.ExecuteReader(); if (!rdr_keranjang.HasRows) { try { var cmd_tulis = new SQLiteCommand(tulis); cmd_tulis.CommandText = "INSERT INTO Keranjang('deskripsi', 'jumlah', 'harga satuan', 'total', 'barcode') " + "VALUES(@desc, @jml, @satuan, @tot, @barc)"; cmd_tulis.Parameters.AddWithValue("@desc", desc); if (jumlah_barang == 0) { cmd_tulis.Parameters.AddWithValue("@jml", 1); total = harga * 1; } else { cmd_tulis.Parameters.AddWithValue("@jml", jumlah_barang); total = harga * jumlah_barang; } cmd_tulis.Parameters.AddWithValue("@satuan", harga); cmd_tulis.Parameters.AddWithValue("@tot", total); cmd_tulis.Parameters.AddWithValue("@barc", barc); cmd_tulis.Prepare(); cmd_tulis.ExecuteNonQuery(); jumlah_barang = 0; } catch (Exception tulis_ex) { MessageBox.Show(tulis_ex.ToString()); } } else { //var cmd_update = new SQLiteCommand(update1); //cmd_update.CommandText = "UPDATE Keranjang set jumlah = jumlah+1 WHERE barcode=@barc"; //cmd_update.Parameters.AddWithValue("@barc", textBox1); //cmd_update.Prepare(); //cmd_update.ExecuteNonQuery(); } sqlite_conn.Close(); tulis.Close(); update1.Close(); textBox1.Text = ""; label6.Text = harga.ToString(); textBox1.Focus(); Refresh_data(); Update_label(false); } catch (SQLiteException sqli_ex) { MessageBox.Show(sqli_ex.ToString()); } //button4.PerformClick(); } }
//read data from database and add to drawParameterList public List <Coordinates.Coordinate> readTrackData(int trackId) { StringBuilder query; dbCoordinatesList = new List <Coordinates.Coordinate>(); try { query = new StringBuilder(); query.Append("SELECT DISTINCT longitude, latitude, altitude, fix FROM gpsData WHERE trackId = " + Int64.Parse(trackId.ToString()) + " order by ID ASC ;"); createSqliteConn(); dataReader = readData(query); while (dataReader.Read()) { dbCoordinatesList.Add(new Coordinates.Coordinate(new Geometry.Point(float.Parse(dataReader.GetString(1).ToString()), float.Parse(dataReader.GetString(0).ToString()), float.Parse(dataReader.GetValue(2).ToString().Contains(",") ? dataReader.GetValue(2).ToString() : dataReader.GetValue(2).ToString() + ",0")), true ));//(latitude, longitude, altitude) } closeSqliteConn(); } catch (SQLiteException sqliteEx) { MessageBox.Show("m2" + sqliteEx.Message); } return(dbCoordinatesList); }
public JsonFile GetRecipeData(string userId, string id) { JsonFile x = new JsonFile(); try { //db.CreateDataBase(userId, db.recipes); //db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.recipes, CONSUMERS, "INTEGER"); //new column in recipes tbl. //db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.recipes, USER_ID, "VARCHAR(50)"); //new column in recipes tbl. // db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.recipes, MEAL_GROUP); //new column in recipes tbl. // db.AddColumn(userId, db.GetDataBasePath(userId, dataBase), db.recipes, RECIPE_DATA, "TEXT"); //new column in recipes tbl. using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, dataBase))) { connection.Open(); string sql = string.Format(@"SELECT recipeData FROM recipes WHERE id = '{0}'", id); using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { command.Parameters.Add(new SQLiteParameter("id", id)); Clients.Client client = new Clients.Client(); using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { string data = reader.GetValue(0) == DBNull.Value ? null : reader.GetString(0); if (!string.IsNullOrWhiteSpace(data)) { x = JsonConvert.DeserializeObject <JsonFile>(data); // new sistem: recipe saved in db } else { x = JsonConvert.DeserializeObject <JsonFile>(GetJsonFile(userId, id)); // old sistem: recipe saved in json file } } } } } return(x); } catch (Exception e) { L.SendErrorLog(e, id, userId, "Recipes", "GetRecipeData"); return(x); } }
public string GetAppRecipe(string id, string lang) { try { NewRecipe x = new NewRecipe(); using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + Server.MapPath(string.Format("~/App_Data/{0}", appDataBase)))) { connection.Open(); string sql = string.Format(@"SELECT id, title, description, energy FROM recipes WHERE id = '{0}'", id); using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { Clients.Client client = new Clients.Client(); using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0); x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1); x.description = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2); x.energy = reader.GetValue(3) == DBNull.Value ? 0 : Convert.ToDouble(reader.GetString(3)); x.data = JsonConvert.DeserializeObject <JsonFile>(GetAppJsonFile(x.id, lang)); } } } connection.Close(); } return(JsonConvert.SerializeObject(x, Formatting.None)); } catch (Exception e) { return(e.Message); } }
public static string GetString(this SQLiteDataReader reader, Enum columnName) { var index = reader.GetOrdinal(columnName); return(reader.IsDBNull(index) ? null : reader.GetString(index)); }
/// <summary> /// Write the loadfile data in the database to a file. /// Cannot sort descending. /// </summary> /// <param name="loadfile">Loadfile object</param> /// <param name="outputfilepath">Output path to new file, may overwrite existing file</param> /// <param name="tablename">The name of the table in our SQLite database</param> /// <param name="fieldnames">List of fields from the View, in their View order</param> /// <param name="sortfield">Optional, Name of the field on which to sort the output. /// Default is _rowid_, ie, order in which rows were loaded</param> public static void Save(LoadfileBase loadfile, string outputfilepath, string tablename, List <string> fieldnames, string Command, string sortfield = "[rowid]") { string HeaderCommaSeparated = null; fieldnames.Remove("rowid"); if (fieldnames.Count != 0) { HeaderCommaSeparated = "[" + string.Join("],[", fieldnames.ToArray()) + "]"; } // initiate the loadfile object's streamwriter if (loadfile is ConcordanceDat || loadfile is Csv) { loadfile.Save(outputfilepath, ((IDelimited)loadfile).HasHeader, fieldnames); } else { loadfile.Save(outputfilepath); } // Select statement order by the sort field and the rows that we want to show it in the grid // in the future, we may want to provide output for only selected fields // *** DO NOT use brackets when building the following SQL statement.... brackets must be added when the sortState is set on the main form. // We want to sort on >1 field in either asc or desc order, eg, "ORDER BY [Custodian-Name] DESC, [ControlNumber] ASC" *** string commandString; //If Command is null, it means save is pressed from a normal tab //Else Save is pressed from an Ad-Hoc Select tab: commandString = Command == null ? "SELECT " + HeaderCommaSeparated + " FROM [" + tablename + "] ORDER BY " + sortfield : Command; Log.ErrorLog.AddMessage("Exporting to " + outputfilepath + " using command: " + commandString); try { // build the command using (SQLiteCommand MySelectCommand = new SQLiteCommand(commandString, Db.Connect.Connection)) { using (SQLiteDataReader MyReader = MySelectCommand.ExecuteReader()) { while (MyReader.Read()) { List <string> nextRecord = new List <string>(); for (int i = 0; i < MyReader.FieldCount; i++) { nextRecord.Add(MyReader.GetString(i)); } loadfile.WriteRecord(nextRecord); } } } } catch (Exception Ex) { // log it and Log.ErrorLog.AddMessage("Error during Export operation to " + outputfilepath + Environment.NewLine + Ex.Message + Environment.NewLine + Ex.StackTrace); // tell user global::System.Windows.MessageBox.Show(Ex.Message + Environment.NewLine + Ex.StackTrace); } finally { // this call to the loadfile object's close method ensures that the streamwriter flushes and closes loadfile.Close(); } }
private void LoadStreets() { RemoveStreets(); string strFilename = $"{Tool.StartupPath}\\streets_bw.sqlite"; RectLatLng bb = this.mcMapControl.ViewArea; //TODO: Check if the area is not to big ... But what is big ? SQLiteConnectionStringBuilder csbDatabase = new SQLiteConnectionStringBuilder { DataSource = strFilename }; using (SQLiteConnection dbConnection = new SQLiteConnection(csbDatabase.ConnectionString)) { dbConnection.Open(); try { // 0 1 2 3 //const string strSelectStatement = "select highway,ref,name,way from streets_bw where highway in ('motorway','motorway_link','trunk','trunk_link','primary','secondary','primary_link','secondary_link','residential')"; const string strSelectStatement = "select highway,ref,name,way from streets_bw"; uint iCounter = 0; DateTime dtStart = DateTime.Now; using (SQLiteCommand dbSelectCommand = new SQLiteCommand(strSelectStatement, dbConnection)) { using (SQLiteDataReader dbResult = dbSelectCommand.ExecuteReader()) { while (dbResult.Read()) { Highway type = Highway.Unknown; try { type = (Highway)Enum.Parse(typeof(Highway), dbResult.GetString(0), true); } catch (Exception ex) { Debug.WriteLine(ex.Message); } string strRef = dbResult.GetStringOrNull(1); string strName = dbResult.GetStringOrNull(2); LineString way = (LineString)dbResult.GetGeometryFromWKB(3); if (bb.Contains(way.Coordinate.ToPointLatLng())) { List <PointLatLng> list = new List <PointLatLng>(way.Count); list.AddRange(way.Coordinates.Select(pos => pos.ToPointLatLng())); this.Dispatcher.Invoke(() => { PathMarker mrWay = new PathMarker(this.mcMapControl, list, type, $"{(strName.IsNotEmpty() ? strName : "Unknown")}{(strRef.IsNotEmpty() ? $" ({strRef})" : "")}") { Tag = type }; this.mcMapControl.Markers.Add(mrWay); }); iCounter++; } } } } DateTime dtStop = DateTime.Now; MB.Information("Load {0} Ways In {1}.", iCounter, (dtStop - dtStart).ToHHMMSSString()); } catch (Exception ex) { MB.Error(ex); } finally { if (dbConnection.State == ConnectionState.Open) { dbConnection.Close(); } } } }
public static void Read(this IBeatmap beatmap, SQLiteDataReader reader) { int i = 1; beatmap.TitleRoman = reader.GetString(i); i++; beatmap.ArtistRoman = reader.GetString(i); i++; beatmap.TitleUnicode = reader.GetString(i); i++; beatmap.ArtistUnicode = reader.GetString(i); i++; beatmap.Creator = reader.GetString(i); i++; beatmap.DiffName = reader.GetString(i); i++; beatmap.Mp3Name = reader.GetString(i); i++; beatmap.Md5 = reader.GetString(i); i++; beatmap.OsuFileName = reader.GetString(i); i++; beatmap.MaxBpm = reader.GetDouble(i); i++; beatmap.MinBpm = reader.GetDouble(i); i++; beatmap.Tags = reader.GetString(i); i++; beatmap.State = reader.GetByte(i); i++; beatmap.Circles = (short)reader.GetInt32(i); i++; beatmap.Sliders = (short)reader.GetInt32(i); i++; beatmap.Spinners = (short)reader.GetInt32(i); i++; beatmap.EditDate = reader.GetDateTime(i).ToUniversalTime(); i++; beatmap.ApproachRate = (float)reader.GetDouble(i); i++; beatmap.CircleSize = (float)reader.GetDouble(i); i++; beatmap.HpDrainRate = (float)reader.GetDouble(i); i++; beatmap.OverallDifficulty = (float)reader.GetDouble(i); i++; beatmap.SliderVelocity = reader.SafeGetDouble(i); i++; beatmap.DrainingTime = reader.GetInt32(i); i++; beatmap.TotalTime = reader.GetInt32(i); i++; beatmap.PreviewTime = reader.GetInt32(i); i++; beatmap.MapId = reader.GetInt32(i); i++; beatmap.MapSetId = reader.GetInt32(i); i++; beatmap.ThreadId = reader.GetInt32(i); i++; /*beatmap.MapRating =*/ reader.GetInt32(i); i++; beatmap.Offset = (short)reader.GetInt32(i); i++; beatmap.StackLeniency = (float)reader.SafeGetDouble(i); i++; beatmap.PlayMode = (PlayMode)reader.GetByte(i); i++; beatmap.Source = reader.GetString(i); i++; beatmap.AudioOffset = (short)reader.GetInt32(i); i++; beatmap.LetterBox = reader.GetString(i); i++; beatmap.Played = reader.GetBoolean(i); i++; beatmap.LastPlayed = reader.GetDateTime(i).ToUniversalTime(); i++; beatmap.IsOsz2 = reader.GetBoolean(i); i++; beatmap.Dir = reader.GetString(i); i++; beatmap.LastSync = reader.GetDateTime(i).ToUniversalTime(); i++; beatmap.DisableHitsounds = reader.GetBoolean(i); i++; beatmap.DisableSkin = reader.GetBoolean(i); i++; beatmap.DisableSb = reader.GetBoolean(i); i++; beatmap.BgDim = reader.GetInt16(i); i++; beatmap.Somestuff = reader.GetInt16(i); i++; beatmap.DeSerializeStars((byte[])reader.GetValue(i)); i++; i++; // beatmapChecksum beatmap.MainBpm = reader.SafeGetDouble(i); i++; }
private void InventoryFieldGeneration() { SQLiteConnection conn = new SQLiteConnection(DATABASE); conn.Open(); var command = conn.CreateCommand(); GameColumn.Children.Clear(); Qty.Children.Clear(); Count.Children.Clear(); Price.Children.Clear(); Label gameColLabel = new Label(); Label conName = new Label(); Label platCount = new Label(); Label priceLabel = new Label(); gameColLabel.Content = "Game"; conName.Content = "Console"; platCount.Content = "Quantity Sold"; priceLabel.Content = "Total Value"; gameColLabel.FontWeight = FontWeights.ExtraBold; conName.FontWeight = FontWeights.ExtraBold; platCount.FontWeight = FontWeights.ExtraBold; priceLabel.FontWeight = FontWeights.ExtraBold; GameColumn.Children.Add(gameColLabel); Count.Children.Add(conName); Qty.Children.Add(platCount); Price.Children.Add(priceLabel); command.CommandText = "SELECT s.game, s.plat, t.quantity, CASE WHEN s.price < 0 THEN 0" + " ELSE s.price END from transactions t " + " inner join (SELECT g.name game, b.name plat, b.price, b.game_id, b.platform_id " + " FROM games g INNER JOIN(SELECT p.name, m.price, game_id, platform_id from " + " multiplat_games m inner join platforms p on p.id = m.platform_id) b on " + " g.id = b.game_id) s on s.game_id = t.game_id and s.platform_id = t.platform_id" + " where julianday('now')-julianday(t.time) < " + timeFrame + " order by Game DESC;"; SQLiteDataReader sdr = command.ExecuteReader(); List <String> tempgameNames = new List <String>(); List <String> tempconsoleNames = new List <String>(); List <int> tempgameQuantity = new List <int>(); List <int> tempgameValue = new List <int>(); List <String> gameNames = new List <String>(); List <String> consoleNames = new List <String>(); List <int> gameQuantity = new List <int>(); List <int> gameValue = new List <int>(); while (sdr.Read()) { tempgameNames.Add(sdr.GetString(0)); tempconsoleNames.Add(sdr.GetString(1)); tempgameQuantity.Add(sdr.GetInt32(2)); tempgameValue.Add(sdr.GetInt32(3)); } sdr.Close(); int tempQty = 0; bool flag = false; for (int i = 0; i < tempgameNames.Count() - 1; i++) { if (i == tempgameNames.Count() - 2) { if (tempgameNames[i].Equals(tempgameNames[i + 1]) && tempconsoleNames[i].Equals(tempconsoleNames[i + 1])) { tempQty += tempgameQuantity[i] + tempgameQuantity[i + 1]; gameNames.Add(tempgameNames[i]); consoleNames.Add(tempconsoleNames[i]); gameQuantity.Add(tempQty); gameValue.Add((tempgameValue[i])); } else { if (flag) { gameQuantity.Add(tempQty); } else { gameQuantity.Add(tempgameQuantity[i]); } gameNames.Add(tempgameNames[i]); consoleNames.Add(tempconsoleNames[i]); gameValue.Add((tempgameValue[i])); gameNames.Add(tempgameNames[i + 1]); consoleNames.Add(tempconsoleNames[i + 1]); gameQuantity.Add(tempgameQuantity[i + 1]); gameValue.Add((tempgameValue[i + 1])); break; } } else if (tempgameNames[i].Equals(tempgameNames[i + 1]) && tempconsoleNames[i].Equals(tempconsoleNames[i + 1])) { flag = true; tempQty += tempgameQuantity[i] + tempgameQuantity[i + 1]; } else if (flag) { gameNames.Add(tempgameNames[i]); consoleNames.Add(tempconsoleNames[i]); gameQuantity.Add(tempQty); gameValue.Add((tempgameValue[i])); flag = false; tempQty = 0; } else { gameNames.Add(tempgameNames[i]); consoleNames.Add(tempconsoleNames[i]); gameQuantity.Add(tempgameQuantity[i]); gameValue.Add((tempgameValue[i])); } } int tmpQ = 0; int tmpP = 0; String tmpG; String tmpC; for (int k = 0; k < gameNames.Count() - 1; k++) { for (int i = 0; i < gameNames.Count() - 1; i++) { if (gameQuantity[i + 1] > gameQuantity[i]) { tmpQ = gameQuantity[i]; tmpP = gameValue[i]; tmpG = gameNames[i]; tmpC = consoleNames[i]; gameQuantity[i] = gameQuantity[i + 1]; gameValue[i] = gameValue[i + 1]; gameNames[i] = gameNames[i + 1]; consoleNames[i] = consoleNames[i + 1]; gameQuantity[i + 1] = tmpQ; gameValue[i + 1] = tmpP; gameNames[i + 1] = tmpG; consoleNames[i + 1] = tmpC; } } } for (int i = 0; i < gameNames.Count; i++) { Label name = new Label(); Label platform = new Label(); Label quantity = new Label(); Label price = new Label(); name.Content = gameNames[i]; platform.Content = consoleNames[i]; quantity.Content = gameQuantity[i]; price.Content = gameValue[i]; GameColumn.Children.Add(name); Count.Children.Add(platform); Qty.Children.Add(quantity); Price.Children.Add(price); } conn.Close(); }
private void Kanvas_Drop(object sender, DragEventArgs e) { Point PustioSliku = e.GetPosition(Kanvas); if (e.Data.GetDataPresent("myFormat")) { //MessageBox.Show("Dodato"); Model.Lista ls = e.Data.GetData("myFormat") as Model.Lista; Image img = new Image() { Width = 30, Height = 30 }; img.Source = new BitmapImage(new Uri(ls.Ikonica)); img.ContextMenu = this.FindResource("Slicica") as ContextMenu; img.Name = "Oznaka_" + ls.Onaka; img.ToolTip = ls.Naziv; img.MouseDown += Slika_MouseDown; img.PreviewMouseLeftButtonDown += ListaBox_PreviewMouseLeftButtonDown; img.MouseMove += Slika_MouseMove; //img.PreviewMouseRightButtonUp += Desni_Klik_slika; SQLiteConnection sqliteCon = new SQLiteConnection(dbConn); Boolean moze = true; try { sqliteCon.Open(); string prov = "select * from spomenikmapa"; SQLiteCommand coma = new SQLiteCommand(prov, sqliteCon); coma.ExecuteNonQuery(); SQLiteDataReader dre = coma.ExecuteReader(); while (dre.Read()) { double xkor = (double)dre.GetValue(3); double ykor = (double)dre.GetValue(4); if (e.GetPosition(Kanvas).X >= xkor - 10 && e.GetPosition(Kanvas).X <= xkor + 30 && e.GetPosition(Kanvas).Y >= ykor - 10 && e.GetPosition(Kanvas).Y <= ykor + 30) { moze = false; } } sqliteCon.Close(); } catch (Exception x) { MessageBox.Show(x.Message); } //Boolean moze = provera(e.GetPosition(Kanvas).X, e.GetPosition(Kanvas).Y, ls.Onaka); if (moze) { if (PustioSliku.Y >= 385) { Canvas.SetLeft(img, e.GetPosition(this.Kanvas).X); Canvas.SetTop(img, e.GetPosition(this.Kanvas).Y - 40); } else if (PustioSliku.X >= 590) { Canvas.SetLeft(img, e.GetPosition(this.Kanvas).X - 30); Canvas.SetTop(img, e.GetPosition(this.Kanvas).Y); } else { Canvas.SetLeft(img, e.GetPosition(this.Kanvas).X); Canvas.SetTop(img, e.GetPosition(this.Kanvas).Y); } this.Kanvas.Children.Add(img); Listaspom.Remove((Model.Lista)ListaBox.SelectedItem); //Ubacivanje u BAZU //SQLiteConnection sqliteCon = new SQLiteConnection(dbConn); try { if (PustioSliku.Y >= 385) { sqliteCon.Open(); string query = "insert into spomenikmapa (oznaka,naziv,ikonica,X,Y) values('" + ls.Onaka + "','" + ls.Naziv + "','" + ls.Ikonica + "','" + e.GetPosition(this.Kanvas).X + "','" + (e.GetPosition(this.Kanvas).Y - 40) + "') "; SQLiteCommand command = new SQLiteCommand(query, sqliteCon); command.ExecuteNonQuery(); string query2 = "delete from zaDodavanje where oznaka='" + ls.Onaka + "'"; SQLiteCommand command2 = new SQLiteCommand(query2, sqliteCon); command2.ExecuteNonQuery(); } else if (PustioSliku.X >= 590) { sqliteCon.Open(); string query = "insert into spomenikmapa (oznaka,naziv,ikonica,X,Y) values('" + ls.Onaka + "','" + ls.Naziv + "','" + ls.Ikonica + "','" + (e.GetPosition(this.Kanvas).X - 30) + "','" + e.GetPosition(this.Kanvas).Y + "') "; SQLiteCommand command = new SQLiteCommand(query, sqliteCon); command.ExecuteNonQuery(); string query2 = "delete from zaDodavanje where oznaka='" + ls.Onaka + "'"; SQLiteCommand command2 = new SQLiteCommand(query2, sqliteCon); command2.ExecuteNonQuery(); } else { sqliteCon.Open(); string query = "insert into spomenikmapa (oznaka,naziv,ikonica,X,Y) values('" + ls.Onaka + "','" + ls.Naziv + "','" + ls.Ikonica + "','" + e.GetPosition(this.Kanvas).X + "','" + e.GetPosition(this.Kanvas).Y + "') "; SQLiteCommand command = new SQLiteCommand(query, sqliteCon); command.ExecuteNonQuery(); string query2 = "delete from zaDodavanje where oznaka='" + ls.Onaka + "'"; SQLiteCommand command2 = new SQLiteCommand(query2, sqliteCon); command2.ExecuteNonQuery(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { MessageBox.Show("Ikonice se ne smeju preklapati"); } } if (e.Data.GetDataPresent("Slika")) { //Lista ls = e.Data.GetData("myFormat") as Lista; Image slika = e.Data.GetData("Slika") as Image; Image img = new Image() { Width = 30, Height = 30 }; img.Source = slika.Source; img.Name = slika.Name; //img.ToolTip = slika.Name; img.MouseDown += Slika_MouseDown; img.PreviewMouseLeftButtonDown += ListaBox_PreviewMouseLeftButtonDown; img.MouseMove += Slika_MouseMove; //img.PreviewMouseRightButtonUp += Desni_Klik_slika; img.ContextMenu = FindResource("Slicica") as ContextMenu; string ozna = img.Name.Substring(7); SQLiteConnection sqliteCon = new SQLiteConnection(dbConn); Boolean moze = true; try { sqliteCon.Open(); string prov = "select * from spomenikmapa"; SQLiteCommand coma = new SQLiteCommand(prov, sqliteCon); coma.ExecuteNonQuery(); SQLiteDataReader dre = coma.ExecuteReader(); while (dre.Read()) { string oz = dre.GetString(0); double xkor = (double)dre.GetValue(3); double ykor = (double)dre.GetValue(4); if (oz.Equals(ozna)) { continue; } if (e.GetPosition(Kanvas).X >= xkor - 10 && e.GetPosition(Kanvas).X <= xkor + 30 && e.GetPosition(Kanvas).Y >= ykor - 10 && e.GetPosition(Kanvas).Y <= ykor + 30) { moze = false; } } sqliteCon.Close(); } catch (Exception x) { MessageBox.Show(x.Message); } if (moze) { if (PustioSliku.Y >= 380) { Canvas.SetLeft(img, e.GetPosition(this.Kanvas).X); Canvas.SetTop(img, e.GetPosition(this.Kanvas).Y - 40); } else if (PustioSliku.X >= 590) { Canvas.SetLeft(img, e.GetPosition(this.Kanvas).X - 30); Canvas.SetTop(img, e.GetPosition(this.Kanvas).Y); } else { Canvas.SetLeft(img, e.GetPosition(this.Kanvas).X); Canvas.SetTop(img, e.GetPosition(this.Kanvas).Y); } this.Kanvas.Children.Add(img); this.Kanvas.Children.Remove(slika); //Izmena koordinaata u bazi //SQLiteConnection sqliteCon = new SQLiteConnection(dbConn); try { if (PustioSliku.Y >= 380) { sqliteCon.Open(); string query = "update spomenikmapa set X='" + e.GetPosition(this.Kanvas).X + "',Y='" + (e.GetPosition(this.Kanvas).Y - 40) + "'where oznaka='" + img.Name.Substring(7) + "'"; SQLiteCommand command = new SQLiteCommand(query, sqliteCon); command.ExecuteNonQuery(); } else if (PustioSliku.X >= 590) { sqliteCon.Open(); string query = "update spomenikmapa set X='" + (e.GetPosition(this.Kanvas).X - 30) + "',Y='" + e.GetPosition(this.Kanvas).Y + "'where oznaka='" + img.Name.Substring(7) + "'"; SQLiteCommand command = new SQLiteCommand(query, sqliteCon); command.ExecuteNonQuery(); } else { sqliteCon.Open(); string query = "update spomenikmapa set X='" + e.GetPosition(this.Kanvas).X + "',Y='" + e.GetPosition(this.Kanvas).Y + "'where oznaka='" + img.Name.Substring(7) + "'"; SQLiteCommand command = new SQLiteCommand(query, sqliteCon); command.ExecuteNonQuery(); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } else { MessageBox.Show("Ikonice se ne smeju preklapati"); } } }
public ExpressException convertExpressException(SQLiteDataReader reader) { ExpressException expressException = new ExpressException(); expressException.EOID = reader.GetInt32(0); if (!Convert.IsDBNull(reader.GetValue(1))) { expressException.EOEIID = reader.GetInt32(1); } if (!Convert.IsDBNull(reader.GetValue(2))) { expressException.EOLCID = reader.GetInt32(2); } if (!Convert.IsDBNull(reader.GetValue(3))) { expressException.EOBARCODE = reader.GetString(3); } if (!Convert.IsDBNull(reader.GetValue(4))) { expressException.EOORDERNO = reader.GetString(4); } if (!Convert.IsDBNull(reader.GetValue(5))) { expressException.EOEXPTYPE = reader.GetInt32(5); } if (!Convert.IsDBNull(reader.GetValue(6))) { expressException.EOVALIDATECODE = reader.GetInt32(6); } if (!Convert.IsDBNull(reader.GetValue(7))) { expressException.EOOVERTIMEDAY = reader.GetInt32(7); } if (!Convert.IsDBNull(reader.GetValue(8))) { expressException.EOOVERTIMECNT = reader.GetInt32(8); } if (!Convert.IsDBNull(reader.GetValue(9))) { expressException.EOOVERTIMEPRICE = reader.GetDouble(9); } if (!Convert.IsDBNull(reader.GetValue(10))) { expressException.EOOVERTIMEFEE = reader.GetDouble(10); } if (!Convert.IsDBNull(reader.GetValue(11))) { expressException.EOREMARK = reader.GetString(11); } if (!Convert.IsDBNull(reader.GetValue(12))) { expressException.TFBUZSTATUS = reader.GetInt32(12); } if (!Convert.IsDBNull(reader.GetValue(13))) { expressException.TFDELETEFLAG = reader.GetInt32(13); } if (!Convert.IsDBNull(reader.GetValue(14))) { expressException.TFCREATERID = reader.GetInt32(14); } if (!Convert.IsDBNull(reader.GetValue(15))) { expressException.TFCREATERNAME = reader.GetString(15); } if (!Convert.IsDBNull(reader.GetValue(16))) { expressException.TFCREATEDATE = reader.GetString(16); } if (!Convert.IsDBNull(reader.GetValue(17))) { expressException.TFUPDATERID = reader.GetInt32(17); } if (!Convert.IsDBNull(reader.GetValue(18))) { expressException.TFUPDATERNAME = reader.GetString(18); } if (!Convert.IsDBNull(reader.GetValue(19))) { expressException.TFUPDATEDATE = reader.GetString(19); } if (!Convert.IsDBNull(reader.GetValue(20))) { expressException.TFBACKUPFIELD1 = reader.GetDouble(20); } if (!Convert.IsDBNull(reader.GetValue(21))) { expressException.TFBACKUPFIELD2 = reader.GetString(21); } if (!Convert.IsDBNull(reader.GetValue(22))) { expressException.TFBACKUPFIELD3 = reader.GetString(22); } return(expressException); }
private List <KeyValuePair <string, string> > ReadStarSystems(string[] names) { if (names.Count() == 0) { return(null); } List <KeyValuePair <string, string> > results = new List <KeyValuePair <string, string> >(); using (var con = SimpleDbConnection()) { con.Open(); using (var cmd = new SQLiteCommand(con)) { using (var transaction = con.BeginTransaction()) { foreach (string name in names) { try { cmd.CommandText = SELECT_BY_NAME_SQL; cmd.Prepare(); cmd.Parameters.AddWithValue("@name", name); using (SQLiteDataReader rdr = cmd.ExecuteReader()) { if (rdr.Read()) { results.Add(new KeyValuePair <string, string>(name, rdr.GetString(2))); } } } catch (SQLiteException) { Logging.Warn("Problem reading data for star system '" + name + "' from database, refreshing database and re-obtaining from source."); RecoverStarSystemDB(); Instance.GetStarSystem(name); } } } } } return(results); }
public ExpressInfo convertExpressInfo(SQLiteDataReader reader) { ExpressInfo expressInfo = new ExpressInfo(); expressInfo.EIID = reader.GetInt32(0); if (!Convert.IsDBNull(reader.GetValue(1))) { expressInfo.EIORDERNO = reader.GetString(1); } if (!Convert.IsDBNull(reader.GetValue(2))) { expressInfo.ELLCMAINID = reader.GetInt32(2); } if (!Convert.IsDBNull(reader.GetValue(3))) { expressInfo.EILCID = reader.GetInt32(3); } if (!Convert.IsDBNull(reader.GetValue(4))) { expressInfo.EILCNAME = reader.GetString(4); } if (!Convert.IsDBNull(reader.GetValue(5))) { expressInfo.EISENDERID = reader.GetInt32(5); } if (!Convert.IsDBNull(reader.GetValue(6))) { expressInfo.EISTORETIME = reader.GetString(6); } if (!Convert.IsDBNull(reader.GetValue(7))) { expressInfo.EISTOREUSERPHONE = reader.GetString(7); } if (!Convert.IsDBNull(reader.GetValue(8))) { expressInfo.ELTAKEUSERTYPE = reader.GetInt32(8); } if (!Convert.IsDBNull(reader.GetValue(9))) { expressInfo.EITAKETIME = reader.GetString(9); } if (!Convert.IsDBNull(reader.GetValue(10))) { expressInfo.EITAKEIDTYPE = reader.GetInt32(10); } if (!Convert.IsDBNull(reader.GetValue(11))) { expressInfo.EITAKEIDCODE = reader.GetString(11); } if (!Convert.IsDBNull(reader.GetValue(12))) { expressInfo.EIPAYMENTMODE = reader.GetInt32(12); } if (!Convert.IsDBNull(reader.GetValue(13))) { expressInfo.EIPAYMENTMONEY = reader.GetDouble(13); } if (!Convert.IsDBNull(reader.GetValue(14))) { expressInfo.EIBARCODE = reader.GetString(14); } if (!Convert.IsDBNull(reader.GetValue(15))) { expressInfo.EIEXPTYPE = reader.GetInt32(15); } if (!Convert.IsDBNull(reader.GetValue(16))) { expressInfo.EIMAILTYPE = reader.GetInt32(16); } if (!Convert.IsDBNull(reader.GetValue(17))) { expressInfo.EIEBOXID = reader.GetInt32(17); } if (!Convert.IsDBNull(reader.GetValue(18))) { expressInfo.EIEBOXNO = reader.GetString(18); } if (!Convert.IsDBNull(reader.GetValue(19))) { expressInfo.EIEBOXABBR = reader.GetString(19); } if (!Convert.IsDBNull(reader.GetValue(20))) { expressInfo.EILATTICENO = reader.GetString(20); } if (!Convert.IsDBNull(reader.GetValue(21))) { expressInfo.EIVALIDATECODE = reader.GetInt64(21); } if (!Convert.IsDBNull(reader.GetValue(22))) { expressInfo.ELEXPSAVEMODE = reader.GetInt32(22); } if (!Convert.IsDBNull(reader.GetValue(23))) { expressInfo.ELEXPREMARK = reader.GetString(23); } if (!Convert.IsDBNull(reader.GetValue(24))) { expressInfo.ELOVERTIME = reader.GetString(24); } if (!Convert.IsDBNull(reader.GetValue(25))) { expressInfo.TFBUZSTATUS = reader.GetInt32(25); } if (!Convert.IsDBNull(reader.GetValue(26))) { expressInfo.EISENDERNAME = reader.GetString(26); } if (!Convert.IsDBNull(reader.GetValue(27))) { expressInfo.EISENDERPHONE = reader.GetString(27); } if (!Convert.IsDBNull(reader.GetValue(28))) { expressInfo.EITAKEUSERNAME = reader.GetString(28); } if (!Convert.IsDBNull(reader.GetValue(29))) { expressInfo.EITAKEUSERPHONE = reader.GetString(29); } return(expressInfo); }
private void button1_Click(object sender, EventArgs e) { try { label1.Text = ""; string pathdb = textBox6.Text; //путь к папке где располагается db string pathh = textBox1.Text; //папка от куда брать файл string newPath = textBox5.Text; //перенос в новую папку // string test= "'2103790728'";// берет из названия файла song_id var dr = new System.IO.DirectoryInfo(pathh); foreach (System.IO.FileInfo fi in dr.GetFiles("*.mp3")) { // if (!(Path.GetFileName(endPath) == textBox2.Text)) test = Path.GetFileName(pathh + @"\" + fi.Name); test = test.Substring(test.IndexOf('-') + 1); // test = test.Substring(0, test.LastIndexOf('-')); // string cs = @"URI=file:" + pathdb + @"\" + "Xiami.db"; //путь к db var con = new SQLiteConnection(cs); con.Open(); string stm = $"SELECT * FROM song_info WHERE song_id=={test}"; var cmd = new SQLiteCommand(stm, con); SQLiteDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { //Console.OutputEncoding = System.Text.Encoding.GetEncoding("GB2312"); // Console.WriteLine($"{rdr.GetString(11)} "); textBox2.Text = rdr.GetString(11); textBox3.Text = rdr.GetString(3); textBox4.Text = rdr.GetString(6); } endPath = newPath + textBox2.Text + ".mp3"; //путь к переименнованому файлу newPathsong = newPath + fi.Name; //место где сохранили наш файл if (!(File.Exists(endPath))) { fi.CopyTo(newPath + fi.Name, true); //переносим файл в нужную нам папку File.Move(newPathsong, newPath + textBox2.Text + ".mp3"); //присваиваем название нашему файлу label1.Text = "Скопировано"; } else { label1.Text = "Не удалось скопировать т.к. уже есть"; } } } catch (Exception) { label1.Text = "Не удалось скопировать"; } }
public List <ObjDecl> SearchObject(String BeginsWith, String Scope, String Object) { List <ObjDecl> Result = new List <ObjDecl>(); String _SQL; // Todo maybe its more efficient to put this into stored procedure? String _SQL2 = " from ObjectLinks inner join ObjectList as tab1 on tab1.ID==ObjectLinks.ID_ObjectList" + " inner join ObjectList as tab2 on tab2.ID==ObjectLinks.ID_ObjectListRel " + " left join ObjectDecl on ObjectDecl.ID==ObjectLinks.ID_ObjectDecl "; String _ClassType; if (Object.Equals("")) { // because output needs to be sorted, the UNION needs to be wrapped in additional SELECT for ordering _SQL = "select Col1,_ClassTyp,ClassID,Descr,Params,Returns,Start,Length From ( "; //is it a class-object //because left join ObjectDecl ClassType might be null if no Class-SEQ was imported _ClassType = ((int)ObjDecl.TClassType.tCTClass).ToString(); _SQL += "SELECT distinct tab2.Object as Col1," + _ClassType + " as _ClassTyp, tab2.ClassID,tab1.Descr, '' as Params, '' as Returns , '0' as Start, '0' as Length " + _SQL2 + "where tab1.Scope=='"; _SQL += Scope + "'" + " AND (ClassType isnull OR ClassType==" + ((int)ObjDecl.TClassType.tCTFunc).ToString() + ")" + //?? " AND tab2.Object Like('" + BeginsWith + "%') "; _SQL = _SQL + " UNION "; //is it a SEQ-function _ClassType = ((int)ObjDecl.TClassType.tCTSeq).ToString(); _SQL = _SQL + "SELECT distinct Function as Col1," + _ClassType + " as _ClassTyp, tab2.ClassID,ObjectDecl.Descr, ObjectDecl.Params, ObjectDecl.Returns, ObjectDecl.Start, ObjectDecl.Length " + _SQL2 + "where tab1.Scope=='"; _SQL = _SQL + Scope + "' AND ClassType==" + _ClassType + " AND Function Like('" + BeginsWith + "%')"; _SQL = _SQL + " UNION "; //is it a variable of basic type _ClassType = ((int)ObjDecl.TClassType.tCTType).ToString(); _SQL = _SQL + "SELECT distinct tab2.Object as Col1," + _ClassType + " as _ClassTyp, tab2.ClassID ,tab2.Descr, '' as Params, '' as Returns, '0' as Start, '0' as Length " + _SQL2 + "where tab1.Scope=='"; _SQL = _SQL + Scope + "' AND ClassType==" + _ClassType + " AND tab2.Object Like('" + BeginsWith + "%')"; _SQL = _SQL + ") order by Col1; "; } else // its a function of an object { _ClassType = ((int)ObjDecl.TClassType.tCTFunc).ToString(); _SQL = "SELECT distinct Function as Col1,ObjectDecl.ClassID,ObjectDecl.Descr, ObjectDecl.Params, ObjectDecl.Returns, ObjectDecl.Start, ObjectDecl.Length, " + _ClassType + " as _ClassTyp" + _SQL2 + "where tab1.Scope=='"; _SQL += Scope + "' AND tab2.Object=='" + Object + "' AND Function Like('" + BeginsWith + "%')" + " order by Function;"; } try { SQLiteCommand command = new SQLiteCommand(_SQL, m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { Result.Add(new ObjDecl((reader.GetString(reader.GetOrdinal("ClassID"))), (ObjDecl.TClassType)(reader.GetInt32(reader.GetOrdinal("_ClassTyp"))), reader.GetString(reader.GetOrdinal("Col1")), reader.GetString(reader.GetOrdinal("Params")), reader.GetString(reader.GetOrdinal("Returns")), reader.GetString(reader.GetOrdinal("Descr")), reader.GetInt32(reader.GetOrdinal("Start")), reader.GetInt32(reader.GetOrdinal("Length")))); } reader.Dispose(); command.Dispose(); } catch (Exception e) { HandleDBError(e); } finally { } return(Result); }
private void SetComboBox() { using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=.\" + databaseName + ";")) { conn.Open(); SQLiteCommand command = new SQLiteCommand("select teamxsection.id_teamxsection, section.id_section, name from section join teamxsection on teamxsection.id_section=section.id_section where id_team=" + teamId + " order by section.id_section;", conn); SQLiteDataReader reader = command.ExecuteReader(); int sectionBefore = -1; while (reader.Read()) { if (sectionBefore == reader.GetInt32(1)) { //je to B tým sectionsList.Add(new TeamSection(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2) + " B")); } else { //je to A tým sectionsList.Add(new TeamSection(reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2))); } sectionBefore = reader.GetInt32(1); } reader.Close(); } if (sectionsList.Count > 0) { for (int i = 0; i < sectionsList.Count; i++) { SectionList.Items.Add(sectionsList.ElementAt(i).SectionName); } SectionList.SelectedIndex = 0; SetParticipatingList(); } }
protected override DTO ConvertReaderToObject(SQLiteDataReader reader) { UserDTO result = new UserDTO(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetInt32(3)); return(result); }
/// <summary> /// Liefert alle Personen aus der DB. /// </summary> /// <returns>Eine Liste der Personen.</returns> public List <IVersorger> GetAllByVersorgertyp(EnumStammdatenTyp versorgerTyp) { var connection = new SQLiteConnection(SQL_CONNECTION_STRING); connection.Open(); var versorger = new List <IVersorger>(); var statement = new SQLiteCommand(SQL_SELECT_ALL_BY_VERSORGERTYP, connection); statement.Parameters.Add(new SQLiteParameter("@versorgertyp", (int)versorgerTyp)); SQLiteDataReader reader = statement.ExecuteReader(); while (reader.Read()) { versorger.Add(VersorgerFactory.Create(reader.GetInt32(0), (EnumStammdatenTyp)reader.GetInt32(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6))); } connection.Close(); return(versorger); }
//List Population Methods //ORDERS TABLE private void populateOrderList(ObservableCollection <Order> list) { SQLiteConnection conn = new SQLiteConnection(dbConnectionString); if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } try { String ordersQuery = "SELECT [Id],[Firstname],[Lastname],[Phone],[Email],[FoodDept],[Ordername],[Delivery],[Address],[Deliverytime],[Instructions],[CreatorID] FROM [Orders]"; SQLiteCommand connCMD = new SQLiteCommand(ordersQuery, conn); connCMD.CommandType = System.Data.CommandType.Text; SQLiteDataReader reader = connCMD.ExecuteReader(); while (reader.Read()) { int ID = reader.GetInt32(0); String Firstname = reader.GetString(1); String Lastname = reader.GetString(2); String Phone = reader.GetString(3); String Email = reader.GetString(4); String FoodDept = reader.GetString(5); String Ordername = reader.GetString(6); String Delivery = reader.GetString(7); String Address = reader.GetString(8); String Deliverytime = reader.GetString(9); String Instructions = reader.GetString(10); String CreatorID = reader.GetString(11); //insert into list list.Add(new Order(ID, Firstname, Lastname, Phone, Email, FoodDept, Ordername, Delivery, Address, Deliverytime, Instructions, CreatorID)); } conn.Close(); } catch (Exception ex) { MessageBox.Show("Having Trouble Populating List." + ex.Message); conn.Close(); } }
public static List <T> Read <T>(string requestCondition = null) { var sqlConnection = NewSqLiteConnection(); sqlConnection.Open(); SQLiteDataReader reader = null; var table = GetTableNameFor <T>(); var type = typeof(T); var command = requestCondition == null ? new SQLiteCommand($"SELECT * FROM [{table}]", sqlConnection) : new SQLiteCommand($"SELECT * FROM [{table}] WHERE {requestCondition}", sqlConnection); var list = new ArrayList(); try { reader = command.ExecuteReader(); while (reader.Read()) { if (type.IsEquivalentTo(typeof(Doctor))) { list.Add(new Doctor( reader.GetInt32(0), reader.IsDBNull(1) ? null : reader.GetString(1), reader.IsDBNull(2) ? null : reader.GetString(2), reader.IsDBNull(3) ? null : reader.GetString(3) )); } if (type.IsEquivalentTo(typeof(Patient))) { list.Add(new Patient { Id = reader.GetInt64(0), LastName = reader.IsDBNull(1) ? null : reader.GetString(1), FirstName = reader.IsDBNull(2) ? null : reader.GetString(2), SurName = reader.IsDBNull(3) ? null : reader.GetString(3), Sex = (PatientSex)Enum.ToObject(typeof(PatientSex), reader.GetInt32(4)), ParentName = reader.GetString(5), Address = reader.GetString(6), BirthDate = reader.GetDateTime(7), GestationAge = reader.GetInt32(8), BirthWeight = reader.GetInt32(9), BirthHeight = reader.GetInt32(10), BirthHeadSize = reader.GetInt32(11), BirthChestSize = reader.GetInt32(12), ApgarScale = new ApgarResult { AfterBirth = reader.GetInt32(13), AfterOneMinute = reader.GetInt32(14), AfterFiveMinute = reader.GetInt32(15) }, HasDisability = (NoYesRadioButtonResult)reader.GetInt32(16), IsNotFirstHospitalization = (HospitalizationCount)reader.GetInt32(17), HospitalizationDate = reader.GetDateTime(18), ALVDuration = reader.GetInt32(19), CPAPDuration = reader.GetInt32(20), CerebralIschemia = (CerebralIschemiaDegree)reader.GetInt32(21), IVH = new IVHModel { Degree = (IVHDegree)reader.GetInt32(22), Localization = (IVHLocalization)reader.GetInt32(23) }, Meningitis = (NoYesRadioButtonResult)reader.GetInt32(24), Encephalitis = (NoYesRadioButtonResult)reader.GetInt32(25), ConvulsiveSyndromeDuration = reader.GetInt32(26), Sepsis = (NoYesRadioButtonResult)reader.GetInt32(27), HDN = (NoYesRadioButtonResult)reader.GetInt32(28), VKDB = (NoYesRadioButtonResult)reader.GetInt32(29), Anemia = (NoYesRadioButtonResult)reader.GetInt32(30), Hyperbilirubinemia = (NoYesRadioButtonResult)reader.GetInt32(31), UNEC = (NoYesRadioButtonResult)reader.GetInt32(32), BirthDefect = reader.GetString(33), Surgery = reader.GetString(34), PatientHistory = reader.GetString(35) }); } if (type.IsEquivalentTo(typeof(Derangement))) { list.Add(new Derangement( reader.GetString(0), reader.GetString(1), reader.IsDBNull(2) ? null : reader.GetString(2), reader.IsDBNull(3) ? null : reader.GetString(3) )); } if (type.IsEquivalentTo(typeof(MedicalReport))) { list.Add(new MedicalReport( reader.GetInt32(0), reader.GetInt32(1), reader.GetDateTime(2), reader.IsDBNull(3) ? null : reader.GetString(3), reader.GetInt32(4) )); } if (type.IsEquivalentTo(typeof(MedicalReportItem))) { list.Add(new MedicalReportItem( reader.GetInt32(0), reader.GetInt32(1), reader.GetString(2), (DerangementState)reader.GetInt16(3), reader.IsDBNull(4) ? null : reader.GetString(4) )); } } } catch (Exception ex) { MessageBox.Show(ex.Message, ex.Source, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { reader.Close(); sqlConnection.Close(); } return(list.Cast <T>().ToList()); }
public string Get(string userId, string id) { try { NewMyMeals x = new NewMyMeals(); db.AddColumn(userId, db.GetDataBasePath(userId, userDataBase), db.meals, MEAL_DATA, "TEXT"); //new column in meals tbl. using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + db.GetDataBasePath(userId, userDataBase))) { connection.Open(); string sql = string.Format("SELECT id, title, description, userId, userGroupId, mealData FROM meals WHERE id = '{0}'", id); using (SQLiteCommand command = new SQLiteCommand(sql, connection)) { using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { x.id = reader.GetValue(0) == DBNull.Value ? "" : reader.GetString(0); x.title = reader.GetValue(1) == DBNull.Value ? "" : reader.GetString(1); x.description = reader.GetValue(2) == DBNull.Value ? "" : reader.GetString(2); x.userId = reader.GetValue(3) == DBNull.Value ? "" : reader.GetString(3); x.userGroupId = reader.GetValue(4) == DBNull.Value ? "" : reader.GetString(4); //x.data = JsonConvert.DeserializeObject<JsonFileMeals>(GetJsonFile(userId, id)); // OLD string data = reader.GetValue(5) == DBNull.Value ? null : reader.GetString(5); if (!string.IsNullOrWhiteSpace(data)) { x.data = JsonConvert.DeserializeObject <JsonFileMeals>(data); // new sistem: recipe saved in db } else { x.data = JsonConvert.DeserializeObject <JsonFileMeals>(GetJsonFile(userId, id)); // old sistem: recipe saved in json file } } } } } return(JsonConvert.SerializeObject(x, Formatting.None)); } catch (Exception e) { L.SendErrorLog(e, id, userId, "MyMeals", "Get"); return(JsonConvert.SerializeObject(e.Message, Formatting.None)); } }