Monday, June 20, 2005 3:25 AM bart

Adventures in Comega - part 5 (database access)

A couple of months ago I've been posting about Comega (refer to http://community.bartdesmet.net/blogs/bart/category/40.aspx for the complete list of Comega-related stuff on my blog). So, I'm back in the Comega world and I hope you'll accompany me on this discovery tour :-). This time I'll be covering one of the key goals of Comega, that's filling the gap between various data formats: the relational model (RDBMS), the semi-structued model (XML) and objects (classes).

Introduction

Data access is one of the most historical things development languages and frameworks support. Without the notion of data and persistence there would be just a fraction of the applications we know today (just Windows 3.0 functionality maybe :-o). Currently, .NET supports data access to various kinds of data sources using ADO.NET and several data providers, including the SQL Server .NET native client (System.Data.SqlClient). A typical piece of code to gain access to the data in, let's use the Northwind Products table, looks like this:

using (SqlConnection conn = new SqlConnection(dsn))
{
     SqlCommand cmd = new SqlCommand("SELECT * FROM Products", conn);
     conn.Open();
     SqlDataReader reader = cmd.ExecuteReader();
     //...
}

ADO.NET also includes the notion of a strongly typed DataSet which tries to close the gap between on-line data and off-line data by means of a synchronization pattern called a DataProvider. Strongly-typed DataSets do a pretty good job in simplifying the task to manipulate data and performing some necessairy type-checking on the entered data:

NorthwindDataSet ds = new NorthwindDataSet();

using (SqlConnection conn = new SqlConnection(dsn))
{
     SqlCommand select = new SqlCommand("SELECT * FROM Products", conn);
     SqlDataAdapter adap = new SqlDataAdapter();
     adap.Connection = conn;
     adap.SelectCommand = select;
     //...
     adap.Fill(ds);
     foreach(NorthwindDataSet.ProductsRow row in ds.Products.Rows)
     {     
          //use things such as row.UnitPrice
     }
}

Please note I'm writing these pieces of code by head without copy-paste from the devenv. So, if there are any typos or syntactical mistakes, don't shoot please :-)

Now, what's the problem with this? Looks pretty good and relatively simple to use... Well, there still is a gap between the types from the database and the types used in the development framework. Although ADO.NET does a pretty good job to map these things (SqlDataType enumeration for example) and there are several tools to do this mapping (O/R-tools as we know them, think of Hibernate). But in the end, it still is a mapping. The language itself is not aware of what's happening and the compiler can't do much (not to say nothing) to check the integrity of mappings and the validity of passed parameters (e.g. in a parameterized SqlCommand). Comega knows the concept of relational data and allows developers to use SQL directly in the language, in a strongly typed fashion.

What about the database schema?

Sounds promising to have SQL support in the language directly. But there's still an unknown factor ... how can the language (and the compiler) know the structure of a database? More specifically, the schema of the database being used with information about fields and data types? This is where the tool Sql2Comega.exe appears on the scene. Let's show you how it works with the well-known Northwind database:

c:\program files\microsoft research\comega\Samples>sql2comega /server:localhost /catalog:northwind /trustedconnection

Of course you have to run this on the Comega command prompt that can be found through the Start-menu. In this sample I'm using a trusted connection with Windows authentication on the SQL Server machine. Alternatively you can use /userid and /password to use SQL authentication too. The result of this is a newly created assembly called Northwind.dll. Time to inspect what's going on in this assembly: ildasm.exe :-)

The generated data access assembly

So, open up the Northwind.dll file inside ildasm.exe, through the VS.NET 2003 Command Prompt. The first thing you'll see is a bunch of classes that should look familiar: Alphabeticallistofproducts, Categories, CategorySalesfor1997, CurrentProductList, and so on. These are in fact reflections of the tables, views and stored procedures from the Nortwhind database. Now, be prepared to be a little disappointed when you take a look at these classes. For example, the Categories class just contains:

.class public auto ansi beforefieldinit Categories
       extends [mscorlib]System.Object
{
} // end of class Categories

