In the previous chapter, you learned how to create mappings for DynamicObjects. Elementary provides the option to automatically generate these mappings at run-time by doing reverse engineering the database schema. This allows you to read and write to the database without any configuration. Let's look at the most simple possible mapping you could have.
Using attributes:
[assembly:ElementaryDatabase(Name="Test", CreateDynamicClasses=true,
ConnectionString="Data Source=localhost; Initial Catalog=northwind; User ID=sa; Password=; Trusted_Connection=false;",
OledbConnectionString="Provider=sqloledb;Data Source=localhost; Initial Catalog=northwind; User ID=sa; Password=;")]
Using Xml:
<?xml version="1.0" encoding="utf-8"?>
<settings>
<database name="northwind"
connectionString="Data Source=localhost; Initial Catalog=northwind; User ID=sa; Password=; Trusted_Connection=false;"
oledbConnectionString="Provider=sqloledb;Data Source=localhost; Initial Catalog=northwind; User ID=sa; Password=;"
createDynamicClasses="true">
</database>
</settings>It doesn't get much simpler than that! Now, let's load it and see what happens. First, let's create a test to load the Elementary file and see the output (you could also load the assembly to use the attribute-based approach):
using System;
using System.Collections.Generic;
using System.Transactions;
using Adapdev;
using Elementary;
using Elementary.Northwind.Samples;
using NUnit.Framework;
namespace Elementary.Northwind.Samples.DynamicObjectsAutomatically
{
/// <summary>
/// Summary description for Test.
/// </summary>
[TestFixture]
public class Test
{
[TestFixtureSetUp]
public void SetUp()
{
Elementary.Clear();
Elementary.LoadMappings(@"..\..\DynamicObjectsAutomatically\Elementary.config");
}
[Test]
public void ViewLoadLog()
{
// Display the full load and validation log
Console.WriteLine(Elementary.LoadLog);
}
}
}
Here's the output:
Building default file. Unable to locate file: C:\Projects\Elementary\trunk\src\Elementary.Northwind.Samples\bin\Debug\Elementary.Mappings.xml Building ..\..\DynamicObjectsAutomatically\Elementary.config Loading database: northwind AutoRetrieveSchema: True Create Dynamic Classes: True Create Schema: False Connection String: Data Source=localhost; Initial Catalog=northwind; User ID=sa; Password=; Trusted_Connection=false; OleDb Connection String: Provider=sqloledb;Data Source=localhost; Initial Catalog=northwind; User ID=sa; Password=; Validate Connection: False Validate OleDb Connection: False Validate Schema: False Loading dynamic table: alphabetical list of products Loading dynamic column: categoryid Loading dynamic column: categoryname Loading dynamic column: discontinued Loading dynamic column: productid Loading dynamic column: productname Loading dynamic column: quantityperunit Loading dynamic column: reorderlevel Loading dynamic column: supplierid Loading dynamic column: unitprice Loading dynamic column: unitsinstock Loading dynamic column: unitsonorder Loading dynamic table: categories Loading dynamic column: categoryid (PK) Loading dynamic join: categoriesproducts Loading dynamic join table: categories - categoryid Loading dynamic join table: products - categoryid Loading dynamic column: categoryname Loading dynamic column: description Loading dynamic column: picture Loading dynamic table: category sales for 1997 Loading dynamic column: categoryname Loading dynamic column: categorysales Loading dynamic table: current product list Loading dynamic column: productid Loading dynamic column: productname Loading dynamic table: customer and suppliers by city Loading dynamic column: city Loading dynamic column: companyname Loading dynamic column: contactname Loading dynamic column: relationship Loading dynamic table: customercustomerdemo Loading dynamic column: customerid (PK) (FK) Loading dynamic column: customertypeid (PK) (FK) Loading dynamic table: customerdemographics Loading dynamic column: customerdesc Loading dynamic column: customertypeid (PK) Loading dynamic join: customerdemographicscustomercustomerdemo Loading dynamic join table: customerdemographics - customertypeid Loading dynamic join table: customercustomerdemo - customertypeid Loading dynamic table: customers Loading dynamic column: address Loading dynamic column: city Loading dynamic column: companyname Loading dynamic column: contactname Loading dynamic column: contacttitle Loading dynamic column: country Loading dynamic column: customerid (PK) Loading dynamic join: customerscustomercustomerdemo Loading dynamic join table: customers - customerid Loading dynamic join table: customercustomerdemo - customerid Loading dynamic join: customersorders Loading dynamic join table: customers - customerid Loading dynamic join table: orders - customerid Loading dynamic column: fax Loading dynamic column: phone Loading dynamic column: postalcode Loading dynamic column: region Loading dynamic table: employees Loading dynamic column: address Loading dynamic column: birthdate Loading dynamic column: city Loading dynamic column: country Loading dynamic column: employeeid (PK) Loading dynamic join: employeesemployees Loading dynamic join table: employees - employeeid Loading dynamic join table: employees - reportsto Loading dynamic join: employeesemployeeterritories Loading dynamic join table: employees - employeeid Loading dynamic join table: employeeterritories - employeeid Loading dynamic join: employeesorders Loading dynamic join table: employees - employeeid Loading dynamic join table: orders - employeeid Loading dynamic column: extension Loading dynamic column: firstname Loading dynamic column: hiredate Loading dynamic column: homephone Loading dynamic column: lastname Loading dynamic column: notes Loading dynamic column: photo Loading dynamic column: photopath Loading dynamic column: postalcode Loading dynamic column: region Loading dynamic column: reportsto (FK) Loading dynamic column: title Loading dynamic column: titleofcourtesy Loading dynamic table: employeeterritories Loading dynamic column: employeeid (PK) (FK) Loading dynamic column: territoryid (PK) (FK) Loading dynamic table: invoices Loading dynamic column: address Loading dynamic column: city Loading dynamic column: country Loading dynamic column: customerid Loading dynamic column: customername Loading dynamic column: discount Loading dynamic column: extendedprice Loading dynamic column: freight Loading dynamic column: orderdate Loading dynamic column: orderid Loading dynamic column: postalcode Loading dynamic column: productid Loading dynamic column: productname Loading dynamic column: quantity Loading dynamic column: region Loading dynamic column: requireddate Loading dynamic column: salesperson Loading dynamic column: shipaddress Loading dynamic column: shipcity Loading dynamic column: shipcountry Loading dynamic column: shipname Loading dynamic column: shippeddate Loading dynamic column: shippername Loading dynamic column: shippostalcode Loading dynamic column: shipregion Loading dynamic column: unitprice Loading dynamic table: order details Loading dynamic column: discount Loading dynamic column: orderid (PK) (FK) Loading dynamic column: productid (PK) (FK) Loading dynamic column: quantity Loading dynamic column: unitprice Loading dynamic table: order details extended Loading dynamic column: discount Loading dynamic column: extendedprice Loading dynamic column: orderid Loading dynamic column: productid Loading dynamic column: productname Loading dynamic column: quantity Loading dynamic column: unitprice Loading dynamic table: order subtotals Loading dynamic column: orderid Loading dynamic column: subtotal Loading dynamic table: orders Loading dynamic column: customerid (FK) Loading dynamic column: employeeid (FK) Loading dynamic column: freight Loading dynamic column: orderdate Loading dynamic column: orderid (PK) Loading dynamic join: ordersorder details Loading dynamic join table: orders - orderid Loading dynamic join table: order details - orderid Loading dynamic column: requireddate Loading dynamic column: shipaddress Loading dynamic column: shipcity Loading dynamic column: shipcountry Loading dynamic column: shipname Loading dynamic column: shippeddate Loading dynamic column: shippostalcode Loading dynamic column: shipregion Loading dynamic column: shipvia (FK) Loading dynamic table: orders qry Loading dynamic column: address Loading dynamic column: city Loading dynamic column: companyname Loading dynamic column: country Loading dynamic column: customerid Loading dynamic column: employeeid Loading dynamic column: freight Loading dynamic column: orderdate Loading dynamic column: orderid Loading dynamic column: postalcode Loading dynamic column: region Loading dynamic column: requireddate Loading dynamic column: shipaddress Loading dynamic column: shipcity Loading dynamic column: shipcountry Loading dynamic column: shipname Loading dynamic column: shippeddate Loading dynamic column: shippostalcode Loading dynamic column: shipregion Loading dynamic column: shipvia Loading dynamic table: product sales for 1997 Loading dynamic column: categoryname Loading dynamic column: productname Loading dynamic column: productsales Loading dynamic table: products Loading dynamic column: categoryid (FK) Loading dynamic column: discontinued Loading dynamic column: productid (PK) Loading dynamic join: productsorder details Loading dynamic join table: products - productid Loading dynamic join table: order details - productid Loading dynamic column: productname Loading dynamic column: quantityperunit Loading dynamic column: reorderlevel Loading dynamic column: supplierid (FK) Loading dynamic column: unitprice Loading dynamic column: unitsinstock Loading dynamic column: unitsonorder Loading dynamic table: products above average price Loading dynamic column: productname Loading dynamic column: unitprice Loading dynamic table: products by category Loading dynamic column: categoryname Loading dynamic column: discontinued Loading dynamic column: productname Loading dynamic column: quantityperunit Loading dynamic column: unitsinstock Loading dynamic table: quarterly orders Loading dynamic column: city Loading dynamic column: companyname Loading dynamic column: country Loading dynamic column: customerid Loading dynamic table: region Loading dynamic column: regiondescription Loading dynamic column: regionid (PK) Loading dynamic join: regionterritories Loading dynamic join table: region - regionid Loading dynamic join table: territories - regionid Loading dynamic table: sales by category Loading dynamic column: categoryid Loading dynamic column: categoryname Loading dynamic column: productname Loading dynamic column: productsales Loading dynamic table: sales totals by amount Loading dynamic column: companyname Loading dynamic column: orderid Loading dynamic column: saleamount Loading dynamic column: shippeddate Loading dynamic table: shippers Loading dynamic column: companyname Loading dynamic column: phone Loading dynamic column: shipperid (PK) Loading dynamic join: shippersorders Loading dynamic join table: shippers - shipperid Loading dynamic join table: orders - shipvia Loading dynamic table: summary of sales by quarter Loading dynamic column: orderid Loading dynamic column: shippeddate Loading dynamic column: subtotal Loading dynamic table: summary of sales by year Loading dynamic column: orderid Loading dynamic column: shippeddate Loading dynamic column: subtotal Loading dynamic table: suppliers Loading dynamic column: address Loading dynamic column: city Loading dynamic column: companyname Loading dynamic column: contactname Loading dynamic column: contacttitle Loading dynamic column: country Loading dynamic column: fax Loading dynamic column: homepage Loading dynamic column: phone Loading dynamic column: postalcode Loading dynamic column: region Loading dynamic column: supplierid (PK) Loading dynamic join: suppliersproducts Loading dynamic join table: suppliers - supplierid Loading dynamic join table: products - supplierid Loading dynamic table: territories Loading dynamic column: regionid (FK) Loading dynamic column: territorydescription Loading dynamic column: territoryid (PK) Loading dynamic join: territoriesemployeeterritories Loading dynamic join table: territories - territoryid Loading dynamic join table: employeeterritories - territoryid Loading dynamic class: alphabetical list of products Loading dynamic property: categoryid Loading dynamic property: categoryname Loading dynamic property: discontinued Loading dynamic property: productid Loading dynamic property: productname Loading dynamic property: quantityperunit Loading dynamic property: reorderlevel Loading dynamic property: supplierid Loading dynamic property: unitprice Loading dynamic property: unitsinstock Loading dynamic property: unitsonorder Loading dynamic class: categories Loading dynamic property: categoryid Loading dynamic property: categoryname Loading dynamic property: description Loading dynamic property: picture Loading dynamic class: category sales for 1997 Loading dynamic property: categoryname Loading dynamic property: categorysales Loading dynamic class: current product list Loading dynamic property: productid Loading dynamic property: productname Loading dynamic class: customer and suppliers by city Loading dynamic property: city Loading dynamic property: companyname Loading dynamic property: contactname Loading dynamic property: relationship Loading dynamic class: customercustomerdemo Loading dynamic property: customerid Loading dynamic property: customertypeid Loading dynamic class: customerdemographics Loading dynamic property: customerdesc Loading dynamic property: customertypeid Loading dynamic class: customers Loading dynamic property: address Loading dynamic property: city Loading dynamic property: companyname Loading dynamic property: contactname Loading dynamic property: contacttitle Loading dynamic property: country Loading dynamic property: customerid Loading dynamic property: fax Loading dynamic property: phone Loading dynamic property: postalcode Loading dynamic property: region Loading dynamic class: employees Loading dynamic property: address Loading dynamic property: birthdate Loading dynamic property: city Loading dynamic property: country Loading dynamic property: employeeid Loading dynamic property: extension Loading dynamic property: firstname Loading dynamic property: hiredate Loading dynamic property: homephone Loading dynamic property: lastname Loading dynamic property: notes Loading dynamic property: photo Loading dynamic property: photopath Loading dynamic property: postalcode Loading dynamic property: region Loading dynamic property: reportsto Loading dynamic property: title Loading dynamic property: titleofcourtesy Loading dynamic class: employeeterritories Loading dynamic property: employeeid Loading dynamic property: territoryid Loading dynamic class: invoices Loading dynamic property: address Loading dynamic property: city Loading dynamic property: country Loading dynamic property: customerid Loading dynamic property: customername Loading dynamic property: discount Loading dynamic property: extendedprice Loading dynamic property: freight Loading dynamic property: orderdate Loading dynamic property: orderid Loading dynamic property: postalcode Loading dynamic property: productid Loading dynamic property: productname Loading dynamic property: quantity Loading dynamic property: region Loading dynamic property: requireddate Loading dynamic property: salesperson Loading dynamic property: shipaddress Loading dynamic property: shipcity Loading dynamic property: shipcountry Loading dynamic property: shipname Loading dynamic property: shippeddate Loading dynamic property: shippername Loading dynamic property: shippostalcode Loading dynamic property: shipregion Loading dynamic property: unitprice Loading dynamic class: order details Loading dynamic property: discount Loading dynamic property: orderid Loading dynamic property: productid Loading dynamic property: quantity Loading dynamic property: unitprice Loading dynamic class: order details extended Loading dynamic property: discount Loading dynamic property: extendedprice Loading dynamic property: orderid Loading dynamic property: productid Loading dynamic property: productname Loading dynamic property: quantity Loading dynamic property: unitprice Loading dynamic class: order subtotals Loading dynamic property: orderid Loading dynamic property: subtotal Loading dynamic class: orders Loading dynamic property: customerid Loading dynamic property: employeeid Loading dynamic property: freight Loading dynamic property: orderdate Loading dynamic property: orderid Loading dynamic property: requireddate Loading dynamic property: shipaddress Loading dynamic property: shipcity Loading dynamic property: shipcountry Loading dynamic property: shipname Loading dynamic property: shippeddate Loading dynamic property: shippostalcode Loading dynamic property: shipregion Loading dynamic property: shipvia Loading dynamic class: orders qry Loading dynamic property: address Loading dynamic property: city Loading dynamic property: companyname Loading dynamic property: country Loading dynamic property: customerid Loading dynamic property: employeeid Loading dynamic property: freight Loading dynamic property: orderdate Loading dynamic property: orderid Loading dynamic property: postalcode Loading dynamic property: region Loading dynamic property: requireddate Loading dynamic property: shipaddress Loading dynamic property: shipcity Loading dynamic property: shipcountry Loading dynamic property: shipname Loading dynamic property: shippeddate Loading dynamic property: shippostalcode Loading dynamic property: shipregion Loading dynamic property: shipvia Loading dynamic class: product sales for 1997 Loading dynamic property: categoryname Loading dynamic property: productname Loading dynamic property: productsales Loading dynamic class: products Loading dynamic property: categoryid Loading dynamic property: discontinued Loading dynamic property: productid Loading dynamic property: productname Loading dynamic property: quantityperunit Loading dynamic property: reorderlevel Loading dynamic property: supplierid Loading dynamic property: unitprice Loading dynamic property: unitsinstock Loading dynamic property: unitsonorder Loading dynamic class: products above average price Loading dynamic property: productname Loading dynamic property: unitprice Loading dynamic class: products by category Loading dynamic property: categoryname Loading dynamic property: discontinued Loading dynamic property: productname Loading dynamic property: quantityperunit Loading dynamic property: unitsinstock Loading dynamic class: quarterly orders Loading dynamic property: city Loading dynamic property: companyname Loading dynamic property: country Loading dynamic property: customerid Loading dynamic class: region Loading dynamic property: regiondescription Loading dynamic property: regionid Loading dynamic class: sales by category Loading dynamic property: categoryid Loading dynamic property: categoryname Loading dynamic property: productname Loading dynamic property: productsales Loading dynamic class: sales totals by amount Loading dynamic property: companyname Loading dynamic property: orderid Loading dynamic property: saleamount Loading dynamic property: shippeddate Loading dynamic class: shippers Loading dynamic property: companyname Loading dynamic property: phone Loading dynamic property: shipperid Loading dynamic class: summary of sales by quarter Loading dynamic property: orderid Loading dynamic property: shippeddate Loading dynamic property: subtotal Loading dynamic class: summary of sales by year Loading dynamic property: orderid Loading dynamic property: shippeddate Loading dynamic property: subtotal Loading dynamic class: suppliers Loading dynamic property: address Loading dynamic property: city Loading dynamic property: companyname Loading dynamic property: contactname Loading dynamic property: contacttitle Loading dynamic property: country Loading dynamic property: fax Loading dynamic property: homepage Loading dynamic property: phone Loading dynamic property: postalcode Loading dynamic property: region Loading dynamic property: supplierid Loading dynamic class: territories Loading dynamic property: regionid Loading dynamic property: territorydescription Loading dynamic property: territoryid Validating references... Checking database references... Checking class to table mappings... Done.
As you can see, it automatically retrieved the schema and loaded all of the tables and columns, to include relationships between tables. It then built up the class model with properties. Now, we can work with DynamicObjects just as if we'd actually hand written the classes.
[Test]
public void GetCollection()
{
IObjectMapper<DynamicObject> mapper = Elementary.GetIObjectMapper<DynamicObject>("products");
IList<DynamicObject> al = mapper.GetCollection();
Assert.IsTrue(al.Count > 0, "No products loaded.");
Assert.AreEqual(77, al.Count, "Not all products were loaded.");
Console.WriteLine("Loaded {0} products.", al.Count);
Console.WriteLine("\nFirst product:\n{0}", al[0]);
}Output:
Loaded 77 products.
First product:
[DynamicObject]
AssemblyName: Adapdev.Dynamic
FullName: northwind.products
Alias: products
unitsinstock: 39
reorderlevel: 10
productname: Chai
unitprice: 18.0000
supplierid: 1
categoryid: 1
productid: 1
discontinued: False
unitsonorder: 0
quantityperunit: 10 boxes x 20 bags