private void btnA_Edit_Click(object sender, RoutedEventArgs e) { try { //let's check first if the textboxes are empty since we cannot allow a blank data if (txtCcode.Text.Trim() == null || txtCdesc.Text.Trim() == null) { MessageBox.Show("Course Data cannot be empty"); return; } SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { //let's check first if the data trying to change is already exist. string check = "SELECT TOP 1 * FROM tblCourse WHERE coursecode = @course and course_id <> @id"; SqlCommand cmdcheck = new SqlCommand(check, con.Con); cmdcheck.Parameters.AddWithValue("@course", txtCcode.Text.Trim()); cmdcheck.Parameters.AddWithValue("@id", txtCourseiD.Text.Trim()); SqlDataReader sdr = cmdcheck.ExecuteReader(); if (sdr.Read()) //if theres a record, return, meaning we cannot allow it { MessageBox.Show("Cannot update record, the data you are trying to change already exist."); sdr.Close(); return; } else //we can change or update it { sdr.Close(); CrudConfiguration update = new CrudConfiguration(); string tablename = "tblCourse"; //tablename string[] col = new string[] { "coursecode", "coursedesc" }; //tblcolumns to update string[] values = new string[] { txtCcode.Text.Trim(), txtCdesc.Text.Trim() }; //values to pass Dictionary <string, string> conds = new Dictionary <string, string> { { "course_id", txtCourseiD.Text.Trim() } }; //the conditions to make if (update.updateTrans(tablename, col, values, conds)) { CourseLoad(""); enabledNewTransbutton(); clearControls(); MessageBox.Show("Record updated successfully"); } } } else { return; } con.conclose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void genKeyId() { SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { string q = "update tblSettings SET PrevVal = (SELECT NextVal FROM tblSettings WHERE id = 1 ) , NextVal = (SELECT(NextVal + 1) FROM tblSettings WHERE id = 1 ) WHERE id = 1"; SqlCommand cmd = new SqlCommand(q, con.Con); cmd.ExecuteNonQuery(); string query = "SELECT NextVal FROM tblSettings where id = 1"; SqlCommand x = new SqlCommand(query, con.Con); SqlDataReader sdr = x.ExecuteReader(); string latestval = ""; while (sdr.Read()) { latestval = sdr["NextVal"].ToString(); } string sdate = DateTime.Now.ToString(); DateTime convertsdate = Convert.ToDateTime(sdate.ToString()); string year = convertsdate.Year.ToString(); string format = "101-" + year.ToString() + "-" + latestval.ToString(); txtBarrowerId.Text = format; } else { return; } con.conclose(); }
private void onLoadBooks() { SqlConnect con = new SqlConnect(); con.conOpen(); if (con == null) { return; } else { string query = "SELECT A.*, (B.author_fname + ' ' + B.author_lname) AS Author FROM tblBooks A INNER JOIN tblAuthor B ON A.bookauthor_id = B.authorid"; SqlCommand cmd = new SqlCommand(query, con.Con); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); DataTable dt = new DataTable(); sda.Fill(dt); gridDataLoading.ItemsSource = dt.DefaultView; gridDataLoading.Columns[0].Visibility = Visibility.Collapsed; gridDataLoading.Columns[2].Visibility = Visibility.Collapsed; gridDataLoading.Columns[1].Header = "Book Title"; gridDataLoading.Columns[3].Header = "Date Published"; gridDataLoading.Columns[4].Header = "Quantity"; gridDataLoading.Columns[5].Header = "Stocks"; gridDataLoading.CanUserAddRows = false; con.conclose(); } }
public bool deleteTrans(string table, Dictionary <string, string> conds) { try { SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { string query = "DELETE FROM " + table + " WHERE " + iterateCond(conds); SqlCommand cmd = new SqlCommand(query, con.Con); Console.WriteLine(query); cmd.ExecuteNonQuery(); } else { return(false); } } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } return(true); }
private void btnB_Edit_Click(object sender, RoutedEventArgs e) { try { //let's check first if the textboxes are empty since we cannot allow a blank data if (txtFname.Text.Trim() == null || txtLname.Text.Trim() == null || cboCourse.Text == null) { MessageBox.Show("Barrower information cannot be empty"); return; } SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { //let's check first if the data trying to change is already exist. string check = "SELECT TOP 1 * FROM tblBarrower WHERE barrower_fname = @fname and barrower_lastname = @lname and course = @course and barrower_id <> @id"; SqlCommand cmdcheck = new SqlCommand(check, con.Con); cmdcheck.Parameters.AddWithValue("@fname", txtFname.Text.Trim()); cmdcheck.Parameters.AddWithValue("@lname", txtLname.Text.Trim()); cmdcheck.Parameters.AddWithValue("@course", cboCourse.SelectedValue.ToString()); cmdcheck.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim()); SqlDataReader sdr = cmdcheck.ExecuteReader(); if (sdr.Read()) //if theres a record, return, meaning we cannot allow it { MessageBox.Show("Cannot update record, the data you are trying to change already exist."); sdr.Close(); return; } else //we can change or update it { sdr.Close(); string query = "UPDATE tblBarrower SET barrower_fname = @fname, barrower_lastname = @lname, course = @course WHERE barrower_id = @id"; SqlCommand cmd = new SqlCommand(query, con.Con); cmd.Parameters.AddWithValue("@fname", txtFname.Text.Trim()); cmd.Parameters.AddWithValue("@lname", txtLname.Text.Trim()); cmd.Parameters.AddWithValue("@course", cboCourse.SelectedValue.ToString()); cmd.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim()); cmd.ExecuteNonQuery(); gridBarrowerLoad(""); enabledNewTransbutton(); clearControls(); MessageBox.Show("Record updated successfully"); } } else { return; } con.conclose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnA_Save_Click(object sender, RoutedEventArgs e) { try { //let's check first if textboxes are empty since we cannot insert blank data if (txtCcode.Text.Trim() == null || txtCdesc.Text.Trim() == null) { MessageBox.Show("Course data cannot be empty."); return; } SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { //check first if the record trying to add is already exist string check = "SELECT TOP 1 * FROM tblCourse WHERE coursecode = @ccode and coursedesc = @cdesc"; SqlCommand cmdcheck = new SqlCommand(check, con.Con); cmdcheck.Parameters.AddWithValue("@ccode", txtCcode.Text.Trim()); cmdcheck.Parameters.AddWithValue("@cdesc", txtCdesc.Text.Trim()); SqlDataReader sdr = cmdcheck.ExecuteReader(); if (sdr.Read()) //if theres a record { MessageBox.Show("Cannot Insert. The record you are trying to add is already exist. "); sdr.Close(); return; } else //let's insert and add the data { sdr.Close(); CrudConfiguration insert = new CrudConfiguration(); string[] col = new string[] { "coursecode", "coursedesc" }; string[] values = new string[] { txtCcode.Text.Trim(), txtCdesc.Text.Trim() }; string tablename = "tblCourse"; if (insert.saveTrans(tablename, col, values)) { CourseLoad(""); enabledNewTransbutton(); clearControls(); MessageBox.Show("Record saved successfully"); } } } else { return; } con.conclose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnA_Save_Click(object sender, RoutedEventArgs e) { try { //let's check first if textboxes are empty since we cannot insert blank data if (txtAuthorFname.Text.Trim() == null || txtAuthorLname.Text.Trim() == null) { MessageBox.Show("Author name cannot be empty."); return; } SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { //check first if the record trying to add is already exist string check = "SELECT TOP 1 * FROM tblAuthor WHERE author_fname = @fname and author_lname = @lname"; SqlCommand cmdcheck = new SqlCommand(check, con.Con); cmdcheck.Parameters.AddWithValue("@fname", txtAuthorFname.Text.Trim()); cmdcheck.Parameters.AddWithValue("@lname", txtAuthorLname.Text.Trim()); SqlDataReader sdr = cmdcheck.ExecuteReader(); if (sdr.Read()) //if theres a record { MessageBox.Show("Cannot Insert. The record you are trying to add is already exist. \n Author Firstname: " + txtAuthorFname.Text.Trim() + " \n Author LastName: " + txtAuthorLname.Text.Trim() + " "); sdr.Close(); return; } else //let's insert and add the data { sdr.Close(); string query = "INSERT INTO tblAuthor (author_fname, author_lname) VALUES (@fname, @lname)"; SqlCommand cmd = new SqlCommand(query, con.Con); cmd.Parameters.AddWithValue("@fname", txtAuthorFname.Text.Trim()); cmd.Parameters.AddWithValue("@lname", txtAuthorLname.Text.Trim()); cmd.ExecuteNonQuery(); AuthorLoad(""); enabledNewTransbutton(); clearControls(); MessageBox.Show("Record saved successfully"); } } else { return; } con.conclose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnA_Delete_Click(object sender, RoutedEventArgs e) { try { SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { //let's check first if the data trying to delete is being used by the borrowers list. string check = "SELECT TOP 1 * FROM tblBarrower WHERE course = @id"; SqlCommand cmdcheck = new SqlCommand(check, con.Con); cmdcheck.Parameters.AddWithValue("@id", txtCourseiD.Text.Trim()); SqlDataReader sdr = cmdcheck.ExecuteReader(); if (sdr.Read()) //if theres a record, return, meaning we cannot allow it { MessageBox.Show("Cannot delete record, the data you are trying to delete is being used by the barrowers"); sdr.Close(); return; } else //we can delete it { sdr.Close(); CrudConfiguration delete = new CrudConfiguration(); string tablename = "tblCourse"; //tablename Dictionary <string, string> conds = new Dictionary <string, string> { { "course_id", txtCourseiD.Text.Trim() } }; //the conditions to make if (delete.deleteTrans(tablename, conds)) { CourseLoad(""); enabledNewTransbutton(); clearControls(); MessageBox.Show("Record deleted successfully"); } } } else { return; } con.conclose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnA_Delete_Click(object sender, RoutedEventArgs e) { try { SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { //check first if theres any book from tblBooks that uses the id of author string check = "SELECT TOP 1 * FROM tblBooks WHERE bookauthor_id = @id"; SqlCommand cmdcheck = new SqlCommand(check, con.Con); cmdcheck.Parameters.AddWithValue("@id", txtAuthoriD.Text.Trim()); SqlDataReader sdr = cmdcheck.ExecuteReader(); if (sdr.Read()) //if theres a record { string booktitle = sdr["booktitle"].ToString(); // if ever the booktitle is ask, we can use this. MessageBox.Show("Cannot delete record, there's a reference from book list."); sdr.Close(); return; } else //we can proceed to deleting of that record { sdr.Close(); string query = "DELETE FROM tblAuthor WHERE authorid = @id"; SqlCommand cmd = new SqlCommand(query, con.Con); cmd.Parameters.AddWithValue("@id", txtAuthoriD.Text.Trim()); cmd.ExecuteNonQuery(); AuthorLoad(""); enabledNewTransbutton(); clearControls(); MessageBox.Show("Record deleted successfully"); } } else { return; } con.conclose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnLogin_Click(object sender, RoutedEventArgs e) { SqlConnect con = new SqlConnect(); con.conOpen(); if (con == null) { return; } else { Console.WriteLine("Successfully Connected"); SqlCommand cmd = new SqlCommand("SELECT * FROM tblUsers WHERE username = @username and password = @password ", con.Con); cmd.Parameters.AddWithValue("@username", txtUsername.Text); cmd.Parameters.AddWithValue("@password", txtPassword.Password); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); if (sda.Fill(dt) > 0) { Console.WriteLine("Valid Account"); con.conclose(); this.Close(); n.Show(); } else { Console.WriteLine("Invalid Account"); } //SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM tblUsers WHERE username = '******' and password = '******' ", con.Con); //parametarized //string sql = "SELECT * FROM tblUsers WHERE username = @username and password = @password "; //using (SqlCommand cmd = new SqlCommand(sql, con.Con)) //{ // var acctParam = new SqlParameter("username", SqlDbType.VarChar); // acctParam.Value = txtUsername.Text; // cmd.Parameters.Add(acctParam); // var results = cmd.ExecuteReader(); //} } }
private void btnB_Delete_Click(object sender, RoutedEventArgs e) { try { SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { //let's check first if the data trying to delete has a record in our main transactions. string check = "SELECT TOP 1 * FROM tblTransaction WHERE barrower_id = @id"; SqlCommand cmdcheck = new SqlCommand(check, con.Con); cmdcheck.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim()); SqlDataReader sdr = cmdcheck.ExecuteReader(); if (sdr.Read()) //if theres a record, return, meaning we cannot allow it { MessageBox.Show("Cannot delete record, the data you are trying to delete has a history on main transaction."); sdr.Close(); return; } else //we can change or update it { sdr.Close(); string query = "DELETE FROM tblBarrower WHERE barrower_id = @id"; SqlCommand cmd = new SqlCommand(query, con.Con); cmd.Parameters.AddWithValue("@id", txtBarrowerId.Text.Trim()); cmd.ExecuteNonQuery(); gridBarrowerLoad(""); enabledNewTransbutton(); clearControls(); MessageBox.Show("Record deleted successfully"); } } else { return; } con.conclose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void AuthorLoad(string value) { var _condition = ""; if (value == "") { _condition = ""; } else { //_condition = "WHERE author_fname LIKE '%" + value + "%' OR author_lname LIKE '%" + value + "%'"; _condition = "WHERE author_fname LIKE '%" + @value + "%' OR author_lname LIKE '%" + @value + "%'"; } SqlConnect con = new SqlConnect(); con.conOpen(); if (con == null) { return; } else { try { string q = "SELECT * , (author_fname + ' ' + author_lname )as Author FROM tblAuthor " + _condition; SqlCommand cmd = new SqlCommand(q, con.Con); cmd.Parameters.AddWithValue("@value", value); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); DataTable dt = new DataTable(); sda.Fill(dt); dgAuthor.ItemsSource = dt.DefaultView; dgAuthor.Columns[0].Visibility = Visibility.Collapsed; dgAuthor.Columns[1].Visibility = Visibility.Collapsed; dgAuthor.Columns[2].Visibility = Visibility.Collapsed; } catch (Exception ex) { MessageBox.Show(ex.Message); } } con.conclose(); }
private void CourseLoad(string value) { var _condition = ""; if (value == "") { _condition = ""; } else { _condition = "WHERE coursecode LIKE '%" + @value + "%' OR coursedesc LIKE '%" + @value + "%'"; } SqlConnect con = new SqlConnect(); con.conOpen(); if (con == null) { return; } else { try { string q = "SELECT * FROM tblCourse " + _condition; SqlCommand cmd = new SqlCommand(q, con.Con); cmd.Parameters.AddWithValue("@value", value); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); DataTable dt = new DataTable(); sda.Fill(dt); dgCourse.ItemsSource = dt.DefaultView; dgCourse.Columns[0].Visibility = Visibility.Collapsed; } catch (Exception ex) { MessageBox.Show(ex.Message); } } con.conclose(); }
public bool saveTrans(string table, string[] cols, string[] vals) { try { SqlConnect con = new SqlConnect(); con.conOpen(); string[] param = new string[vals.Length]; for (int y = 0; y < vals.Length; y++) { param[y] = "@param" + y.ToString(); } //param = param.TrimEnd(param[param.Length - 1]); if (con != null) { string query = "INSERT INTO " + table + " (" + getColumns(cols) + ") VALUES (" + separateParam(param) + ") "; SqlCommand cmd = new SqlCommand(query, con.Con); int count = 0; foreach (string paramX in vals) { var valParam = "@param" + count.ToString(); cmd.Parameters.AddWithValue(valParam, paramX); Console.WriteLine("param:" + valParam + " value:" + paramX); count += 1; } Console.WriteLine(query); cmd.ExecuteNonQuery(); } else { return(false); } } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } return(true); }
public void comboBoxLoad() { SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { string q = "SELECT * , (author_fname + ' ' + author_lname) as Author FROM tblAuthor"; SqlCommand cmd = new SqlCommand(q, con.Con); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet dt = new DataSet(); sda.Fill(dt, "tblAuthor"); cboBauthor.ItemsSource = dt.Tables[0].DefaultView; cboBauthor.DisplayMemberPath = dt.Tables[0].Columns["Author"].ToString(); cboBauthor.SelectedValuePath = dt.Tables[0].Columns["authorid"].ToString(); } else { return; } con.conclose(); }
private void gridLoading(string value) { var _condition = ""; if (value == "") { _condition = ""; } else { _condition = "WHERE A.booktitle LIKE '%" + @value + "%' OR B.author_fname LIKE '%" + @value + "%' OR B.author_lname LIKE '%" + @value + "%'"; } SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { string q = "SELECT A.*, (B.author_fname + ' ' + B.author_lname) AS Author FROM tblBooks A INNER JOIN tblAuthor B ON A.bookauthor_id = B.authorid " + _condition; SqlCommand cmd = new SqlCommand(q, con.Con); cmd.Parameters.AddWithValue("@value", value); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); dgBooks.ItemsSource = dt.DefaultView; dgBooks.Columns[0].Visibility = Visibility.Collapsed; dgBooks.Columns[2].Visibility = Visibility.Collapsed; dgBooks.Columns[1].Header = "Book Title"; dgBooks.Columns[6].Header = "Author"; dgBooks.Columns[3].Header = "Published"; dgBooks.Columns[4].Header = "Quantity"; dgBooks.Columns[5].Header = "Stocks"; } else { return; } con.conclose(); }
private void UserControl_Loaded(object sender, RoutedEventArgs e) { SqlConnect con = new SqlConnect(); con.conOpen(); if (con == null) { return; } else { string query = "SELECT COUNT(*) as 'count' FROM tblTransactions WHERE status = 0"; SqlCommand cmd = new SqlCommand(query, con.Con); SqlDataReader sdr = cmd.ExecuteReader(); if (sdr.Read()) { string unreturned = sdr["count"].ToString(); txtUnreturnedbook.Text = "Unreturned books: " + unreturned; Console.WriteLine("Unreturned books:" + sdr["count"]); } } }
public void loadCourse() { SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { string q = "SELECT course_id, coursecode FROM tblCourse"; SqlCommand cmd = new SqlCommand(q, con.Con); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataSet dt = new DataSet(); sda.Fill(dt, "tblCourse"); cboCourse.ItemsSource = dt.Tables[0].DefaultView; cboCourse.DisplayMemberPath = dt.Tables[0].Columns["coursecode"].ToString(); cboCourse.SelectedValuePath = dt.Tables[0].Columns["course_id"].ToString(); } else { return; } con.conclose(); }
public bool updateTrans(string table, string[] cols, string[] vals, Dictionary <string, string> conds) { try { SqlConnect con = new SqlConnect(); con.conOpen(); string[] param = new string[vals.Length]; for (int y = 0; y < vals.Length; y++) { param[y] = "@param" + y.ToString(); } if (con != null) { string query = "UPDATE " + table + " SET " + joinedColsVals(cols, param) + " WHERE " + iterateCond(conds); SqlCommand cmd = new SqlCommand(query, con.Con); int count = 0; foreach (string paramX in vals) { var valParam = "@param" + count.ToString(); cmd.Parameters.AddWithValue(valParam, paramX); Console.WriteLine("param:" + valParam + " value:" + paramX); count += 1; } Console.WriteLine(query); cmd.ExecuteNonQuery(); } else { return(false); } } catch (Exception ex) { MessageBox.Show(ex.Message); return(false); } return(true); }
private void gridBarrowerLoad(string value) { var _condition = ""; if (value == "") { _condition = ""; } else { _condition = "WHERE A.barrower_fname LIKE '%" + @value + "%' OR A.barrower_lastname LIKE '%" + @value + "%' OR B.coursecode LIKE '%" + @value + "%'"; } SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { string q = "SELECT A.*, B.course_id FROM tblBarrower A INNER JOIN tblCourse B ON A.course = B.course_id " + _condition; SqlCommand cmd = new SqlCommand(q, con.Con); cmd.Parameters.AddWithValue("@value", value); SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); dgBarrower.ItemsSource = dt.DefaultView; dgBarrower.Columns[0].Header = "Barrower Id"; dgBarrower.Columns[1].Header = "First Name"; dgBarrower.Columns[2].Header = "Last Name"; dgBarrower.Columns[3].Header = "Course"; } else { return; } con.conclose(); }
private void btnB_Save_Click(object sender, RoutedEventArgs e) { try { //let's check first if the textboxes are empty since we cannot allow a blank data if (txtbTitle.Text.Trim() == null || txtbQty.Text.Trim() == null || cboBauthor.Text == null || dP_datePublished.Text == null) { MessageBox.Show("Book info cannot be empty"); return; } if (Convert.ToInt32(txtbQty.Text) < 0) { MessageBox.Show("Quantity cannot be less than zero"); return; } SqlConnect con = new SqlConnect(); con.conOpen(); if (con != null) { //let's check first if the data trying to change is already exist. string check = "SELECT TOP 1 * FROM tblBooks WHERE booktitle = @title"; SqlCommand cmdcheck = new SqlCommand(check, con.Con); cmdcheck.Parameters.AddWithValue("@title", txtbTitle.Text.Trim()); SqlDataReader sdr = cmdcheck.ExecuteReader(); if (sdr.Read()) //if theres a record, return, meaning we cannot allow it { MessageBox.Show("Cannot add this record, the data you are trying to insert already exist."); sdr.Close(); return; } else //we can add or insert it { sdr.Close(); string query = "INSERT INTO tblBooks (booktitle, bookauthor_id, datepublished, qty, stocks) VALUES (@title, @author, @dtpub, @qty, @qty)"; SqlCommand cmd = new SqlCommand(query, con.Con); cmd.Parameters.AddWithValue("@title", txtbTitle.Text.Trim()); cmd.Parameters.AddWithValue("@author", cboBauthor.SelectedValue.ToString()); cmd.Parameters.AddWithValue("@dtpub", dP_datePublished.Text.Trim()); cmd.Parameters.AddWithValue("@qty", txtbQty.Text.Trim()); //cmd.Parameters.AddWithValue("@stocks", txtbStocks.Text.Trim()); cmd.ExecuteNonQuery(); gridLoading(""); enabledNewTransbutton(); clearControls(); MessageBox.Show("Record saved successfully"); } } else { return; } con.conclose(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }