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);
}
}
