June 2005 - Posts

Hi folks,

If you have a question about the Microsoft Virtual Server 2005 technology on TechEd 2005 Europe in Amsterdam next week (which you'll get for free when attending the conference :o), don't hesitate to visit the ATE booth (station 15B) to ask all of your questions. The ATE is opened on Tuesday from 11:30 AM till 4:30 PM and from 6:30 PM till 9:00 PM (evening session); on Wednesday from 11:00 AM till 5:00 PM; on Thursday from 11:00 AM till 5:00 PM; on Friday from 11:00 AM till 3:00 PM. You can find me out there on:

  • Tuesday - 6:30 PM till 9:00 PM
  • Wednesday - 2:00 PM till 5:00 PM
  • Friday - 11:00 AM till 1:15 PM

Hope to see you there!

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

Introduction

In the previous two posts on database access in Comega I explained how to import a database schema using Sql2Comega.exe and how to use the select keyword in Comega to query the database using the imported data access assembly that contains the metadata information about the database. By using SQL statements in the language directly, you gain in strong-typing and compiler-level checking of statements sent to the server. Actually, the Comega database access stuff can be summarized as "wrapping" keywords around the underlying data provider (currently SQL Server only).

DML

It's fine to be able to access the database using some select construction. However, it's even nicer to have the ability to manipulate the data in the database using keywords, as queries are most typing-sensitive (think of parameterized DML statements which are sent to a database server). Let's start with insertion. Generally spoken, there are two different approaches to insert a new row in a table. First, you can use an implicitly constructed row, by using the following syntax:

int n = insert CustomerID="Test", ContactName="This", CompanyName="Too" into DB.Customers;

where n returns the number of affected rows. Remark that the string values are surrounded by the Comega string delimiters, thus double quotes. Alternatively, one can create the row explicitly (without a type specification, as shown in the previous posts too):

row = new {CustomerID="Test", ContactName="This", CompanyName="Too" into DB.Customers};
int n = insert row into DB.Customers;

In case of insertion conflicts and exceptions, e.g. because a required field value is needed or a constraint is violated, the classic try...catch structure in Comega can be used:

try
{
     row = new {CustomerID=someID, ContactName=someContact, CompanyName=someName into DB.Customers};
     int n = insert row into DB.Customers;
}
catch (SqlException ex) { ... }

As shown in the sample above, variables can be used to specify the field values. It's because of this that the string concatenation and statement parameterization stuff "classic approach" is vanishing due to the introduction of data access keywords in Comega. Note that it's possible too to nest select and insert statements, e.g. to write an insert statement that uses a query (in another table for example) to retrieve the row to be inserted in the table.

I guess that the update and delete statements are pretty straightforward once you get to know the idea of the insert statement. Basically, the structure looks as follows:

update DB.Table set Field1=value1, Field2=value2 ... where Field3 == value3 && Field4 == value4 || Field5 == value5 ...;

of course not formally formulated in BNF. But it gives the informal idea. Note it's also possible to use output parameters, by specifying an assignment to a local variable in the set portion of the statement, e.g.:

update DB.Table set local_variable_to_be_get=SomeFirstField, SomeSecondField=local_variable_to_be_set where ...;

Last but not least, the delete statement which is really as straightforward as possible:

delete from DB.Table where SomeField == somevalue;

Transactions

I hope all of my blog readers know the concept of transactions. If not, well, there's enough stuff to learn about these things. Briefly, a transaction has the ACID properties, what stands for atomicity, consistency, isolation, durability. Using this concept one can guarantee that multiple edit operations on some resource (for example a database) succeed or fail together in a all-or-nothing fashion. Now, one of the problems with transactions today is the lack of built-in block structure support to express transactions, commit actions and rollback actions. Well, honestly, the System.Transactions namespace in .NET v2.0 is a great enhancement, but not on the level of the language syntax. Comega changes this but introducing the keywords transact, commit and rollback. The structure looks like this:

transact(DB)
{
     //perform some database operations
}
commit
{
     //implicitly called when transact block succeeds
}
rollback
{
     //implicitly called when transact block has thrown an exception
}

If you want explicit control, e.g. based on conditional evaluation of return values of DML-statements or output parameters in update-statements, it's also possible to use the keyword rollback where needed:

transact(DB)
{
     //let's try something
     ...

     //let's check whether it's okay for us
     if (somevar != somerequiredvalue) rollback;
}

Put some breakpoints in your code and execute the sample. You'll see nothing about the DML operations using a tool like query-analyzer till the commit is executed (because of isolation).

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

This evening my laptop offered me the option to connect to an unsecured WLAN with an unknown SSID in the living room. Pretty cool :o. Even cooler to get a valid IP address, have no MAC filtering, therefore allowing me to connect to the internet with just one click. Even more, the router admin pages are unsecured so all the ISP information can be revealed :s. Today the "WLAN owner hunting" will start to tell him/her about the problem... I hope I can spot the person in question. It's all about honesty isn't it, trying to inform people. You guys have to know I'm living in the middle of a city center with quite some apartments in the neighbourhood. So one, it will be difficult to find the owner, second there might already be others who have used the WLAN in a malicious way :s.

Time for security evangelism :-).

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

Introduction

In the first part of episode 5 of my series on "Adventures in Comega" I showed you the internal IL-kitchen of database access in Comega and how the Sql2Comega.exe tool is used to export the metadata of a database for usage in the Comega language. Now I'll show you the various constructs that are available in Comega to perform database operations using built-in keywords.

Selection

Okay, the first one I showed in the previous post already is the "select"-keyword to perform ... right, selection. The syntax should be well-known and consists of a "select ... from ..." skeleton with some optional clauses I'll cover further on. An example looks like this:

results = select * from DB.Products

You don't have to worry about the type of the results-variable, it's the job of the Comega compiler to generate the necessairy constructs to make it work, as we saw in the previous post (type infer). Now you can use the foreach keyword to iterate over the rows:

foreach (result in results)
{
     string pname = result.ProductName;
     double uprice = result.UnitPrice;
     //...
}

In fact, results is just a stream (see first episodes of this series for more information about the concept of "streams"). If you do want to specify the stream type explicitly, you can do:

struct { SqlString ProductName; SqlMoney UnitPrice; }* result = select ProductName, UnitPrice from DB.Products

