/// <summary> /// Query the database and return the table as a list of objects of the supplied type. /// </summary> /// <typeparam name="T">The data return type.</typeparam> /// <param name="EstablishedConnection">An open and established connection to a MySQL database.</param> /// <param name="QueryString">The full SQL query string to be used to retrieve the value requested.</param> /// <param name="Parameters">Named parameters for the query.</param> /// <param name="ThrowException">Throw exception or cache in LastExecutionException and continue.</param> /// <param name="SqlTransaction">Supply an existing transaction for use in this operation.</param> /// <returns>A list of objects populated with the requested data.</returns> public static IEnumerable <T> GetDataObjects <T>(MySqlConnection EstablishedConnection, string QueryString, Dictionary <string, object> Parameters = null, bool ThrowException = true, MySqlTransaction SqlTransaction = null) where T : DALBaseModel => ObjectResultsHelper.GetDataObjects <T>(EstablishedConnection, QueryString, Parameters : Parameters, ThrowException : ThrowException, SqlTransaction);
/// <summary> /// Populates table columns and the insert query as needed. /// </summary> private void PopulateColumnDetails() { // if we have no table columns or insert query, build them automatically if ((TableColumns?.Count ?? 0) == 0 || string.IsNullOrWhiteSpace(InsertQuery)) { // we need a table name at minimum to autobuild the rest if (string.IsNullOrWhiteSpace(TableName)) { throw new ArgumentNullException("Error auto-populating Bulk Table Writer call: table name not defined"); } // pull the table details from the database var currentTableDetails = (ExistingConnection != null) ? ObjectResultsHelper.GetDataObjects <DALTableRowDescriptor>(ExistingConnection, $"DESCRIBE {TableName}") : ObjectResultsHelper.GetDataObjects <DALTableRowDescriptor>(ConfigConnectionString, $"DESCRIBE {TableName}"); // use all column for insert EXCEPT autonumber fields and the boilerplate create_date and last_updated columns var insertColumns = currentTableDetails .Where(x => !x.Extra.Contains("auto_increment") && !new string[] { "create_date", "last_updated" }.Contains(x.Field)); // don't update primary key or unique columns on duplicate key as it's unnecessary var updateColumns = insertColumns .Where(x => !x.Key.Contains("PRI") && !x.Key.Contains("UNI")); // if we don't have an insert query, make one if (string.IsNullOrWhiteSpace(InsertQuery)) { var newQuery = new StringBuilder(); newQuery.Append("INSERT INTO "); newQuery.Append(TableName); newQuery.Append(" (`"); newQuery.Append(string.Join("`,`", insertColumns.Select(x => x.Field))); newQuery.Append("`) VALUES ("); newQuery.Append(string.Join(",", insertColumns.Select(x => $"@{x.Field}"))); newQuery.Append(") "); newQuery.Append("ON DUPLICATE KEY UPDATE "); newQuery.Append(string.Join(",", insertColumns.Where(x => !x.Key.Contains("PRI") && !x.Key.Contains("UNI")).Select(x => $"`{x.Field}` = VALUES(`{x.Field}`)"))); // don't update primary key or unique columns on duplicate key as it's unnecessary newQuery.Append(";"); SetInsertQuery(newQuery.ToString()); } // if we don't have a table columns list, make it if ((TableColumns?.Count ?? 0) <= 0) { // use all of the insert columns because it's the larger set var columnDefinitions = insertColumns .Select((x) => { var fieldType = x.Type; var fieldSize = -1; // try to pull the type and size from the column name if (fieldType.Contains("(")) { var typeParts = fieldType.Split(new char[] { '(', ')' }, StringSplitOptions.RemoveEmptyEntries); fieldType = typeParts[0]; // if there's no size or the size is unparseable, just use -1 when inserting data fieldSize = int.TryParse(typeParts[1], out int sizeField) ? sizeField : -1; } var convertedType = new DALPropertyType(fieldType); // we don't already have this conversion defined, throw exception //if (!MySqlTypeConverter.ContainsKey(fieldType)) if (convertedType.PropertyType == null) { throw new KeyNotFoundException($"Error auto-populating columns for [{TableName}]: Invalid field type [{fieldType}]"); } //return new Tuple<string, MySqlDbType, int, string, string>(x.Field, MySqlTypeConverter[fieldType], fieldSize, null, x.Default); return(new Tuple <string, MySqlDbType, int, string, string>(x.Field, convertedType.PropertyMySqlDbType, fieldSize, null, x.Default)); }); // add all those columns to the output table AddColumns(columnDefinitions); } } }
/// <summary> /// Query the database and return the table as a list of objects of the supplied type. /// </summary> /// <typeparam name="T">The data return type.</typeparam> /// <param name="ConfigConnectionString">An enum type to reference a connection string defined in web.config.</param> /// <param name="QueryString">The full SQL query string to be used to retrieve the value requested.</param> /// <param name="Parameters">Named parameters for the query.</param> /// <param name="ThrowException">Throw exception or cache in LastExecutionException and continue.</param> /// <param name="AllowUserVariables">Will allow special user variables (variables start with "@") to be defined in the query that will be eventually executed.</param> /// <returns>A list of objects populated with the requested data.</returns> public static IEnumerable <T> GetDataObjects <T>(Enum ConfigConnectionString, string QueryString, Dictionary <string, object> Parameters = null, bool ThrowException = true, bool AllowUserVariables = false) where T : DALBaseModel => ObjectResultsHelper.GetDataObjects <T>(ConfigConnectionString, QueryString, Parameters : Parameters, ThrowException : ThrowException, AllowUserVariables : AllowUserVariables);