A walkthrough the Jasper API with IronPython - Part 1

This blog post is part one of my walkthrough of the Jasper API in IronPython.  In our Channel 9 interview, I briefly demo'ed some of this walkthrough.  Hoping also to get a screen cast of this at some point. 

To run this sample I use for the walkthrough you will need to install a few things:

1. Download and install the Jasper CTP release.  This has the prerequisite that you must have Visual Studio Code name "Orcas" Beta1 installed.
2. Download and install IronPython 1.1.
3. You must also have SQL Server Express or any other SQL Server 2005 edition installed.

One thing to keep in mind is this API walkthrough is more about the code you don't have to write then the code I will show.  In other words, (with a few interesting exceptions) a lot of the API surface area you will see will seem like standard O/R code, however - there is no config files, no mapping files, no code gen, no tools, no wizards, no nothing.  Just step up and start writing code to access your database.

For the walkthrough, I am going to utilize the interactive console that shipped with IronPython 1.1 called ipy.exe.  You should be able to find this in the IronPython-1.1 directory of the IronPython installation directory.  If you have never tried out a REPL environment like this, it's going to feel very old school DOS command prompt like at first.   However, it is a wonderful way to examine and play around new APIs and an excellent way to develop little scripts in a iterative manner.  In fact, one could imagine developing apps in Visual Studio in more iterative manner using an interactive console as part of the IDE.  But that is another topic for another time.

About the only thing you need to understand about the interactive console is that when you enter a expression and it is evaluated instantly.  The cool thing is any state of the running program is preserved so I can basically run through an entire program line by line debugging it as I go.

Also, if you don't know Python, it probably isn't a big deal either.  For anyone who knows C# or VB, Python code will seem very familiar. 

To start with, we need to fire up the IronPython RELP environment by calling the following via a command prompt:

 ipy -X:TabCompletion -X:ColorfulConsole

The options get you some nice intellisense-like support and some nice font colors. Once you do this, you should see something like the following:

 IronPython 1.1 (1.1) on .NET 2.0.50727.1318
Copyright (c) Microsoft Corporation. All rights reserved.
>>>

 Here is a screen shot:

Note - I have selected a nice web 2.0 background color (a nice solid shade of green) to give the experience a modern feel because of the afore mention old school command prompt feel.  Doing so is purely optional, but highly advised.

Next you must import the clr into the Python environment:

 >>> import clr

Now, create a reference to the Jasper assembly:

 >>> clr.AddReferenceToFileAndPath("C:\\Program Files\\Microsoft Codename Jasper CTP\\Binaries\\Microsoft.Jasper.CTP.dll")

