public Values getValues(int id)
{
Values v = new Values();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
string sqlString = $"SELECT * FROM Products WHERE id ={id}";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
if (mySQLReader.Read())
{
v.id = mySQLReader.GetInt32(0);
v.product_name = mySQLReader.GetString(1);
v.stock_quantity = mySQLReader.GetInt32(2);
v.des_box = mySQLReader.GetString(3);
v.URL = mySQLReader.GetString(4);
v.categories = mySQLReader.GetString(5);
return(v);
}
else
{
return(null);
}
}
public bool Login(string email, string password)
{
int count = DbConnecter.instance.Count("age", "member", "email = '" + email + "' AND password = '******';");
if (count == 0)
{
return(false);
}
else
{
string sql = "SELECT * FROM member WHERE email = '" + email + "' AND password = '******';";
MySql.Data.MySqlClient.MySqlDataReader reader = DbConnecter.instance.Reader(sql);
reader.Read();
member.email = email;
member.password = password;
member.gender = reader.GetInt32(2);
member.age = reader.GetInt32(3);
member.child = reader.GetInt32(4);
member.productBookmarks = "";
if (reader.IsDBNull(5) == false)
{
member.productBookmarks = reader.GetString(5);
}
member.ingredientBookmarks = "";
if (reader.IsDBNull(6) == false)
{
member.ingredientBookmarks = reader.GetString(6);
}
reader.Close();
DbConnecter.instance.CloseConnection();
return(true);
}
}
//when check name and password, get the user's information
public PropertyClass CheckNameAndPasswd(string name,string password)
{
PropertyClass accountClass=null;
string strSql="select * from account where accountName='"+name.Trim()+"' and accountPassword='******'";
try{
sdr=db.GetDataReader(strSql);
sdr.Read();
if(sdr.HasRows)
{
accountClass=new PropertyClass();
accountClass.UserId=sdr.GetInt32("id");
accountClass.AccountName=name.Trim();
accountClass.AccountPassword=password.Trim();
accountClass.Email=sdr["email"].ToString();
accountClass.Job=sdr.GetInt32("job");;
accountClass.Superviser=sdr.GetInt32("superviser");
}
}
catch(Exception ex)
{
}
finally
{
sdr.Close();
}
return accountClass;
}
private void DoSQLQueryPieBanque()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM database.detail WHERE detail.titre='Solde banque'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
BIATD = reader.GetInt32(reader.GetOrdinal("t1"));
BIATE = reader.GetInt32(reader.GetOrdinal("t2"));
STB = reader.GetInt32(reader.GetOrdinal("t3"));
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
private void RulesForm_Load(object sender, EventArgs e)
{
string time="";
this.ControlBox = false;
con.Open();
cmd = new MySqlCommand("select sum(mark) from test where ecode='" + SelectExam.exam_code + "'", con);
dr = cmd.ExecuteReader();
dr.Read();
cnt = dr.GetInt32(0);
dr.Close();
cmd = new MySqlCommand("select duration,tot_quest from exam where eid='" + SelectExam.exam_code + "'", con);
dr = cmd.ExecuteReader();
if (dr.Read())
{
noquest = dr.GetInt32("tot_quest");
temp = dr.GetInt32("duration");
time = (temp.ToString().Length > 1) ? "mins" : "hrs";
label3.Text = "Duraton of Exam is : " + temp.ToString() + " " + time + "\n\nTotal Number Of Questions : " + noquest+"\n\nMax. Marks : "+cnt;
}
else
{
MessageBox.Show("Exam Code not selected", "Code Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
this.Hide();
new SelectExam().Show();
}
dr.Close();
con.Close();
}
private void DoSQLQueryPieFournisseur()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM database.detail WHERE detail.t1='" + Session["AnnPieFournisseur"] + "' AND detail.titre='Solde fournisseurs'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
fournisseur = reader.GetString(reader.GetOrdinal("t1"));
DebitF1 = reader.GetInt32(reader.GetOrdinal("t2"));
CreditF1 = reader.GetInt32(reader.GetOrdinal("t3"));
SoldeF1 = reader.GetInt32(reader.GetOrdinal("t4"));
}
reader.Close();
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
// overload 2/9/2011 fix load history in new month
public bool CheckMonthlyDocIsApprove(POSMySQL.POSControl.CDBUtil dbUtil, MySql.Data.MySqlClient.MySqlConnection conn,
DateTime dateFrom, DateTime dateTo)
{
this.DocumentTypeId = 7;
string sql = " SELECT DocumentDate, DocumentID FROM document WHERE documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
" AND DocumentStatus=1 AND DocumentDate BETWEEN '" + dateFrom.ToString("yyyy-MM-dd", dateProvider) + "' AND '" + dateTo.ToString("yyyy-MM-dd", dateProvider) + "'";
MySql.Data.MySqlClient.MySqlDataReader reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
this.DocumentDate = reader.GetDateTime("DocumentDate");
this.DocumentId = reader.GetInt32("DocumentID");
reader.Close();
return(false); // Approved
}
else
{
sql = " SELECT DocumentDate, DocumentID FROM document WHERE documenttypeid=" + this.DocumentTypeId + " AND shopid=" + this.ShopId +
" AND DocumentStatus=2 AND DocumentDate BETWEEN '" + dateFrom.ToString("yyyy-MM-dd", dateProvider) + "' AND '" + dateTo.ToString("yyyy-MM-dd", dateProvider) + "'";
reader.Close();
reader = dbUtil.sqlRetrive(sql, conn);
if (reader.Read())
{
this.DocumentDate = reader.GetDateTime("DocumentDate");
this.DocumentId = reader.GetInt32("DocumentID");
reader.Close();
return(true); // Not Approve
}
else
{
reader.Close();
}
}
return(false);
}
private void DoSQLAffiche1()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "SELECT * FROM database.event WHERE event.iduser='******' AND event.idex='2'";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
namec1 = reader.GetString(reader.GetOrdinal("name"));
monthstart1 = reader.GetInt32(reader.GetOrdinal("monthstart"));
yearstart1 = reader.GetInt32(reader.GetOrdinal("yearstart"));
daystart1 = reader.GetInt32(reader.GetOrdinal("daystart"));
yearend1 = reader.GetInt32(reader.GetOrdinal("yearend"));
monthend1 = reader.GetInt32(reader.GetOrdinal("monthend"));
dayend1 = reader.GetInt32(reader.GetOrdinal("dayend"));
hstart1 = reader.GetInt32(reader.GetOrdinal("hstart"));
hend1 = reader.GetInt32(reader.GetOrdinal("hend"));
minstart1 = reader.GetInt32(reader.GetOrdinal("minstart"));
minend1 = reader.GetInt32(reader.GetOrdinal("minend"));
}
Labelnamec1.Text = namec1;
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
reader.Close();
conn.Close();
}
public List <AmphurModel> getAmphur(int province_id)
{
MySqlConnection con = WebApiConfig.conn();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
MySqlCommand cm = con.CreateCommand();
cm.CommandText = "SELECT * FROM amphur WHERE PROVINCE_ID =" + province_id.ToString();
try
{
con.Open();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw;
}
mySQLReader = cm.ExecuteReader();
var result = new List <AmphurModel>();
while (mySQLReader.Read())
{
AmphurModel p = new AmphurModel();
p.amphur_id = mySQLReader.GetInt32(0);
p.amphur_code = mySQLReader.GetString(1);
p.amphur_name = mySQLReader.GetString(2);
p.geo_id = mySQLReader.GetInt32(3);
p.province_id = mySQLReader.GetInt32(4);
result.Add(p);
}
return(result);
}
public Drink loadDrinkDetails(int drinkId)
{
Drink p = new Drink();
MySqlConnection conn = mysqlDbConnect.GetConnection();
MySql.Data.MySqlClient.MySqlDataReader mySqlReade = null;
String sqlString = "SELECT * FROM drink WHERE drinkId='" + drinkId + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReade = cmd.ExecuteReader();
if (mySqlReade.Read())
{
p.drinkId = mySqlReade.GetInt32(0);
p.drinkName = mySqlReade.GetString(1);
p.drinkPrice = mySqlReade.GetInt32(2);
return(p);
}
else
{
return(null);
}
}
public Food loadFoodDetails(int foodId)
{
Food p = new Food();
MySqlConnection conn = mysqlDbConnect.GetConnection();
MySql.Data.MySqlClient.MySqlDataReader mySqlReade = null;
String sqlString = "SELECT * FROM food WHERE foodId='" + foodId + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReade = cmd.ExecuteReader();
if (mySqlReade.Read())
{
p.foodId = mySqlReade.GetInt32(0);
p.foodName = mySqlReade.GetString(1);
p.foodPrice = mySqlReade.GetInt32(2);
return(p);
}
else
{
return(null);
}
}
public static StavkaKnjigeTrgovineNaVelikoDTO readerToStavkaKnjigeTrgovineNaVeliko(MySqlDataReader reader)
{
StavkaKnjigeTrgovineNaVelikoDTO stavka = new StavkaKnjigeTrgovineNaVelikoDTO();
stavka.RedniBroj = reader.GetInt32("RedniBroj");
stavka.PoslovnaGodina = reader.GetInt32("PoslovnaGodina");
stavka.Datum = reader.GetDateTime("Datum");
return stavka;
}
public Tag Map(MySqlDataReader reader)
{
Tag tag = new Tag();
tag.Id = reader.GetInt32("id");
tag.Tagname = reader.GetString("tagname");
tag.TagCount = reader.IsDBNull(2) ? (int?)null : reader.GetInt32(2);
return tag;
}
public Favorite Map(MySqlDataReader reader)
{
Favorite fav = new Favorite();
fav.UserId = reader.GetInt32(0);
fav.PostId = reader.GetInt32(1);
fav.Annotation = reader.GetString("annotation");
return fav;
}
public override void Fill(MySql.Data.MySqlClient.MySqlDataReader dr)
{
_ID = dr.GetInt32(db_ID);
_RoleID = dr.GetInt32(db_RoleID);
_Username = dr.GetString(db_Username);
_Password = dr.GetString(db_Password);
_Salt = dr.GetString(db_Salt);
}
public Session ProcessRow(Session session, MySqlDataReader Reader, int offset)
{
session.Id = Reader.GetInt32(0 + offset);
session.Daytime_id = Reader.GetInt32(1 + offset);
session.Classroom_id = Reader.GetInt32(2 + offset);
session.Pair_id = Reader.GetInt32(3 + offset);
return session;
}
public override void Fill(MySql.Data.MySqlClient.MySqlDataReader dr)
{
_ID = dr.GetInt32(db_ID);
_Abbreviation = dr.GetString(db_Abbreviation);
_Name = dr.GetString(db_Name);
_EndWeek = dr.GetInt32(db_EndWeek);
_StartWeek = dr.GetInt32(db_StartWeek);
}
public SearchPost Map(MySqlDataReader rdr)
{
SearchPost SP = new SearchPost();
SP.Url = HttpContext.Current.Request.Url.AbsoluteUri + "/"+ rdr.GetInt32("id");
SP.Id = rdr.GetInt32("id");
SP.Body = rdr.GetString("body");
SP.Title = rdr.GetString("title");
return SP;
}
public override void Fill(MySql.Data.MySqlClient.MySqlDataReader dr)
{
_ID = dr.GetInt32(db_ID);
_FirstName = dr.GetString(db_FirstName);
_LastName = dr.GetString(db_LastName);
_MiddleName = dr.GetString(db_MiddleName);
_Number = dr.GetInt32(db_Number);
_CourseLoad = dr.GetInt32(db_CourseLoad);
}
public override void Fill(MySql.Data.MySqlClient.MySqlDataReader dr)
{
_ID = dr.GetInt32(db_ID);
_InstructorID = dr.GetInt32(db_InstructorID);
_SectionID = dr.GetInt32(db_SectionID);
_IsPrimary = dr.GetBoolean(db_IsPrimary);
_TeachingPercentage = dr.GetInt32(db_TeachingPercentage);
_DateArchived = dr.GetDateTime(db_DateArchived);
}
public static FilmDTO readerToFilmDTO(MySqlDataReader reader)
{
FilmDTO film = new FilmDTO();
film.Id = reader.GetInt32("idFilm");
film.Naziv = reader["nazivFilm"].ToString();
film.Opis = reader["opisFilm"].ToString();
film.Trajanje = reader.GetInt32("trajanjeFilm");
return film;
}
public static SalaDTO readerToSalaDTO(MySqlDataReader reader)
{
SalaDTO sala = new SalaDTO();
sala.Id = reader.GetInt32("idSala");
sala.Aktivna = reader.GetInt32("aktivna");
sala.Naziv = reader["nazivSala"].ToString();
sala.BrojRedova = reader.GetInt32("brojRedova");
sala.BrojSjedistaURedu = reader.GetInt32("brojSjedistaURedu");
return sala;
}
public static RezervacijaDTO readerToRezervacijaDTO(MySqlDataReader reader)
{
RezervacijaDTO rezervacija = new RezervacijaDTO();
rezervacija.Id = reader.GetInt32("idRezervacija");
rezervacija.VrijemeRezervacije = reader.GetDateTime("vrijemeRezervacija");
rezervacija.Opis = reader["opisRezervacija"].ToString();
rezervacija.Aktivna = reader.GetInt32("aktivnaRezervacija");
rezervacija.Zaposleni = MySqlZaposleniDAO.readerToZaposleni(reader);
return rezervacija;
}
public static ProjekcijaDTO readerToProjekcijaDTO(MySqlDataReader reader, SalaDTO sala)
{
ProjekcijaDTO projekcija = new ProjekcijaDTO();
projekcija.Id = reader.GetInt32("idProjekcija");
projekcija.Vrijeme = reader.GetDateTime("vrijemeProjekcija");
projekcija.Cijena = reader.GetDouble("cijenaProjekcija");
projekcija.Film = new MySqlFilmDAO().getById(reader.GetInt32("idFilm"));
projekcija.Sala = sala;
return projekcija;
}
public static Usuario GetUsuario(MySqlDataReader rdr)
{
if (rdr.IsDBNull(rdr.GetOrdinal("CODUSU"))) return null;
Usuario u = new Usuario();
u.CodUsu = rdr.GetInt32("CODUSU");
u.NomUsu = rdr.GetString("NOMUSU");
u.Login = rdr.GetString("LOGIN");
u.PasswordPropio = rdr.GetString("PASSWORD_PROPIO");
u.NivelAriges = rdr.GetInt32("NIVEL_ARIGES");
return u;
}
protected Student GetStudentFromDataReader(MySqlDataReader dataReader)
{
int studentenID = dataReader.GetInt32("student_id");
string naam = dataReader.GetString("studentnaam");
DateTime geboorteDatum = dataReader.GetDateTime("geboortedatum");
int studiePunten = dataReader.GetInt32("studiepunten");
Student student = new Student { ID = studentenID, Naam = naam, GeboorteDatum = geboorteDatum, StudiePunten = studiePunten };
return student;
}
public override void Fill(MySql.Data.MySqlClient.MySqlDataReader dr)
{
_ID = dr.GetInt32(db_ID);
_StartingRowNumber = dr.GetInt32(db_StartingRowNumber);
_AcademicSemester = dr.GetString(db_AcademicSemester);
_AddChangeDelete = dr.GetString(db_AddChangeDelete);
_SectionCRN = dr.GetString(db_SectionCRN);
_Department = dr.GetString(db_Department);
_CourseNumber = dr.GetString(db_CourseNumber);
_SectionNumber = dr.GetString(db_SectionNumber);
_CrossListID = dr.GetString(db_CrossListID);
_CourseTitle = dr.GetString(db_CourseTitle);
_Campus = dr.GetString(db_Campus);
_ScheduleType = dr.GetString(db_ScheduleType);
_MoodleRequired = dr.GetString(db_MoodleRequired);
_InstructorApprovalRequired = dr.GetString(db_InstructorApprovalRequired);
_PartOfTerm = dr.GetString(db_PartOfTerm);
_FixedCredit = dr.GetString(db_FixedCredit);
_MinimumCredits = dr.GetString(db_MinimumCredits);
_MaximumCredits = dr.GetString(db_MaximumCredits);
_ClassLimit = dr.GetString(db_ClassLimit);
_Monday = dr.GetString(db_Monday);
_Tuesday = dr.GetString(db_Tuesday);
_Wednesday = dr.GetString(db_Wednesday);
_Thursday = dr.GetString(db_Thursday);
_Friday = dr.GetString(db_Friday);
_Saturday = dr.GetString(db_Saturday);
_Sunday = dr.GetString(db_Sunday);
_StartTime = dr.GetString(db_StartTime);
_EndTime = dr.GetString(db_EndTime);
_Building = dr.GetString(db_Building);
_Room = dr.GetString(db_Room);
_PrimaryInstructorFirstName = dr.GetString(db_PrimaryInstructorFirstName);
_PrimaryInstructorLastName = dr.GetString(db_PrimaryInstructorLastName);
_PrimaryInstructorNumber = dr.GetString(db_PrimaryInstructorNumber);
_PrimaryInstructorTeachingPercentage = dr.GetString(db_PrimaryInstructorTeachingPercent);
_SecondaryInstructorFirstName = dr.GetString(db_SecondaryInstructorFirstName);
_SecondaryInstructorLastName = dr.GetString(db_SecondaryInstructorLastName);
_SecondaryInstructorNumber = dr.GetString(db_SecondaryInstructorNumber);
_SecondaryInstructorTeachingPercentage = dr.GetString(db_SecondaryInstructorTeachingPercent);
_ClassFeeDetailCode = dr.GetString(db_ClassFeeDetailCode);
_ClassFeeAmount = dr.GetString(db_ClassFeeAmount);
_SectionNotes = dr.GetString(db_SectionNotes);
_CrossListSubject = dr.GetString(db_CrossListSubject);
_CrossListCourseNumber = dr.GetString(db_CrossListCourseNumber);
_DepartmentComments = dr.GetString(db_DepartmentComments);
_DefaultFontStyle = dr.GetString(db_DefaultFontStyle);
_SpringAbbreviation = dr.GetString(db_SpringAbbreviation);
_SummerAbbreviation = dr.GetString(db_SummerAbbreviation);
_FallAbbreviation = dr.GetString(db_FallAbbreviation);
_DefaultFontSize = dr.GetInt32(db_DefaultFontSize);
_RequiresMoodleAbbreviation = dr.GetString(db_RequiresMoodleAbbreviation);
_RequiresPermissionAbbreviation = dr.GetString(db_RequiresPermissionAbbreviation);
}
public override void Fill(MySql.Data.MySqlClient.MySqlDataReader dr)
{
_ID = dr.GetInt32(db_ID);
_BuildingID = dr.GetInt32(db_BuildingID);
int number = 0;
int.TryParse((string)dr[db_Number], out number);
_Number = number;
_SeatsAvailable = dr.GetInt32(db_SeatsAvailable);
_Details = dr.GetString(db_Details);
}
public void LoadFromDB(MySqlDataReader reader)
{
Decks.ForEach(v => v.Clear());
while (reader.Read())
{
DeckType deckType = (DeckType)reader.GetInt32(0);
Int32 slotNo = reader.GetInt16(1);
Int32 characterNo = reader.GetInt32(2);
this[deckType].Characters[slotNo] = _user.InvenCharacter.Find(characterNo);
}
}
// Haal adres van klanten binnen
protected Adres getFullAdresFromDataReader(MySqlDataReader datareader)
{
Adres adres = new Adres
{
Id = datareader.GetInt32("adresId"),
Straat = datareader.GetString("straat"),
Postcode = datareader.GetString("postcode"),
Huisnummer = datareader.GetInt32("huisnummer"),
HuisnummerToevoegsel = String.IsNullOrEmpty(datareader["huisnummertoevoeging"].ToString()) ? "Geen" : datareader.GetString("huisnummertoevoeging"),
Stad = datareader.GetString("stad"),
};
return adres;
}
private static ProjectMember FillProjectMember(MySqlDataReader dr)
{
ProjectMember p = new ProjectMember();
p = new ProjectMember();
p.EmpProj = dr.GetInt32("EmpProj");
p.ProjID = dr.GetInt32("ProjID");
p.EmpID = dr.GetInt32("EmpID");
p.Role = dr.GetString("Role");
p.TotalHours = dr.GetDecimal("TotalHours");
p.StartDate = dr.GetDateTime("StartDate");
p.EndDate = dr["EndDate"] as DateTime?;
return p;
}
private static WeekSummary MapWeekSummary(MySqlDataReader dr)
{
var weekSummary = new WeekSummary
{
LastGamePlayed = dr.GetDateTime("lastGamePlayed"),
Season = dr.GetInt32("season"),
SeasonType = (SeasonType) dr.GetInt32("type"),
Week = dr.GetInt32("week")
};
weekSummary.LastGamePlayed = weekSummary.LastGamePlayed.AddDays(ConfigSettings.GameTimeOffSetInDays);
return weekSummary;
}
public void LoadFromDB(MySqlDataReader reader)
{
Items.Clear();
while (reader.Read())
{
Int32 itemNo = reader.GetInt32(0);
Int32 itemId = reader.GetInt32(1);
Item ch = new Item(_user, itemId, itemNo);
ch.PromotionId = reader.GetInt16(2);
ch.Quantity = reader.GetInt32(3);
Items.Add(ch);
}
}
public static ZaposleniDTO readerToZaposleni(MySqlDataReader reader)
{
ZaposleniDTO zapoleni = new ZaposleniDTO();
zapoleni.Id = reader.GetInt32("idZaposleni");
zapoleni.Ime = reader["ime"].ToString();
zapoleni.Prezime = reader["prezime"].ToString();
zapoleni.DatumRodjenja = reader.GetDateTime("datumRodjenja");
zapoleni.KorisnickoIme = reader["korisnickoIme"].ToString();
zapoleni.Lozinka = reader["lozinka"].ToString();
zapoleni.Telefon = reader["telefon"].ToString();
zapoleni.Email = reader["e_mail"].ToString();
zapoleni.Aktivan = reader.GetInt32("aktivan");
return zapoleni;
}
public Session createSessionFromDataReader(MySqlDataReader dr)
{
Session session;
String userLogin;
int code = 0;
DateTime date;
String dateString;
Video video;
String mode;
String comment;
float evaluetion;
if (dr.Equals(DBNull.Value)) return null;
if (!dr["comment"].Equals(DBNull.Value))
comment = dr.GetString("comment");
else
comment = "";
if (dr["code"].Equals(DBNull.Value)) return null;
code = dr.GetInt32("code");
if (dr["userLogin"].Equals(DBNull.Value)) return null;
userLogin = dr.GetString("userLogin");
if (!dr["evaluetion"].Equals(DBNull.Value))
evaluetion = (float)Convert.ToDouble(dr.GetDecimal("evaluetion"));
else
evaluetion = 0;
DaoVideo daoVideo = new DaoVideo();
daoVideo.openConnection();
video = daoVideo.getVideoByCode(dr.GetInt32("videoCode"));
date = dr.GetDateTime("date");
dateString = date.Day + "/" + date.Month + "/" + date.Year;
if (!dr["modeCode"].Equals(DBNull.Value))
mode = getModeByCode(dr.GetInt32("modeCode"));
else
mode = "";
session = new Session(userLogin, video, dateString, evaluetion, mode, comment);
session.setCode(code);
return session;
}
public List<Sector> LoadSector()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon=new Connection(this.user)).Parameters()))
{
listSec = new List<Sector>();
cn.Open();
sql = "select * from asada.view_sectores";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
sec = new Sector();
sec.Code = reader.GetString(0);
sec.Consecutive = reader.GetInt32(1);
sec.Description = reader.GetString(2);
listSec.Add(sec);
}
reader.Close();
return listSec;
}
}
catch (Exception)
{
throw;
}
}
public static StatusFilmDTO readerToStatusFilmDTO(MySqlDataReader reader)
{
StatusFilmDTO status = new StatusFilmDTO();
status.Id = reader.GetInt32("idStatusFilm");
status.Naziv = reader["nazivStatusFilm"].ToString();
return status;
}
public Values getValues(int id)
{
Values v = new Values();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
string sqlString = $"SELECT * FROM products WHERE id ={id}";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
if (mySQLReader.Read())
{
v.id = mySQLReader.GetInt32(0);
v.productName = mySQLReader.GetString(1);
v.productCategory = mySQLReader.GetString(2);
v.productDescription = mySQLReader.GetString(3);
v.productImage = mySQLReader.GetString(4);
return(v);
}
else
{
return(null);
}
}
public static RobaDTO readerToRobaDTO(MySqlDataReader reader)
{
RobaDTO roba = new RobaDTO();
roba.SifraRoba = reader.GetInt32("SifraRoba");
roba.Naziv = reader.GetString("Naziv");
return roba;
}
public void LoadFromDB(MySqlDataReader reader)
{
Items.Clear();
while (reader.Read())
{
Int32 characterNo = reader.GetInt32(0);
Int32 characterId = reader.GetInt32(1);
Character ch = new Character(_user, characterId, characterNo);
ch.Level = reader.GetInt16(2);
ch.Exp = reader.GetInt32(3);
ch.GradeId = reader.GetInt16(4);
ch.PromotionId = reader.GetInt16(5);
Items.Add(ch);
}
}
public void ChangeReviewSummary()
{
Transform starPanel = reviewSummaryPanel.GetChild(1);
Transform scorePanel = reviewSummaryPanel.GetChild(2);
//string sql = "SELECT COUNT(*), AVG(CAST(score as FLOAT)) FROM review GROUP BY productID HAVING productID = " + product.id + ";";
string sql = "SELECT COUNT(*), AVG(score) FROM review GROUP BY productID HAVING productID = " + product.id + ";";
MySql.Data.MySqlClient.MySqlDataReader reader = DbConnecter.instance.Reader(sql);
int reviewer = 0;
float score = 0;
if (reader.Read())
{
reviewer = reader.GetInt32(0);
score = (float)reader.GetDouble(1);
}
reader.Close();
DbConnecter.instance.CloseConnection();
for (int i = 0; i < 5; i++)
{
float fillAmount = Mathf.Clamp(score - i, 0, 1);
Image yellowStar = starPanel.GetChild(i).GetChild(0).GetComponent <Image>();
yellowStar.fillAmount = fillAmount;
}
Text scoreText = scorePanel.GetChild(0).GetComponent <Text>();
scoreText.text = score.ToString("0.00") + " (" + reviewer + ")";
}
public Dictionary<int, string> get_db_controlsamples_current(int id)
{
_connection.Open();
var controlsamples = new Dictionary<int, string>();
var myCommand = new MySqlCommand(@"
SELECT
controlsamples.Id_ControlSample,
controlsamples.NameControlSample
FROM
controlsamples
WHERE controlsamples.Id_SizeTube = @A
", _connection.MySqlConnection);
myCommand.Parameters.AddWithValue("A", id);
_mySqlDataReader = myCommand.ExecuteReader();
while (_mySqlDataReader.Read())
{
controlsamples.Add(_mySqlDataReader.GetInt32(0), _mySqlDataReader.GetString(1));
}
_mySqlDataReader.Close();
_connection.Close();
return controlsamples;
}
public List<Sector> LoadSector()
{
try
{
using (MySqlConnection cn = new MySqlConnection((clsCon = new Connection(this.user)).Parameters()))
{
listSec = new List<Sector>();
cn.Open();
sql = "select * from asada.view_sectores";
cmd = new MySqlCommand(sql, cn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
sec = new Sector();
sec.Code = reader.GetString(0);
sec.Consecutive = reader.GetInt32(1);
sec.Description = reader.GetString(2);
listSec.Add(sec);
}
reader.Close();
return listSec;
}
}
catch (Exception e)
{
Logs lg = new Logs();
lg.Log(DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss") + ", " + e.Message.ToString());
throw;
}
}
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 static PyObject DBColumnToPyObject(int index, ref MySqlDataReader reader)
{
Type type = reader.GetFieldType(index);
switch (type.Name)
{
case "String":
return new PyString(reader.GetString(index));
case "UInt32":
case "Int32":
case "UInt16":
case "Int16":
case "SByte":
case "Byte":
return new PyInt(reader.GetInt32(index));
case "UInt64":
case "Int64":
return new PyLongLong(reader.GetInt64(index));
case "Byte[]":
return new PyBuffer((byte[])reader.GetValue(index));
case "Double":
return new PyFloat(reader.GetDouble(index));
case "Decimal":
return new PyFloat((double)reader.GetDecimal(index));
case "Boolean":
return new PyBool(reader.GetBoolean(index));
default:
Log.Error("Database", "Unhandled MySQL type " + type.Name);
break;
}
return null;
}
public static int SafeGetInt(MySqlDataReader reader, string colName)
{
if (!reader.IsDBNull(reader.GetOrdinal(colName)))
return reader.GetInt32(colName);
else
return default(int);
}
public Customer getCustomer(long contactNo)
{
Customer p = new Customer();
MySqlConnection conn = mysqlDbConnect.GetConnection();
MySql.Data.MySqlClient.MySqlDataReader mySqlReade = null;
String sqlString = "SELECT * FROM customer WHERE contactNo = '" + contactNo.ToString() + "'";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReade = cmd.ExecuteReader();
if (mySqlReade.Read())
{
p.contactNo = mySqlReade.GetInt32(0);
p.firstName = mySqlReade.GetString(1);
p.lastName = mySqlReade.GetString(2);
p.email = mySqlReade.GetString(3);
p.address = mySqlReade.GetString(4);
p.password = mySqlReade.GetString(5);
return(p);
}
else
{
return(null);
}
}
public ArrayList getCustomer()
{
ArrayList CustomerArray = new ArrayList();
MySqlConnection conn = mysqlDbConnect.GetConnection();
MySql.Data.MySqlClient.MySqlDataReader mySqlReade = null;
String sqlString = "SELECT * FROM customer";
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySqlReade = cmd.ExecuteReader();
while (mySqlReade.Read())
{
Customer p = new Customer();
p.contactNo = mySqlReade.GetInt32(0);
p.firstName = mySqlReade.GetString(1);
p.lastName = mySqlReade.GetString(2);
p.email = mySqlReade.GetString(3);
p.address = mySqlReade.GetString(4);
p.password = mySqlReade.GetString(5);
CustomerArray.Add(p);
}
return(CustomerArray);
}
private bool DoSQLQueryTest()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
String queryStr = "";
queryStr = "SELECT COUNT(id) as countindex FROM database.metadata WHERE metadata.codeuser='******' ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
countindex = 0;
while (reader.HasRows && reader.Read())
{
countindex = reader.GetInt32(reader.GetOrdinal("countindex"));
}
if (countindex != 0)
{
return(true);
}
reader.Close();
conn.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
return(false);
}
protected void addOrder()
{
string allTitles = null;
int lastID = 0;
string price = totalPrice + "";
string emailCustomer = actualCustomer.Email;
for (int i = 0; i < movies.Count; i++)
{
allTitles = allTitles + movies[i].Title + "; ";
}
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebbAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
queryStr = "";
queryStr = "insert into bookings (totalPrice,bookingdate,user_email) values ('" + price + "',now(),'" + emailCustomer + "');SELECT LAST_INSERT_ID();";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.Read())
{
lastID = reader.GetInt32(reader.GetOrdinal("LAST_INSERT_ID()"));
}
conn.Close();
System.Diagnostics.Debug.WriteLine("Last id: " + lastID);
for (int j = 0; j < movies.Count; j++)
{
insertMoviesIntoBooking(lastID, Convert.ToInt32(movies[j].Id));
insertNewRateRow(Convert.ToInt32(movies[j].Id), emailCustomer);
}
}
protected Game GetGameFromDataReader(MySqlDataReader dataReader)
{
string gameNaam = dataReader.GetString("gamenaam");
int gameId = dataReader.GetInt32("game_id");
Game game = new Game { ID = gameId, Naam = gameNaam };
return game;
}
public void findAll()
{
db = new Database();
brands.Clear();
brandQuery = new MySqlCommand();
brandQuery.Connection = db.Connection();
brandQuery.CommandText = "SELECT * FROM brand ORDER BY brandname ASC";
this.brandResult = brandQuery.ExecuteReader();
while (brandResult.Read())
{
if (!brandResult.IsDBNull(0))
{
idbrand = brandResult.GetInt32(0);
}
if (!brandResult.IsDBNull(1))
{
brandname = brandResult.GetString(1);
}
brands.Add(new Brand { idbrand = this.idbrand, brandname = this.brandname});
}
db.Close();
}
// Get All The Patients (Select *)
public ArrayList GetPatients()
{
MySqlConnection conn;
conn = new MySqlConnection();
try
{
conn.ConnectionString = myConnectionString;
conn.Open();
ArrayList patientArray = new ArrayList();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
String sqlString = "SELECT * FROM patientinfo";
// Prepare Statement
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
// Execute Statement
mySQLReader = cmd.ExecuteReader();
while (mySQLReader.Read())
{
PatientInfo p = new PatientInfo
{
PatientId = mySQLReader.GetInt32(0),
Firstname = mySQLReader.GetString(1),
Lastname = mySQLReader.GetString(2),
PrimaryDoctor = mySQLReader.GetString(3),
SecondaryDoctor = mySQLReader.GetString(4),
PhoneNumber = mySQLReader.GetString(5),
Street = mySQLReader.GetString(6),
Number = mySQLReader.GetString(7),
PostalCode = mySQLReader.GetString(8),
City = mySQLReader.GetString(9),
EmergencyContact = mySQLReader.GetString(10),
EmergencyContactPhone = mySQLReader.GetString(11),
Sex = mySQLReader.GetString(12),
Weight = mySQLReader.GetString(13),
Height = mySQLReader.GetString(14),
Department = mySQLReader.GetString(15),
};
patientArray.Add(p);
}
return(patientArray);
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public Shipment getShipment(long shipmentId)
{
MySql.Data.MySqlClient.MySqlConnection conn;
string myConnectionString = ConfigurationManager.ConnectionStrings["PhpMySqlRemoteDB"].ConnectionString;
conn = new MySql.Data.MySqlClient.MySqlConnection();
try
{
conn.ConnectionString = myConnectionString;
conn.Open();
Shipment sh = new Shipment();
MySql.Data.MySqlClient.MySqlDataReader mySQLReader = null;
string sqlString = "SELECT * FROM Shipments WHERE ShipmentID = " + shipmentId.ToString();
MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(sqlString, conn);
mySQLReader = cmd.ExecuteReader();
if (mySQLReader.Read())
{
sh.ShipmentID = mySQLReader.GetInt32(0);
sh.TripID = mySQLReader.GetInt32(1);
sh.Username = mySQLReader.GetString(2);
sh.From_City_Country = mySQLReader.GetString(3);
sh.To_City_Country = mySQLReader.GetString(4);
sh.IWantItBefore = mySQLReader.GetDateTime(5);
sh.ShipmentName = mySQLReader.GetString(6);
sh.ShipmentNote = mySQLReader.GetString(7);
return(sh);
}
else
{
return(null);
}
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public override void Fill(MySql.Data.MySqlClient.MySqlDataReader dr)
{
_ID = dr.GetInt32(db_ID);
_Name = dr.GetString(db_Name);
_Abbreviation = dr.GetString(db_Abbreviation);
_RequiresRoom = dr.GetBoolean(db_RequiresRoom);
_RequiresDays = dr.GetBoolean(db_RequiresDays);
_RequiresTimes = dr.GetBoolean(db_RequiresTimes);
}
protected void DoSQLCountLike(object sender, EventArgs e)
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
String queryStr = "";
queryStr = "SELECT id FROM database.metadata WHERE metadata.codeuser='******' ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
idmetadata3 = reader.GetInt32(reader.GetOrdinal("id"));
}
reader.Close();
String queryStr3;
idLike = 0;
queryStr3 = "";
queryStr3 = "SELECT id FROM database.like WHERE like.idmetadata='" + idmetadata3 + "' AND like.iduser='******' ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr3, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
idLike = reader.GetInt32(reader.GetOrdinal("id"));
}
reader.Close();
if (idLike == 0)
{
String queryStr2 = "";
queryStr2 = "INSERT INTO database.like (iduser,idmetadata)VALUES('" + Session["Id"] + "','" + idmetadata3 + "')";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr2, conn);
cmd.ExecuteNonQuery();
}
conn.Close();
DoSQLQueryyIm();
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
private void qrScanFinished(string dataText)
{
if (isOpenBrowserIfUrl)
{
if (Utility.CheckIsUrlFormat(dataText))
{
if (!dataText.Contains("http://") && !dataText.Contains("https://"))
{
dataText = "http://" + dataText;
}
Application.OpenURL(dataText);
}
}
if (this.UiText != null)
{
this.UiText.text = dataText;
}
if (this.resetBtn != null)
{
this.resetBtn.SetActive(true);
}
if (this.scanLineObj != null)
{
this.scanLineObj.SetActive(false);
}
if (dataText.Length == 13)
{
string sql = "SELECT * FROM product WHERE barcode='" + dataText + "';";
MySql.Data.MySqlClient.MySqlDataReader reader = DbConnecter.instance.Reader(sql);
if (reader.Read())
{
Product product = new Product();
product.id = reader.GetInt32(0);
product.name = reader.GetString(1);
product.barcode = reader.GetString(2);
product.company = reader.GetString(3);
product.category = product.koreanToCategory(reader.GetString(4));
product.ingredients = reader.GetString(5);
product.imagePath = reader.GetString(6);
reader.Close();
DbConnecter.instance.CloseConnection();
Stop();
Panels.instance.productInfoPanel.gameObject.SetActive(true);
Panels.instance.productInfoPanel.PanelOn(product, Panels.instance.homePanel.gameObject);
}
else if (this.isNullPanel != null)
{
isNullPanel.SetActive(true);
DbConnecter.instance.CloseConnection();
}
}
else if (this.isNullPanel != null)
{
isNullPanel.SetActive(true);
}
}
/*
* syntheticDetergent = 0, //합성세제
* bleach = 1, //표백제
* softner = 2, //섬유유연제
* freshener = 3, //방향제
* deodorant = 4, //탈취제
* detergent = 5 //세정제
*/
public void PanelOn(Category category)
{
Transform body = transform.GetChild(1);
content = body.GetChild(0).GetComponent <RectTransform>();
panelSize = body.GetComponent <RectTransform>().rect.size;
string categoryKorean = "";
if (category == Category.syntheticDetergent)
{
categoryKorean = "합성세제";
}
else if (category == Category.bleach)
{
categoryKorean = "표백제";
}
else if (category == Category.softner)
{
categoryKorean = "섬유유연제";
}
else if (category == Category.freshener)
{
categoryKorean = "방향제";
}
else if (category == Category.deodorant)
{
categoryKorean = "탈취제";
}
else if (category == Category.detergent)
{
categoryKorean = "세정제";
}
string sql = "SELECT * FROM product WHERE category = '" + categoryKorean + "';";
int idCount = DbConnecter.instance.Count("id", "product", "category = '" + categoryKorean + "'");
List <ProductUnit> units = AddUnits(idCount);
MySql.Data.MySqlClient.MySqlDataReader reader = DbConnecter.instance.Reader(sql);
int number = 0;
while (reader.Read())
{
Product product = new Product();
product.id = reader.GetInt32(0);
product.name = reader.GetString(1);
product.barcode = reader.GetString(2);
product.company = reader.GetString(3);
product.ingredients = reader.GetString(5);
product.imagePath = reader.GetString(6);
product.category = category;
units[number].InfoChange(product, gameObject);
number++;
}
reader.Close();
DbConnecter.instance.CloseConnection();
}
private void DoSQLQueryyIm()
{
try
{
String connString = System.Configuration.ConfigurationManager.ConnectionStrings["WebAppConnString"].ToString();
conn = new MySql.Data.MySqlClient.MySqlConnection(connString);
conn.Open();
String queryStr = "";
queryStr = "SELECT * FROM database.metadata WHERE metadata.codeuser='******' ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr, conn);
reader = cmd.ExecuteReader();
// z01 = "";
userName = "";
datee = "";
contentmeta = "";
while (reader.HasRows && reader.Read())
{
userName = reader.GetString(reader.GetOrdinal("username"));
iduser = reader.GetString(reader.GetOrdinal("iduser"));
idmetadata3 = reader.GetInt32(reader.GetOrdinal("id"));
datee = reader.GetString(reader.GetOrdinal("date"));
contentmeta = reader.GetString(reader.GetOrdinal("content"));
}
if (reader.HasRows)
{
// Label3.Text = z01;
Image2.ImageUrl = "~/ShowImage.ashx?id=" + iduser;
Image3.ImageUrl = "~/ShowMetadata.ashx?codeuser="******"urole"];
Image4.ImageUrl = "~/ShowImage.ashx?id=" + Session["Id"];
LabelProfil.Text = userName;
LabelDate.Text = datee;
LabelContent.Text = contentmeta;
}
reader.Close();
String queryStr1 = "";
queryStr1 = "SELECT COUNT(id) as countlike FROM database.like WHERE like.idmetadata='" + idmetadata3 + "' ";
cmd = new MySql.Data.MySqlClient.MySqlCommand(queryStr1, conn);
reader = cmd.ExecuteReader();
while (reader.HasRows && reader.Read())
{
countlike = reader.GetString(reader.GetOrdinal("countlike"));
}
LabelLike.Text = countlike;
conn.Close();
}
catch (Exception e)
{
Console.WriteLine(e);
}
}
static void Main(string[] args)
{
string cs = "server=localhost;userid=root;password=;database=employees";
using var connexion = new MySql.Data.MySqlClient.MySqlConnection(cs);
connexion.Open();
Console.WriteLine($"MySQL Version: {connexion.ServerVersion}");
var stm = "SELECT * FROM salaries LIMIT 10";
var cmd = new MySql.Data.MySqlClient.MySqlCommand(stm, connexion);
using MySql.Data.MySqlClient.MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Console.WriteLine("{0} {1} {2} {3}", rdr.GetInt32(0), rdr.GetInt32(1), rdr.GetDateTime(2), rdr.GetDateTime(3));
}
}
