public static void ConnectInsertTocustomerPaymentTable(CustomerPaymentData customerDataObject)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
FeedcustomerData(msqlCommand, customerDataObject);
}
catch (Exception er)
{
}
finally
{
msqlConnection.Close();
}
}
static void FeedcustomerData(MySql.Data.MySqlClient.MySqlCommand msqlCommand, CustomerPaymentData cusomerDataObject)
{
//define the command text
msqlCommand.CommandText = "INSERT INTO customer_payment(customer_id,payment_amount,payment_date,payment_id)"
+ "VALUES (@customer_id,@payment_amount,@payment_date,@payment_id)";
msqlCommand.Parameters.AddWithValue("@customer_id", cusomerDataObject.customerId);
msqlCommand.Parameters.AddWithValue("@payment_id", cusomerDataObject.paymentId);
msqlCommand.Parameters.AddWithValue("@payment_amount", cusomerDataObject.paymentAmount);
msqlCommand.Parameters.AddWithValue("@payment_date", cusomerDataObject.paymentDate);
msqlCommand.ExecuteNonQuery();
}
public static void EditCustomerPayments(CustomerPaymentData returnEditedCustomerPaymentsData)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "UPDATE customer_payment SET payment_amount='" + returnEditedCustomerPaymentsData.paymentAmount + "' WHERE payment_id='" + returnEditedCustomerPaymentsData.paymentId + "'; ";
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
public static List<CustomerPaymentData> FetchCustomerPaymentData()
{
List<CustomerPaymentData> customerPaymentCollection = new List<CustomerPaymentData>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select customers.id, customers.address, customers.ph_no,customers.customer_name, customer_payment.payment_id, customer_payment.payment_amount, customer_payment.payment_date FROM customers,customer_payment WHERE customer_payment.customer_id = customers.id;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
CustomerPaymentData addCustomerPaymentWindowObject = new CustomerPaymentData();
//addCustomerPaymentWindowObject.serialNo = (_customerPaymentCollection.Count + 1).ToString();
addCustomerPaymentWindowObject.customerId = msqlReader.GetString("id");
addCustomerPaymentWindowObject.paymentId = msqlReader.GetString("payment_id");
addCustomerPaymentWindowObject.customerName = msqlReader.GetString("customer_name");
addCustomerPaymentWindowObject.customerAddress = msqlReader.GetString("address");
addCustomerPaymentWindowObject.customerPhone = msqlReader.GetString("ph_no");
addCustomerPaymentWindowObject.paymentAmount = msqlReader.GetDouble("payment_amount");
addCustomerPaymentWindowObject.paymentDate = msqlReader.GetDateTime("payment_date");
customerPaymentCollection.Add(addCustomerPaymentWindowObject);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return customerPaymentCollection;
}
public addCustomerPaymentWindow()
{
InitializeComponent();
customerPaymentData = new CustomerPaymentData();
}