public static string DeleteFunctie(ContactpersonType s)
{
DbDataReader reader= Database.GetData("SELECT * FROM tbl_contacts,tbl_contactType WHERE tbl_contactType.ID=tbl_contacts.JobRole AND tbl_contactType.JobRole=@name",Database.AddParameter("@name",s.Name));
if (reader.HasRows)
{
return "Gelieve eerste alle Contactpersonen met deze functie te verwijderen";
}
else
{
Database.ModifyData("DELETE FROM tbl_contactType WHERE ID=@id",Database.AddParameter("@id",Convert.ToInt32(s.ID)));
}
return null;
}
public static ObservableCollection<Contactperson> ZoekContact(string _contactName, string _contactCompany, ContactpersonType _contactJobRole)
{
string jobrole = "";
if (_contactJobRole != null)
{
jobrole = _contactJobRole.Name;
}
else {
jobrole = "";
}
ObservableCollection<Contactperson> contacts = new ObservableCollection<Contactperson>();
DbDataReader reader = Database.GetData("SELECT tbl_contacts.ID as ContactID,ContactName,Company,City,Email,Phone,CellPhone,tbl_contactType.ID as TypeID,tbl_contactType.JobRole FROM tbl_contacts, tbl_contactType WHERE tbl_contacts.JobRole=tbl_contactType.ID and ContactName LIKE @name AND Company LIKE @company AND tbl_contactType.JobRole LIKE @jobrole",
Database.AddParameter("@name","%"+ _contactName +"%"),
Database.AddParameter("@company","%"+ _contactCompany +"%"),
Database.AddParameter("@jobrole","%"+ jobrole +"%")
);
foreach (IDataRecord db in reader)
{
contacts.Add(Create(db));
}
return contacts;
}
public static void EditContact(string _name, string _company, ContactpersonType _jobRole, string _city, string _email, string _phone, string _cellphone,string _id)
{
Database.ModifyData("UPDATE tbl_contacts SET ContactName=@name,Company=@company,JobRole=@jobrole,City=@city,Email=@email,Phone=@phone,CellPhone=@cellphone WHERE ID=@id",
Database.AddParameter("@id",Convert.ToInt32(_id)),
Database.AddParameter("@name", _name),
Database.AddParameter("@company", _company),
Database.AddParameter("@jobrole", Convert.ToInt32(_jobRole.ID)),
Database.AddParameter("@city", _city),
Database.AddParameter("@email", _email),
Database.AddParameter("@phone", _phone),
Database.AddParameter("@cellphone", _cellphone)
);
}
public static void AddContact(string _name,string _company,ContactpersonType _jobRole,string _city,string _email,string _phone,string _cellphone)
{
Database.ModifyData("INSERT INTO tbl_contacts (ContactName,Company,JobRole,City,Email,Phone,CellPhone) VALUES(@name,@company,@jobrole,@city,@email,@phone,@cellphone)",
Database.AddParameter("@name",_name),
Database.AddParameter("@company",_company),
Database.AddParameter("@jobrole",Convert.ToInt32(_jobRole.ID)),
Database.AddParameter("@city",_city),
Database.AddParameter("@email",_email),
Database.AddParameter("@phone",_phone),
Database.AddParameter("@cellphone",_cellphone)
);
}
public static void EditType(ContactpersonType s,string _name)
{
Database.ModifyData("UPDATE tbl_contactType SET JobRole=@name WHERE ID=@id", Database.AddParameter("@id", Convert.ToInt32(s.ID)), Database.AddParameter("@name", _name));
}