public static List <Recipe> GetRecipesNow()
{
var res = new List <Recipe>();
using SQLiteConnection connect = new SQLiteConnection($"Data Source={DbPath}");
connect.Open();
SQLiteCommand command = new SQLiteCommand
{
Connection = connect,
CommandText =
"SELECT Recipe.* FROM Recipe LEFT JOIN Ingredient ON Recipe.RecipeID = Ingredient.RecipeID LEFT JOIN Warehouse ON Warehouse.ProductID = Ingredient.ProductID GROUP BY Recipe.RecipeID HAVING SUM(Ingredient.Amount <= Warehouse.Amount) == COUNT(Ingredient.Amount); "
};
SQLiteDataReader sqlReader = command.ExecuteReader();
while (sqlReader.Read())
{
Recipe t = new Recipe
{
Id = sqlReader.GetInt32(0),
Name = sqlReader.GetString(1),
Instruction = sqlReader.GetString(2),
Time = sqlReader.GetInt32(3),
Proteins = sqlReader.GetDouble(4),
Fats = sqlReader.GetDouble(5),
Carbohydrates = sqlReader.GetDouble(6),
Calories = sqlReader.GetDouble(7)
};
res.Add(t);
}
connect.Close();
return(res);
}
public static AtributosIngreso GetById(int id)
{
AtributosIngreso a = new AtributosIngreso();
Conexion.OpenConnection();
SQLiteCommand cmd = new SQLiteCommand("SELECT id, idFormaDePago, fechaInicial, fechaDeCierre, idMoneda, year, costo, costoEnvio, observaciones FROM ATRIBUTOSINGRESO WHERE id = @id;");
cmd.Parameters.Add(new SQLiteParameter("@id", id));
cmd.Connection = Conexion.Connection;
SQLiteDataReader obdr = cmd.ExecuteReader();
while (obdr.Read())
{
a.Id = obdr.GetInt32(0);
a.Formadepago = pFormaDePago.GetById(obdr.GetInt32(1));
a.Fechainicial = obdr.GetDateTime(2);
a.Fechacierre = obdr.GetDateTime(3);
a.Moneda = pMoneda.GetById(obdr.GetInt32(4));
a.Year = obdr.GetDateTime(5);
a.Costo = obdr.GetDouble(6);
a.Costoenvio = obdr.GetDouble(7);
a.Observaciones = obdr.GetString(8);
}
return(a);
}
public void loadDataFormDB(SQLiteConnection connection)
{
using (SQLiteCommand cmd = new SQLiteCommand(connection))
{
cmd.CommandText = "SELECT startTime, endTime, version, filename, threadCount, convertStatus, timeElapsed, tileCompleteCount, tileTotalCount, totalReceivedBytes, totalWroteBytes, netWorkSpeed, totalWroteCount FROM TimeNameMap order by version DESC";
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
current = new MBVersion()
{
start = reader.GetInt64(0),
end = reader.GetInt64(1),
version = reader.GetInt64(2),
name = reader.GetString(3),
threadCount = reader.GetInt64(4),
status = reader.GetString(5),
timeSpan = reader.GetDouble(6),
completeCount = reader.GetInt64(7),
totalCount = reader.GetInt64(8),
receivedBytes = reader.GetInt64(9),
wroteBytes = reader.GetInt64(10),
networkSpeed = reader.GetDouble(11),
wroteCounts = reader.GetInt64(12)
};
addOneVersion(current);
}
joinCircle();
}
}
public static List <Recipe> SearchRecipes(string text)
{
var res = new List <Recipe>();
using SQLiteConnection connect = new SQLiteConnection($"Data Source={DbPath}");
connect.Open();
SQLiteCommand command = new SQLiteCommand
{
Connection = connect,
CommandText = $"SELECT * FROM Recipe WHERE INSTR(LOWER(Name), LOWER('{text}')) > 0"
};
SQLiteDataReader sqlReader = command.ExecuteReader();
while (sqlReader.Read())
{
Recipe t = new Recipe()
{
Id = sqlReader.GetInt32(0),
Name = sqlReader.GetString(1),
Instruction = sqlReader.GetString(2),
Time = sqlReader.GetInt32(3),
Proteins = sqlReader.GetDouble(4),
Fats = sqlReader.GetDouble(5),
Carbohydrates = sqlReader.GetDouble(6),
Calories = sqlReader.GetDouble(7)
};
res.Add(t);
}
connect.Close();
return(res);
}
public Dictionary <string, object> ObtenerDatosPrenda(int cod)
{
Dictionary <string, object> datos = new Dictionary <string, object>();
using (SQLiteConnection conexionSQL = new SQLiteConnection(cadenaConexion))
{
using (SQLiteCommand cmdConsulta = new SQLiteCommand("SELECT * FROM Prendas WHERE PrendaId=@Id", conexionSQL))
{
conexionSQL.Open();
cmdConsulta.Parameters.AddWithValue("@Id", cod);
using (SQLiteDataReader reader = cmdConsulta.ExecuteReader(System.Data.CommandBehavior.KeyInfo))
{
if (reader.Read())
{
datos.Add("Imagen", reader.GetBlob(1, true));
datos.Add("Nombre", reader.GetString(2));
datos.Add("Marca", reader.GetString(3));
datos.Add("Stock", reader.GetInt32(4));
datos.Add("Talla", reader.GetString(5));
datos.Add("Categoria", reader.GetInt32(6));
datos.Add("Proveedor", reader.GetInt32(7));
datos.Add("PrecioCompra", reader.GetDouble(8));
datos.Add("PrecioVenta", reader.GetDouble(9));
}
reader.Close();
}
}
}
return(datos);
}
private bool trackprocess()
{
if (bikeid == -1)
{
return(false);
}
this.gMapControl1.Overlays.Clear();
var cmd = new SQLiteCommand(this.SQLiteConnection);
cmd.CommandText = "SELECT * FROM bike WHERE id=@id";
cmd.Parameters.AddWithValue("@id", bikeid);
SQLiteDataReader rdr = cmd.ExecuteReader();
double la = 0;
double lo = 0;
while (rdr.Read())
{
la = rdr.GetDouble(2);
lo = rdr.GetDouble(3);
}
GMapOverlay markers = new GMapOverlay("markers");
GMapMarker marker = new GMarkerGoogle(new PointLatLng(la, lo), GMarkerGoogleType.blue_pushpin);
markers.Markers.Add(marker);
this.gMapControl1.Overlays.Add(markers);
return(true);
}
public List <Sale> getIncomes(DateTime initDate, DateTime endDate)
{
List <Sale> incomes = new List <Sale>();
string from = ToUnixTime(initDate).ToString();
string to = ToUnixTime(endDate).ToString();
string query = Query.GetIncomes(from, to);
SQLiteCommand command;
connection.Open();
command = new SQLiteCommand(query, connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
incomes.Add(new Sale()
{
Fecha = FromUnixTime(reader.GetInt64(0)).ToShortDateString(),
Hora = FromUnixTime(reader.GetInt64(1)).ToLongTimeString(),
User = reader.GetString(2),
Product = reader.GetString(3),
Quantity = reader.GetDouble(4),
Price = reader.GetDouble(5)
});
}
reader.Close();
connection.Close();
return(incomes);
}
public void LoadMascots(List <QPeptide> Mascots)
{
iLog.Log("Loading Mascot Peptides");
iLog.RepProgress(0);
SQLiteCommand Select = new SQLiteCommand(
"Select MascotScan, MascotMZ, MascotScore, MascotRT, TheorIsoRatio, Charge, " +
"IPI, ipis, Sequence, Peptides, ModDesc, ModMass, [Case] From Mascots " +
"Order by IPI", con);
SQLiteDataReader Reader = Select.ExecuteReader();
while (Reader.Read())
{
QPeptide Pept = new QPeptide();
Pept.MascotScan = Reader.GetInt32(0);
Pept.MascotMZ = Reader.GetDouble(1);
Pept.MascotScore = Reader.GetDouble(2);
Pept.MascotRT = Reader.GetDouble(3);
Pept.TheorIsotopeRatio = Reader.GetDouble(4);
Pept.Charge = Reader.GetInt32(5);
Pept.IPI = Reader.GetString(6);
//Pept.IPIs = Reader.GetString(7);
Pept.Sequence = Reader.GetString(8);
Pept.peptides = Reader.GetInt32(9);
Pept.ModDesk = Reader.GetString(10);
Pept.ModMass = Reader.GetDouble(11);
Pept.Case = Reader.GetString(12);
Mascots.Add(Pept);
}
}
void LoadPeaks(SQLiteConnection con)
{
Peaks = new List <Peak>();
SQLiteCommand Select = new SQLiteCommand(
"Select Features.[FileID], Traces.TraceID, MeanMass, RTPeaks.Apex, RTPeaks.[Left], RTPeaks.[Right], ApexCount, RTPeaks.ApexIntensity, PeakNumber, TracePeak, Files.Mode " +
"From Features,Traces,RTPeaks,Files " +
"where [Features].[FeatureID] = [Traces].[onFeatureID] and " +
"Features.[FileID]=Files.FileIndex and " +
"[RTPeaks].[TraceID] = Traces.[TraceID] and RTPeaks.Main = 1 ", con);
SQLiteDataReader Reader = Select.ExecuteReader();
int IDs = 0;
while (Reader.Read())
{
Peak P = new Peak();
P.FileID = Reader.GetInt32(0);
P.TraceID = Reader.GetInt32(1);
P.MeanMass = Reader.GetDouble(2);
P.Apex = Reader.GetDouble(3);
P.Left = Reader.GetDouble(4);
P.Right = Reader.GetDouble(5);
P.ApexCount = Reader.GetInt32(6);
P.ApexIntensity = Reader.GetDouble(7);
P.PeakID = Reader.GetInt32(8);
P.Source = Reader.GetInt32(9) == 1?PeakOrTrace.Trace:PeakOrTrace.Peak;
P.CandID = IDs;
P.Mode = Reader.GetInt32(10);
IDs++;
Peaks.Add(P);
}
}
public List <Product> RetrieveProduct()
{
using (SQLiteConnection connection = new SQLiteConnection(connect.connectionString))
{
try
{
connection.Open();
SQLiteCommand cmd = new SQLiteCommand("SELECT * FROM product", connection);
dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
listProduct.Add(new Product( //добавление данных из БД в приложение
//Category, Name, Description, Price, Rating, Quantity
dataReader.GetInt32(dataReader.GetOrdinal("ID")),
dataReader.GetString(dataReader.GetOrdinal("category")),
dataReader.GetString(dataReader.GetOrdinal("name")),
dataReader.GetString(dataReader.GetOrdinal("description")),
dataReader.GetDouble(dataReader.GetOrdinal("price")),
dataReader.GetDouble(dataReader.GetOrdinal("rating")),
dataReader.GetInt32(dataReader.GetOrdinal("quantity"))));
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
return(listProduct);
}
public static Shop GetShop(int id, bool closeConnection)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
if (connection.State == ConnectionState.Closed)
{
return(null);
}
string SQL = string.Format("select * from shop where Id = {0}", id);
command = new SQLiteCommand(SQL, connection);
SQLiteDataReader dataReader = command.ExecuteReader();
SQL = string.Format("select * from product where IdShop = {0}", id);
command = new SQLiteCommand(SQL, connection);
SQLiteDataReader dataReaderProducts = command.ExecuteReader();
List <Product> products = new List <Product>();
while (dataReaderProducts.Read())
{
products.Add(new Product(dataReaderProducts["Name"].ToString(), dataReaderProducts.GetDouble(3)));
}
dataReader.Read();
Shop shop = new Shop(dataReader.GetInt32(0), dataReader["Name"].ToString(), dataReader["City"].ToString(), dataReader["Adress"].ToString(),
dataReader.GetDouble(4), dataReader.GetDouble(5), products);
if (closeConnection)
{
connection.Close();
}
return(shop);
}
//! Get station data for a groundstation from DataBase
/*!
* \param String name of the Station
*/
public Ground.Station getStationFromDB(string StaName)
{
if (!isConnected)
{
connectDB();
}
SQLiteCommand command = new SQLiteCommand(m_dbConnection);
Ground.Station station;
command.CommandText = String.Format("SELECT * FROM {0} WHERE name='{1}';",
Constants.StationDB, StaName);
using (SQLiteDataReader read = command.ExecuteReader())
{
while (read.Read())
{
station = new Ground.Station(
read.GetString(0),
read.GetDouble(1),
read.GetDouble(2),
read.GetDouble(3));
return(station);
}
}
return(null);
}
public static Scaling getScaling(long tag_id)
{
try
{
using (SQLiteConnection conn = SQLiteDBMS.getConnection())
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(
"SELECT * FROM Scaling WHERE tag_id =" + tag_id, conn);
List <Tag> list = new List <Tag>();
using (SQLiteDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
return(new Scaling(reader.GetString(0), reader.GetDouble(1),
reader.GetDouble(2), reader.GetDouble(3), reader.GetDouble(4)));
}
}
}
}
catch (SQLiteException ex)
{
// table not exist
if (ex.ErrorCode == 1)
{
createScalingTable();
}
}
return(null);
}
public Materia ListarPorCodigo(int codigo)
{
Materia materia = null;
using (SQLiteCommand comando = conexao.Buscar().CreateCommand())
{
comando.CommandType = System.Data.CommandType.Text;
comando.CommandText = "Select * from Materia where IDMateria = @IDMateria";
comando.Parameters.AddWithValue("@IDMateria", codigo);
using (SQLiteDataReader reader = comando.ExecuteReader())
{
if (reader.HasRows)
{
materia = new Materia();
reader.Read();
materia.IDMateria = reader.GetInt32(0);
materia.Nome = reader.GetString(1);
materia.PesoProva1 = reader.GetDouble(2);
materia.PesoProva2 = reader.GetDouble(3);
materia.PesoProva3 = reader.GetDouble(4);
}
}
}
return(materia);
}
public List <address> getAllAddress(markerType type)
{
List <address> li = new List <address>();
SQLiteCommand cmd = new SQLiteCommand(m_con);
if (type == markerType.source)
{
cmd.CommandText = "select * from send";
}
else
{
cmd.CommandText = "select * from recv";
}
SQLiteDataReader dr = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (dr.Read())
{
address one = new address();
one.name = dr.GetString(0);
one.pos.Lat = dr.GetDouble(1);
one.pos.Lng = dr.GetDouble(2);
li.Add(one);
}
return(li);
}
public Harbor GetHarborById(int id)
{
string query = "SELECT *" +
"FROM HARBOR H " +
$"WHERE H.ID = {id}";
Harbor result = new Harbor();
using (_connexion = new SQLiteConnection(_connString))
{
_connexion.Open();
using (SQLiteCommand command = _connexion.CreateCommand())
{
command.CommandText = query;
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
result.Id = id;
result.Name = reader.GetString(1);
result.Country = reader.GetString(2);
result.Latitude = reader.GetDouble(3);
result.Longitude = reader.GetDouble(4);
}
}
}
_connexion.Close();
}
return(result);
}
public List <Food> GetAllFoods(string tablename)
{
if (!PersistentDataProvider.Current.allTableNames.Contains(tablename))
{
MessageBox.Show($"Table {tablename} not found");
}
try
{
using var con = new SQLiteConnection("Data Source=" + directory + databaseName);
con.Open();
string stm = "SELECT * FROM " + tablename;
using var cmd = new SQLiteCommand(stm, con);
using SQLiteDataReader rdr = cmd.ExecuteReader();
List <Food> result = new List <Food>();
while (rdr.Read())
{
Food b = new Food(rdr.GetString(1), rdr.GetDouble(2), rdr.GetDouble(3), rdr.GetDouble(4));
b.ID = rdr.GetInt32(0);
result.Add(b);
}
return(result.OrderBy(x => x.Name).ToList());
}
catch (System.Data.SQLite.SQLiteException)
{
MessageBox.Show("Table " + tablename + " not found!");
return(null);
}
}
public static List <FlightModel> LoadFlights()
{
string SqliteCmd = "select FlightID, TakeOffSiteID, LandingSiteID, GliderID, TakeOffDateTime, FlownDistance, FlightDuration, CumulatedElevation, MaxAltitude, File, FlightType, Comment from Flight";
using var connection = new SQLiteConnection(LoadConnectionString());
connection.Open();
using var cmd = new SQLiteCommand(SqliteCmd, connection);
using SQLiteDataReader reader = cmd.ExecuteReader();
List <FlightModel> Flights = new List <FlightModel>();
while (reader.Read())
{
Flights.Add(new FlightModel(reader.GetInt32(0),
reader.GetInt32(1),
reader.GetInt32(2),
reader.GetInt32(3),
reader.GetString(4),
reader.GetDouble(5),
reader.GetDouble(6),
reader.GetDouble(7),
reader.GetDouble(8),
reader.GetString(9),
reader.GetInt32(10),
reader.GetString(11)));
}
return(Flights);
}
public List <Sale> getAllSales()
{
List <Sale> sales = new List <Sale>();
string query = Query.GET_ALL_SALES;
SQLiteCommand command;
connection.Open();
command = new SQLiteCommand(query, connection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
sales.Add(new Sale()
{
Fecha = FromUnixTime(reader.GetInt64(0)).ToShortDateString(),
Hora = FromUnixTime(reader.GetInt64(1)).ToLongTimeString(),
User = reader.GetString(2),
Product = reader.GetString(3),
Quantity = reader.GetDouble(4),
Price = reader.GetDouble(5)
});
}
reader.Close();
connection.Close();
return(sales);
}
private ScanInfo getScanInfoByFilePath(int examId, string filePath)
{
Connection.Open();
String sql = String.Format("select * from ScanResultTbl where File_Path=\'{0}\'", filePath);
SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
SQLiteDataReader rdr = cmd.ExecuteReader();
ScanInfo scanInfo = new ScanInfo();
scanInfo.examId = -1;
while (rdr.Read())
{
scanInfo.examId = examId;
scanInfo.patternIdx = rdr.GetInt32(rdr.GetOrdinal("Pattern_ID"));
scanInfo.ascans = (int)rdr.GetDouble(rdr.GetOrdinal("A_scans"));
scanInfo.bscans = (int)rdr.GetDouble(rdr.GetOrdinal("B_Scans"));
scanInfo.scan_w = rdr.GetDouble(rdr.GetOrdinal("Scan_Width"));
scanInfo.scan_h = rdr.GetDouble(rdr.GetOrdinal("Scan_Height"));
scanInfo.scan_direction = rdr.GetInt32(rdr.GetOrdinal("Scan_Direction"));
scanInfo.eye_side = rdr.GetInt32(rdr.GetOrdinal("Eye_Side"));
scanInfo.meas_time = rdr.GetDateTime(rdr.GetOrdinal("Measure_Time"));
scanInfo.file_path = rdr["File_Path"] as String;
break;
}
rdr.Close();
Connection.Close();
return(scanInfo);
}
private void LoadChannels()
{
using (SQLiteConnection connection = new SQLiteConnection(this.config.Database))
{
connection.Open();
string strainStatement = "select SensorId,ChannelNo,InitValue,OutputRangeTop,OutputRangeBottom,MeasureRangeTop,MeasureRangeBottom,Type from CVChannels where GroupNo ='" + config.DeviceId + "'";
SQLiteCommand command = new SQLiteCommand(strainStatement, connection);
using (SQLiteDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
string sensorId = reader.GetString(0);
int channelNo = reader.GetInt32(1);
double initValue = reader.GetDouble(2);
double outputRangeTop = reader.GetDouble(3);
double outputRangeBottom = reader.GetDouble(4);
double measureRangeTop = reader.GetDouble(5);
double measureRangeBottom = reader.GetDouble(6);
string type = reader.GetString(7);
CurrentVoltageChannel channel = new CurrentVoltageChannel(sensorId, channelNo, initValue, outputRangeTop, outputRangeBottom, measureRangeTop, measureRangeBottom, type);
channels.Add(channelNo, channel);
}
}
}
}
public List <ChatList> listChat()
{
new Database();
var liste = new List <ChatList>();
String query = "SELECT chat_list._id as id ,chat_list.key_remote_jid, chat_list.subject, chat_list.creation, max(messages.timestamp) as max FROM chat_list LEFT OUTER JOIN messages on messages.key_remote_jid = chat_list.key_remote_jid GROUP BY chat_list.key_remote_jid, chat_list.subject, chat_list.creation ORDER BY max(messages.timestamp) desc";
SQLiteCommand myCmd = new SQLiteCommand(query, this.myConn);
this.openConnection();
SQLiteDataReader rslt = myCmd.ExecuteReader();
if (rslt.HasRows)
{
while (rslt.Read())
{
ChatList cl = new ChatList();
cl.setKey_remote_jid(rslt["key_remote_jid"].ToString());
cl.setId(rslt.GetDouble(0));
cl.setTimestamp(rslt.GetDouble(4));
// if (rslt["creation"] != null )
// {
// cl.setCreation(rslt.GetFieldValue<double>(3)) ;
// }
cl.setSubject(rslt["subject"].ToString());
liste.Add(cl);
}
}
this.closeConnection();
return(liste);
}
public override bool GetAllRecords(out List <ViewData> list)
{
list = new List <ViewData>();
lock (syncObject)
{
if (!Open("GetAllRecords"))
{
Logger.Log("GetAllRecords: Open() failed");
return(false);
}
SQLiteCommand sqlCommand = new SQLiteCommand("SELECT * FROM Positions", connection);
string results = string.Empty;
try
{
SQLiteDataReader reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
ViewData record = new ViewData
{
id = reader.GetInt64((int)ColumnIndices.ID),
symbol = reader.GetString((int)ColumnIndices.Symbol),
buyDate = reader.GetDateTime((int)ColumnIndices.BuyDate),
buyPrice = reader.GetDecimal((int)ColumnIndices.BuyPrice),
sharesBought = reader.GetDouble((int)ColumnIndices.SharesBought),
numShares = reader.GetDouble((int)ColumnIndices.NumShares),
highestClose = reader.GetDecimal((int)ColumnIndices.HighestClose),
lastClose = reader.GetDecimal((int)ColumnIndices.LastClose),
fixedStop = reader.GetDecimal((int)ColumnIndices.FixedStopPrice),
stopPercent = reader.GetInt32((int)ColumnIndices.StopPercent),
useTrailing = reader.GetBoolean((int)ColumnIndices.UseTrailing),
useDividends = reader.GetBoolean((int)ColumnIndices.UseDividends),
dividends = reader.GetDecimal((int)ColumnIndices.Dividends),
useOptions = reader.GetBoolean((int)ColumnIndices.UseOptions),
options = reader.GetDecimal((int)ColumnIndices.OptionsSold),
lastUpdate = reader.GetDateTime((int)ColumnIndices.LastUpdate)
};
list.Add(record);
}
reader.Close();
}
catch (SQLiteException e)
{
MessageBox.Show("GetAllRecords - " + e.Message);
return(false);
}
catch (Exception ex)
{
MessageBox.Show("GetAllRecords - " + ex.Message);
return(false);
}
finally
{
connection.Close();
}
} // lock
list.Sort(CompareBySymbol);
return(true);
}
public AzusifiedCube GetLatestEuroExchangeRates()
{
if (getLatestEuroExchangeRatesCommand == null)
{
SQLiteConnection conn = GetConnectionForTable("azusa.euro_exchange_rates");
getLatestEuroExchangeRatesCommand = conn.CreateCommand();
getLatestEuroExchangeRatesCommand.CommandText = "SELECT * FROM euro_exchange_rates ORDER BY dateAdded DESC";
}
SQLiteDataReader dataReader = getLatestEuroExchangeRatesCommand.ExecuteReader();
AzusifiedCube cube = new AzusifiedCube();
if (dataReader.Read())
{
cube.CubeDate = dataReader.GetDateTime(0);
cube.DateAdded = dataReader.GetDateTime(1);
cube.USD = dataReader.GetDouble(2);
cube.JPY = dataReader.GetDouble(3);
cube.GBP = dataReader.GetDouble(4);
dataReader.Close();
return(cube);
}
else
{
dataReader.Close();
cube.CubeDate = new DateTime(2021, 4, 5);
cube.DateAdded = DateTime.Now;
cube.USD = 1.1746;
cube.JPY = 130.03;
cube.GBP = 0.85195;
return(cube);
}
}
public EditGradeCategoriesForm(int currentClass)
{
InitializeComponent();
currentClassId = currentClass;
//adds a default set of categories that user can choose from
for (int i = 0; i < 15; i++)
{
ComboBox cb = (ComboBox)this.Controls["cbCategory" + i];
cb.Items.Clear();
cb.Items.AddRange(new string[] { "Homework", "Quizzes", "Tests", "Projects", "Labs", "Midterm", "Final" });
}
int currentCount = 0;
//get a list of all the current category information for the selected class
SQLiteDataReader categories = Database.executeQuery("SELECT Type, Percentage, GradingMethod FROM GradeCategory WHERE ClassID = '" + currentClassId + "';");
while (categories.Read() == true)
{
//populate current line with retrieved information
//get category information
this.Controls["cbCategory" + currentCount].Text = categories.GetString(0);
category.Add(categories.GetString(0));
//get percentage information
this.Controls["txtPercentage" + currentCount].Text = categories.GetDouble(1).ToString();
percentage.Add(categories.GetDouble(1));
//determine what grading method was used and update row
if (categories.GetString(2).Equals("Points"))
{
RadioButton points = (RadioButton)this.Controls["gradingMethod" + currentCount + "Panel"].Controls["rbPoints" + currentCount];
points.Checked = true;
type.Add("Points");
}
else
{
RadioButton perc = (RadioButton)this.Controls["gradingMethod" + currentCount + "Panel"].Controls["rbPercentage" + currentCount];
perc.Checked = true;
type.Add("Percentage");
}
currentCount++;
//if the next line is not already visible, then add the line
if (currentCount > locationCounter)
{
lnkAddCategory_LinkClicked(null, null);
}
}
categories.Close();
//set the location of where to insert (everything before this point is a database update)
locationToInsert = currentCount;
}
public static List <Computer> FetchComputers()
{
m_dbConnection.Open();
List <Computer> listOfComputers = new List <Computer>();
string sql1 = $"SELECT ID,Price,Manufacturor,CPU,Cores,RAM,SSDorHDD,StorageCapacity,VRAM,Diagonal,Weight,BatteryCapacity,RefreshRate FROM Computer";
SQLiteCommand command1 = new SQLiteCommand(sql1, m_dbConnection);
SQLiteDataReader reader1 = command1.ExecuteReader();
int ID;
double price;
string manufacturor;
double CPU;
int cores;
int RAM;
int SSD;
int storageCapacity;
int vRAM;
double diagonal;
double weigh;
int batteryCapacity;
int refreshRate;
while (reader1.Read())
{
ID = reader1.GetInt32(0);
price = reader1.GetDouble(1);
manufacturor = reader1.GetString(2);
CPU = reader1.GetDouble(3);
cores = reader1.GetInt32(4);
RAM = reader1.GetInt32(5);
SSD = reader1.GetInt32(6);
storageCapacity = reader1.GetInt32(7);
vRAM = reader1.GetInt32(8);
diagonal = reader1.GetDouble(9);
weigh = reader1.GetDouble(10);
batteryCapacity = reader1.GetInt32(11);
refreshRate = reader1.GetInt32(12);
Console.WriteLine(manufacturor);
bool tempSSD = false;
if (SSD == 1)
{
tempSSD = true;
}
Computer singleComputer = new Computer(ID, manufacturor, price, CPU, cores, RAM, tempSSD,
storageCapacity, vRAM, diagonal, weigh, batteryCapacity, refreshRate);
listOfComputers.Add(singleComputer);
}
m_dbConnection.Close();
return(listOfComputers);
}
public List <SalesOrder> Query(int p)//查询安装订单数据
{
List <SalesOrder> orderList = new List <SalesOrder>();
SalesOrder newOrder;
try
{
SQLiteConnection con = new SQLiteConnection(@"Data Source=SQL\GREE.db");
con.Open();
string sql = "SELECT SalesOrder.totalNumber,SalesOrder.subunitNumber,SalesOrder.customerName," +
"SalesOrder.customerTel,SalesOrder.customerAddress,MachineTypeStock.machineTypeName," +
"MachineTypeStock.machineTypeNumber,MachineTypeStock.machineTypeClass," +
"MachineTypeStock.machineTypeRemarks,SalesOrder.quantity,SalesOrder.installDate," +
"SalesOrder.installman,SalesOrder.installCosts,SalesOrder.pipeLength," +
"SalesOrder.remarks,SalesOrder.remarks,SalesOrder.state FROM SalesOrder,MachineTypeStock WHERE " +
"SalesOrder.machineNumber = MachineTypeStock.machineTypeNumber " +
"LIMIT " + p + ", 1000;";
SQLiteCommand command = new SQLiteCommand(sql, con);
SQLiteDataReader sr = command.ExecuteReader();
while (sr.Read())
{
newOrder = new SalesOrder
{
TNVal = sr.GetString(0),
SNVal = sr.GetInt32(1),
CNameVal = sr.GetString(2),
CTelVal = sr.GetString(3),
CAddressVal = sr.GetString(4),
MTNameVal = sr.GetString(5),
MTNumberVal = sr.GetString(6),
MTClassVal = sr.GetString(7),
MTRemarksVal = sr.GetString(8),
QuantityVal = sr.GetInt32(9),
InstallDateVal = sr.GetString(10),
InstallmanVal = sr.GetString(11),
InstallCostsVal = sr.GetDouble(12),
PipeLengthVal = sr.GetDouble(13),
RemarksVal = sr.GetString(14),
//StateVal = sr.GetInt32(15)
};
orderList.Add(newOrder);
}
command.Dispose();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(orderList);
}
private int rentprocess()
{
//if(dataGridView1)
try
{
var cmd = new SQLiteCommand(this.SQLiteConnection);
cmd.CommandText = "SELECT * FROM rent WHERE username=@username";
cmd.Parameters.AddWithValue("@username", username);
SQLiteDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows)
{
return(0);
}
if (bikeid == -1)
{
return(-1);
}
var cmd1 = new SQLiteCommand(this.SQLiteConnection);
cmd1.CommandText = "UPDATE bike SET work=@work WHERE id=@id";
cmd1.Parameters.AddWithValue("@id", this.bikeid);
cmd1.CommandText = "SELECT * FROM bike WHERE id=@id";
cmd1.Parameters.AddWithValue("@id", bikeid);
SQLiteDataReader rdr2 = cmd1.ExecuteReader();
double la = 0;
double lo = 0;
while (rdr2.Read())
{
la = rdr2.GetDouble(1);
lo = rdr2.GetDouble(2);
}
var cmd2 = new SQLiteCommand(SQLiteConnection);
cmd2.CommandText = "INSERT INTO rent VALUES(@username,@bikeid,@Latitude,@Longitude,@time)";
cmd2.Parameters.AddWithValue("@username", this.username);
cmd2.Parameters.AddWithValue("@bikeid", bikeid);
cmd2.Parameters.AddWithValue("@Latitude", la);
cmd2.Parameters.AddWithValue("@Longitude", lo);
cmd2.Parameters.AddWithValue("@time", DateTime.Now.ToString());
cmd2.Prepare();
cmd2.ExecuteNonQuery();
var cmd4 = new SQLiteCommand(this.SQLiteConnection);
cmd4.CommandText = "UPDATE bike SET work=@work WHERE id=@id";
cmd4.Parameters.AddWithValue("@id", this.bikeid);
cmd4.Parameters.AddWithValue("@work", 1);
cmd4.ExecuteNonQuery();
}
catch
{
}
return(1);
}
internal static Scale ReadEntityRecord(SQLiteDataReader reader)
{
Scale item = new Scale();
item.ID = reader.GetInt32(0);;
item.Name = reader.IsDBNull(1) ? string.Empty : reader.GetString(1);
item.ScaleNotation = reader.IsDBNull(2) ? string.Empty : reader.GetString(2);
item.TrackWidthScale = reader.IsDBNull(3) ? 0.0d : reader.GetDouble(3);
item.TrackWidthReal = reader.IsDBNull(4) ? 0.0d : reader.GetDouble(4);
return(item);
}
public DbResult <List <Article> > getAll()
{
var result = new DbResult <List <Article> >();
try
{
_logger.Debug("ArticleRepository.getAll -> Begin");
string sql = "SELECT * FROM `articles` ORDER BY `ArtNo`";
SQLiteCommand cmd = new SQLiteCommand(sql, Connection);
SQLiteDataReader reader = cmd.ExecuteReader();
_logger.Debug("ArticleRepository.Add -> sql = " + sql);
var articles = new List <Article>();
while (reader.Read())
{
reader.GetDouble(reader.GetOrdinal("Id"));
_logger.Debug("ArticleRepository.getAll -> Id = " + reader.GetDouble(reader.GetOrdinal("Id")));
_logger.Debug("ArticleRepository.getAll -> ArtNo = " + reader.GetString(reader.GetOrdinal("ArtNo")));
_logger.Debug("ArticleRepository.getAll -> Name = " + reader.GetString(reader.GetOrdinal("Name")));
_logger.Debug("ArticleRepository.getAll -> Description = " + reader.GetString(reader.GetOrdinal("Description")));
_logger.Debug("ArticleRepository.getAll -> Price = " + reader.GetDecimal(reader.GetOrdinal("Price")));
_logger.Debug("ArticleRepository.getAll -> Quant = " + reader.GetInt32(reader.GetOrdinal("Quant")));
_logger.Debug("ArticleRepository.getAll -> DateTimeAdded = " + reader.GetDateTime(reader.GetOrdinal("DateTimeAdded")));
articles.Add(
new Article(
(long)reader.GetDouble(reader.GetOrdinal("Id")),
reader.GetString(reader.GetOrdinal("ArtNo")),
reader.GetString(reader.GetOrdinal("Name")),
reader.GetString(reader.GetOrdinal("Description")),
reader.GetDecimal(reader.GetOrdinal("Price")),
reader.GetInt32(reader.GetOrdinal("Quant")),
reader.GetDateTime(reader.GetOrdinal("DateTimeAdded")))
);
}
result.Data = articles;
result.Status = DbResultStatus.OK;
result.Msg = "Ok";
}
catch (Exception ex)
{
_logger.Error("ArticleRepository.getAll -> Exception = " + ex.Message + " " + ex.StackTrace);
result.Data = null;
result.Status = DbResultStatus.ERROR;
result.Msg = ex.Message;
}
return(result);
}
