public List<Visitor> GetVisitorsList(string name)
{
int id = GetZoneId(name);
List<Visitor> visitors = new List<Visitor>();
SqlConnection connection = new SqlConnection(connectionString);
string query =
"SELECT tbl_Visitor.v_Name,tbl_Visitor.v_Email,tbl_Visitor.v_ContactNo FROM tbl_Visitor JOIN tbl_Visit ON tbl_Visitor.v_Id=tbl_Visit.visitor_Id JOIN tbl_Zone ON tbl_Visit.zone_Id=tbl_Zone.z_Id WHERE tbl_Zone.z_Id='"+id+"'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Visitor visitor = new Visitor();
visitor.Name = reader[0].ToString();
visitor.Email = reader[1].ToString();
visitor.ContactNo = reader[2].ToString();
//MessageBox.Show(visitor.Name);
visitors.Add(visitor);
}
reader.Close();
connection.Close();
return visitors;
}
private void saveVisitorButton_Click(object sender, EventArgs e)
{
GetAllCheckBoxes();
Visitor visitor = new Visitor();
visitor.Name = nameTextBox.Text;
visitor.Email = emailTextBox.Text;
visitor.ContactNo = contactNoTextBox.Text;
MessageBox.Show(visitorManager.Save(visitor));
GetTextBoxesClear();
}
public string Save(Visitor visitor)
{
if (visitor.Name == string.Empty)
{
return "Please Enter Visitor Name";
}
else if (visitor.Email == string.Empty)
{
return "Please Enter Visitor Email";
}
else if (visitor.ContactNo == string.Empty)
{
return "Please Enter Visitor Contact Number";
}
else if(visitorGateway.IsThisEmailExists(visitor.Email))
{
return "This Email Id already Exists.Try Again!!";
}
else
{
int value = visitorGateway.Save(visitor);
if (value > 0)
{
return "Visitor Information Saved Successfully";
}
else
{
return "Save Operation Failed";
}
}
}
public int Save(Visitor visitor)
{
SqlConnection connection = new SqlConnection(connectionString);
string query = string.Format("INSERT INTO tbl_Visitor OUTPUT INSERTED.v_Id VALUES('{0}','{1}','{2}')",visitor.Name,visitor.Email,visitor.ContactNo);
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
// int rowsAffected = command.ExecuteNonQuery();
int vid = (int) command.ExecuteScalar();
connection.Close();
// MessageBox.Show(vid.ToString());
connection.Open();
foreach (int id in selectedZoneId)
{
string query1 = string.Format("INSERT INTO tbl_Visit VALUES('{0}','{1}')", vid, id);
string query2 = "UPDATE tbl_Zone SET z_NoOfVisitors+=1 WHERE z_id='"+id+"'";
SqlCommand command1 = new SqlCommand(query1, connection);
SqlCommand command2=new SqlCommand(query2,connection);
command1.ExecuteNonQuery();
command2.ExecuteNonQuery();
}
connection.Close();
return vid;
}