private void button_red_Click(object sender, EventArgs e)//редактировать
        {
            var row = ab_tab.SelectedRows.Count > 0 ? ab_tab.SelectedRows[0] : null;

            if (row == null)
            {
                MessageBox.Show(" Сначала выберите строчку в Телефонном справочнике", "Ошибка", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            var form = new Form_c();

            form.textBox_surname.Text    = row.Cells["surname"].Value.ToString();
            form.textBox_name.Text       = row.Cells["name"].Value.ToString();
            form.textBox_patronymic.Text = row.Cells["patronymic"].Value.ToString();
            form.textBox_adres.Text      = row.Cells["adress"].Value.ToString();
            form.textBox_comment.Text    = row.Cells["comment"].Value.ToString();
            form.textBox_phone.Text      = row.Cells["phone"].Value.ToString();
            {
                var request = "SELECT * FROM provider";
                var adapter = new SqlDataAdapter(request, connectionString);
                var prTable = new DataTable();
                adapter.Fill(prTable);
                var dict = new Dictionary <int, string>();
                foreach (DataRow row1 in prTable.Rows)
                {
                    dict.Add((int)row1["id"], row1["provider_company"].ToString());
                }
                form.ProviderData = dict;
                dict.Clear();

                List <string> qwe = new List <string>();

                request = "SELECT DISTINCT  type From contact";
                adapter = new SqlDataAdapter(request, connectionString);
                prTable = new DataTable();
                adapter.Fill(prTable);

                foreach (DataRow row2 in prTable.Rows)


                {
                    qwe.Add(row2["type"].ToString());
                }
                form.TypeData = qwe;
            }

            //form.ProviderId = (int) row.Cells["id"].Value;
            //     form.TypeId = row.Cells["type"].Value.ToString();


            var res = form.ShowDialog();

            if (res == DialogResult.OK)
            {
                var surname    = form.textBox_surname.Text;
                var name       = form.textBox_name.Text;
                var patronymic = form.textBox_patronymic.Text;
                var adress     = form.textBox_adres.Text;
                var comment    = form.textBox_comment.Text;
                var phone      = form.textBox_phone.Text;
                var type       = form.comboBox_type.Text;
                var id         = row.Cells["id"].Value.ToString();
                var connection = new SqlConnection(connectionString);

                connection.Open();
                var request = string.Format(@"UPDATE  abonent SET surname=N'{0}',name=N'{1}', patronymic=N'{2}', adress=N'{3}', comment=N'{4}' WHERE id={5}
                                              UPDATE  contact SET Phone=N'{6}', type=N'{7}'",
                                            surname, name, patronymic, adress, comment, id, phone, type);
                var command = new SqlCommand(request, connection);
                command.ExecuteNonQuery();
                connection.Close();
                updateAbonentDGV();
                updatePhone_TabDGV();
                updateContactDGV();
            }
        }
        private void button_add_Click(object sender, EventArgs e)//добавить абонента
        {
            var form = new Form_c();
            {
                var request = "SELECT * FROM provider";
                var adapter = new SqlDataAdapter(request, connectionString);
                var prTable = new DataTable();
                adapter.Fill(prTable);
                var dict = new Dictionary <int, string>();
                foreach (DataRow row in prTable.Rows)
                {
                    dict.Add((int)row["id"], row["provider_company"].ToString());
                }
                form.ProviderData = dict;
                dict.Clear();

                List <string> qwe = new List <string>();

                request = "SELECT DISTINCT  type From contact";
                adapter = new SqlDataAdapter(request, connectionString);
                prTable = new DataTable();
                adapter.Fill(prTable);

                foreach (DataRow row in prTable.Rows)
                {
                    qwe.Add(row["type"].ToString());
                }
                form.TypeData = qwe;
            }
            var res = form.ShowDialog();

            if (res == DialogResult.OK)
            {
                var surname    = form.textBox_surname.Text;
                var name       = form.textBox_name.Text;
                var patronymic = form.textBox_patronymic.Text;
                var address    = form.textBox_adres.Text;
                var comment    = form.textBox_comment.Text;
                var providerId = form.ProviderId;
                var phone      = form.textBox_phone.Text;
                var type       = form.comboBox_type.Text;
                var connection = new SqlConnection(connectionString);
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var command = new SqlCommand();
                        command.Connection  = connection;
                        command.CommandType = CommandType.Text;
                        command.Transaction = transaction;

                        command.CommandText = string.Format(@"INSERT INTO abonent (surname, name, patronymic, adress, comment) VALUES (N'{0}',N'{1}',N'{2}',N'{3}',N'{4}')",
                                                            surname, name, patronymic, address, comment);
                        command.ExecuteNonQuery();
                        command.CommandText = string.Format("INSERT INTO contact VALUES (N'{0}',N'{1}',N'{2}')",
                                                            phone, type, providerId);
                        command.ExecuteNonQuery();

                        command.CommandText = string.Format("INSERT INTO dbo.abonent_has_contact values" +
                                                            "((select id from dbo.abonent where name=N'{0}'), (select id from dbo.contact where Phone = N'{1}'))", name, phone);
                        command.ExecuteNonQuery();

                        transaction.Commit();
                        connection.Close();
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                    }
                }
            }
            updateAbonentDGV();
            updateContactDGV();
            updatePhone_TabDGV();
        }