private static async Task <bool> CreateMapTable() { MySqlConnection connection = new MySqlConnection(_connection); connection.Open(); // Construct the sql string based on the constants for table name, column names, and variable length values. string sqlString = @$ "CREATE TABLE IF NOT EXISTS `{_mapSchema}`.`{Constants.MapDAOTableName}` (" + $@"`{Constants.MapDAOHashColumn}` VARCHAR({Constants.DefaultHashCharacterLength}) NOT NULL," + $@"`{Constants.MapDAOActualColumn}` LONGTEXT NOT NULL," + $@"`{Constants.MapDAOoccurrencesColumn}` INT NOT NULL," + $@"PRIMARY KEY(`{Constants.MapDAOHashColumn}`));" + $@"INSERT INTO `{_mapSchema}`.`{Constants.MapDAOTableName}` (`{Constants.MapDAOHashColumn}`, `{Constants.MapDAOActualColumn}`, `{Constants.MapDAOoccurrencesColumn}`) VALUES('0', '1', '1');" + $@"INSERT INTO `{_mapSchema}`.`{Constants.MapDAOTableName}` (`{Constants.MapDAOHashColumn}`, `{Constants.MapDAOActualColumn}`, `{Constants.MapDAOoccurrencesColumn}`) VALUES('1', '0', '1');"; // Create the commmand object and execute/dispose it asynchronously. MySqlCommand command = new MySqlCommand(sqlString, connection); await command.ExecuteNonQueryAsync().ConfigureAwait(false); await command.DisposeAsync().ConfigureAwait(false); Environment.SetEnvironmentVariable("COUNT", (2).ToString(), EnvironmentVariableTarget.User); connection.Close(); return(true); }
/// <summary> /// Executes the CREATE TABLE statement for the store table in the mysql schema. /// </summary> /// <returns>Task (bool)</returns> private static async Task <bool> CreateStoreTable() { MySqlConnection connection = new MySqlConnection(_connection); connection.Open(); // Construct the sql string based on the constants for table name, column names, and variable length values. string sqlString = @$ "CREATE TABLE IF NOT EXISTS `{_exogredientSchema}`.`{Constants.StoreDAOTableName}` (" + $@"`{Constants.StoreDAOStoreIdColumn}` INT NOT NULL AUTO_INCREMENT," + $@"`{Constants.StoreDAOStoreNameColumn}` VARCHAR({Constants.StoreNameMaxCharacters}) NOT NULL," + $@"`{Constants.StoreDAOLatitudeColumn}` DOUBLE NOT NULL," + $@"`{Constants.StoreDAOLongitudeColumn}` DOUBLE NOT NULL," + $@"`{Constants.StoreDAOPlaceIdColumn}` VARCHAR({Constants.PlaceIDCharacters}) NOT NULL," + $@"`{Constants.StoreDAOStoreDescriptionColumn}` VARCHAR({Constants.StoreDescriptionMaxCharacters}) NOT NULL," + $@"PRIMARY KEY(`{Constants.StoreDAOStoreIdColumn}`)," + $@"UNIQUE INDEX geocode ({Constants.StoreDAOLatitudeColumn}, {Constants.StoreDAOLongitudeColumn}));"; // Create the commmand object and execute/dispose it asynchronously. MySqlCommand command = new MySqlCommand(sqlString, connection); await command.ExecuteNonQueryAsync().ConfigureAwait(false); await command.DisposeAsync().ConfigureAwait(false); connection.Close(); return(true); }
/// <summary> /// Creates the ticket tables, including its enumeration tables /// </summary> /// <returns>Whether the tables were created successfully</returns> private static async Task <bool> CreateTicketTables() { MySqlConnection connection = new MySqlConnection(_connection); connection.Open(); string insertCategoriesQuery = BuildMultiSingleValueInsertStatement(Constants.TicketCategoryDAOTableName, new Constants.TicketCategories()); string insertStatusesQuery = BuildMultiSingleValueInsertStatement(Constants.TicketStatusDAOTableName, new Constants.TicketStatuses()); string insertFlagColorsQuery = BuildMultiSingleValueInsertStatement(Constants.TicketFlagColorDAOTableName, new Constants.TicketFlagColors()); StringBuilder sqlString = new StringBuilder($@" -- Enumeration tables CREATE TABLE IF NOT EXISTS `{Constants.TicketCategoryDAOTableName}` ( `{Constants.TicketDAOCategoryColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL, PRIMARY KEY (`{Constants.TicketDAOCategoryColumn}`) ); CREATE TABLE IF NOT EXISTS `{Constants.TicketStatusDAOTableName}` ( `{Constants.TicketDAOStatusColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL, PRIMARY KEY (`{Constants.TicketDAOStatusColumn}`) ); CREATE TABLE IF NOT EXISTS `{Constants.TicketFlagColorDAOTableName}` ( `{Constants.TicketDAOFlagColorColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL, PRIMARY KEY (`{Constants.TicketDAOFlagColorColumn}`) ); CREATE TABLE IF NOT EXISTS `{Constants.TicketDAOTableName}` ( `{Constants.TicketDAOTicketIDColumn}` INT UNSIGNED NOT NULL AUTO_INCREMENT, `{Constants.TicketDAOSubmitTimestampColumn}` INT UNSIGNED NOT NULL, `{Constants.TicketDAOCategoryColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL, `{Constants.TicketDAOStatusColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL, `{Constants.TicketDAOFlagColorColumn}` VARCHAR({Constants.DefaultVarCharLength}) NOT NULL, `{Constants.TicketDAODescriptionColumn}` LONGTEXT NOT NULL, `{Constants.TicketDAOIsReadColumn}` BOOLEAN NOT NULL DEFAULT 0, PRIMARY KEY (`{Constants.TicketDAOTicketIDColumn}`), FOREIGN KEY (`{Constants.TicketDAOCategoryColumn}`) REFERENCES `{Constants.TicketCategoryDAOTableName}`(`{Constants.TicketDAOCategoryColumn}`), FOREIGN KEY (`{Constants.TicketDAOStatusColumn}`) REFERENCES `{Constants.TicketStatusDAOTableName}`(`{Constants.TicketDAOStatusColumn}`), FOREIGN KEY (`{Constants.TicketDAOFlagColorColumn}`) REFERENCES `{Constants.TicketFlagColorDAOTableName}`(`{Constants.TicketDAOFlagColorColumn}`) ); {insertCategoriesQuery} {insertStatusesQuery} {insertFlagColorsQuery} "); // Create the commmand object and execute/dispose it asynchronously. MySqlCommand command = new MySqlCommand(sqlString.ToString(), connection); await command.ExecuteNonQueryAsync().ConfigureAwait(false); await command.DisposeAsync().ConfigureAwait(false); connection.Close(); return(true); }
public City GetCity(string cityName, string cityZIP) { //Create a request to get the city using MySqlCommand rqst = DataBaseManager.Connection.CreateCommand(); //Fill the request rqst.CommandText = "SELECT ville_nom_reel, " + "ville_code_postal, " + "ville_longitude_deg, " + "ville_latitude_deg " + "FROM `villes_france_free` " + $"WHERE UPPER(ville_nom_simple) = UPPER('{ cityName.Replace('\'', '-') }')" + $"AND ville_code_postal = '{ cityZIP }'"; City city = null; //Running the request using (DbDataReader reader = rqst.ExecuteReader()) { if (reader.HasRows) { //Count the number of records get by the request //should be equal to 1 int rowCount = 1; while (reader.Read()) { //If rowCount >1 if (rowCount > 1) { //Free ressources rqst.DisposeAsync(); reader.DisposeAsync(); //Raise error throw new NotImplementedException("Too much records returned for the same City name and Zip code"); } int cityNameOrd = reader.GetOrdinal("ville_nom_simple"); int longitudeOrd = reader.GetOrdinal("ville_nom_simple"); int latitudeOrd = reader.GetOrdinal("ville_nom_simple"); int zipOrd = reader.GetOrdinal("ville_code_postal"); string name = reader.GetString(cityNameOrd); double longitude = reader.GetDouble(longitudeOrd); double latitude = reader.GetDouble(latitudeOrd); string cityZip = reader.GetString(zipOrd); city = new City(name, cityZip, longitude, latitude); rowCount++; } } } return(city); }
/// <summary> /// Executes the CREATE TABLE statement for the upload table in the mysql schema. /// </summary> /// <returns>Task (bool)</returns> private static async Task <bool> CreateUploadTable() { MySqlConnection connection = new MySqlConnection(_connection); connection.Open(); // Construct the sql string based on the constants for table name, column names, and variable length values. string sqlString = @$ "CREATE TABLE `{_exogredientSchema}`.`{Constants.UploadDAOTableName}` (" + $@"`{Constants.UploadDAOUploadIdColumn}` INT NOT NULL AUTO_INCREMENT," + $@"`{Constants.UploadDAOPostTimeDateColumn}` TIMESTAMP NOT NULL," + $@"`{Constants.UploadDAOUploaderColumn}` VARCHAR({Constants.MaximumUsernameCharacters}) NOT NULL," + $@"`{Constants.UploadDAOStoreIdColumn}` INT NOT NULL," + $@"`{Constants.UploadDAODescriptionColumn}` VARCHAR({Constants.MaximumUploadDescriptionCharacters}) NOT NULL," + $@"`{Constants.UploadDAORatingColumn}` VARCHAR({Constants.MaxRatingDigits}) NOT NULL," + $@"`{Constants.UploadDAOPhotoColumn}` VARCHAR({Constants.MaximumPhotoCharacters}) NOT NULL," + $@"`{Constants.UploadDAOPriceColumn}` DOUBLE({Constants.MaximumPriceDigits},{Constants.PriceAccuracyDigits}) NOT NULL," + $@"`{Constants.UploadDAOPriceUnitColumn}` VARCHAR({Constants.PriceUnitMaxCharacters}) NOT NULL," + $@"`{Constants.UploadDAOIngredientNameColumn}` VARCHAR({Constants.MaximumIngredientNameCharacters}) NOT NULL," + $@"`{Constants.UploadDAOUpvoteColumn}` INT NOT NULL," + $@"`{Constants.UploadDAODownvoteColumn}` INT NOT NULL," + $@"`{Constants.UploadDAOInProgressColumn}` TINYINT(1) NOT NULL," + $@"`{Constants.UploadDAOCategoryColumn}` VARCHAR({Constants.MaximumCategoryCharacters}) NOT NULL," + $@"PRIMARY KEY(`{Constants.UploadDAOUploadIdColumn}`)," + $@"INDEX `{Constants.UploadDAOStoreIdColumn}_idx` (`{Constants.UploadDAOStoreIdColumn}` ASC) INVISIBLE," + $@"CONSTRAINT `{Constants.UploadDAOUploaderColumn}`" + $@" FOREIGN KEY (`{Constants.UploadDAOUploaderColumn}`)" + $@" REFERENCES `{Constants.ExogredientSQLSchemaName}`.`{Constants.UserDAOtableName}` (`{Constants.UserDAOusernameColumn}`)" + $@" ON DELETE NO ACTION" + $@" ON UPDATE NO ACTION," + $@"CONSTRAINT `{Constants.UploadDAOStoreIdColumn}`" + $@" FOREIGN KEY (`{Constants.UploadDAOStoreIdColumn}`)" + $@" REFERENCES `{Constants.ExogredientSQLSchemaName}`.`{Constants.StoreDAOTableName}` (`{Constants.StoreDAOStoreIdColumn}`)" + $@" ON DELETE NO ACTION" + $@" ON UPDATE NO ACTION);"; // Create the commmand object and execute/dispose it asynchronously. MySqlCommand command = new MySqlCommand(sqlString, connection); await command.ExecuteNonQueryAsync().ConfigureAwait(false); await command.DisposeAsync().ConfigureAwait(false); return(true); }
/// <summary> /// Executes the CREATE TABLE statement for the anonymous_user table in the mysql schema. /// </summary> /// <returns>Task (bool)</returns> private static async Task <bool> CreateAnonymousUserTable() { MySqlConnection connection = new MySqlConnection(_connection); connection.Open(); // Construct the sql string based on the constants for table name, column names, and variable length values. string sqlString = @$ "CREATE TABLE `{_exogredientSchema}`.`{Constants.AnonymousUserDAOTableName}` (" + $@"`{Constants.AnonymousUserDAOIPColumn}` VARCHAR({(Constants.AnonymousUserDAOIsColumnMasked[Constants.AnonymousUserDAOIPColumn] ? Constants.DefaultHashCharacterLength : Constants.IPAddressLength)}) NOT NULL," + $@"`{Constants.AnonymousUserDAOtimestampLockedColumn}` BIGINT NOT NULL," + $@"`{Constants.AnonymousUserDAOregistrationFailuresColumn}` INT NOT NULL," + $@"`{Constants.AnonymousUserDAOlastRegFailTimestampColumn}` BIGINT NOT NULL," + $@"PRIMARY KEY(`{Constants.AnonymousUserDAOIPColumn}`));"; // Create the commmand object and execute/dispose it asynchronously. MySqlCommand command = new MySqlCommand(sqlString, connection); await command.ExecuteNonQueryAsync().ConfigureAwait(false); await command.DisposeAsync().ConfigureAwait(false); return(true); }
public async Task QueryAsync(string query, OrderedDictionary args = null) { using (MySqlConnection conn = new MySqlConnection(connectionString)) { try { await conn.OpenAsync(); using (var cmd = new MySqlCommand(query, conn)) { if (args != null) { IDictionaryEnumerator reader = args.GetEnumerator(); while (reader.MoveNext()) { cmd.Parameters.AddWithValue(reader.Key.ToString(), reader.Value); } } await cmd.ExecuteNonQueryAsync(); await cmd.DisposeAsync(); } } catch (Exception e) { Console.WriteLine(e); } finally { await conn.CloseAsync(); await conn.DisposeAsync(); } } }
/// <summary> /// Executes the CREATE TABLE statement for the user table in the mysql schema. /// </summary> /// <returns>Task (bool)</returns> private static async Task <bool> CreateUserTable() { MySqlConnection connection = new MySqlConnection(_connection); connection.Open(); // Construct the sql string based on the constants for table name, column names, and variable length values. string sqlString = @$ "CREATE TABLE IF NOT EXISTS `{_exogredientSchema}`.`{Constants.UserDAOtableName}` (" + $@"`{Constants.UserDAOusernameColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOusernameColumn] ? Constants.DefaultHashCharacterLength : Constants.MaximumUsernameCharacters)}) NOT NULL," + $@"`{Constants.UserDAOnameColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOnameColumn] ? Constants.DefaultHashCharacterLength : (Constants.MaximumFirstNameCharacters + Constants.MaximumLastNameCharacters + 1))}) NOT NULL," + $@"`{Constants.UserDAOemailColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOemailColumn] ? Constants.DefaultHashCharacterLength : Constants.MaximumEmailCharacters)}) NOT NULL," + $@"`{Constants.UserDAOphoneNumberColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOphoneNumberColumn] ? Constants.DefaultHashCharacterLength : Constants.PhoneNumberCharacterLength)}) NOT NULL," + $@"`{Constants.UserDAOpasswordColumn}` VARCHAR({Constants.DefaultHashCharacterLength}) NOT NULL," + $@"`{Constants.UserDAOdisabledColumn}` TINYINT(1) NOT NULL," + $@"`{Constants.UserDAOuserTypeColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOuserTypeColumn] ? Constants.DefaultHashCharacterLength : Constants.MaximumUserTypeLength)}) NOT NULL," + $@"`{Constants.UserDAOsaltColumn}` VARCHAR({Constants.DefaultSaltLength}) NOT NULL," + $@"`{Constants.UserDAOtempTimestampColumn}` BIGINT NOT NULL," + $@"`{Constants.UserDAOemailCodeColumn}` VARCHAR({(Constants.UserDAOIsColumnMasked[Constants.UserDAOemailCodeColumn] ? Constants.DefaultHashCharacterLength : Constants.EmailCodeLength)}) NOT NULL," + $@"`{Constants.UserDAOemailCodeTimestampColumn}` BIGINT NOT NULL," + $@"`{Constants.UserDAOloginFailuresColumn}` INT NOT NULL," + $@"`{Constants.UserDAOlastLoginFailTimestampColumn}` BIGINT NOT NULL," + $@"`{Constants.UserDAOemailCodeFailuresColumn}` INT NOT NULL," + $@"`{Constants.UserDAOphoneCodeFailuresColumn}` INT NOT NULL," + $@"PRIMARY KEY (`{Constants.UserDAOusernameColumn}`)," + $@"UNIQUE INDEX `{Constants.UserDAOemailColumn}_UNIQUE` (`{Constants.UserDAOemailColumn}` ASC) VISIBLE," + $@"UNIQUE INDEX `{Constants.UserDAOphoneNumberColumn}_UNIQUE` (`{Constants.UserDAOphoneNumberColumn}` ASC) VISIBLE);"; // Create the commmand object and execute/dispose it asynchronously. MySqlCommand command = new MySqlCommand(sqlString, connection); await command.ExecuteNonQueryAsync().ConfigureAwait(false); await command.DisposeAsync().ConfigureAwait(false); connection.Close(); return(true); }