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);
}
// Generates result row.
public void FormResultRow(string compare)
{
ResultRow row = new ResultRow();
row.Number = stepCount;
row.SetStack(stack);
row.Symbol = compare;
row.SetInputChain(inputChain);
ResultTable.Add(row);
}
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);
}
}
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);
}
}
private static ResultTable ExecuteRelatedInternal(SqlBuilder builder, Dictionary <string, RowData> results)
{
if (results.Count > 0)
{
MetadataTable mt = builder.BaseTable().WithMetadata().Model;
foreach (string key in results.Keys)
{
foreach (MetadataForeignKey fk in mt.ForeignKeys.Values.Where(x => (x.ReferencedSchema + "." + x.ReferencedTable).Equals(key, StringComparison.OrdinalIgnoreCase)))
{
RowData row = results[key];
foreach (MetadataColumnReference mcr in fk.ColumnReferences)
{
if (row.Columns.Contains(mcr.Column.Name))
{
Field f = builder.BaseTable().FindField(mcr.Column.Name);
if (f != null)
{
f.Value = row.Column(mcr.Column.Name);
}
else
{
(builder.BaseTable() as InsertIntoTable).Value(mcr.Column.Name, row.Column(mcr.Column.Name), SqlDbType.VarChar);
}
}
}
}
}
}
DataTable dt = new DataTable();
ResultTable table = new ResultTable();
using (SqlConnection context = new SqlConnection(builder.ConnectionString))
{
context.Open();
SqlCommand cmd = new SqlCommand(builder.ToSql(), context);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.AcceptChangesDuringFill = false;
adapter.Fill(dt);
context.Close();
}
if (builder.SubQueries.Count > 0)
{
Dictionary <string, RowData> subresults = new System.Collections.Generic.Dictionary <string, RowData>(results);
if (dt.Rows.Count > 0)
{
MetadataTable mt = builder.BaseTable().WithMetadata().Model;
if (!subresults.ContainsKey(mt.Fullname))
{
ResultTable rt = new ResultTable(dt, ResultTable.DateHandlingEnum.None);
RowData row = rt.First();
table.Add(row);
subresults.Add(mt.Fullname, row);
}
}
foreach (SqlBuilder Builder in builder.SubQueries.Values)
{
ResultTable sub = ExecuteRelatedInternal(Builder, subresults);
foreach (RowData row in sub)
{
table.Add(row);
}
}
}
return(table);
}
public static ResultTable Execute(this SqlBuilder[] Builders, int TimeoutSeconds = 30)
{
ResultTable table = new ResultTable();
using (TransactionScope trans = new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions()
{
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
Timeout = TimeSpan.FromSeconds(TimeoutSeconds)
}))
{
try
{
foreach (SqlBuilder builder in Builders)
{
DataTable dt = new DataTable();
using (SqlConnection context = new SqlConnection(builder.ConnectionString))
{
context.Open();
SqlCommand cmd = new SqlCommand(builder.ToSql(), context);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.AcceptChangesDuringFill = false;
adapter.Fill(dt);
context.Close();
}
if (builder.SubQueries.Count > 0)
{
Dictionary <string, RowData> results = new Dictionary <string, RowData>();
if (dt.Rows.Count > 0)
{
MetadataTable mt = builder.BaseTable().WithMetadata().Model;
if (!results.ContainsKey(mt.Fullname))
{
ResultTable rt = new ResultTable(dt, ResultTable.DateHandlingEnum.None);
RowData row = rt.First();
results.Add(mt.Fullname, row);
table.Add(row);
}
}
foreach (SqlBuilder Builder in builder.SubQueries.Values)
{
ResultTable sub = ExecuteRelatedInternal(Builder, results);
foreach (RowData row in sub)
{
table.Add(row);
}
}
}
}
}
catch (TransactionException exTrans)
{
trans.Dispose();
throw exTrans;
}
catch (SqlException exSql)
{
trans.Dispose();
throw exSql;
}
catch (ApplicationException exApplication)
{
trans.Dispose();
throw exApplication;
}
trans.Complete();
}
return(table);
}
