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