But this is not needed as you can see in the previous code snippets. However, it can be useful when you're using aliases:

struct { SqlString Name; }* result = select ProductName as Name from DB.Products

You can go even further by declaring a field without a name, using parentheses. I won't cover this over here, there's a sample in the Comega documentation stuff. The iteration alias can be specified using the "in" keyword:

foreach (row in select p.ProductName from p in DB.Products) { ... }

Projection

Right, we can select fields in rows. Assume you want to map these fields to attributes in class instances? How to do that? A straightforward way is this one:

class Product
{
     private SqlString ProductName;
     private SqlMoney UnitPrice;

     public Product(SqlString ProductName, SqlMoney UnitPrice)
     {
          this.ProductName = ProductName;
          this.UnitPrice = UnitPrice;
     }

     //...
}

foreach (row in select ProductName, UnitPrice from DB.Products)
{
     Product p = new Product(row.ProductName, row.UnitPrice);
     //...
}

Well, this should work. However, using projection you can actually do this work automatically, by generating a steam of Product instances (Product*):

Product* products = select new Product(ProductName, UnitPrice) from DB.Products;

Although I didn't cover XML support in Comega to the full extent yet, I'll show you how you can use XML syntax in Comega too:

class Product
{
     struct
     {
          SqlString ProductName;
          SqlMoney UnitPrice;
     }
}

Product* products =
     select
         
<Product>
               <ProductName>{ProductName}</ProductName>
               <UnitPrice>{UnitPrice}</UnitPrice>
          </Product>
     from DB.Products;

This is one of my favorite features in Comega I have to admit. Although IntelliSense is rather poor for the moment (you shouldn't expect this kind of stuff to work in experimental releases) I'm sure that a well-working IntelliSense will boost developer productivity when writing this kind of code (as the matter in fact, the <Product>-section can be generated almost automatically).

Beside of using the normal constructors in the projections, you can use functions too, or tuple constructors:

public SqlString ConvertPrice(SqlMoney unitPrice)
{
     return "EUR " + unitPrice;
}

foreach (row in select ConvertPrice(UnitPrice) from DB.Products)
{
     //row is SqlString
}

or

foreach (row in select new{Name=ProductName, Price=UnitPrice} from DB.Products)
{
     //row is tuple type with correctly-typed child attributes
}

Restricting the selection results

First of all, there's the wellknown select clause. Thanks to the integration with the Comega language you'll have a strongly-typed and type-checked syntax for the specification of where-clauses:

results = select ProductID, SupplierID from DB.Products where UnitsInStock < 5;

Other operators are >, ==, !=, <=, >=. To combine expressions the operators && (and) and || (or) can be used. It's important to realize that the selection is happening on the server, so the where-clause is translated into a WHERE-clause in a SQL statement behind the scenes. When using a variable in the condition, it will be evaluated once:

int baseLevel = 5;
results = select ProductID, SupplierID from DB.Products where UnitsInStock < baseLevel;

Next, we have the distinct keyword at our service:

results = select distinct SupplierID from DB.Products;

and the top keyword which can be useful in combinatio with sort operations too (just to give an example):

int supplier = ...;
results = select top 10 ProductName from DB.Products where SupplierID == supplier order by UnitPrice desc;

Should look familiar. The top keyword can also be combined with the keyword percent to have a factor relative to the number of selected rows (e.g. top 25 percent).

Singleton select

Now, one of the problems we have in here is that we always retrieve a stream of records, even when there's only one result. A selection on a primary key will typically generate only one result. The same holds for aggregates which are supported too:

int n = select singleton count(*) from DB.Products;
string name = select singleton ProductName from DB.Products where ProductID=1;

Joins

Okay, I guess you already can see how transparent the use of SQL keywords in Comega is, compared to the equivalent keywords in the T-SQL language. The same holds for all kind of joins:

  • just using a where-clause and different selected tables, aliased with a name (select x.PK, x.Bla, y.Bla from x as DB.TableX, y as DB.TableY where x.PK == y.FK)
  • inner joins with inner join ... in ... on ...
  • left joins with left join ... in ... on ...
  • right joins, outer joins

Grouping, aggregates, having

Exactly the same as in "classic SQL". Just try it and it should work :-)

Subqueries and quantifiers

Subqueries are supported too, which allows nesting of selects. The simplest form of this is to use a singleton select in the subquery expression and to compare that result with some given value. The documentation shows this relevant sample:

   rows = select ContactName, Phone
            from c in DB.Customers
            where (select singleton Count(OrderID)
                     from o in DB.Orders
                     where o.CustomerID == c.CustomerID)
> 10;

So, as you can see, the nested query has access to the fields of the outer query (in this case, the CustomerID) for comparison in the where clause. The idea is that a singleton select returns a scalar value, not a stream, so it can be compared with some other value or it can be inserted in the results of a select as a field. However, sometimes you want to operate on streams (e.g. a query based on the existence of a result performed by a subquery). To allow this, Comega has the operators all, any, exists that we call quantifiers. The usage of these is pretty straightforward and more information can be found in the documentation once again.

Functions

Although not all T-SQL functions are supported (luckily :o) the most relevant ones are, including mathematical functions, string functions and date/time functions.

More to come

In the next post for episode 5 I'll show the DML statements insert, update and delete, as well as transactions.

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

Posted Tuesday, June 21, 2005 1:22 AM by bart | with no comments
Filed under:

Okay, let's resume where we suspended :-). In the previous Monad adventure episode I've been talking about the concept of cmdlets or "command-lets" as the atomic units of work in the Monad system. Now, let's dive deeper into this stuff and show some examples of working with cmdlets.

Obtaining a list of cmdlets

In RDBMSs you can query the databases on the system using a database itself (the catalog). In a similar fashion there's a cmdlet in Monad to display all of the cmdlets on the system. It's called Get-Command:

MSH> get-command

