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;////ADDING REFERENCE TO System.Data.SqlServerCe
////DLL FOR THIS NAMESPACE IS AVAILABLE IN...(C:\PROGRAM AND FILES\MICROSOFT SQL SERVER COMPACT\V3.5\DESKTOP 
namespace WINDOWS3
{
public partial class MAIN : Form
{
ADDSTUDENT add_student = null;
ERROR_DIALOG error_dialog = null;
bool DATA_GRID_VIEW_IS_CLICKED=false;///THIS WILL CHECK IF DATA_GRID_VIEW HAS BEEN CLICKED OR NOT
#region DECLARATION ...THIS REGION IS VERY IMPORTANT ,READ CAREFULLY
string Connection_String = "Data Source=DATA\\SCHOOL.sdf;PWD=muhsin";///this is used as connection string
SqlCeConnection Connection = null;///SqlCeConnection object which is used to connect to database file(SCHOOL.sdf)
SqlCeCommand command = null;///SqlCeCommand object   which is used to specify sql command
SqlCeDataAdapter adapater = null;///SqlCeDataAdapater object which is used to send command to database file(SCHOOL.sdf) also it is used to fill our table
DataTable table = null;///DataTable object that will act as temporary storage of data from database file (SCHOOL.sdf) 

#endregion
public MAIN()
{
InitializeComponent();
}
#region THIS FUNCTION LOAD DATA FROM DATABASE FILE(SCHOOL.sdf) INTO DATA_GRID_VIEW
void Load_Data()
{
try
{
Connection = new SqlCeConnection(Connection_String);
command = new SqlCeCommand("SELECT * FROM STUDENTS", Connection);
table = new DataTable("STUDENTS");
Connection.Open();
adapater = new SqlCeDataAdapter(command);
adapater.Fill(table);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
Connection.Close();
////this try...catch block will handle error in case of connection  failure
try
{
this.DATA_GRID_VIEW.DataSource = table;
}
catch (System.Exception ex)
{
error_dialog = new ERROR_DIALOG();
error_dialog.ERROR_LB.Text = ex.Message;
error_dialog.ShowDialog();
}
}
} 
#endregion
#region FIRST ROW IS SELECTED BY DEFAULT IN DATA GRID VIEW...THIS CODE REMOVE DEFAULT SELECTION IN DATA GRID VIEW
private void DATA_GRID_VIEW_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
{
((DataGridView)sender).ClearSelection();///IF YOU REMOVE THIS LINE OF CODE,THE FIRST ROW IN DATA GRID VIEW WILL  BE SELECTED BY DEFAULT
} 
#endregion
#region THIS FUNCTION WILL LOAD CURRENT STUDENT DATA
void Load_Current_Student()
{
///UNHIDE STUDENT_TBL
this.STUDENT_TBL.Visible = true;
this.STUDENT_ID_LB.Text = this.DATA_GRID_VIEW.CurrentRow.Cells[0].Value.ToString();
this.STUDENT_NAME_LB.Text = this.DATA_GRID_VIEW.CurrentRow.Cells[1].Value.ToString();
this.CONTACTS_LB.Text = this.DATA_GRID_VIEW.CurrentRow.Cells[2].Value.ToString();
} 
#endregion
#region  THIS FUNCTION ADDS NEW STUDENT INTO THE DATABASE
void Add_New_Student()
{
add_student = new ADDSTUDENT();
if (add_student.ShowDialog() == DialogResult.OK)
{
////TEST IF USER HAS FILLED ALL DETAILS
if ((add_student.STUDENT_ID_TXTB.Text.Length > 0) && (add_student.STUDENT_NAME_TXTB.Text.Length > 0) && (add_student.CONTACTS_TXTB.Text.Length > 0))
{
try
{
Connection = new SqlCeConnection(Connection_String);
command = new SqlCeCommand("INSERT INTO STUDENTS VALUES(" + add_student.STUDENT_ID_TXTB.Text + ",'" + add_student.STUDENT_NAME_TXTB.Text + "','" + add_student.CONTACTS_TXTB.Text+ "')", Connection);
Connection.Open();
command.ExecuteNonQuery();
Load_Data();///THIS WILL UPDATE DATA GRID VIEW
}
catch (System.Exception ex)
{
error_dialog = new ERROR_DIALOG();
error_dialog.ERROR_LB.Text = ex.Message;
error_dialog.ShowDialog();
}
finally
{
Connection.Close();
}
}
}
} 
#endregion
#region THIS FUNCTON EDITS STUDENT INFORMATION
void Edit_Student()
{
///FIRST TEST IF USER HAS SELECTED STUDENT FROM DATA GRID VIEW
if (DATA_GRID_VIEW_IS_CLICKED)
{
DATA_GRID_VIEW_IS_CLICKED = false;
///HERE WE USE SAME FORM THAT IS USED FOR ADDING STUDENT
add_student = new ADDSTUDENT();
add_student.ADD_STUDENT_LB.Text = "EDIT STUDENT";
add_student.STUDENT_ID_TXTB.Text =this.STUDENT_ID_LB.Text;
add_student.STUDENT_NAME_TXTB.Text =this.STUDENT_NAME_LB.Text;
add_student.CONTACTS_TXTB.Text =this.CONTACTS_LB.Text;

if (add_student.ShowDialog() == DialogResult.OK)
{
if ((add_student.STUDENT_ID_TXTB.Text.Length > 0) && (add_student.STUDENT_NAME_TXTB.Text.Length > 0) && (add_student.CONTACTS_TXTB.Text.Length > 0))
{
try
{
Connection = new SqlCeConnection(Connection_String);
command = new SqlCeCommand("UPDATE STUDENTS SET StudentId="+add_student.STUDENT_ID_TXTB.Text+",Name='"+add_student.STUDENT_NAME_TXTB.Text+"',Contacts='"+add_student.CONTACTS_TXTB.Text+"' WHERE StudentId="+this.DATA_GRID_VIEW.CurrentRow.Cells[0].Value.ToString()+"", Connection);
Connection.Open();
command.ExecuteNonQuery();
}
catch (System.Exception ex)
{
error_dialog = new ERROR_DIALOG();
error_dialog.ERROR_LB.Text = ex.Message;
error_dialog.ShowDialog();
}
finally
{
Connection.Close();
this.STUDENT_TBL.Visible = false;
Load_Data();
}
}
}
}
else
{
error_dialog = new ERROR_DIALOG();
error_dialog.ERROR_LB.Text = "SELECT STUDENT FROM THE LIST,THEN CLICK EDIT";
error_dialog.ShowDialog();
}
}
#endregion
#region THIS FUNCTION DELETES STUDENT INFROMATION FROM THE DATABASE
void Delete_Student()
{
///FIRST TEST IF USER HAS SELECTED STUDENT FROM DATA GRID VIEW
if (DATA_GRID_VIEW_IS_CLICKED)
{
DATA_GRID_VIEW_IS_CLICKED = false;
try
{
Connection = new SqlCeConnection(Connection_String);
command = new SqlCeCommand("DELETE FROM STUDENTS WHERE StudentID="+this.DATA_GRID_VIEW.CurrentRow.Cells[0].Value.ToString()+"", Connection);
Connection.Open();
command.ExecuteNonQuery();

}
catch(System.Exception ex)
{
error_dialog = new ERROR_DIALOG();
error_dialog.ERROR_LB.Text = ex.Message;
error_dialog.ShowDialog();
}
finally
{
Connection.Close();
this.STUDENT_TBL.Visible =false;
Load_Data();
}
}
else
{
error_dialog = new ERROR_DIALOG();
error_dialog.ERROR_LB.Text = "SELECT STUDENT FROM THE LIST,THEN CLICK DELETE";
error_dialog.ShowDialog();
}
} 
#endregion
#region EVENT HANDLERS FOR SIDE MENU BUTTONS
private void ADD_STUDENT_BTN_Click(object sender, EventArgs e)
{
Add_New_Student();
}
private void EDIT_STUDENT_BTN_Click(object sender, EventArgs e)
{
Edit_Student();
}
private void DELETE_STUDENT_BTN_Click(object sender, EventArgs e)
{
Delete_Student();
} 
#endregion
#region EVENT HANDLERS FOR TOP MENU BUTTONS

private void ADD_STUDENT_BTNN_Click(object sender, EventArgs e)
{
this.ADD_STUDENT_BTN.PerformClick();
}
private void EDIT_STUDENT_BTNN_Click(object sender, EventArgs e)
{
this.EDIT_STUDENT_BTN.PerformClick();
}
private void DELETE_STUDENT_BTNN_Click(object sender, EventArgs e)
{
this.DELETE_STUDENT_BTN.PerformClick();
} 
#endregion
#region OTHER EVENT HANDLERS
private void DATA_GRID_VIEW_CellClick(object sender, DataGridViewCellEventArgs e)
{
DATA_GRID_VIEW_IS_CLICKED = true;
Load_Current_Student();
}
private void MAIN_Load(object sender, EventArgs e)
{
////LOAD DATA INTO DATA_GRID_VIEW
Load_Data();
///HIDE STUDENT_TBL,SAVE_BTN,CLEAR_BTN
this.STUDENT_TBL.Visible = false;
}
private void ABOUT_BTN_Click(object sender, EventArgs e)
{
    ABOUT about = new ABOUT();
    about.ShowDialog();
}

#endregion




}
}
