Ejemplo n.º 1
0
        public static List <COInsured> GetUsers(string _cocno)
        {
            List <COInsured> users = new List <COInsured>();
            String           cmdText;
            MySqlDataReader  reader = null;

            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();

            //get data from db and load...

            //To query data from the database using MySqlDataReader
            cmdText = "SELECT id,cid,firstname,middlename,lastname, DATE_FORMAT(birthdate,'%m-%d-%Y'),relationship FROM tblcoinsured WHERE cid=@cocno";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);

            cmd.Parameters.AddWithValue("@cocno", _cocno);

            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    COInsured s = new COInsured(reader.GetInt16(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6));
                    //Console.WriteLine(reader.GetString(0) + "|" + reader.GetString(1) + "|" + reader.GetString(2));
                    users.Add(s);
                }
            }
            reader.Close();
            return(users);
        }
Ejemplo n.º 2
0
        public static List <Client> GetUsers(string search)
        {
            List <Client>   users = new List <Client>();
            String          cmdText;
            MySqlDataReader reader = null;

            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();

            //get data from db and load...

            //To query data from the database using MySqlDataReader
            cmdText = "SELECT id,cocno,firstname,middlename,lastname,DATE_FORMAT(date_received,'%m-%d-%Y'),street,barangay,city,zipcode,company,position,gender,civilstatus,bloodtype,groupp,amount,status,DATE_FORMAT(birthdate,'%m-%d-%Y'),mobile,phone,units from tblclient WHERE cocno like @search or firstname like @search or middlename like @search or lastname like @search";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);

            cmd.Prepare();

            //we will bound a value to the placeholder
            cmd.Parameters.AddWithValue("@search", "%" + search + "%");
            cmd.ExecuteNonQuery();

            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Client s = new Client(reader.GetInt16(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetInt32(9), reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetString(13), reader.GetString(14), reader.GetString(15), reader.GetDouble(16), reader.GetBoolean(17), reader.GetString(18), reader.GetString(19), reader.GetString(20), reader.GetInt32(21));
                    //Console.WriteLine(reader.GetString(0) + "|" + reader.GetString(1) + "|" + reader.GetString(2));
                    users.Add(s);
                }
            }
            reader.Close();
            return(users);
        }
Ejemplo n.º 3
0
        public void AddNewCI()
        {
            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();

            //DateTime dr = Convert.ToDateTime(dateReceived);
            //DateTime bd = Convert.ToDateTime(birthdate);

            //This is the mysql command that we will query into the db.
            //It uses Prepared statements and the Placeholder is @name.
            //Using prepared statements is faster and secure.
            //TO INSERT values into the database using prepares statements
            String       cmdText = "INSERT INTO tblcoinsured VALUES(0,@cid,@firstname,@middlename,@lastname,STR_TO_DATE(@birthdate,'%m/%d/%Y'),@relationship)";
            MySqlCommand cmd     = new MySqlCommand(cmdText, con);

            cmd.Prepare();

            //we will bound a value to the placeholder
            //cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@cid", cid);
            cmd.Parameters.AddWithValue("@firstname", firstname);
            cmd.Parameters.AddWithValue("@middlename", middlename);
            cmd.Parameters.AddWithValue("@lastname", lastname);
            cmd.Parameters.AddWithValue("@relationship", relationship);
            cmd.Parameters.AddWithValue("@birthdate", birthdate);


            cmd.ExecuteNonQuery(); //execute the mysql command

            con.Close();
        }
Ejemplo n.º 4
0
        public void EditB()
        {
            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();

            String       cmdText = "UPDATE tblbenificiaries SET cid=@cid,firstname=@firstname,middlename=@middlename,lastname=@lastname,relationship=@relationship,address=@address,birthdate=STR_TO_DATE(@birthdate,'%m/%d/%Y'),gender=@gender,phone=@phone,mobile=@mobile WHERE id =@id";
            MySqlCommand cmd     = new MySqlCommand(cmdText, con);

            cmd.Prepare();

            //we will bound a value to the placeholder
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@cid", cid);
            cmd.Parameters.AddWithValue("@firstname", firstname);
            cmd.Parameters.AddWithValue("@middlename", middlename);
            cmd.Parameters.AddWithValue("@lastname", lastname);
            cmd.Parameters.AddWithValue("@relationship", relationship);
            cmd.Parameters.AddWithValue("@address", address);
            cmd.Parameters.AddWithValue("@birthdate", birthdate);
            cmd.Parameters.AddWithValue("@gender", gender);
            cmd.Parameters.AddWithValue("@phone", phone);
            cmd.Parameters.AddWithValue("@mobile", mobile);
            cmd.ExecuteNonQuery(); //execute the mysql command

            con.Close();
        }
