bool ReadColValues(XmlReader reader, ResultTable table, bool addColumns) { int count = 0; var ss = reader.ReadElementContentAsArray(); if (ss != null) { for (int i = 0; i < ss.Length - 1; i += 2) { var key = ss[i]; var val = ss[i + 1]; if (addColumns) { if (base.ContainsColumn(key)) { table.ColumnSet.AddColumn(key, val); count++; } } else { if (table.ColumnSet.ContainsColumn(key)) { table.ColumnSet.SetValue(key, val); count++; } } } } return(count == table.ColumnSet.Count && count > 0); }
public void ParallelSelectWithSubquery() { Guid g = StopWatch.Start(); int loops = 80; SqlBuilder builder = SqlBuilder.Select() .From("Account") .AllColumns(false) .SubSelect("Contact", "AccountID", "AccountID", null) .AllColumns(false) .Builder(); Console.WriteLine(builder.ToSql()); ResultTable result = builder.Execute(); double one = StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds); int count = result.Count + result.SelectMany(x => x.Column <ResultTable>("ContactList")).Count(); g = StopWatch.Start(); int total = 0; ParallelLoopResult r = Parallel.For(0, loops, new ParallelOptions() { MaxDegreeOfParallelism = 8 }, a => { total += SelectWithSubqueryInternal(); }); double twenty = StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds); Console.WriteLine("{2} selects parallel executed with a total of {3} rows in {0:0.00}ms. Estimated {1:0.00}ms", twenty, one * loops, loops, loops * count); Assert.IsTrue(r.IsCompleted); }
public void SelectFunctionColumnsFromOneTable() { SqlBuilder builder = SqlBuilder.Select(20).From("Account", null).AllColumns(false) .Fn() .GetDate("Today") .Concat("My Name", ConstantField <string> .Constant("Michael"), ConstantField <string> .Constant(" "), ConstantField <string> .Constant("Randrup") ) .ToTable() .Column <string>(Guid.NewGuid().ToString(), "UniqueID") .Builder; Console.WriteLine(builder.ToSql()); Guid g = StopWatch.Start(); ResultTable result = builder.Execute(120); Console.WriteLine("ResulTable with {0} rows executed in {1}s", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Seconds)); g = StopWatch.Start(); List <Account> list = builder.List <Account>(null, 30, true, true); Console.WriteLine("List<Account> with {0} rows executed in {1}s", list.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Seconds)); list = null; result = null; }
public void ExecuteDeepParent() { Guid g = StopWatch.Start(); SqlBuilder builder = SqlBuilder.Select() .From("Contact") .AllColumns(false) .SubSelect("Account", "AccountID", "AccountID", null, null, "Account") .AllColumns(false) .ConcatColumns("Address", ", ", "Address1", "PostalCode", "City") .InnerJoin("Contact").On("AccountID", SqlOperators.Equal, "AccountID") .ToTable().Column("ContactID") .Builder(); Console.WriteLine(builder.ToSql()); ResultTable result = builder.Execute(); Console.WriteLine("ResulTable with {0} rows executed in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)); foreach (dynamic row in result.Where(x => x.Column <ResultTable>("Account").Count > 0).Take(50)) { dynamic Parent = row.Column <ResultTable>("Account"); if (Parent.Count > 0) { Console.WriteLine("The Contact {0} is connected to the account {1} - {2}", row.Name, Parent[0].Name, Parent[0].Address); } else { Console.WriteLine("The Contact {0} is not connected to an account", row.Name); } } }
private ResultTable ExecuteDeepInternal() { Guid g = StopWatch.Start(); SqlBuilder builder = SqlBuilder.Select() .From("Account") .AllColumns(false) .SubSelect("Contact", "AccountID", "AccountID", null, null, "Contacts") .AllColumns(false) .SubSelect("Activity", "ContactID", "ContactID", null, null, "Activities") .AllColumns(false) .InnerJoin("Checkkode").On("ActivityTypeID", SqlOperators.Equal, "CheckID") .And <decimal>("CheckGroup", SqlOperators.Equal, 5) .ToTable().Column("BeskrivelseDK", "ActivityType") .Builder.ParentBuilder.From("Contact") .SubSelect("CampaignActivity", "ContactID", "ContactID", null, null) .AllColumns(false) .InnerJoin("Checkkode").On("CampaignActivityTypeID", SqlOperators.Equal, "CheckID") .And <decimal>("CheckGroup", SqlOperators.Equal, 4) .ToTable().Column("BeskrivelseDK", "ActivityType") .Builder(); Console.WriteLine(builder.ToSql()); ResultTable result = builder.Execute(); Console.WriteLine("ResulTable with {0} rows executed in {1}ms", result.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)); return(result); }
public override void OnResultPublished(Guid stepRun, ResultTable result) { ResultPublishedEventArgs e = new ResultPublishedEventArgs(stepRun, result); RaiseResultPublished(e); OnActivity(); }
public void SetResults(ResultTable rt) { tb.Inlines.Clear(); switch (rt.Pagination.GetMode()) { case PaginationMode.All: tb.Inlines.Add(SearchMessage._0Results_N.NiceToString().ForGenderAndNumber(number: rt.Rows.Length).FormatSpan( new Run(rt.TotalElements.Value.ToString()) { FontWeight = FontWeights.Bold })); break; case PaginationMode.Firsts: var top = (Pagination.Firsts)rt.Pagination; var run = new Run(rt.Rows.Length.ToString()) { FontWeight = FontWeights.Bold }; if(rt.Rows.Length == top.TopElements) run.Foreground = Brushes.Red; tb.Inlines.Add(SearchMessage.First0Results_N.NiceToString().ForGenderAndNumber(number: rt.Rows.Length).FormatSpan(run)); break; case PaginationMode.Paginate: tb.Inlines.Add(SearchMessage._01of2Results_N.NiceToString().ForGenderAndNumber(number: rt.Rows.Length).FormatSpan( new Run(rt.StartElementIndex.Value.ToString()) { FontWeight = FontWeights.Bold }, new Run(rt.EndElementIndex.Value.ToString()) { FontWeight = FontWeights.Bold }, new Run(rt.TotalElements.Value.ToString()) { FontWeight = FontWeights.Bold })); break; default: break; } }
internal void MakeQuery() { List<QueryToken> tokens = new List<QueryToken>(); foreach (var root in document.RecursivePartsRootElements()) { foreach (var item in root.Descendants<BaseNode>()) { item.FillTokens(tokens); } } var columns = tokens.NotNull().Distinct().Select(qt => new Signum.Entities.DynamicQuery.Column(qt, null)).ToList(); var filters = systemWordTemplate != null ? systemWordTemplate.GetFilters(this.queryDescription) : new List<Filter> { new Filter(QueryUtils.Parse("Entity", this.queryDescription, 0), FilterOperation.EqualTo, this.entity.ToLite()) }; this.table = DynamicQueryManager.Current.ExecuteQuery(new QueryRequest { QueryName = this.queryDescription.QueryName, Columns = columns, Pagination = new Pagination.All(), Filters = filters, Orders = new List<Order>(), }); this.dicTokenColumn = table.Columns.ToDictionary(rc => rc.Column.Token); }
public void InsertOneAccountAsStoredProcedureText() { Guid g = StopWatch.Start(); SqlBuilder builder = GetInsertUpdateBuilder(); ResultTable result = builder.Execute(30, false); Assert.IsTrue(result.Count == 1, "The insert procedure did not return 1 row"); decimal ID = Convert.ToDecimal(builder.Procedure.Parameters.First(x => x.Name.Equals("retval")).Value); Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "One account inserted in {0}ms")); Assert.IsTrue(ID > 0, "The Account was not inserted"); Console.WriteLine(SerializationExtensions.ToJson <RowData>(result[0], true)); g = StopWatch.Start(); builder = GetInsertUpdateBuilder(ID, "Nørregade 28D"); result = builder.Execute(30, false); Assert.IsTrue(result.Count == 1, "The update procedure did not return 1 row"); decimal ID2 = Convert.ToDecimal(builder.Procedure.Parameters.First(x => x.Name.Equals("retval")).Value); Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "One account updated in {0}ms")); Assert.AreEqual <decimal>(ID, ID2, "The Insert/update IDs do not match {0} != {1}", ID, ID2); builder = SqlBuilder.Select() .From("Account").AllColumns(false) .Where <decimal>("Account", "AccountID", SqlOperators.Equal, ID2) .Builder(); result = builder.Execute(30, false); Assert.IsTrue(result.Count == 1, "The updated account {0} could not be retrieved", ID2); Console.WriteLine(SerializationExtensions.ToJson <RowData>(result[0], true)); DeleteOneAccount(ID2); }
public List <Event> GetEvents() { List <Event> events; using (new SPMonitoredScope("Boxing Events Search Retrieval")) { KeywordQuery query = new KeywordQuery(SPContext.Current.Site); query.SelectProperties.AddRange(new[] { "Title", "EventDate", "Path" }); query.QueryText = "ContentType:\"CT-Event\""; var searchExecutor = new SearchExecutor(); ResultTableCollection results = searchExecutor.ExecuteQuery(query); ResultTable resultTable = results.Filter("TableType", KnownTableTypes.RelevantResults).Single(); events = (from DataRow row in resultTable.ResultRows select new Event( (string)row["Title"], (DateTime)row["EventDate"], (string)row["Path"]) ).OrderByDescending(ev => ev.EventDate) .ThenBy(ev => ev.Title) .ToList(); } return(events); }
public string DeleteAttendanceRecord(string employeeName, string date) { List <string> records; ViewAttendanceRecord(employeeName, date, out records); IList <IWebElement> tableRows = ResultTable.FindElements(By.TagName("tr")); foreach (var row in tableRows) { if (row.Text.Contains(employeeName) & row.Text.Contains(date)) { IWebElement checkBox = row.FindElement(By.Name("chkSelectRow[]")); if (!checkBox.Selected) { checkBox.Click(); DeleteBtn.Click(); OkBtn.Click(); return("Success"); } } } return("No Record Found"); }
public static DataTable ExecuteQuery(string SiteCollection, string QueryString) { SPSite col = new SPSite(SiteCollection); ServerContext ctx = ServerContext.GetContext(col); FullTextSqlQuery ftq = new FullTextSqlQuery(ctx); ftq.ResultTypes = ResultType.RelevantResults; ftq.EnableStemming = true; ftq.TrimDuplicates = true; ftq.QueryText = QueryString; ResultTableCollection allresults = ftq.Execute(); ResultTable relevant = allresults[ResultType.RelevantResults]; DataTable dt = null; if (relevant.RowCount > 0) { dt = new DataTable("Search Result"); dt.Columns.Add("Title"); dt.Columns.Add("URL"); while (relevant.Read()) { DataRow row = dt.NewRow(); row[0] = relevant.GetString(0); row[1] = relevant.GetString(1); dt.Rows.Add(row); } } return(dt); }
/// <inheritdoc/> public override void RestoreState() { BandBase parent = Parent as BandBase; // SaveState was skipped, there is nothing to restore if (saveStateSkipped) { return; } if (!IsManualBuild) { base.RestoreState(); } else { if (parent != null) { parent.AfterPrint -= new EventHandler(ResultTable.GeneratePages); } helper = null; ResultTable.Dispose(); SetResultTable(null); Visible = saveVisible; } }
private static List <DocumentoSharePoint> OrdenarResultados(ResultTable resultTable) { List <DocumentoSharePoint> resultados = new List <DocumentoSharePoint>(); List <string> primerOrden = new List <string>(); DocumentoSharePoint dsp; while (resultTable.Read()) { dsp = new DocumentoSharePoint(); DateTime fechaBusqueda; bool esFecha = DateTime.TryParse(resultTable["Fecha"].ToString(), out fechaBusqueda); if (esFecha) { dsp.Fecha = fechaBusqueda; } dsp.Path = resultTable["Path"].ToString(); dsp.Titulo = resultTable["Title"].ToString(); resultados.Add(dsp); } if (desc) { primerOrden = resultados.OrderBy(t => t.Path).Select(p => p.Path).ToList(); } else { primerOrden = resultados.Where(t => t.Path.Split('/').Last().StartsWith("20110807")).OrderByDescending(t => t.Path).Select(p => p.Path).ToList().ToList(); var segundoOrden = resultados.Where(t => !t.Path.Split('/').Last().StartsWith("20110807")).OrderBy(t => t).Select(p => p.Path).ToList(); primerOrden.AddRange(segundoOrden); } return(resultados.OrderBy(f => f.Fecha).ToList()); }
public bool ReadRow(XmlReader reader, ResultTable table) { if (table.RowCount > 0) { if (reader.ReadToDescendant("moid") && ReadColValues(reader, table, false)) { } else { return(false); } } int xmlIndex = 0; int count = 0; while (reader.ReadElement("r")) { var s = reader.ReadElementContentAsString(); if (table.ColumnSet2.SetValue(xmlIndex, s)) { count++; } xmlIndex++; } if (count == table.ColumnSet2.Count) { return(true); } return(false); }
public ActionResult AddResults(IFormCollection result) { Result res = new Result(); res.TestId = Convert.ToInt32(result["TestId"]); IList <string> names; names = result["Name"]; IList <string> units; units = result["Unit"]; IList <string> values; values = result["Value"]; for (int i = 0; i < names.Count; i++) { if (names[i] == "" || names[i] == null || names[i] == "Select name") { break; } res.Name = names[i]; res.Unit = units[i]; res.Value = Convert.ToDouble(values[i]); ResultTable.InsertData(res); } return(View()); }
private void LinqPadExtensionsTabled(ResultTable <object> obj) { if (obj != null) { this.DataGridViewModel.AddTable(obj); } }
ResultTable ReadSearchResultTable(HtmlElement body) { HtmlElement t = body.Find(x => x.TagName == "TABLE" && x.AttributeIs("className", "searchResultTable")); ResultTable data = new ResultTable(); foreach (HtmlElement tr in t.GetElementsByTagName("TR")) { Dictionary <string, string> row = new Dictionary <string, string>(); row["OwnAcctId"] = CreditCard; foreach (HtmlElement td in tr.GetElementsByTagName("TD")) { string[] idp = td.GetAttribute("headers").Split(new char[] { ':' }); if (idp.Length > 1) { string id = idp[1]; if (!String.IsNullOrEmpty(id)) { row[id] = td.InnerText; } } } data.Add(row); } return(data); }
public void RefreshCurrentPageDataTable() { using (ComReleaser comreleaser = new ComReleaser()) { IFeatureCursor cursor = TargetFeatureClass.Search(null, false); comreleaser.ManageLifetime(cursor); IFeature tempFt = cursor.NextFeature(); comreleaser.ManageLifetime(tempFt); ISet <int> IngoreFieldIndex = ConvertIngoreColName2FieldIndex(); for (int index = 0; index < FeatureCount; index++) { if (index < CurrentPage * PageContains) { tempFt = cursor.NextFeature(); } else if (index > (CurrentPage + 1) * PageContains) { break; } else { int colIndex = 0; DataRow tempRow = ResultTable.NewRow(); for (int i = 0; i < TargetFeatureClass.Fields.FieldCount; i++) { if (IngoreFieldIndex.Contains(i)) { continue; } tempRow[colIndex++] = tempFt.get_Value(index); } } } } }
protected override void ProcessRecord() { base.ProcessRecord(); try { KeywordQuery query = new KeywordQuery(this.Site.Read()); query.QueryText = this.Query; query.SelectProperties.AddRange(this.Select); SearchExecutor executor = new SearchExecutor(); ResultTableCollection result = executor.ExecuteQuery(query); ResultTable resultTable = result.Filter("TableType", KnownTableTypes.RelevantResults).FirstOrDefault(); if (resultTable == null) { throw new Exception("Search executor did not return result table of type RelevantResults"); } DataTable dataTable = new DataTable(); dataTable.Load(resultTable); foreach (DataRow row in dataTable.Rows) { PSObject obj = new PSObject(); foreach (DataColumn column in dataTable.Columns) { obj.Members.Add(new PSNoteProperty(column.Caption, row[column])); } WriteObject(obj); } } catch (Exception ex) { ThrowTerminatingError(ex, ErrorCategory.NotSpecified); } }
public void CreateBackup() { ResultTable table = Mysql.Query("SELECT * FROM `shaiya_mob_db`"); if (table.Rows.Count == 0) { return; } string savePath = AppDomain.CurrentDomain.BaseDirectory + "Backup\\MobDB_" + DateTime.Now.ToString().Replace('.', '_').Replace(':', '_') + ".sql"; System.IO.Directory.CreateDirectory(AppDomain.CurrentDomain.BaseDirectory + "Backup\\"); System.IO.FileInfo info = new System.IO.FileInfo(savePath); using (System.IO.TextWriter writer = info.CreateText()) { writer.WriteLine("TRUNCATE TABLE `shaiya_mob_db`;"); writer.WriteLine("INSERT INTO `shaiya_mob_db` ( `id`, `pos_x`, `pos_y`, `name`, `mapname`, `level`, `anzahl`, `element`, `boss`, `info` ) VALUES"); for (int i = 0; i < table.Rows.Count; i++) { ResultRow row = table.Rows[i]; writer.Write("( " + row["id"].GetInt() + ", " + row["pos_x"].GetInt() + ", " + row["pos_y"].GetInt() + ", '" + row["name"].GetString() + "', '" + row["mapname"].GetString() + "', '" + row["level"].GetString() + "', '" + row["anzahl"].GetString() + "', '" + row["element"].GetString() + "', " + row["boss"].GetByte() + ", '" + row["info"].GetString() + "' )"); if (i < table.Rows.Count - 1) { writer.WriteLine(","); } } writer.WriteLine(";"); } }
public ContentResult Update(string rowData) { RowData row = SerializationExtensions.FromJson <RowData>(rowData); row.LoadMetadata(); row.Column("Name", "Random " + Guid.NewGuid().ToString()); SqlBuilder builder = row.Update(false, true); ResultTable result = builder.Execute(30, false); if (result.Count == 1) { builder = SqlBuilder.Select() .From("Contact") .Column("ContactID") .Column("Name") .Column("Telephone") .Column("WorkEmail") .Column("ModifiedOn") .WithMetadata().InnerJoin("AccountID") .Column("Name", "AccountName") .From("Contact") .Where <decimal>("Contact", "ContactID", SqlOperators.Equal, result.First().Column <decimal>("ContactID")) .Builder(); result = builder.Execute(30, false, ResultTable.DateHandlingEnum.ConvertToDate); row = result.First(); return(Content(SerializationExtensions.ToJson <dynamic>(row), "application/json")); } return(Content("Hmmmm...?", "application/text")); }
/// <summary> /// Create the result table of all possible changes for the given coinInventory. /// </summary> /// <param name="coinInventory">The inventory of all the coins to make the change.</param> /// <param name="change">The given change required.</param> /// <returns>Return <c>ResultTable</c> if result is found else null.</returns> /// private ResultTable CreateResultTable(IList <int> orderedDenomination, IReadOnlyDictionary <int, int> coinInventory, int change, int smallestDenomination = 0) { ResultTable result = new ResultTable(orderedDenomination.Count, change); for (int i = 0; i < orderedDenomination.Count; i++) { var availableCoin = coinInventory[orderedDenomination[i]]; for (int j = change; j >= smallestDenomination; j--) { if (result.HasResult(j)) { continue; } int k = 1; // number of current coin type needed to make up the change (j) int kDenominationTotal = orderedDenomination[i]; while (k <= availableCoin && j - kDenominationTotal >= 0) { // it is possible to make up the change (j) using current denomination + previous results if (result.HasResult(j - kDenominationTotal)) { result[i, j] = k; if (j == change) { return(result); } break; } kDenominationTotal = ++k * orderedDenomination[i]; } } } return(null); }
/// <summary> /// Executes a keyword query against Office search service. /// </summary> /// <param name="keywordQuery">A keyword query instance.</param> /// <param name="refiners">A list of <see cref="SearchRefiner"/> objects where refinement results are populated.</param> /// <returns>Results returned from Office search service.</returns> public static ResultTable ExecuteQuery(KeywordQuery keywordQuery, SearchRefiner[] refiners) { CommonHelper.ConfirmNotNull(keywordQuery, "keywordQuery"); LastQueryText = keywordQuery.QueryText; if (refiners != null) { keywordQuery.Refiners = String.Join(",", refiners.Select(v => v.PropertyName).ToArray()); keywordQuery.RefinementFilters.AddRange(refiners.Where(v => v.RefinementToken != null).Select(v => v.RefinementToken).ToArray()); } SearchExecutor executor = new SearchExecutor(); ResultTableCollection queryResults = executor.ExecuteQuery(keywordQuery); ResultTable relevantResults = queryResults.Filter("TableType", KnownTableTypes.RelevantResults).FirstOrDefault(); if (relevantResults == null) { throw new Exception("Search executor did not return result table of type RelevantResults"); } if (refiners != null) { ResultTable refinementResults = queryResults.Filter("TableType", KnownTableTypes.RefinementResults).FirstOrDefault(); if (refinementResults == null) { throw new Exception("Search executor did not return result table of type RefinementResults"); } foreach (SearchRefiner refiner in refiners) { foreach (DataRow row in refinementResults.Table.Rows.OfType <DataRow>().Where(v => refiner.PropertyName.Equals(v["RefinerName"]))) { refiner.AddRefinement((string)row["RefinementName"], (string)row["RefinementToken"], (int)row["RefinementCount"]); } } } return(relevantResults); }
public void TestResultTableLoad() { var table = new ResultTable(); var selectSql = @"SELECT * FROM TT ;"; using (var cmd = new MySqlCommand(selectSql, dbConn)) { using (var dbReader = cmd.ExecuteReader()) { table.Load(dbReader); } } Assert.Equal("a", table.Columns[0].ColumnName); Assert.Equal("b", table.Columns[1].ColumnName); Assert.Equal("c", table.Columns[2].ColumnName); Assert.Equal(typeof(int), table.Columns[0].DataType); Assert.Equal(typeof(string), table.Columns[1].DataType); Assert.Equal(typeof(double), table.Columns[2].DataType); Assert.Equal(100, table.Columns[1].MaxLength); Assert.Equal("data", table.Rows[2][1]); Assert.Equal(4, table.Rows.Count); }
public static QvxTable ConvertTable(ResultTable table) { var resultTable = new QvxTable() { TableName = table.Name }; var fields = new List <QvxField>(); foreach (var header in table.Headers) { fields.Add(new QvxField(header.Name, QvxFieldType.QVX_TEXT, QvxNullRepresentation.QVX_NULL_FLAG_SUPPRESS_DATA, FieldAttrType.ASCII)); } var rows = new List <QvxDataRow>(); QvxDataRow newRow = null; foreach (var row in table.Rows) { if (row.IsFirstRow) { newRow = new QvxDataRow(); } var field = fields.FirstOrDefault(f => f.FieldName == row.Header); newRow[field] = row.Value; if (row.IsLastRow) { rows.Add(newRow); } } resultTable.Fields = fields.ToArray(); resultTable.GetRows = () => { return(rows); }; return(resultTable); }
public void AutoUpdateFromRowDataObject() { Guid g = StopWatch.Start(); SqlBuilder builder = SqlBuilder.Select().WithMetadata(true, SetupData.MetadataFileName) .From("Account") .AllColumns(false) .Where <decimal>("Account", "AccountID", SqlOperators.Equal, 526) .Builder; Console.WriteLine(builder.ToSql()); ResultTable r = builder.Execute(); Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "1 Account selected in {0}ms")); g = StopWatch.Start(); Assert.IsTrue(r.Count == 1, "Executed 1 account"); RowData row = r.First(); row.Column("Name", Guid.NewGuid().ToString()); builder = SqlBuilder.Update().Update(row, new string[] { "AccountID", "Name" }); Console.WriteLine(builder.ToSql()); r = builder.Execute(); Console.WriteLine(StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds, "1 Account updated in {0}ms")); row.AcceptChanges(); Assert.IsTrue(r.First().Column <string>("Name") == row.Column <string>("Name"), "Names are equal"); Assert.IsFalse(row.HasChanges, "The row does not have changes"); }
private void PrintTitle() { TableCell templateCell = titleDescriptor.TemplateCell; if (titleDescriptor.TemplateCell == null) { templateCell = CreateCell(Res.Get("ComponentsMisc,Matrix,Title")); } TableCellData resultCell = ResultTable.GetCellData(HeaderWidth, 0); templateCell.SaveState(); templateCell.GetData(); resultCell.RunTimeAssign(templateCell, true); resultCell.ColSpan = ResultTable.ColumnCount - HeaderWidth; templateCell.RestoreState(); // print left-top cell if (titleDescriptor.TemplateCell == null) { templateCell.Text = ""; } else { templateCell = Matrix[0, 0]; } resultCell = ResultTable.GetCellData(0, 0); templateCell.SaveState(); templateCell.GetData(); resultCell.RunTimeAssign(templateCell, true); templateCell.RestoreState(); resultCell.ColSpan = HeaderWidth; }
private void dashboardViewer1_DataLoading(object sender, DataLoadingEventArgs e) { if (e.DataSourceName.StartsWith("ods|")) { string[] names = e.DataSourceName.Split("|".ToCharArray()); DashboardSqlDataSource dataSource = dataSources.First(ds => ds.ComponentName == names[1]); SqlQuery query = dataSource.Queries.First(q => q.Name == names[2]); XElement dsXML = dataSource.SaveToXml(); SqlDataSource sqlDS = new SqlDataSource(); sqlDS.LoadFromXml(dsXML); sqlDS.ConnectionName = "Connection"; sqlDS.Fill(query.Name); ResultSet rSet = ((IListSource)sqlDS).GetList() as ResultSet; ResultTable rTable = rSet.Tables.First(t => t.TableName == query.Name); if (query.Name == "Invoices") { var dt = ConvertResultTableToDataTable(rTable); for (int i = dt.Rows.Count - 1; i >= 0; i--) { if (((DateTime)dt.Rows[i]["OrderDate"]).Year < 2016) { dt.Rows.RemoveAt(i); } } e.Data = dt; } else { e.Data = rTable; } } }
public void SelectPeopleInMarketingListsWithoutEmail() { List <string> domains = new List <string>() { "@hotmail", "@yahoo", "@live", "@msn", "@outlook", "@mail.tele", "@gmail", "@post" }; Guid g = StopWatch.Start(); SqlBuilder builder = SqlBuilder.Select() .From("Contact") .AllColumns(false) .WhereExists("ListMember").And("ContactID", SqlOperators.Equal, "ContactID") .EndExists() .And <List <string> >("Contact", "WorkEmail", SqlOperators.In, domains) .Builder(); Console.WriteLine(builder.ToSql()); ResultTable results = builder.Execute(); Console.WriteLine("{0} contacts executed in {1}ms, that are listmembers and have a public domain email address", results.Count, StopWatch.Stop(g, StopWatch.WatchTypes.Milliseconds)); }
public void TestTableToReader() { var table = new ResultTable(); table.Columns.Add(new ColumnRef("a"), aColDef); table.Columns.Add(new ColumnRef("b"), bColDef); table.Columns.Add(new ColumnRef("c"), cColDef); for (var i = 0; i < 5; i++) { var row = table.NewRow(); row.Add(new object[] { (i + 1) * 1, (i + 1) * 2, (i + 1) * 3 }); table.Rows.Add(row); } var results = new List <int[]>(); using (var reader = new ResultReader(table)) { while (reader.Read()) { var row = new int[3]; row[0] = (int)reader[new ColumnRef("a")]; row[1] = (int)reader[bColDef]; row[2] = (int)reader["c"]; results.Add(row); } } Assert.Equal(3, results[2][0]); Assert.Equal(6, results[2][1]); Assert.Equal(9, results[2][2]); Assert.Equal(5, results.Count); }
public JsonResult NewChart() { List <object> data = new List <object>(); DataTable dt = new DataTable(); dt.Columns.Add("Value", System.Type.GetType("System.Double")); dt.Columns.Add("Data", System.Type.GetType("System.String")); DataRow dr; var dataTest = TestTable.GetDataByPatientId(Properties.UserId); foreach (var test in dataTest) { var dataResult = ResultTable.GetDataByTestIdAndName(test.Id, nameOfTest); if (dataResult.Count != 0) { for (int i = 0; i < dataResult.Count(); i++) { dr = dt.NewRow(); dr["Value"] = dataResult[i].Value; dr["Data"] = test.Date.ToString("dd.MM.yyyy"); dt.Rows.Add(dr); } } } foreach (DataColumn dc in dt.Columns) { List <object> x = new List <object>(); x = (from DataRow drr in dt.Rows select drr[dc.ColumnName]).ToList(); data.Add(x); } return(Json(data)); }
public static void WriteDataInExcelFile(ResultTable results, Stream stream) { if (results == null) throw new ApplicationException(ExcelMessage.ThereAreNoResultsToWrite.NiceToString()); using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true)) { document.PackageProperties.Creator = ""; document.PackageProperties.LastModifiedBy = ""; WorkbookPart workbookPart = document.WorkbookPart; WorksheetPart worksheetPart = document.GetWorksheetPartByName(ExcelMessage.Data.NiceToString()); CellBuilder cb = PlainExcelGenerator.CellBuilder; SheetData sheetData = worksheetPart.Worksheet.Descendants<SheetData>().SingleEx(); List<ColumnData> columnEquivalences = GetColumnsEquivalences(document, sheetData, results); UInt32Value headerStyleIndex = worksheetPart.Worksheet.FindCell("A1").StyleIndex; //Clear sheetData from the template sample data sheetData.InnerXml = ""; sheetData.Append(new Sequence<Row>() { (from columnData in columnEquivalences select cb.Cell(columnData.Column.Column.DisplayName, headerStyleIndex)).ToRow(), from r in results.Rows select (from columnData in columnEquivalences select cb.Cell(r[columnData.Column], cb.GetTemplateCell(columnData.Column.Column.Type), columnData.StyleIndex)).ToRow() }.Cast<OpenXmlElement>()); var pivotTableParts = workbookPart.PivotTableCacheDefinitionParts .Where(ptpart => ptpart.PivotCacheDefinition.Descendants<WorksheetSource>() .Any(wss => wss.Sheet.Value == ExcelMessage.Data.NiceToString())); foreach (PivotTableCacheDefinitionPart ptpart in pivotTableParts) { PivotCacheDefinition pcd = ptpart.PivotCacheDefinition; WorksheetSource wss = pcd.Descendants<WorksheetSource>().FirstEx(); wss.Reference.Value = "A1:" + GetExcelColumn(columnEquivalences.Count(ce => !ce.IsNew) - 1) + (results.Rows.Count() + 1).ToString(); pcd.RefreshOnLoad = true; pcd.SaveData = false; pcd.Save(); } workbookPart.Workbook.Save(); document.Close(); } }
public static byte[] WriteDataInExcelFile(ResultTable queryResult, byte[] template) { using (MemoryStream ms = new MemoryStream()) { ms.WriteAllBytes(template); ms.Seek(0, SeekOrigin.Begin); ExcelGenerator.WriteDataInExcelFile(queryResult, ms); return ms.ToArray(); } }
public static bool SendQuery( string Query, out ResultTable Result ) { try { Result = Mysql.Query( Query ); mLastException = null; return true; } catch( Exception e ) { System.Diagnostics.Debug.WriteLine( e ); Result = null; mLastException = e; return false; } }
public void SelectAndSerializeDataRows() { SqlBuilder builder = SqlBuilder.Select(100) .From("Account") .AllColumns(false) //.WhereNotExists("Contact").And("AccountID", SqlOperators.Equal, "AccountID") //.EndExists().Builder.BaseTable() .SubSelect("Contact","AccountID","AccountID","c") .AllColumns(false) .Builder(); Console.WriteLine(builder.ToSql()); ResultTable result = builder.Execute(); string prefix = DateTime.Now.Ticks.ToString(); string path = Path.GetTempPath(); for (int i = 0; i < result.Count; i++) { SerializationExtensions.ToFile<RowData>(result[i], Path.Combine(path, string.Format("Row{0}@{1}.json", i + 1,prefix))); } ResultTable result2 = new ResultTable(); for (int i = 0; i < result.Count; i++) { result2.Add(SerializationExtensions.FromFile<RowData>(Path.Combine(path, string.Format("Row{0}@{1}.json", i + 1, prefix)))); } Assert.IsTrue(result.Count == result2.Count, "Number of rows does not match"); result = new ResultTable(builder, 60, false); string prefix2 = DateTime.Now.Ticks.ToString(); for (int i = 0; i < result.Count; i++) { SerializationExtensions.ToFile<RowData>(result[i], Path.Combine(path, string.Format("Row{0}@{1}.json", i + 1, prefix2))); } string[] files = Directory.EnumerateFiles(path, "Row*.json").ToArray(); foreach (string file in files) { File.Delete(file); } }
/// <summary> /// Executes a Query and returns the first int32 Value /// </summary> /// <param name="Query"></param> public int QueryCount( string Query ) { DataTable result = new DataTable(); mLastError = null; if( Query.Length == 0 ) return 0; Open(); mAdapter = new MySqlDataAdapter(); mAdapter.SelectCommand = new MySqlCommand( Query, mConnection ); try { mAdapter.Fill( result ); } catch( Exception ex ) { System.Diagnostics.Debug.WriteLine( ex ); mLastError = ex; return 0; } ResultTable table = new ResultTable( result ); if( table.Rows.Count == 0 ) return 0; return table.Rows[ 0 ][ 0 ].GetInt32(); }
//Manual Json printer for performance and pretty print public static object DataJson(ChartRequest request, ResultTable resultTable) { int index = 0; var cols = request.Columns.Select((c, i) => new { name = "c" + i, displayName = request.Columns[i].ScriptColumn.DisplayName, title = c.GetTitle(), token = c.Token == null ? null : c.Token.Token.FullKey(), type = c.Token == null ? null : c.Token.Token.GetChartColumnType().ToString(), isGroupKey = c.IsGroupKey, converter = c.Token == null ? null : c.Converter(index++) }).ToList(); if (!request.GroupResults) { cols.Insert(0, new { name = "entity", displayName = "Entity", title = "", token = ChartColumnType.Lite.ToString(), type = "entity", isGroupKey = (bool?)true, converter = new Func<ResultRow, object>(r => r.Entity.Key()) }); } var parameters = request.Parameters.ToDictionary(p=> p.Name, p => p.Value); return new { columns = cols.ToDictionary(a => a.name, a => new { a.title, a.displayName, a.token, a.isGroupKey, a.type, }), parameters = request.ChartScript.Parameters.ToDictionary(a => a.Name, a => parameters.TryGetC(a.Name) ?? a.DefaultValue(a.GetToken(request))), rows = resultTable.Rows.Select(r => cols.ToDictionary(a => a.name, a => a.converter == null ? null : a.converter(r))).ToList() }; }
void ExecuteQuery() { using (ExecutionMode.Global()) { List<QueryToken> tokens = new List<QueryToken>(); if (template.From != null && template.From.Token != null) tokens.Add(template.From.Token.Token); foreach (var tr in template.Recipients.Where(r => r.Token != null)) tokens.Add(tr.Token.Token); foreach (var t in template.Messages) { TextNode(t).FillQueryTokens(tokens); SubjectNode(t).FillQueryTokens(tokens); } var columns = tokens.Distinct().Select(qt => new Column(qt, null)).ToList(); var filters = systemEmail != null ? systemEmail.GetFilters(qd) : new List<Filter> { new Filter(QueryUtils.Parse("Entity", qd, 0), FilterOperation.EqualTo, entity.ToLite()) }; this.table = DynamicQueryManager.Current.ExecuteQuery(new QueryRequest { QueryName = queryName, Columns = columns, Pagination = new Pagination.All(), Filters = filters, Orders = new List<Order>(), }); this.dicTokenColumn = table.Columns.ToDictionary(rc => rc.Column.Token); this.currentRows = table.Rows; } }
public void Search(bool resetPage = true) { if (IsSearching) { searchQueuedResetPage = resetPage; return; } ClearResults(); IsSearching = true; var pag = Pagination as Pagination.Paginate; if (resetPage && pag != null && pag.CurrentPage != 1) { try { avoidPaginationChange = true; Pagination = new Pagination.Paginate(pag.ElementsPerPage, 1); } finally { avoidPaginationChange = false; } } QueryRequest request = UpdateMultiplyMessage(true); request.QueryBatch(rt => { hasBeenLoaded = true; resultTable = rt; if (rt != null) { SetResults(rt); } }, () => { IsSearching = false; if (generateListViewColumnsQueued) { generateListViewColumnsQueued = false; GenerateListViewColumns(); } if (searchQueuedResetPage != null) { var c = searchQueuedResetPage.Value; searchQueuedResetPage = null; Search(c); } }); }
private void SetResults(ResultTable rt) { try { avoidPaginationChange = true; gvResults.Columns.ZipForeach(rt.Columns, (gvc, rc) => { var header = (SortGridViewColumnHeader)gvc.Header; if (!rc.Column.Token.Equals(header.RequestColumn.Token)) throw new InvalidOperationException("The token in the ResultColumn ({0}) does not match with the token in the GridView ({1})" .FormatWith(rc.Column.Token.FullKey(), header.RequestColumn.Token.FullKey())); if (header.ResultColumn == null || header.ResultColumn.Index != rc.Index) gvc.CellTemplate = CreateDataTemplate(rc); header.ResultColumn = rc; }); lvResult.ItemsSource = rt.Rows; foreach (GridViewColumn column in gvResults.Columns) { if (double.IsNaN(column.Width)) column.Width = column.ActualWidth; column.Width = double.NaN; } if (rt.Rows.Length > 0) { lvResult.SelectedIndex = 0; lvResult.ScrollIntoView(rt.Rows.FirstEx()); } ItemsCount = lvResult.Items.Count; lvResult.Background = Brushes.White; lvResult.Focus(); paginationSelector.elementsInPageLabel.Visibility = Visibility.Visible; paginationSelector.elementsInPageLabel.SetResults(rt); paginationSelector.Visibility = System.Windows.Visibility.Visible; paginationSelector.TotalPages = rt.TotalPages; //tbResultados.Visibility = Visibility.Visible; //tbResultados.Foreground = rt.Rows.Length == ElementsPerPage ? Brushes.Red : Brushes.Black; OnQueryResultChanged(false); } finally { avoidPaginationChange = false; } }
public void ClearResults() { OnQueryResultChanged(true); resultTable = null; paginationSelector.elementsInPageLabel.Visibility = Visibility.Hidden; paginationSelector.TotalPages = null; lvResult.ItemsSource = null; lvResult.Background = Brushes.WhiteSmoke; }
public static void WriteDataInExcelFile(ResultTable results, string fileName) { using (FileStream fs = File.Open(fileName, FileMode.Open, FileAccess.ReadWrite)) WriteDataInExcelFile(results, fs); }
private static List<ColumnData> GetColumnsEquivalences(this SpreadsheetDocument document, SheetData sheetData, ResultTable results) { var resultsCols = results.Columns.ToDictionary(c => c.Column.DisplayName); var headerCells = sheetData.Descendants<Row>().FirstEx().Descendants<Cell>().ToList(); var templateCols = headerCells.ToDictionary(c => document.GetCellValue(c)); var rowDataCellTemplates = sheetData.Descendants<Row>() .FirstEx(r => IsValidRowDataTemplate(r, headerCells)) .Descendants<Cell>().ToList(); var dic = templateCols.OuterJoinDictionaryCC(resultsCols, (name, cell, resultCol) => { if (resultCol == null) throw new ApplicationException(ExcelMessage.TheExcelTemplateHasAColumn0NotPresentInTheFindWindow.NiceToString().FormatWith(name)); if (cell != null) { return new ColumnData { IsNew = false, StyleIndex = rowDataCellTemplates[headerCells.IndexOf(cell)].StyleIndex, Column = resultCol, }; } else { CellBuilder cb = PlainExcelGenerator.CellBuilder; return new ColumnData { IsNew = true, StyleIndex = 0, Column = resultCol, }; } }); return dic.Values.ToList(); }