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); }