/*
* This is the main function of the form. It uses a Select statement that encompasses a searchTerm.
* This query allows for Searching all (just "" as the searchTerm) and searching for any name, first or last or both.
* We use this loadData function anytime we add,update,or delete records
*/
private void loadData(string searchTerm)
{
DBCommands.sql = "SELECT users.id, first_nm, last_nm, job_ttl, work_location, yrs_in_job " +
"FROM users JOIN job_details ON job_details.id = users.id WHERE CONCAT(first_nm, ' ', last_nm) ILIKE @searchTerm::varchar AND users.stat_cd = 'A' ORDER BY last_nm";
// The format %str% is postgres language for grabbing anything before and after the search term.
// example: "John Snow Movies" if search term was snow, it would still grab "John Snow Movies"
string strSearchTerm = string.Format("%{0}%", searchTerm);
// We use our cmd method from our DBCommands class again here to setup a new command with parameters
// In this case the parameter will be the search term, if there is one
// We set create a new DataTable object called dt which we set equal to the dt we return from
// the PerformCommand method in the class.
DBCommands.cmd = new NpgsqlCommand(DBCommands.sql, DBCommands.con);
DBCommands.cmd.Parameters.Clear();
// you will notice the @ sign inside of the sql Select statment above in front of searchTerm.
// This is how the AddWithValue() works. It will find searchTerm as the given parameter and set it
// to the value we pass in, in this case strSearchTerm
DBCommands.cmd.Parameters.AddWithValue("searchTerm", strSearchTerm);
DataTable dt = DBCommands.PerformCommand(DBCommands.cmd);
// The rest below is just styling our Datagridview from the form
DataGridView dgv1 = dbGrid;
dgv1.MultiSelect = false;
dgv1.AutoGenerateColumns = true;
dgv1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
dgv1.DataSource = dt;
dgv1.Columns[0].HeaderText = "ID";
dgv1.Columns[1].HeaderText = "First_Name";
dgv1.Columns[2].HeaderText = "Last_Name";
dgv1.Columns[3].HeaderText = "Job_Title";
dgv1.Columns[4].HeaderText = "Work_Location";
dgv1.Columns[5].HeaderText = "Years_In_Job";
dgv1.Columns[0].Width = 120;
dgv1.Columns[1].Width = 150;
dgv1.Columns[2].Width = 150;
dgv1.Columns[3].Width = 150;
dgv1.Columns[4].Width = 150;
dgv1.Columns[5].Width = 125;
}
/*
* PostgreSQL requires us to execute after doing sql queries with a command and parameters
* and it gets lengthy to type the whole thing out.
* So, instead, it is good practice to create an execute, or in this case executeCMD function
* so you just have to call executeCMD instead. This is like committing in other languages like GIT
*/
private void executeCMD(string mySQL, string param)
{
DBCommands.cmd = new NpgsqlCommand(mySQL, DBCommands.con);
addParameters(param);
DBCommands.PerformCommand(DBCommands.cmd);
}
