private void loadPerformances(SQLiteConnection db) { string sql = "create table if not exists Performances (Id int PRIMARY KEY ASC, Title text, Description text, Date text, seatsUpper text, seatsDress text, seatsStalls text, priceUpper real, priceDress real, priceStalls real);"; SQLiteCommand cmd = new SQLiteCommand(sql, db); cmd.ExecuteNonQuery(); sql = "SELECT Id, Title, Description, Date, seatsUpper, seatsDress, seatsStalls, priceUpper, priceDress, priceStalls from Performances"; cmd = new SQLiteCommand(sql, db); try { SQLiteDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { int id = rdr.GetInt32(0); string title = rdr.GetString(1); string description = rdr.GetString(2); string dateString = rdr.GetString(3); string seatsUpperString = rdr.GetString(4); string seatsDressString = rdr.GetString(5); string seatsStallsString = rdr.GetString(6); float priceUpper = rdr.GetFloat(7); float priceDress = rdr.GetFloat(8); float priceStalls = rdr.GetFloat(9); Performance temp = new Performance(id, title, description, dateString, seatsUpperString, seatsDressString, seatsStallsString, priceUpper, priceDress, priceStalls); performances.Add(temp); } } catch (Exception ex) { throw new Exception(ex.Message); } }
internal static IEnumerable <HopsIngredientDataModel> GetHopsIngredientsForRecipe(int recipeId, SQLiteConnection connection) { using var selectIngredientsCommand = connection.CreateCommand(); selectIngredientsCommand.CommandText = "SELECT HopsIngredients.id, HopsIngredients.amount, HopsIngredients.time, HopsIngredients.type, HopsIngredients.form, Hops.name, Hops.alpha, HopsIngredients.use, Hops.notes, Hops.beta, Hops.hsi, Hops.origin, HopsIngredients.dryHopTime FROM HopsIngredients " + "JOIN HopsInRecipe ON HopsInRecipe.hopsIngredient = HopsIngredients.id AND HopsInRecipe.recipe = @recipeId " + "JOIN Hops ON Hops.id = HopsIngredients.hopsInfo"; selectIngredientsCommand.Parameters.AddWithValue("recipeId", recipeId); using SQLiteDataReader reader = selectIngredientsCommand.ExecuteReader(); while (reader.Read()) { HopsCharacteristics characteristics = new HopsCharacteristics(reader.GetFloat(6), reader.GetFloat(9), reader.GetFloat(10)); var hopsInfo = new Hops.Hops(reader.GetString(5), characteristics, reader.GetString(8), reader.GetString(11)); string dryHopTimeValue = reader[12].ToString(); int? dryHopTime = dryHopTimeValue.IsNullOrEmpty() ? null : (int?)int.Parse(dryHopTimeValue); yield return(new HopsIngredientDataModel(hopsInfo, reader.GetInt32(0)) { Amount = reader.GetFloat(1), Time = reader.GetInt32(2), FlavorType = EnumConverter.Parse <HopsFlavorType>(reader[3].ToString()), Form = EnumConverter.Parse <HopsForm>(reader[4].ToString()), Use = EnumConverter.Parse <HopsUse>(reader.GetString(7)), DryHopTime = dryHopTime }); } }
// A composite SQL command that takes data from the 'PlayerInventory' and 'Product' tables // This function specifically returns all the data needed for the player inventory menu in the game for a specific productID public string[] DataBasePlayerInventorySelectForInventoryMenu(string productId) { string[] combinedSelect = new string[4]; // Creating the conection SQLiteConnection connection = new SQLiteConnection(dbFilePath); connection.Open(); SQLiteCommand cmd = connection.CreateCommand(); // Creating the sql command and executing string combinedCommand = string.Format("SELECT Product.ProductName, Product.BasePrice, PlayerInventory.LastPriceAVG, PlayerInventory.Stock FROM PlayerInventory JOIN Product ON (PlayerInventory.ProductID = Product.ProductID) WHERE PlayerInventory.ProductID = {0} AND Product.ProductID = {0} ORDER BY Product.ProductID ASC", productId); cmd.CommandText = combinedCommand; SQLiteDataReader sqlReader = cmd.ExecuteReader(); // Reading from the reader and then inputting them into an array of strings while (sqlReader.Read()) { combinedSelect[0] = sqlReader.GetString(0); combinedSelect[1] = Convert.ToString(sqlReader.GetFloat(1)); combinedSelect[2] = Convert.ToString(sqlReader.GetFloat(2)); combinedSelect[3] = Convert.ToString(sqlReader.GetInt32(3)); } // Closing the connection and returning the array connection.Close(); return(combinedSelect); }
// PRODUCTS (Shorthand: PD) // SQL select command for the 'products' table public string[,] DataBaseProductsSelect(string colName, string whereValue) { string[,] selectionResult = new string[1, 6]; int indexCounter = 0; // Creating the connection to the database SQLiteConnection connection = new SQLiteConnection(dbFilePath); connection.Open(); SQLiteCommand cmd = connection.CreateCommand(); // Creating the command and then executing string combinedCommand = string.Format("SELECT * FROM Product WHERE {0} = {1} ORDER BY ProductID ASC", colName, whereValue); cmd.CommandText = combinedCommand; SQLiteDataReader sqlReader = cmd.ExecuteReader(); // Going through each of the rows the sqlreader sends and inputing the variables to a 2d string (Also converting them to strings) while (sqlReader.Read()) { selectionResult[indexCounter, 0] = Convert.ToString(sqlReader.GetInt32(0)); selectionResult[indexCounter, 1] = sqlReader.GetString(1); selectionResult[indexCounter, 2] = Convert.ToString(sqlReader.GetFloat(2)); selectionResult[indexCounter, 3] = Convert.ToString(sqlReader.GetFloat(3)); selectionResult[indexCounter, 4] = Convert.ToString(sqlReader.GetFloat(4)); selectionResult[indexCounter, 5] = Convert.ToString(sqlReader.GetFloat(5)); indexCounter += 1; } // Closing the connection to the database and returning the 2d array of a selected item in the products table connection.Close(); return(selectionResult); }
public void asocirajPodatkeIzBaze(string serijskiBrojParam) { String izvor = "URI=file:evidencija.db"; SQLiteConnection conn = new SQLiteConnection(izvor); conn.Open(); SQLiteCommand naredba = conn.CreateCommand(); naredba.CommandText = "SELECT * FROM ORUZJE WHERE SERIJSKI_BROJ='" + serijskiBrojParam + "';"; SQLiteDataReader reader = naredba.ExecuteReader(); while (reader.Read()) { this.serijskiBroj = reader.GetString(0); this.model = reader.GetString(1); this.proizvodjac = reader.GetString(2); this.specTip = reader.GetString(3); this.specKalibar = reader.GetString(4); this.vrijednostUHRK = reader.GetDouble(5); this.dimenMasaUG = reader.GetFloat(6); this.dimenVisinaUCM = reader.GetFloat(7); this.dimenSirinaUCM = reader.GetFloat(8); this.dimenDuljinaUCM = reader.GetFloat(9); this.dimenDuljinaCijeviUCM = reader.GetFloat(10); this.godinaProizvodnje = reader.GetInt32(11); } reader.Close(); naredba.Dispose(); conn.Close(); }
private void InitSystem() { if (Settings.System == null) { Settings.System = new HeThong(); } if (Settings.DBConnection == null || Settings.DBConnection.State != ConnectionState.Open) { return; } var cmd = new SQLiteCommand("SELECT PhiGiaoDichMua,PhiGiaoDichBan,PhiUngTruocTienBan FROM HeThong", Settings.DBConnection); SQLiteDataReader reader = cmd.ExecuteReader(); int iTmp = 0; float fTemp = 0; string sTemp = string.Empty; if (reader.HasRows) { while (reader.Read()) { fTemp = reader.GetFloat(0); Settings.System.PhiGiaoDichMua = fTemp; fTemp = reader.GetFloat(1); Settings.System.PhiGiaoDichBan = fTemp; fTemp = reader.GetFloat(2); Settings.System.PhiUngTruocTienBan = fTemp; break; } } }
private bool MakeIngredient(SQLiteDataReader reader, out Ingredient ingredient) { ingredient = new Ingredient(); Debug.WriteLine("###########!!!"); if (reader.Read()) { Debug.WriteLine("###########2222"); // ID, 이름 ingredient.Id = reader.GetInt64((int)_colunms.ID); ingredient.Name = reader.GetString((int)_colunms.Name); // 알러지 정보 갖고오기 if (reader[(int)_colunms.Allergies].GetType() != typeof(DBNull)) { BitMask <Ingredient.Allergy> bm = new BitMask <Ingredient.Allergy>(reader.GetInt32((int)_colunms.Allergies)); List <Ingredient.Allergy> allergyList; bm.ParseValues(out allergyList); ingredient.AllergyList = allergyList; } // 재고 정보 갖고 오기 string unitType = reader.GetString((int)_colunms.UnitType); if (unitType.Equals("Gram")) { Gram stock = new Gram(reader.GetFloat((int)_colunms.Stock), Gram.Units.None); ingredient.Stock = stock; } else if (unitType.Equals("Litter")) { // 리터 구현 필요 } // 발주처 정보 갖고 오기 if (reader[(int)_colunms.OrderInfoID].GetType() != typeof(DBNull)) { long id = reader.GetInt64((int)_colunms.OrderInfoID); if (id != 0) { ingredient.OrderInfoID = reader.GetInt64((int)_colunms.OrderInfoID); } } // 가격 정보 갖고 오기 if (reader[(int)_colunms.Price].GetType() != typeof(DBNull)) { ingredient.Price = reader.GetFloat((int)_colunms.Price); } return(true); } else { return(false); } }
public void selectAll() { if (allUnitFeatuers == null || allUnitFeatuers.Count == 0) { ruleManager.selectAll(); string query = "SELECT * FROM " + UNIT_TABLE_NAME + ""; SQLiteDataReader mReader = sqlManager.executeReader(query); List <UnitFeatures> unitList = new List <UnitFeatures>(); int Counter = 0; if (mReader.Read()) { do { Counter++; int id = mReader.GetInt32(0); string name = mReader.GetString(1); int powerSpawn = mReader.GetInt32(2); int powerMove = mReader.GetInt32(3); int powerAttack = mReader.GetInt32(4); float powerRegen = mReader.GetFloat(5); int airDamage = mReader.GetInt32(6); int groundDamage = mReader.GetInt32(7); float armorPricsingDamagePoint = mReader.GetFloat(8); float splashDamagePoint = mReader.GetFloat(9); int health = mReader.GetInt32(10); bool isArmored = mReader.GetBoolean(11); bool isGrounded = mReader.GetBoolean(12); int width = (int)mReader.GetFloat(15); int height = (int)mReader.GetFloat(16); int vision = mReader.GetInt32(17); int requiredLevel = mReader.GetInt32(18); int coinCost = mReader.GetInt32(19); int lastModified = mReader.GetInt32(21); int moveSpeed = mReader.GetInt32(22); string description = mReader.GetString(23); bool isVehicle = mReader.GetBoolean(24); int shotPerTurn = mReader.GetInt32(25); bool isInDeck; bool isLocked; int currentLevel; isInDeck = false; //mReader.GetBoolean(14); isLocked = false; //mReader.GetBoolean(13); currentLevel = 1; //mReader.GetInt32(20); UnitFeatures unit = new UnitFeatures(id, name, powerSpawn, powerMove, powerAttack, powerRegen, airDamage, groundDamage, armorPricsingDamagePoint, splashDamagePoint, health, isArmored, isGrounded, isLocked, isInDeck, width, height, vision, requiredLevel, coinCost, currentLevel, lastModified, moveSpeed, description, isVehicle, shotPerTurn);//,numberOfStateImages,numberOfDirections); List <UpgradeRule> upgradeRule = ruleManager.selectUnitUpgradeRule(id); unit.rules = upgradeRule; unitList.Add(unit); } while (mReader.Read()); } allUnitFeatuers = unitList; } }
StockData CreateStockData(SQLiteDataReader reader) { StockData data = new StockData(); data.Ticker = reader.GetString(reader.GetOrdinal("ticker")); data.Time = reader.GetInt64(reader.GetOrdinal("time")); data.Open = reader.GetFloat(reader.GetOrdinal("open")); data.High = reader.GetFloat(reader.GetOrdinal("high")); data.Low = reader.GetFloat(reader.GetOrdinal("Low")); data.Close = reader.GetFloat(reader.GetOrdinal("Close")); data.Vol = reader.GetFloat(reader.GetOrdinal("vol")); return(data); }
public static List <Product> ViewAllProducts() { using (SQLiteConnection connection = Helper.ConnectToDb()) { try { string query = "SELECT * FROM products"; SQLiteCommand command = new SQLiteCommand(query, connection); connection.Open(); SQLiteDataReader reader = command.ExecuteReader(); if (reader.Read()) { List <Product> allProducts = new List <Product> { new Product( reader.GetInt32(0), reader.GetString(1), reader.GetString(2), Convert.ToDouble(reader.GetFloat(3)), reader.GetInt32(4), Convert.ToDateTime(reader.GetString(5)) ) }; while (reader.Read()) { allProducts.Add(new Product( reader.GetInt32(0), reader.GetString(1), reader.GetString(2), Convert.ToDouble(reader.GetFloat(3)), reader.GetInt32(4), Convert.ToDateTime(reader.GetString(5)) ) ); } return(allProducts); } else { return(null); } } catch (Exception e) { Console.WriteLine(e); return(null); } finally { connection.Close(); } } }
public void InsertTest() { SQLiteBulkInsert target = new SQLiteBulkInsert(m_dbCon, m_testTableName); bool didThrow = false; try { target.Insert("hello"); //object.length must equal the number of parameters added } catch (Exception ex) { didThrow = true; } Assert.IsTrue(didThrow); AddParameters(target); target.CommitMax = 4; DateTime dt1 = DateTime.Now; DateTime dt2 = DateTime.Now; DateTime dt3 = DateTime.Now; DateTime dt4 = DateTime.Now; target.Insert("john", 3.45f, 10, dt1); target.Insert("paul", -0.34f, 100, dt2); target.Insert("ringo", 1000.98f, 1000, dt3); target.Insert("george", 5.0f, 10000, dt4); long count = CountRecords(); Assert.AreEqual(4, count); SQLiteDataReader reader = SelectAllRecords(); Assert.IsTrue(reader.Read()); Assert.AreEqual("john", reader.GetString(1)); Assert.AreEqual(3.45f, reader.GetFloat(2)); Assert.AreEqual(10, reader.GetInt32(3)); Assert.AreEqual(dt1, reader.GetDateTime(4)); Assert.IsTrue(reader.Read()); Assert.AreEqual("paul", reader.GetString(1)); Assert.AreEqual(-0.34f, reader.GetFloat(2)); Assert.AreEqual(100, reader.GetInt32(3)); Assert.AreEqual(dt2, reader.GetDateTime(4)); Assert.IsTrue(reader.Read()); Assert.AreEqual("ringo", reader.GetString(1)); Assert.AreEqual(1000.98f, reader.GetFloat(2)); Assert.AreEqual(1000, reader.GetInt32(3)); Assert.AreEqual(dt3, reader.GetDateTime(4)); Assert.IsTrue(reader.Read()); Assert.AreEqual("george", reader.GetString(1)); Assert.AreEqual(5.0f, reader.GetFloat(2)); Assert.AreEqual(10000, reader.GetInt32(3)); Assert.AreEqual(dt4, reader.GetDateTime(4)); Assert.IsFalse(reader.Read()); DeleteRecords(); count = CountRecords(); Assert.AreEqual(0, count); }
public static ListItem FormListItem(SQLiteDataReader data) { return(new ListItem { ItemID = data["MIFARE"].ToString(), ItemCategory = data["category"].ToString(), ItemDate = data["date"].ToString(), ItemCashlessSaldo = data.GetFloat(data.GetOrdinal("cashlessMoney")).ToString("0.00"), ItemMoneySaldo = data.GetFloat(data.GetOrdinal("realMoney")).ToString("0.00"), ItemPurchase = data.GetFloat(data.GetOrdinal("payed")).ToString("0.00"), ItemCashReload = data.GetFloat(data.GetOrdinal("enteredMoney")).ToString("0.00"), ItemAutoReload = data.GetFloat(data.GetOrdinal("autoReload")).ToString("0.00") }); }
public static List <EquipmentData> GetListByTime(long equipmentID, DateTime dt1, DateTime dt2) { //dt2 = dt2.AddDays(1); string sql = string.Format("select a.Chroma,a.Temperature,a.Humidity,a.AddTime from tb_EquipmentData{0} a where AddTime >= @dt1 and AddTime <= @dt2", equipmentID); List <EquipmentData> list = new List <EquipmentData>(); using (SQLiteConnection conn = new SQLiteConnection(SqliteHelper.ConnectionString)) { conn.Open(); SQLiteCommand cmd = new SQLiteCommand(sql, conn); cmd.Parameters.AddWithValue("@dt1", dt1); cmd.Parameters.AddWithValue("@dt2", dt2); SQLiteDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { EquipmentData eq = new EquipmentData(); eq.Chroma = reader.GetFloat(0); eq.AddTime = reader.GetDateTime(3); list.Add(eq); } } return(list); }
// PRODUCTLOCATION (Shorthand: PDLT) // A SQL select command for the 'ProductLocation' table public string[,] DataBaseProductLocationSelect(string colName, string whereValue) { string[,] selectionResult = new string[24, 5]; int indexCounter = 0; // Creating the connection to the database SQLiteConnection connection = new SQLiteConnection(dbFilePath); connection.Open(); SQLiteCommand cmd = connection.CreateCommand(); // Creating the command and then executing in this case taking in arguments on what things to search for string combinedCommand = string.Format("SELECT * FROM ProductLocation WHERE {0} = {1} ORDER BY ProductID ASC", colName, whereValue); cmd.CommandText = combinedCommand; SQLiteDataReader sqlReader = cmd.ExecuteReader(); // Going throught the sqlReader and putting any data into the 2d array of strings while (sqlReader.Read()) { selectionResult[indexCounter, 0] = Convert.ToString(sqlReader.GetInt32(0)); selectionResult[indexCounter, 1] = Convert.ToString(sqlReader.GetInt32(1)); selectionResult[indexCounter, 2] = Convert.ToString(sqlReader.GetInt32(2)); selectionResult[indexCounter, 3] = (sqlReader.GetFloat(3).ToString("n2")); selectionResult[indexCounter, 4] = Convert.ToString(sqlReader.GetValue(4)); indexCounter += 1; } // The connection is then closed and the results are returned connection.Close(); return(selectionResult); }
public void VsaviSestavine() { try { SQLiteConnection conn = new SQLiteConnection("data source = database.db"); conn.Open(); SQLiteCommand com = new SQLiteCommand(conn); com.CommandText = "SELECT ns.kolicina, s.ime AS sIme, e.ime AS eIme FROM norme n " + "INNER JOIN sestavine_norme ns ON ns.norma_id=n.id " + "INNER JOIN sestavine s ON s.id=ns.sestavina_id " + "INNER JOIN enote e ON e.id=s.enota_id " + "WHERE n.ime = '" + Ime + "';"; SQLiteDataReader reader = com.ExecuteReader(); while (reader.Read()) { Sestavina temSestavina = new Sestavina(reader.GetString(1), reader.GetFloat(0).ToString(), reader.GetString(2)); AddIng(temSestavina); } com.Dispose(); conn.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void AddData(int v) { sponsors.Clear(); SponsorsList.Items.Clear(); using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=.\" + database + ";")) { conn.Open(); SQLiteCommand command = new SQLiteCommand("select id_sponsor, name, monthly_payment, renew_bonus, min_team_strength, success_payment from sponsor;", conn); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { int success = (int)(reader.GetFloat(5) * 100); sponsors.Add(new Sponsor(reader.GetInt32(0), reader.GetString(1), reader.GetInt32(2), reader.GetInt32(3), reader.GetInt32(4), success)); } reader.Close(); } for (int i = 0; i < sponsors.Count; i++) { SponsorsList.Items.Add(sponsors.ElementAt(i).Name); } if (v != -1) { SponsorsList.SelectedIndex = v; } else if (SponsorsList.Items.Count > 0) { SponsorsList.SelectedIndex = 0; } }
public static float GetBaseSalary(int groupName) { float baseSalary = 0.00f; SQLiteConnection sQLiteConnection = new SQLiteConnection(LoadConnectionString()); if (File.Exists("testbase.db")) { sQLiteConnection.Open(); SQLiteCommand gettingBaseSalaryCommand = sQLiteConnection.CreateCommand(); gettingBaseSalaryCommand.CommandText = "SELECT salary FROM Groups WHERE id = @groupId"; gettingBaseSalaryCommand.Parameters.Add("@groupId", System.Data.DbType.String).Value = groupName; SQLiteDataReader gettingBaseSalary = gettingBaseSalaryCommand.ExecuteReader(); gettingBaseSalary.Read(); baseSalary = gettingBaseSalary.GetFloat(0); sQLiteConnection.Close(); } else { MessageBox.Show("Не найден файл базы данных", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(baseSalary); }
public static List <Employee> ReadEmployees() { List <Employee> resultingEmployees = new List <Employee>(); using (SQLiteConnection con = new SQLiteConnection($"Data Source = {PATH}; Version = {VERSION}")) { con.Open(); using (SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM Employees", con)) { using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Employee e = new Employee() { ID = reader.GetInt32(0), Name = reader.GetString(1), Age = reader.GetInt32(2), Address = reader.GetString(3), Salary = reader.GetFloat(4) }; resultingEmployees.Add(e); } } } } return(resultingEmployees); }
public static User GetUserByUsername(string username) { try { connection.Open(); using (SQLiteCommand cmd = new SQLiteCommand(connection)) { cmd.CommandText = "SELECT * FROM user WHERE username = @Username"; cmd.Parameters.AddWithValue("@Username", username); using (SQLiteDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { User tempUser = new User(); tempUser.UserID = reader.GetInt32(0); tempUser.Username = reader.GetString(1); tempUser.Password = reader.GetString(2); tempUser.Money = reader.GetFloat(3); connection.Close(); return(tempUser); } } return(null); } } catch (SQLiteException) { connection.Close(); return(null); } }
public List <float> QueryFloat(string sql) { List <float> wynik = new List <float>(); try { int i = 0; SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { while (reader.FieldCount > i) { wynik.Add(reader.GetFloat(i)); i++; } i = 0; } return(wynik); } catch { wynik.Clear(); wynik.Add(-1); return(wynik); } }
internal void cargarComida(EditarComida editarComida, int id) { try { conectar(); string sql = "select * from comida where id_comida = " + id; command = new SQLiteCommand(sql, connection); SQLiteDataReader lector = command.ExecuteReader(); while (lector.Read()) { editarComida.textBoxNombre.Text = lector.GetString(1); editarComida.checkBoxSinTACC.Checked = bool.Parse(lector.GetString(2)); editarComida.checkBoxVegetariano.Checked = bool.Parse(lector.GetString(3)); editarComida.textBoxPrecio.Text = lector.GetFloat(4).ToString(); } command.Connection.Close();; } catch (Exception e) { throw new Exception("Error: " + e); } finally { desconectar(); } }
}//获取总余额 public ArrayList getDataByWhere(String where) { ArrayList rst = new ArrayList(); using (SQLiteConnection sc = new SQLiteConnection("data source=" + dbpath)) { sc.Open(); string sql = "select * from main.money " + where; SQLiteCommand command = new SQLiteCommand(sql, sc); Console.WriteLine(sql); using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { String[] record = { "", "", "", "", "", "" }; record[0] = reader.GetInt32(0).ToString(); record[1] = reader.GetString(1); record[2] = reader.GetString(2); record[3] = reader.GetFloat(3).ToString(); record[4] = reader.GetString(4); record[5] = reader.GetString(5); rst.Add(record); } } } return(rst); }
} //获取全部数据 public String getBalance(String t) { String rst = ""; using (SQLiteConnection sc = new SQLiteConnection("data source=" + dbpath)) { sc.Open(); String sql = string.Format(@"SELECT (sum(_value)-(SELECT sum(_value) FROM money where _direct = 'out' and _type = '{0}'))FROM money where _direct = 'in' and _type = '{0}'", t); SQLiteCommand command = new SQLiteCommand(sql, sc); using (SQLiteDataReader reader = command.ExecuteReader()) { while (reader.Read()) { try { return(reader.GetFloat(0).ToString()); } catch (Exception) { } } } } return(rst); }//获取总余额
public static List <EquipmentData> GetListByTime2(string equipmentID, DateTime dt1, DateTime dt2) { //dt2 = dt2.AddDays(1); string sql = string.Format("select a.EquipmentID,a.Chroma,a.Temperature,a.Humidity,a.AddTime,b.UnitType from tb_EquipmentData a left join tb_Equipment b on a.EquipmentID=b.ID where EquipmentID in ({0}) and AddTime >='{1}' and AddTime <='{2}'", equipmentID, dt1.ToString("yyyy-MM-dd HH:mm:ss"), dt2.ToString("yyyy-MM-dd HH:mm:ss")); List <EquipmentData> list = new List <EquipmentData>(); Stopwatch watch = new Stopwatch(); watch.Start(); using (SQLiteDataReader reader = SqliteHelper.ExecuteReader(sql)) { Trace.WriteLine("ExecuteReader: " + watch.Elapsed); watch.Restart(); while (reader.Read()) { EquipmentData eq = new EquipmentData(); eq.EquipmentID = reader.GetInt32(0); eq.Chroma = reader.GetFloat(1); eq.Chroma = eq.Chroma < 10000 ? eq.Chroma : 0; eq.AddTimeStr = reader[4].ToString(); eq.UnitType = reader.GetByte(5); list.Add(eq); } Trace.WriteLine("read: " + watch.Elapsed); } watch.Stop(); return(list); }
internal static IEnumerable <FermentableIngredientDataModel> GetFermentableIngredientsForRecipe(int recipeId, SQLiteConnection connection) { using SQLiteCommand selectIngredientsCommand = connection.CreateCommand(); selectIngredientsCommand.CommandText = "SELECT FermentableIngredients.id, FermentableIngredients.amount, Fermentables.name, Fermentables.yield, Fermentables.yieldByWeight, Fermentables.color, Fermentables.origin, Fermentables.notes, Fermentables.diastaticPower, Fermentables.type, Fermentables.maltCategory, Fermentables.gravityPoint FROM FermentableIngredients " + "JOIN FermentablesInRecipe ON FermentablesInRecipe.fermentableIngredient = FermentableIngredients.id AND FermentablesInRecipe.recipe = @recipeId " + "JOIN Fermentables ON Fermentables.id = FermentableIngredients.fermentableInfo"; selectIngredientsCommand.Parameters.AddWithValue("recipeId", recipeId); using SQLiteDataReader reader = selectIngredientsCommand.ExecuteReader(); while (reader.Read()) { string yieldValue = reader[3].ToString(); float? yield = !yieldValue.IsNullOrEmpty() ? (float?)float.Parse(yieldValue) : null; string yieldByWeightValue = reader[4].ToString(); float? yieldByWeight = !yieldByWeightValue.IsNullOrEmpty() ? (float?)float.Parse(yieldByWeightValue) : null; string diastaticPowerValue = reader[8].ToString(); float? diastaticPower = !diastaticPowerValue.IsNullOrEmpty() ? (float?)float.Parse(diastaticPowerValue) : null; string maltCategoryValue = reader[10].ToString(); MaltCategory?maltCategory = !maltCategoryValue.IsNullOrEmpty() ? EnumConverter.Parse <MaltCategory>(maltCategoryValue) : null; var characteristics = new FermentableCharacteristics(yield, reader.GetFloat(5), diastaticPower) { GravityPoint = reader.GetInt32(11), Type = EnumConverter.Parse <FermentableType>(reader.GetString(9)), MaltCategory = maltCategory, YieldByWeight = yieldByWeight }; var fermentableInfo = new Fermentable(reader.GetString(2), characteristics, reader.GetString(7), reader.GetString(6)); yield return(new FermentableIngredientDataModel(fermentableInfo, reader.GetInt32(0)) { Amount = reader.GetFloat(1) }); } }
// SQL Select statement that takes the argument for a specific productid and locationid to return a specific row public string[] DataBaseProductLocationSelectSpecificProduct(string productId, string locationId) { string[] selectionResult = new string[5]; // connection to the database is made SQLiteConnection connection = new SQLiteConnection(dbFilePath); connection.Open(); SQLiteCommand cmd = connection.CreateCommand(); // Creation of the sql command and the execution of the reader string combinedCommand = string.Format("SELECT * FROM ProductLocation WHERE ProductID = {0} AND LocationID = {1} ORDER BY ProductID ASC", productId, locationId); cmd.CommandText = combinedCommand; SQLiteDataReader sqlReader = cmd.ExecuteReader(); // Reading from the reader and then putting them in the array of strings while (sqlReader.Read()) { selectionResult[0] = Convert.ToString(sqlReader.GetInt32(0)); selectionResult[1] = Convert.ToString(sqlReader.GetInt32(1)); selectionResult[2] = Convert.ToString(sqlReader.GetInt32(2)); selectionResult[3] = Convert.ToString(sqlReader.GetFloat(3)); selectionResult[4] = Convert.ToString(sqlReader.GetValue(4)); } // Closing the connection and then returning the output of the select statement connection.Close(); return(selectionResult); }
static void ExportSqlite2Csv(AcquisitionConfig config) { string fileName = config.id + "-" + config.type + ".csv"; string sqlStatement = "select Stamp,Value from " + config.table + " where SensorId='" + config.id + "' and Type='" + config.type + "' order by Stamp asc"; StreamWriter sw = new StreamWriter(fileName, true); //true表示如果a.txt文件已存在,则以追加的方式写入 SQLiteConnection conn = null; int i = 0; try { conn = new SQLiteConnection(config.connectionString); conn.Open(); SQLiteCommand command = new SQLiteCommand(sqlStatement, conn); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { string stamp = reader.GetString(0); string value = reader.GetFloat(1).ToString(); string record = stamp + "," + value; sw.WriteLine(record); } conn.Close(); sw.Close(); } catch (Exception ex) { sw.Close(); conn.Close(); Console.WriteLine(ex.Message); } }
// PRODUCTCHANGES (Shorthand: PDCH) // SQL select statement for 'ProductChanges' table, along with an argument for what exact value to search for public string[,] DataBaseProductChangesSelect(string colName, string whereValue) { string[,] selectionResult = new string[50, 6]; int indexCounter = 0; // Creating the connection to the database SQLiteConnection connection = new SQLiteConnection(dbFilePath); connection.Open(); SQLiteCommand cmd = connection.CreateCommand(); // Creating the command to be executed and then executing it through the sqlReader string combinedCommand = string.Format("SELECT * FROM ProductChanges WHERE {0} = {1} ORDER BY ChangeID ASC", colName, whereValue); cmd.CommandText = combinedCommand; SQLiteDataReader sqlReader = cmd.ExecuteReader(); // Reading from the reader and putting it into the 2d array while (sqlReader.Read()) { selectionResult[indexCounter, 0] = Convert.ToString(sqlReader.GetInt32(0)); selectionResult[indexCounter, 1] = Convert.ToString(sqlReader.GetInt32(1)); selectionResult[indexCounter, 2] = Convert.ToString(sqlReader.GetInt32(2)); selectionResult[indexCounter, 3] = Convert.ToString(sqlReader.GetFloat(3)); selectionResult[indexCounter, 4] = Convert.ToString(sqlReader.GetInt32(4)); selectionResult[indexCounter, 5] = Convert.ToString(sqlReader.GetInt32(5)); indexCounter += 1; } // Closing the connection and then returning the output of the select statement connection.Close(); return(selectionResult); }
// PLAYER (Shorthand: PL) // IMPORTANT: CITIES DONT NEED STOCK** // SQL Command to select items from the 'PlayerInventory' table public string[] DataBasePlayerInventorySelect(string colName, string whereValue) { string[] selectionResult = new string[4]; // Creating the connection to the database SQLiteConnection connection = new SQLiteConnection(dbFilePath); connection.Open(); SQLiteCommand cmd = connection.CreateCommand(); // Creating the command and then executing it string combinedCommand = string.Format("SELECT * FROM PlayerInventory WHERE {0} = {1} ORDER BY ProductID ASC", colName, whereValue); cmd.CommandText = combinedCommand; SQLiteDataReader sqlReader = cmd.ExecuteReader(); // Reading what the sql command returned and then puttting it into a array of strings while (sqlReader.Read()) { selectionResult[0] = Convert.ToString(sqlReader.GetInt32(0)); selectionResult[1] = Convert.ToString(sqlReader.GetFloat(1)); selectionResult[2] = Convert.ToString(sqlReader.GetInt32(2)); selectionResult[3] = Convert.ToString(sqlReader.GetValue(3)); } // Closing the connection to the database and returning the results connection.Close(); return(selectionResult); }
/// <summary> /// Reads a type from the database and converts it to a C# type /// </summary> /// <param name="type">The type of data we expect</param> /// <param name="dataReader">The SQLiteDataReader</param> /// <param name="col"></param> /// <returns></returns> private object SQLReadFromType(string type, SQLiteDataReader dataReader, int col) { object obj = null; switch (type) { case "System.String": obj = dataReader.GetString(col); break; case "System.Int64": obj = dataReader.GetInt64(col); break; case "System.Int32": obj = dataReader.GetInt32(col); break; case "System.Single": obj = dataReader.GetFloat(col); break; case "System.Double": obj = dataReader.GetDouble(col); break; default: break; } return(obj); }