protected void Page_Load(object sender, EventArgs e)
{
index = listBoxContacts.SelectedIndex;
//SQL initialize
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = CON_STR;
{
try
{
//Settings for SQL server to work
myConnection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
//Sets first and lastname of all contacts from SQL as items in listboxcontacts
myCommand.CommandText = "SELECT * FROM Midgard FULL JOIN Contact ON Contact.ID = PersonID FULL JOIN Adress ON Adress.ID = AdressID where Contact.ID is not null order by lastname";
SqlDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
//foreach (var persons in myReader)
{
//Skapa alla personer och alla adresser som finns i listan
Person tempPerson = new Person(myReader["Firstname"].ToString(), myReader["Lastname"].ToString(), myReader["SSN"].ToString(), (int)myReader["PersonID"]);
if (myReader["AdressID"].ToString().Length > 1)
{
Adress tempAdress = new Adress(myReader["Street"].ToString(), myReader["Zip"].ToString(), myReader["City"].ToString(), (int)myReader["AdressID"]);
if (tempPerson.ID1 == (int)myReader["personID"])
tempPerson.AdressLista.Add(tempAdress);
if (!IsPostBack)
{
foreach (var item in contactList)
{
if (item.ID1 == (int)myReader["personID"])
item.AdressLista.Add(tempAdress);
}
}
}
//Check if person already exist in contactlist. If not add to list.
if ((from p in contactList where p.PersonalNumber.Equals(tempPerson.PersonalNumber) select p).Count() == 0)
{
contactList.Add(tempPerson);
}
}
myReader.Close();
if (!IsPostBack)
{
listBoxContacts.Items.Clear();
foreach (var tmpContact in contactList)
{
ListItem tmpItem = new ListItem($"{tmpContact.Name + " "} {" " + tmpContact.LastName}", tmpContact.ID1.ToString());
listBoxContacts.Items.Add(tmpItem);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
myConnection.Close();
}
}
}
protected void CreateNewFullPerson()
{
listBoxContacts.Items.Clear();
Person tempPerson = new Person(TextBoxFirstnameADD.Text, TextBoxLastnameADD.Text, TextBoxSSNADD.Text, GetLastID());
contactList.Add(tempPerson);
ListItem tmpItem = new ListItem($"{tempPerson.Name + " "} {" " + tempPerson.LastName}", tempPerson.ID1.ToString());
listBoxContacts.Items.Add(tmpItem);
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = CON_STR;
try
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "spAddContact";
SqlParameter firstNameParam = new SqlParameter("@firstName", SqlDbType.VarChar);
firstNameParam.Value = TextBoxFirstnameADD.Text;
myCommand.Parameters.Add(firstNameParam);
SqlParameter lastnameParam = new SqlParameter("@lastName", SqlDbType.VarChar);
lastnameParam.Value = TextBoxLastnameADD.Text;
myCommand.Parameters.Add(lastnameParam);
SqlParameter ssnparam = new SqlParameter("@ssn", SqlDbType.VarChar);
ssnparam.Value = TextBoxSSNADD.Text;
myCommand.Parameters.Add(ssnparam);
SqlParameter paramID = new SqlParameter("@new_id", SqlDbType.Int);
paramID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(paramID);
int numberOfRows = myCommand.ExecuteNonQuery();
if((TextBoxCityADD.Text.Length>0)&(TextBoxZipADD.Text.Length>0&TextBoxStreetADD.Text.Length>1))
AddContactAdressToNewPerson(GetLastID());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
myConnection.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
string v = Request.QueryString["id"];
//SQL initialize
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = CON_STR;
{
try
{
//Settings for SQL server to work
myConnection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandText = "select * from Midgard Full outer join Contact on Contact.ID = PersonID Full outer join Adress on Adress.ID = AdressID where PersonID = '" + v + "' and street is not null";
SqlDataReader myReader = myCommand.ExecuteReader();
myReader.Read();
thisPerson = new Person(myReader["Firstname"].ToString(), myReader["Lastname"].ToString(), myReader["SSN"].ToString(), (int)myReader["PersonID"]);
myReader.Close();
SqlDataReader myReader1 = myCommand.ExecuteReader();
while (myReader1.Read())
{
Adress tempAdress = new Adress(myReader1["Street"].ToString(), myReader1["Zip"].ToString(), myReader1["City"].ToString(), (int)myReader1["AdressID"]);
thisPerson.AdressLista.Add(tempAdress);
}
foreach (var item in thisPerson.AdressLista)
{
ListBoxAdressPerson.Items.Add(item.StreetAdress + item.ZipCode + item.City + Environment.NewLine);
}
myReader1.Close();
TableRow tRow = new TableRow();
TableCell firstCell = new TableCell();
firstCell.Text = thisPerson.Name;
TableCell secondCell = new TableCell();
secondCell.Text = thisPerson.LastName;
TableCell thirdCell = new TableCell();
thirdCell.Text = thisPerson.PersonalNumber;
TableCell fourthCell = new TableCell();
fourthCell.Controls.Add(ListBoxAdressPerson);
tRow.Cells.Add(firstCell);
tRow.Cells.Add(secondCell);
tRow.Cells.Add(thirdCell);
tRow.Cells.Add(fourthCell);
TableCell fifthCell = new TableCell();
TableCell sixthCell = new TableCell();
TableCell seventhCell = new TableCell();
tRow.Cells.Add(fifthCell);
tRow.Cells.Add(sixthCell);
tRow.Cells.Add(seventhCell);
personTable.Rows.Add(tRow);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
myConnection.Close();
}
}
}
protected void btnAddContact_Click(object sender, EventArgs e)
{
listBoxContacts.Items.Clear();
Person tempPerson = new Person(TextBoxFirstnameADD.Text, TextBoxLastnameADD.Text, TextBoxSSNADD.Text, GetLastID());
contactList.Add(tempPerson);
foreach (var tmpContact in contactList)
{
ListItem tmpItem = new ListItem($"{tmpContact.Name + " "} {" " + tmpContact.LastName}", tmpContact.ID1.ToString());
listBoxContacts.Items.Add(tmpItem);
}
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString = CON_STR;
try
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand();
myCommand.Connection = myConnection;
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.CommandText = "spAddContact";
SqlParameter firstNameParam = new SqlParameter("@firstName", SqlDbType.VarChar);
firstNameParam.Value = TextBoxFirstnameADD.Text;
myCommand.Parameters.Add(firstNameParam);
SqlParameter lastnameParam = new SqlParameter("@lastName", SqlDbType.VarChar);
lastnameParam.Value = TextBoxLastnameADD.Text;
myCommand.Parameters.Add(lastnameParam);
SqlParameter ssnparam = new SqlParameter("@ssn", SqlDbType.VarChar);
ssnparam.Value = TextBoxSSNADD.Text;
myCommand.Parameters.Add(ssnparam);
SqlParameter paramID = new SqlParameter("@new_id", SqlDbType.Int);
paramID.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(paramID);
int numberOfRows = myCommand.ExecuteNonQuery();
AddContactAdressToNewPerson(GetLastID());
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
myConnection.Close();
}
}
public static void RemovePersonFromList(List<Person> ContactList, Person personToRemove)
{
ContactList.Remove(personToRemove);
}