using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;

namespace db_labs
{
    class DbManipulation
    {
        public string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\baxov_user_Database1.mdf;Integrated Security=True;User Instance=True";
        public DataTable myDataTable;
        public DataSet myDataSet = new DataSet();
        public ListView myListView = new ListView();
        public List<string> myEmails = new List<string>(); //here us save email addresses
        public string selectString;

        public void getEmails()
        {

            SqlConnection mySqlConnection = new SqlConnection(connectionString);
            mySqlConnection.Open();

            //Создание SQL-команды
            selectString = "SELECT id,mail FROM users ";
            SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = selectString;

            // Закрепление SQL-адаптеру объекта команды
            SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.SelectCommand = mySqlCommand;

            // Определение набора данных DataSet и заполнение его
            string dataTableName = "id";
            mySqlDataAdapter.Fill(myDataSet, dataTableName);
            myDataTable = myDataSet.Tables[dataTableName];

            // Вывод данных на экран
            foreach (DataRow myDataRow in myDataTable.Rows)
            {
                string userEmail = Convert.ToString(myDataRow["mail"]);
                myListView.BeginInvoke((Action)delegate { myListView.Items.Add(userEmail); });

                //Создаем коллекцию валидных адресов
                if (System.Text.RegularExpressions.Regex.IsMatch(userEmail,
                @"^(?("")("".+?""@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))" +
                @"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$"))
                    myEmails.Add(userEmail);
            }

            mySqlConnection.Close();
        }

        public void save_invalid_mails(string invalidMail)
        {




            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var command = new SqlCommand("INSERT INTO foo (email) VALUES (@mail)", connection))
                {
                        command.Parameters.Add(new SqlParameter("@mail", invalidMail));
                        command.ExecuteNonQuery();
                }
                connection.Close();
            }


            /*
            SqlConnection mySqlConnection = new SqlConnection(connectionString);
            mySqlConnection.Open();

            //Создание SQL-команды
            selectString = "INSERT INTO invalid (email_invalid) VALUES (@invalidMail);";
            SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = selectString;
            mySqlCommand.Parameters.Add("@invalidMail", SqlDbType.NText, 55, invalidMail);
            mySqlCommand.ExecuteNonQuery();
            mySqlConnection.Close();
            */
            /*
            SqlConnection mySqlConnection = new SqlConnection(connectionString);
            mySqlConnection.Open();

            //Создание SQL-команды
            selectString = "INSERT INTO invalid (email_invalid) VALUES ("+ invalidMail +");";
            SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
            mySqlCommand.CommandText = selectString;

            // Закрепление SQL-адаптеру объекта команды
            SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.InsertCommand = mySqlCommand;

            mySqlConnection.Close();
            */

            /*
            SqlConnection mySqlConnection = new SqlConnection(connectionString);
            mySqlConnection.Open();

            //Создание SQL-команды
            SqlCommand command = new SqlCommand("INSERT INTO invalid (email_invalid) VALUES ('@invalidMail')", mySqlConnection);

            // Add the parameters for the InsertCommand.
            command.Parameters.Add("@invalidMail", SqlDbType.NText, 55, invalidMail);

            // Закрепление SQL-адаптеру объекта команды
            SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
            mySqlDataAdapter.InsertCommand = command;

            mySqlConnection.Close();
            //MessageBox.Show(invalidMail);
            */
        }
    }
}