(Note - if the Jasper dll is gac'ed, all could replace the above line with clr.AddReference("Microsoft.Jasper.CTP"))

Finally, import all the types from the Jasper namespace:

 >>> from Microsoft.Jasper import *

Now we can actually start working with the API, the first thing to do is create a DynamicContext.  To do this, define the following connection string:

 >>> connString = "Provider='System.Data.SqlClient';Provider Connection String='Initial Catalog=Northwind;

    Data Source=localhost;Integrated Security=SSPI;';Generate Default EDM=True"

For the remainder of the demo, I will be using the Sql Server Northwind sample database.  If you don't have it installed, you can use the following download to get the download the Northwind sample database.

Now create the DynamicContext:

 >>> nw = DynamicContext.CreateDynamicContext(connString)

This might take a few moments.  Essentially what is happening is that the Jasper framework is opening a connection to the database and querying the system catalogues to find out what tables exist and what are the PK-FK relationships are between those tables.  From that meta data a default EDM (Entity Data Model) is generated.  With the default EDM, the Jasper framework then generates runtime types to represent the underlying EDM in terms of the CLR.  In addition, a DynamicContext type is created to track runtime instances and as a entry point to accessing the database.   An instance of this DynamicContext is returned from the CreateDynamicContext method.

First thing lets do is grab a single entity - in this case the first product:

 >>> product = nw.Products[0]

The Products property on the DynamicContext is actually a Query object.  This Query object can be queried via Linq or Entity Sql.  It can also be accessed as a collection as I am doing here.  I can now access properties of the Product instance:

 >>> print product.ProductName
Chai
>>> print product.Discontinued
False

I can even get the type of the product instance:

 >>> print type(product)
<type 'Product'>

In this case, each Product instance represents a row in the Products table where the properties of Product.  Note - this is using the default EDM generation mechanism where a Entity Type is created for each table in the database.  Further, each Entity Type has properties based on the columns in the table.  Also, note that the framework has done some basic plurization fix up, i.e. the Products table maps to the Product type.  This naming and mapping is fully customizable and designed to support legacy and complex databases, but more on that later.

Next, I am going to navigate to another entity, in this case the Category for the Product:

 >>> category = product.Category
>>> print type(category)
<type 'Category'>
>>> print category.CategoryName
Beverages
>>> print category.Products.Count()
12

A couple of interesting things to note here, in addition to creating runtime types to represent database tables, Jasper has also created relationships between the types based on the PK-FK relationship from the database.  Since there is a single Category for a Product, each Product has a Category reference.  On the other hand, each Category has many Products so each Category instance has a collection of products. Also, notice the non-trivial plurization - Jasper created a Category Type for the Categories table.

More useful then simple navigation is the ability to run queries.  For the initial release of Jasper, query is supported in three ways:

1.  Building up queries through the Entity Sql query builder API.

2.  Direct Entity Sql queries.

3.  Linq queries

Unfortunately, Linq is not supported in IronPython 1.1, so I can not demo this, however I can show the first two.  If you are unfamiliar with Entity Sql, check out the online documentation.

First, lets build up a simple query via the query builder.  As I said before, the properties on the DynamicContext represent Query instances.  On these Query instance one can call the query builder API (i.e. Where, OrderBy, Select, etc) where each method returns a new Query object.  You can then execute the query by iterating through the results:

 >>> products = nw.Products.Where("it.UnitPrice > 25.0 and it.Category.CategoryID = 4")
>>> for p in products : p.ProductName, p.UnitPrice, p.Category.CategoryID
...
('Queso Manchego La Pastora', 38.0000, 4)
('Mascarpone Fabioli', 32.0000, 4)
('Raclette Courdavault', 55.0000, 4)
('Camembert Pierrot', 34.0000, 4)
('Gudbrandsdalsost', 36.0000, 4)
('Mozzarella di Giovanni', 34.8000, 4)

One of the cool things about Python is the Tuple type. Essentially, any expression that returns more then more then one value returns a Tuple.  The easiest way to think about the is rows in a database table.  Above, I have created a sequence of Tuples instead of printing out each individual value.

Next, I am going to make the above query a little more complicated by ordering the results:

 >>> products = nw.Products.Where("it.UnitPrice > 25.0 and it.Category.CategoryID = 6").OrderBy("it.UnitPrice")
>>> for p in products : p.ProductName, p.UnitPrice, p.Category.CategoryID
...
('Perth Pasties', 32.8000, 6)
('Alice Mutton', 39.0000, 6)
('Mishi Kobe Niku', 97.0000, 6)
(u'Th\xfcringer Rostbratwurst', 123.7900, 6)

Queries can also executed against nested collections, not just the top level DynamicContext Queries:

 >>> order = nw.Orders.Where("it.ShipName = 'Vins et alcools Chevalier'").First()
>>> orderDetail = order.OrderDetails.Where("it.Product.ProductName = 'Queso Cabrales'").First()
>>> orderDetail.Product.ProductName
'Queso Cabrales'
>>> orderDetail.Discount
0.0

Note, each nested collection is also a Query object - the only difference is the results are filtered by the parent object.  In the example above, the query for the OrderDetails is only for the first Order which matches the specified ShipName.

it is also possible to do queries directly against the EDM via a direct query mechanism.  In the case that Generate Default EDM=True was specified, this is essentially a sql query directly against the back end database:

 >>> orders = nw.ExecuteDirectQuery("Select Top(10) o.ShipCity, SqlServer.COUNT(o.ShipCity) as OrderCount from Orders 

    as o group by o.ShipCity")

 
>>> for o in orders: o.ShipCity, o.OrderCount
...
('Aachen', 6)
('Albuquerque', 18)
('Anchorage', 10)
(u'\xc5rhus', 11)
('Barcelona', 5)
('Barquisimeto', 14)
('Bergamo', 10)
('Berlin', 6)
('Bern', 8)
('Boise', 31)

Even for adhoc queries and projections, the Jasper framework generates a runtime type with Properties for all the values in the projection.  The only difference is that these instances are immutable versus the instances representing entities which can be modified.

So, that is a quick walkthrough creating a DynamicContext, navigating around the runtime objects, and querying the store.  In part two of this walkthrough, I will examine doing CUD (Create, Update, Delete) operations, calling stored procedures, modifying the underlying database on the fly, and customization.

Comments

• Anonymous
  May 11, 2007
  Pingback from http://oakleafblog.blogspot.com/2007/05/using-entity-framework-with-ironpython.html
• Anonymous
  May 30, 2008
  This blog post is part one of my walkthrough of the Jasper API in IronPython. In our Channel 9 interview , I briefly demo'ed some of this walkthrough. Hoping also to get a screen cast of this at some point. To run this sample I use for the walkthroug
• Anonymous
  June 05, 2008
  This blog post is part one of my walkthrough of the Jasper API in IronPython. In our Channel 9 interview , I briefly demo'ed some of this walkthrough. Hoping also to get a screen cast of this at some point. To run this sample I use for the walkthroug