.field public valuetype [System.Data]System.Data.SqlTypes.SqlInt32 CategoryID
.custom instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlColumnAttribute::.ctor() = ( 01 00 03 00 53 02 0F 49 73 41 75 74 6F 49 6E 63   // ....S..IsAutoInc
                                                                                                       72 65 6D 65 6E 74 01 53 02 0A 49 73 49 64 65 6E   // rement.S..IsIden
                                                                                                       74 69 74 79 01 53 02 0A 49 73 52 65 61 64 4F 6E   // tity.S..IsReadOn
                                                                                                       6C 79 01 )                                        // ly.
.field public valuetype [System.Data]System.Data.SqlTypes.SqlString CategoryName
.custom instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlColumnAttribute::.ctor() = ( 01 00 01 00 53 08 0A 43 6F 6C 75 6D 6E 53 69 7A   // ....S..ColumnSiz
                                                                                                       65 0F 00 00 00 )                                  // e....
.field public valuetype [System.Data]System.Data.SqlTypes.SqlString Description
.custom instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlColumnAttribute::.ctor() = ( 01 00 04 00 53 02 0B 41 6C 6C 6F 77 44 42 4E 75   // ....S..AllowDBNu
                                                                                                       6C 6C 01 53 0E 0D 42 61 73 65 54 61 62 6C 65 4E   // ll.S..BaseTableN
                                                                                                       61 6D 65 0A 43 61 74 65 67 6F 72 69 65 73 53 08   // ame.CategoriesS.
                                                                                                       0A 43 6F 6C 75 6D 6E 53 69 7A 65 FF FF FF 3F 53   // .ColumnSize...?S
                                                                                                       02 06 49 73 4C 6F 6E 67 01 )                      // ..IsLong.
.field public valuetype [System.Data]System.Data.SqlTypes.SqlBinary Picture
.custom instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlColumnAttribute::.ctor() = ( 01 00 04 00 53 02 0B 41 6C 6C 6F 77 44 42 4E 75   // ....S..AllowDBNu
                                                                                                       6C 6C 01 53 0E 0D 42 61 73 65 54 61 62 6C 65 4E   // ll.S..BaseTableN
                                                                                                       61 6D 65 0A 43 61 74 65 67 6F 72 69 65 73 53 08   // ame.CategoriesS.
                                                                                                       0A 43 6F 6C 75 6D 6E 53 69 7A 65 FF FF FF 7F 53   // .ColumnSize....S
                                                                                                       02 06 49 73 4C 6F 6E 67 01 )                      // ..IsLong.
.method public hidebysig specialname rtspecialname
        instance void  .ctor() cil managed
{
  // Code size       7 (0x7)
  .maxstack  1
  IL_0000:  ldarg.0
  IL_0001:  call       instance void [mscorlib]System.Object::.ctor()
  IL_0006:  ret
} // end of method Categories::.ctor

Recall I said a little disappointed. There's no real data access stuff going in out there. It's just a couple of fields with types from System.Data.SqlTypes that are publicly visible together with a simple constructor. The only interesting stuff so far is the usage of SqlColumnAttribute as an attribute that's tagging the various fields out there. So, the assembly seems to know everything about the underlying database structure: field types, allow null columns, column sizes, etc. But the data access itself is not visible (for the moment). One thing you should notice however is that these attributes are inside the namespace System.Compiler.Sql, which tells us that the compiler is indeed aware of SQL.

There should be more, isn't it? And yes, there is and it's called the class Database. The class descriptor in IL looks promising:

