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