/// <summary> /// Query the database for a single column of data and return as a list 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 data as the specified return type.</returns> public static IEnumerable <T> GetDataList <T>(MySqlConnection EstablishedConnection, string QueryString, Dictionary <string, object> Parameters = null, bool ThrowException = true, MySqlTransaction SqlTransaction = null) //where T : DALBaseModel => ObjectResultsHelper.GetDataList <T>(EstablishedConnection, QueryString, Parameters: Parameters, ThrowException: ThrowException, SqlTransaction: 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 for a single column of data and return as a list 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 data as the specified return type.</returns> public static IEnumerable <T> GetDataList <T>(Enum ConfigConnectionString, string QueryString, Dictionary <string, object> Parameters = null, bool ThrowException = true, bool AllowUserVariables = false) //where T : DALBaseModel => ObjectResultsHelper.GetDataList <T>(ConfigConnectionString, QueryString, Parameters: Parameters, ThrowException: ThrowException, AllowUserVariables: AllowUserVariables);