using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace lib123
{
    public partial class Form1 : Form
    {
        SqlConnection sqlConn;
        SqlCommand sqlComm;
        SqlDataAdapter sqlAdptr;

          public Form1()
        {
            InitializeComponent();
           
           sqlConn = new SqlConnection();
           sqlComm = new SqlCommand();
           sqlAdptr = new SqlDataAdapter();
           sqlComm.Connection = sqlConn;
           sqlComm.CommandType = CommandType.Text;
           sqlConn.ConnectionString = "Data Source=007-PC\\SQLEXPRESS ;Initial Catalog= Email_DB;Integrated Security =True ";
             
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            FillGrid();
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
           
            if (sqlConn.State != ConnectionState.Open)
                sqlConn.Open();

            // sqlComm = sqlConn.CreateCommand(); 

            StreamReader streamReader = new StreamReader(@"C:\Users\007\Desktop\spam-email\spamenglish.txt"); //get the file
            string stringWithMultipleSpaces = streamReader.ReadToEnd(); //load file to string
            streamReader.Close();

            Regex r = new Regex(" +"); //specify delimiter (spaces)
            string[] words = r.Split(stringWithMultipleSpaces); //(convert string to array of words)

           
            int c = 1;
            string strQry = "select ISNULL( max(id),0) as id from word_tb ";
            sqlComm.CommandText = strQry;

            int LastID = int.Parse(sqlComm.ExecuteScalar().ToString());
           string x ;
            String st = null;
            
            for (int i = 0; i < words.Length; i++)
            {
                string y = words[i];


                for (int j = 0; j <LastID; j++)
                {
                 x = "select word from word_tb where id = j";
                sqlComm.CommandText = x ;
         
 

                if (x.Equals(y))
                {
                    c = c + 1;
                    string sql = "UPDATE    word_tb SET  count ='c'  WHERE (id = j)";
             

                    sqlComm.CommandText = sql;

                }
                else
                {
                    LastID = LastID + 1;
                    st += "INSERT INTO word_tb(id, word,count) VALUES('" + LastID + "', '" + words[i].ToString() + "','" + c + "');";

                    //st += "INSERT INTO word_tb(word) VALUES('" + words[i].ToString() + "');";

                }
                 }
            }

                sqlComm.CommandType = CommandType.Text;
                sqlComm.CommandText = st;
                sqlComm.ExecuteNonQuery();


                FillGrid();

            }
      

        private void FillGrid()
        {
            DataTable tbl = new DataTable();

          string   strQry = "select * from word_tb ";
            
            sqlComm.CommandText = strQry;

            sqlAdptr.SelectCommand = sqlComm;
            sqlAdptr.Fill (tbl) ;


            dataGridView1.DataSource = tbl;



        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

        //private void btnDelete_Click(object sender, EventArgs e)
        //{
        //    string str = "DELETE FROM word_tb";
        //    sqlComm.CommandType = CommandType.Text;
        //    sqlComm.CommandText = str;
        //    sqlComm.ExecuteNonQuery();

        //    dataGridView1.Rows.Clear();


        //            }



    

    }
    }