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.Data.SqlServerCe;
using System.Diagnostics;

namespace datagridview {
    public partial class Form1 : Form {
        SqlCeConnection conn = new SqlCeConnection(@"Data Source=C:\Users\DELL\Desktop\datagridview with complete db\datagridview\Database1.sdf");
        String selectedId = null;
        public Form1() {

            InitializeComponent();

            conn.Open(); // OPEN YUNG CONNECTION
            loadData(); //Method para ma load sa datagridview yung laman ng database
                        //tingnan sa ilalim kung ano laman ng method na to :)
        }


        //codes para sa pag add ng data sa database :)
        private void btnAdd_Click(object sender, EventArgs e) {
            try {
                conn.Open();
                String username = tbUsername.Text;
                String password = tbPassword.Text;

                //tbl_users is the name of table
                //username and password is yung name ng table columns :)
                //dont forget to put your connection variable in there :)
                SqlCeCommand cmd = new SqlCeCommand("INSERT INTO tbl_users (username,password) VALUES ('" + username + "', '" + password + "')", conn);
                cmd.ExecuteNonQuery();


                loadData();//para ma refresh automatically yung datagridview :) or yung table

                MessageBox.Show("success");
                clearTextFields(); //method to na gawa natin. para ma clear yung textfields :) see below

            } catch(SqlCeException sqlEx) {

            } catch(Exception ex) {
                Debug.WriteLine(ex.ToString());
                MessageBox.Show(ex.ToString());
            }

            finally {
                conn.Close();
            }
        }

//----------------------------------------------------------------------------------------
        //for updating naman :)
        private void btnUpdate_Clicked(object sender, EventArgs e) {
            try {
                conn.Open();

                string query = "Update tbl_users Set username= '"+tbUsername.Text+"', password='"+tbPassword.Text+"' WHERE ID='"+selectedId+"'";

                SqlCeCommand cmd = new SqlCeCommand(query, conn);
                cmd.ExecuteNonQuery();

                loadData();
                clearTextFields();
                MessageBox.Show("Record has been updated");
            } catch(Exception ex) {
                MessageBox.Show("update: " + ex.ToString());


            }finally{
                conn.Close();

            }

        }

//----------------------------------------------------------------------------------------
        //delete yung pinakamadaling gawin :)
        private void btnDelete_Clicked(object sender, EventArgs e) {
            try {
                conn.Open();
                SqlCeCommand cmd = new SqlCeCommand("DELETE FROM tbl_users where ID = '" + tbID.Text + "';", conn);
                cmd.ExecuteNonQuery();

                loadData();
                clearTextFields();
                MessageBox.Show("Deleted Successfully");



            } catch(SqlCeException ScException) {
                MessageBox.Show(ScException.ToString());
            } catch(Exception ex) {
                MessageBox.Show(ex.ToString());
            } finally {
                conn.Close();
            }
        }

        //code para ma refresh yung table :)
        private void btnRefresh_Clicked(object sender, EventArgs e) {
            conn.Open();
            loadData();
        }


        private void btnClear_Clicked(object sender, EventArgs e) {
            clearTextFields();
        }

        // tinawag natin to sa constructor natin(dapat alam nyo kung ano ang constructor. dumaan na tayo sa copro2 :) )
        //after ng InitializeComponent() para ma load agad sa datagridview/table
        //yung laman ng database
        private void loadData() {
            try {
                //dgv is the variable name of our datagridview :)
                dgv.Rows.Clear(); // clear muna yung laman ng table para hindi ma doble :)

                String query = "SELECT * FROM tbl_users";
                SqlCeCommand cmd = new SqlCeCommand(query, conn);

                SqlCeDataReader reader = cmd.ExecuteReader();

                while(reader.Read()) {
                    dgv.Rows.Add(reader["ID"].ToString(),reader["username"].ToString(), reader["password"].ToString());

                }


            } catch(Exception ex) {
                MessageBox.Show("loadData: " + ex.ToString());
            } finally {
                conn.Close();
            }

        }


        //add this event sa datagridview nyo :)
        //para every time na mag cclick kayo ng cell
        //mapupunta sa textboxes nyo yung clinick nyong cell
        private void dgc_CellClicked(object sender, DataGridViewCellEventArgs e) {
            selectedId = dgv.Rows[e.RowIndex].Cells["Col_ID"].Value.ToString();
            String Col_Username = dgv.Rows[e.RowIndex].Cells["Col_Username"].Value.ToString();
            String Col_Password = dgv.Rows[e.RowIndex].Cells["Col_Password"].Value.ToString();

            tbID.Text = selectedId;
            tbUsername.Text = Col_Username;
            tbPassword.Text = Col_Password;
        }

        //call this method para ma clear yung laman ng mga textfields nyo :)
        private void clearTextFields() {
            tbID.Text = null;
            tbUsername.Text = null;
            tbPassword.Text = null;
        }











    }
}
/*int n = dataGridView1.Rows.Add();

          dataGridView1.Rows[n].Cells[0].Value = tbLname.Text;
          dataGridView1.Rows[n].Cells[1].Value = tbFname.Text;
          dataGridView1.Rows[n].Cells[2].Value = tbAge.Text;
          */