public async Task TestJson()
{
var organisations = new OrganisationTable("orgs");
var users = new UserTable();
var services = new ServiceCollection()
.AddRoscoeSqlServer("Server=localhost;Database=test;");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var schema = GraphQLSchema.Create(x => new RootQueryType(x, db));
var result = await schema.ExecuteRequestAsync(@"query {
Organisations {
id: Id
Name
UserNames
Users {
Id
Name
Organisation {
Name
}
}
}
}", null);
var json = JsonConvert.SerializeObject(result, Formatting.Indented);
}
}
public async Task QueryTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("Host=localhost;Database=test");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var subquery = db.Query(Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue()).Value;
var subquery2 = db.Query(() => Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue().Value()).Value;
var from = db.Query(() => new { Id = Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue().Value() }).Value;
var query = db.Query(() => new
{
Id = organisations.Id.NullValue(),
Name = organisations.Name.ToLower().Value(),
//IdName = (organisations.Id + organisations.Name).Value(),
})
.From(organisations)
//.InnerJoin(from, from.Value().Id == organisations.Id)
.Where(organisations.Id == subquery.DbValue());
//.InnerJoin(organisations, organisations.Id == organisations.Id)
//.Where(organisations.Name == "")
//.GroupBy(organisations.Id);
var sql = query.ToString();
var result = await db.ExecuteAsync(query);
}
}
public async Task UpdateTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("Host=localhost;Database=test");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var subquery = db.Query(() => Guid.Parse("c0cac2b4-4821-4135-900b-4a36b46f0122").DbValue().Value()).Value;
var query = db.Update(organisations)
.Set(organisations.Name, "new name".DbValue())
//.From(organisations)
//.InnerJoin(organisations, organisations.Id == organisations.Id)
.Where(organisations.Id == subquery.DbValue())
.Returning(() => new
{
Id = organisations.Id.Value(),
Name = organisations.Name.Value(),
});
var sql = query.ToString();
var result = await db.ExecuteAsync(query);
}
}
public async Task QueryTest()
{
var services = new ServiceCollection()
.AddRoscoeSqlServer("Server=localhost;Database=test;");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var query = db.Query(() => new
{
Id = organisations.Id.Value(),
Name = Elvis(organisations.Name.Value(), x => x.ToUpper()),
NameIsFoo = db.Functions
.Case()
.When(organisations.Name == new Foo().Value(), 1.DbValue())
.Else(0)
.EndCase().Value(),
})
.From(organisations)
.ForJsonPath();
var result = await db.ExecuteAsync(query);
}
}
protected void RetrieveTable(object sender, EventArgs e)
{
ArrayList org = new ArrayList();
string name = FilterTxt.Value;
CheckBoxList checkboxlist = (CheckBoxList)checkboxes.FindControl("CheckBoxList");
foreach (ListItem item in checkboxlist.Items)
{
if (item.Selected)
{
org.Add(item.Text);
}
}
SaveCheckBoxOrgState();
OrganisationTable.DataSource = dao.SearchOrganisation(org, name, int.Parse(hiddenselect.Value), int.Parse(FilterPastevent.SelectedValue.ToString()), Source.SelectedItem.Text, FilterCat1.SelectedItem.Text, FilterDesignation.Value, FilterCat2.Value);
OrganisationTable.DataBind();
PopulateOrganisationCheckBoxState();
SaveCheckBoxState();
IndividualTable.DataSource = dao.SearchIndividual(name, int.Parse(hiddenselect.Value), int.Parse(FilterPastevent.SelectedValue.ToString()), Source.SelectedItem.Text, FilterCat1.SelectedItem.Text, FilterDesignation.Value, FilterCat2.Value);
IndividualTable.DataBind();
PopulateCheckBoxState();
}
private static async Task InsertOrganisationAsync(OrganisationTable organisationTable)
{
await OrganisationEntityBuilder.Create()
.WithName(organisationTable.Name)
.WithId(Guid.NewGuid())
.Build()
.InsertAsync()
.ConfigureAwait(false);
}
public IValueAccessor <GraphQLNotNull <OrganisationType> > Organisation()
{
var organisations = new OrganisationTable("uo");
return(queryBuilder.Select(
db.Query()
.From(organisations)
.Where(users.OrganisationId == organisations.Id),
x => new OrganisationType(batch, db, organisations, x)));
}
public OrganisationType(
IAsyncBatch batch,
RoscoeDb db,
OrganisationTable organisations,
JsonQueryBuilder queryBuilder)
{
this.batch = batch;
this.db = db;
this.organisations = organisations;
this.queryBuilder = queryBuilder;
}
public IValueAccessor <GraphQLNotNull <GraphQLList <GraphQLNotNull <GraphQLString> > > > OrganisationNames()
{
var organisations = new OrganisationTable("o");
return(provider
.QueryJson()
.SelectScalarList <GraphQLString, string>(
db.Query()
.From(organisations),
organisations.Name));
}
public IValueAccessor <GraphQLNotNull <GraphQLList <GraphQLNotNull <OrganisationType> > > > Organisations()
{
var organisations = new OrganisationTable("o");
return(provider
.QueryJson()
.SelectList(
db.Query()
.From(organisations),
x => new OrganisationType(batch, db, organisations, x)));
}
public IValueAccessor <GraphQLNotNull <OrganisationType> > Organisation(Input <GraphQLNotNull <GraphQLID> > organisationId)
{
var organisations = new OrganisationTable("o");
return(provider
.QueryJson()
.Select(
db.Query()
.From(organisations)
.Where(organisations.Id == Guid.Parse(organisationId.Value())),
x => new OrganisationType(batch, db, organisations, x)));
}
public IValueAccessor <GraphQLNotNull <GraphQLList <GraphQLNotNull <OrganisationType> > > > OrganisationsNamed(Input <GraphQLString> name)
{
var organisations = new OrganisationTable("o");
return(provider
.QueryJson()
.SelectList(
db.Query()
.From(organisations)
.Where(organisations.Name == name.Value()),
x => new OrganisationType(batch, db, organisations, x)));
}
public IValueAccessor <GraphQLNotNull <GraphQLList <GraphQLNotNull <OrganisationType> > > > Organisations()
{
var organisations = new OrganisationTable("o");
return(provider
.QueryJson(
x => x.Query().ForJsonPathWithoutArrayWrapper(),
x => new SqlServerJsonQueryBuilderAdapter(db, x))
.SelectList(
db.Query()
.From(organisations),
x => new OrganisationType(batch, db, organisations, x)));
}
public IValueAccessor <GraphQLNotNull <GraphQLList <GraphQLNotNull <GraphQLString> > > > OrganisationNames()
{
var organisations = new OrganisationTable("o");
return(provider
.QueryJson(
x => x.Query().ForJsonPathWithoutArrayWrapper(),
x => new SqlServerJsonQueryBuilderAdapter(db, x))
.SelectScalarList <GraphQLString, string>(
db.Query()
.From(organisations),
organisations.Name));
}
public async Task ThenTheOrdering_PartyOrganisationIsReturned(Table table)
{
var expected = table.CreateSet <OrganisationTable>().FirstOrDefault();
var response = (await _response.ReadBodyAsJsonAsync());
var actual = new OrganisationTable
{
Name = response.Value <string>("name"),
OdsCode = response.Value <string>("odsCode")
};
actual.Should().BeEquivalentTo(expected);
}
public IValueAccessor <GraphQLNotNull <OrganisationType> > Organisation(Input <GraphQLNotNull <GraphQLID> > organisationId)
{
var organisations = new OrganisationTable("o");
return(provider
.QueryJson(
x => x.Query().ForJsonPathWithoutArrayWrapper(),
x => new SqlServerJsonQueryBuilderAdapter(db, x))
.Select(
db.Query()
.From(organisations)
.Where(organisations.Id == Guid.Parse(organisationId.Value())),
x => new OrganisationType(batch, db, organisations, x)));
}
public UnitOfWork()
{
_database = new Entities();
User = new UserTables(_database.Users);
SecurityQuestion = new SecurityQuestionTables(_database.SecurityQuestions);
Role = new RoleTable(_database.Roles);
OrganisationTable = new OrganisationTable(_database.OrganisationDetails);
BranchTable = new BranchTable(_database.BrancheDetails);
BankTable = new BankTable(_database.BankDetails);
EmployeeTable = new EmployeeTable(_database.Employees);
EmployeeLocationTable = new EmployeeLocationTable(_database.EmployeeLocations);
CustomerTable = new CustomerTable(_database.Customers);
AccountTable = new AccountTable(_database.Accounts);
AccountTypeTable = new AccountTypeTable(_database.AccountTypes);
AccessLevelTable = new AccountAccessLevelTable(_database.AccessLevels);
UserAccountAccessTable = new UserAccountAccessTable(_database.UserAccountAccesses);
AccoutTransactionTable = new AccoutTransactionTable(_database.Transactions);
}
public void InsertTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var selectQuery = db.Query(() => new
{
Id = organisations.Id.NullValue(),
Name = organisations.Name.ToLower().Value(),
//IdName = (organisations.Id + organisations.Name).Value(),
})
.From(organisations);
var updateQuery = db.Update(organisations)
.Set(organisations.Name, "new name".DbValue())
//.From(organisations)
//.InnerJoin(organisations, organisations.Id == organisations.Id)
.Where(organisations.Name == "")
;
var query = db.InsertValues(organisations, new { organisations.Id, organisations.Name })
.Values(new { Id = Guid.NewGuid().DbValue(), Name = "org 1".DbValue() })
.OnConflict(organisations.Id, organisations.Name)
//.DoNothing()
.Update(x => x.Set(organisations.Name, "org 1".DbValue()))
.Returning(() => new
{
Id = organisations.Id.Value(),
});
var selectSql = selectQuery.ToString();
var updateSql = updateQuery.ToString();
var sql = query.ToString();
}
}
public void DeleteTest()
{
var services = new ServiceCollection()
.AddRoscoePostgres("");
using (var provider = services.BuildServiceProvider())
{
var db = new RoscoeDb(provider);
var organisations = new OrganisationTable("o");
var query = db.Delete(organisations)
.From(organisations)
.InnerJoin(organisations, organisations.Id == organisations.Id)
.Where(organisations.Name == "")
.Returning(() => new
{
organisations.Id,
});
var sql = query.ToString();
}
}
private void bindOrgdata()
{
OrganisationTable.DataSource = dao.GetOrganisation();
OrganisationTable.DataBind();
OrganisationTable.HeaderRow.TableSection = TableRowSection.TableHeader;
}
