private void QueryBuilder_SetupProperties(string serverType)
{
queryBuilder1.ExpressionEditor = expressionEditor1;
sqlTextEditor1.QueryBuilder = queryBuilder1;
if (serverType.ToLower( ) == "mssql")
{
SqlConnection mssqlConn = (SqlConnection)Entity.GetConnection(iqtoolsConnString, "mssql");
MSSQLSyntaxProvider syntaxProvider = new MSSQLSyntaxProvider( );
MSSQLMetadataProvider metadataProvider = new MSSQLMetadataProvider( );
metadataProvider.Connection = mssqlConn;
queryBuilder1.SyntaxProvider = syntaxProvider;
queryBuilder1.MetadataProvider = metadataProvider;
qb.SyntaxProvider = syntaxProvider;
qb.MetadataProvider = metadataProvider;
}
else if (serverType.ToLower( ) == "mysql")
{
MySqlConnection mysqlConn = (MySqlConnection)Entity.GetConnection(iqtoolsConnString, "mysql");
MySQLSyntaxProvider mysqlSyntaxProvider = new MySQLSyntaxProvider( );
MySQLMetadataProvider mysqlMetadataProvider = new MySQLMetadataProvider( );
mysqlMetadataProvider.Connection = mysqlConn;
queryBuilder1.SyntaxProvider = mysqlSyntaxProvider;
queryBuilder1.MetadataProvider = mysqlMetadataProvider;
qb.SyntaxProvider = mysqlSyntaxProvider;
}
else if (serverType.ToLower() == "pgsql")
{
//NpgsqlConnection pgsqlConn = (NpgsqlConnection)Entity.GetConnection(iqtoolsConnString, "pgsql");
//PostgreSQLSyntaxProvider pgsqlSyntaxProvider = new PostgreSQLSyntaxProvider();
////UniversalSyntaxProvider uSyntaxProvider = new UniversalSyntaxProvider();
//UniversalMetadataProvider umdprovider = new UniversalMetadataProvider();
//umdprovider.Connection = pgsqlConn;
//queryBuilder1.SyntaxProvider = pgsqlSyntaxProvider;
////queryBuilder1.SyntaxProvider = uSyntaxProvider;
//queryBuilder1.MetadataProvider = umdprovider;
//qb.SyntaxProvider = pgsqlSyntaxProvider;
////qb.SyntaxProvider = uSyntaxProvider;
}
if (rdbAdvanced.Checked)
{
queryBuilder1.RefreshMetadata( );
}
else if (rdbBasic.Checked)
{
if (server == "pgsql")
{
queryBuilder1.MetadataContainer.LoadFromXMLFile("Resources\\iqtools_cpad.xml");
}
else
{
queryBuilder1.MetadataContainer.LoadFromXMLFile("Resources\\iqtools_iqcare.xml");
}
}
}
//methods
public void InitForTest()
{
ResetQueryBuilder();
MSSQLMetadataProvider metaprov = new MSSQLMetadataProvider();
//metaprov.Connection = new SqlConnection("Server=PROFASTWS1;Database=Namelists;Trusted_Connection=True;");
metaprov.Connection = new SqlConnection("Server=PROFASTWS1;Database=AdventureWorks2008R2;Trusted_Connection=True;");
queryBuilder.MetadataProvider = metaprov;
MSSQLSyntaxProvider sqlsyn = new MSSQLSyntaxProvider();
queryBuilder.SyntaxProvider = sqlsyn;
queryBuilder.SQL = this.QueryText;
// kick the query builder to fill metadata tree
queryBuilder.InitializeDatabaseSchemaTree();
}
private BaseSyntaxProvider GetSyntaxProvider()
{
BaseSyntaxProvider sqlsyn = null;
switch (this.DatabasePlatform)
{
case QueryBuilderDatabasePlatform.MSSQLServer:
sqlsyn = new MSSQLSyntaxProvider();
break;
case QueryBuilderDatabasePlatform.SQLServerCE:
//sqlsyn = new MSSQLCESyntaxProvider(); //does not work
sqlsyn = new GenericSyntaxProvider(); //does not work
break;
case QueryBuilderDatabasePlatform.OLEDB:
if (queryBuilder.MetadataProvider.Connection.ConnectionString.Contains("Provider=Microsoft.ACE.OLEDB.12.0") ||
queryBuilder.MetadataProvider.Connection.ConnectionString.Contains("Provider=Microsoft.Jet.OLEDB.4.0"))
{
sqlsyn = new MSAccessSyntaxProvider();
}
else if (queryBuilder.MetadataProvider.Connection.ConnectionString.Contains("Provider=OraOLEDB.Oracle") ||
queryBuilder.MetadataProvider.Connection.ConnectionString.Contains("Provider=msdaora"))
{
sqlsyn = new OracleSyntaxProvider();
}
else
{
switch (this.AnsiSQLVersion)
{
case AnsiSQLLevel.SQL89:
sqlsyn = new SQL89SyntaxProvider();
break;
case AnsiSQLLevel.SQL92:
sqlsyn = new SQL92SyntaxProvider();
break;
case AnsiSQLLevel.SQL2003:
sqlsyn = new SQL2003SyntaxProvider();
break;
}
}
break;
case QueryBuilderDatabasePlatform.ODBC:
if (queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={microsoft access driver"))
{
sqlsyn = new MSAccessSyntaxProvider();
}
else if (queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={oracle") ||
queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={microsoft odbc for oracle"))
{
sqlsyn = new OracleSyntaxProvider();
}
else if (queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={sql server") ||
queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("sql server"))
{
sqlsyn = new MSSQLSyntaxProvider();
}
else if (queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={ibm db2") ||
queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("ibm db2"))
{
sqlsyn = new DB2SyntaxProvider();
}
else if (queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={ibm informix") ||
queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("informix"))
{
sqlsyn = new InformixSyntaxProvider();
}
else if (queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={mysql"))
{
sqlsyn = new MySQLSyntaxProvider();
}
else if (queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={adaptive server") ||
queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={sybase ase"))
{
sqlsyn = new SybaseSyntaxProvider();
}
else if (queryBuilder.MetadataProvider.Connection.ConnectionString.ToLower().Contains("driver={sql anywhere"))
{
sqlsyn = new GenericSyntaxProvider();
}
else
{
switch (this.AnsiSQLVersion)
{
case AnsiSQLLevel.SQL89:
sqlsyn = new SQL89SyntaxProvider();
break;
case AnsiSQLLevel.SQL92:
sqlsyn = new SQL92SyntaxProvider();
break;
case AnsiSQLLevel.SQL2003:
sqlsyn = new SQL2003SyntaxProvider();
break;
}
}
break;
case QueryBuilderDatabasePlatform.MSAccess:
sqlsyn = new MSAccessSyntaxProvider();
break;
case QueryBuilderDatabasePlatform.Oracle:
sqlsyn = new OracleSyntaxProvider();
break;
default:
sqlsyn = new GenericSyntaxProvider();
break;
}
return(sqlsyn);
}
private void getQueryParameters(string querySQL)
{
QueryBuilder qb = new QueryBuilder();
if (serverType == "mssql")
{
MSSQLSyntaxProvider syntaxProvider = new MSSQLSyntaxProvider();
qb.SyntaxProvider = syntaxProvider;
qb.SQL = querySQL;
SqlCommand cmd = new SqlCommand(querySQL);
if (qb.Parameters.Count > 0)
{
Hashtable myParameters = new Hashtable();
int j = 0; myParameters.Clear();
//ParameterList pl;
for (int i = 0; i < qb.Parameters.Count; i++)
{
j = 0;
SqlParameter p = new SqlParameter();
p.ParameterName = qb.Parameters[i].Name;
p.DbType = qb.Parameters[i].DataType;
foreach (DictionaryEntry de in myParameters)
{
if (de.Key.ToString().Trim().ToLower() == qb.Parameters[i].Name.Trim().ToLower())
{
j = 1;
break;
}
}
if (j == 0)
{
myParameters.Add(p.ParameterName, p.DbType);
}
}
using (frmQueryParameters qp = new frmQueryParameters(qb.Parameters, cmd))
{
qp.StartPosition = FormStartPosition.CenterScreen;
qp.ShowDialog();
}
}
}
else if (serverType == "pgsql")
{
//PostgreSQLSyntaxProvider syntaxProvider = new PostgreSQLSyntaxProvider();
//qb.SyntaxProvider = syntaxProvider;
//qb.SQL = querySQL;
//NpgsqlCommand cmd = new NpgsqlCommand(querySQL);
//if (qb.Parameters.Count > 0)
//{
// Hashtable myParameters = new Hashtable(); int j = 0; myParameters.Clear();
// for (int i = 0; i < qb.Parameters.Count; i++)
// {
// j = 0;
// NpgsqlParameter p = new NpgsqlParameter();
// p.ParameterName = qb.Parameters[i].FullName;
// p.DbType = qb.Parameters[i].DataType;
// foreach (DictionaryEntry de in myParameters)
// {
// if (de.Key.ToString().Trim().ToLower() == qb.Parameters[i].FullName.Trim().ToLower())
// {
// j = 1;
// break;
// }
// }
// if (j == 0)
// {
// cmd.Parameters.Add(p);
// myParameters.Add(p.ParameterName, p.DbType);
// }
// }
// using (frmQueryParameters qp = new frmQueryParameters(qb.Parameters, cmd))
// {
// qp.StartPosition = FormStartPosition.CenterScreen;
// qp.ShowDialog();
// }
//}
}
}
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;
}
}