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