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 cocno=@cocno,date_received=STR_TO_DATE(@date_received,'%m/%d/%Y'),street=@street,barangay=@barangay,city=@city,zipcode=@zipcode,firstname=@firstname,lastname=@lastname,middlename=@middlename,company=@company,position=@position,birthdate=STR_TO_DATE(@birthdate,'%m/%d/%Y'),gender=@gender,civilstatus=@civilstatus,bloodtype=@bloodtype,phone=@phone,mobile=@mobile,units=@units,amount=@amount,status=@status,groupp=@group 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("@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 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 static Beneficiaries GetUser(int personId) { Beneficiaries p = null; string cmdText; MySqlConnection con = null; con = DBConnection.GetDBConnection(); cmdText = "SELECT * FROM tblbeneficiaries"; 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 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)); } reader.Close(); } catch (Exception err) { Console.WriteLine("Error: " + err.ToString()); } finally { con.Close(); } return(p); }