private void InitializeSqlContext()
{
try
{
queryBuilder.Clear();
BaseMetadataProvider metadataProvider = null;
if (_selectedConnection == null)
{
return;
}
// create new SqlConnection object using the connections string from the connection form
if (!_selectedConnection.IsXmlFile)
{
metadataProvider = _selectedConnection.ConnectionDescriptor?.MetadataProvider;
}
// setup the query builder with metadata and syntax providers
queryBuilder.SQLContext.MetadataContainer.Clear();
queryBuilder.MetadataProvider = metadataProvider;
queryBuilder.SyntaxProvider = _selectedConnection.ConnectionDescriptor?.SyntaxProvider;
queryBuilder.MetadataLoadingOptions.OfflineMode = metadataProvider == null;
if (metadataProvider == null)
{
queryBuilder.MetadataContainer.ImportFromXML(_selectedConnection.ConnectionString);
}
// Instruct the query builder to fill the database schema tree
queryBuilder.InitializeDatabaseSchemaTree();
}
catch { }
}
private BaseMetadataProvider GetMetadataProvider()
{
BaseMetadataProvider metaprov = null;
switch (this.DatabasePlatform)
{
case QueryBuilderDatabasePlatform.Informix:
queryBuilder.MetadataLoadingOptions.LoadSystemObjects = true;
metaprov = new InformixMetadataProvider();
metaprov.Connection = new IfxConnection(this.ConnectionString);
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
//universal provider no better than informix metadata provider: still no objects showing in right side objects pane
//metaprov = new UniversalMetadataProvider();
//IfxConnection ifxConn = new IfxConnection(this.ConnectionString);
//metaprov.Connection = (IDbConnection)ifxConn;
break;
default:
metaprov = new UniversalMetadataProvider();
metaprov.Connection = new OdbcConnection(this.ConnectionString);
break;
}
return(metaprov);
}
private void eventMetadataProvider1_ExecSQL(BaseMetadataProvider metadataProvider, string sql, bool schemaOnly, out IDataReader dataReader)
{
dataReader = null;
SqlConnection cnn = new SqlConnection();
//MySqlConnection myCnn = new MySqlConnection();
if (clsGbl.PMMSType.ToLower() == serverType)
{
cnn = new SqlConnection(Entity.getconnString(clsGbl.xmlPath));
}
else if (clsGbl.PMMSType.ToLower() == "mysql")
{
//myCnn = (MySqlConnection)Entity.getdbConn((SqlConnection)Entity.GetConnection(Entity.getconnString(clsGbl.xmlPath), serverType), "iqtools");
}
try
{
if (cnn != null && clsGbl.PMMSType.ToLower() == serverType)
{
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
IDbCommand command = cnn.CreateCommand();
command.CommandText = sql;
dataReader = command.ExecuteReader();
}
}
catch { }
}
private BaseMetadataProvider GetMetadataProvider()
{
BaseMetadataProvider metaprov = null;
switch (this.DatabasePlatform)
{
case QueryBuilderDatabasePlatform.Sybase:
metaprov = new UniversalMetadataProvider();
AseConnection aseConn = new AseConnection(this.ConnectionString);
metaprov.Connection = (IDbConnection)aseConn;
//workaround for problem with using Sybase provider
//NOTE: has been fixed as of 11/26/2013 release: see above code (12/2/13 mc)
//metaprov = new ODBCMetadataProvider();
//string odbcConnectionString = ConvertSybaseConnectionStringToOdbc(this.ConnectionString);
//OdbcConnection aseConn = new OdbcConnection(odbcConnectionString);
//metaprov.Connection = (IDbConnection)aseConn;
break;
default:
metaprov = new UniversalMetadataProvider();
metaprov.Connection = new OdbcConnection(this.ConnectionString);
break;
}
return(metaprov);
}
private void way3EventMetadataProvider_ExecSQL(BaseMetadataProvider metadataProvider, string sql, bool schemaOnly, out IDataReader dataReader)
{
dataReader = null;
if (_dbConnection != null)
{
IDbCommand command = _dbConnection.CreateCommand();
command.CommandText = sql;
dataReader = command.ExecuteReader();
}
}
/// <summary>
/// Notifies the kernal that a provider has completed refreshing
/// </summary>
/// <param name="provider">The provider.</param>
/// <param name="item">The item.</param>
public void OnProviderRefreshCompleted(BaseMetadataProvider provider, BaseItem item)
{
var key = item.Id + provider.GetType().Name;
Tuple <BaseMetadataProvider, BaseItem, CancellationTokenSource> current;
if (_currentlyRunningProviders.TryRemove(key, out current))
{
current.Item3.Dispose();
}
}
/// <summary>
/// Fetches metadata and returns true or false indicating if any work that requires persistence was done
/// </summary>
/// <param name="provider">The provider.</param>
/// <param name="item">The item.</param>
/// <param name="force">if set to <c>true</c> [force].</param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns>Task{System.Boolean}.</returns>
/// <exception cref="System.ArgumentNullException"></exception>
private async Task <ItemUpdateType?> FetchAsync(BaseMetadataProvider provider, BaseItem item, bool force, CancellationToken cancellationToken)
{
if (item == null)
{
throw new ArgumentNullException();
}
cancellationToken.ThrowIfCancellationRequested();
// Don't clog up the log with these providers
if (!(provider is IDynamicInfoProvider))
{
_logger.Debug("Running {0} for {1}", provider.GetType().Name, item.Path ?? item.Name ?? "--Unknown--");
}
// This provides the ability to cancel just this one provider
var innerCancellationTokenSource = new CancellationTokenSource();
try
{
var changed = await provider.FetchAsync(item, force, CancellationTokenSource.CreateLinkedTokenSource(cancellationToken, innerCancellationTokenSource.Token).Token).ConfigureAwait(false);
if (changed)
{
return(provider.ItemUpdateType);
}
return(null);
}
catch (OperationCanceledException ex)
{
_logger.Debug("{0} canceled for {1}", provider.GetType().Name, item.Name);
// If the outer cancellation token is the one that caused the cancellation, throw it
if (cancellationToken.IsCancellationRequested && ex.CancellationToken == cancellationToken)
{
throw;
}
return(null);
}
catch (Exception ex)
{
_logger.ErrorException("{0} failed refreshing {1} {2}", ex, provider.GetType().Name, item.Name, item.Path ?? string.Empty);
provider.SetLastRefreshed(item, DateTime.UtcNow, ProviderRefreshStatus.Failure);
return(ItemUpdateType.Unspecified);
}
finally
{
innerCancellationTokenSource.Dispose();
}
}
/// <summary>
/// Providers the supports item.
/// </summary>
/// <param name="provider">The provider.</param>
/// <param name="item">The item.</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise</returns>
private bool ProviderSupportsItem(BaseMetadataProvider provider, BaseItem item)
{
try
{
return(provider.Supports(item));
}
catch (Exception ex)
{
_logger.ErrorException("{0} failed in Supports for type {1}", ex, provider.GetType().Name, item.GetType().Name);
return(false);
}
}
private BaseMetadataProvider GetMetadataProvider()
{
BaseMetadataProvider metaprov = null;
switch (this.DatabasePlatform)
{
case QueryBuilderDatabasePlatform.MSSQLServer:
metaprov = new MSSQLMetadataProvider();
metaprov.Connection = new SqlConnection(this.ConnectionString);
break;
case QueryBuilderDatabasePlatform.SQLServerCE:
//metaprov = new MSSQLCEMetadataProvider(); //does not work
//metaprov = new UniversalMetadataProvider(); //does not work
//metaprov.Connection = new SqlCeConnection(this.ConnectionString);
string oledbConnectionString = ConvertSQLCE35ConnectionStringToOLEDB(this.ConnectionString);
metaprov = new OLEDBMetadataProvider();
metaprov.Connection = new OleDbConnection(oledbConnectionString);
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
break;
case QueryBuilderDatabasePlatform.OLEDB:
metaprov = new OLEDBMetadataProvider();
metaprov.Connection = new OleDbConnection(this.ConnectionString);
break;
case QueryBuilderDatabasePlatform.ODBC:
metaprov = new ODBCMetadataProvider();
metaprov.Connection = new OdbcConnection(this.ConnectionString);
break;
case QueryBuilderDatabasePlatform.MSAccess:
metaprov = new OLEDBMetadataProvider();
metaprov.Connection = new OleDbConnection(this.ConnectionString);
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
break;
case QueryBuilderDatabasePlatform.Oracle:
metaprov = new OracleMetadataProvider();
metaprov.Connection = new System.Data.OracleClient.OracleConnection(this.ConnectionString);
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
break;
default:
metaprov = new UniversalMetadataProvider();
metaprov.Connection = new OdbcConnection(this.ConnectionString);
break;
}
return(metaprov);
}
private void InitializeSqlContext()
{
try
{
QBuilder.Clear();
BaseMetadataProvider metadataProvaider = null;
// create new SqlConnection object using the connections string from the connection form
if (!_selectedConnection.IsXmlFile)
{
metadataProvaider = _selectedConnection.ConnectionDescriptor.MetadataProvider;
}
// setup the query builder with metadata and syntax providers
QBuilder.SQLContext.MetadataContainer.Clear();
QBuilder.MetadataProvider = metadataProvaider;
QBuilder.SyntaxProvider = _selectedConnection.ConnectionDescriptor.SyntaxProvider;
QBuilder.MetadataLoadingOptions.OfflineMode = metadataProvaider == null;
if (metadataProvaider == null)
{
QBuilder.MetadataContainer.ImportFromXML(_selectedConnection.ConnectionString);
}
// Instruct the query builder to fill the database schema tree
QBuilder.InitializeDatabaseSchemaTree();
}
finally
{
if (QBuilder.MetadataContainer.LoadingOptions.OfflineMode)
{
TsmiOfflineMode.IsChecked = true;
}
if (CBuilder.QueryTransformer != null)
{
CBuilder.QueryTransformer.SQLUpdated -= QueryTransformer_SQLUpdated;
}
CBuilder.QueryTransformer = new QueryTransformer
{
Query = QBuilder.SQLQuery,
SQLGenerationOptions = QBuilder.SQLGenerationOptions
};
CBuilder.QueryTransformer.SQLUpdated += QueryTransformer_SQLUpdated;
DataGridResult.QueryTransformer = CBuilder.QueryTransformer;
DataGridResult.SqlQuery = QBuilder.SQLQuery;
}
}
/// <summary>
/// Fetches metadata and returns true or false indicating if any work that requires persistence was done
/// </summary>
/// <param name="provider">The provider.</param>
/// <param name="item">The item.</param>
/// <param name="force">if set to <c>true</c> [force].</param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns>Task{System.Boolean}.</returns>
/// <exception cref="System.ArgumentNullException"></exception>
private async Task <bool> FetchAsync(BaseMetadataProvider provider, BaseItem item, bool force, CancellationToken cancellationToken)
{
if (item == null)
{
throw new ArgumentNullException();
}
cancellationToken.ThrowIfCancellationRequested();
_logger.Info("Running {0} for {1}", provider.GetType().Name, item.Path ?? item.Name ?? "--Unknown--");
// This provides the ability to cancel just this one provider
var innerCancellationTokenSource = new CancellationTokenSource();
OnProviderRefreshBeginning(provider, item, innerCancellationTokenSource);
try
{
return(await provider.FetchAsync(item, force, CancellationTokenSource.CreateLinkedTokenSource(cancellationToken, innerCancellationTokenSource.Token).Token).ConfigureAwait(false));
}
catch (OperationCanceledException ex)
{
_logger.Info("{0} cancelled for {1}", provider.GetType().Name, item.Name);
// If the outer cancellation token is the one that caused the cancellation, throw it
if (cancellationToken.IsCancellationRequested && ex.CancellationToken == cancellationToken)
{
throw;
}
return(false);
}
catch (Exception ex)
{
_logger.ErrorException("{0} failed refreshing {1}", ex, provider.GetType().Name, item.Name);
provider.SetLastRefreshed(item, DateTime.UtcNow, ProviderRefreshStatus.Failure);
return(true);
}
finally
{
innerCancellationTokenSource.Dispose();
OnProviderRefreshCompleted(provider, item);
}
}
private BaseMetadataProvider GetMetadataProvider()
{
BaseMetadataProvider metaprov = null;
switch (this.DatabasePlatform)
{
case QueryBuilderDatabasePlatform.MySQL:
metaprov = new MySQLMetadataProvider();
metaprov.Connection = new MySqlConnection(this.ConnectionString);
break;
default:
metaprov = new UniversalMetadataProvider();
metaprov.Connection = new OdbcConnection(this.ConnectionString);
break;
}
return(metaprov);
}
public static bool CheckParameters(BaseMetadataProvider metadataProvider, BaseSyntaxProvider syntaxProvider, ParameterList parameters)
{
var acceptableFormats =
GetAcceptableParametersFormats(metadataProvider, syntaxProvider);
if (acceptableFormats.Count == 0)
{
return(true);
}
foreach (var parameter in parameters)
{
if (!acceptableFormats.Any(x => IsSatisfiesFormat(parameter.FullName, x)))
{
return(false);
}
}
return(true);
}
private BaseMetadataProvider GetMetadataProvider()
{
BaseMetadataProvider metaprov = null;
switch (this.DatabasePlatform)
{
case QueryBuilderDatabasePlatform.Oracle:
metaprov = new OracleMetadataProvider();
metaprov.Connection = new System.Data.OracleClient.OracleConnection(this.ConnectionString);
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
break;
default:
metaprov = new UniversalMetadataProvider();
metaprov.Connection = new OdbcConnection(this.ConnectionString);
break;
}
return(metaprov);
}
private BaseMetadataProvider GetMetadataProvider()
{
BaseMetadataProvider metaprov = null;
switch (this.DatabasePlatform)
{
case QueryBuilderDatabasePlatform.SQLServerCE_V4:
metaprov = new MSSQLCEMetadataProvider();
metaprov.Connection = new SqlCeConnection(this.ConnectionString);
queryBuilder.DatabaseSchemaTreeOptions.DefaultExpandLevel = 0;
break;
default:
metaprov = new UniversalMetadataProvider();
metaprov.Connection = new OdbcConnection(this.ConnectionString);
break;
}
return(metaprov);
}
/// <summary>
/// Notifies the kernal that a provider has begun refreshing
/// </summary>
/// <param name="provider">The provider.</param>
/// <param name="item">The item.</param>
/// <param name="cancellationTokenSource">The cancellation token source.</param>
public void OnProviderRefreshBeginning(BaseMetadataProvider provider, BaseItem item, CancellationTokenSource cancellationTokenSource)
{
var key = item.Id + provider.GetType().Name;
Tuple <BaseMetadataProvider, BaseItem, CancellationTokenSource> current;
if (_currentlyRunningProviders.TryGetValue(key, out current))
{
try
{
current.Item3.Cancel();
}
catch (ObjectDisposedException)
{
}
}
var tuple = new Tuple <BaseMetadataProvider, BaseItem, CancellationTokenSource>(provider, item, cancellationTokenSource);
_currentlyRunningProviders.AddOrUpdate(key, tuple, (k, v) => tuple);
}
public static List <string> GetAcceptableParametersFormats(BaseMetadataProvider metadataProvider,
BaseSyntaxProvider syntaxProvider)
{
if (metadataProvider is MSSQLMetadataProvider)
{
return(new List <string> {
AtNameParamFormat
});
}
if (metadataProvider is OracleMetadataProvider)
{
return(new List <string> {
ColonNameParamFormat
});
}
if (metadataProvider is PostgreSQLMetadataProvider)
{
return(new List <string> {
ColonNameParamFormat
});
}
if (metadataProvider is MySQLMetadataProvider)
{
return(new List <string> {
AtNameParamFormat, QuestionParamFormat, QuestionNumberParamFormat, QuestionNameParamFormat
});
}
if (metadataProvider is OLEDBMetadataProvider)
{
if (syntaxProvider is MSAccessSyntaxProvider)
{
return(new List <string> {
AtNameParamFormat, ColonNameParamFormat, QuestionParamFormat
});
}
if (syntaxProvider is MSSQLSyntaxProvider)
{
return(new List <string> {
QuestionParamFormat
});
}
if (syntaxProvider is OracleSyntaxProvider)
{
return(new List <string> {
ColonNameParamFormat, QuestionParamFormat, QuestionNumberParamFormat
});
}
if (syntaxProvider is DB2SyntaxProvider)
{
return(new List <string> {
QuestionParamFormat
});
}
}
if (metadataProvider is ODBCMetadataProvider)
{
if (syntaxProvider is MSAccessSyntaxProvider)
{
return(new List <string> {
QuestionParamFormat
});
}
if (syntaxProvider is MSSQLSyntaxProvider)
{
return(new List <string> {
QuestionParamFormat
});
}
if (syntaxProvider is MySQLSyntaxProvider)
{
return(new List <string> {
QuestionParamFormat
});
}
if (syntaxProvider is PostgreSQLSyntaxProvider)
{
return(new List <string> {
QuestionParamFormat
});
}
if (syntaxProvider is OracleSyntaxProvider)
{
return(new List <string> {
ColonNameParamFormat, QuestionParamFormat, QuestionNumberParamFormat
});
}
if (syntaxProvider is DB2SyntaxProvider)
{
return(new List <string> {
QuestionParamFormat
});
}
}
return(new List <string>());
}
private void InitializeSqlContext()
{
try
{
Cursor = Cursors.WaitCursor;
BaseMetadataProvider metadataProvider = null;
BaseSyntaxProvider syntaxSyntaxProvider;
// create new SqlConnection object using the connections string from the connection form
if (!_selectedConnection.IsXmlFile)
{
syntaxSyntaxProvider = _selectedConnection.ConnectionDescriptor.SyntaxProvider;
metadataProvider = _selectedConnection.ConnectionDescriptor.MetadataProvider;
}
else
{
switch (_selectedConnection.Type)
{
case ConnectionTypes.MSSQL:
syntaxSyntaxProvider = new MSSQLSyntaxProvider();
break;
case ConnectionTypes.MSAccess:
syntaxSyntaxProvider = new MSAccessSyntaxProvider();
break;
case ConnectionTypes.Oracle:
syntaxSyntaxProvider = new OracleSyntaxProvider();
break;
case ConnectionTypes.MySQL:
syntaxSyntaxProvider = new MySQLSyntaxProvider();
break;
case ConnectionTypes.PostgreSQL:
syntaxSyntaxProvider = new PostgreSQLSyntaxProvider();
break;
case ConnectionTypes.OLEDB:
syntaxSyntaxProvider = new SQL92SyntaxProvider();
break;
case ConnectionTypes.ODBC:
syntaxSyntaxProvider = new SQL92SyntaxProvider();
break;
default:
throw new ArgumentOutOfRangeException();
}
}
// setup the query builder with metadata and syntax providers
queryBuilder1.SQLContext.MetadataContainer.Clear();
queryBuilder1.MetadataProvider = metadataProvider;
queryBuilder1.SyntaxProvider = syntaxSyntaxProvider;
queryBuilder1.MetadataLoadingOptions.OfflineMode = metadataProvider == null;
if (metadataProvider == null)
{
queryBuilder1.MetadataContainer.ImportFromXML(_selectedConnection.ConnectionString);
}
// Instruct the query builder to fill the database schema tree
queryBuilder1.InitializeDatabaseSchemaTree();
toolStripStatusLabel1.Text = @"Query builder state: " + (queryBuilder1.SleepMode ? "Inactive" : "Active");
RepairImageLists();
RefreshNoConnectionLabel();
queryBuilder1.QueryView.UserPredefinedConditions.Clear();
queryBuilder1.QueryView.UserPredefinedConditions.Add(
new PredefinedCondition(
"Check range",
null,
"Between 10 AND 100",
false));
}
finally
{
Cursor = Cursors.Default;
}
}
