public List <Programs> FindAll()
{
List <Programs> allPrograms = new List <Programs>();
try
{
using (connection = Database.GetConnection())
{
using (command = new MySqlCommand(Views.ALLPrograms, connection))
{
command.CommandType = CommandType.Text;
connection.Open();
using (reader = command.ExecuteReader())
{
while (reader.Read())
{
Programs Programs = new Programs
{
Id = reader.GetInt32("id"),
Department = new Departments()
{
Id = reader.GetInt32("department_id")
},
Name = reader.GetString("Name"),
CreditHour = reader.GetFloat("creditHour"),
Durations = reader.GetString("duration")
};
allPrograms.Add(Programs);
}
}
}
}
}
catch (Exception ex)
{
Logger.Log(ex);
}
return(allPrograms);
}
public void LoadCases()
{
Connection.Open();
MySqlCommand cmd = new MySqlCommand("SELECT City, PostalCode, Zone, PriceFactor FROM AreaPrices", Connection);
MySqlDataReader Reader = cmd.ExecuteReader();
while (Reader.Read())
{
Estimations _estimator = new Estimations(Connection)
{
City = Reader.GetString(0),
PostalCode = Reader.GetUInt32(1),
Zone = Reader.GetUInt32(2),
PriceFactor = Reader.GetFloat(3)
};
EstimatorList.Add(_estimator);
}
Reader.Close();
Connection.Close();
}
public static List <Servicio> Buscar(string nombre)
{
List <Servicio> _lista = new List <Servicio>();
ConexBD cnx = new ConexBD();
cnx.Conectar();
MySqlCommand _comando = new MySqlCommand(String.Format(
"SELECT * FROM Servicio where nombreServicio like '%{0}%' ", nombre), cnx.ObtenerConexion());
MySqlDataReader _reader = _comando.ExecuteReader();
while (_reader.Read())
{
Servicio servicio = new Servicio();
servicio.idServicio = _reader.GetInt32(0);
servicio.NombreServicio = _reader.GetString(1);
servicio.precioServicio = _reader.GetFloat(2);
_lista.Add(servicio);
}
_comando.Connection.Close();
cnx.cerrarConexion();
return(_lista);
}
private List <Insulin> ReadInsulinsFromReader(MySqlDataReader insulinsReader)
{
List <Insulin> insulins = new List <Insulin>();
if (insulinsReader.HasRows)
{
while (insulinsReader.Read())
{
insulins.Add(new Insulin(insulinsReader.GetInt32("_id"),
HelperMethods.DateTimeStringToTimestamp(insulinsReader.GetString("timestamp")),
insulinsReader.GetFloat("value"),
insulinsReader.GetInt16("dayDosage") == 1
));
}
}
else
{
LOG.Info("No rows found.");
}
return(insulins);
}
internal float getMaxAverageTasksPerListOutOfAllProjects()
{
float maxAverageTasksPerListOutOfAllProjects = 0;
mySqlConnection.Open();
MySqlCommand cmd = mySqlConnection.CreateCommand();
cmd.CommandText =
"SELECT MAX(subQuery.averageTasksOnList) " +
"FROM (SELECT AVG(subSubQuery.numTasksOnList) as averageTasksOnList " +
"FROM (SELECT COUNT(*) as numTasksOnList, projectId " +
"FROM Tasks GROUP BY listId, projectId) as subSubQuery " +
"GROUP BY projectId) as subQuery";
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
maxAverageTasksPerListOutOfAllProjects = reader.GetFloat(0);
}
mySqlConnection.Close();
return(maxAverageTasksPerListOutOfAllProjects);
}
// Вивести СЛУЖБИ ДОСТАВКИ
public static List <Courier> getCouriers()
{
List <Courier> list = new List <Courier>();
try
{
MySqlCommand command = new MySqlCommand("SELECT * FROM fruitsvegetables.couriers", connect);
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Courier x = new Courier(reader.GetInt32(0), reader.GetString(1),
reader.GetString(2), reader.GetFloat(3));
list.Add(x);
}
reader.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(ex.Message);
}
return(list);
}
public List <ViewConsultaFliperama> ListarVendasFliperama()
{
string query = "select * from vw_consulta_fliperama";
ProjetoDataBase database = new ProjetoDataBase();
MySqlDataReader reader = database.ExecuteSelect(query);
List <ViewConsultaFliperama> itens = new List <ViewConsultaFliperama>();
while (reader.Read())
{
ViewConsultaFliperama flip = new ViewConsultaFliperama();
flip.Cpf = reader.GetString("ds_cpf");
flip.ValorUnitario = reader.GetFloat("vl_preco");
flip.DataDaCompra = reader.GetDateTime("dt_compra");
flip.QuantidadeDeFixas = reader.GetInt32("nr_fixas");
itens.Add(flip);
}
reader.Close();
return(itens);
}
public ActionResult Index(string data1, string data2)
{
MySqlConnection connection = new MySqlConnection(connectionString);
List <StockPrice> data = new List <StockPrice>();
connection.Open();
MySqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM prices WHERE date BETWEEN " + "'" + data1 + "'" + " AND " + "'" + data2 + "'";
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
StockPrice one = new StockPrice {
date = reader.GetDateTime(0), price = reader.GetFloat(1)
};
data.Add(one);
}
reader.Close();
connection.Close();
ArrayList dataChart = new ArrayList();
ArrayList header = new ArrayList {
"data", "wartość"
};
dataChart.Add(header);
foreach (StockPrice s in data)
{
ArrayList elem = new ArrayList {
s.date.ToString("yyyy/MM/dd"), s.price
};
dataChart.Add(elem);
}
string dataStr = JsonConvert.SerializeObject(dataChart, Formatting.None);
ViewBag.Data = new HtmlString(dataStr);
return(View(data));
}
private T fillReadedObject(MySqlDataReader reader)
{
T newObj = Activator.CreateInstance <T>();
for (int i = 0; i < properties.Count; i++)
{
string typeName = properties[i].PropertyType.ToString();
if (typeName.Contains("Int"))
{
properties[i].SetValue(newObj, reader.GetInt32(i));
}
else if (typeName.Contains("Double"))
{
properties[i].SetValue(newObj, reader.GetDouble(i));
}
else if (typeName.Contains("Decimal"))
{
properties[i].SetValue(newObj, reader.GetDecimal(i));
}
else if (typeName.Contains("Float"))
{
properties[i].SetValue(newObj, reader.GetFloat(i));
}
else if (typeName.Contains("String"))
{
properties[i].SetValue(newObj, reader.GetString(i));
}
else if (typeName.Contains("Boolean"))
{
properties[i].SetValue(newObj, reader.GetBoolean(i));
}
else if (typeName.Contains("DateTime"))
{
properties[i].SetValue(newObj, reader.GetDateTime(i));
}
}
return(newObj);
}
public static void getRabbitRodK(MySqlConnection sql, ref RabbitGen rabbit)
{
if (rabbit.Sex == Rabbit.SexType.FEMALE)
{
MySqlCommand cmd = new MySqlCommand(String.Format(@"SELECT
COALESCE((sum(f_children)-sum(f_killed)+sum(f_added))/(sum(f_children)+sum(f_added)),0) k
FROM f***s
WHERE f_rabid={0:d};", rabbit.ID), sql);
MySqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
rabbit.RodK = rd.GetFloat("k");
}
rd.Close();
}
if (rabbit.Sex == Rabbit.SexType.MALE)
{
MySqlCommand cmd = new MySqlCommand(String.Format(@"SELECT
(SELECT COUNT(f_state) FROM f***s WHERE f_partner={0:d} AND f_state='okrol' AND f_times=1) o,
(SELECT COUNT(f_state) FROM f***s WHERE f_partner={0:d} AND f_state='proholost' AND f_times=1) p;" ,
rabbit.ID), sql
);
MySqlDataReader rd = cmd.ExecuteReader();
if (rd.Read())
{
float o = rd.GetFloat("o");
float p = rd.GetFloat("p");
if (p + 0 == 0)
{
rabbit.RodK = 0;
}
else
{
rabbit.RodK = o / (o + p);
}
}
rd.Close();
}
}
public List <delivery> getDeliveryList()
{
List <delivery> delivList = new List <delivery>();
delivery deliv = new delivery();
MySqlConnection conn = null;
DataTable dt = new DataTable();
MySqlDataAdapter sda = new MySqlDataAdapter();
using (conn = new MySqlConnection(db.getConnString()))
{
conn.Open();
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select location as 'Delivery Area', price as 'Initial Charge' from deliveryrates order by 'Delivery Area'; ";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
deliv = new delivery();
deliv.location = reader.GetString(0);
deliv.price = reader.GetFloat(1);
deliv.priceStr = String.Concat("Php ", (string.Format("{0:N2}", deliv.price)));
delivList.Add(deliv);
}
if (!reader.HasRows)
{
deliv = null;
}
}
}
}
return(delivList);
}
public List <Bedroom> ChargerAllBedroomToClean()
{
List <Bedroom> list = new List <Bedroom>();
if (OpenConnection() == false)
{
return(list);
}
string req = "UPDATE bedroom LEFT JOIN link_reservationbedroomoptions ON bedroom.id = link_reservationbedroomoptions.id_Bedroom LEFT JOIN reservation ON link_reservationbedroomoptions.id_Reservation = reservation.id SET bedroom.state = 'Non nettoyé' WHERE(TO_DAYS(now()) - TO_DAYS(bedroom.date_last_clean)) > 3 OR reservation.dateEnd = NOW(); " +
"SELECT bedroom.id, bedroom.number, bedroom.state, bedroom.date_last_clean, typebedroom.id AS typeId, typebedroom.name, typebedroom.price FROM bedroom JOIN typebedroom ON bedroom.id_TypeBedroom = typebedroom.id WHERE bedroom.state = 'Non nettoyé' ;";
MySqlCommand mySqlCommand = new MySqlCommand(req, mySqlConnection);
MySqlDataReader reader2 = mySqlCommand.ExecuteReader();
while (reader2.Read())
{
Bedroom bedroom = new Bedroom
{
Id = reader2.GetInt32("id"),
Number = reader2.GetInt32("number"),
State = reader2.GetString("state"),
DateLastClean = reader2.GetDateTime("date_last_clean"),
TypeBedroom = new TypeBedroom
{
Id = reader2.GetInt32("typeId"),
Name = reader2.GetString("name"),
Price = reader2.GetFloat("price")
}
};
list.Add(bedroom);
}
CloseConnection();
return(list);
}
public static List <Country> SortByAmerica()
{
List <Country> allCountries = new List <Country> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = @"SELECT * FROM country WHERE code = 'USA';";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
string countryName = rdr.GetString(1);
string countryCode = rdr.GetString(0);
int countryPopulation = rdr.GetInt32(6);
int countryIndependenceYear = 0;
if (!rdr.IsDBNull(5))
{
countryIndependenceYear = rdr.GetInt32(5);
}
float countryLifeExpectancy = 0;
if (!rdr.IsDBNull(7))
{
countryLifeExpectancy = rdr.GetFloat(7);
}
string countryFormOfGov = rdr.GetString(11);
Country newCountry = new Country(countryName, countryCode, countryPopulation, countryIndependenceYear, countryLifeExpectancy, countryFormOfGov);
allCountries.Add(newCountry);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allCountries);
}
public Goods GetGoods(int goods_id)
{
string sql = "select * from goods join sell_goods where goods_id=@goods_id and goods_id=sell_goods_id";
var result = new Goods();
using (MySqlConnection conn = new MySqlConnection(connectionstring))
{
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.Add(new MySqlParameter("@goods_id", goods_id));
try
{
conn.Open();
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
result = new Goods()
{
goods_id = goods_id,
goods_name = reader.GetString("goods_name"),
goods_price = reader.GetFloat("goods_price"),
goods_details = reader.GetString("goods_detail"),
goods_img_path = reader.GetString("goods_img_path"),
goods_stock = reader.GetInt32("sell_stock"),
goods_volume = reader.GetInt32("sell_volume"),
seller_phone = reader.GetString("seller_phone"),
goods_tag = reader.GetString("goods_tag"),
};
}
reader.Close();
}
catch { }
finally
{
conn.Close();
}
}
return(result);
}
internal List <Apuesta> RetrieveById_mercadoandEmail_Usuario(string user, int id)
{
CultureInfo culInfo = new System.Globalization.CultureInfo("es-ES");
culInfo.NumberFormat.NumberDecimalSeparator = ".";
culInfo.NumberFormat.CurrencyDecimalSeparator = ".";
culInfo.NumberFormat.PercentDecimalSeparator = ".";
culInfo.NumberFormat.CurrencyDecimalSeparator = ".";
System.Threading.Thread.CurrentThread.CurrentCulture = culInfo;
MySqlConnection con = Connect();
MySqlCommand command = con.CreateCommand();
command.CommandText = "SELECT mercados.Over_Under,apuestas.Tipo,apuestas.Cuota,apuestas.Dinero_apostado FROM apuestas,mercados WHERE Email_Usuario = '@user' AND ID_Mercado = '@id'";
command.Parameters.AddWithValue("@id", id);
command.Parameters.AddWithValue("@user", user);
try
{
con.Open();
MySqlDataReader res = command.ExecuteReader();
Apuesta a = null;
List <Apuesta> apuestas = new List <Apuesta>();
while (res.Read())
{
Debug.WriteLine("recuperamos2: " + res.GetInt32(0) + " " + res.GetString(1)
+ " " + res.GetInt32(2) + " " + res.GetString(3) + " " +
res.GetFloat(4) + " " + res.GetInt32(5) + " " + res.GetDateTime(6));
a = new Apuesta(res.GetInt32(0), res.GetString(1), res.GetInt32(2), res.GetString(3), res.GetFloat(4), res.GetInt32(5), res.GetDateTime(6));
apuestas.Add(a);
}
con.Close();
return(apuestas);
}
catch (MySqlException e)
{
Debug.WriteLine("se ha producido un error de conexión");
return(null);
}
}
public static CharacterModel LoadCharacterByName(string characterName, string playername, string ip)
{
CharacterModel character = new CharacterModel();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand command = connection.CreateCommand();
// Обновление последнего IP.
command.CommandText = "UPDATE accounts SET lastIp = @lastIp WHERE login = @login LIMIT 1";
command.Parameters.AddWithValue("@lastIp", ip);
command.Parameters.AddWithValue("@login", playername);
command.ExecuteNonQuery();
command.CommandText = "SELECT * FROM characterlist WHERE characterName = @characterName LIMIT 1";
command.Parameters.AddWithValue("@characterName", characterName);
using (MySqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
character.id = reader.GetInt32("id");
character.characterName = reader.GetString("characterName");
character.sex = reader.GetInt32("sex");
character.posX = reader.GetFloat("posX");
character.posY = reader.GetFloat("posY");
character.posZ = reader.GetFloat("posZ");
character.rotation = reader.GetFloat("rotation");
character.adminRank = reader.GetInt32("adminRank");
character.money = reader.GetInt32("money");
character.bank = reader.GetInt32("bank");
character.health = reader.GetInt32("health");
character.armor = reader.GetInt32("armor");
character.played = reader.GetInt32("played");
character.xp = reader.GetInt32("xp");
character.lvl = reader.GetInt32("lvl");
character.hunger = reader.GetFloat("hunger");
character.thirst = reader.GetFloat("thirst");
}
}
}
return(character);
}
//Function: void getAllQuotes(name)
//Purpose: retrieves all of the quotes from the db via customer name
private void getAllQuotes(string name)
{
quoteList.Clear();
string query = "SELECT * FROM quote";
//connect to the db and retrieve the data
if (this.connect())
{
//execute the query
MySqlCommand cmd = new MySqlCommand(query, connection);
MySqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
//create new quotes using the data read
if ((dr["custName"] + "").Contains(name) && dr.GetFloat(6) != 1)
{
quoteList.Add(new Quote(dr.GetInt32(9), dr["name"] + "", dr["custName"] + "", dr["email"] + "", dr.GetInt32(3), dr.GetFloat(4), dr.GetFloat(5), dr.GetFloat(6)));
}
}
dr.Close();
}
this.stopConnection();
}
public static IList <DAOMovimientos> Buscar(MySqlConnection con, string nombre)
{
List <DAOMovimientos> lista = new List <DAOMovimientos>();
MySqlCommand comando = new MySqlCommand(string.Format("SELECT id_movimiento,nombre_movimiento,fecha_movimiento,cantidad,cliente,proveedor,total from movimientos where nombre_movimiento LIKE ('%{0}%')", nombre), con);
MySqlDataReader reader = comando.ExecuteReader();
while (reader.Read())
{
DAOMovimientos Movimiento = new DAOMovimientos();
Movimiento.id = reader.GetInt32(0);
Movimiento.nombre_movimiento = reader.GetString(1);
Movimiento.fecha = reader.GetString(2);
Movimiento.cantidad = reader.GetInt32(3);
Movimiento.cliente = reader.GetString(4);
Movimiento.proveedor = reader.GetString(5);
Movimiento.total = reader.GetFloat(6);
lista.Add(Movimiento);
}
return(lista);
}
public static float GetFloat(string MySqlCommand, string Column)
{
float tmp = 0;
MySqlDataReader DataReader = null;
MySqlCommand Command = new MySqlCommand(MySqlCommand, connection);
try
{
DataReader = Command.ExecuteReader();
while (DataReader.Read())
{
tmp = DataReader.GetFloat(Column);
}
}
finally
{
if (DataReader != null)
{
DataReader.Close();
}
}
return(tmp);
}
public User GetUserByEmail(string email)
{
User user = new User();
sql = "SELECT * FROM user WHERE email = '" + email + "'";
connection.Open();
command = new MySqlCommand(sql, connection);
command.ExecuteNonQuery();
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
user.Id = reader.GetInt32(0);
user.Firstname = reader.GetString(1);
user.MembershipTypeId = reader.GetInt32(6);
user.Password = reader.GetString(7);
user.Email = reader.GetString(3);
user.Height = reader.GetFloat(5);
}
connection.Close();
return(user);
}
public float GetAllIncome(string seller_phone)
{
float res = 0;
string sql1 = "select sum(plist_goods_total_price) as sum from purchaselists where plist_seller_phone=@phone";
using (MySqlConnection conn = new MySqlConnection(connectionstring))
{
MySqlCommand cmd1 = new MySqlCommand(sql1, conn);
conn.Open();
cmd1.Parameters.Add(new MySqlParameter("@phone", seller_phone));
MySqlDataReader reader = cmd1.ExecuteReader();
while (reader.Read())
{
res = reader.GetFloat("sum");
}
conn.Close();
}
return(res);
}
public Produto Detalhes(int id)
{
string sql = "SELECT * FROM Produto WHERE idProduto = @id";
conexao.Open();
MySqlCommand command = new MySqlCommand(sql, conexao);
command.Parameters.AddWithValue("id", id);
MySqlDataReader reader = command.ExecuteReader();
Produto produto = new Produto();
reader.Read();
produto.id = reader.GetInt32("idProduto");
if (!reader.IsDBNull(reader.GetOrdinal("nome")))
{
produto.nome = reader.GetString("nome");
}
if (!reader.IsDBNull(reader.GetOrdinal("saborPrincipal")))
{
produto.saborPrincipal = reader.GetString("saborPrincipal");
}
if (!reader.IsDBNull(reader.GetOrdinal("descricao")))
{
produto.descricao = reader.GetString("descricao");
}
if (!reader.IsDBNull(reader.GetOrdinal("preco")))
{
produto.preco = reader.GetFloat("preco");
}
if (!reader.IsDBNull(reader.GetOrdinal("dataCadastro")))
{
produto.dataCadastro = reader.GetDateTime("dataCadastro");
}
reader.Close();
conexao.Close();
return(produto);
}
/**
* Método: ConsultarDatosItem
* ---------------------------------------------------------------------------
* Este metodo realiza la consulta de los datos tales como cupo y monto segun
* el item pasados por parametro
* @param idItem: Identificador de referencia en la base de datos
* @param cupo: cupo del juego a consultar
* @param monto: monto a consultar
*/
public int ConsultarDatosItem(int idItem, ref int cupo, ref float monto)
{
try
{
log.Debug("Método: " + MethodBase.GetCurrentMethod().Name);
Conectar();
string query = "SELECT CUPO, MONTO FROM TB_ITEM WHERE ID_ITEM=" + idItem;
int result1 = 0, result = 0;
float result2 = 0;
command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
while (reader.Read())
{
result1 = reader.GetInt32(0);
result2 = reader.GetFloat(1);
}
if (result1 != 0 && result2 != 0)
{
cupo = result1;
monto = result2;
result = 1;
}
return(result);
}
catch (Exception e)
{
throw e;
}
finally
{
Desconectar();
}
}
public static bool SelectAllMapObjects(int map, out List <DB_WorldObject> data)
{
var locked = false;
data = null;
if (!IsConnected)
{
return(false);
}
try
{
using (MySqlCommand _cmd = _connection.CreateCommand())
{
_cmd.CommandText = $"SELECT * FROM {tb_03_01} WHERE map = {map};";
Monitor.Enter(_connection, ref locked);
using (MySqlDataReader _result = _cmd.ExecuteReader())
{
if (_result.HasRows)
{
data = new List <DB_WorldObject>();
while (_result.Read())
{
data.Add(new DB_WorldObject(_result.GetInt32(0), _result.GetUInt16(1), _result.GetInt32(2), _result.GetByte(3), _result.GetByte(4), _result.GetVector3(5),
_result.GetVector3(8), _result.GetFloat(11), _result.GetInt32(12), _result.GetInt32(13), _result.GetInt32(14)));
}
}
}
}
return(true);
}
catch { return(false); }
finally { if (locked)
{
Monitor.Exit(_connection);
}
}
}
public List <Computer> GetAllComputers()
{
List <Computer> computers = new List <Computer>();
string query = "SELECT * FROM computers";
if (OpenConnection())
{
using (MySqlCommand command = new MySqlCommand(query, connection))
{
using (MySqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
if (reader.GetInt32("id") == -1)
{
continue;
}
computers.Add(
new Computer
{
ID = reader.GetInt32("id"),
Name = reader.GetString("computer_name"),
Price = reader.GetFloat("price"),
SerialNumber = reader.GetString("serial_number"),
RoomID = reader.GetInt32("room_id"),
StatusID = reader.GetInt32("status"),
TypeID = reader.GetInt32("type"),
RoomValue = RoomSql.RoomsKeyValues[reader.GetInt32("room_id")]
});
}
}
}
CloseConnection();
}
return(computers);
}
// Methode retournant l'historique de transaction d'un compte en banque | utilisation de jointure entre 5 tables differents
public static bool loadAccountHistory(int compte)
{
histories = new List <ClientHistory>();
historyResult = new List <string>();
query = "SELECT client.nom, client.prenom, client.nif, transaction.libele_transaction, historique.montant, historique.date_transaction" +
" FROM client, compte_client, historique, transaction, compte" +
" WHERE (compte_client.no_compte = " + compte + ") AND (historique.no_compte = " + compte +
") AND (transaction.type_transaction = historique.type_transaction) " +
" AND (client.nif = compte_client.nif)";
try
{
connection = new MySqlConnection(@"Data Source=localhost;port=3309;Initial Catalog=mybank;User Id=root;password=''");
connection.Open();
command = new MySqlCommand(query, connection);
reader = command.ExecuteReader();
while (reader.HasRows && reader.Read())
{
ClientHistory ch = new ClientHistory();
ch.Nom = reader.GetString(reader.GetOrdinal("nom"));
ch.Prenom = reader.GetString(reader.GetOrdinal("prenom"));
ch.Montant = reader.GetFloat(reader.GetOrdinal("montant"));
ch.Nif = reader.GetInt32(reader.GetOrdinal("nif"));
ch.Libele = reader.GetString(reader.GetOrdinal("libele_transaction"));
ch.Date = reader.GetDateTime(reader.GetOrdinal("date_transaction"));
histories.Add(ch);
}
connection.Close();
return(true);
}
catch (MySqlException e)
{
string error2 = e.Message;
connection.Close();
return(false);
}
}
// GET Payment Call
public Payment GetPayment(long id)
{
Payment Payment = new Payment();
MySqlDataReader mySQLReader = null;
string slqCommandString = "SELECT * FROM Payments, Payments_fd WHERE(" +
"Payments.CCNumber = Payments_fd.CCNumber) AND " +
"Payments.PaymentID = " + id.ToString();
try
{
MySqlCommand sqlCommand = new MySqlCommand(slqCommandString, sqlConnection);
mySQLReader = sqlCommand.ExecuteReader();
if (mySQLReader.Read())
{
Payment.paymentID = mySQLReader.GetInt32(0);
Payment.hostUserID = mySQLReader.GetInt32(1);
Payment.reservationID = mySQLReader.GetInt32(2);
Payment.CCNumber = mySQLReader.GetString(3);
Payment.amount = mySQLReader.GetFloat(4);
// 5 is CCNumber again
Payment.CCType = mySQLReader.GetString(6);
Payment.CCVerificationCode = mySQLReader.GetInt32(7);
Payment.CCExpiryDate = mySQLReader.GetDateTime(8);
mySQLReader.Close();
return(Payment);
}
mySQLReader.Close();
return(null);
}
catch (MySqlException ex)
{
Console.WriteLine("Found an error when performing a GET Payment call in PaymentPersistenceService (GetPayment): " + ex);
return(null);
}
}
/// <summary>
/// 获取work_package_task_list表中State=0的工单
/// </summary>
public void get_info_package_from_db()
{
this.info_package.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;//设置DataGridView的行列为自动调整
String PackageSql = "SELECT `Ap_id`,`Total_plies`,`Total_area`,`Package_num` FROM `work_package_task_list` WHERE `State` = 0";
MySqlCommand InfoPackageCommand = getSqlCommand(PackageSql, mysql);
try
{
mysql.Open();
MySqlDataReader InfoPackageRead = InfoPackageCommand.ExecuteReader();
if (InfoPackageRead.HasRows)
{
while (InfoPackageRead.Read())
{
ap_id = InfoPackageRead.GetString(0);
total_layer = InfoPackageRead.GetInt32(1);
total_area = InfoPackageRead.GetFloat(2);
total_plate = InfoPackageRead.GetInt32(3);
string[] toshow = new string[] { ap_id, total_layer.ToString(), total_area.ToString(), total_plate.ToString() };
int rows = this.info_package.Rows.Add();
for (int i = 0; i < info_package.ColumnCount; i++)//把包信息显示到DataGridView里面
{
info_package.Rows[rows].Cells[i].Value = toshow[i];
}
}
}
}
catch (MySqlException ex)
{
Console.WriteLine(DateTime.Now + " get_info_package_from_db()方法中MySqlException Error:" + ex.ToString());
error_flag = true;
}
finally
{
mysql.Close();
}
}
public Dictionary <int, float> GetAllPrices()
{
Dictionary <int, float> result = new Dictionary <int, float>();
MySqlDataReader mysql_datareader = null;
MySqlCommand command = null;
try {
string query = "SELECT id, price FROM products";
command = m_dbConnection.CreateCommand();
command.Connection.Open();
command.CommandText = query;
mysql_datareader = command.ExecuteReader();
while (mysql_datareader.Read())
{
int id = mysql_datareader.GetInt32(0);
float price = mysql_datareader.GetFloat(1);
result[id] = price;
}
}
catch (Exception e) {
Console.Out.WriteLine("\n\n**********************************************************************");
Console.Out.WriteLine(e.Message);
Console.Out.WriteLine(e.InnerException);
Console.Out.WriteLine(e.Source);
Console.Out.WriteLine("**********************************************************************\n\n");
}
finally {
mysql_datareader?.Close();
command?.Connection?.Close();
}
return(result);
}
public static List <Country> GetByContinent(string userInput)
{
List <Country> allCountries = new List <Country> {
};
MySqlConnection conn = DB.Connection();
conn.Open();
MySqlCommand cmd = conn.CreateCommand() as MySqlCommand;
cmd.CommandText = "SELECT * FROM country WHERE continent LIKE '" + userInput + "%';";
MySqlDataReader rdr = cmd.ExecuteReader() as MySqlDataReader;
while (rdr.Read())
{
string countryName = rdr.GetString(1);
string countryContinent = rdr.GetString(2);
int countryCapital = 0;
if (!rdr.IsDBNull(13))
{
countryCapital = rdr.GetInt32(13);
}
int countryPopulation = rdr.GetInt32(6);
float countryLifeExpectancy = 0;
if (!rdr.IsDBNull(7))
{
countryLifeExpectancy = rdr.GetFloat(7);
}
Country newCountry = new Country(countryName, countryContinent, countryCapital, countryPopulation, countryLifeExpectancy);
allCountries.Add(newCountry);
}
conn.Close();
if (conn != null)
{
conn.Dispose();
}
return(allCountries);
}
