public static void InsertIntoUsers(SqlConnection connection, User user)
{
string saveUser = "******" +
" VALUES ('" + user.name + "');";
SqlCommand querySaveUser = new SqlCommand(saveUser, connection);
querySaveUser.ExecuteNonQuery();
}
public static void EditContact(Contact contact, Contact newContact, User user)
{
if (user.contacts.Contains(contact))
{
user.contacts.Remove(contact);
user.contacts.Add(contact);
}
user.contacts.Sort();
}
public User(User contacts, String name)
{
this.name = name;
this.contacts = new List<Contact>();
if (contacts.contacts.Count != 0)
{
foreach (Contact user in contacts.contacts)
{
this.contacts.Add(user);
}
}
}
public FormForAdd(User list, int index)
{
connection1 = BaseDataAccess.CreateConnection();
numbers = new List<PhoneNumber>();
foreach (Contact user in list.contacts)
{
UserContactsProcessing.AddContact(user, this.list);
}
InitializeComponent();
this.index = index + 1;
}
public static void SelectFromPhoneNumbers(SqlConnection connection, User user, Contact contact)
{
string sql1 = "SELECT * FROM PHONENUMBER WHERE Id_contact =" + contact.id;
SqlCommand command2 = new SqlCommand(sql1, connection);
SqlDataReader dr = command2.ExecuteReader();
while (dr.Read())
{
List<PhoneNumber> pn = new List<PhoneNumber>();
pn.Add(new PhoneNumber(dr["Number"].ToString(), dr["Label"].ToString()));
user.contacts[Int32.Parse(contact.id) - 1].numbers = pn;
}
dr.Close();
}
public static void InsertIntoContacts(SqlConnection connection, Contact contact, User user, int index)
{
string saveContact = "INSERT into CONTACTS (Name,Surname,Patronymic,Id_user) " +
" VALUES ('" + contact.name + "', '" + contact.surname + "', '" + contact.patronymic + "', '" + index + "');";
SqlCommand querySaveStaff = new SqlCommand(saveContact, connection);
querySaveStaff.ExecuteNonQuery();
foreach (PhoneNumber p in contact.numbers)
{
string saveNumber = "INSERT into PHONENUMBER (Number,Label,Id_contact) " +
" VALUES ('" + p.number + "', '" + p.label + "', '" + (contact.id + user.contacts.Count) + "');";
SqlCommand querySaveNumber = new SqlCommand(saveNumber, connection);
querySaveNumber.ExecuteNonQuery();
}
}
private void btAddUser_Click(object sender, EventArgs e)
{
if (tbName.Text == "" )
{
return;
}
else
{
User user = new User(tbName.Text);
users.Add(user);
connection1.Open();
BaseDataAccess.InsertIntoUsers(connection1, user);
connection1.Close();
this.Close();
}
}
public static User Find(User user, string find)
{
User foundContacts = new User();
String[] result = find.Split(' ' );
if (result.Length == 1)
{
foreach (Contact contact in user.contacts)
{
if (contact.surname == find || contact.name == find || contact.patronymic == find)
{
AddContact(contact, foundContacts);
}
}
}
else if (result.Length == 2)
{
foreach (Contact contact in user.contacts)
{
if (contact.name == result[0] && contact.surname == result[1] || contact.name == result[1] && contact.surname == result[0] ||
contact.name == result [0] && contact.patronymic == result[1] )
{
AddContact(contact, foundContacts);
}
}
}
else if (result.Length == 3)
{
foreach (Contact contact in user.contacts)
{
if (contact.name == result[0] && contact.surname == result[1] && contact.patronymic == result[2] || contact.name == result[1] && contact.surname == result[0] && contact.patronymic == result[2])
{
AddContact(contact, foundContacts);
}
}
}
return foundContacts;
}
public FormForEdit(User list, Contact contact)
{
list = new User();
this.contact = new Contact();
foreach (Contact us in list.contacts)
{
UserContactsProcessing.AddContact(us, this.list);
}
this.contact = contact;
this.index = Int32.Parse(contact.id);
InitializeComponent();
tbName.Text = contact.name;
tbSurname.Text = contact.surname;
tbPatronymic.Text = contact.patronymic;
foreach (PhoneNumber number in contact.numbers)
{
lbNumbers.Items.Add(number.ToString());
}
}
public static void AddContact(Contact contact, User user)
{
user.contacts.Add(contact);
}
private void btEdit_Click(object sender, EventArgs e)
{
if (dgUsers.SelectedRows.Count == 0)
{
return;
}
StringBuilder sb = new StringBuilder();
sb.Append(dgUsers[0, dgUsers.CurrentRow.Index].Value.ToString() + ' ');
sb.Append(dgUsers[1, dgUsers.CurrentRow.Index].Value.ToString());
string[] result = sb.ToString().Split(' ');
List<PhoneNumber> pn = new List<PhoneNumber>();
for (int i = 3; i < result.Length-1; i+=2)
{
pn.Add(new PhoneNumber(result[i], result[i+1]));
}
Contact contact = new Contact(result[0], result[1], result[2], pn);
contact.id = (dgUsers.CurrentRow.Index + 1).ToString();
FormForEdit form = new FormForEdit(user, contact);
form.ShowDialog();
if (form.DialogResult == DialogResult.OK)
{
user = form.list;
}
for (int i = 0; i < form.list.contacts.Count; i++)
{
dgUsers.Rows.Add();
dgUsers.Rows[i].Cells[0].Value = form.list.contacts[i].surname + " " + form.list.contacts[i].name + " " + form.list.contacts[i].patronymic;
StringBuilder s = new StringBuilder();
foreach (PhoneNumber number in users[index].contacts[i].numbers)
{
s.Append(number.ToString() + " ");
}
dgUsers.Rows[i].Cells[1].Value = s;
}
}
private void MainForm_Load(object sender, EventArgs e)
{
users = new List<User>();
user = new User();
connection1 = BaseDataAccess.CreateConnection();
connection1.Open();
users = BaseDataAccess.SelectFromUsers(connection1);
for (int i = 0; i < users.Count; i++)
{
lbUsers.Items.Add(users[i].name);
}
}
private void lbUsers_Click(object sender, EventArgs e)
{
index = lbUsers.SelectedIndex + 1;
foreach (User us in users)
{
if (Int32.Parse(us.id) == index)
{
this.user = us;
}
}
dgUsers.Rows.Clear();
user.contacts = BaseDataAccess.SelectFromContacts(connection1, index);
foreach (Contact ct in user.contacts)
{
BaseDataAccess.SelectFromPhoneNumbers(connection1, user, ct);
}
dgUsers = createDataGridView();
user.contacts.Sort();
for (int i = 0; i < user.contacts.Count; i++)
{
dgUsers.Rows.Add();
dgUsers.Rows[i].Cells[0].Value = user.contacts[i].surname + " " + user.contacts[i].name + " " + user.contacts[i].patronymic;
StringBuilder sb = new StringBuilder();
foreach (PhoneNumber number in user.contacts[i].numbers)
{
sb.Append(number.ToString() + " ");
}
dgUsers.Rows[i].Cells[1].Value = sb;
}
}
private void btFind_Click(object sender, EventArgs e)
{
if (tbName.Text == "")
{
return;
}
else
{
User newUser = new User();
newUser = UserContactsProcessing.Find(user, tbName.Text);
dgUsers.Rows.Clear();
dgUsers = createDataGridView();
for (int i = 0; i < newUser.contacts.Count; i++)
{
dgUsers.Rows.Add();
dgUsers.Rows[i].Cells[0].Value = newUser.contacts[i].surname + " " + newUser.contacts[i].name + " " + newUser.contacts[i].patronymic;
StringBuilder sb = new StringBuilder();
foreach (PhoneNumber number in newUser.contacts[i].numbers)
{
sb.Append(number.ToString() + " ");
}
dgUsers.Rows[i].Cells[1].Value = sb;
}
}
}
