I am developing a report at the moment and I want to apply OOP principle because I feel like my code is not well written and there is duplication.
I have a method that gets data from the database and binds it to the controls on my report.
Here is a sample method I used to print preview a report:
public static void CreateReportResult(string poNumber)
{
DataSet ds = new DataSet("PurchaseOrders");
Dal.GetDataSet(ds, sqlQuery(poNumber));
PurchaseReport purchaseOrderReport = new PurchaseReport
{
DataSource = ds,
DataMember = "PurchaseOrders"
};
purchaseOrderReport.xrDateCreated.DataBindings.Add("Text", null, "PurchaseOrders.DateCreated", "{0:dddd, d MMMM, yyyy}");
purchaseOrderReport.xrPO.DataBindings.Add("Text", null, "PurchaseOrders.PONumber", "{0}");
purchaseOrderReport.xrVendor.DataBindings.Add("Text", null, "PurchaseOrders.POVendor", "{0}");
purchaseOrderReport.xrPromocode.DataBindings.Add("Text", null, "PurchaseOrders.PromoCode", "{0}");
//Bill Group
//purchaseOrderReport.xrBusinessName.DataBindings.Add("Text", null, "PurchaseOrders.b_BillToGroup", "{0}");
purchaseOrderReport.xrAddress1.DataBindings.Add("Text", null, "PurchaseOrders.b_AddressLine1", "{0}");
purchaseOrderReport.xrAddress2.DataBindings.Add("Text", null, "PurchaseOrders.b_AddressLine2", "{0}");
purchaseOrderReport.xrCityStateZip.DataBindings.Add("Text", null, "PurchaseOrders.CityStateZip", "{0}");
purchaseOrderReport.xrPhone.DataBindings.Add("Text", null, "PurchaseOrders.b_Phone", "{0}");
//Customer
purchaseOrderReport.xrCustomerBusinessName.DataBindings.Add("Text", null, "PurchaseOrders.CustomerBusinessName", "{0}");
purchaseOrderReport.xrCustomerName.DataBindings.Add("Text", null, "PurchaseOrders.CustomerName", "{0}");
purchaseOrderReport.xrCustomerAddress.DataBindings.Add("Text", null, "PurchaseOrders.customerAddress", "{0}");
purchaseOrderReport.xrCustomerCityStateZip.DataBindings.Add("Text", null, "PurchaseOrders.CustomerCityStateZip", "{0}");
purchaseOrderReport.xrCustomerPhone.DataBindings.Add("Text", null, "PurchaseOrders.CustomerPhone", "{0}");
//Items
purchaseOrderReport.xrTableCell4.DataBindings.Add("Text", null, "PurchaseOrders.ModelNumber", "{0}");
purchaseOrderReport.xrTableCell5.DataBindings.Add("Text", null, "PurchaseOrders.Quantity", "{0}");
purchaseOrderReport.xrTableCell8.DataBindings.Add("Text", null, "PurchaseOrders.Price", "{0}");
purchaseOrderReport.xrTableCell6.DataBindings.Add("Text", null, "PurchaseOrders.Description", "{0}");
//LiftGate
// purchaseOrderReport.xrLiftGate.DataBindings.Add("Text", null, "PurchaseOrders.LiftGate", "{0}");
DataTable dt = new DataTable();
Dal.GetDataTable(dt, "SELECT TOP 1 LiftGate, UPSAccount, LocalPickUp from Purchase_Order where PONumber='" + poNumber + "'");
foreach (DataRow row in dt.Rows)
{
if ((bool)row["UPSAccount"])
{
purchaseOrderReport.xrUPS.Visible = true;
}
if ((bool)row["LocalPickUp"])
{
purchaseOrderReport.xrPickedUp.Visible = true;
}
if ((bool)row["LiftGate"])
{
purchaseOrderReport.xrLiftGate.Text = "YES";
}
else
{
purchaseOrderReport.xrLiftGate.Text = "NO";
}
}
ReportPrintTool printTool = new ReportPrintTool(purchaseOrderReport);
printTool.ShowRibbonPreview(); //Preview a Report
}
Down here at the bottom of the code is the actual code use to print preview the report. I also have another method same as the one above but its function is to print the report.
The problem with my code is when I need to change something I need to change it on my print method as well which breaks the law of OOP. Anyone here who's willing to guide me write this one into a better, more maintainable piece of code.
By the way, this is the code for my Print method
public static void PrintPDF(string poNumber)
{
#region PrintPDF
DataSet ds = new DataSet("PurchaseOrders");
Dal.GetDataSet(ds, sqlQuery(poNumber));
PurchaseReport purchaseOrderReport = new PurchaseReport
{
DataSource = ds,
DataMember = "PurchaseOrders"
};
purchaseOrderReport.xrDateCreated.DataBindings.Add("Text", null, "PurchaseOrders.DateCreated",
"{0:dddd, d MMMM, yyyy}");
purchaseOrderReport.xrPO.DataBindings.Add("Text", null, "PurchaseOrders.PONumber", "{0}");
purchaseOrderReport.xrVendor.DataBindings.Add("Text", null, "PurchaseOrders.POVendor", "{0}");
purchaseOrderReport.xrPromocode.DataBindings.Add("Text", null, "PurchaseOrders.PromoCode", "{0}");
//Bill Group
//purchaseOrderReport.xrBusinessName.DataBindings.Add("Text", null, "PurchaseOrders.b_BillToGroup", "{0}");
purchaseOrderReport.xrAddress1.DataBindings.Add("Text", null, "PurchaseOrders.b_AddressLine1", "{0}");
purchaseOrderReport.xrAddress2.DataBindings.Add("Text", null, "PurchaseOrders.b_AddressLine2", "{0}");
purchaseOrderReport.xrCityStateZip.DataBindings.Add("Text", null, "PurchaseOrders.CityStateZip", "{0}");
purchaseOrderReport.xrPhone.DataBindings.Add("Text", null, "PurchaseOrders.b_Phone", "{0}");
//Customer
purchaseOrderReport.xrCustomerBusinessName.DataBindings.Add("Text", null,
"PurchaseOrders.CustomerBusinessName", "{0}");
purchaseOrderReport.xrCustomerName.DataBindings.Add("Text", null, "PurchaseOrders.CustomerName", "{0}");
purchaseOrderReport.xrCustomerAddress.DataBindings.Add("Text", null, "PurchaseOrders.customerAddress", "{0}");
purchaseOrderReport.xrCustomerCityStateZip.DataBindings.Add("Text", null,
"PurchaseOrders.CustomerCityStateZip", "{0}");
purchaseOrderReport.xrCustomerPhone.DataBindings.Add("Text", null, "PurchaseOrders.CustomerPhone", "{0}");
//Items
purchaseOrderReport.xrTableCell4.DataBindings.Add("Text", null, "PurchaseOrders.ModelNumber", "{0}");
purchaseOrderReport.xrTableCell5.DataBindings.Add("Text", null, "PurchaseOrders.Quantity", "{0}");
purchaseOrderReport.xrTableCell8.DataBindings.Add("Text", null, "PurchaseOrders.Price", "{0}");
purchaseOrderReport.xrTableCell6.DataBindings.Add("Text", null, "PurchaseOrders.Description", "{0}");
//LiftGate
// purchaseOrderReport.xrLiftGate.DataBindings.Add("Text", null, "PurchaseOrders.LiftGate", "{0}");
DataTable dt = new DataTable();
Dal.GetDataTable(dt,
"SELECT TOP 1 LiftGate, UPSAccount, LocalPickUp from Purchase_Order where PONumber='" + poNumber + "'");
foreach (DataRow row in dt.Rows)
{
if ((bool) row["UPSAccount"])
{
purchaseOrderReport.xrUPS.Visible = true;
}
if ((bool) row["LocalPickUp"])
{
purchaseOrderReport.xrPickedUp.Visible = true;
}
if ((bool) row["LiftGate"])
{
purchaseOrderReport.xrLiftGate.Text = "YES";
}
else
{
purchaseOrderReport.xrLiftGate.Text = "NO";
}
}
ReportPrintTool printTool = new ReportPrintTool(purchaseOrderReport);
printTool.Print();
#endregion
}
And this is my code for the querying the database to get the data..
private static string sqlQuery(string poNumber)
{
string sql= @"SELECT a.POVendor ,
a.PONumber ,
a.DateCreated ,
a.PromoCode ,
a.CustomerBusinessName ,
a.CustomerName ,
CONCAT(a.CustomerAddress1, ' ', a.CustomerAddress2) AS customerAddress,
CONCAT(a.CustomerCity, ' ', a.CustomerState, ' ', a.CustomerZip) AS CustomerCityStateZip,
a.CustomerPhone ,
a.b_BillToGroup ,
a.b_AddressLine1 ,
a.b_AddressLine2 ,
a.b_City,
a.b_State,
a.b_Zip,
CONCAT(a.b_City, ' ', a.b_State, ' ', a.b_Zip) as CityStateZip ,
a.b_Phone ,
b.ModelNumber ,
b.Quantity ,
b.Description,
'$' + Cast(b.Price as varchar(10)) as Price
FROM PURCHASE_ORDER a
INNER JOIN PURCHASE_ORDER_ITEMS b ON a.PONumber = b.PurchaseOrderItems
WHERE a.PONumber='" + poNumber + "'";
return sql;
}
This report is created using DevExpress Report, just in case you need other information.
poNumber
in your code is user-provided I'd like to suggest you use a prepared statement. In the case of MySQL it would be something likeMySqlCommand cmd = new MySqlCommand("SELECT ... FROM ... WHERE a.PONumber = ?poNumber", connection); cmd.Parameters.Add(new MySqlParameter("?poNumber", poNumber);
\$\endgroup\$poNumber
it ever gets passed. Prepared statements allow it to use the plan cache instead. \$\endgroup\$