/// <summary>
/// Creates an UPDATE command from the provided connection using DbParameters.
/// </summary>
/// <param name="command"></param>
public DbCommand GenerateCommand(DbConnection connection)
{
if (_newColumnValues.Count == 0)
{
throw new Exception("Can't update table without columns to be updated.");
}
var command = connection.CreateCommand();
StringBuilder sb = new StringBuilder($"UPDATE {SqlTableHelper.GetTableName<T>()} SET ");
for (int i = 0; i < _newColumnValues.Count; i++)
{
var colVal = _newColumnValues[i];
var param = command.CreateParameter();
var paramName = Util.GetUniqueParameterName();
param.ParameterName = paramName;
param.Value = colVal.Value;
param.DbType = colVal.ValueType;
command.Parameters.Add(param);
sb.Append($"{Util.FormatSQL(colVal.Column)}={ProviderSpecific.ParameterPrefix}{paramName}");
if (i < _newColumnValues.Count - 1)
{
sb.Append(", ");
}
}
command.CommandText += sb.ToString();
_condition?.GenerateCommand(command);
return(command);
}
/// <summary>
/// Creates a DELETE command from the provided connection using DbParameters.
/// </summary>
/// <param name="command"></param>
public DbCommand GenerateCommand(DbConnection connection)
{
if (string.IsNullOrEmpty(_table))
{
throw new Exception("Table is not set.");
}
var command = connection.CreateCommand();
command.CommandText += $"DELETE FROM {Util.FormatSQL(_table)} ";
if (_condition != null)
{
_condition.GenerateCommand(command);
command.CommandText += " ";
}
return(command);
}
/*
* /// <summary>
* /// Returns a reader for the specified amount of rows for the specified entity type.
* /// </summary>
* /// <typeparam name="T">The type of entity to be queried.</typeparam>
* /// <param name="condition">An optional condition to allow querying for specific rows. Leave at null to query all rows.</param>
* /// <param name="amountOfRows">The amount of rows to be returned (maximum). Leave at -1 to query all rows.</param>
* /// <returns>A DbDataReader that has tried to query rows for entities of type T.</returns>
* internal DbDataReader GetReader<T>(BuiltSqlCondition condition = null, int amountOfRows = -1)
* {
* return GetReader(typeof(T), condition, amountOfRows);
* }
*
* /// <summary>
* /// Returns a reader for the specified amount of rows for the specified entity type.
* /// </summary>
* /// <param name="entityType">The type of entity to be queried.</param>
* /// <param name="condition">An optional condition to allow querying for specific rows. Leave at null to query all rows.</param>
* /// <param name="amountOfRows">The amount of rows to be returned (maximum). Leave at -1 to query all rows.</param>
* /// <returns>A DbDataReader that has tried to query rows for entities of type T.</returns>
* internal DbDataReader GetReader(Type entityType, BuiltSqlCondition condition = null, int amountOfRows = -1)
* {
* var select = SqlBuild.Select(entityType);
*
* if (condition != null)
* select.Condition = condition;
*
* if (amountOfRows > 0)
* select.Limit(amountOfRows);
*
* return select.GenerateCommand(_connection).ExecuteReader();
* }
*
* /// <summary>
* /// Returns a reader for the specified amount of rows for the specified entity type.
* /// </summary>
* /// <param name="entityType">The type of entity to be queried.</param>
* /// <param name="commandString">The command to execute to create the reader.</param>
* /// <returns>A DbDataReader that has tried to query rows for entities of type entityType.</returns>
* internal DbDataReader GetReader(Type entityType, string commandString)
* {
* using (var command = _connection.CreateCommand())
* {
* command.CommandText = commandString;
* return command.ExecuteReader();
* }
* }
*/
/// <summary>
/// Fetches and initializes all available rows for entities of type T.
/// </summary>
/// <typeparam name="T">The type of entity to be queried.</typeparam>
/// <param name="condition">An optional condition to allow querying for specific rows. Leave at null to query all rows.</param>
/// <returns>An enumerable of constructed entities of type T.</returns>
public IEnumerable <T> All <T>(BuiltSqlCondition condition = null) where T : new()
{
var command = _connection.CreateCommand();
command.CommandText = SqlBuild.Select <T>().GenerateStatement();
condition?.GenerateCommand(command);
var reader = command.ExecuteReader();
List <T> resultSet = new List <T>();
while (reader.Read())
{
resultSet.Add(BuildSingle <T>(reader));
}
command.Dispose();
return(resultSet);
}