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