Comparison of a simple select statement in C-omega vs. ADO.Net


In a couple weeks, I’m doing a talk at a .NET users group in Ontario about . Cω is a cool research language from Microsoft Research that extends C# with direct support for data access (SQL and XML) and concurrency abstractions.  I’ve been planning on writing a number of blog entries about Cω, but I haven’t yet been able to make the time.  I was just working on the slides for my talk and thought I’d at least post one of my examples comparing simple database access with ADO.Net and Cω.  One important concept in efficiently building reliable and maintainable software is “discover errors as early (and cheaply) as possible”.  With that in mind, let’s look at some code (disclaimer: I’m by no means an ADO.Net expert).


 


Say we just want to get the names of all the employees who live in a specific city (in the sample Northwind database that comes with SQL Server).  The simplest approach has a number of well-known problems:


 


      SqlDataAdapter da = new SqlDataAdapter(


      “SELECT * FROM Employees WHERE City='”+city+”‘”, nwindConn );


DataSet ds = new DataSet();


      da.Fill(ds,”Employees”);


 


      foreach (DataRow dr in ds.Tables[“Employees”].Rows)


      {


            string name = dr[“LastName”].ToString();


int id = (int)dr[“EmployeeID”];


            Console.WriteLine( id + “: ” + name);


      } 




  • The argument ‘city’ is subject to a SQL injection attack


  • The data is weakly typed, so we have to cast to get what we want, which could fail at run-time


  • Several string literals (table and column names) are used which could result in run-time errors


  • The query is stored as a string providing no opportunity for validation before run-time

 We prevent the injection attack by using SqlParameters, and use strongly typed data sets to prevent the problems with string literals and casting:


 


      SqlDataAdapter da = new SqlDataAdapter(


            “SELECT * FROM Employees WHERE City= @city”, nwindConn );


      SqlParameter cityParam = da.SelectCommand.Parameters.Add(“@city”, SqlDbType.VarChar, 80);


      cityParam.Value = city;


 


      NorthwindDataSet ds = new NorthwindDataSet();


      da.Fill(ds, ds.Employees.TableName );


 


      foreach (NorthwindDataSet.EmployeesRow dr in ds.Employees.Rows)


      {


            string name = dr.LastName;


            int id = dr.EmployeeID;


            Console.WriteLine( id + “: ” + name);


      }


 


This is a lot better (although people are often too lazy to write the extra code to use SqlParameters).  However, the SQL command is still stored as a string meaning we won’t find any errors in it until run-time, and the relationship between the shape of its output and the NorthwindDataSet is implicit and brittle.  We might want to put the SQL into a stored procedure:


 


CREATE PROCEDURE EmployeesForCity


      @City nvarchar(80)


AS


SELECT EmployeeID, LastName FROM Employees WHERE City = @City


 


And then write our code without explicit knowledge of the structure of the underlying database:


 


      SqlCommand cmd = new SqlCommand( “dbo.EmployeesForCity”, nwindConn );


      cmd.CommandType = CommandType.StoredProcedure;


      SqlParameter cityParam = cmd.Parameters.Add(“@city”, SqlDbType.VarChar, 80);


      cityParam.Value = city;


 


      SqlDataAdapter da = new SqlDataAdapter( cmd );


      NorthwindDataSet ds = new NorthwindDataSet();


      da.Fill(ds, ds.EmployeesForCity.TableName );


 


      foreach (NorthwindDataSet.EmployeesForCityRow dr in ds.EmployeesForCity.Rows)


      {


            string name = dr.LastName;


            int id = dr.EmployeeID;


            Console.WriteLine( id + “: ” + name);


      }


 


This is better; at least now our SQL statement can be checked before we run our app.  However, we still have some literal strings in our code, and we’re still going to get a run-time error if the stored procedure changes in some way (I don’t see any easy way to rebuild the XSD from the database schema in VS.Net 2003).  Plus, we’ve had to write significantly more code here.  At best, it feels like our code has a weak connection to the underlying database.  For small programs like this, it’s no big deal.  But for large data-intensive programs, people complain a lot about spending a lot of the time messing with plumbing code. 


 


Here’s the equivalent code in Cω:


 


      rows = select * from DB.Employees where City == city;            


      foreach( row in rows )


      {


            string name = row.LastName.Value;


            int id = row.EmployeeID.Value;


            Console.WriteLine( id.ToString() + “: ” + name);


      }


 


Note the following:




  • There is no knowledge embedded in string literals


  • The select statement is strongly typed, meaning that we know exactly at compile-time what the shape of the data will look like (intellisense even shows us), and what valid statements are.


  • We can embed local variables like “city” directly into our sql without worrying about injection attacks.


  • We can rely on type inference so we don’t have to explicitly specify the type of “rows” and “row”, even though it is strongly typed.


  • We can configure our build such that we’ll know at compile time if the database has changed in any way that would make the code fail.  Of course the database could still be changed underneath our program resulting in a run-time error if we don’t recompile.

 And if you are willing to abandon SQL syntax all together, you could equivalently write:


 


      DB.Employees [City==city].{


            Console.WriteLine( it.EmployeeID + “: ” + it.LastName );


      };


 


How do you like that for concisely expressing what you mean?


 


[Update: Added entry with C# 3.0 / DLinq syntax for this comparison]

Comments (20)

  1. AIM48 says:

    This is great – this is what i was expecting when i first heard of ".net code as stored procedures"

  2. Paul Wilson says:

    Will this work with any database, or just MS SQL?

    Thanks, Paul Wilson

  3. What happens if your database structure changes? Where does C Omega get the schema of the database from?

  4. dru says:

    Damn! This is excellent.

  5. Rick Byers says:

    Paul, One of the design goals of Cw is to isolate the language from the database implementation. There is certainly nothing baked into the language specific to MS SQL. However, in order to talk to a database, you need to generate a "database assembly". Cw comes with a Sql2COmega tool which does this automatically for MS Sql. I believe the intention is that you could replace that tool with a different one for different databases. However, I have no idea if that would actually work with the current bits – I suspect it has really only been tested with MS SQL, so there may be some interoperability bugs. Regardless, the important thing is that the design is database-neutral. In fact, you can use the same SELECT statement to get data from an XML doc, or even just an in-memory object graph 🙂

  6. Rick Byers says:

    David, Cw gets the database structure from a "database assembly" that is generated either by the Visual Studio integration, or by running a command-line tool. This is similar to the way strongly typed datasets work in ADO.Net today. However, with Cw database assemblies, you should be able to detect most relevant database changes immediately by rebuilding the database assembly and recompiling. There is no interactive "XSD designer" or anything, so rebuilding should be fully automatic. In fact, you’ll get excellent error messages point to the parts of the code that no longer conform to the database schema (complete with Intellisense in VS guiding you along).

    Of course, if the database changes and we don’t rebuild our database assembly, the application will get a typical SqlException or something.

  7. wesbran.com says:

    Rick Byers, a developer on the CLR team at Microsoft, has posted an interesting comparison of the select statement in ADO.NET vs Cω. Cω (C-Omega) is an extension to the C# programming language that allows for greater integration between the…

  8. Rick Byers says:

    Thanks for all the positive comments! I hope this is a sign people will find my talk interesting <grin>. I think this stuff is cool and extremely relevant to the future of mainstream programming, but I wasn’t sure how many other people would feel that way. In fact, I’m quite surprised at the lack of buz about this stuff.

    At least Anders also thinks this kind of thing is important: http://www.theserverside.net/talks/videos/AndersHejlsberg/dsl/q17.html

  9. Cleve Littlefield says:

    How does this correspond to the work Anders is doing in this area? Are you guys working together or will I expect a different syntax from his implementation?

    Sorry to say but for C# I dont put much stake in anything that doesnt have his blessing…

  10. <p>&lt;ul&gt;&lt;li&gt;&lt;a href=&quot;http://metaatem.net/words&quot; target=&quot;_blank&quot;&gt;Spell with flickr&lt;/a&gt;&lt;/li&gt;&lt;li&gt;&lt;a href=&quot;http://aspalliance.com/articleViewer.aspx?aId=652&amp;pId=1&quot; target=&quot;_blank&quo

  11. Sahil Malik says:

    Interesting …. is all I’ll say at this point :).

    There’s so much more under the seams that I’d rather kick the tires off before saying OH MY GOD EXCELLENT IM GOING BONKERS !!

    It’s "interesting" at this point .. worth watchin’ out 4 !!

  12. Rick Byers says:

    Cleve,

    Cw is just a research prototype to experiment with ideas. There is no relationship with C#. The C# team has not announced any details of their plans other than the general comments Anders has made. One thing Anders told me was that, being a research language, the Cw folks had the freedom to experiment and risk screwing up. C# will not have that freedom, so they will have to take a more cautious approach. Also note that I’m not affiliated with either the C# or Cw teams – I just think this is cool, Iwork on the CLR.

    Sahil,

    That’s exactly the right approach. This is a hard problem. I find it interesting to get a glimpse into some approaches that could be followed by mainstream languages, but that is really all Cw is good for. Cw is licensed for academic use only, and is certainly not a production environment.

  13. Craig Humphrey says:

    Excellent stuff guys, kinda reminds me of what Oracle did with plsql (as an object based library for C about 10 years ago) and IIRC Delphi…

    Looking forward to seeing this supported in the main-stream languages, well, C# and perhaps C++ 🙂

  14. Gabe says:

    This kind of language feature needs to be fast tracked into the entire .NET platform! "Impedience mismatch" between object oriented languages and relational data is a huge productivity sucker and source of errors. 90% of my business object code consitis of the C.R.U.D. This type of methodology aliviates the most painful aspects. I want this now! 🙂

    I can’t emphasize this enough. A language that can work with and query the data natively (and with strong typing) is nothing short of revolutionary. ADO.NET and VFP come close, but this looks like the real deal.

  15. Jason Haley says:

    Interesting finds so far this week

  16. Rick Byers says:

    Ever since I started planning for my users-group talk (and wrote this blog entry) about data access with…

  17. tatianna says:

    this is an awsome web site and i would like to know how to find a comparinsion statement       thanks tatianna

  18. Rick Byers says:

    Six months ago I posted a comparison of a simple select statement in C-omega vs. ADO.Net which some people

  19. Rick Byers says:

    Ever since I started planning for my users-group talk (and wrote this blog entry ) about data access