public async Task <List <Dictionary <string, object> > > Get(string relex)
{
var relexParser = new NReco.Data.Relex.RelexParser();
var q = relexParser.Parse(relex);
CheckTable(q.Table.Name);
return(await DbAdapter.Select(q).ToDictionaryListAsync().ConfigureAwait(false));
}
public Article FindById(int id)
{
var result = _DbNRecoAdapter.Select(
new Query(
"Articles",
(QField)"Id" == (QConst)id
)
).Single <Article>();
return(result);
}
public static void InsertUpdateDeleteForOneRecord(DbDataAdapter dbAdapter)
{
// add new employee by POCO model
var newEmployee = new Employee()
{
EmployeeID = 1000, FirstName = "John", LastName = "Smith"
};
dbAdapter.Insert("Employees", newEmployee);
Console.WriteLine("Added new employee: John Smith (ID=1000)");
// add new employee by dictionary
dbAdapter.Insert("Employees", new Dictionary <string, object>()
{
{ "EmployeeID", 1001 },
{ "FirstName", "Jim" },
{ "LastName", "Gordon" }
});
Console.WriteLine("Added new employee: Jim Gordon (ID=1001)");
// update employee by poco model
newEmployee.FirstName = "Bart";
var newEmployeeByIdQuery = new Query("Employees", (QField)"EmployeeID" == (QConst)newEmployee.EmployeeID);
dbAdapter.Update(newEmployeeByIdQuery, newEmployee);
Console.WriteLine("New first name for EmployeeID=1000: {0}",
dbAdapter.Select(new Query("Employees", (QField)"EmployeeID" == (QConst)1000).Select("FirstName")).Single <string>());
// update employee by dictionary
dbAdapter.Update(
new Query("Employees", (QField)"EmployeeID" == (QConst)1001),
new Dictionary <string, object>()
{
{ "FirstName", "Bruce" },
{ "LastName", "Wayne" }
}
);
var employee_1001_data = dbAdapter.Select(new Query("Employees", (QField)"EmployeeID" == (QConst)1001)).ToDictionary();
Console.WriteLine("New name for EmployeeID=1001: {0} {1}", employee_1001_data["FirstName"], employee_1001_data["LastName"]);
// update only some fields from model
newEmployee.LastName = "Simpson";
newEmployee.FirstName = "Homer";
dbAdapter.Update(newEmployeeByIdQuery, new { LastName = "LastName" });
var newEmployeeNameFromDb = dbAdapter.Select(new Query(newEmployeeByIdQuery).Select("FirstName", "LastName")).ToDictionary();
Console.WriteLine("First+Last for EmployeeID=1000 after update: {0} {1}",
newEmployeeNameFromDb["FirstName"], newEmployeeNameFromDb["LastName"]);
}
public object Resolve(ResolveFieldContext context)
{
var query = new Query(
$"'{_tableMetadata.TableName}'"
);
ApplyArguments(query, context.Arguments);
if (context.FieldName.Contains("_list"))
{
return(_dbAdapter.Select(query).ToDictionaryList());
}
else
{
return(_dbAdapter.Select(query).ToDictionary());
}
}
private void GetColumnsMetadata(TableMetadata table)
{
var tableColumns = _DbNRecoAdapter.Select(
$"PRAGMA table_info('{@table.TableName}');"
).ToList <ColumnMetadata>();
table.Columns = tableColumns;
}
public static void SelectDemo(DbDataAdapter dbAdapter)
{
// select single value
Console.WriteLine("Records count in 'Employees' table: {0}",
dbAdapter.Select(new Query("Employees").Select(QField.Count)).Single <int>()
);
// select data into POCO models (columns are mapped to object properties)
Console.WriteLine("All names from 'Employees' table:");
foreach (var employee in dbAdapter.Select(new Query("Employees")).ToList <Employee>())
{
Console.WriteLine("#{0}: {1} {2}", employee.EmployeeID, employee.FirstName, employee.LastName);
}
Console.WriteLine();
// select data into dictionaries (illustrates subquery)
Console.WriteLine("Products from 'Seafood' category:");
var productBySeafoodQuery = new Query("Products",
new QConditionNode((QField)"CategoryID", Conditions.In,
new Query("Categories", (QField)"CategoryName" == (QConst)"Seafood").Select("CategoryID")
)
).Select("ProductName", "UnitPrice");
foreach (var product in dbAdapter.Select(productBySeafoodQuery).ToDictionaryList())
{
Console.WriteLine("{0} for ${1}", product["ProductName"], product["UnitPrice"]);
}
// select data into RecordSet
Console.WriteLine("Customers from USA:");
var customersRS = dbAdapter.Select(
new Query("Customers", (QField)"Country" == (QConst)"USA").Select("CustomerID", "CompanyName", "ContactName")
).ToRecordSet();
foreach (var row in customersRS)
{
foreach (var col in customersRS.Columns)
{
Console.Write("{0}={1} |", col.Name, row[col]);
}
Console.WriteLine();
}
Console.WriteLine();
}
static void Main(string[] args)
{
// configure ADO.NET and NReco.Data components
var dbFactory = new LoggingDbFactory(Microsoft.Data.Sqlite.SqliteFactory.Instance)
{
LastInsertIdSelectText = "SELECT last_insert_rowid()"
};
var dbCmdBuilder = new DbCommandBuilder(dbFactory);
var dbConn = dbFactory.CreateConnection();
dbConn.ConnectionString = "Data Source=" + Path.Combine(Directory.GetCurrentDirectory(), "northwind.db");
var dbAdapter = new DbDataAdapter(dbConn, dbCmdBuilder);
// lets perform some queries to illustrate that logging works
var employeesCnt = dbAdapter.Select(new Query("Employees").Select(QField.Count)).Single <int>();
dbConn.Open(); // open connection for transaction
try {
using (var tr = dbConn.BeginTransaction()) {
dbAdapter.Transaction = tr;
// some updates
dbAdapter.Insert("Employees", new {
EmployeeID = 1001,
FirstName = "Test",
LastName = "Test"
});
var deleted = dbAdapter.DeleteAsync(new Query("Employees", (QField)"EmployeeID" > (QConst)1000)).Result;
tr.Rollback(); // do not save these changes
dbAdapter.Transaction = null; // clear transaction context
}
} finally {
dbConn.Close();
}
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
}
public static void UpdateForRecordSet(DbDataAdapter dbAdapter)
{
var customersRS = dbAdapter.Select(
new Query("Customers").OrderBy("CustomerID asc")
).ToRecordSet();
Console.WriteLine("Loaded {0} customer records ({1} columns)", customersRS.Count, customersRS.Columns.Count);
// in most cases primary key should be set explicetly
customersRS.SetPrimaryKey("CustomerID");
// lets change USA,Canada to 'North America'
int updateRows = 0;
foreach (var row in customersRS)
{
if ("USA".Equals(row["Country"]) || "Canada".Equals(row["Country"]))
{
row["Country"] = "North America";
updateRows++;
}
}
// lets delete some customer
var deleteRows = 0;
foreach (var row in customersRS)
{
if ("PARIS".Equals(row["CustomerID"]))
{
row.Delete();
deleteRows++;
}
}
// lets add one customer from Ukraine
var uaCustomer = customersRS.Add();
uaCustomer["CompanyName"] = "MegaSuperAwsome Inc";
uaCustomer["CustomerID"] = "MEGAUA";
uaCustomer["Country"] = "Ukraine";
Console.WriteLine("RecordSet rows: update={0} delete={1} insert={2}", updateRows, deleteRows, 1);
dbAdapter.Connection.Open();
using (var tr = dbAdapter.Connection.BeginTransaction()) {
dbAdapter.Transaction = tr; // associate transaction for adapter commands
try {
dbAdapter.Update("Customers", customersRS);
Console.WriteLine("Customers count={0}", dbAdapter.Select(new Query("Customers").Select(QField.Count)).Single <int>());
// lets throw an exception
throw new Exception("Rollback Test!");
tr.Commit();
} catch (Exception ex) {
Console.WriteLine("Exception is thrown: {0}", ex.ToString());
tr.Rollback();
} finally {
dbAdapter.Transaction = null;
dbAdapter.Connection.Close();
}
}
}