//Class Methods /// <summary> /// Fills the class with Address information from database using addressId /// </summary> /// <param name="existingAddressId"></param> /// <param name="conn"></param> private void setDatabaseAddressInfo(int existingAddressId, MySqlConnection conn) { using (conn) { string myQuery = "SELECT * FROM address WHERE addressId = " + existingAddressId.ToString(); MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery); while (reader.Read()) { addressId = (int)reader["addressId"]; address = reader["address"].ToString(); address2 = reader["address2"].ToString(); cityId = (int)reader["cityId"]; postalCode = reader["postalCode"].ToString(); phone = reader["phone"].ToString(); //createDate = (DateTime)reader["createDate"]; createdBy = reader["createdBy"].ToString(); //lastUpdate = (DateTime)reader["lastUpdate"]; lastUpdateBy = reader["lastUpdateBy"].ToString(); } conn.Close(); if (addressId != existingAddressId) { throw new Exception("Address ID wasn't found"); } cityObject = new City(cityId, loggedInUser, conn); } }
//Class Methods /// <summary> /// Finds a city by the city ID, and fills an object with the information from the database /// </summary> /// <param name="CityId"></param> /// <param name="CreatedBy"></param> /// <param name="conn"></param> private void setDatabaseCityInfo(int CityId, string CreatedBy, MySqlConnection conn) { using (conn) { string myExecuteQuery = "SELECT * FROM city WHERE cityId = " + CityId.ToString(); MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myExecuteQuery); while (reader.Read()) { cityId = (int)reader["cityId"]; cityName = reader["city"].ToString(); countryId = (int)reader["countryId"]; createDate = (DateTime)reader["createDate"]; createdBy = reader["createdBy"].ToString(); lastUpdate = (DateTime)reader["lastUpdate"]; lastUpdateBy = reader["lastUpdateBy"].ToString(); } conn.Close(); if (cityId != CityId) { throw new Exception("City ID wasn't found"); } countryObject = new Country(countryId, conn); } }
// Class Methods /// <summary> /// Sets the Customer Object with Database info /// </summary> /// <param name="existingCustomerId"></param> /// <param name="conn"></param> private void setDatabaseCustomerInfo(int existingCustomerId, MySqlConnection conn) { using (conn) { string myExecuteQuery = "SELECT * FROM customer WHERE customerId = " + existingCustomerId.ToString(); MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myExecuteQuery); while (reader.Read()) { customerID = (int)reader["customerId"]; customerName = reader["customerName"].ToString(); addressID = (int)reader["addressId"]; bool activeCheck = (bool)reader["active"]; if (activeCheck == true) { active = 1; } else { active = 0; } createdBy = reader["createdBy"].ToString(); lastUpdateBy = reader["lastUpdateBy"].ToString(); } conn.Close(); if (customerID != existingCustomerId) { throw new Exception("Customer ID wasn't found"); } addressObject = new Address(addressID, conn); } }
/// <summary> /// Constructor to Add Customer to Database /// </summary> /// <param name="CustomerId"></param> /// <param name="UserId"></param> /// <param name="Title"></param> /// <param name="Description"></param> /// <param name="Location"></param> /// <param name="Contact"></param> /// <param name="Type"></param> /// <param name="Url"></param> /// <param name="StartTime"></param> /// <param name="EndTime"></param> /// <param name="CreatedBy"></param> /// <param name="UpdatedBy"></param> /// <param name="AppointmentType"></param> public Appointment(int CustomerId, int UserId, string Title, string Description, string Location, string Contact, string Url, DateTime StartTime, DateTime EndTime, string CreatedBy, string UpdatedBy, string AppointmentType) { using (var conn = new MySqlConnection(connecterString)) { string findHighestAppointmentId = "SELECT MAX(appointmentId) FROM appointment"; var reader = DataChanges.GetMySqlReader(conn, findHighestAppointmentId); while (reader.Read()) { try { highestAppointmentId = (int)reader["MAX(appointmentId)"]; } catch { highestAppointmentId = 0; } } conn.Close(); type = AppointmentType; highestAppointmentId++; appointmentId = highestAppointmentId; customerId = CustomerId; userId = UserId; title = Title; description = Description; location = Location; contact = Contact; url = Url; start = StartTime; end = EndTime; createdBy = CreatedBy; createDate = DateTime.Now; lastUpdate = DateTime.Now; lastUpdateBy = UpdatedBy; string addAppointmentQuery = "INSERT INTO appointment VALUES(" + appointmentId.ToString() + ", " + customerId.ToString() + ", '" + title + "', '" + description + "', '" + location + "', '" + contact + "', '" + url + "', '" + start.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + end.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '" + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "', '" + type + "', " + userId.ToString() + ")"; //DataChanges db = new DataChanges(); DataChanges.ExecuteMySqlCommand(addAppointmentQuery, conn); } }
//Class Methods /// <summary> /// Sets the Object's properties with the Database information based on Country ID with the given connection /// </summary> /// <param name="CountryId"></param> /// <param name="conn"></param> private void getDatabaseCountryInfo(int CountryId, MySqlConnection conn) { //Search Database to find country by ID, then populate the information with the returned information. using (conn) { string myExecuteQuery = "SELECT * FROM country WHERE countryId = " + CountryId.ToString(); MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myExecuteQuery); while (reader.Read()) { countryId = (int)reader["countryId"]; country = reader["country"].ToString(); createDate = (DateTime)reader["createDate"]; createdBy = reader["createdBy"].ToString(); lastUpdate = (DateTime)reader["lastUpdate"]; lastUpdatedBy = reader["lastUpdateBy"].ToString(); } } }
/// <summary> /// Constructor to create a new user /// </summary> /// <param name="Username"></param> /// <param name="Password"></param> /// <param name="userCreating"></param> /// <param name="Connection"></param> public User(string Username, string Password, string userCreating, MySqlConnection Connection) { using (Connection) { try { int numOfUsers = 0; string getAllUsersQuery = "SELECT * FROM user"; // Create a user based on the Username and password given //Get the number of users currently in the database, Add one, and save it as a variable to use as a new ID MySqlDataReader reader = DataChanges.GetMySqlReader(Connection, getAllUsersQuery); while (reader.Read()) { numOfUsers++; } Connection.Close(); numOfUsers++; DateTime today = DateTime.Now; //Fill User Object with userId = numOfUsers; userName = Username; password = Password; lastUpdate = today; lastUpdatedBy = userCreating; createdBy = userCreating; createDate = today; active = 1; //DataChanges db = new DataChanges(); //Create a Command to insert a new user with the id as the count + 1 of users, the username and password given, the user adding the user. string insertCommand = "INSERT INTO user VALUES (" + userId.ToString() + ", '" + userName + "', '" + password + "', " + active.ToString() + ", '" + createdBy + "', '" + createDate.ToString("yyyy-mm-dd") + "', '" + lastUpdate.ToString("yyyy-mm-dd") + "', '" + lastUpdatedBy + "')"; DataChanges.ExecuteMySqlCommand(insertCommand, Connection); //See how many users are in Database //reader = DataChanges.GetMySqlReader(conn, "SELECT * FROM user"); } catch { throw new Exception("Failed to Add User"); } } }
// Constructors /// <summary> /// City Constructor using City Name /// </summary> /// <param name="CityName"></param> /// <param name="CountryName"></param> /// <param name="CreatedBy"></param> /// <param name="conn"></param> public City(string CityName, string CountryName, string CreatedBy, MySqlConnection conn) { //Search Database for City that matches. //Check to see if Country with given name exists. If not, update the database to add the country using (conn) { countryObject = new Country(CountryName, CreatedBy, conn); int cityCount = 0; bool cityExists = false; int existingCityId = 0; bool databaseEmpty = true; int newMaxCityId = 0; //Get the max value of id instead of counting all of the accounts. string myQuery = "SELECT MAX(cityId) FROM city"; MySqlDataReader reader1 = DataChanges.GetMySqlReader(conn, myQuery); //Use this to see if the country exists, and to count the number of countries while (reader1.Read()) { newMaxCityId = (int)reader1["Max(cityId)"]; databaseEmpty = false; } conn.Close(); myQuery = "SELECT * FROM city"; MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery); //Use this to see if the city exists, and to count the number of cities while (reader.Read()) { cityCount++; string checkCity = reader["city"].ToString(); int checkCountry = (int)reader["countryId"]; if (checkCity == CityName) { if (countryObject.countryId == checkCountry) { cityExists = true; var cityId = reader["cityId"]; existingCityId = (int)cityId; } } } conn.Close(); if (cityExists == true) { setDatabaseCityInfo(existingCityId, CreatedBy, conn); } else { //Increment the number of countries by one to use as the id cityCount++; if (databaseEmpty == true) { cityId = 1; } else { newMaxCityId++; cityId = newMaxCityId; } //Set values of Class cityName = CityName; //Create a country object to check if a country is already created, and if it's not, create it, and use the id of the existing or new Country countryId = countryObject.countryId; createDate = DateTime.Now; createdBy = CreatedBy; lastUpdate = DateTime.Now; lastUpdateBy = CreatedBy; //add city to database if it doesn't exist //DataChanges db = new DataChanges(); myQuery = "INSERT INTO city VALUES(" + cityId.ToString() + ", '" + cityName + "', " + countryId.ToString() + ", '" + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '" + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "')"; DataChanges.ExecuteMySqlCommand(myQuery, conn); } } //If there isn't one that matches, add it with the information given }
//Constructors /// <summary> /// Constructor based on Country Name /// </summary> /// <param name="CountryName"></param> /// <param name="CreatedBy"></param> /// <param name="conn"></param> public Country(string CountryName, string CreatedBy, MySqlConnection conn) { //Check to see if Country with given name exists. If not, update the database to add the country using (conn) { int countryCount = 0; bool countryExists = false; int existingCountryId = 0; bool databaseEmpty = true; int newMaxCountryId = 0; //Get the max value of id instead of counting all of the accounts. string myQuery = "SELECT MAX(countryId) FROM country"; MySqlDataReader reader1 = DataChanges.GetMySqlReader(conn, myQuery); //Use this to see if the country exists, and to count the number of countries while (reader1.Read()) { newMaxCountryId = (int)reader1["Max(countryId)"]; databaseEmpty = false; } conn.Close(); myQuery = "SELECT * FROM country"; MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery); //Use this to see if the country exists, and to count the number of countries while (reader.Read()) { countryCount++; string checkCountry = reader["country"].ToString(); if (checkCountry == CountryName) { countryExists = true; var countryId = reader["countryId"]; existingCountryId = (int)countryId; } } conn.Close(); if (countryExists == true) { getDatabaseCountryInfo(existingCountryId, conn); } else { //Increment the number of countries by one to use as the id countryCount++; //Set values of Class if (databaseEmpty == true) { countryId = 1; } else { newMaxCountryId++; countryId = newMaxCountryId; } country = CountryName; createDate = DateTime.Now; createdBy = CreatedBy; lastUpdate = DateTime.Now; lastUpdatedBy = CreatedBy; //add country to database if it doesn't exist //DataChanges db = new DataChanges(); myQuery = "INSERT INTO country VALUES(" + countryId.ToString() + ", '" + country + "', '" + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '" + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdatedBy + "')"; DataChanges.ExecuteMySqlCommand(myQuery, conn); } } }
// Constructors /// <summary> /// Address Constructor Using Address Information. /// </summary> /// <param name="Address"></param> /// <param name="Address2"></param> /// <param name="City"></param> /// <param name="CountryName"></param> /// <param name="PostalCode"></param> /// <param name="Phone"></param> /// <param name="CreatedBy"></param> /// <param name="conn"></param> public Address(string Address, string Address2, string City, String CountryName, string PostalCode, string Phone, string CreatedBy, MySqlConnection conn) { //Check Database to see if the address matches exactly with any records. //If It does, populate information with information from Database //If it's not, use the given information to add a new record to the database //Get City ID by searching for matches for City //Use current date/time for creade date and last update //Throw error if Insert failed cityObject = new City(City, CountryName, CreatedBy, conn); int addressCount = 0; bool addressExists = false; int existingAddressId = 0; string myQuery = "SELECT * FROM address"; MySqlDataReader reader = DataChanges.GetMySqlReader(conn, myQuery); //Use this to see if the country exists, and to count the number of countries while (reader.Read()) { addressCount++; string checkAddress = reader["address"].ToString() + reader["address2"].ToString() + reader["cityId"].ToString(); string givenAddress = Address + Address2 + cityObject.cityId.ToString(); if (givenAddress == checkAddress) { //Set existingAddressId to be able to Call function that fills class from reader by id //after connection is closed addressExists = true; existingAddressId = (int)reader["addressId"]; } } conn.Close(); if (addressExists == true) { setDatabaseAddressInfo(existingAddressId, conn); } else { //Increment the number of addresses by one to use as the id addressCount++; //Set values of Class addressId = addressCount; address = Address; address2 = Address2; //Create a country object to check if a country is already created, and if it's not, create it, and use the id of the existing or new Country cityId = cityObject.cityId; postalCode = PostalCode; phone = Phone; createDate = DateTime.Now; createdBy = CreatedBy; lastUpdate = DateTime.Now; lastUpdateBy = CreatedBy; //add country to database if it doesn't exist //DataChanges db = new DataChanges(); myQuery = "INSERT INTO address VALUES(" + addressId.ToString() + ", '" + address + "', '" + address2 + "', " + cityId + ", '" + postalCode + "', '" + phone + "', '" + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '" + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "')"; DataChanges.ExecuteMySqlCommand(myQuery, conn); } }
//Constructors /// <summary> /// Constructor for Adding a new Customer, or loading customer object if matching customer exists /// </summary> /// <param name="Name"></param> /// <param name="Address1"></param> /// <param name="Address2"></param> /// <param name="City"></param> /// <param name="CountryName"></param> /// <param name="PostalCode"></param> /// <param name="Phone"></param> /// <param name="CreatedBy"></param> /// <param name="conn"></param> public Customer(string Name, string Address1, string Address2, string City, string CountryName, string PostalCode, string Phone, string CreatedBy, MySqlConnection conn) { //Check Database for number of customers, add one, and use as customer ID using (conn) { addressObject = new Address(Address1, Address2, City, CountryName, PostalCode, Phone, CreatedBy, conn); bool customerExists = false; int existingCustomerId = 0; bool databaseEmpty = true; int newMaxCityId = 0; //Get the max value of id instead of counting all of the accounts. string myQuery = "SELECT MAX(customerId) FROM customer"; MySqlDataReader reader1 = DataChanges.GetMySqlReader(conn, myQuery); //Use this to see if the country exists, and to count the number of countries while (reader1.Read()) { newMaxCityId = (int)reader1["Max(customerId)"]; databaseEmpty = false; } conn.Close(); //Check to see if there are any matches to the customer string mySqlQuery = "SELECT * FROM customer JOIN address ON customer.addressId = address.addressId"; MySqlDataReader reader = DataChanges.GetMySqlReader(conn, mySqlQuery); while (reader.Read()) { string TempName = reader["customerName"].ToString(); if (TempName == Name) { string tempAddress = reader["address"].ToString() + reader["address2"].ToString(); string tempPhone = reader["phone"].ToString(); if (tempAddress == Address1 + Address2 && tempPhone == Phone) { customerExists = true; existingCustomerId = (int)reader["customerId"]; } } } conn.Close(); if (customerExists == true) { setDatabaseCustomerInfo(existingCustomerId, conn); } else { newMaxCityId++; if (databaseEmpty == true) { customerID = 1; } else { customerID = newMaxCityId; } //Set name = the name given //use address constructor to set phone, and address along with postal Code, etc in an object //get address ID from address object to add address ID //Set active, //Use current date/time to set create and last update dates //Use the given logged in user name to set createdBy, and lastupdated by customerName = Name; addressID = addressObject.addressId; active = 1; createDate = DateTime.Now; CreatedBy = loggedInUser; lastUpdate = DateTime.Now; lastUpdateBy = loggedInUser; //add customer to database if it doesn't exist //DataChanges db = new DataChanges(); myQuery = "INSERT INTO customer VALUES(" + customerID.ToString() + ", '" + customerName + "', " + addressID.ToString() + ", " + active.ToString() + ", '" + createDate.ToString("yyyy-MM-dd") + "', '" + createdBy + "', '" + lastUpdate.ToString("yyyy-MM-dd") + "', '" + lastUpdateBy + "')"; DataChanges.ExecuteMySqlCommand(myQuery, conn); } } }