} // end getFilteredCustomerList
public static void addCustomerRoster(CustomerRoster cust)
{
addCustomer(cust);
string query = Scripts.sqlAddCustomerRoster;
using (
OleDbCommand dbCommand = new OleDbCommand()
{
Connection = new OleDbConnection(ConnectionAccess.connString),
CommandType = CommandType.Text,
CommandText = query,
Parameters =
{
new OleDbParameter("@DateOfService", OleDbType.Date),
new OleDbParameter("@FirstName", cust.FirstName),
new OleDbParameter("@MiddleInitial", cust.MiddleInitial),
new OleDbParameter("@LastName", cust.LastName),
new OleDbParameter("@DOB", OleDbType.Date),
new OleDbParameter("@PhoneNumber", cust.PhoneNumber),
new OleDbParameter("@Staff", cust.Staff),
new OleDbParameter("@EnrollmentType", cust.EnrollmentType),
new OleDbParameter("@ReasonForVisit", cust.ReasonForVisit),
new OleDbParameter("@IntakeDate", OleDbType.Date),
new OleDbParameter("@ISIS_ID", cust.ISIS_ID),
new OleDbParameter("@AgeGroup", cust.AgeGroup),
new OleDbParameter("@SelfCertified", cust.SelfCertified),
new OleDbParameter("@PSAExpDate", OleDbType.Date),
new OleDbParameter("@YouthSchool", cust.YouthSchool),
new OleDbParameter("@Email", cust.Email),
new OleDbParameter("@Notes", cust.Notes)
}
}) // end using parenthetical
{ // begin using scope
dbCommand.Parameters[0].Value = cust.DateOfService;
dbCommand.Parameters[4].Value = cust.DOB;
dbCommand.Parameters[9].Value = cust.IntakeDate;
dbCommand.Parameters[13].Value = cust.PSAExpDate;
foreach (OleDbParameter param in dbCommand.Parameters)
{ // replace ambiguous null values with explicit DBNulls.
if (param.Value == null)
{
param.Value = DBNull.Value;
}
}
dbCommand.Connection.Open();
int rowsAffected = dbCommand.ExecuteNonQuery();
dbCommand.Connection.Close();
Console.WriteLine($"Rows affected: {rowsAffected}");
}
}
// just said screw it and put all the properties in Customer.
// Not using reflection for the different types anymore(Just Customer), so it should be fine(?)
#endregion End Properties
#region Methods
/// <summary>
/// Returns a List of CustomerRoster objects (variant of DataTable Method)
/// </summary>
/// <param name="Customer_ID"></param>
/// <returns></returns>
public static List <CustomerRoster> getCustomerList(int?Customer_ID = null)
{
DataTable dt = new DataTable();
List <CustomerRoster> customerRosterList = new List <CustomerRoster>();
//string query = Scripts.sqlGetCustomerDetails;
string query = Scripts.sqlGetCustomerRosterDetails;
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter())
{
// Create the command and set its properties
dataAdapter.SelectCommand = new OleDbCommand();
dataAdapter.SelectCommand.Connection = new OleDbConnection(ConnectionAccess.connString);
dataAdapter.SelectCommand.CommandType = CommandType.Text;
// Assign the SQL to the command object
dataAdapter.SelectCommand.CommandText = query;
if (Customer_ID != null)
{
dataAdapter.SelectCommand.Parameters.AddWithValue("@Customer_ID", Customer_ID);
}
else
{
dataAdapter.SelectCommand.Parameters.AddWithValue("@Customer_ID", DBNull.Value);
}
// Fill the datatable from adapter
try
{
dataAdapter.Fill(dt);
}
catch (Exception ex)
{
Console.Error.WriteLine($"An Error Occured trying to retrieve the data!.\n\nException: {ex}");
}
} // end using scope
foreach (DataRow row in dt.Rows)
{
CustomerRoster tempCust = new CustomerRoster();
foreach (PropertyInfo property in typeof(CustomerRoster).GetProperties())
{
var rowVal = row[$"{property.Name}"];
if (rowVal.GetType() == typeof(DBNull))
{
rowVal = null;
}
property.SetValue(tempCust, rowVal);
}
customerRosterList.Add(tempCust);
}
return(customerRosterList);
}
