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:
- Create a new Comega project (console application).
- Right-click on References in the "Solution Explorer" and choose "Add Database Schema..."
- 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).