public override void Load() { DBInterface.CommandText = "select " + "document.name, " + "document.GUID, " + "document.extension, " + "persondocument.description, " + "from persondocument " + "inner join document " + "on document.idDocument = persondocument.idDocument " + "where document.idDocument = @id and persondocument.idPerson = @idPerson;"; DBInterface.AddParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32, ID); DBInterface.AddParameter("@idPerson", MySql.Data.MySqlClient.MySqlDbType.Int32, person.ID); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count == 1) { Name = Convert.ToString(tab.Rows[0]["name"]); guid = Convert.ToString(tab.Rows[0]["GUID"]); Extension = Convert.ToString(tab.Rows[0]["extension"]); Description = Convert.ToString(tab.Rows[0]["description"]); } else if (tab.Rows.Count > 1) { throw new DuplicateNameException("Attached file to person table has rows with same id"); } Changed = false; }
public void TableRowsCounterTest2() { DBInterface.CommandText = "SELECT Name FROM person"; DataTable tab = DBInterface.ExecuteSelection(); int numberBefore = tab.Rows.Count; DBInterface.CommandText = "INSERT INTO person (Name) VALUES (@name)"; DBInterface.AddParameter("@name", MySqlDbType.String, "Bob"); DBInterface.ExecuteTransaction(); DBInterface.CommandText = "INSERT INTO person (Name) VALUES (@name)"; DBInterface.AddParameter("@name", MySqlDbType.String, "Alisa"); DBInterface.ExecuteTransaction(); DBInterface.CommandText = "SELECT Name FROM person"; tab = DBInterface.ExecuteSelection(); int numberAfter = tab.Rows.Count; DBInterface.CommandText = "DELETE FROM person where (name = @nameA) OR (name = @nameB)"; DBInterface.AddParameter("@nameA", MySqlDbType.String, "Bob"); DBInterface.AddParameter("@nameB", MySqlDbType.String, "Alisa"); DBInterface.ExecuteTransaction(); Assert.AreEqual(numberAfter - numberBefore, 2, "Number of rows in People table has changed"); }
public override void Load() { Clear(); DBInterface.CommandText = "SELECT * FROM sellcontroller.milecardstatus;"; DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { MileCardStatus mcs = new MileCardStatus(); mcs.ID = Convert.ToInt32(row["idMileCardStatus"]); mcs.Name = row["status"].ToString(); mcs.MinVal = Convert.ToInt32(row["minMiles"]); mcs.MaxVal = Convert.ToInt32(row["maxMiles"]); if (row["idAviaCompanyUnion"] != DBNull.Value) { mcs.AviaCompanyUnionID = Convert.ToInt32(row["idAviaCompanyUnion"]); } if (row["idAviaCompany"] != DBNull.Value) { mcs.AviaCompanyID = Convert.ToInt32(row["idAviaCompany"]); } mcs.Note = row["note"].ToString(); this.Add(mcs); } }
public void AddElement(AviaCompany ac, AviaCompanyUnion acu) { DBInterface.CommandText = "select * " + "from joinaviacompanyunion " + "where joinaviacompanyunion.idAviaCompany = @idAviaCompany " + "and joinaviacompanyunion.idAviaCompanyUnion = @idAviaCompanyUnion;"; DBInterface.AddParameter("@idAviaCompany", MySql.Data.MySqlClient.MySqlDbType.Int32, ac.ID); DBInterface.AddParameter("@idAviaCompanyUnion", MySql.Data.MySqlClient.MySqlDbType.Int32, acu.ID); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count == 0) { DBInterface.StoredProcedure("join_avia_company_union_insert"); DBInterface.AddParameter("@inIdAviaCompanyUnion", MySql.Data.MySqlClient.MySqlDbType.Int32, acu.ID); DBInterface.AddParameter("@inIdAviaCompany", MySql.Data.MySqlClient.MySqlDbType.Int32, ac.ID); DBInterface.AddParameter("@inNote", MySql.Data.MySqlClient.MySqlDbType.String, ""); DBInterface.AddOutParameter("@outIdJoinAviaCompanyUnion", MySql.Data.MySqlClient.MySqlDbType.Int32); DBInterface.ExecuteTransaction(); } }
public void AddElement(Country country, CountryUnion countryUnion) { DBInterface.CommandText = "select * " + "from joincountryunion " + "where joincountryunion.idCountry = @idCountry " + "and joincountryunion.idCountryUnion = @idCountryUnion;"; DBInterface.AddParameter("@idCountry", MySql.Data.MySqlClient.MySqlDbType.Int32, country.ID); DBInterface.AddParameter("@idCountryUnion", MySql.Data.MySqlClient.MySqlDbType.Int32, countryUnion.ID); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count == 0) { DBInterface.StoredProcedure("join_Country_union_insert"); DBInterface.AddParameter("@inIdCountryUnion", MySql.Data.MySqlClient.MySqlDbType.Int32, countryUnion.ID); DBInterface.AddParameter("@inIdCountry", MySql.Data.MySqlClient.MySqlDbType.Int32, country.ID); DBInterface.AddParameter("@inNote", MySql.Data.MySqlClient.MySqlDbType.String, ""); DBInterface.AddOutParameter("@outIdJoinCountryUnion", MySql.Data.MySqlClient.MySqlDbType.Int32); DBInterface.ExecuteTransaction(); } }
public void Load(Country country) { Clear(); if (country == null) { return; } DBInterface.CommandText = "select " + "countryunion.idCountryUnion, " + "countryunion.UnionName, " + "countryunion.shortUnionName, " + "countryunion.note " + "from joincountryunion " + "left join countryunion " + "on joincountryunion.idCountryUnion = countryunion.idCountryUnion " + "where joincountryunion.idCountry = @idCountry"; DBInterface.AddParameter("@idCountry", MySql.Data.MySqlClient.MySqlDbType.Int32, country.ID); DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { CountryUnion item = Create(); item.ID = Convert.ToInt32(row["idCountryUnion"]); item.Name = row["UnionName"].ToString(); item.ShortName = row["shortUnionName"].ToString(); item.Note = row["note"].ToString(); } UpdateMirror(); }
public override void Load() { Clear(); DBInterface.CommandText = "select " + "contact.idContact, " + "typecontact.nameContact, " + "contact.value, " + "contact.description " + "from contact " + "left join person " + "on contact.idPerson = person.idPerson " + "left join typecontact " + "on contact.idTypeContact = typecontact.idTypeContact " + "where " + "person.idPerson = @id;"; DBInterface.AddParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32, person.ID); DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { Contact item = Contact.Create(Convert.ToString(row["nameContact"])); item.ID = Convert.ToInt32(row["idContact"]); item.Content = Convert.ToString(row["value"]); item.Description = Convert.ToString(row["description"]); Add(item); } }
public override void Load() { Clear(); DBInterface.CommandText = "select " + "document.name, " + "document.GUID, " + "document.extension, " + "document.idDocument, " + "persondocument.description " + "from persondocument " + "inner join document " + "on document.idDocument = persondocument.idDocument " + "where persondocument.idPerson = @idPerson;"; DBInterface.AddParameter("@idPerson", MySql.Data.MySqlClient.MySqlDbType.Int32, person.ID); DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { AttachFilePerson atp = Create(); atp.ID = Convert.ToInt32(row["idDocument"]); atp.Name = Convert.ToString(row["name"]); atp.guid = Convert.ToString(row["GUID"]); atp.Extension = Convert.ToString(row["extension"]); atp.Description = Convert.ToString(row["description"]); Add(atp); } }
public void Load() { Changed = false; DBInterface.CommandText = "select ownerName, ownerSurname, number, idCitizen, idCountry, expireDate, note, isActive from passport where idPassport = @id;"; DBInterface.AddParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32, ID); DataTable tab = DBInterface.ExecuteSelection(); if ((tab != null) && (tab.Rows.Count == 1)) { PersonName = tab.Rows[0]["ownerName"].ToString(); PersonSurname = tab.Rows[0]["ownerSurname"].ToString(); SerialNumber = tab.Rows[0]["number"].ToString(); ValidTill = Convert.ToDateTime(tab.Rows[0]["expireDate"]); Description = tab.Rows[0]["note"].ToString(); int idCountry = Convert.ToInt32(tab.Rows[0]["idCountry"]); CountryOfEmmitation = new Country(); CountryOfEmmitation.ID = idCountry; CountryOfEmmitation.Load(); int idCitizen = Convert.ToInt32(tab.Rows[0]["idCitizen"]); Citizen = new Country(); Citizen.ID = idCountry; Citizen.Load(); vizaList = null; } }
public bool Check() { try { DBInterface.CommandText = "SELECT * FROM sellcontroller.user WHERE login = @name;"; DBInterface.AddParameter("@name", MySql.Data.MySqlClient.MySqlDbType.String, Name); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count == 0) { return(false); } if (tab.Rows.Count > 1) { throw new ArgumentException("User with current id is not unique."); } ID = Convert.ToInt32(tab.Rows[0]["idUser"]); if (tab.Rows[0]["hashcode"].ToString() == PassHash()) { return(true); } else { return(false); } } catch { return(false); } }
public void Load(AviaCompany ac) { Clear(); if (ac == null) { return; } DBInterface.CommandText = "select " + "aviacompanyunion.idAviaCompanyUnion, " + "aviacompanyunion.UnionName, " + "aviacompanyunion.note " + "from joinaviacompanyunion " + "left join aviacompanyunion " + "on joinaviacompanyunion.idAviaCompanyUnion = aviacompanyunion.idAviaCompanyUnion " + "where joinaviacompanyunion.idAviaCompany = @idAviaCompany"; DBInterface.AddParameter("@idAviaCompany", MySql.Data.MySqlClient.MySqlDbType.Int32, ac.ID); DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { AviaCompanyUnion acu = new AviaCompanyUnion(); acu.ID = Convert.ToInt32(row["idAviaCompanyUnion"]); acu.Name = row["UnionName"].ToString(); acu.Note = row["note"].ToString(); this.Add(acu); } UpdateMirror(); }
public override void Load() { DBInterface.CommandText = "SELECT `people`.`idPeople`," + "`people`.`idPerson`," + "`people`.`firstName`," + "`people`.`middleName`," + "`people`.`lastName`," + "`people`.`birthDate`," + "`people`.`Note`," + "`people`.`itn`" + "FROM `sellcontroller`.`people` WHERE `idPeople` = @id;"; DBInterface.AddParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32, ID); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count == 1) { FirstName = Convert.ToString(tab.Rows[0]["firstName"]); SecondName = Convert.ToString(tab.Rows[0]["lastName"]); MiddleName = Convert.ToString(tab.Rows[0]["middleName"]); Birth = Convert.ToDateTime(tab.Rows[0]["birthDate"]); Description = Convert.ToString(tab.Rows[0]["Note"]); } else if (tab.Rows.Count > 1) { throw new DuplicateNameException("People table has rows with same id"); } ContactList.Load(); Changed = false; }
private bool IsUserEmpty() { DBInterface.CommandText = "SELECT * FROM sellcontroller.user;"; DataTable tab = DBInterface.ExecuteSelection(); return(tab.Rows.Count == 0); }
public override void Save() { if (Changed) { if (ID >= 0) { DBInterface.CommandText = "UPDATE `sellcontroller`.`company` " + "SET " + "`officialCompanyName` = @name, " + "`MFO` = @mfo, " + "`edrpou` = @edrpou, " + "`account` = @account, " + "`bankName` = @bankName, " + "`note` = @desc " + "WHERE `idCompany` = @id;"; DBInterface.AddParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32, CompanyID); DBInterface.AddParameter("@name", MySql.Data.MySqlClient.MySqlDbType.String, FullName); DBInterface.AddParameter("@desc", MySql.Data.MySqlClient.MySqlDbType.String, Description); DBInterface.AddParameter("@mfo", MySql.Data.MySqlClient.MySqlDbType.String, MFO); DBInterface.AddParameter("@edrpou", MySql.Data.MySqlClient.MySqlDbType.String, EDRPOU); DBInterface.AddParameter("@account", MySql.Data.MySqlClient.MySqlDbType.String, Account); DBInterface.AddParameter("@bankName", MySql.Data.MySqlClient.MySqlDbType.String, BankName); DBInterface.ExecuteTransaction(); RaiseUpdated(false); } else { DBInterface.CommandText = "insert into person (isPeople) values (0); " + "insert into company (idPerson, officialCompanyName, note, MFO, edrpou, account, bankName) " + "values (LAST_INSERT_ID(), @name, @desc, @mfo, @edrpou, @account, @bankName);"; DBInterface.AddParameter("@name", MySql.Data.MySqlClient.MySqlDbType.String, FullName); DBInterface.AddParameter("@desc", MySql.Data.MySqlClient.MySqlDbType.String, Description); DBInterface.AddParameter("@mfo", MySql.Data.MySqlClient.MySqlDbType.String, MFO); DBInterface.AddParameter("@edrpou", MySql.Data.MySqlClient.MySqlDbType.String, EDRPOU); DBInterface.AddParameter("@account", MySql.Data.MySqlClient.MySqlDbType.String, Account); DBInterface.AddParameter("@bankName", MySql.Data.MySqlClient.MySqlDbType.String, BankName); CompanyID = Convert.ToInt32(DBInterface.ExecuteTransaction()); DBInterface.CommandText = "select idPerson from company where idCompany = @id"; DBInterface.AddParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32, CompanyID); DataTable tab = DBInterface.ExecuteSelection(); ID = Convert.ToInt32(tab.Rows[0]["idPerson"]); RaiseUpdated(true); } Changed = false; } }
static public PersonGeneral Create(int PersonID) { PersonGeneral personGen = null; DBInterface.CommandText = "select " + "person.idPerson, " + "people.idPeople, " + "company.idCompany, " + "people.firstName, " + "people.lastName, " + "people.middleName, " + "company.officialCompanyName, " + "person.isPeople " + "from person " + "left join people " + "on people.idPerson = person.idPerson " + "left join company " + "on company.idPerson = person.idPerson " + "where person.idPerson = @id;"; DBInterface.AddParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32, PersonID); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count == 1) { DataRow row = tab.Rows[0]; if (row["isPeople"].ToString() == "1") { personGen = new Person(); Person person = personGen as Person; person.ID = Convert.ToInt32(row["idPerson"]); person.PersonID = Convert.ToInt32(row["idPeople"]); person.FirstName = row["firstName"].ToString(); person.SecondName = row["lastName"].ToString(); person.MiddleName = row["middleName"].ToString(); } else { personGen = new Company(); Company company = personGen as Company; company.ID = Convert.ToInt32(row["idPerson"]); company.CompanyID = Convert.ToInt32(row["idCompany"]); company.FullName = row["officialCompanyName"].ToString(); } } return(personGen); }
public override void Load() { Clear(); DBInterface.CommandText = "SELECT * FROM sellcontroller.visa where idPassport = @idPassport;"; DBInterface.AddParameter("@idPassport", MySql.Data.MySqlClient.MySqlDbType.Int32, passport.ID); DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { Viza viza = Create(); viza.ID = Convert.ToInt32(row["idVisa"]); viza.Load(); } }
public void Load() { DBInterface.CommandText = "SELECT * FROM sellcontroller.user WHERE login = @name;"; DBInterface.AddParameter("@name", MySql.Data.MySqlClient.MySqlDbType.String, Name); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count != 1) { throw new ArgumentException("User with current id is not unique."); } FullName = Convert.ToString(tab.Rows[0]["Name"]); role.SetByInt(Convert.ToInt32(tab.Rows[0]["isAdminRole"])); }
public override void Load() { Clear(); DBInterface.CommandText = "SELECT * FROM sellcontroller.milecard where idOwnerPerson = @idPerson;"; DBInterface.AddParameter("@idPerson", MySql.Data.MySqlClient.MySqlDbType.Int32, personOwner.ID); DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { MileCard mc = Create(); mc.ID = Convert.ToInt32(row["idMileCard"]); mc.Load(); this.Add(mc); } }
public override void Load() { Clear(); DBInterface.CommandText = "SELECT * FROM sellcontroller.country"; DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { Country item = new Country() { ID = Convert.ToInt32(row["idCountry"]), Name = Convert.ToString(row["nameCountry"]) }; Add(item); } }
private int ContactTypeID() { DBInterface.CommandText = "select idTypeContact from typecontact where nameContact = @name"; DBInterface.AddParameter("@name", MySql.Data.MySqlClient.MySqlDbType.String, contactType); DataTable tab = DBInterface.ExecuteSelection(); if ((tab != null) && (tab.Rows.Count == 1)) { return(Convert.ToInt32(tab.Rows[0]["idTypeContact"])); } else { throw new ArgumentException("Unhandled contacy type"); } }
public override void Load() { Clear(); DBInterface.CommandText = "select " + "person.idPerson, " + "people.idPeople, " + "company.idCompany, " + "people.firstName, " + "people.lastName, " + "people.middleName, " + "company.officialCompanyName, " + "person.isPeople " + "from person " + "left join people " + "on people.idPerson = person.idPerson " + "left join company " + "on company.idPerson = person.idPerson;"; DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { if ((row["isPeople"].ToString() == "1") && (row["idPeople"] != DBNull.Value)) { Person person = new Person(); person.ID = Convert.ToInt32(row["idPerson"]); person.PersonID = Convert.ToInt32(row["idPeople"]); person.FirstName = row["firstName"].ToString(); person.SecondName = row["lastName"].ToString(); person.MiddleName = row["middleName"].ToString(); person.labelList.Load(); this.Add(person); } else if (row["idCompany"] != DBNull.Value) { Company company = new Company(); company.ID = Convert.ToInt32(row["idPerson"]); company.CompanyID = Convert.ToInt32(row["idCompany"]); company.FullName = row["officialCompanyName"].ToString(); company.labelList.Load(); this.Add(company); } } }
static public GraphDirected CreateParentGraph() { GraphDirected graph = new GraphDirected(); DBInterface.CommandText = "SELECT * FROM sellcontroller.label"; DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { if (!(row["idParent"] is DBNull)) { graph.Add(Convert.ToInt32(row["idLabel"]), Convert.ToInt32(row["idParent"])); } } return(graph); }
public void Load() { DBInterface.CommandText = "SELECT `contact`.`idContact`, `contact`.`value` FROM `sellcontroller`.`contact` WHERE `idContact` = @id;"; DBInterface.AddParameter("@id", MySql.Data.MySqlClient.MySqlDbType.Int32, ID); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count == 1) { Content = Convert.ToString(tab.Rows[0]["value"]); } else if (tab.Rows.Count > 1) { throw new DuplicateNameException("Contact table has rows with same id"); } Changed = false; }
public override void Load() { Clear(); throw new NotImplementedException("Waiting for stored procedure"); DBInterface.CommandText = ""; DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { Contact item = Contact.Create(Convert.ToString(row["nameContact"])); item.ID = Convert.ToInt32(row["idContact"]); item.Content = Convert.ToString(row["value"]); item.Description = Convert.ToString(row["note"]); Add(item); } }
public override void Load() { Clear(); DBInterface.CommandText = "SELECT * from sellcontroller.countryunion"; DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { CountryUnion item = Create(); item.ID = Convert.ToInt32(row["idCountryUnion"]); item.Name = row["UnionName"].ToString(); item.ShortName = row["shortUnionName"].ToString(); item.Note = row["note"].ToString(); } }
private void DeleteJoin() { DBInterface.CommandText = "select * " + "from joinaviacompanyunion " + "where joinaviacompanyunion.idAviaCompanyUnion = @idAviaCompanyUnion;"; DBInterface.AddParameter("@idAviaCompanyUnion", MySql.Data.MySqlClient.MySqlDbType.Int32, ID); DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { DBInterface.StoredProcedure("join_avia_company_union_delete"); DBInterface.AddParameter("@inIdJoinAviaCompanyUnion", MySql.Data.MySqlClient.MySqlDbType.Int32, Convert.ToInt32(row["idJoinAviaCompanyUnion"])); DBInterface.ExecuteTransaction(); } }
private void Load(string name) { DBInterface.CommandText = "SELECT * FROM `sellcontroller`.`label` WHERE `name` = @name;"; DBInterface.AddParameter("@name", MySql.Data.MySqlClient.MySqlDbType.String, name); DataTable tab = DBInterface.ExecuteSelection(); if (tab.Rows.Count == 1) { ID = Convert.ToInt32(tab.Rows[0]["idLabel"]); Name = Convert.ToString(tab.Rows[0]["name"]); Comment = Convert.ToString(tab.Rows[0]["comment"]); SetColor(tab.Rows[0]["color"]); } else if (tab.Rows.Count > 1) { throw new DuplicateNameException("Label table has rows with same name"); } }
public override void Load() { Clear(); DBInterface.CommandText = "SELECT * FROM sellcontroller.aviacompanyunion;"; DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { AviaCompanyUnion acu = new AviaCompanyUnion(); acu.ID = Convert.ToInt32(row["idAviaCompanyUnion"]); acu.Name = row["UnionName"].ToString(); acu.Note = row["note"].ToString(); this.Add(acu); } }
public void RemoveElement(Country country, CountryUnion countryUnion) { DBInterface.CommandText = "select * " + "from joincountryunion " + "where joincountryunion.idCountry = @idCountry " + "and joincountryunion.idCountryUnion = @idCountryUnion;"; DBInterface.AddParameter("@idCountry", MySql.Data.MySqlClient.MySqlDbType.Int32, country.ID); DBInterface.AddParameter("@idCountryUnion", MySql.Data.MySqlClient.MySqlDbType.Int32, countryUnion.ID); DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { DBInterface.StoredProcedure("join_Country_union_delete"); DBInterface.AddParameter("@inIdJoinCountryUnion", MySql.Data.MySqlClient.MySqlDbType.Int32, Convert.ToInt32(row["idJoinCountryUnion"])); DBInterface.ExecuteTransaction(); } }
public override void Load() { Clear(); DBInterface.CommandText = "SELECT * FROM sellcontroller.aviacompany;"; DataTable tab = DBInterface.ExecuteSelection(); foreach (DataRow row in tab.Rows) { AviaCompany ac = new AviaCompany(); ac.ID = Convert.ToInt32(row["idAviaCompany"]); ac.FullName = row["name"].ToString(); ac.ICAO = row["shortName"].ToString(); ac.Description = row["note"].ToString(); this.Add(ac); } }