.class public auto ansi beforefieldinit Database
       extends [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlDatabase
{
  .custom instance void [System.Compiler.Runtime]System.Compiler.ComposerAttribute::.ctor() = ( 01 00 02 00 53 0E 0C 41 73 73 65 6D 62 6C 79 4E   // ....S..AssemblyN
                                                                                                61 6D 65 59 53 79 73 74 65 6D 2E 43 6F 6D 70 69   // ameYSystem.Compi
                                                                                                6C 65 72 2E 53 71 6C 2C 20 56 65 72 73 69 6F 6E   // ler.Sql, Version
                                                                                                3D 31 2E 30 2E 34 32 32 30 2E 30 2C 20 43 75 6C   // =1.0.4220.0, Cul
                                                                                                74 75 72 65 3D 6E 65 75 74 72 61 6C 2C 20 50 75   // ture=neutral, Pu
                                                                                                62 6C 69 63 4B 65 79 54 6F 6B 65 6E 3D 37 33 36   // blicKeyToken=736
                                                                                                34 34 30 63 39 62 34 31 34 65 61 31 36 53 0E 08   // 440c9b414ea16S..
                                                                                                54 79 70 65 4E 61 6D 65 1F 53 79 73 74 65 6D 2E   // TypeName.System.
                                                                                                43 6F 6D 70 69 6C 65 72 2E 53 71 6C 2E 53 71 6C   // Compiler.Sql.Sql
                                                                                                43 6F 6D 70 6F 73 65 72 )                         // Composer
  .custom instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlDatabaseAttribute::.ctor() = ( 01 00 01 00 53 0E 0C 44 61 74 61 62 61 73 65 4E   // ....S..DatabaseN
                                                                                                           61 6D 65 09 6E 6F 72 74 68 77 69 6E 64 )          // ame.northwind
  .custom instance void [System.Compiler.Runtime]System.Compiler.AnonymousAttribute::.ctor() = ( 01 00 00 00 )
} // end of class Database

This class derives from System.Compiler.Sql.SqlDatabase, so we can assume this base class forms the bridge between the self-written code and the database in a transparent way. Let's take a closer look and start with the constructors:

.method public hidebysig specialname rtspecialname
        instance void  .ctor(string connectionString) cil managed
{
  // Code size       8 (0x8)
  .maxstack  2
  IL_0000:  ldarg.0
  IL_0001:  ldarg.1
  IL_0002:  call       instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlDatabase::.ctor(string)
  IL_0007:  ret
} // end of method Database::.ctor

.method public hidebysig specialname rtspecialname
        instance void  .ctor(class [System.Data]System.Data.SqlClient.SqlConnection connection) cil managed
{
  // Code size       8 (0x8)
  .maxstack  2
  IL_0000:  ldarg.0
  IL_0001:  ldarg.1
  IL_0002:  call       instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlDatabase::.ctor(class [System.Data]System.Data.SqlClient.SqlConnection)
  IL_0007:  ret
} // end of method Database::.ctor

.method public hidebysig specialname rtspecialname
        instance void  .ctor() cil managed
{
  // Code size       7 (0x7)
  .maxstack  1
  IL_0000:  ldarg.0
  IL_0001:  call       instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlDatabase::.ctor()
  IL_0006:  ret
} // end of method Database::.ctor

So, you can create an instance of the Database class by calling a constructor that takes the connection (string or object) to the database, just as we expect. The empty constructor will use the application configuration file to obtain the connection string. Note there's a static constructor too (not displayed in the code snippet above) that is used to initialize a static field called DB to gain access to the database directly (using the default connection string).

What about the fields? Well, for every table in the database you'll find something like this:

.field private class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlTable _Categories

Pretty straightforward to understand in my opinion. Next, there's some more complicated stuff to gain access to the tables by means of getters (of properties, but translated into methods on the IL level):

.method public hidebysig specialname instance class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlTable
        get_Categories() cil managed
{
  // Code size       42 (0x2a)
  .maxstack  5
  IL_0000:  ldarg.0
  IL_0001:  ldfld      class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlTable northwind.Database::_Categories
  IL_0006:  brtrue.s   IL_0023
  IL_0008:  ldarg.0
  IL_0009:  ldarg.0
  IL_000a:  ldstr      "Categories"
  IL_000f:  ldtoken    northwind.Categories
  IL_0014:  call       class [mscorlib]System.Type [mscorlib]System.Type::GetTypeFromHandle(valuetype [mscorlib]System.RuntimeTypeHandle)
  IL_0019:  newobj     instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlTable::.ctor(class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlDatabase,
                                                                                                      string,
                                                                                                      class [mscorlib]System.Type)
  IL_001e:  stfld      class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlTable northwind.Database::_Categories
  IL_0023:  ldarg.0
  IL_0024:  ldfld      class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlTable northwind.Database::_Categories
  IL_0029:  ret
} // end of method Database::get_Categories

Here some magic is happening. The SqlTable constructor is called with three parameters: the database class isntance, a string pointing to the table to be queried and an additional type. What you should notice in here is that the derived class is hiding all internal details of the database communication and get_Categories is just returning a (generic?) SqlTable instance. It's the further responsibility of the language to hide the pseudo-generic nature of this property by wrapping calls to it in the SQL keywords that are supported by the language (see further on).

And last but not least, some other stuff in there of which the most significant parts are the attributes used by the compiler to recognize table names etc:

.property instance class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlTable
        Categories()
{
  .custom instance void [System.Compiler.Runtime]System.Compiler.ElementTypeAttribute::.ctor() = ( 01 00 01 00 53 50 0B 45 6C 65 6D 65 6E 74 54 79   // ....SP.ElementTy
                                                                                                   70 65 14 6E 6F 72 74 68 77 69 6E 64 2E 43 61 74   // pe.northwind.Cat
                                                                                                   65 67 6F 72 69 65 73 )                            // egories
  .custom instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlTableAttribute::.ctor() = ( 01 00 01 00 53 0E 09 54 61 62 6C 65 4E 61 6D 65   // ....S..TableName
                                                                                                        0A 43 61 74 65 67 6F 72 69 65 73 )                // .Categories
  .get instance class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlTable northwind.Database::get_Categories()
} // end of property Database::Categories

So, the real database access code is completely invisible in here. It's just "redirected" to the base class that does the real work. However, the assembly acts as a bridge between the database access and the compiler in a strongly typed fashion, by means of attributes.

How to use it?

So, we ran the Sql2Comega.exe tool from the command-line to obtain a "data access assembly" used by the compiler. Now it's time to take a look at the language constructs that make the usage of the data (in various ways) possible. Before I continue, I have to point out that the Sql2Comega tool can also be used directly in Visual Studio .NET after the installation of Comega:

  1. Create a new Comega project (console application).
  2. Right-click on References in the "Solution Explorer" and choose "Add Database Schema..."
  3. In the "Comega SQL Generator" dialog enter the parameters for the connection, click Next, and specify a name for the assembly and click Build.

Okay, time to write some little sample code:

using System;
using
Northwind;

public
class Test
{
     static void
Main()
    
{
          string
dsn = "...";
          Database db =
new
Database(dsn);
          foreach (row in select * from
db.Products)
               Console.WriteLine(row.ProductName);
     }
}

Wow, that's cool, isn't it? We do have strongly typed access to the database indeed and it's possible to iterate over the data in a flexible way. Although this looks pretty simple on the surface, the engine behind it is fairly complex. Ildasm.exe will show us how it works (once again). But I must warn you, what comes is for geeks only :-). The first thing to investigate is the Main method:

.method private hidebysig static void  Main() cil managed
{
  .entrypoint
  // Code size       129 (0x81)
  .maxstack  14
  .locals init ([0] class Test/'closure:13515' V_0,
           [1] class System.Collections.Generic.'IEnumerable<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' V_1,
           [2] valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued V_2)
  IL_0000:  newobj     instance void Test/'closure:13515'::'.ctor$PST06000028'()
  IL_0005:  stloc.0
  IL_0006:  ldloc.0
  IL_0007:  ldstr      "..."
  IL_000c:  stfld      string Test/'closure:13515'::dsn$PST04000012
  IL_0011:  ldloc.0
  IL_0012:  ldloc.0
  IL_0013:  ldfld      string Test/'closure:13515'::dsn$PST04000012
  IL_0018:  newobj     instance void [Northwind]Northwind.Database::.ctor(string)
  IL_001d:  stfld      class [Northwind]Northwind.Database Test/'closure:13515'::db$PST04000013
  IL_0022:  ldloc.0
  IL_0023:  call       instance class System.Collections.Generic.'IEnumerable<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' Test/'closure:13515'::'select:13549'()
  IL_0028:  stloc.1
  IL_0029:  ldloc.1
  IL_002a:  brfalse    IL_0080
  IL_002f:  ldloc.0
  IL_0030:  ldloc.1
  IL_0031:  callvirt   instance class System.Collections.Generic.'IEnumerator<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' System.Collections.Generic.'IEnumerable<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>'::GetEnumerator()
  IL_0036:  stfld      class System.Collections.Generic.'IEnumerator<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' Test/'closure:13515'::'foreachEnumerator: 2581$PST04000014'
  IL_003b:  ldloc.0
  IL_003c:  ldfld      class System.Collections.Generic.'IEnumerator<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' Test/'closure:13515'::'foreachEnumerator: 2581$PST04000014'
  IL_0041:  brfalse    IL_0080
  IL_0046:  ldloc.0
  IL_0047:  ldfld      class System.Collections.Generic.'IEnumerator<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' Test/'closure:13515'::'foreachEnumerator: 2581$PST04000014'
  IL_004c:  callvirt   instance bool System.Collections.Generic.'IEnumerator<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>'::MoveNext()
  IL_0051:  brfalse    IL_0080
  IL_0056:  ldloc.0
  IL_0057:  ldfld      class System.Collections.Generic.'IEnumerator<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' Test/'closure:13515'::'foreachEnumerator: 2581$PST04000014'
  IL_005c:  callvirt   instance valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued System.Collections.Generic.'IEnumerator<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>'::get_Current()
  IL_0061:  stloc.2
  IL_0062:  ldloc.0
  IL_0063:  ldloc.2
  IL_0064:  stfld      valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued Test/'closure:13515'::row
  IL_0069:  ldloc.0
  IL_006a:  ldflda     valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued Test/'closure:13515'::row
  IL_006f:  ldfld      valuetype [System.Data]System.Data.SqlTypes.SqlString StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued::ProductName
  IL_0074:  box        [System.Data]System.Data.SqlTypes.SqlString
  IL_0079:  call       void [mscorlib]System.Console::WriteLine(object)
  IL_007e:  br.s       IL_0046
  IL_0080:  ret
} // end of method Test::Main

The first thing you can see is the name of the type being used to represent a single row: StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued. Well, euhm, it should have a unique name, right? Other things you can find while analyzing this code are of course the loop construct (IL_0046..IL_007e) and the generated "closure class" that we met in the previous episodes too when talking about iterators etc. So, we should find a class closure:13515 in the assembly too, and yes here it is. Beside some fields to gain access to the database, the dsn, the enumerator and the rows:

.field privatescope class [Northwind]Northwind.Database db$PST04000013
.field privatescope string dsn$PST04000012
.field privatescope specialname class System.Collections.Generic.'IEnumerator<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' 'foreachEnumerator: 2581$PST04000014'
.field public valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued row

there is a select-operation too:

.method assembly instance class System.Collections.Generic.'IEnumerable<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>'
        'select:13549'() cil managed
{
  // Code size       24 (0x18)
  .maxstack  2
  .locals init ([0] class Test/'closure:13515'/'closure:13566' V_0,
           [1] class System.Collections.Generic.'IEnumerable<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' 'return value',
           [2] class System.Collections.Generic.'IEnumerable<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>' 'Display Return Local')
  IL_0000:  newobj     instance void Test/'closure:13515'/'closure:13566'::'.ctor$PST0600002A'()
  IL_0005:  stloc.0
  IL_0006:  ldloc.0
  IL_0007:  ldarg.0
  IL_0008:  stfld      class Test/'closure:13515' Test/'closure:13515'/'closure:13566'::'this value: $PST04000016'
  IL_000d:  ldloc.0
  IL_000e:  stloc.1
  IL_000f:  br         IL_0014
  IL_0014:  ldloc.1
  IL_0015:  stloc.2
  IL_0016:  ldloc.1
  IL_0017:  ret
} // end of method 'closure:13515'::'select:13549'

This is the place where we meet another closure class, embedded in the first one as an inner class, with the following definition:

.class auto ansi sealed nested private specialname 'closure:13566'
       extends [mscorlib]System.Object
       implements [mscorlib]System.Collections.IEnumerable,
                  System.Collections.Generic.'IEnumerator<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>',
                  [mscorlib]System.Collections.IEnumerator,
                  [mscorlib]System.IDisposable,
                  System.Collections.Generic.'IEnumerable<StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued>'
{
} // end of class 'closure:13566'

So, this class implements an enumerator of the type-with-the-long-name. More interesting is to see the System.Data.SqlClient appear in here (*happy*):

.field private valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued 'current Value'
.field privatescope specialname class [System.Data]System.Data.SqlClient.SqlDataReader 'var: 6$PST04000019'

The only thing that's still missing is how the "current value" is updated. As you know, an enumerator has the method MoveNext, so let's take a look over there (dropped irrelevant lines):

.method public virtual instance bool  MoveNext() cil managed
{
  // Code size       399 (0x18f)
  .maxstack  26
  .locals init ([0] class Test/'closure:13515'/'closure:13566' 'Closure Class Local13549',
           [1] class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlQuery V_1,
           [2] object[] V_2,
           [3] valuetype [System.Data]System.Data.SqlTypes.SqlInt32 V_3,
           [4] valuetype [System.Data]System.Data.SqlTypes.SqlString V_4,

           [5] valuetype [System.Data]System.Data.SqlTypes.SqlInt32 V_5,
           [6] valuetype [System.Data]System.Data.SqlTypes.SqlInt32 V_6,
           [7] valuetype [System.Data]System.Data.SqlTypes.SqlString V_7,
           [8] valuetype [System.Data]System.Data.SqlTypes.SqlMoney V_8,
           [9] valuetype [System.Data]System.Data.SqlTypes.SqlInt16 V_9,
           [10] valuetype [System.Data]System.Data.SqlTypes.SqlInt16 V_10,
           [11] valuetype [System.Data]System.Data.SqlTypes.SqlInt16 V_11,
           [12] valuetype [System.Data]System.Data.SqlTypes.SqlBoolean V_12)
  IL_0000:  ldarg.0
  IL_0001:  stloc.0
  IL_0002:  ldarg.0
  IL_0003:  ldfld      int32 Test/'closure:13515'/'closure:13566'::'current Entry Point: '
  IL_0008:  switch     (
                        IL_0015,
                        IL_017d)
  IL_0015:  ldstr      "select Products.ProductID, Products.ProductName, P"
  + "roducts.SupplierID, Products.CategoryID, Products.QuantityPerUnit, Prod"
  + "ucts.UnitPrice, Products.UnitsInStock, Products.UnitsOnOrder, Products."
  + "ReorderLevel, Products.Discontinued from Products as Products"
  IL_001a:  ldnull
  IL_001b:  newobj     instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlDefinition::.ctor(string,
                                                                                                           class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlParameterDefinition[])
  IL_0020:  ldloc.0
  IL_0021:  ldfld      class Test/'closure:13515' Test/'closure:13515'/'closure:13566'::'this value: $PST04000016'
  IL_0026:  ldfld      class [Northwind]Northwind.Database Test/'closure:13515'::db$PST04000013
  IL_002b:  newobj     instance void [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlQuery::.ctor(class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlDefinition,
                                                                                                      class [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlDatabase)
  IL_0030:  stloc.1
  IL_0031:  ldc.i4.0
  IL_0032:  newarr     [mscorlib]System.Object
  IL_0037:  stloc.2
  IL_0038:  ldloc.0
  IL_0039:  ldloc.1
  IL_003a:  ldloc.2
  IL_003b:  call       instance class [System.Data]System.Data.SqlClient.SqlDataReader [System.Compiler.Sql.Runtime]System.Compiler.Sql.SqlQuery::ExecuteReader(object[])
  IL_0040:  stfld      class [System.Data]System.Data.SqlClient.SqlDataReader Test/'closure:13515'/'closure:13566'::'var: 6$PST04000019'
  IL_0045:  ldloc.0
  IL_0046:  ldfld      class [System.Data]System.Data.SqlClient.SqlDataReader Test/'closure:13515'/'closure:13566'::'var: 6$PST04000019'
  IL_004b:  call       instance bool [System.Data]System.Data.SqlClient.SqlDataReader::Read()
  IL_0050:  brtrue.s   IL_0055
  IL_0052:  ldc.i4.1
  IL_0053:  br.s       IL_0056
  IL_0055:  ldc.i4.0
  IL_0056:  brtrue     IL_0182
  IL_005b:  ldloc.0
  IL_005c:  ldfld      class [System.Data]System.Data.SqlClient.SqlDataReader Test/'closure:13515'/'closure:13566'::'var: 6$PST04000019'
  IL_0061:  ldc.i4.0
  IL_0062:  call       instance valuetype [System.Data]System.Data.SqlTypes.SqlInt32 [System.Data]System.Data.SqlClient.SqlDataReader::GetSqlInt32(int32)
  IL_0067:  stloc.3
  IL_0068:  ldloc.0
  IL_0069:  ldfld      class [System.Data]System.Data.SqlClient.SqlDataReader Test/'closure:13515'/'closure:13566'::'var: 6$PST04000019'
  IL_006e:  ldc.i4.1
  IL_006f:  call       instance valuetype [System.Data]System.Data.SqlTypes.SqlString [System.Data]System.Data.SqlClient.SqlDataReader::GetSqlString(int32)
  IL_0074:  stloc.s    V_4

  IL_00e7:  ldloc.0
  IL_00e8:  ldflda     valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued Test/'closure:13515'/'closure:13566'::'var: 7$PST0400001A'
  IL_00ed:  ldloc.3
  IL_00ee:  stfld      valuetype [System.Data]System.Data.SqlTypes.SqlInt32 StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued::ProductID
  IL_00f3:  ldloc.0
  IL_00f4:  ldflda     valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued Test/'closure:13515'/'closure:13566'::'var: 7$PST0400001A'
  IL_00f9:  ldloc.s    V_4
  IL_00fb:  stfld      valuetype [System.Data]System.Data.SqlTypes.SqlString StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued::ProductName

  IL_0168:  ldarg.0
  IL_0169:  ldloc.0
  IL_016a:  ldfld      valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued Test/'closure:13515'/'closure:13566'::'var: 7$PST0400001A'
  IL_016f:  stfld      valuetype StructuralTypes.Tuple_SqlInt32_ProductID_SqlString_ProductName_SqlInt32_SupplierID_SqlInt32_CategoryID_SqlString_QuantityPerUnit_SqlMoney_UnitPrice_SqlInt16_UnitsInStock_SqlInt16_UnitsOnOrder_SqlInt16_ReorderLevel_SqlBoolean_Discontinued Test/'closure:13515'/'closure:13566'::'current Value'
  IL_0174:  ldarg.0
  IL_0175:  ldc.i4.1
  IL_0176:  stfld      int32 Test/'closure:13515'/'closure:13566'::'current Entry Point: '
  IL_017b:  ldc.i4.1
  IL_017c:  ret
  IL_017d:  br         IL_0045
  IL_0182:  ldloc.0
  IL_0183:  ldfld      class [System.Data]System.Data.SqlClient.SqlDataReader Test/'closure:13515'/'closure:13566'::'var: 6$PST04000019'
  IL_0188:  call       instance void [System.Data]System.Data.SqlClient.SqlDataReader::Close()
  IL_018d:  ldc.i4.0
  IL_018e:  ret
} // end of method 'closure:13566'::MoveNext

This code snippet shows how the underlying reader gets initialized and called and how the retrieved data is stored in the locals. Other generated types have to do with the principles of the iterators/enumerators on Comega as explained in previous episodes. So, there will be a series of StructuralTypes, containing a Tuple_the-long-type-name that contains all the fields that can be accessed (e.g. ProductName). This is the type of the row variable in the sample illustrated above.

Other features

Well, it's already pretty late right now over here, so let's keep the functional view of the database access technology in Comega for the next time :-). In the 5bis part I'll cover the functionality you have at your service in Comega regarding data access constructs:

  • Select with projections, column aliases, where clauses, distinct and top keywords, joins, grouping and aggregates, subqueries, ordering, etc
  • Insert, update and delete functionality
  • Transactions

So, cool stuff coming up. Too much for a couple of blog posts in fact (and I'll try to restrict the amount of IL in these upcoming posts in order to give a clear view of the functionality rather the the inner details).

Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

Filed under:

Comments

No Comments