Ejemplo n.º 5
0
        public static List <Beneficiaries> GetUsers()
        {
            List <Beneficiaries> users = new List <Beneficiaries>();
            String          cmdText;
            MySqlDataReader reader = null;

            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();

            //get data from db and load...

            //To query data from the database using MySqlDataReader
            cmdText = "SELECT * from tblbeneficiaries";
            MySqlCommand cmd = new MySqlCommand(cmdText, con);

            reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Beneficiaries s = new Beneficiaries(reader.GetInt16(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetString(9), reader.GetString(10));
                    //Console.WriteLine(reader.GetString(0) + "|" + reader.GetString(1) + "|" + reader.GetString(2));
                    users.Add(s);
                }
            }
            reader.Close();
            return(users);
        }
Ejemplo n.º 6
0
        public void Edit()
        {
            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();

            String       cmdText = "UPDATE tblclient SET street=@street, barangay=@barangay,city=@city,zipcode=@zipcode,firstname=@firstname,lastname=@lastname,middlename=@middlename,company=@company,position=@position,birthdate=@birthdate,gender=@gender,civilstatus=@civilstatus, bloodtype=@bloodtype,phone=@phone,mobile=@mobile,units=@units,amount=@amount,status=@status,group=@group";
            MySqlCommand cmd     = new MySqlCommand(cmdText, con);

            cmd.Prepare();

            //we will bound a value to the placeholder
            cmd.Parameters.AddWithValue("@street", street);
            cmd.Parameters.AddWithValue("@barangay", barangay);
            cmd.Parameters.AddWithValue("@city", city);
            cmd.Parameters.AddWithValue("@zipcode", zipcode);
            cmd.Parameters.AddWithValue("@firstname", firstname);
            cmd.Parameters.AddWithValue("@lastname", lastname);
            cmd.Parameters.AddWithValue("@middlename", middlename);
            cmd.Parameters.AddWithValue("@company", company);
            cmd.Parameters.AddWithValue("@position", position);
            cmd.Parameters.AddWithValue("@birthdate", birthdate);
            cmd.Parameters.AddWithValue("@gender", gender);
            cmd.Parameters.AddWithValue("@civilstatus", civilstatus);
            cmd.ExecuteNonQuery(); //execute the mysql command

            con.Close();
        }
Ejemplo n.º 7
0
        public void AddNew()
        {
            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();

            //DateTime dr = Convert.ToDateTime(dateReceived);
            //DateTime bd = Convert.ToDateTime(birthdate);

            //This is the mysql command that we will query into the db.
            //It uses Prepared statements and the Placeholder is @name.
            //Using prepared statements is faster and secure.
            //TO INSERT values into the database using prepares statements
            String       cmdText = "INSERT INTO tblclient VALUES(0,@cocno,STR_TO_DATE(@date_received,'%m/%d/%Y'),@street,@barangay,@city,@zipcode,@firstname,@lastname,@middlename,@company,@position,STR_TO_DATE(@birthdate,'%m/%d/%Y'),@gender,@civilstatus,@bloodtype,@phone,@mobile,@units,@amount,@status,@group)";
            MySqlCommand cmd     = new MySqlCommand(cmdText, con);

            cmd.Prepare();

            //we will bound a value to the placeholder

            cmd.Parameters.AddWithValue("@cocno", cocno);
            cmd.Parameters.AddWithValue("@date_received", dateReceived);
            cmd.Parameters.AddWithValue("@street", street);
            cmd.Parameters.AddWithValue("@barangay", barangay);
            cmd.Parameters.AddWithValue("@city", city);
            cmd.Parameters.AddWithValue("@zipcode", zipcode);
            cmd.Parameters.AddWithValue("@firstname", firstname);
            cmd.Parameters.AddWithValue("@lastname", lastname);
            cmd.Parameters.AddWithValue("@middlename", middlename);
            cmd.Parameters.AddWithValue("@company", company);
            cmd.Parameters.AddWithValue("@position", position);
            cmd.Parameters.AddWithValue("@birthdate", birthdate);
            cmd.Parameters.AddWithValue("@gender", gender);
            cmd.Parameters.AddWithValue("@civilstatus", civilstatus);
            cmd.Parameters.AddWithValue("@bloodtype", bloodtype);
            cmd.Parameters.AddWithValue("@phone", phone);
            cmd.Parameters.AddWithValue("@mobile", mobile);
            cmd.Parameters.AddWithValue("@units", units);
            cmd.Parameters.AddWithValue("@amount", amount);
            cmd.Parameters.AddWithValue("@status", status);
            cmd.Parameters.AddWithValue("@group", group);

            cmd.ExecuteNonQuery(); //execute the mysql command

            con.Close();
        }
