public List<Zone> GetZoneWiseVisitors(out int zoneWiseTotal) { SqlConnection connection = new SqlConnection(connectionString); List<Zone> zones = new List<Zone>(); string query = string.Format(@"select tbl_ZoneType.ZoneType , COUNT(tbl_Visitor.Email) from tbl_ZoneType join tbl_VisitorZone on tbl_ZoneType.ID = tbl_VisitorZone.ZID join tbl_Visitor on tbl_VisitorZone.VID = tbl_Visitor.ID group by tbl_Zonetype.ZoneType"); SqlCommand cmd = new SqlCommand(query,connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Zone zone = new Zone(); zone.ZoneType = reader["ZoneType"].ToString(); zone.zoneWiseTotalVisitor = Convert.ToInt16(reader[1]); zones.Add(zone); } connection.Close(); zoneWiseTotal = GetTotalVisitor(); return zones; }
public string Save(Zone aZone) { if(gateway.HasZonExists(aZone.ZoneType)) { return "Zone Type Already Exists"; } else { return gateway.Save(aZone); } }
public List<Zone> LoadCheckedListBox() { List<Zone> zoneList = new List<Zone>(); SqlConnection connection = new SqlConnection(connectionString); string query = string.Format("SELECT * FROM tbl_ZoneType "); SqlCommand cmd = new SqlCommand(query, connection); connection.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Zone aZone = new Zone(); aZone.ID = Convert.ToInt32(reader["ID"]); aZone.ZoneType = reader["ZoneType"].ToString(); zoneList.Add(aZone); } reader.Close(); connection.Close(); return zoneList; }
public string Save(Zone aZone) { string message = ""; SqlConnection connection = new SqlConnection(connectionString); string query = string.Format("INSERT INTO tbl_ZoneType VALUES('{0}')",aZone.ZoneType); SqlCommand command = new SqlCommand(query, connection); connection.Open(); command.ExecuteNonQuery(); connection.Close(); message = aZone.ZoneType + " has been saved"; return message; }