Command Type    Name                      Definition
------------    ----                      ----------
Cmdlet          get-command               get-command [-Verb String[]] [-Nou...
Cmdlet          get-help                  get-help [[-Name] String] [-Catego...
Cmdlet          get-history               get-history [[-Id] Int64[]] [[-Cou...
Cmdlet          invoke-history            invoke-history [[-Id] String] [-Ve...
Cmdlet          add-history               add-history [[-InputObject] Object...
Cmdlet          foreach-object            foreach-object [-Process] ScriptBl...
Cmdlet          where-object              where-object [-ScriptToApply] Scri...
Cmdlet          set-mshdebug              set-mshdebug [-Trace Int32] [-Step...
Cmdlet          add-content               add-content [-Path] String[] [-Val...
Cmdlet          clear-content             clear-content [-Path] String[] [-P...
Cmdlet          clear-property            clear-property [-Path] String[] [-...
Cmdlet          combine-path              combine-path [-Path] String[] [-Ch...
Cmdlet          convert-path              convert-path [-Path] String[] [-Ve...
Cmdlet          copy-property             copy-property [-Path] String[] [[-...
Cmdlet          get-eventlog              get-eventlog [-LogName] String [-N...
Cmdlet          get-childitem             get-childitem [[-Path] String[]] [...
Cmdlet          get-content               get-content [-Path] String[] [-Rea...
Cmdlet          get-property              get-property [-Path] String[] [[-P...
Cmdlet          get-WMIObject             get-WMIObject [-Class] String [[-P...
Cmdlet          move-property             move-property [-Path] String[] [-D...
Cmdlet          get-location              get-location [-Provider String[]] ...
Cmdlet          set-location              set-location [[-Path] String] [-Pa...
Cmdlet          push-location             push-location [[-Path] String] [-P...
Cmdlet          pop-location              pop-location [-PassThru] [-StackNa...
Cmdlet          new-drive                 new-drive [-Name] String [-Provide...
Cmdlet          remove-drive              remove-drive [-Name] String[] [-Pr...
Cmdlet          get-drive                 get-drive [[-Name] String[]] [-Sco...
Cmdlet          get-item                  get-item [-Path] String[] [-Filter...
Cmdlet          new-item                  new-item [-Path] String[] [-Type S...
Cmdlet          set-item                  set-item [-Path] String[] [[-Value...
Cmdlet          remove-item               remove-item [-Path] String[] [-Fil...
Cmdlet          move-item                 move-item [-Path] String[] [[-Dest...
Cmdlet          rename-item               rename-item [-Path] String [-Name]...
Cmdlet          copy-item                 copy-item [-Path] String[] [[-Dest...
Cmdlet          clear-item                clear-item [-Path] String[] [-Forc...
Cmdlet          invoke-item               invoke-item [-Path] String[] [-Fil...
Cmdlet          get-provider              get-provider [[-Provider] String[]...
Cmdlet          new-property              new-property [-Path] String[] [-Pr...
Cmdlet          parse-path                parse-path [-Path] String[] [-Pare...
Cmdlet          test-path                 test-path [-Path] String[] [-Filte...
Cmdlet          get-process               get-process [[-ProcessName] String...
Cmdlet          stop-process              stop-process [-Id] Int32[] [-PassT...
Cmdlet          remove-property           remove-property [-Path] String[] [...
Cmdlet          rename-property           rename-property [-Path] String [-P...
Cmdlet          resolve-path              resolve-path [-Path] String[] [-Cr...
Cmdlet          get-service               get-service [[-ServiceName] String...
Cmdlet          stop-service              stop-service [-ServiceName] String...
Cmdlet          start-service             start-service [-ServiceName] Strin...
Cmdlet          suspend-service           suspend-service [-ServiceName] Str...
Cmdlet          resume-service            resume-service [-ServiceName] Stri...
Cmdlet          restart-service           restart-service [-ServiceName] Str...
Cmdlet          set-service               set-service [-ServiceName] String ...
Cmdlet          new-service               new-service [-ServiceName] String ...
Cmdlet          set-content               set-content [-Path] String[] [-Val...
Cmdlet          set-property              set-property [-Path] String[] -Pro...
Cmdlet          format-list               format-list [[-Property] Object[]]...
Cmdlet          format-custom             format-custom [[-Property] Object[...
Cmdlet          format-table              format-table [[-Property] Object[]...
Cmdlet          format-wide               format-wide [[-Property] Object] [...
Cmdlet          out-null                  out-null [-InputObject MshObject] ...
Cmdlet          out-default               out-default [-InputObject MshObjec...
Cmdlet          out-host                  out-host [-Paging] [-InputObject M...
Cmdlet          out-file                  out-file [-Path] String [[-Encodin...
Cmdlet          out-printer               out-printer [[-Printer] String] [-...
Cmdlet          out-string                out-string [-Stream] [-Width Int32...
Cmdlet          update-formatdata         update-formatdata [-Trace] [-Force...
Cmdlet          export-csv                export-csv [-Path] String -InputOb...
Cmdlet          import-csv                import-csv [-Path] String[] [-Verb...
Cmdlet          export-alias              export-alias [-Path] String [[-Nam...
Cmdlet          Invoke-Command            Invoke-Command [-Command] String [...
Cmdlet          get-alias                 get-alias [[-Name] String[]] [-Exc...
Cmdlet          get-Culture               get-Culture [-Verbose] [-Debug] [-...
Cmdlet          get-Date                  get-Date [[-To] DateTime] [-Year I...
Cmdlet          get-host                  get-host [-Verbose] [-Debug] [-Err...
Cmdlet          get-member                get-member [[-Name] String[]] [-In...
Cmdlet          get-UICulture             get-UICulture [-Verbose] [-Debug] ...
Cmdlet          get-unique                get-unique [-InputObject MshObject...
Cmdlet          import-alias              import-alias [-Path] String [-Scop...
Cmdlet          match-string              match-string [-Pattern] String[] -...
Cmdlet          Measure-Object            Measure-Object [-InputObject MshOb...
Cmdlet          new-alias                 new-alias [-Name] String [-Value] ...
Cmdlet          new-Timespan              new-Timespan [[-From] DateTime] [[...
Cmdlet          read-host                 read-host [[-Prompt] Object] [-Sec...
Cmdlet          set-alias                 set-alias [-Name] String [-Value] ...
Cmdlet          set-Date                  set-Date [-To] DateTime [-DisplayH...
Cmdlet          Start-Sleep               Start-Sleep [-Seconds] Int32 [-Ver...
Cmdlet          tee-object                tee-object [-FileName] String [-In...
Cmdlet          Time-Expression           Time-Expression [[-Expression] Scr...
Cmdlet          write-host                write-host [[-Object] Object] [-No...
Cmdlet          write-progress            write-progress [-Activity] String ...
Cmdlet          new-object                new-object [-TypeName] String [[-A...
Cmdlet          select-object             select-object [[-Property] Object[...
Cmdlet          group-object              group-object [[-Property] Object[]...
Cmdlet          sort-object               sort-object [[-Property] Object[]]...
Cmdlet          get-variable              get-variable [[-Name] String[]] [-...
Cmdlet          new-variable              new-variable [-Name] String [[-Val...
Cmdlet          set-variable              set-variable [-Name] String[] [[-V...
Cmdlet          remove-variable           remove-variable [-Name] String[] [...
Cmdlet          clear-variable            clear-variable [-Name] String[] [-...
Cmdlet          Write-debug               Write-debug [-Message] String [-Ve...
Cmdlet          Write-verbose             Write-verbose [-Message] String [-...
Cmdlet          Write-Error               Write-Error [-Message] String [-Ca...
Cmdlet          Write-Object              Write-Object [-InputObject] Object...
Cmdlet          get-tracesource           get-tracesource [[-Name] String[]]...
Cmdlet          set-tracesource           set-tracesource [-Name] String[] [...
Cmdlet          trace-expression          trace-expression [-Name] String[] ...
Cmdlet          get-acl                   get-acl [[-Path] String[]] [-Audit...
Cmdlet          set-acl                   set-acl [-Path] String[] [-ACLObje...
Cmdlet          get-PfxCertificate        get-PfxCertificate [-Path] String[...
Cmdlet          get-credential            get-credential [-Credential] MshCr...
Cmdlet          get-AuthenticodeSignature get-AuthenticodeSignature [-Path] ...
Cmdlet          set-AuthenticodeSignature set-AuthenticodeSignature [-Path] ...
Cmdlet          new-SecureString          new-SecureString [-Verbose] [-Debu...
Cmdlet          export-SecureString       export-SecureString [-SecureString...
Cmdlet          import-SecureString       import-SecureString [-String] Stri...


MSH>

Quite some stuff, right? Now, Get-Process can be used to limit this list, based on the verb, the noun, or a wildcard:

MSH> get-command -verb set

Command Type    Name                      Definition
------------    ----                      ----------
Cmdlet          set-mshdebug              set-mshdebug [-Trace Int32] [-Step...
Cmdlet          set-location              set-location [[-Path] String] [-Pa...
Cmdlet          set-item                  set-item [-Path] String[] [[-Value...
Cmdlet          set-service               set-service [-ServiceName] String ...
Cmdlet          set-content               set-content [-Path] String[] [-Val...
Cmdlet          set-property              set-property [-Path] String[] -Pro...
Cmdlet          set-alias                 set-alias [-Name] String [-Value] ...
Cmdlet          set-Date                  set-Date [-To] DateTime [-DisplayH...
Cmdlet          set-variable              set-variable [-Name] String[] [[-V...
Cmdlet          set-tracesource           set-tracesource [-Name] String[] [...
Cmdlet          set-acl                   set-acl [-Path] String[] [-ACLObje...
Cmdlet          set-AuthenticodeSignature set-AuthenticodeSignature [-Path] ...


MSH> get-command -noun acl

Command Type    Name                      Definition
------------    ----                      ----------
Cmdlet          get-acl                   get-acl [[-Path] String[]] [-Audit...
Cmdlet          set-acl                   set-acl [-Path] String[] [-ACLObje...


MSH> get-command *service

Command Type    Name                      Definition
------------    ----                      ----------
Cmdlet          get-service               get-service [[-ServiceName] String...
Cmdlet          stop-service              stop-service [-ServiceName] String...
Cmdlet          start-service             start-service [-ServiceName] Strin...
Cmdlet          suspend-service           suspend-service [-ServiceName] Str...
Cmdlet          resume-service            resume-service [-ServiceName] Stri...
Cmdlet          restart-service           restart-service [-ServiceName] Str...
Cmdlet          set-service               set-service [-ServiceName] String ...
Cmdlet          new-service               new-service [-ServiceName] String ...


MSH>

Right, there's apparently more to see about cmdlets than what is displayed (look at the ellipsis on the right-hand side). How to show this? The Format-List cmdlet should be your friend in this kind of scenarios:

MSH> get-command get-service | format-list


Name          : get-service
CommandType   : Cmdlet
Definition    : get-service [[-ServiceName] String[]] [-Include String[]] [-Exc
                lude String[]] [-Verbose] [-Debug] [-ErrorAction ActionPreferen
                ce] [-ErrorVariable String] [-OutVariable String] [-OutBuffer I
                nt32]
                get-service -DisplayName String[] [-Include String[]] [-Exclude
                 String[]] [-Verbose] [-Debug] [-ErrorAction ActionPreference]
                [-ErrorVariable String] [-OutVariable String] [-OutBuffer Int32
                ]
                get-service [-Include String[]] [-Exclude String[]] [-Input Ser
                viceController[]] [-Verbose] [-Debug] [-ErrorAction ActionPrefe
                rence] [-ErrorVariable String] [-OutVariable String] [-OutBuffe
                r Int32]

Path          :
AssemblyInfo  :
DLL           : C:\Program Files\Microsoft Command Shell\System.Management.Auto
                mation.Commands.Management.DLL
HelpFile      : System.Management.Automation.Commands.Management.dll-Help.xml
ParameterSets : {Default, DisplayName, Input}
Type          : System.Management.Automation.Commands.GetServiceCommand
Verb          : get
Noun          : service

 

MSH>

Pipelines

The previous sample showed you two things: how to use Format-List and how to use the pipe symbol (|) to combine cmdlets by redirecting the output of one cmdlet to the input of another one in kind of a chain structure. Now, let's try to apply some filtering with Where-Object:

MSH> get-service | where-object { $_.status -eq "Running" }

Status   Name               DisplayName
------   ----               -----------
Running  1-vmsrvc           Virtual Machine Additions Services ...
Running  ADAM_BISGSS        BISGSS
Running  AeLookupSvc        Application Experience Lookup Service
Running  AudioSrv           Windows Audio
Running  Browser            Computer Browser
Running  CiSvc              Indexing Service
Running  CryptSvc           Cryptographic Services
Running  DcomLaunch         DCOM Server Process Launcher
Running  Dhcp               DHCP Client
Running  dmserver           Logical Disk Manager
Running  Dnscache           DNS Client
Running  ERSvc              Error Reporting Service
Running  Eventlog           Event Log
Running  EventSystem        COM+ Event System
Running  helpsvc            Help and Support
Running  HTTPFilter         HTTP SSL
Running  IISADMIN           IIS Admin Service
Running  lanmanserver       Server
Running  lanmanworkstation  Workstation
Running  LmHosts            TCP/IP NetBIOS Helper
Running  MSDTC              Distributed Transaction Coordinator
Running  MSSQL$SHAREPOINT   MSSQL$SHAREPOINT
Running  MSSQL$SQLEXPRESS   SQL Server (SQLEXPRESS)
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)
Running  MSSQLServerOLAP... Analysis Server (MSSQLSERVER)
Running  Netman             Network Connections
Running  Nla                Network Location Awareness (NLA)
Running  PlugPlay           Plug and Play
Running  PolicyAgent        IPSEC Services
Running  ProtectedStorage   Protected Storage
Running  RemoteRegistry     Remote Registry
Running  ReportServer       Report Server (MSSQLSERVER)
Running  RpcSs              Remote Procedure Call (RPC)
Running  SamSs              Security Accounts Manager
Running  Schedule           Task Scheduler
Running  seclogon           Secondary Logon
Running  SENS               System Event Notification
Running  ShellHWDetection   Shell Hardware Detection
Running  Spooler            Print Spooler
Running  SPTimer            SharePoint Timer Service
Running  SQLBrowser         SQL Browser
Running  SQLSERVERAGENT     SQL Server Agent (MSSQLSERVER)
Running  TermService        Terminal Services
Running  TFSServerScheduler TFSServerScheduler
Running  TrkWks             Distributed Link Tracking Client
Running  W32Time            Windows Time
Running  W3SVC              World Wide Web Publishing Service
Running  winmgmt            Windows Management Instrumentation
Running  wuauserv           Automatic Updates
Running  WZCSVC             Wireless Configuration


MSH>

This deserves some extra explanation. The parameter of where-object contains the condition to filter for. In this case, we obtain a reference to every item in the list using the $_ operator. Using the dot operator we can select a property, in this case we choose for status. This value is then compared to the string "Running" for equality (-eq). To show some other operators, consider the following example:

MSH> get-service | where-object { $_.status -eq "Running" -and $_.name -like "MSSQL*" }

Status   Name               DisplayName
------   ----               -----------
Running  MSSQL$SHAREPOINT   MSSQL$SHAREPOINT
Running  MSSQL$SQLEXPRESS   SQL Server (SQLEXPRESS)
Running  MSSQLSERVER        SQL Server (MSSQLSERVER)
Running  MSSQLServerOLAP... Analysis Server (MSSQLSERVER)


MSH>

Should be clear I guess? Now, what can we query on the output of a Get-Service invocation? To find out about this, together wit the types, use Get-Member:

MSH> Get-Service | Get-Member -MemberType property


    TypeName: System.ServiceProcess.ServiceController

Name                MemberType Definition
----                ---------- ----------
CanPauseAndContinue Property   System.Boolean CanPauseAndContinue {get;}
CanShutdown         Property   System.Boolean CanShutdown {get;}
CanStop             Property   System.Boolean CanStop {get;}
Container           Property   System.ComponentModel.IContainer Container {g...
DependentServices   Property   System.ServiceProcess.ServiceController[] Dep...
DisplayName         Property   System.String DisplayName {get;set;}
MachineName         Property   System.String MachineName {get;set;}
ServiceHandle       Property   System.Runtime.InteropServices.SafeHandle Ser...
ServiceName         Property   System.String ServiceName {get;set;}
ServicesDependedOn  Property   System.ServiceProcess.ServiceController[] Ser...
ServiceType         Property   System.ServiceProcess.ServiceType ServiceType...
Site                Property   System.ComponentModel.ISite Site {get;set;}
Status              Property   System.ServiceProcess.ServiceControllerStatus...


MSH>

This is where the .NET types become visible.

Whatif? Confirm!

Now assume we want to take some action for the selected services (e.g. all MSSQL* services as explained above), for example to stop these services. This can be accomplished by piping the output of the Get-Service cmdlet to the input of the Stop-Service cmdlet. However, this can be a little too agressive, we want more control. There are various ways to do this, e.g. by using a foreach construct together with Read-Host to ask the user for input. Monad has built-in functionality however to see the "showplan" of the execution, by using the -whatif parameter:

MSH> get-service | where-object { $_.status -eq "Running" -and $_.name -like "MSSQL*" } | stop-service -whatif
What if: Operation "stop-service" on Target "MSSQL$SHAREPOINT (MSSQL$SHAREPOINT)"
What if: Operation "stop-service" on Target "SQL Server (SQLEXPRESS) (MSSQL$SQLEXPRESS)"
What if: Operation "stop-service" on Target "SQL Server (MSSQLSERVER) (MSSQLSERVER)"
What if: Operation "stop-service" on Target "Analysis Server (MSSQLSERVER) (MSSQLServerOLAPService)"
MSH>

Okay, that's fine. Let's execute it, but we don't want to stop the SHAREPOINT instance. We could extend the filter or ask for manual user intervention using -confirm. Let's use the latter one:

MSH> get-service | where-object { $_.status -eq "Running" -and $_.name -like "MSSQL*" } | stop-service -confirm

Continue with this operation?
Operation "stop-service" on Target "MSSQL$SHAREPOINT (MSSQL$SHAREPOINT)"
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help
(default is "N") :N

Continue with this operation?
Operation "stop-service" on Target "SQL Server (SQLEXPRESS) (MSSQL$SQLEXPRESS)"
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help
(default is "N") :Y

Continue with this operation?
Operation "stop-service" on Target "SQL Server (MSSQLSERVER) (MSSQLSERVER)"
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help
(default is "N") :Y
stop-service : Cannot stop service 'SQL Server (MSSQLSERVER) (MSSQLSERVER)' bec
ause it has dependent services. It can only be stopped if the Force flag is set
.
At line:1 char:98
+ get-service | where-object { $_.status -eq "Running" -and $_.name -like "MSSQ
L*" } | stop-service  <<<< -confirm

Continue with this operation?
Operation "stop-service" on Target "Analysis Server (MSSQLSERVER)
(MSSQLServerOLAPService)"
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help
(default is "N") :Y
MSH>

Okay, SQL Server couldn't be stopped because it has a dependency but it draws the overall picture of the principles of pipelines in Monad.

Aliasing

Right, we saw the use of Where-Object in the previous samples. Pretty powerful but nasty to type. An interesting concept in Monad to solve this problem is the use of aliases. The Get-Alias cmdlet show us all of the defined aliases:

MSH> get-alias

Command Type    Name                      Definition
------------    ----                      ----------
Alias           ac                        add-content
Alias           clc                       clear-content
Alias           cli                       clear-item
Alias           clp                       clear-property
Alias           cpi                       copy-item
Alias           cpp                       copy-property
Alias           cv                        clear-variable
Alias           cvpa                      convert-path
Alias           epal                      export-alias
Alias           fc                        format-custom
Alias           fl                        format-list
Alias           foreach                   foreach-object
Alias           ft                        format-table
Alias           fw                        format-wide
Alias           gal                       get-alias
Alias           gc                        get-content
Alias           gci                       get-childitem
Alias           gcm                       get-command
Alias           gdr                       get-drive
Alias           ghy                       get-history
Alias           gi                        get-item
Alias           gl                        get-location
Alias           gm                        get-member
Alias           gp                        get-property
Alias           gps                       get-process
Alias           group                     group-object
Alias           gsv                       get-service
Alias           gu                        get-unique
Alias           gv                        get-variable
Alias           help                      get-help
Alias           ic                        invoke-command
Alias           ihy                       invoke-history
Alias           ii                        invoke-item
Alias           ipal                      import-alias
Alias           ipcsv                     import-csv
Alias           mi                        move-item
Alias           mp                        move-property
Alias           nal                       new-alias
Alias           ndr                       new-drive
Alias           ni                        new-item
Alias           nv                        new-variable
Alias           oh                        out-host
Alias           rdr                       remove-drive
Alias           ri                        remove-item
Alias           rmi                       rename-item
Alias           rmp                       rename-property
Alias           rp                        remove-property
Alias           rv                        remove-variable
Alias           rvpa                      resolve-path
Alias           sal                       set-alias
Alias           sasv                      start-service
Alias           sc                        set-content
Alias           select                    select-object
Alias           si                        set-item
Alias           sl                        set-location
Alias           sleep                     start-sleep
Alias           sort                      sort-object
Alias           sp                        set-property
Alias           spps                      stop-process
Alias           spsv                      stop-service
Alias           sv                        set-variable
Alias           where                     where-object
Alias           cat                       get-content
Alias           cd                        set-location
Alias           clear                     clear-host
Alias           cp                        copy-item
Alias           h                         get-history
Alias           history                   get-history
Alias           kill                      stop-process
Alias           lp                        out-printer
Alias           ls                        get-childitem
Alias           man                       get-help
Alias           mount                     new-drive
Alias           mv                        move-item
Alias           popd                      pop-location
Alias           ps                        get-process
Alias           pushd                     push-location
Alias           pwd                       get-location
Alias           r                         invoke-history
Alias           rm                        remove-item
Alias           rmdir                     remove-item
Alias           echo                      write-object
Alias           cls                       clear-host
Alias           chdir                     set-location
Alias           copy                      copy-item
Alias           del                       remove-item
Alias           dir                       get-childitem
Alias           erase                     remove-item
Alias           move                      move-item
Alias           rd                        remove-item
Alias           ren                       rename-item
Alias           set                       set-variable
Alias           type                      get-content


MSH>

Nice, so Where-Object can be abbreviated to "where". Maybe you'd like to abbreviate it further down to just w:

MSH> new-alias

Cmdlet new-alias at command pipeline position 1
Supply values for the following parameters:
(Type !? for Help.)
Name: w
Value: where-object
MSH>

So, now w stands for where-object.

What's coming next?

In the next episodes I'll show you how to use various "utility cmdlets", how to create scripts, how to access the registry and other "providers" and how to create your own cmdlets in .NET.

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

Interesting download on the Microsoft Download Center: http://www.microsoft.com/downloads/details.aspx?FamilyID=acdbbf66-9e20-415e-9bce-f5c91aba9567&DisplayLang=en. Funny to see the consistency of the "System Requirements" with every download; luckily this guy's music is compatible with Longhorn ;-). Enjoy it!Del.icio.us | Digg It | Technorati | Blinklist | Furl | reddit | DotNetKicks

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

Posted Monday, June 20, 2005 3:25 AM by bart | with no comments
Filed under:

In the previous post I mentioned how you can obtain a copy of the MSH (Microsoft Shell) Beta 1, codenamed "Monad". In this (and the following) post(s) I assume you already have installed MSH and it's up and running on your machine. You can start it with MSH.exe or through the Start Menu which will contain a shortcut to the Microsoft Shell after installation.

Getting started

When you have started MSH you should see something like this:

Microsoft Command Shell
Copyright (C) 2005 Microsoft Corporation. All rights reserved.

MSH>

Some basic commands to kick off with are:

  • clear - to clear the screen
  • dir - for directory listings (same as get-childitem)
  • help ... - replace ... by some command name to show the "man"-pages

just to have something in your toolbox to play with :-).

Now, let's start by changing the command prompt using the Function Prompt command:

MSH> Function Prompt { "Bart> " }
Bart>

Hmm, rather weird syntax you might think. Well, maybe or maybe not. More will become clear as I explain the basic principles of the MSH shell environment. So, let's show you the usage of variables:

Bart> $prompt="Hello> "
Bart> Function Prompt { "$prompt" }
Hello>

Basically the $ symbol tells the MSH environment to "interpret" what follows. In the first line we define a variable, in the second line we use it. Now, let's change the variable:

Hello> $prompt=$prompt.Substring(0,5) + " World " + "> "
Hello World >

Any clue? Right, the $prompt variable is of the managed type System.String, therefore we can use any of the methods defined in the System.String class of the .NET Framework. By just calling $prompt, you'll show the contents of the variable:

Hello world > $prompt
Hello world >
Hello world >

So, the second line in this sample is nothing more than a print of the $prompt variable contents. Let's show one more action performed on the variable:

Hello world > $prompt.ToUpper()
HELLO WORLD >
Hello world >

Okay, this should be clear I guess. But there's more of course. We'll stay with the definition and usage of variables for now:

MSH> $i = 1
MSH> $i
1
MSH> $i++
MSH> $i
2
MSH> $i+=5
MSH> $i
7
MSH> $i=$i*$i
MSH> $i
49
MSH> ++$i
MSH> $i
50
MSH>

Operators such as +, -, *, /, +=, -=, *=, /=, ++, -- are all avaible in the MSH environment. In this sample, $i is an integer value. What about type conversions?

MSH> $j="2"
MSH> $j
2
MSH> $j++
 : The '++' operator only works on numbers not on 'System.String'
At line:1 char:4
+ $j++ <<<<
MSH>

Hmm, that's right isn't it? When you'd use the +-operator, you'd end up with a string concatenation. So, how can we tell MSH that $j should be converted to an integer?

MSH> $j=[int]$j
MSH> $j++
MSH> $j
3
MSH>

In order to declare a variable of a certain type, just do this:

MSH> $k=[long]1
MSH> $k
1
MSH>

What about arrays? Supported too:

MSH> $array=1,2,3,4,5
MSH> $array
1
2
3
4
5
MSH>

This is the ideal place to introduce the Write-Host (more or less the equivalent of echo in the classic Windows shell) "cmdlet" (more information about "cmdlets" follows in a minute):

MSH> Write-Host $array
1 2 3 4 5
MSH> Write-Host "The array contains $array"
The array contains 1 2 3 4 5
MSH>

Want to know more (e.g. about coloring the output)? Just execute "help Write-Host". Concerning variables, there are additional cmdlets that can be useful, such as Set-Variable. This allows to specify a variable to be read-only, static or private for example (more information via ... "help Set-Variable").

In fact, you can create any .NET type by using the [...] syntax. Well, that's what should be possible in the future. Today, you can only construct objects based on a string (e.g. a System.DateTime object based on a string representing the date). Thus, something like $g=[Guid] simply doesn't work (yet?).

Now, back to the array-stuff. Arrays have indexers, right? That's the case in MSH too:

MSH> $array[0]
1
MSH> $array[0..1]
1
2
MSH> $array[0,2]
1
3
MSH> $array[0..2]
1
2
3
MSH>

As the matter in fact, quite flexible indexers as you can see. Another symbol is @, which can be used to create associative arrays (read: hashtables):

MSH> $hash=@{ name="bart"; array=$array; location=$(Get-Location); someint=2}
MSH> $hash

Key                            Value
---                            -----
name                           bart
array                          {1, 2, 3, 4, 5}
location                       C:\Documents and Settings\Administrator
someint                        2


MSH>

Okay, please shoot me ... I was running as Administrator :$. Well, not much a problem this time as I was running in a sandboxed VPC but right, that shouldn't be much of an excuse. Now, as you can see, the hashtable seems to work:

MSH> $hash["name"]
bart
MSH> $hash["array"][1..3]
2
3
4
MSH>

You know, first rule of thumb ... arrays are pointers. So, what if we change the array, e.g. by reversing it (shows how to call Array.Reverse in Monad):

MSH> [Array]::Reverse($array)
MSH> $hash["array"][1..3]
4
3
2
MSH>

I guess you know now how to display the current date and time?

MSH> Write-Host [DateTime]::Now
[DateTime]::Now
MSH> Write-Host ([DateTime]::Now)
6/19/2005 6:09:42 PM
MSH> [DateTime]::Now

Sunday, June 19, 2005 6:09:53 PM


MSH>

Don't get tricked by the Write-Host cmdlet. You need to use braces in order to have some part of the argument interpreted. Now, let's turn to the real stuff with some program structures:

MSH> foreach ($i in $array) { $i }
5
4
3
2
1
MSH>

or with some manipulation stuff

MSH> $sum=0
MSH> $j=0
MSH> foreach ($i in $array) { $p=$i*$i; $array[$j++]=$p; $sum+=$p }
MSH> $sum
55
MSH> $array
25
16
9
4
1
MSH>

Similarly, there are if-elseif-else and switch statements and loops with while or for:

MSH> $i=0
MSH> while ($i -lt 10) { Write-Host ($i++) }
0
1
2
3
4
5
6
7
8
9
MSH>

Note you need to use -lt to perform lower than comparison. In an analogous fashion there is support for -gt, -le, -ge, -eq, -ne and even for more complex stuff such as -like (pattern matching), -match (regular expressions), -is (type checking as in C#), -contains (check for occurrence of a certain value in a "group", such as an array):

MSH> $b=$array -notcontains 4
MSH> $b
25
16
9
1
MSH>

As a side-note there is some other cool stuff around arrays, such as the use of tail-expressions (cf. Haskell):

MSH> $k,$l,$m=$array
MSH> $k
25
MSH> $l
16
MSH> $m
9
4
1
MSH>

Variable $m contains the tail of the list after assignment of the first two elements to $k and $l.

In order to create an endless loop, use while ($true):

MSH> while ($true) {} #press CTRL-C to stop
MSH>

We've seen the cmdlet Write-Host already. We always did specify the name explicitly, however you can call it indirectly too using the &-operator:

MSH> $cmd="Write-Host"
MSH> &$cmd "Hello"
Hello
MSH>

This is useful when you're creating scripts that need to be altered afterwards in a very broad way.

What is a CmdLet?

A CmdLet (or command-let) is the basic vehicle that drives Monad. Basically it's the equivalent of a "command" in other shells. So it's a basic unit of work. However, it has some "natural language" aspects. First there is a verb and a noun, seperated by a dash character:

verb-noun

The verb indicates what will happen: e.g. get something, add something, clear something, copy something, format something, create something, write something, and so on. The noun is the indicator of the real functionality, the "subject" of the action. Monad comes with a bunch of built-in Cmdlets. A little list with some samples:

  • Get-Datae, Get-Drive, Get-Host, Get-Help, Get-Variable, Get-Item
  • New-Item, New-Object, New-Variable
  • Read-Host, Write-Host
  • Remove-Variable
  • Rename-Item
  • Get-Process, Get-Service, Restart-Service

In a next episode of "adventures in Monad" I'll explain how these cmdlets work, how you can "pipe them together" and how to create your own cmdlets (h).

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

I'm playing around with Monad for quite some time already (see http://community.bartdesmet.net/blogs/bart/archive/2004/10/19/445.aspx). Apparently I've forgotten to post about some experiments in Monad (or I can't find it back :-s). However, you can play with it yourself right now. Visit beta.microsoft.com and use the mshPDC guest ID to login. The beta can run on Windows XP and Windows Server 2003.

Have fun (and time? :-o).

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

Introduction

Let's start with clarifying cryptic names: the R2 in Windows Server 2003 R2 stands for "Release 2", nothing more, nothing less. So, what is it? Well, let's tell you a little story first. A couple of summers ago is was rolling out a brand new network with four Windows Server 2003 installations, which was just a great experience. However, I still remember the post-installation steps I had to take: download all the free add-ons for the Windows Server 2003 platform. You can find a complete list on http://www.microsoft.com/windowsserver2003/downloads/featurepacks/default.mspx.

The idea of R2 is to include a series of those add-ons plus some additional new features in a second release of the server operating system, which eliminates the need to go and search for a bunch of add-ons later on (as the matter in fact, I have a cd-r called "post W2K3 installation setups" that contains quite some downloads that I'm using regularly).

Installation steps

Okay, time to take a look at the installation procedure of the "Customer Preview Program Beta" which can be found on http://www.microsoft.com/windowsserver2003/r2/default.mspx. First of all, it's very important to know that you need to use an evaluation edition of Windows Server 2003 Enterprise Edition (180 days trial) to install the preview software! I forgot this step initially (therefore using an RTM version of W2K3 Enterprise from MSDN), so R2 didn't want to install at all. So, the correct download steps are:

Next, installation time (it will take a couple of hours to receive the product keys and a link to the real download location):

  • (Optionally: use Virtual PC 2004 + SP1)
  • Install Windows Server 2003 Enterprise Edition + SP1 180 days trial.
  • Install R2 on top of it.

This reflects the overall idea of R2: it's just a collection of (very valuable) add-ons on top of the basic installation of the operating system with the latest Service Pack (SP1) installed. When you'll mess around in the Windows Components Setup you'll be asked to insert "Disc 2" of Windows Server 2003, which is in fact the R2 disc. So, when released, you'll buy a Windows Server 2003 with two discs: the basic installation and the additional "feature packs".

Features

Although I don't have the intention to present you an exhaustive list about all the R2 features, let's summarize the R2 information chm file over here:

  • Audit Collector Service (ACS Forwarder): forwards security events in order to have real-time analysis.
  • Hardware Management can be used to manage the hardware of servers (e.g. page files, installed services) as an alternative to WMI scripts which are sometimes nasty to write. In order to accomplish this goal, HM uses the SOAP-protocol and web services technology to establish a communication between the managed server and BMCs (Baseboard Management Controllers). The technology is based on the WS-Management specification and IPMI (Intelligent Platform Management Interface). More information can be found on http://www.microsoft.com/whdc/system/pnppwr/wsm.mspx. To take a jumpstart with this technology, check out the Wsman.cmd and WECUtil.exe commands.
  • Microsoft Management Console 2.1 (MMC 2.1) is more task-driven than its predecessors using an action pane with various tasks in a well-structured format.
  • Active Directory Features:
    • Active Directory Application Mode (ADAM): a lightweight version of Active Directory that can run in a stand-alone fashion without infrastructure requirements; for more information take a look at http://www.microsoft.com/adam. The information includes some usage scenarios (e.g. storage of web-users' information in a directory service).
    • Active Directory Federation Services (ADFS): leverages single sign-on to authenticate users on related web applications in the context of one online session (formerly called "TrustBridge"). So, this technology allows to make bridges between companies in order to share and exchange use identities for a better end-user experience and tighter and more controlled integration (B2B) of infrastructures (concerning identity management). The ADFS story is rather complex, so I want to refer to a complete .NET Show about the topic (http://msdn.microsoft.com/theshow/episode.aspx?xml=theshow/en/Episode047/manifest.xml) and an overview document (http://www.microsoft.com/downloads/details.aspx?FamilyID=8a4ccaf1-d55e-4129-8a5f-97093a48fd3d&DisplayLang=en) as well as the software itself of course. Check out MIIS (Identity Integration Server) and ADAM (see previous list item) as well.
  • Identity Management for UNIX: in fact the integration of SFU (Services For Unix) version 3.5 in R2, to allow Windows to integrate in a NIS domain (Network Information Service), which can also be used in Mac OSX-environments. This includes password synchronization between Windows-accounts and UNIX-accounts.
  • Disk and file management features including support for branch office scenarios to publish, replicate, manage files between "field sites" and the "central hub" of an organization. This includes DFS Namespaces (formerly just known as DFS) and DFS Replication (successor of the File Replication Service but with diff-over-the-wire technology, known as Remote Differential Compression, and scheduling and bandwidth throttling). Furthermore there's the new Print Management MMC that allows administrators to manage a printer infrastructure in a Windows Server 2003-based network (remote installation of printers, management of queues, capacity filtering, etc).
  • Common Log File System (CLFS) is a loadable driver to allow apps (both kernel-mode and application-mode) to use a robust logging system. CLFS is optimized for performance and durability fo persisted sequential data (http://www.microsoft.com/technet/prodtechnol/WindowsServer2003R2Beta2/Library/CLFS/8876fac4-9ec6-435c-b393-ba0ce9a1d3a0.mspx).
  • Storage Resource Manager can be seen as a more flexible variant of NTFS Disk Quotas on the folder level (e.g. allow 100 MB of file storage in that folder for users, send a warning on the 85% level via e-mail, run a command when a threshold of 90% is reached, log events, etc) including file screening technology (e.g. prohibit storage of executables in this folder).
  • Other stuff such as MSNFS (formerly part of SFU), Storage Management for SANs, new SFU-related features such as Visual Studio debugging for POSIX apps and Windows SharePoint Services inclusion.

My conclusion: it's certainly worth the download to take a look at these features in action. I'll jump on the ADFS, MIIS, ADAM train later this summer and if I find the time, I'll keep you posted guys :-).

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

More Posts Next page »