public ActionResult TestMSSQL()
{
string result = "";
NexusExtDBService service = new NexusExtDBService("vo8qh1qcem.database.windows.net", "Omnius");
JToken rows = service.NewQuery()
.Select("*")
.From("Nexus_WS")
.OrderBy("Id desc")
.FetchAll();
result += "<p>" + service.sql + "</p>";
result += "<pre>" + rows.ToString() + "</pre>";
ViewBag.result = result;
return(View("~/Views/Nexus/ExtDB/Test.cshtml"));
}
public ActionResult Test()
{
string result = "";
NexusExtDBService service = new NexusExtDBService("localhost", "fss_rating");
// Multiple rows
/*JToken rows = service.NewQuery()
* .Select("*")
* .From("fss_employee")
* .OrderBy("lastname, firstname")
* .FetchAll();
*
* result += "<p>" + service.sql + "</p>";
* result += "<pre>" + rows.ToString() + "</pre>";*/
// Single row
/*JToken row = service.NewQuery()
* .Select("*")
* .From("fss_employee")
* .Where()
* ._("firstname like {0}", "Martin")
* ._("lastname like {0}", "Novák")
* .FetchOne();
*
* result += "<p>" + service.sql + "</p>";
* result += "<pre>" + row.ToString() + "</pre>";*/
// Single cell
/*object value = service.NewQuery()
* .Select("email")
* .From("fss_employee")
* .Limit(1)
* .Offset(5)
* .FetchCell("email");
*
* result += "<p>" + service.sql + "</p>";
* result += "<pre>" + value.ToString() + "</pre>";*/
// Array of values
/*int[] ids = new int[] { 1, 2, 3, 4, 5 };
*
* List<Object> arr = service.NewQuery()
* .Select("email")
* .From("fss_employee")
* .Where("id in ({0})", ids)
* .OrderBy("email")
* .FetchArray("email");
*
* result += "<p>" + service.sql + "</p>";
* result += "<pre>" + string.Join(", ", arr) + "</pre>";*/
// hash
JToken hash = service.NewQuery()
.Select("CONCAT_WS(' ', lastname, firstname) as name, email")
.From("fss_employee")
.OrderBy("lastname desc, firstname desc")
.FetchHash("name", "email");
result += "<p>" + service.sql + "</p>";
result += "<pre>" + hash.ToString() + "</pre>";
// řádky jako hash
JToken rowsAsHash = service.NewQuery()
.Select("*")
.From("fss_employee")
.OrderBy("lastname, firstname")
.FetchAllAsHash("email");
result += "<p>" + service.sql + "</p>";
result += "<pre>" + rowsAsHash.ToString() + "</pre>";
// hash řádků se subquery
JToken rowsAsHashArray = service.NewQuery()
.Select()
._("({0}) as email", service.NewSubquery()
.Select("email")
.From("fss_employee")
.Where("id = r.fss_employee_id")
.sql
)
._("r.role")
.From("fss_employee_role r")
.OrderBy("email")
.FetchAllAsHashArray("email");
result += "<p>" + service.sql + "</p>";
result += "<pre>" + rowsAsHashArray.ToString() + "</pre>";
ViewBag.result = result;
return(View("~/Views/Nexus/ExtDB/Test.cshtml"));
}