public List<Visitor> GetAllVisitorInSpecificZone(string zoneType)
{
SqlConnection connection = new SqlConnection(connectionString);
List<Visitor> visitors = new List<Visitor>();
string query = string.Format(@"select tbl_Visitor.Name , tbl_Visitor.Email , tbl_Visitor.ContactNumber
from tbl_Visitor join tbl_VisitorZone on tbl_Visitor.ID = tbl_VisitorZone.VID
join tbl_ZoneType on tbl_ZoneType.ID = tbl_VisitorZone.ZID
where tbl_ZoneType.Zonetype ='{0}'", zoneType);
SqlCommand cmd = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Visitor visitor = new Visitor();
visitor.Name = reader[0].ToString();
visitor.Email = reader[1].ToString();
visitor.ContactNumber =reader[2].ToString();
visitors.Add(visitor);
}
connection.Close();
return visitors;
}
public int SaveVisitor(Visitor aVisitor)
{
return vGateway.SaveVisitor(aVisitor);
}
public int SaveVisitor(Visitor aVisitor)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = string.Format("INSERT INTO tbl_Visitor OUTPUT INSERTED.ID VALUES('{0}','{1}','{2}')",aVisitor.Name,aVisitor.Email,aVisitor.ContactNumber);
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
int rowsInserted = (int) command.ExecuteScalar();
connection.Close();
return rowsInserted; ;
}