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