Esempio n. 1
0
        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);
            }
        }
Esempio n. 2
0
        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();
        }
Esempio n. 3
0
        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();
            }
        }
Esempio n. 4
0
 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);
 }
Esempio n. 5
0
        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);
            }
        }
Esempio n. 6
0
        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);
            }
        }
Esempio n. 7
0
        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);
            }
        }
Esempio n. 8
0
        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);
            }
        }
Esempio n. 9
0
        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);
            }
        }
Esempio n. 10
0
        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();
                //}
            }
        }
Esempio n. 11
0
        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);
            }
        }
Esempio n. 12
0
        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();
        }
Esempio n. 13
0
        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();
        }
Esempio n. 14
0
        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);
        }
Esempio n. 15
0
        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();
        }
Esempio n. 16
0
        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();
        }
Esempio n. 17
0
        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"]);
                }
            }
        }
Esempio n. 18
0
        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();
        }
Esempio n. 19
0
 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);
 }
Esempio n. 20
0
        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();
        }
Esempio n. 21
0
        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);
            }
        }