public ArrayList getTrips()
{
MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString = ConfigurationManager.ConnectionStrings["PhpMySqlRemoteDB"].ConnectionString;
conn = new MySql.Data.MySqlClient.MySqlConnection();
try
{
conn.ConnectionString = myConnectionString;
conn.Open();
ArrayList tripArray = new ArrayList();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
string sqlString = "SELECT * FROM Trips";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
while (mySQLReader.Read())
{
Trip t = new Trip();
t.TripID = mySQLReader.GetInt32(0);
t.Username = mySQLReader.GetString(1);
t.From_City_Country = mySQLReader.GetString(2);
t.To_City_Country = mySQLReader.GetString(3);
t.TransportationType = mySQLReader.GetString(4);
t.OutboundTripDetails_Day = mySQLReader.GetDateTime(5);
t.OutboundTripDetails_Time = mySQLReader.GetTimeSpan(6);
t.OutboundTripDetails_Duration = mySQLReader.GetFloat(7);
t.AddAReturnTrip = mySQLReader.GetBoolean(8);
t.ReturnTripDetails_Day = mySQLReader.GetDateTime(9);
t.ReturnTripDetails_Time = mySQLReader.GetTimeSpan(10);
t.ReturnTripDetails_Duration = mySQLReader.GetFloat(11);
t.AvailableWeight = mySQLReader.GetFloat(12);
t.ExcludedCategories = mySQLReader.GetString(13);
t.TripNote = mySQLReader.GetString(14);
tripArray.Add(t);
}
return(tripArray);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public List <Person> getPersons()
{
List <Person> personList = new List <Person>();
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
string sqlString = "SELECT * from tbl_personnel";
var cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
try
{
mySqlReader = cmd.ExecuteReader();
while (mySqlReader.Read())
{
Person p = new Person();
p.ID = mySqlReader.GetInt32(0);
p.FirstName = mySqlReader.GetString(1);
p.LastName = mySqlReader.GetString(2);
p.PayRate = mySqlReader.GetFloat(3);
p.StartDate = mySqlReader.GetDateTime(4);
p.EndDate = mySqlReader.GetDateTime(5);
personList.Add(p);
}
}
catch (Exception ex)
{
Console.WriteLine("MySQL exception");
Console.WriteLine(ex);
Console.WriteLine(ex.Data);
}
return(personList);
}
public gEmployee getEmployee(string als)
{
gEmployee e = new gEmployee();
MySql.Data.MySqlClient.MySqlDataReader getReader = null;
string getString = "SELECT * FROM Emp_details WHERE eAlias = '" + als.ToString() + "';";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(getString, conn);
getReader = cmd.ExecuteReader();
if (getReader.Read())
{
e.Alias = getReader.IsDBNull(0) == false?getReader.GetString(0) : null;
e.GIN = getReader.IsDBNull(1) == false?getReader.GetString(1) : null;
e.DisplayName = getReader.IsDBNull(2) == false?getReader.GetString(2) : null;
e.UserPrincipalName = getReader.IsDBNull(3) == false?getReader.GetString(3) : null;
e.JobCode = getReader.IsDBNull(4) == false?getReader.GetString(4) : null;
e.MobilePhone = getReader.IsDBNull(5) == false?getReader.GetString(5) : null;
e.GOLDMedalOwner = getReader.IsDBNull(6) == false?getReader.GetString(6) : null;
e.QuestOTC = getReader.IsDBNull(7) == false?getReader.GetFloat(7) : 0;
e.Country = getReader.IsDBNull(8) == false?getReader.GetString(8) : null;
e.Department = getReader.IsDBNull(9) == false?getReader.GetString(9) : null;
e.Entity = getReader.IsDBNull(10) == false?getReader.GetString(10) : null;
e.ProgramID = getReader.IsDBNull(11) == false?getReader.GetString(11) : null;
e.CardNo = getReader.IsDBNull(12) == false?getReader.GetString(12) : null;
e.PIN = getReader.IsDBNull(13) == false?getReader.GetString(13) : null;
return(e);
}
else
{
return(null);
}
}
public static KartaDTO readerToKartaDTO(MySqlDataReader reader)
{
KartaDTO karta = new KartaDTO();
karta.Id = reader.GetInt32("idKarta");
karta.DatumProdaje = reader.GetDateTime("datumProdaje");
karta.Cijena = reader.GetFloat("cijenaKarta");
karta.BrojReda = reader.GetInt32("brojReda");
karta.BrojSjedista = reader.GetInt32("brojSjedista");
karta.Zaposleni = MySqlZaposleniDAO.readerToZaposleni(reader);
SalaDTO sala = MySqlSalaDAO.readerToSalaDTO(reader);
karta.Projekcija = MySqlProjekcijaDAO.readerToProjekcijaDTO(reader, sala);
karta.Status = MySqlStatusKartaDAO.readerToStatusKartaDTO(reader);
if (karta.Status.Naziv.Equals("rezervisana"))
{
karta.Rezervacija = MySqlRezervacijaDAO.readerToRezervacijaDTO(reader);
}
return karta;
}
public Person getPerson(long ID)
{
Person p = new Person();
p.FirstName = "Phil";
p.LastName = "Anderson";
MySql.Data.MySqlClient.MySqlDataReader mySqlReader = null;
string sqlString = "SELECT * from tbl_personnel where ID = " + ID.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
try
{
mySqlReader = cmd.ExecuteReader();
if (mySqlReader.Read())
{
p.ID = mySqlReader.GetInt32(0);
p.FirstName = mySqlReader.GetString(1);
p.LastName = mySqlReader.GetString(2);
p.PayRate = mySqlReader.GetFloat(3);
p.StartDate = mySqlReader.GetDateTime(4);
p.EndDate = mySqlReader.GetDateTime(5);
}
else
{
}
}
catch (Exception ex) {
Trace.WriteLine("SQL Exception");
Trace.WriteLine(ex);
Trace.WriteLine(ex.Data);
}
finally
{
}
return(p);
}
public override void Fill(MySql.Data.MySqlClient.MySqlDataReader dr)
{
_ID = dr.GetInt32(db_ID);
_DetailCodeID = dr.GetInt32(db_DetailCodeID);
_Amount = dr.GetFloat(db_Amount);
}
private void button1_Click(object sender, EventArgs e)
{
if (textBox1.Text == "")
MessageBox.Show("Необходимо указать тип помещения");
else
{
try
{
mconnect.Open();
//число помещений в базе
cmd.CommandText = "SELECT COUNT(*) FROM facility WHERE facility.usag = @typ";
cmd.Parameters["@typ"].Value = textBox1.Text;
double c = Convert.ToDouble(cmd.ExecuteScalar());
label6.Text = "Всего помещений: " + c.ToString();
//средняя температура по больнице
cmd.CommandText = "SELECT * FROM facility WHERE facility.usag = @typ";
float sumR = 0, sumS = 0;
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
sumR += rdr.GetFloat(2);
sumS += rdr.GetFloat(3);
}
rdr.Close();
label3.Text = "Средняя цена аренды: " + (sumR / c).ToString();
label4.Text = "Средняя цена продажи: " + (sumS / c).ToString();
//уровень спроса
double sp = 0;
cmd.CommandText = "SELECT COUNT(*) FROM RentDocument, facility WHERE RentDocument.Facility = facility.idfacility AND facility.usag = @typ";
sp += Convert.ToDouble(cmd.ExecuteScalar());
cmd.CommandText = "SELECT COUNT(*) FROM SaleDocument, facility WHERE SaleDocument.Facility = facility.idfacility AND facility.usag = @typ";
sp += Convert.ToDouble(cmd.ExecuteScalar());
label5.Text = "Уровень спроса: " + (sp / c).ToString() + "%";
//изменение цен
draw_axis(g);
p.Color = Color.Blue;
p.Width = 2;
Point preA = new Point();
Point preB = new Point();
Point A = new Point();
Point B = new Point();
preA.X = 39;
preA.Y = 275;
preB.X = 39;
preB.Y = 275;
cmd.CommandText = "SELECT RentPrice, SalePrice FROM facility WHERE facility.usag = @typ";
rdr = cmd.ExecuteReader();
int ax = Convert.ToInt32(387 / (c * 10));
while (rdr.Read())
{
float pr1 = rdr.GetFloat(0);
float pr2 = rdr.GetFloat(1);
A.X = ax + 50;
B.X = ax + 50;
A.Y = 275 - Convert.ToInt32(pr1 / (2920));
B.Y = 275 - Convert.ToInt32(pr2 / (2920)) - 10;
p.Color = Color.Blue;
g.DrawLine(p, preA.X, preA.Y, A.X, A.Y);
g.DrawString(pr1.ToString(), new System.Drawing.Font("Arial Black", 8, FontStyle.Regular, GraphicsUnit.Point), new SolidBrush(Color.Blue), new Point(A.X, A.Y - 15));
preA = A;
p.Color = Color.Red;
g.DrawLine(p, preB.X, preB.Y, B.X, B.Y);
g.DrawString(pr2.ToString(), new System.Drawing.Font("Arial Black", 8, FontStyle.Regular, GraphicsUnit.Point), new SolidBrush(Color.Red), new Point(B.X, B.Y - 15));
preB = B;
ax += 50;
}
rdr.Close();
img.Image = b1;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
if (mconnect != null)
mconnect.Close();
}
}
public void LoadFromReader(MySqlDataReader _reader)
{
m_id = _reader["id"].GetType() != typeof(System.DBNull) ? _reader.GetInt64("id") : 0;
mc_id = false;
m_entity_id = _reader["entity_id"].GetType() != typeof(System.DBNull) ? _reader.GetInt64("entity_id") : 0;
mc_entity_id = false;
m_proto_name = _reader["proto_name"].GetType() != typeof(System.DBNull) ? _reader.GetString("proto_name") : "";
mc_proto_name = false;
m_month = _reader["month"].GetType() != typeof(System.DBNull) ? _reader.GetByte("month") : (byte)0;
mc_month = false;
m_price = _reader["price"].GetType() != typeof(System.DBNull) ? _reader.GetFloat("price") : (double)0;
mc_price = false;
m_comment = _reader["comment"].GetType() != typeof(System.DBNull) ? _reader.GetString("comment") : "";
mc_comment = false;
}
public static KartaDTO readerToKartaDTO(MySqlDataReader reader, ProjekcijaDTO projekcija,RezervacijaDTO rezervacija)
{
KartaDTO karta = new KartaDTO();
karta.Id = reader.GetInt32("idKarta");
karta.DatumProdaje = reader.GetDateTime("datumProdaje");
karta.Cijena = reader.GetFloat("cijenaKarta");
karta.BrojReda = reader.GetInt32("brojReda");
karta.BrojSjedista = reader.GetInt32("brojSjedista");
karta.Zaposleni = MySqlZaposleniDAO.readerToZaposleni(reader);
karta.Projekcija = projekcija;
karta.Rezervacija = rezervacija;
karta.Status = MySqlStatusKartaDAO.readerToStatusKartaDTO(reader);
return karta;
}
public static KartaDTO readerToKartaDTO(MySqlDataReader reader, ZaposleniDTO zaposleni)
{
KartaDTO karta = new KartaDTO();
karta.Id = reader.GetInt32("idKarta");
karta.DatumProdaje = reader.GetDateTime("datumProdaje");
karta.BrojReda = reader.GetInt32("brojReda");
karta.Cijena = reader.GetFloat("cijenaKarta");
karta.BrojSjedista = reader.GetInt32("brojSjedista");
karta.Zaposleni = zaposleni;
SalaDTO sala = MySqlSalaDAO.readerToSalaDTO(reader);
karta.Projekcija = MySqlProjekcijaDAO.readerToProjekcijaDTO(reader, sala);
//karta.Rezervacija = MySqlRezervacijaDAO.readerToRezervacijaDTO(reader);
karta.Status = MySqlStatusKartaDAO.readerToStatusKartaDTO(reader);
return karta;
}
private void LoadLine(MySqlDataReader rdr)
{
string temp = "";
if(!rdr.IsDBNull(0)) { line_no = rdr.GetInt32(0); ORDER_ARRAY[rowCount, CONST_LINE_NO] = line_no.ToString(); }
if (!rdr.IsDBNull(1)) { part_id = rdr.GetString(1); ORDER_ARRAY[rowCount, CONST_PARTID] = part_id; }
if (!rdr.IsDBNull(2)) { desc = rdr.GetString(2); ORDER_ARRAY[rowCount, CONST_DESC] = desc; }
if (!rdr.IsDBNull(3)) { ordered_qty = rdr.GetInt32(3); ORDER_ARRAY[rowCount, CONST_ORDERED] = ordered_qty.ToString(); }
if (!rdr.IsDBNull(4)) { unit_price = rdr.GetFloat(4); ORDER_ARRAY[rowCount, CONST_PRICE] = unit_price.ToString(); }
if (!rdr.IsDBNull(5)) { mod = rdr.GetChar(5); ORDER_ARRAY[rowCount, CONST_MOD] = mod.ToString(); }
if (!rdr.IsDBNull(6)) { gst = Math.Round(rdr.GetDouble(6), 2); ORDER_ARRAY[rowCount, CONST_GST] = gst.ToString(); }
if (!rdr.IsDBNull(7)) { pst = Math.Round(rdr.GetDouble(7), 2); ORDER_ARRAY[rowCount, CONST_PST] = pst.ToString(); }
if (!rdr.IsDBNull(8)) { notes = rdr.GetString(8); ORDER_ARRAY[rowCount, CONST_NOTES] = notes; }
bool cust = false;
if (mod == 'C')
cust = true;
DataGridViewCheckBoxColumn checkColumn = new DataGridViewCheckBoxColumn();
checkColumn.Name = "X";
checkColumn.HeaderText = "X";
checkColumn.Width = 50;
checkColumn.ReadOnly = false;
checkColumn.FillWeight = 10; //if the datagridview is resized (on form resize) the checkbox won't take up too much; value is relative to the other columns' fill values
dgOrderForm.Rows.Add(line_no.ToString(), cust, part_id, "", desc, ordered_qty.ToString(), unit_price.ToString(), gst.ToString(), pst.ToString(), notes);
MAX_LINE = Convert.ToInt32(line_no);
}
private void QueryWeatherData()
{
System.Text.EncodingProvider ppp;
ppp = System.Text.CodePagesEncodingProvider.Instance;
Encoding.RegisterProvider(ppp);
// Query weather data from MySQL Server, update values in WeatherData - data parameters for display to screen (updatescreen()).
conn = null;
rdr = null;
try {
conn = new MySqlConnection(myConnectionString);
conn.Open();
cmd = new MySqlCommand(queryLastEntry, conn);
cmd2 = new MySqlCommand(queryDayMaxTemp, conn);
cmd3 = new MySqlCommand(queryDayMinTemp, conn);
cmd4 = new MySqlCommand(queryAnnualMaxTemp, conn);
cmd5 = new MySqlCommand(queryAnnualMinTemp, conn);
cmd6 = new MySqlCommand(queryPressureChangeThree, conn);
cmd7 = new MySqlCommand(queryPressureChangeSix, conn);
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
// Update screen with data from the most recent row of the weatherdata table only
data.TimeStamp = rdr.GetDateTime(0);
data.Altitude = rdr.GetFloat(1);
data.BarometricPressure = rdr.GetFloat(2);
data.CelsiusTemperature = rdr.GetFloat(3);
data.FahrenheitTemperature = rdr.GetFloat(4);
data.Humidity = rdr.GetFloat(5);
data.LightSensorVoltage = rdr.GetFloat(6);
LogToScreen("Last reading from board read to screen");
seaLevelPressure = getSeaLevelPressure(data.BarometricPressure, data.CelsiusTemperature, locationAlt);
}
rdr.Close();
rdr = cmd2.ExecuteReader();
while (rdr.Read())
{
data.DayMaxTempTime = rdr.GetDateTime(0);
data.DayMaxTemp = rdr.GetFloat(1);
LogToScreen("Maximum daily temperature queried and updated");
}
rdr.Close();
rdr = cmd3.ExecuteReader();
while (rdr.Read())
{
data.DayMinTempTime = rdr.GetDateTime(0);
data.DayMinTemp = rdr.GetFloat(1);
LogToScreen("Minimum daily temperature queried and updated");
}
rdr.Close();
rdr = cmd4.ExecuteReader();
while (rdr.Read())
{
data.AnnualMaxTempDate = rdr.GetDateTime(0);
data.AnnualMaxTemp = rdr.GetFloat(1);
LogToScreen("Maximum annual temperature queried and updated");
}
rdr.Close();
rdr = cmd5.ExecuteReader();
while (rdr.Read())
{
data.AnnualMinTempDate = rdr.GetDateTime(0);
data.AnnualMinTemp = rdr.GetFloat(1);
LogToScreen("Minimum annual temperature queried and updated");
}
rdr.Close();
rdr = cmd6.ExecuteReader();
while (rdr.Read())
{
data.PressThreeHrs = (rdr.GetFloat(1) - data.BarometricPressure);
LogToScreen("Pressure change over 3 hours queried and updated");
}
rdr.Close();
rdr = cmd7.ExecuteReader();
while (rdr.Read())
{
data.PressSixHrs = (rdr.GetFloat(1) - data.BarometricPressure);
LogToScreen("Pressure change over 3 hours queried and updated");
}
rdr.Close();
}
catch (Exception e)
{
LogToScreen("ERROR: Unable to successfully reach " + weatherUri + "!");
LogToScreen("ERROR: " + e.HResult + " {\r\n" + e.Message + "}");
}
finally
{
LogToScreen("");
if (conn != null)
{
conn.Close();
}
if (rdr != null)
{
rdr.Close();
}
}
}
// 取 dr 中某字段的数据并返回
public static object GetValue( DbColumn col, MySqlDataReader r, int idx )
{
switch( col.dataType )
{
case DbDataTypes.Boolean:
return r.GetBoolean( idx );
case DbDataTypes.Int8:
return r.GetSByte( idx );
case DbDataTypes.Int16:
return r.GetInt16( idx );
case DbDataTypes.Int32:
return r.GetInt32( idx );
case DbDataTypes.Int64:
return r.GetInt64( idx );
case DbDataTypes.UInt8:
return r.GetByte( idx );
case DbDataTypes.UInt16:
return r.GetUInt16( idx );
case DbDataTypes.UInt32:
return r.GetUInt32( idx );
case DbDataTypes.UInt64:
return r.GetUInt64( idx );
case DbDataTypes.Float:
return r.GetFloat( idx );
case DbDataTypes.Double:
return r.GetDouble( idx );
case DbDataTypes.DateTime:
return r.GetDateTime( idx );
case DbDataTypes.String:
return r.GetString( idx );
case DbDataTypes.Bytes:
var len = (int)r.GetBytes( idx, 0, null, 0, 0 );
var buf = new byte[ len ];
r.GetBytes( idx, 0, buf, 0, len );
return buf;
default:
throw new Exception( "unsupported DbType" );
}
}
// 往 dr 填充 r 于 i 索引的值
public static void FillValue( DbRow dr, MySqlDataReader r, int i )
{
switch( dr.parent.columns[ i ].dataType )
{
case DbDataTypes.Boolean:
dr[ i ].Assign( r.GetBoolean( i ) ); break;
case DbDataTypes.Int8:
dr[ i ].Assign( r.GetSByte( i ) ); break;
case DbDataTypes.Int16:
dr[ i ].Assign( r.GetInt16( i ) ); break;
case DbDataTypes.Int32:
dr[ i ].Assign( r.GetInt32( i ) ); break;
case DbDataTypes.Int64:
dr[ i ].Assign( r.GetInt64( i ) ); break;
case DbDataTypes.UInt8:
dr[ i ].Assign( r.GetByte( i ) ); break;
case DbDataTypes.UInt16:
dr[ i ].Assign( r.GetUInt16( i ) ); break;
case DbDataTypes.UInt32:
dr[ i ].Assign( r.GetUInt32( i ) ); break;
case DbDataTypes.UInt64:
dr[ i ].Assign( r.GetUInt64( i ) ); break;
case DbDataTypes.Float:
dr[ i ].Assign( r.GetFloat( i ) ); break;
case DbDataTypes.Double:
dr[ i ].Assign( r.GetDouble( i ) ); break;
case DbDataTypes.DateTime:
dr[ i ].Assign( r.GetDateTime( i ) ); break;
case DbDataTypes.String:
dr[ i ].Assign( r.GetString( i ) ); break;
case DbDataTypes.Bytes:
var len = (int)r.GetBytes( i, 0, null, 0, 0 );
var buf = new byte[ len ];
r.GetBytes( i, 0, buf, 0, len );
dr[ i ].Assign( buf );
break;
default:
break;
}
}
public void SetProperty(object obj, SelectQueryFieldStore item,MySqlDataReader rdr)
{
var pinfo = obj.GetType().GetProperty(item.Property);
if (pinfo != null)
{
switch (item.Type)
{
case SelectQueryFieldStore.DataTypes.Int:
pinfo.SetValue(obj, rdr.GetInt32(item.Column), null);
break;
case SelectQueryFieldStore.DataTypes.Bool:
pinfo.SetValue(obj, Convert.ToBoolean(rdr.GetInt32(item.Column)), null);
break;
case SelectQueryFieldStore.DataTypes.Float:
pinfo.SetValue(obj, rdr.GetFloat(item.Column), null);
break;
case SelectQueryFieldStore.DataTypes.String:
pinfo.SetValue(obj, rdr.GetString(item.Column), null);
break;
case SelectQueryFieldStore.DataTypes.Long:
pinfo.SetValue(obj, rdr.GetInt64(item.Column), null);
break;
case SelectQueryFieldStore.DataTypes.Short:
pinfo.SetValue(obj, rdr.GetInt16(item.Column), null);
break;
default:
pinfo.SetValue(obj, rdr.GetString(item.Column), null);
break;
}
}
}
