private static async Task InsertCustomers(IScenarioContext context)
{
var userTable = AllTables.GetItUser();
var customerTable = AllTables.GetItCustomer();
await InsertInto(customerTable, customerTable.UserId)
.From(Select(userTable.UserId)
.From(userTable)
.Where(!Exists(SelectOne()
.From(customerTable)
.Where(customerTable.UserId == userTable.UserId))))
.Exec(context.Database);
context.WriteLine("Customers inserted:");
var clCount = CustomColumnFactory.Int64("Count");
var res = await SelectDistinct(customerTable.CustomerId, userTable.UserId, Cast(CountOneOver(), SqlType.Int64).As(clCount))
.From(customerTable)
.InnerJoin(userTable, @on: customerTable.UserId == userTable.UserId)
.OrderBy(userTable.UserId)
.OffsetFetch(0, 5)
.QueryList(context.Database, r => (UserId: userTable.UserId.Read(r), CustomerId: customerTable.CustomerId.Read(r), Count: clCount.Read(r)));
foreach (var tuple in res)
{
Console.WriteLine(tuple);
}
}
protected override IExprSubQuery CreateQuery()
{
var tUser = AllTables.GetItUser();
var tCompany = AllTables.GetItCompany();
return(Select(
this._tCustomer.CustomerId,
Case()
.When(IsNotNull(tUser.UserId))
.Then((short)CustomerType.User)
.When(IsNotNull(tCompany.CompanyId))
.Then((short)CustomerType.Company)
.Else(Null)
.As(this.CustomerTypeId),
Case()
.When(IsNotNull(tUser.UserId))
.Then(tUser.FirstName + " " + tUser.LastName)
.When(IsNotNull(tCompany.CompanyId))
.Then(tCompany.CompanyName)
.Else("-")
.As(this.Name)
)
.From(this._tCustomer)
.LeftJoin(tUser, on: this._tCustomer.UserId == tUser.UserId)
.LeftJoin(tCompany, on: this._tCustomer.CompanyId == tCompany.CompanyId)
.Done());
}
//[OutputCache(Duration=3600,VaryByHeader="X-Requsted-With",Location=OutputCacheLocation.Server)]
public ActionResult Index()
{
//get map location serial and pot it in viewbag for contact section
string maplocation = (from a in db.C_Section_Content
where a.C_Section_Table.C_Section_Name == "Contact"
select a.C_Url_4).First();
if (maplocation != null)
{
ViewBag.maplocation = maplocation;
}
else
{
ViewBag.maplocation = "";
}
//alltables model has the sections table and section content table join the images table
AllTables AllTables = new AllTables();
try{
AllTables.C_Section_Table = (from o in db.C_Section_Table select o).ToList();
AllTables.C_Section_Content = (from o in db.C_Section_Content.Include(r => r.C_Images) select o).ToList();
}
catch (Exception ex)
{
ViewBag.error = ex.Message;
return(View("error"));
}
return(View(AllTables));
}
/// <summary>
/// 加载注释信息.
/// </summary>
/// <param name="filePath">注释信息文件.</param>
public void LoadComments(string filePath)
{
if (!(File.Exists(filePath)))
{
return;
}
XDocument xDoc = XDocument.Load(filePath);
List <XElement> entitys = xDoc.Root.Elements("EntityClass").ToList();
List <XElement> members;
TableInfoModel tableInfo;
foreach (XElement elem in entitys)
{
members = elem.Elements("Member").ToList();
foreach (XElement m in members)
{
tableInfo = AllTables.Where(item => item.tableName == elem.Attribute("Name").Value&& item.paramName == m.Attribute("Name").Value).First();
if (tableInfo == null)
{
continue;
}
tableInfo.tableDescription = elem.Attribute("Comment").Value;
tableInfo.paramDescription = m.Attribute("Comment").Value;
}
}
}
public async Task Exec(IScenarioContext context)
{
var tCustomer = AllTables.GetItCustomer();
var tUser = AllTables.GetItUser();
var topUsers = new TopFirstNameUsers(5);
await Delete(tCustomer)
.From(tCustomer)
.InnerJoin(topUsers, @on: topUsers.UserId == tCustomer.UserId)
.All()
.Exec(context.Database);
context.WriteLine("Deleted:");
var list = await Select(tUser.FirstName, tUser.LastName)
.From(tUser)
.Where(!Exists(SelectOne().From(tCustomer).Where(tCustomer.UserId == tUser.UserId)))
.OrderBy(tUser.FirstName)
.QueryList(context.Database, r => (FirstName: tUser.FirstName.Read(r), LastName: tUser.LastName.Read(r)));
foreach (var name in list)
{
context.WriteLine($"{name.FirstName} {name.LastName}");
}
}
public async Task Exec(IScenarioContext context)
{
var tUser = AllTables.GetItUser();
var tCustomer = AllTables.GetItCustomer();
var maxVersion = (int) await Select(Max(tUser.Version))
.From(tUser)
.QueryScalar(context.Database);
var countBefore = (long) await Select(Cast(CountOne(), SqlType.Int64))
.From(tUser)
.Where(tUser.Version == maxVersion & Exists(SelectOne().From(tCustomer).Where(tCustomer.UserId == tUser.UserId)))
.QueryScalar(context.Database);
await Update(tUser)
.Set(tUser.Version, tUser.Version + 1)
.From(tUser)
.InnerJoin(tCustomer, on: tCustomer.UserId == tUser.UserId)
.All()
.Exec(context.Database);
var countAfter = (long) await Select(Cast(CountOne(), SqlType.Int64))
.From(tUser)
.Where(tUser.Version == maxVersion + 1)
.QueryScalar(context.Database);
if (countBefore != countAfter)
{
throw new Exception($"Something went wrong: count before {countBefore}, count after {countAfter}");
}
Console.WriteLine();
Console.WriteLine($"{countAfter} items were updated.");
Console.WriteLine();
}
public void TestGetTableData()
{
Assert.AreEqual(2, AllTables.GetData("Barbarian", "Rages", "Level", 1)); // 2 Rages at levels 1 & 2
Assert.AreEqual(2, AllTables.GetData("Barbarian", "Rages", "Level", 2));
Assert.AreEqual(3, AllTables.GetData("Barbarian", "Rages", "Level", 3));
Assert.AreEqual(3, AllTables.GetData("Barbarian", "Rages", "Level", 4));
Assert.AreEqual(3, AllTables.GetData("Barbarian", "Rages", "Level", 5));
Assert.AreEqual(4, AllTables.GetData("Barbarian", "Rages", "Level", 6));
Assert.AreEqual(4, AllTables.GetData("Barbarian", "Rages", "Level", 7));
Assert.AreEqual(4, AllTables.GetData("Barbarian", "Rages", "Level", 8));
Assert.AreEqual(4, AllTables.GetData("Barbarian", "Rages", "Level", 9));
Assert.AreEqual(4, AllTables.GetData("Barbarian", "Rages", "Level", 10));
Assert.AreEqual(4, AllTables.GetData("Barbarian", "Rages", "Level", 11));
Assert.AreEqual(5, AllTables.GetData("Barbarian", "Rages", "Level", 12));
Assert.AreEqual(5, AllTables.GetData("Barbarian", "Rages", "Level", 13));
Assert.AreEqual(5, AllTables.GetData("Barbarian", "Rages", "Level", 14));
Assert.AreEqual(5, AllTables.GetData("Barbarian", "Rages", "Level", 15));
Assert.AreEqual(5, AllTables.GetData("Barbarian", "Rages", "Level", 16));
Assert.AreEqual(6, AllTables.GetData("Barbarian", "Rages", "Level", 17));
Assert.AreEqual(6, AllTables.GetData("Barbarian", "Rages", "Level", 18));
Assert.AreEqual(6, AllTables.GetData("Barbarian", "Rages", "Level", 19));
Assert.AreEqual(int.MaxValue, AllTables.GetData("Barbarian", "Rages", "Level", 20));
}
private void btnSaveXML_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
if (metadataReader == null)
{
MessageBox.Show("Connect to db first!");
}
else
{
var _tables = metadataReader.GetTables(ownersComboBox.SelectedItem.ToString());
if (_tables.Count < 1)
{
MessageBox.Show("Connect to table first!");
return;
}
if (sfd.ShowDialog() == DialogResult.OK)
{
foreach (var item in _tables)
{
var gridData = metadataReader.GetTableDetails(item, ownersComboBox.SelectedItem.ToString()) ??
new List <Column>();
item.Columns = gridData;
}
AllTables at = new AllTables();
at.DataBaseName = _currentConnection.Name;
at.Tables = _tables;
at.Save(sfd.FileName);
}
}
}
public async Task Exec(IScenarioContext context)
{
IReadOnlyList <TableBase> createList = AllTables.BuildAllTableList(context.Dialect == SqlDialect.PgSql);
var dropping = createList.Reverse().Select(i => i.Script.DropIfExist()).Combine();
await context.Database.Statement(dropping);
var creating = createList.Select(i => i.Script.Create()).Combine();
await context.Database.Statement(creating);
}
public ActionResult Fish(int id)
{
List <SelectListItem> FileInfo = new List <SelectListItem>();
List <SelectListItem> FishTypes = new List <SelectListItem>();
AllTables fish = new AllTables();
fish.fish = context.fish.Include("AffiliateLink").Include("FishType").Include("ImageCall").Include("PreferredFood").Where(x => x.ID == id).FirstOrDefault();
{
SelectListItem defaultValue = new SelectListItem();
defaultValue.Value = fish.fish.AffiliateLink.ID.ToString();
defaultValue.Text = fish.fish.AffiliateLink.name;
FileInfo.Add(defaultValue);
}
{
SelectListItem Object = new SelectListItem();
Object.Value = fish.fish.FishType.ID.ToString();
Object.Text = fish.fish.FishType.Name;
FishTypes.Add(Object);
}
foreach (var item in context.affiliateLinks)
{
if (item.ID != fish.fish.AffiliateLink.ID)
{
SelectListItem Object = new SelectListItem();
Object.Value = item.ID.ToString();
Object.Text = item.name;
FileInfo.Add(Object);
}
}
foreach (var item in context.fishTypes)
{
if (item.ID != fish.fish.FishType.ID)
{
SelectListItem Object = new SelectListItem();
Object.Value = item.ID.ToString();
Object.Text = item.Name;
FishTypes.Add(Object);
}
}
SelectList a = new SelectList(FileInfo, "Value", "Text");
SelectList b = new SelectList(FishTypes, "Value", "Text");
IEnumerable <SelectListItem> inum = FileInfo;
ViewBag.Affiliates = a;
ViewBag.Context = context;
ViewBag.FishTypes = b;
return(View(fish));
}
/// <summary>
/// 保存注释信息.
/// </summary>
/// <param name="filePath">注释文件的保存路径.</param>
public void SaveComments(string filePath)
{
if (string.IsNullOrEmpty(filePath))
{
return;
}
List <TableInfoModel> tables = AllTables.Distinct(p => p.tableName).ToList();
XmlDocument document = new XmlDocument();
document.LoadXml("<?xml version=\"1.0\" encoding=\"utf-8\"?><CodeComments></CodeComments>");
XmlNode Root = document.SelectSingleNode("/CodeComments");
XmlElement entityClass, memberElem;
foreach (TableInfoModel tableInfo in tables)
{
if (string.IsNullOrEmpty(tableInfo.tableDescription))
{
tableInfo.tableDescription = string.Empty;
}
entityClass = document.CreateElement("EntityClass");
entityClass.SetAttribute("Name", tableInfo.tableName);
entityClass.SetAttribute("Comment", tableInfo.tableDescription);
// 创建并存储类成员的注释
List <TableInfoModel> classMembers = AllTables.Where(item => item.tableName == tableInfo.tableName).ToList();
foreach (TableInfoModel m in classMembers)
{
memberElem = document.CreateElement("Member");
memberElem.SetAttribute("Name", m.paramName);
memberElem.SetAttribute("Comment", m.paramDescription);
entityClass.AppendChild(memberElem);
}
Root.AppendChild(entityClass);
}
// 保存 xml 文档.
if (File.Exists(filePath))
{
File.Delete(filePath);
}
XmlWriterSettings settings = new XmlWriterSettings
{
Indent = true,
Encoding = Encoding.UTF8,
NewLineChars = Environment.NewLine
};
using (Stream fs = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.Write))
{
XmlWriter writer = XmlTextWriter.Create(fs, settings);
document.WriteTo(writer);
writer.Flush();
writer.Close();
fs.Flush();
}
}
private static void LoadAllTables()
{
for (int i = 0; i < singleTon.chkListTables.Items.Count; i++)
{
AllTables.CreateTableEntry(singleTon.chkListTables.Items[i].ToString());
}
foreach (TableName aTable in TablesAndViewsUI.AllTables.Tables)
{
aTable.ForeignKeys = singleTon.schemaLoader.LoadToList(FormatSQLParser.ForeignKeyQueryString(aTable.Name, FormatSQLParser.MSSQL_ForeignKeyQuery), _aDataObject);
aTable.PrimaryKeys = singleTon.schemaLoader.LoadToList(FormatSQLParser.PrimaryKeyQueryString(aTable.Name, FormatSQLParser.MSSQL_PrimaryKeyQuery), "ColumnName", _aDataObject);
aTable.AlternativeKeys = singleTon.schemaLoader.LoadToList(SchemaRestrictions.Columns, aTable.Name, "COLUMN_NAME", _aDataObject);
}
}
private void todasAsTabelasToolStripMenuItem_Click(object sender, EventArgs e)
{
var AllTB = new AllTables();
AllTB.MdiParent = this;
// o if verifica se o form ja foi aberto assim nao criando mais de um ao mesmo tempo
if (Application.OpenForms.OfType <AllTables>().Count() > 0)
{
Application.OpenForms.OfType <AllTables>().First().Focus();
}
else
{
AllTB.Show();
}
}
// GET: Admin/Home
public ActionResult Index()
{
AllTables MyModel = new AllTables();
MyModel.books = db.books.ToList();
MyModel.users = db.users.ToList();
MyModel.authors = db.authors.ToList();
MyModel.categories = db.categories.ToList();
MyModel.subcategories = db.subcategories.ToList();
MyModel.comments = db.comments.ToList();
MyModel.publications = db.publications.ToList();
MyModel.logs = db.Logs.ToList();
return(View(MyModel));
}
//[OutputCache(Duration = 360)]
public ActionResult Index()
{
AllTables AllTables = new AllTables();
try{
AllTables.C_Section_Table = (from o in db.C_Section_Table select o).ToList();
AllTables.C_Section_Content = (from o in db.C_Section_Content.Include(r => r.C_Images) select o).ToList();
}
catch (Exception ex)
{
ViewBag.error = ex.Message;
return(View("error"));
}
return(PartialView(AllTables));
}
public async Task Exec(IScenarioContext context)
{
var table = AllTables.GetItAllColumnTypes(context.Dialect == SqlDialect.PgSql);
var jsonStringOriginal = await ReadAsJsonString(context, table);
await context.Database.Exec(SqQueryBuilder.Delete(table).All());
await Insert(context, jsonStringOriginal, table);
var jsonStringAfter = await ReadAsJsonString(context : context, table : table);
if (jsonStringOriginal != jsonStringAfter)
{
throw new Exception("Export/Import/Export was not correct");
}
}
private void btnReadTableXML_Click(object sender, EventArgs e)
{
string _Tablexmlpath = string.Empty;
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
_Tablexmlpath = ofd.FileName;
mnuReadTable.ToolTipText = _Tablexmlpath;
}
if (File.Exists(_Tablexmlpath)
)
{
_rgProject.Alltables = AllTables.Load(_Tablexmlpath);
LoadTablesToTree(_rgProject.Alltables.Tables);
}
}
public async Task Exec(IScenarioContext context)
{
var tUser = AllTables.GetItUser();
var tCompany = AllTables.GetItCompany();
var unionResult = await SelectTop(2, (tUser.FirstName + "-" + tUser.LastName).As("Name")).From(tUser)
.Union(SelectTop(2, tCompany.CompanyName.As("Name")).From(tCompany))
.QueryList(context.Database, r => r.GetString("Name"));
Console.WriteLine("Union");
foreach (var name in unionResult)
{
Console.WriteLine(name);
}
var exceptSet = Values(unionResult
.Where((i, index) => index % 2 == 0)
.Select(i => new ExprValue[] { Literal(i) })
.ToList())
.As("EX", "Name");
var unionExceptResult = await SelectTop(2, (tUser.FirstName + "-" + tUser.LastName).As("Name")).From(tUser)
.Union(SelectTop(2, tCompany.CompanyName.As("Name")).From(tCompany))
.Except(Select(exceptSet.Alias.AllColumns()).From(exceptSet))
.QueryList(context.Database, r => r.GetString("Name"));
Console.WriteLine();
Console.WriteLine("Union Except");
foreach (var name in unionExceptResult)
{
Console.WriteLine(name);
}
for (int i = 0; i < unionResult.Count; i++)
{
if (i % 2 != 0)
{
if (unionResult[i] != unionExceptResult[i / 2])
{
throw new Exception(unionResult[i] + " != " + unionExceptResult[i / 2]);
}
}
}
}
public ActionResult Send(AllTables model)
{
string _mail = model.Email;
string _fname = model.First_Name;
string _lname = model.Last_Name;
string _subject = model.Subject;
dynamic email = new Email("MyMail");
email.To = "*****@*****.**";
email.from = _mail;
email.subject = "website email from :" + _fname;
email.Body = _subject;
email.CustMail = _mail;
email.firstname = _fname;
email.lastname = _lname;
email.Send();
return(RedirectToAction("Index"));
}
public override CompletionWindow Complete(TextArea textArea)
{
CompletionWindow completionWindow = new CompletionWindow(textArea);
string lineLeft = textArea.Document.GetLineLeftOf(textArea.Caret.Offset);
string tableName = lineLeft.EverythingBetweenNarrow("Table(\"", "\"");
if (string.IsNullOrWhiteSpace(tableName))
{
return(null);
}
List <string> tableColumns = AllTables.GetColumns(tableName);
IList <ICompletionData> data = completionWindow.CompletionList.CompletionData;
foreach (string tableColumn in tableColumns)
{
data.Add(new TextStringCompletionData(tableColumn));
}
return(completionWindow);
}
public IActionResult Index(AllTables model)
{
// Get all states again
var tables = GetAllTables();
// Set these states on the model. We need to do this because
// only the selected value from the DropDownList is posted back, not the whole
// list of states.
model.Tables = GetSelectListItems(tables);
// In case everything is fine - i.e. both "Name" and "State" are entered/selected,
// redirect user to the "Done" page, and pass the user object along via Session
// if (ModelState.IsValid)
// {
// Session["SignUpModel"] = model;
// return RedirectToAction("Done");
// }
// Something is not right - so render the registration page again,
// keeping the data user has entered by supplying the model.
return(View("~/Views/Klient/Index.cshtml"));
}
public static Chair GetRandomEmptyChair()
{
if (AllTables.Count == 0)
{
return(null);
}
Table[] freeTables = AllTables.Where(tbl => !tbl.IsFull && !tbl.IsDestroyed).ToArray();
if (freeTables.Length == 0)
{
return(null);
}
Table table = freeTables[Random.Range(0, freeTables.Length)];
Chair[] freeChairs = table.Chairs.Where(chr => !chr.IsOccupied).ToArray();
if (freeChairs.Length == 0)
{
return(null);
}
return(freeChairs[Random.Range(0, freeChairs.Length)]);
}
public ActionResult UploadFish(AllTables FishUploaded, HttpPostedFileBase filetobeuploaded, int selectedaffiliate, int selectedfishtype)
{
using (MemoryStream a = new MemoryStream())
{
filetobeuploaded.InputStream.CopyTo(a);
byte[] imagefile = a.GetBuffer();
Image picture = new Image();
picture.Name = filetobeuploaded.FileName;
picture.ImageFile = imagefile;
Fish FishToBeUploaded = FishUploaded.fish;
FishToBeUploaded.ImageCall = picture;
FishToBeUploaded.Price = FishUploaded.fish.Price;
FishToBeUploaded.AffiliateLink = ucContext.affiliateLinks.Where(u => u.ID == selectedaffiliate).FirstOrDefault();
FishToBeUploaded.FishType = ucContext.fishTypes.Where(u => u.ID == selectedfishtype).FirstOrDefault();
FishToBeUploaded.Available = true;
int i = 0;
List <Food> foodpreference = new List <Food>();
foreach (var item in FishUploaded.fpyn)
{
if (item == true)
{
int temp = FishUploaded.fpid[i];
foodpreference.Add(ucContext.foods.Where(u => u.ID == temp).SingleOrDefault());
}
i++;
}
FishToBeUploaded.PreferredFood = foodpreference;
FishToBeUploaded.Available = true;
ucContext.fish.Add(FishToBeUploaded);
ucContext.SaveChanges();
}
return(RedirectToAction("Fish", "List"));
}
private static async Task SelectAllColumns(IScenarioContext context)
{
void PrintColumns(ISqDataRecordReader r)
{
for (int i = 0; i < r.FieldCount; i++)
{
context.Write(r.GetName(i) + ",");
}
context.WriteLine(null);
}
var tUser = AllTables.GetItUser();
var tCustomer = AllTables.GetItCustomer();
await SelectTop(1, AllColumns()).From(tUser).Query(context.Database, PrintColumns);
await SelectTop(1, tUser.AllColumns()).From(tUser).Query(context.Database, PrintColumns);
await SelectTop(1, tUser.AllColumns(), tCustomer.AllColumns())
.From(tUser)
.InnerJoin(tCustomer, on: tCustomer.UserId == tUser.UserId)
.Query(context.Database, PrintColumns);
}
public async Task Exec(IScenarioContext context)
{
var tUser = AllTables.GetItUser();
var top2Users = await SelectTop(2, UserEmail.GetColumns(tUser))
.From(tUser)
.OrderBy(tUser.FirstName)
.QueryList(context.Database, r => UserEmail.Read(r, tUser));
Console.WriteLine(top2Users[0]);
Console.WriteLine(top2Users[1]);
if (context.Dialect != SqlDialect.TSql)
{
top2Users = await SelectTop(2, UserEmail.GetColumns(tUser))
.From(tUser)
.OrderBy(tUser.FirstName)
.Offset(5)
.QueryList(context.Database, r => UserEmail.Read(r, tUser));
Console.WriteLine(top2Users[0].Email);
Console.WriteLine(top2Users[1].Email);
}
}
public async Task Exec(IScenarioContext context)
{
var data = this.ReadUserData();
var utcNow = DateTime.UtcNow;
var userTable = AllTables.GetItUser();
var ids = await InsertDataInto(userTable, data)
.MapData(s => s
.Set(s.Target.ExternalId, s.Source.ExternalId)
.Set(s.Target.FirstName, s.Source.FirstName)
.Set(s.Target.LastName, s.Source.LastName)
.Set(s.Target.Email, s.Source.Email)
)
.AlsoInsert(s => s
.Set(s.Target.RegDate, utcNow)
.Set(s.Target.Version, 1)
.Set(s.Target.Created, utcNow)
.Set(s.Target.Modified, utcNow)
)
.Output(userTable.UserId, userTable.FirstName, userTable.LastName)
.QueryList(context.Database, r => $"{userTable.UserId.Read(r)}, {userTable.FirstName.Read(r)} {userTable.LastName.Read(r)}");
foreach (var id in ids.Take(3))
{
context.WriteLine(id);
}
context.WriteLine("...");
context.WriteLine($"Total users inserted: {ids.Count}");
var count = (long) await Select(Cast(CountOne(), SqlType.Int64)).From(userTable).QueryScalar(context.Database);
context.WriteLine($"Users count: {count}");
await InsertCustomers(context);
}
void ISqlFormattable.AppendTo(SqlStringBuilder builder)
{
if (IsEmpty)
{
return;
}
builder.Append("FROM ");
var tables = AllTables.ToList();
for (int i = 0; i < tables.Count; i++)
{
var source = tables[i];
JoinPart joinPart = null;
if (i > 0 && joinParts.Count > 0)
{
joinPart = GetJoinPart(i - 1);
if (joinPart != null &&
joinPart.OnExpression != null)
{
if (joinPart.JoinType == JoinType.Inner)
{
builder.Append(" INNER JOIN ");
}
else if (joinPart.JoinType == JoinType.Right)
{
builder.Append(" RIGHT OUTER JOIN ");
}
else if (joinPart.JoinType == JoinType.Left)
{
builder.Append(" LEFT OUTER JOIN ");
}
else if (joinPart.JoinType == JoinType.Full)
{
builder.Append(" FULL OUTER JOINT ");
}
}
}
if (i > 0 &&
(joinPart == null || joinPart.OnExpression == null))
{
builder.Append(", ");
}
if (source.IsSubQuery)
{
builder.Append("(");
source.SubQuery.AppendTo(builder);
builder.Append(")");
}
else
{
builder.Append(source.Name);
}
if (!String.IsNullOrEmpty(source.Alias))
{
builder.Append(" AS ");
builder.Append(source.Alias);
}
if (joinPart != null &&
joinPart.OnExpression != null)
{
builder.Append(" ON ");
joinPart.OnExpression.AppendTo(builder);
}
}
}
public async Task Exec(IScenarioContext context)
{
var guid = Guid.Parse("58AD8253-4F8F-4C84-B930-4F58A8F25912");
var tCompany = AllTables.GetItCompany();
var data = new[] { new CompanyInitData(id: 0, name: "TestCompany", externalId: guid) };
var exprInsert = InsertDataInto(tCompany, data)
.MapData(CompanyInitData.GetMapping)
.AlsoInsert(m => m
.Set(m.Target.Modified, GetUtcDate())
.Set(m.Target.Created, GetUtcDate())
.Set(m.Target.Version, 1)
).Done();
using (context.Database.BeginTransaction())
{
await exprInsert.Exec(context.Database);
if (!await CheckExistence())
{
throw new Exception("Inside transaction the data should be visible");
}
}
if (await CheckExistence())
{
throw new Exception("Transaction was not committed, so it should return nothing");
}
using (var t = context.Database.BeginTransaction())
{
await exprInsert.Exec(context.Database);
t.Commit();
}
if (!await CheckExistence())
{
throw new Exception("Transaction was committed, so it should return something");
}
using (var t = context.Database.BeginTransaction(IsolationLevel.Serializable))
{
await Delete(tCompany).Where(tCompany.ExternalId == guid).Exec(context.Database);
t.Commit();
}
if (await CheckExistence())
{
throw new Exception("The row is suppose to be deleted");
}
async Task <bool> CheckExistence()
{
var col = await Select(Literal(1).As("Col"))
.From(tCompany)
.Where(tCompany.ExternalId == guid)
.QueryList(context.Database, r => r.GetInt32("Col"));
return(col.Count > 0);
}
}
public async Task Exec(IScenarioContext context)
{
var tUser = AllTables.GetItUser();
var users = await SqQueryBuilder.Select(UserName.GetColumns(tUser))
.From(tUser)
.OrderBy(tUser.FirstName)
.OffsetFetch(0, 10)
.QueryList(context.Database, r => UserName.Read(r, tUser));
var modifiedUsers = users.Select(u => u.WithFirstName(u.FirstName + "Modified")).ToList();
await SqQueryBuilder.UpdateData(tUser, modifiedUsers)
.MapDataKeys(UserName.GetUpdateKeyMapping)
.MapData(UserName.GetUpdateMapping)
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.Exec(context.Database);
var usersAfterMod = await SqQueryBuilder.Select(UserName.GetColumns(tUser))
.From(tUser)
.OrderBy(tUser.FirstName)
.OffsetFetch(0, 10)
.QueryList(context.Database, r => UserName.Read(r, tUser));
for (var index = 0; index < usersAfterMod.Count; index++)
{
if (usersAfterMod[index].FirstName != modifiedUsers[index].FirstName)
{
throw new Exception("Name was not updated");
}
if (usersAfterMod[index].LastName != modifiedUsers[index].LastName)
{
throw new Exception("Name was not updated");
}
}
await SqQueryBuilder.UpdateData(tUser, users)
.MapDataKeys(UserName.GetUpdateKeyMapping)
.MapData(UserName.GetUpdateMapping)
.AlsoSet(s => s.Set(s.Target.Version, s.Target.Version + 1))
.Done()
.Exec(context.Database);
var usersAfterMod2 = await SqQueryBuilder.Select(UserName.GetColumns(tUser))
.From(tUser)
.OrderBy(tUser.FirstName)
.OffsetFetch(0, 10)
.QueryList(context.Database, r => UserName.Read(r, tUser));
for (var index = 0; index < usersAfterMod2.Count; index++)
{
if (usersAfterMod2[index].FirstName != users[index].FirstName)
{
throw new Exception("Name was not updated");
}
if (usersAfterMod2[index].LastName != users[index].LastName)
{
throw new Exception("Name was not updated");
}
}
}
public TopFirstNameUsers(int top, Alias alias = default) : base(alias)
{
this._top = top;
this._tUser = AllTables.GetItUser();
this.UserId = this._tUser.UserId.AddToDerivedTable(this);
}