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);
}
