private IEnumerable<DataRow> LoadDataTable(string sql, Func<IEnumerable<DataRow>, IEnumerable<DataRow>> orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable<EntityStoreSchemaFilterEntry> filters, params string[] filterAliases)
{
using (EntityCommand command = CreateFilteredCommand(_connection, sql, null, queryTypes, new List<EntityStoreSchemaFilterEntry>(filters), filterAliases))
{
using (DbDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
object[] values = new object[table.Columns.Count];
while (reader.Read())
{
reader.GetValues(values);
table.Rows.Add(values);
}
return orderByFunc(table.AsEnumerable());
}
}
}
public EntityStoreSchemaQueryGenerator(
string baseQuery, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes,
IEnumerable <EntityStoreSchemaFilterEntry> filters, string[] filterAliases)
{
_baseQuery = baseQuery;
_orderByClause = orderByClause;
_filters = filters.Where(entry => (queryTypes & entry.Types) != 0).ToArray();
_filterAliases = filterAliases;
}
public EntityStoreSchemaQueryGenerator(
string baseQuery, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes,
IEnumerable<EntityStoreSchemaFilterEntry> filters, string[] filterAliases)
{
_baseQuery = baseQuery;
_orderByClause = orderByClause;
_filters = filters.Where(entry => (queryTypes & entry.Types) != 0).ToArray();
_filterAliases = filterAliases;
}
/// <summary>
/// Creates a EntityStoreSchemaFilterEntry
/// </summary>
/// <param name="catalog">The pattern to use to select the appropriate catalog or null to not limit by catalog.</param>
/// <param name="schema">The pattern to use to select the appropriate schema or null to not limit by schema.</param>
/// <param name="name">The pattern to use to select the appropriate name or null to not limit by name.</param>
/// <param name="types">The type of objects to apply this filter to.</param>
/// <param name="effect">The effect that this filter should have on the results.</param>
public EntityStoreSchemaFilterEntry(string catalog, string schema, string name, EntityStoreSchemaFilterObjectTypes types, EntityStoreSchemaFilterEffect effect)
{
if (types == EntityStoreSchemaFilterObjectTypes.None)
{
throw EDesignUtil.Argument("types");
}
_catalog = catalog;
_schema = schema;
_name = name;
_types = types;
_effect = effect;
}
/// <summary>
/// Creates a EntityStoreSchemaFilterEntry
/// </summary>
/// <param name="catalog">The pattern to use to select the appropriate catalog or null to not limit by catalog.</param>
/// <param name="schema">The pattern to use to select the appropriate schema or null to not limit by schema.</param>
/// <param name="name">The pattern to use to select the appropriate name or null to not limit by name.</param>
/// <param name="types">The type of objects to apply this filter to.</param>
/// <param name="effect">The effect that this filter should have on the results.</param>
public EntityStoreSchemaFilterEntry(string catalog, string schema, string name, EntityStoreSchemaFilterObjectTypes types, EntityStoreSchemaFilterEffect effect)
{
if (types == EntityStoreSchemaFilterObjectTypes.None)
{
throw EDesignUtil.Argument("types");
}
_catalog = catalog;
_schema = schema;
_name = name;
_types = types;
_effect = effect;
}
/// <summary>
/// Creates a EntityStoreSchemaFilterEntry
/// </summary>
/// <param name="catalog">The pattern to use to select the appropriate catalog or null to not limit by catalog.</param>
/// <param name="schema">The pattern to use to select the appropriate schema or null to not limit by schema.</param>
/// <param name="name">The pattern to use to select the appropriate name or null to not limit by name.</param>
/// <param name="types">The type of objects to apply this filter to.</param>
/// <param name="effect">The effect that this filter should have on the results.</param>
public EntityStoreSchemaFilterEntry(
string catalog, string schema, string name, EntityStoreSchemaFilterObjectTypes types, EntityStoreSchemaFilterEffect effect)
{
if (types == EntityStoreSchemaFilterObjectTypes.None)
{
throw new ArgumentException(
string.Format(
CultureInfo.CurrentCulture,
Resources_VersioningFacade.InvalidStringArgument,
"types"));
}
_catalog = catalog;
_schema = schema;
_name = name;
_types = types;
_effect = effect;
}
internal virtual EntityCommand CreateFilteredCommand(
string sql, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes, List <EntityStoreSchemaFilterEntry> filters,
string[] filterAliases)
{
var command =
new EntityCommand(null, _connection, DependencyResolver.Instance)
{
CommandType = CommandType.Text,
CommandTimeout = 0
};
command.CommandText =
new EntityStoreSchemaQueryGenerator(sql, orderByClause, queryTypes, filters, filterAliases)
.GenerateQuery(command.Parameters);
return(command);
}
/// <summary>
/// Creates a EntityStoreSchemaFilterEntry
/// </summary>
/// <param name="catalog">The pattern to use to select the appropriate catalog or null to not limit by catalog.</param>
/// <param name="schema">The pattern to use to select the appropriate schema or null to not limit by schema.</param>
/// <param name="name">The pattern to use to select the appropriate name or null to not limit by name.</param>
/// <param name="types">The type of objects to apply this filter to.</param>
/// <param name="effect">The effect that this filter should have on the results.</param>
public EntityStoreSchemaFilterEntry(
string catalog, string schema, string name, EntityStoreSchemaFilterObjectTypes types, EntityStoreSchemaFilterEffect effect)
{
if (types == EntityStoreSchemaFilterObjectTypes.None)
{
throw new ArgumentException(
string.Format(
CultureInfo.CurrentCulture,
Resources_VersioningFacade.InvalidStringArgument,
"types"));
}
_catalog = catalog;
_schema = schema;
_name = name;
_types = types;
_effect = effect;
}
internal virtual EntityCommand CreateFilteredCommand(
string sql, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes, List <EntityStoreSchemaFilterEntry> filters,
string[] filterAliases)
{
var command =
new EntityCommand(null, _connection, DependencyResolver.Instance)
{
CommandType = CommandType.Text,
CommandTimeout = 0
};
var optimizeParameters =
((StoreItemCollection)_connection
.GetMetadataWorkspace()
.GetItemCollection(DataSpace.SSpace))
.ProviderManifest
.SupportsParameterOptimizationInSchemaQueries();
command.CommandText =
new EntityStoreSchemaQueryGenerator(sql, orderByClause, queryTypes, filters, filterAliases)
.GenerateQuery(new ParameterCollectionBuilder(command.Parameters, optimizeParameters));
return(command);
}
private IEnumerable <T> LoadDataTable <T>(
string sql, Func <IEnumerable <T>, IEnumerable <T> > orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes,
IEnumerable <EntityStoreSchemaFilterEntry> filters, params string[] filterAliases)
where T : DataRow
{
try
{
if (_addOptionMergeJoinInterceptor != null)
{
DbInterception.Add(_addOptionMergeJoinInterceptor);
}
using (var command = CreateFilteredCommand(sql, null, queryTypes, filters.ToList(), filterAliases))
{
using (var reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
var values = new object[table.Columns.Count];
while (reader.Read())
{
reader.GetValues(values);
table.Rows.Add(values);
}
return(orderByFunc(((IEnumerable <T>)table)));
}
}
}
finally
{
if (_addOptionMergeJoinInterceptor != null)
{
DbInterception.Remove(_addOptionMergeJoinInterceptor);
}
}
}
internal static EntityCommand CreateFilteredCommand(EntityConnection connection, string sql, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes, List<EntityStoreSchemaFilterEntry> filters, string[] filterAliases)
{
EntityCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
if (filters.Count == 0)
{
if (!string.IsNullOrEmpty(orderByClause))
{
command.CommandText = sql + Environment.NewLine + orderByClause;
}
else
{
command.CommandText = sql;
}
return command;
}
StringBuilder whereClause = new StringBuilder();
foreach (string alias in filterAliases)
{
StringBuilder allows = new StringBuilder();
StringBuilder excludes = new StringBuilder();
foreach (EntityStoreSchemaFilterEntry entry in filters)
{
// only apply filters of the correct type
if ((queryTypes & entry.Types) == 0)
{
continue;
}
if (entry.Effect == EntityStoreSchemaFilterEffect.Allow)
{
AddFilterEntry(command, allows, alias, entry);
}
else
{
Debug.Assert(entry.Effect == EntityStoreSchemaFilterEffect.Exclude, "did you add new value?");
AddFilterEntry(command, excludes, alias, entry);
}
}
if (allows.Length != 0)
{
if (whereClause.Length != 0)
{
whereClause.Append(Environment.NewLine);
whereClause.Append("AND");
whereClause.Append(Environment.NewLine);
}
whereClause.Append("(");
whereClause.Append(allows);
whereClause.Append(")");
}
if (excludes.Length != 0)
{
if (whereClause.Length != 0)
{
whereClause.Append(Environment.NewLine);
whereClause.Append("AND");
whereClause.Append(Environment.NewLine);
}
whereClause.Append("NOT (");
whereClause.Append(excludes);
whereClause.Append(")");
}
}
// did we end up with a where clause?
StringBuilder sqlStatement = new StringBuilder(sql);
if (whereClause.Length != 0)
{
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append("WHERE");
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append(whereClause);
}
if (!string.IsNullOrEmpty(orderByClause))
{
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append(orderByClause);
}
command.CommandText = sqlStatement.ToString();
return command;
}
internal static EntityCommand CreateFilteredCommand(EntityConnection connection, string sql, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes, List <EntityStoreSchemaFilterEntry> filters, string[] filterAliases)
{
EntityCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandTimeout = 0;
if (filters.Count == 0)
{
if (!string.IsNullOrEmpty(orderByClause))
{
command.CommandText = sql + Environment.NewLine + orderByClause;
}
else
{
command.CommandText = sql;
}
return(command);
}
StringBuilder whereClause = new StringBuilder();
foreach (string alias in filterAliases)
{
StringBuilder allows = new StringBuilder();
StringBuilder excludes = new StringBuilder();
foreach (EntityStoreSchemaFilterEntry entry in filters)
{
// only apply filters of the correct type
if ((queryTypes & entry.Types) == 0)
{
continue;
}
if (entry.Effect == EntityStoreSchemaFilterEffect.Allow)
{
AddFilterEntry(command, allows, alias, entry);
}
else
{
Debug.Assert(entry.Effect == EntityStoreSchemaFilterEffect.Exclude, "did you add new value?");
AddFilterEntry(command, excludes, alias, entry);
}
}
if (allows.Length != 0)
{
if (whereClause.Length != 0)
{
whereClause.Append(Environment.NewLine);
whereClause.Append("AND");
whereClause.Append(Environment.NewLine);
}
whereClause.Append("(");
whereClause.Append(allows);
whereClause.Append(")");
}
if (excludes.Length != 0)
{
if (whereClause.Length != 0)
{
whereClause.Append(Environment.NewLine);
whereClause.Append("AND");
whereClause.Append(Environment.NewLine);
}
whereClause.Append("NOT (");
whereClause.Append(excludes);
whereClause.Append(")");
}
}
// did we end up with a where clause?
StringBuilder sqlStatement = new StringBuilder(sql);
if (whereClause.Length != 0)
{
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append("WHERE");
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append(whereClause);
}
if (!string.IsNullOrEmpty(orderByClause))
{
sqlStatement.Append(Environment.NewLine);
sqlStatement.Append(orderByClause);
}
command.CommandText = sqlStatement.ToString();
return(command);
}
private IEnumerable <DataRow> LoadDataTable(string sql, Func <IEnumerable <DataRow>, IEnumerable <DataRow> > orderByFunc, DataTable table, EntityStoreSchemaFilterObjectTypes queryTypes, IEnumerable <EntityStoreSchemaFilterEntry> filters, params string[] filterAliases)
{
using (EntityCommand command = CreateFilteredCommand(_connection, sql, null, queryTypes, new List <EntityStoreSchemaFilterEntry>(filters), filterAliases))
{
using (DbDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess))
{
object[] values = new object[table.Columns.Count];
while (reader.Read())
{
reader.GetValues(values);
table.Rows.Add(values);
}
return(orderByFunc(table.AsEnumerable()));
}
}
}
private static ICollection <EntityStoreSchemaFilterEntry> ExecuteDatabaseMetadataQuery(
string esqlQuery, EntityStoreSchemaFilterObjectTypes types, ModelBuilderSettings settings, DoWorkEventArgs args)
{
var filterEntries = new List <EntityStoreSchemaFilterEntry>();
EntityConnection ec = null;
try
{
Version actualEntityFrameworkConnectionVersion;
ec = new StoreSchemaConnectionFactory().Create(
DependencyResolver.Instance,
settings.RuntimeProviderInvariantName,
settings.DesignTimeConnectionString,
settings.TargetSchemaVersion,
out actualEntityFrameworkConnectionVersion);
// if the provider does not support V3 and we are querying for Functions then switch to the pre-V3 query
if (actualEntityFrameworkConnectionVersion < EntityFrameworkVersion.Version3 &&
SelectFunctionsESqlQuery.Equals(esqlQuery, StringComparison.Ordinal))
{
esqlQuery = SelectFunctionsESqlQueryBeforeV3;
}
using (var command = new EntityCommand(null, ec, DependencyResolver.Instance))
{
// NOTE: DO NOT set the command.CommandTimeout value. Some providers don't support a non-zero value, and will throw (eg, SqlCE provider).
// The System.Data.SqlClient's default value is 15, so we will still get a timeout for sql server.
command.CommandType = CommandType.Text;
command.CommandText = esqlQuery;
ec.Open();
DbDataReader reader = null;
try
{
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
if (args != null &&
args.Cancel)
{
break;
}
if (reader.FieldCount == 3)
{
// the types coming back through the reader may not be a string
// (eg, SqlCE returns System.DbNull for catalogName & schemaName), so cast carefully
var catalogName = reader.GetValue(0) as String;
var schemaName = reader.GetValue(1) as String;
var name = reader.GetValue(2) as String;
if (String.IsNullOrEmpty(name) == false)
{
filterEntries.Add(
new EntityStoreSchemaFilterEntry(
catalogName, schemaName, name, types, EntityStoreSchemaFilterEffect.Allow));
}
}
else
{
Debug.Fail("Unexpected field count in reader");
}
}
}
finally
{
if (reader != null)
{
try
{
reader.Close();
reader.Dispose();
}
catch (Exception)
{
Debug.Fail(
"Could not close the DbDataReader in ExecuteDatabaseMetadataQuery(). If this is the result of a connection to a database file, it will leave a read lock on the file.");
}
}
}
}
}
finally
{
if (ec != null)
{
try
{
ec.Close();
ec.Dispose();
}
catch (Exception)
{
Debug.Fail(
"Could not close the EntityConnection in ExecuteDatabaseMetadataQuery(). If this is a connection to a database file, it will leave a read lock on the file.");
}
}
}
return(filterEntries);
}
private static ICollection<EntityStoreSchemaFilterEntry> ExecuteDatabaseMetadataQuery(
string esqlQuery, EntityStoreSchemaFilterObjectTypes types, ModelBuilderSettings settings, DoWorkEventArgs args)
{
var filterEntries = new List<EntityStoreSchemaFilterEntry>();
EntityConnection ec = null;
try
{
Version actualEntityFrameworkConnectionVersion;
ec = new StoreSchemaConnectionFactory().Create(
DependencyResolver.Instance,
settings.RuntimeProviderInvariantName,
settings.DesignTimeConnectionString,
settings.TargetSchemaVersion,
out actualEntityFrameworkConnectionVersion);
// if the provider does not support V3 and we are querying for Functions then switch to the pre-V3 query
if (actualEntityFrameworkConnectionVersion < EntityFrameworkVersion.Version3
&& SelectFunctionsESqlQuery.Equals(esqlQuery, StringComparison.Ordinal))
{
esqlQuery = SelectFunctionsESqlQueryBeforeV3;
}
using (var command = new EntityCommand(null, ec, DependencyResolver.Instance))
{
// NOTE: DO NOT set the the command.CommandTimeout value. Some providers don't support a non-zero value, and will throw (eg, SqlCE provider).
// The System.Data.SqlClient's default value is 15, so we will still get a timeout for sql server.
command.CommandType = CommandType.Text;
command.CommandText = esqlQuery;
ec.Open();
DbDataReader reader = null;
try
{
reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
if (args != null
&& args.Cancel)
{
break;
}
if (reader.FieldCount == 3)
{
// the types coming back through the reader may not be a string
// (eg, SqlCE returns System.DbNull for catalogName & schemaName), so cast carefully
var catalogName = reader.GetValue(0) as String;
var schemaName = reader.GetValue(1) as String;
var name = reader.GetValue(2) as String;
if (String.IsNullOrEmpty(name) == false)
{
filterEntries.Add(
new EntityStoreSchemaFilterEntry(
catalogName, schemaName, name, types, EntityStoreSchemaFilterEffect.Allow));
}
}
else
{
Debug.Fail("Unexpected field count in reader");
}
}
}
finally
{
if (reader != null)
{
try
{
reader.Close();
reader.Dispose();
}
catch (Exception)
{
Debug.Fail(
"Could not close the DbDataReader in ExecuteDatabaseMetadataQuery(). If this is the result of a connection to a database file, it will leave a read lock on the file.");
}
}
}
}
}
finally
{
if (ec != null)
{
try
{
ec.Close();
ec.Dispose();
}
catch (Exception)
{
Debug.Fail(
"Could not close the EntityConnection in ExecuteDatabaseMetadataQuery(). If this is a connection to a database file, it will leave a read lock on the file.");
}
}
}
return filterEntries;
}
internal override EntityCommand CreateFilteredCommand(
string sql, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes, List <EntityStoreSchemaFilterEntry> filters,
string[] filterAliases)
{
return(_entityCommands[_timesCalled++]);
}
internal override EntityCommand CreateFilteredCommand(
string sql, string orderByClause, EntityStoreSchemaFilterObjectTypes queryTypes, List<EntityStoreSchemaFilterEntry> filters,
string[] filterAliases)
{
return _entityCommands[_timesCalled++];
}