Ejemplo n.º 8
0
        public static Client GetUser(int personId)
        {
            Client          p = null;
            string          cmdText;
            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();


            cmdText  = "SELECT * FROM tblclient ";
            cmdText += "WHERE id=" + personId;

            MySqlCommand cmd = new MySqlCommand(cmdText, con);

            MySqlDataReader reader = null;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    //call sproc and load from database...
                    p = new Client(reader.GetInt16(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6), reader.GetString(7), reader.GetString(8), reader.GetInt32(9), reader.GetString(10), reader.GetString(11), reader.GetString(12), reader.GetString(13), reader.GetString(14), reader.GetString(15), reader.GetDouble(16), reader.GetBoolean(17), reader.GetString(18), reader.GetString(19), reader.GetString(20), reader.GetInt32(21));
                }

                reader.Close();
            }
            catch (Exception err)
            {
                Console.WriteLine("Error: " + err.ToString());
            }
            finally
            {
                con.Close();
            }

            return(p);
        }
Ejemplo n.º 9
0
        public static COInsured GetUser(int personId)
        {
            COInsured       p = null;
            string          cmdText;
            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();


            cmdText  = "SELECT * FROM tblcoinsured";
            cmdText += "WHERE id=" + personId;

            MySqlCommand cmd = new MySqlCommand(cmdText, con);

            MySqlDataReader reader = null;

            try
            {
                reader = cmd.ExecuteReader();

                if (reader.HasRows)
                {
                    reader.Read();
                    //call sproc and load from database...
                    p = new COInsured(reader.GetInt16(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.GetString(5), reader.GetString(6));
                }

                reader.Close();
            }
            catch (Exception err)
            {
                Console.WriteLine("Error: " + err.ToString());
            }
            finally
            {
                con.Close();
            }

            return(p);
        }
Ejemplo n.º 10
0
        public void Delete()
        {
            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();

            try
            {
                string       cmdText = "DELETE FROM tblclient WHERE id = @id";
                MySqlCommand cmd     = new MySqlCommand(cmdText, con);
                cmd.Parameters.AddWithValue("@id", Id);

                int result = cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                Console.WriteLine("Error: " + err.ToString());
            }
            finally
            {
                con.Close();
            }
        }
Ejemplo n.º 11
0
        public void EditCI()
        {
            MySqlConnection con = null;

            con = DBConnection.GetDBConnection();

            String       cmdText = "UPDATE tblcoinsured SET cid=@cid,firstname=@firstname,middlename=@middlename,lastname=@lastname,relationship=@relationship,birthdate=STR_TO_DATE(@birthdate,'%m/%d/%Y') WHERE id =@id";
            MySqlCommand cmd     = new MySqlCommand(cmdText, con);

            cmd.Prepare();

            //we will bound a value to the placeholder
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@cid", cid);
            cmd.Parameters.AddWithValue("@firstname", firstname);
            cmd.Parameters.AddWithValue("@middlename", middlename);
            cmd.Parameters.AddWithValue("@lastname", lastname);
            cmd.Parameters.AddWithValue("@relationship", relationship);
            cmd.Parameters.AddWithValue("@birthdate", birthdate);
            cmd.ExecuteNonQuery(); //execute the mysql command

            con.Close();
        }