Retrieve data from the database
Level: advanced
note
The examples in this article are also implemented in the web service. The package that contains the web service implementation is attached in the Resources block.
The CreateJson method processes query results in the examples. View the method below.
CreateJson() method
private string CreateJson(IDataReader dataReader) {
var list = new List <dynamic> ();
var cnt = dataReader.FieldCount;
var fields = new List <string> ();
for (int i = 0; i < cnt; i++) {
fields.Add(dataReader.GetName(i));
}
while (dataReader.Read()) {
dynamic exo = new System.Dynamic.ExpandoObject();
foreach(var field in fields) {
((IDictionary <String, Object> ) exo).Add(field, dataReader.GetColumnValue(field));
}
list.Add(exo);
}
return JsonConvert.SerializeObject(list);
}
Example 1
Example
Select a number of records from the needed table (object schema).
SelectColumns() method
public string SelectColumns(string tableName, int top) {
top = top > 0 ? top : 1;
var result = "{}";
var select = new Select(UserConnection)
.Top(top)
.Column(Column.Asterisk())
.From(tableName);
using(DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using(IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
result = CreateJson(dataReader);
}
}
return result;
}
Example 2
Example
Select the IDs, names, and birth dates of contacts whose birth dates are later than the required year.
SelectContactsYoungerThan() method
public string SelectContactsYoungerThan(string birthYear) {
var result = "{}";
var year = DateTime.ParseExact(birthYear, "yyyy", CultureInfo.InvariantCulture);
var select = new Select(UserConnection)
.Column("Id")
.Column("Name")
.Column("BirthDate")
.From("Contact")
.Where("BirthDate").IsGreater(Column.Parameter(year))
.Or("BirthDate").IsNull()
.OrderByDesc("BirthDate")
as Select;
using(DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using(IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
result = CreateJson(dataReader);
}
}
return result;
}
Example 3
Example
Select the IDs, names, and birth dates of contacts whose birth dates are later than the specified year and who have the account specified.
SelectContactsYoungerThanAndHasAccountId() method
public string SelectContactsYoungerThanAndHasAccountId(string birthYear) {
var result = "{}";
var year = DateTime.ParseExact(birthYear, "yyyy", CultureInfo.InvariantCulture);
var select = new Select(UserConnection)
.Column("Id")
.Column("Name")
.Column("BirthDate")
.From("Contact")
.Where()
.OpenBlock("BirthDate").IsGreater(Column.Parameter(year))
.Or("BirthDate").IsNull()
.CloseBlock()
.And("AccountId").Not().IsNull()
.OrderByDesc("BirthDate")
as Select;
using(DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using(IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
result = CreateJson(dataReader);
}
}
return result;
}
Example 4
Example
Select the IDs and names of all contacts by joining them to the IDs and names of the corresponding accounts.
SelectContactsJoinAccount() method
public string SelectContactsJoinAccount() {
var result = "{}";
var select = new Select(UserConnection)
.Column("Contact", "Id").As("ContactId")
.Column("Contact", "Name").As("ContactName")
.Column("Account", "Id").As("AccountId")
.Column("Account", "Name").As("AccountName")
.From("Contact")
.Join(JoinType.Inner, "Account")
.On("Contact", "Id").IsEqual("Account", "PrimaryContactId")
as Select;
using(DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using(IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
result = CreateJson(dataReader);
}
}
return result;
}
Example 5
Example
Select the IDs and names of the primary account contacts.
SelectAccountPrimaryContacts() method
public string SelectAccountPrimaryContacts() {
var result = "{}";
var select = new Select(UserConnection)
.Column("Id")
.Column("Name")
.From("Contact").As("C")
.Where()
.Exists(new Select(UserConnection)
.Column("A", "PrimaryContactId")
.From("Account").As("A")
.Where("A", "PrimaryContactId").IsEqual("C", "Id"))
as Select;
using(DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using(IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
result = CreateJson(dataReader);
}
}
return result;
}
Example 6
Example
Select the countries and the number of cities in a country if such number is greater than the specified number.
SelectCountriesWithCitiesCount() method
public string SelectCountriesWithCitiesCount(int count) {
var result = "{}";
var select = new Select(UserConnection)
.Column(Func.Count("City", "Id")).As("CitiesCount")
.Column("Country", "Name").As("CountryName")
.From("City")
.Join(JoinType.Inner, "Country")
.On("City", "CountryId").IsEqual("Country", "Id")
.GroupBy("Country", "Name")
.Having(Func.Count("City", "Id")).IsGreater(Column.Parameter(count))
.OrderByDesc("CitiesCount")
as Select;
using(DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) {
using(IDataReader dataReader = select.ExecuteReader(dbExecutor)) {
result = CreateJson(dataReader);
}
}
return result;
}
Example 7
Example
Retrieve the ID of the contact by their name.
SelectCountryIdByCityName() method
public string SelectCountryIdByCityName(string CityName) {
var result = "";
var select = new Select(UserConnection)
.Column("CountryId")
.From("City")
.Where("Name").IsEqual(Column.Parameter(CityName)) as Select;
result = select.ExecuteScalar <Guid> ().ToString();
return result;
}