private ActionResult GetData(QueryTransformer qt, Param[] _params)
{
var conn = DataBaseHelper.CreateSqLiteConnection(GetDataBasePath());
var sql = qt.SQL;
if (_params != null)
{
foreach (var p in _params)
{
p.DataType = qt.Query.QueryParameters.First(qp => qp.FullName == p.Name).DataType;
}
}
try
{
var data = DataBaseHelper.GetData(conn, sql, _params);
return(Json(data));
}
catch (Exception e)
{
return(Json(new ErrorOutput {
Error = e.Message
}));
}
}
public ActionResult SelectRecordsCount(Param[] _params)
{
var qb = _aqbs.Get(instanceId);
var qt = _qts.Get(instanceId);
var qtForSelectRecordsCount = new QueryTransformer {
QueryProvider = qb.SQLQuery
};
try
{
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
try
{
var data = Execute(qtForSelectRecordsCount, _params);
return(Json(data.First().Values.First()));
}
catch (Exception e)
{
return(Json(new ErrorOutput {
Error = e.Message
}));
}
}
finally
{
qtForSelectRecordsCount.Dispose();
}
}
// Protected methods
protected virtual async Task ProcessBatch(List <BatchItem <TKey, TEntity> > batch, CancellationToken cancellationToken)
{
await using var dbContext = CreateDbContext();
var keys = new HashSet <TKey>();
foreach (var item in batch)
{
if (!item.TryCancel(cancellationToken))
{
keys.Add(item.Input);
}
}
var pEntity = Expression.Parameter(typeof(TEntity), "e");
var eKey = KeyExtractorExpressionBuilder.Invoke(pEntity);
var eBody = Expression.Call(Expression.Constant(keys), ContainsMethod, eKey);
var eLambda = (Expression <Func <TEntity, bool> >)Expression.Lambda(eBody, pEntity);
var query = QueryTransformer.Invoke(dbContext.Set <TEntity>().Where(eLambda));
var entities = await query
.ToDictionaryAsync(KeyExtractor, cancellationToken)
.ConfigureAwait(false);
PostProcessor.Invoke(entities);
foreach (var item in batch)
{
entities.TryGetValue(item.Input, out var entity);
item.SetResult(Result.Value(entity) !, CancellationToken.None);
}
}
private void Form1_Load(object sender, EventArgs e)
{
Load -= Form1_Load;
_sqlContext = new SQLContext
{
SyntaxProvider = new MSSQLSyntaxProvider {
ServerVersion = MSSQLServerVersion.MSSQL2012
},
LoadingOptions = { OfflineMode = true }
};
_sqlContext.MetadataContainer.ImportFromXML("Northwind.xml");
var sqlText = new StringBuilder();
sqlText.AppendLine("Select Categories.CategoryName,");
sqlText.AppendLine("Products.QuantityPerUnit");
sqlText.AppendLine("From Categories");
sqlText.AppendLine("Inner Join Products On Categories.CategoryID = Products.CategoryID");
_sqlQuery = new SQLQuery(_sqlContext);
_sqlQuery.SQLUpdated += _sqlQuery_SQLUpdated;
_sqlQuery.SQL = sqlText.ToString();
_queryTransformer = new QueryTransformer {
Query = _sqlQuery
};
_queryTransformer.SQLUpdated += _queryTransformer_SQLUpdated;
LoadData();
}
public void TestWillNotRetrieveValuesIfThereAreNoSetterActions()
{
string columnName = "col";
IEnumerable <object[]> values = new List <object[]>()
{
new object[] { "value_for_col" }
};
Mock <IColumnToObject <object> > mockColumnToObject = new Mock <IColumnToObject <object> >();
Mock <IQueryResult> mockQueryResult = new Mock <IQueryResult>();
mockColumnToObject.Setup(_ => _.GetColumnSetter(columnName)).Returns(null);
mockQueryResult.Setup(_ => _.ColumnsNames()).Returns(new List <string>()
{
columnName
});
mockQueryResult.Setup(_ => _.ResultRows()).Returns(values);
QueryTransformer <object> model = new QueryTransformer <object>("transformer", mockColumnToObject.Object);
model.Transform(mockQueryResult.Object);
IInvocationList invocations = mockQueryResult.Invocations;
List <IInvocation> invocationMethods = new List <IInvocation>(invocations);
List <string> calledMethods = new List <string>(invocationMethods.ConvertAll(current => current.Method.Name));
CollectionAssert.DoesNotContain(calledMethods, "ResultRows");
}
private void MainWindow_Loaded(object sender, RoutedEventArgs e)
{
Loaded -= MainWindow_Loaded;
_collectionColumns = new ObservableCollection <VisibleColumn>();
_collectionColumns.CollectionChanged += _collectionColumns_CollectionChanged;
_sqlContext = new SQLContext
{
SyntaxProvider = new MSSQLSyntaxProvider {
ServerVersion = MSSQLServerVersion.MSSQL2012
},
LoadingOptions = { OfflineMode = true }
};
_sqlContext.MetadataContainer.ImportFromXML("Northwind.xml");
// Load a sample query
var sqlText = new StringBuilder();
sqlText.AppendLine("Select Categories.CategoryName,");
sqlText.AppendLine("Products.QuantityPerUnit");
sqlText.AppendLine("From Categories");
sqlText.AppendLine("Inner Join Products On Categories.CategoryID = Products.CategoryID");
_sqlQuery = new SQLQuery(_sqlContext);
_sqlQuery.SQLUpdated += _sqlQuery_SQLUpdated;
_sqlQuery.SQL = sqlText.ToString();
_queryTransformer = new QueryTransformer {
Query = _sqlQuery
};
_queryTransformer.SQLUpdated += _queryTransformer_SQLUpdated;
_queryTransformer.SQLGenerationOptions = new SQLFormattingOptions();
LoadQuery();
}
public ActionResult GetData()
{
var qb = _aqbs.Get(instanceId);
var conn = qb.MetadataProvider.Connection;
var sqlQuery = new SQLQuery(qb.SQLContext)
{
SQL = qb.ActiveUnionSubQuery.SQL
};
QueryTransformer qt = new QueryTransformer
{
QueryProvider = sqlQuery
};
qt.Take("7");
var columns = qt.Columns.Select(c => c.ResultName).ToList();
try
{
var data = DataBaseHelper.GetDataList(conn, qt.SQL);
var result = new { columns, data };
return(Json(result));
}
catch (Exception e)
{
var result = new { columns, data = new List <List <object> > {
new List <object> {
e.Message
}
} };
return(Json(result));
}
}
public ContentWindowChild(SQLContext sqlContext)
{
Init();
SqlContext = sqlContext;
SqlSourceType = SourceType.New;
SqlQuery = new SQLQuery(SqlContext);
SqlQuery.SQLUpdated += SqlQuery_SQLUpdated;
SqlQuery.QueryRoot.AllowSleepMode = true;
SqlQuery.QueryAwake += SqlQueryOnQueryAwake;
SqlQuery.SleepModeChanged += SqlQuery_SleepModeChanged;
NavigationBar.QueryView = QueryView;
QueryView.Query = SqlQuery;
QueryView.ActiveUnionSubQueryChanged += QueryView_ActiveUnionSubQueryChanged;
BoxSql.Query = SqlQuery;
BoxSqlCurrentSubQuery.Query = SqlQuery;
BoxSqlCurrentSubQuery.ExpressionContext = QueryView.ActiveUnionSubQuery;
QueryView.ActiveUnionSubQueryChanged += delegate
{
BoxSqlCurrentSubQuery.ExpressionContext = QueryView.ActiveUnionSubQuery;
};
_transformerSql = new QueryTransformer();
_timerStartingExecuteSql = new Timer(TimerStartingExecuteSql_Elapsed);
CBuilder.QueryTransformer = new QueryTransformer
{
Query = SqlQuery
};
// Options to present the formatted SQL query text to end-user
// Use names of virtual objects, do not replace them with appropriate derived tables
SqlFormattingOptions = new SQLFormattingOptions {
ExpandVirtualObjects = false
};
// Options to generate the SQL query text for execution against a database server
// Replace virtual objects with derived tables
SqlGenerationOptions = new SQLGenerationOptions {
ExpandVirtualObjects = true
};
NavigationBar.QueryView = QueryView;
NavigationBar.Query = SqlQuery;
CBuilder.QueryTransformer.SQLUpdated += QueryTransformer_SQLUpdated;
DataViewerResult.QueryTransformer = CBuilder.QueryTransformer;
DataViewerResult.SqlQuery = SqlQuery;
UpdateStateButtons();
}
public void TestCreatesModelsWithExpectedValues()
{
string columnName = "name";
string columnDescription = "description";
string columnPrice = "price";
string nameModelA = "name of model";
string descriptionModelA = "desc";
decimal priceModelA = 3m;
string nameModelB = "name ";
string descriptionModelB = "desct";
decimal priceModelB = 54.32m;
IEnumerable <string> columns = new List <string>()
{
columnName, columnDescription, columnPrice
};
IEnumerable <object[]> values = new List <object[]>()
{
new object[] { nameModelA, descriptionModelA, priceModelA },//ModelA
new object[] { nameModelB, descriptionModelB, priceModelB }//ModelB
};
//Mocked objects
Mock <IColumnToObject <Model> > mockColumnToObject = new Mock <IColumnToObject <Model> >();
Mock <IQueryResult> mockQueryResult = new Mock <IQueryResult>();
//Setup of mockColumnToObject
mockColumnToObject.Setup(_ => _.GetColumnSetter(columnName)).Returns((model, value) => model.Name = value.ToString());
mockColumnToObject.Setup(_ => _.GetColumnSetter(columnDescription)).Returns((model, value) => model.Descripcion = value.ToString());
mockColumnToObject.Setup(_ => _.GetColumnSetter(columnPrice)).Returns((model, value) => model.Price = decimal.Parse(value.ToString()));
//Setup of mockQueryResult
mockQueryResult.Setup(_ => _.ColumnsNames()).Returns(columns);
mockQueryResult.Setup(_ => _.ResultRows()).Returns(values);
IColumnToObject <Model> columnToObject = mockColumnToObject.Object;
QueryTransformer <Model> queryTransformer = new QueryTransformer <Model>("transformer", columnToObject);
List <Model> createdModels = new List <Model>(queryTransformer.Transform(mockQueryResult.Object));
Assert.AreEqual(2, createdModels.Count);
Model modelA = createdModels[0];
Model modelB = createdModels[1];
//ModelA check
Assert.AreEqual(nameModelA, modelA.Name);
Assert.AreEqual(descriptionModelA, modelA.Descripcion);
Assert.AreEqual(priceModelA, modelA.Price);
//ModelB check
Assert.AreEqual(nameModelB, modelB.Name);
Assert.AreEqual(descriptionModelB, modelB.Descripcion);
Assert.AreEqual(priceModelB, modelB.Price);
}
public void Put(QueryTransformer qt)
{
if (GetState(qt.Tag.ToString()) == null)
{
Insert(qt);
}
else
{
Update(qt);
}
}
private ActionResult GetData(QueryTransformer qt, Param[] _params)
{
try
{
var data = Execute(qt, _params);
return(Json(data));
}
catch (Exception e)
{
return(Json(new ErrorOutput {
Error = e.Message
}));
}
}
public QueryTransformer Get(string id)
{
var qt = new QueryTransformer {
Tag = id, QueryProvider = _aqbs.Get(id).SQLQuery
};
var state = GetState(id);
if (state != null)
{
qt.XML = state;
}
return(qt);
}
private List <Dictionary <string, object> > Execute(QueryTransformer qt, Param[] _params)
{
var conn = qt.Query.SQLContext.MetadataProvider.Connection;
var sql = qt.SQL;
if (_params != null)
{
foreach (var p in _params)
{
p.DataType = qt.Query.QueryParameters.First(qp => qp.FullName == p.Name).DataType;
}
}
return(DataBaseHelper.GetData(conn, sql, _params));
}
public ActionResult GetRecordCountSql([FromBody] GetRecordCountSqlModel model)
{
var qt = _qts.Get(model.InstanceId);
using (var qtForSelectRecordsCount = new QueryTransformer {
QueryProvider = qt.QueryProvider
})
{
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
return(Content(qtForSelectRecordsCount.SQL));
}
}
public ChildForm(SQLContext sqlContext, ConnectionInfo connectionInfo)
{
InitializeComponent();
_queryTransformerTop10 = new QueryTransformer();
Debug.Assert(sqlContext != null);
SqlSourceType = SourceType.New;
_sqlContext = sqlContext;
_connectionInfo = connectionInfo;
SqlQuery = new SQLQuery(_sqlContext);
SqlQuery.QueryRoot.AllowSleepMode = true;
SqlQuery.SleepModeChanged += SqlQuery_SleepModeChanged;
SqlQuery.QueryAwake += SqlQuery_QueryAwake;
_timerForFastResult = new Timer(TimerForFastResult_Elapsed);
CBuilder.QueryTransformer = new QueryTransformer
{
Query = SqlQuery
};
SqlFormattingOptions = new SQLFormattingOptions();
CBuilder.QueryTransformer.SQLUpdated += CBuilder_SQLUpdated;
rtbQueryText.QueryProvider = SqlQuery;
TextBoxCurrentSubQuerySql.QueryProvider = SqlQuery;
resultGrid1.SqlQuery = SqlQuery;
resultGrid2.SqlQuery = SqlQuery;
resultGrid1.QueryTransformer = CBuilder.QueryTransformer;
QView.Query = SqlQuery;
NavBar.QueryView = QView;
NavBar.Query = SqlQuery;
RepairImageLists();
toolStripStatusLabel1.Text = "Query builder state: " + ((SqlQuery.SleepMode) ? "Inactive" : "Active");
Application.Idle += Application_Idle;
SqlQuery.SQLUpdated += query_SQLUpdated;
QueryView.ActiveUnionSubQueryChanged += QueryViewOnActiveUnionSubQueryChanged;
rtbQueryText.ExpressionContext = QView.ActiveUnionSubQuery;
TextBoxCurrentSubQuerySql.ExpressionContext = QView.ActiveUnionSubQuery;
QueryNavBarOptions.Updated += QueryNavBarOptions_Updated;
}
public QueryTransformer Get(string id)
{
var qt = new QueryTransformer {
Tag = id
};
qt.QueryProvider = QueryBuilderStore.Get(id).SQLQuery;
var state = GetState(id);
if (state != null)
{
qt.XML = state;
}
return(qt);
}
private void UpdateRecordCount(QueryTransformer qt)
{
var qtForSelectRecordsCount = QueryTransformerStore.Create("QueryResults_for_select_records_count");
qtForSelectRecordsCount.QueryProvider = qt.QueryProvider;
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
try
{
var data = SelectRecordsCount.GetData(qtForSelectRecordsCount, new Param[0]);
_recordsCount = int.Parse(data.First().Values.First().ToString());
recordsCount.Text = "Records count: " + _recordsCount;
}
finally
{
QueryTransformerStore.Remove("QueryResults_for_select_records_count");
}
}
private void UpdateRecordCount(QueryTransformer qt)
{
var qtForSelectRecordsCount = new QueryTransformer {
QueryProvider = qt.QueryProvider
};
try
{
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
var data = SelectRecordsCount.GetData(qtForSelectRecordsCount, new Param[0]);
_recordsCount = int.Parse(data.First().Values.First().ToString());
recordsCount.Text = "Records count: " + _recordsCount;
}
finally
{
qtForSelectRecordsCount.Dispose();
}
}
public ActionResult SelectRecordsCount([FromBody] Param[] _params)
{
var qb = _aqbs.Get(instanceId);
var qt = _qts.Get(instanceId);
var qtForSelectRecordsCount = new QueryTransformer {
QueryProvider = qb.SQLQuery
};
try
{
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
return(GetData(qtForSelectRecordsCount, _params));
}
finally
{
qtForSelectRecordsCount.Dispose();
}
}
private ActionResult GetData(QueryTransformer qt, Param[] _params)
{
var conn = qt.Query.SQLContext.MetadataProvider.Connection;
var sql = qt.SQL;
if (_params != null)
{
foreach (var p in _params)
{
p.DataType = qt.Query.QueryParameters.First(qp => qp.FullName == p.Name).DataType;
}
}
try
{
var data = DataBaseHelper.GetData(conn, sql, _params);
return(Json(data));
}
catch (Exception e)
{
return(StatusCode((int)HttpStatusCode.BadRequest, e.Message));
}
}
private int GetRowCount(QueryBuilder queryBuilder, QueryTransformer queryTransformer)
{
try
{
queryBuilder.MetadataProvider.Connection.Open();
queryTransformer.ResultOffset = null;
queryTransformer.ResultCount = null;
var selectedColumn = new SelectedColumn(null, "count(*)");
queryTransformer.Aggregations.Add(selectedColumn, "cnt");
var cmd = (OleDbCommand)queryBuilder.MetadataProvider.Connection.CreateCommand();
cmd.CommandTimeout = 30;
cmd.CommandText = queryTransformer.Sql;
queryTransformer.Aggregations.Remove(selectedColumn);
return((int)cmd.ExecuteScalar());
}
catch (Exception ex)
{
string message = "GetRowCount error!";
Logger.Error(message, ex);
throw;
}
}
public override object GetDataForModel(Param[] _params)
{
var qb = QueryBuilderStore.Get("QueryResults");
var qt = QueryTransformerStore.Get("QueryResults");
var qtForSelectRecordsCount = new QueryTransformer {
QueryProvider = qt.QueryProvider
};
try
{
qtForSelectRecordsCount.Assign(qt);
qtForSelectRecordsCount.Skip("");
qtForSelectRecordsCount.Take("");
qtForSelectRecordsCount.SelectRecordsCount("recCount");
var data = GetData(qtForSelectRecordsCount, _params);
return(data.First().Values.First());
}
finally
{
qtForSelectRecordsCount.Dispose();
}
}
public void Put(QueryTransformer qt)
{
var key = GetCacheKey(qt.Tag.ToString());
ExecuteWithLock(key, () => _cache.SetString(key, qt.XML));
}
public void TestExecutesDefinedSetterActions()
{
string columnName = "name";
string columnDescription = "description";
string columnPrice = "price";
string columnWithNoAction = "no_action_column";
string columnWithNoAction2 = "no_action_column2";
string name = "name of model";
string description = "desc";
decimal price = 3m;
string noAction = "no_action_value";
string noAction2 = "no_action_value2";
IEnumerable <string> columns = new List <string>()
{
columnName, columnDescription, columnPrice, columnWithNoAction, columnWithNoAction2
};
IEnumerable <object[]> values = new List <object[]>()
{
new object[] { name, description, price, noAction, noAction2 }
};
//Mocked objects
Mock <Action <Model, object> > mockNameAction = new Mock <Action <Model, object> >();
Mock <Action <Model, object> > mockDescriptionAction = new Mock <Action <Model, object> >();
Mock <Action <Model, object> > mockPriceAction = new Mock <Action <Model, object> >();
Mock <IColumnToObject <Model> > mockColumnToObject = new Mock <IColumnToObject <Model> >();
Mock <IQueryResult> mockQueryResult = new Mock <IQueryResult>();
//Setup of mockColumnToObject
mockColumnToObject.Setup(_ => _.GetColumnSetter(columnName)).Returns(mockNameAction.Object);
mockColumnToObject.Setup(_ => _.GetColumnSetter(columnDescription)).Returns(mockDescriptionAction.Object);
mockColumnToObject.Setup(_ => _.GetColumnSetter(columnPrice)).Returns(mockPriceAction.Object);
mockColumnToObject.Setup(_ => _.GetColumnSetter(columnWithNoAction)).Returns(null);
mockColumnToObject.Setup(_ => _.GetColumnSetter(columnWithNoAction2)).Returns(null);
//Setup of mockQueryResult
mockQueryResult.Setup(_ => _.ColumnsNames()).Returns(columns);
mockQueryResult.Setup(_ => _.ResultRows()).Returns(values);
IColumnToObject <Model> columnToObject = mockColumnToObject.Object;
QueryTransformer <Model> queryTransformer = new QueryTransformer <Model>("transformer", columnToObject);
queryTransformer.Transform(mockQueryResult.Object);
//Invocation of "Name" check
IInvocationList invocations = mockNameAction.Invocations;
List <IInvocation> calledMethods = new List <IInvocation>(invocations);
List <string> calleMethodsNames = new List <string>(calledMethods.ConvertAll(current => current.Method.Name));
CollectionAssert.Contains(calleMethodsNames, "Invoke");
//Invocation of "Description" check
invocations = mockDescriptionAction.Invocations;
calledMethods = new List <IInvocation>(invocations);
calleMethodsNames = new List <string>(calledMethods.ConvertAll(current => current.Method.Name));
CollectionAssert.Contains(calleMethodsNames, "Invoke");
//Invocation of "Price" check
invocations = mockPriceAction.Invocations;
calledMethods = new List <IInvocation>(invocations);
calleMethodsNames = new List <string>(calledMethods.ConvertAll(current => current.Method.Name));
CollectionAssert.Contains(calleMethodsNames, "Invoke");
//If there is a call on a null action, it will produce a NullPointerException
}
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;
}
}
}
}
public MainWindow()
{
InitializeComponent();
// Options to present the formatted SQL query text to end-user
// Use names of virtual objects, do not replace them with appropriate derived tables
QBuilder.SQLFormattingOptions = new SQLFormattingOptions {
ExpandVirtualObjects = false
};
// Options to generate the SQL query text for execution against a database server
// Replace virtual objects with derived tables
QBuilder.SQLGenerationOptions = new SQLGenerationOptions {
ExpandVirtualObjects = true
};
Closing += MainWindow_Closing;
Dispatcher.Hooks.DispatcherInactive += Hooks_DispatcherInactive;
var currentLang = Thread.CurrentThread.CurrentUICulture.TwoLetterISOLanguageName;
LoadLanguage();
var defLang = "en";
if (Helpers.Localizer.Languages.Contains(currentLang.ToLower()))
{
Language = XmlLanguage.GetLanguage(currentLang);
defLang = currentLang.ToLower();
}
var menuItem = MenuItemLanguage.Items.Cast <MenuItem>().First(item => (string)item.Tag == defLang);
menuItem.IsChecked = true;
QBuilder.SyntaxProvider = new GenericSyntaxProvider();
_transformerSql = new QueryTransformer();
_timerStartingExecuteSql = new Timer(TimerStartingExecuteSql_Elapsed);
// DEMO WARNING
if (BuildInfo.GetEdition() == BuildInfo.Edition.Trial)
{
var trialNoticePanel = new Border
{
BorderBrush = Brushes.Black,
BorderThickness = new Thickness(1),
Background = Brushes.LightGreen,
Padding = new Thickness(5),
Margin = new Thickness(0, 0, 0, 2)
};
trialNoticePanel.SetValue(Grid.RowProperty, 1);
var label = new TextBlock
{
Text =
@"Generation of random aliases for the query output columns is the limitation of the trial version. The full version is free from this behavior.",
HorizontalAlignment = HorizontalAlignment.Left,
VerticalAlignment = VerticalAlignment.Top
};
var button = new Button
{
Background = Brushes.Transparent,
Padding = new Thickness(0),
BorderThickness = new Thickness(0),
Cursor = Cursors.Hand,
Margin = new Thickness(0, 0, 5, 0),
HorizontalAlignment = HorizontalAlignment.Right,
VerticalAlignment = VerticalAlignment.Center,
Content = new Image
{
Source = ActiveQueryBuilder.View.WPF.Helpers.GetImageSource(Properties.Resources.cancel,
ImageFormat.Png),
Stretch = Stretch.None
}
};
button.Click += delegate { GridRoot.Visibility = Visibility.Collapsed; };
trialNoticePanel.Child = label;
GridRoot.Children.Add(trialNoticePanel);
GridRoot.Children.Add(button);
}
QBuilder.SQLQuery.QueryRoot.AllowSleepMode = true;
QBuilder.SleepModeChanged += SqlQuery_SleepModeChanged;
QBuilder.QueryAwake += SqlQuery_QueryAwake;
}
private void Insert(QueryTransformer qt)
{
var sql = string.Format("insert into QueryTransformers values ('{0}', '{1}')", qt.Tag, qt.XML);
ExecuteNonQuery(sql);
}
private void Update(QueryTransformer qt)
{
var sql = string.Format("update QueryTransformers set state = '{1}' where id = '{0}'", qt.Tag, qt.XML);
ExecuteNonQuery(sql);
}
