Microsoft has a very strange pattern of alternately creating great and horrible things for programmers.  The Windows SDK was difficult; MFC was great; COM was a nightmare; C# is a dream; LINQ has gone back to the Dark Side.

Here’s my first working code, which took forever and probably could be done much better… with two books plus the web as reference, it was hard to do even this much.

SqlConnection conn = new SqlConnection(
   @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TestDb.mdf;Integrated Security=True;User Instance=True");
SqlCommand comm = new SqlCommand("SELECT * FROM People", conn);
SqlDataAdapter adapter = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
adapter.Fill(ds);

var results = from t in ds.Tables[0].AsEnumerable()
                          where !t.IsNull("Level")
                                && t.Field("Level") > 6
                          select t;
foreach (DataRow row in results) {
     this.ResultsLabel.Text +=
                    string.Format("{0}: {1}\n",
                    row["PersonID"], row["Name"]);
}

Ugh.  I appreciate the idea of adding SQL support directly to the language, but this really doesn’t seem easier than, say, using SQL, something we’ve been able to do for years.  Compare the equivalent SQL query, in fact:

SELECT PersonID, Name FROM People
WHERE Level IS NOT NULL AND Level > 6

Microsoft has gone to a whole lot of work to make something that kind of looks like SQL but isn’t. The compiler is much smarter in some ways (e.g. it will figure out many data types from context) but not enough– it seems entirely arbitrary what objects can be used in a LINQ query and which can’t. It’s particularly annoying since Visual Studio is smart enough to make it easy to create a database for you, and it will create some accessor objects for you… and then I have to mess around with connection strings and string column names in the code.  (Come to think of it, I’m making an actual SQL query in order to make a fake SQL query.  Why is this a good thing?)

I’ll get better at this soon; I’m just picking it up, so I’m in the ranting stage. But I wish the Light Side had been allowed to work on this. So far it looks like what Joel calls “Fire and motion“: Microsoft messing with things in hopes that they can move forward while everyone else is forced to catch up. If they’d made database queries easier, that would be cool, but so far I’d rather write the SQL.

Advertisements