private void tabControl1_Selected(object sender, TabControlEventArgs e)
{
// Move the input focus to the query builder.
// This will fire Leave event in the text box and update the query builder
// with modified query text.
queryBuilder1.Focus();
Application.DoEvents();
// Try to execute the query using current database connection
if (e.TabPage == tabPageData)
{
dataGridView1.DataSource = null;
if (queryBuilder1.MetadataProvider != null && queryBuilder1.MetadataProvider.Connected)
{
DB2Command command = (DB2Command)queryBuilder1.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryBuilder1.SQL;
// handle the query parameters
if (queryBuilder1.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder1.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder1.Parameters[i].FullName))
{
DB2Parameter parameter = new DB2Parameter();
parameter.ParameterName = queryBuilder1.Parameters[i].FullName;
parameter.DbType = queryBuilder1.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
DB2DataAdapter adapter = new DB2DataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
}
}
private static DbCommand CreateSqlCommand(string sqlCommand, SQLQuery sqlQuery)
{
DbCommand command = (DbCommand)sqlQuery.SQLContext.MetadataProvider.Connection.CreateCommand();
command.CommandText = sqlCommand;
// handle the query parameters
if (sqlQuery.QueryParameters.Count <= 0)
{
return(command);
}
foreach (Parameter p in sqlQuery.QueryParameters.Where(item => !command.Parameters.Contains(item.FullName)))
{
var parameter = command.CreateParameter();
parameter.ParameterName = p.FullName;
parameter.DbType = p.DataType;
command.Parameters.Add(parameter);
}
var qpf = new QueryParametersForm(command);
qpf.ShowDialog();
return(command);
}
private void UpdateResultsGrid()
{
// Check database connection
if (queryBuilder.MetadataProvider == null || queryBuilder.MetadataProvider.Connection == null)
{
Label label = new Label();
label.Text = "You should connect a database";
label.TextAlign = ContentAlignment.MiddleCenter;
label.Dock = DockStyle.Fill;
dataGridView1.Controls.Add(label);
}
else if (queryBuilder.SQL.Length == 0) // check the query text is not empty
{
Label label = new Label();
label.Text = "No query to execute";
label.TextAlign = ContentAlignment.MiddleCenter;
label.Dock = DockStyle.Fill;
dataGridView1.Controls.Add(label);
}
else
{
dataGridView1.Controls.Clear();
}
var queryToExecute = "";
// Limit query results to 10 rows for preview purposes
//queryBuilder.SQLContext.LoadingOptions.OfflineMode = true;
//queryBuilder.SQLContext.SyntaxProvider = queryBuilder.SyntaxProvider;
//queryBuilder.SQLContext.MetadataProvider = queryBuilder.MetadataProvider;
//var query = new SQLQuery(sqlContext) {SQL = queryToExecute};
//tempQueryBuilder.SQL = queryToExecute;
using (QueryTransformer queryTransformer = new QueryTransformer())
{
queryTransformer.Query = queryBuilder.QueryView.Query;
queryTransformer.ResultCount = "10"; // select top 10 rows only
queryToExecute = queryTransformer.SQL;
}
// Try to execute the query using current database connection:
if (tabControl1.SelectedTab == tabPageResultsPreview)
{
dataGridView1.DataSource = null;
if (queryBuilder.MetadataProvider != null && queryBuilder.MetadataProvider.Connected)
{
if (queryBuilder.MetadataProvider is MSSQLMetadataProvider)
{
SqlCommand command = (SqlCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryToExecute;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
else if (queryBuilder.MetadataProvider is OLEDBMetadataProvider)
{
OleDbCommand command = (OleDbCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryToExecute;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
OleDbParameter parameter = new OleDbParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
else if (queryBuilder.MetadataProvider is ODBCMetadataProvider)
{
OdbcCommand command = (OdbcCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
command.CommandText = queryToExecute;
// handle the query parameters
if (queryBuilder.Parameters.Count > 0)
{
for (int i = 0; i < queryBuilder.Parameters.Count; i++)
{
if (!command.Parameters.Contains(queryBuilder.Parameters[i].FullName))
{
OdbcParameter parameter = new OdbcParameter();
parameter.ParameterName = queryBuilder.Parameters[i].FullName;
parameter.DbType = queryBuilder.Parameters[i].DataType;
command.Parameters.Add(parameter);
}
}
using (QueryParametersForm qpf = new QueryParametersForm(command))
{
qpf.ShowDialog();
}
}
OdbcDataAdapter adapter = new OdbcDataAdapter(command);
DataSet dataset = new DataSet();
try
{
adapter.Fill(dataset, "QueryResult");
dataGridView1.DataSource = dataset.Tables["QueryResult"];
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "SQL query error");
}
}
// enable sorting
foreach (DataGridViewColumn column in dataGridView1.Columns)
{
column.SortMode = DataGridViewColumnSortMode.Automatic;
}
}
}
}
