private static async Task Step18ExportToJson(ISqDatabase database)
{
var tableUser = new TableUser(Alias.Empty);
var selectExpr = Select(tableUser.FirstName, tableUser.LastName)
.From(tableUser)
.Where(tableUser.LastName == "Sturman")
.Done();
//Exporting
var memoryStream = new MemoryStream();
var jsonWriter = new Utf8JsonWriter(memoryStream);
selectExpr.SyntaxTree().ExportToJson(jsonWriter);
string json = Encoding.UTF8.GetString(memoryStream.ToArray());
Console.WriteLine(json);
//Importing
var restored = (ExprQuerySpecification)ExprDeserializer
.DeserializeFormJson(JsonDocument.Parse(json).RootElement);
var result = await restored
.QueryList(database, r => (tableUser.FirstName.Read(r), tableUser.LastName.Read(r)));
foreach (var name in result)
{
Console.WriteLine(name);
}
}
protected override IExprSubQuery CreateQuery()
{
var tUser = new TableUser();
var tCompany = new TableCompany();
var tCustomer = new TableCustomer();
return(Select(
tCustomer.CustomerId.As(this.CustomerId),
Case()
.When(IsNotNull(tUser.UserId))
.Then(Cast(Literal(1), SqlType.Int16))
.When(IsNotNull(tCompany.CompanyId))
.Then(Cast(Literal(2), SqlType.Int16))
.Else(Null)
.As(this.Type),
Case()
.When(IsNotNull(tUser.UserId))
.Then(tUser.FirstName + " " + tUser.LastName)
.When(IsNotNull(tCompany.CompanyId))
.Then(tCompany.CompanyName)
.Else(Null)
.As(this.Name)
)
.From(tCustomer)
.LeftJoin(tUser, on: tUser.UserId == tCustomer.UserId)
.LeftJoin(tCompany, on: tCompany.CompanyId == tCustomer.CompanyId)
.Done());
}
private static async Task Step19ExportToXml(ISqDatabase database)
{
var tableUser = new TableUser(Alias.Empty);
var selectExpr = Select(tableUser.FirstName, tableUser.LastName)
.From(tableUser)
.Where(tableUser.LastName == "Sturman")
.Done();
//Exporting
var stringBuilder = new StringBuilder();
using XmlWriter writer = XmlWriter.Create(stringBuilder);
selectExpr.SyntaxTree().ExportToXml(writer);
//Importing
XmlDocument document = new XmlDocument();
document.LoadXml(stringBuilder.ToString());
var restored = (ExprQuerySpecification)ExprDeserializer
.DeserializeFormXml(document.DocumentElement !);
var result = await restored
.QueryList(database, r => (tableUser.FirstName.Read(r), tableUser.LastName.Read(r)));
foreach (var name in result)
{
Console.WriteLine(name);
}
}
private static async Task Step15SyntaxModification(ISqDatabase database)
{
var tUser = new TableUser();
Console.WriteLine("Original expression:");
var expression = SelectTop(1, tUser.FirstName).From(tUser).Done();
await expression.QueryScalar(database);
expression = expression
.WithTop(null)
.WithSelectList(tUser.UserId, tUser.FirstName + " " + tUser.LastName)
.WithWhere(tUser.UserId == 7);
Console.WriteLine("With changed selection list and filter:");
await expression.QueryScalar(database);
var tCustomer = new TableCustomer();
expression = expression
.WithInnerJoin(tCustomer, on: tCustomer.UserId == tUser.UserId);
Console.WriteLine("With joined table");
await expression.QueryScalar(database);
}
private static async Task Step11AnalyticAndWindowFunctions(ISqDatabase database)
{
var cUserName = CustomColumnFactory.String("Name");
var cNum = CustomColumnFactory.Int64("Num");
var cFirst = CustomColumnFactory.String("First");
var cLast = CustomColumnFactory.String("Last");
var user = new TableUser();
await Select(
(user.FirstName + " " + user.LastName)
.As(cUserName),
RowNumber()
/*.OverPartitionBy(some fields)*/
.OverOrderBy(user.FirstName)
.As(cNum),
FirstValue(user.FirstName + " " + user.LastName)
/*.OverPartitionBy(some fields)*/
.OverOrderBy(user.FirstName)
.FrameClauseEmpty()
.As(cFirst),
LastValue(user.FirstName + " " + user.LastName)
/*.OverPartitionBy(some fields)*/
.OverOrderBy(user.FirstName)
.FrameClause(
FrameBorder.UnboundedPreceding,
FrameBorder.UnboundedFollowing)
.As(cLast))
.From(user)
.Query(database,
r => Console.WriteLine(
$"Num: {cNum.Read(r)}, Name: {cUserName.Read(r)}, " +
$"First: {cFirst.Read(r)}, Last: {cLast.Read(r)}"));
}
private static async Task Step13TempTables(ISqDatabase database)
{
var tmp = new TempTable();
var tableUser = new TableUser();
var tableCompany = new TableCompany();
await database.Statement(tmp.Script.Create());
//Users
await InsertInto(tmp, tmp.Name)
.From(Select(tableUser.FirstName + " " + tableUser.LastName)
.From(tableUser))
.Exec(database);
//Companies
await InsertInto(tmp, tmp.Name)
.From(Select(tableCompany.CompanyName)
.From(tableCompany))
.Exec(database);
await Select(tmp.Columns)
.From(tmp)
.OrderBy(tmp.Name)
.Query(database,
r => Console.WriteLine($"Id: {tmp.Id.Read(r)}, Name: {tmp.Name.Read(r)}"));
//Dropping the temp table is optional
//It will be automatically removed when
//the connection is closed
await database.Statement(tmp.Script.Drop());
}
private static async Task Step7CreatingCustomers(ISqDatabase database)
{
var tUser = new TableUser();
var tCompany = new TableCompany();
var tCustomer = new TableCustomer();
var tSubCustomer = new TableCustomer();
//Users
await InsertInto(tCustomer, tCustomer.UserId)
.From(
Select(tUser.UserId)
.From(tUser)
.Where(!Exists(
SelectOne()
.From(tSubCustomer)
.Where(tSubCustomer.UserId == tUser.UserId))))
.Exec(database);
//Companies
await InsertInto(tCustomer, tCustomer.CompanyId)
.From(
Select(tCompany.CompanyId)
.From(tCompany)
.Where(!Exists(
SelectOne()
.From(tSubCustomer)
.Where(tSubCustomer.CompanyId == tCompany.CompanyId))))
.Exec(database);
}
private static async Task Step12Merge(ISqDatabase database)
{
var data = new[]
{
new { FirstName = "Francois", LastName = "Sturman2" },
new { FirstName = "Allina", LastName = "Freeborne2" },
new { FirstName = "Maye", LastName = "Malloy" },
};
var action = CustomColumnFactory.String("Actions");
var inserted = CustomColumnFactory.NullableInt32("Inserted");
var deleted = CustomColumnFactory.NullableInt32("Deleted");
var tableUser = new TableUser();
await MergeDataInto(tableUser, data)
.MapDataKeys(s => s
.Set(s.Target.FirstName, s.Source.FirstName))
.MapData(s => s
.Set(s.Target.LastName, s.Source.LastName))
.WhenMatchedThenUpdate()
.AlsoSet(s => s
.Set(s.Target.Version, s.Target.Version + 1)
.Set(s.Target.ModifiedAt, GetUtcDate()))
.WhenNotMatchedByTargetThenInsert()
.AlsoInsert(s => s
.Set(s.Target.Version, 1)
.Set(s.Target.ModifiedAt, GetUtcDate()))
.Output((t, s, m) => m.Inserted(t.UserId.As(inserted)).Deleted(t.UserId.As(deleted)).Action(action))
.Done()
.Query(database,
r => Console.WriteLine(
$"UserId Inserted: {inserted.Read(r)},UserId Deleted: {deleted.Read(r)} , Action: {action.Read(r)}"));
}
private static async Task Step5DeletingData(ISqDatabase database)
{
var tUser = new TableUser();
await Delete(tUser)
.Where(tUser.FirstName.Like("May%"))
.Output(tUser.UserId)
.Query(database, record => Console.WriteLine("Removed user id: " + tUser.UserId.Read(record)));
}
private static async Task Step16Models(ISqDatabase database)
{
var tUser = new TableUser();
var users = await Select(UserName.GetColumns(tUser))
.From(tUser)
.QueryList(database, r => UserName.Read(r, tUser));
foreach (var userName in users)
{
Console.WriteLine($"{userName.Id} {userName.FirstName} {userName.LastName}");
}
}
private static async Task Step3SelectingData(ISqDatabase database)
{
var tUser = new TableUser();
var selectResult = await Select(tUser.UserId, tUser.FirstName, tUser.LastName)
.From(tUser)
.OrderBy(tUser.FirstName, tUser.LastName)
.QueryList(database,
r => (
Id: tUser.UserId.Read(r),
FirstName: tUser.FirstName.Read(r),
LastName: tUser.LastName.Read(r)));
foreach (var record in selectResult)
{
Console.WriteLine(record);
}
}
private static async Task Step2InsertingData(ISqDatabase database)
{
var tUser = new TableUser();
var data = new[]
{
new { FirstName = "Francois", LastName = "Sturman" },
new { FirstName = "Allina", LastName = "Freeborne" },
new { FirstName = "Maye", LastName = "Maloy" },
};
await InsertDataInto(tUser, data)
.MapData(s => s
.Set(s.Target.FirstName, s.Source.FirstName)
.Set(s.Target.LastName, s.Source.LastName))
.AlsoInsert(s => s
.Set(s.Target.Version, 1)
.Set(s.Target.ModifiedAt, GetUtcDate()))
.Exec(database);
}
private static async Task Step8JoinTables(ISqDatabase database)
{
var tUser = new TableUser();
var tCompany = new TableCompany();
var tCustomer = new TableCustomer();
var cType = CustomColumnFactory.Int16("Type");
var cName = CustomColumnFactory.String("Name");
var customers = await Select(
tCustomer.CustomerId,
Case()
.When(IsNotNull(tUser.UserId))
.Then(Cast(1, SqlType.Int16))
.When(IsNotNull(tCompany.CompanyId))
.Then(Cast(2, SqlType.Int16))
.Else(Null)
.As(cType),
Case()
.When(IsNotNull(tUser.UserId))
.Then(tUser.FirstName + " " + tUser.LastName)
.When(IsNotNull(tCompany.CompanyId))
.Then(tCompany.CompanyName)
.Else(Null)
.As(cName)
)
.From(tCustomer)
.LeftJoin(tUser, on: tUser.UserId == tCustomer.UserId)
.LeftJoin(tCompany, on: tCompany.CompanyId == tCustomer.CompanyId)
.QueryList(database,
r => (Id: tCustomer.CustomerId.Read(r), CustomerType: cType.Read(r), Name: cName.Read(r)));
foreach (var customer in customers)
{
Console.WriteLine($"Id: {customer.Id}, Name: {customer.Name}, Type: {customer.CustomerType}");
}
}
private static async Task Step4UpdatingData(ISqDatabase database)
{
var tUser = new TableUser();
await Update(tUser)
.Set(tUser.LastName, "Malloy")
.Set(tUser.Version, tUser.Version + 1)
.Set(tUser.ModifiedAt, GetUtcDate())
.Where(tUser.LastName == "Maloy")
.Exec(database);
//Writing to console without storing in memory
await Select(tUser.Columns)
.From(tUser)
.Query(database,
record =>
{
Console.Write(tUser.UserId.Read(record) + ",");
Console.Write(tUser.FirstName.Read(record) + " ");
Console.Write(tUser.LastName.Read(record) + ",");
Console.Write(tUser.Version.Read(record) + ",");
Console.WriteLine(tUser.ModifiedAt.Read(record).ToString("s"));
});
}
private static async Task Step20ExportToPlain(ISqDatabase database)
{
var tableUser = new TableUser(Alias.Empty);
ExprBoolean filter1 = tableUser.LastName == "Sturman";
ExprBoolean filter2 = tableUser.LastName == "Freeborne";
var tableFavoriteFilter = new TableFavoriteFilter();
var tableFavoriteFilterItem = new TableFavoriteFilterItem();
var filterIds = await InsertDataInto(tableFavoriteFilter, new[] { "Filter 1", "Filter 2" })
.MapData(s => s.Set(s.Target.Name, s.Source))
.Output(tableFavoriteFilter.FavoriteFilterId)
.QueryList(database, r => tableFavoriteFilterItem.FavoriteFilterId.Read(r));
var filter1Items =
filter1.SyntaxTree()
.ExportToPlainList((i, id, index, b, s, value) =>
FilterPlainItem.Create(filterIds[0], i, id, index, b, s, value));
var filter2Items =
filter2.SyntaxTree()
.ExportToPlainList((i, id, index, b, s, value) =>
FilterPlainItem.Create(filterIds[1], i, id, index, b, s, value));
await InsertDataInto(tableFavoriteFilterItem, filter1Items.Concat(filter2Items))
.MapData(s => s
.Set(s.Target.FavoriteFilterId, s.Source.FavoriteFilterId)
.Set(s.Target.Id, s.Source.Id)
.Set(s.Target.ParentId, s.Source.ParentId)
.Set(s.Target.IsTypeTag, s.Source.IsTypeTag)
.Set(s.Target.ArrayIndex, s.Source.ArrayIndex)
.Set(s.Target.Tag, s.Source.Tag)
.Set(s.Target.Value, s.Source.Value)
)
.Exec(database);
//Restoring
var restoredFilterItems = await Select(tableFavoriteFilterItem.Columns)
.From(tableFavoriteFilterItem)
.Where(tableFavoriteFilterItem.FavoriteFilterId.In(filterIds))
.QueryList(
database,
r => new FilterPlainItem(
favoriteFilterId: tableFavoriteFilterItem.FavoriteFilterId.Read(r),
id: tableFavoriteFilterItem.Id.Read(r),
parentId: tableFavoriteFilterItem.ParentId.Read(r),
isTypeTag: tableFavoriteFilterItem.IsTypeTag.Read(r),
arrayIndex: tableFavoriteFilterItem.ArrayIndex.Read(r),
tag: tableFavoriteFilterItem.Tag.Read(r),
value: tableFavoriteFilterItem.Value.Read(r)));
var restoredFilter1 = (ExprBoolean)ExprDeserializer
.DeserializeFormPlainList(restoredFilterItems.Where(fi =>
fi.FavoriteFilterId == filterIds[0]));
var restoredFilter2 = (ExprBoolean)ExprDeserializer
.DeserializeFormPlainList(restoredFilterItems.Where(fi =>
fi.FavoriteFilterId == filterIds[1]));
Console.WriteLine("Filter 1");
await Select(tableUser.FirstName, tableUser.LastName)
.From(tableUser)
.Where(restoredFilter1)
.Query(database,
(object)null,
(s, r) =>
{
Console.WriteLine($"{tableUser.FirstName.Read(r)} {tableUser.LastName.Read(r)}");
return(s);
});
Console.WriteLine("Filter 2");
await Select(tableUser.FirstName, tableUser.LastName)
.From(tableUser)
.Where(restoredFilter2)
.Query(database,
(object)null,
(s, r) =>
{
Console.WriteLine($"{tableUser.FirstName.Read(r)} {tableUser.LastName.Read(r)}");
return(s);
});
}