/// <summary>
/// Checks the existence of a database using the given <see paramref="storage"/> storage object.
/// </summary>
/// <param name="storage">The storage to use.</param>
/// <param name="databaseName">The name of the database existence of which to check.</param>
/// <returns><em>TRUE</em> if the given database exists. <em>FALSE</em> otherwise.</returns>
public static async Task <bool> ExistsDatabaseAsync(this IRelationalStorage storage, string databaseName)
{
var existsTemplate = RelationalConstants.GetConstant(storage.InvariantName, RelationalConstants.ExistsDatabaseKey);
var ret = await storage.ReadAsync(string.Format(existsTemplate, databaseName), command =>
{
var p = command.CreateParameter();
p.ParameterName = "databaseName";
p.Value = databaseName;
command.Parameters.Add(p);
}, (selector, resultSetCount) => { return(selector.GetBoolean(0)); }).ConfigureAwait(continueOnCapturedContext: false);
return(ret.First());
}
/// <summary>
/// Executes a multi-record insert query clause with <em>SELECT UNION ALL</em>.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="storage">The storage to use.</param>
/// <param name="tableName">The table name to against which to execute the query.</param>
/// <param name="parameters">The parameters to insert.</param>
/// <param name="useSqlParams"><em>TRUE</em> if the query should be in parameterized form. <em>FALSE</em> otherwise.</param>
/// <returns>The rows affected.</returns>
public static Task <int> ExecuteMultipleInsertIntoAsync <T>(this IRelationalStorage storage, string tableName, IEnumerable <T> parameters, bool useSqlParams = false)
{
if (string.IsNullOrWhiteSpace(tableName))
{
throw new ArgumentException("The name must be a legal SQL table name", "tableName");
}
if (parameters == null)
{
throw new ArgumentNullException("parameters");
}
//SqlParameters map is needed in case the query needs to be parameterized in order to avoid two
//reflection passes as first a query needs to be constructed and after that when a database
//command object has been created, parameters need to be provided to them.
var sqlParameters = new Dictionary <string, object>();
const string insertIntoValuesTemplate = "INSERT INTO {0} ({1}) SELECT {2};";
string columns = string.Empty;
var values = new List <string>();
var materializedParameters = parameters.ToList();
if (materializedParameters.Any())
{
//Type and property information are the same for all of the objects.
//The following assumes the property names will be retrieved in the same
//order as is the index iteration done.
var type = materializedParameters.First().GetType();
var properties = type.GetProperties();
var startEscapeIndicator = RelationalConstants.GetConstant(storage.InvariantName, RelationalConstants.StartEscapeIndicatorKey);
var endEscapeIndicator = RelationalConstants.GetConstant(storage.InvariantName, RelationalConstants.StartEscapeIndicatorKey);
columns = string.Join(",", properties.Select(f => string.Format("{0}{1}{2}", startEscapeIndicator, f.Name, endEscapeIndicator)));
int parameterCount = 0;
//This datarows will be used multiple times. It is all right as all the rows
//are of the same length, so all the values will always be replaced.
var dataRows = new string[properties.Length];
foreach (var row in materializedParameters)
{
for (int i = 0; i < properties.Length; ++i)
{
if (useSqlParams)
{
var parameterName = string.Format(string.Format(indexedParameterTemplate, parameterCount));
dataRows[i] = parameterName;
sqlParameters.Add(parameterName, properties[i].GetValue(row, null));
++parameterCount;
}
else
{
dataRows[i] = string.Format(sqlFormatProvider, "{0}", properties[i].GetValue(row, null));
}
}
values.Add(string.Format("{0}", string.Join(",", dataRows)));
}
}
//If this is an Oracle database, every UNION ALL SELECT needs to have "FROM DUAL" appended.
if (storage.InvariantName == WellKnownRelationalInvariants.OracleDatabase)
{
//Counting starts from 1 as the first SELECT should not select from dual.
for (int i = 1; i < values.Count; ++i)
{
values[i] = string.Concat(values[i], " FROM DUAL");
}
}
var query = string.Format(insertIntoValuesTemplate, tableName, columns, string.Join(" UNION ALL SELECT ", values));
return(storage.ExecuteAsync(query, command =>
{
if (useSqlParams)
{
foreach (var sqlParameter in sqlParameters)
{
var p = command.CreateParameter();
p.ParameterName = sqlParameter.Key;
p.Value = sqlParameter.Value ?? DBNull.Value;
p.Direction = ParameterDirection.Input;
command.Parameters.Add(p);
}
}
}));
}
/// <summary>
/// Deletes all data from Orleans database tables.
/// </summary>
/// <param name="storage">The storage to use.</param>
/// <returns>The call will be succesful if the DDL query is successful. Otherwise an exception will be thrown.</returns>
public static async Task DeleteAllDataAsync(this IRelationalStorage storage)
{
var deleteAllTemplate = RelationalConstants.GetConstant(storage.InvariantName, RelationalConstants.DeleteAllDataKey);
await storage.ExecuteAsync(deleteAllTemplate, command => { }).ConfigureAwait(continueOnCapturedContext: false);
}
/// <summary>
/// Drops a database with a given name.
/// </summary>
/// <param name="storage">The storage to use.</param>
/// <param name="databaseName">The name of the database to drop.</param>
/// <returns>The call will be succesful if the DDL query is successful. Otherwise an exception will be thrown.</returns>
public static async Task DropDatabaseAsync(this IRelationalStorage storage, string databaseName)
{
var dropTemplate = RelationalConstants.GetConstant(storage.InvariantName, RelationalConstants.DropDatabaseKey);
await storage.ExecuteAsync(string.Format(dropTemplate, databaseName), command => { }).ConfigureAwait(continueOnCapturedContext: false);
}