The past week, I put on the classic "Web Developer" hat to do some heads down work, so blogging, writing replies back to blog-comments, and answering newsgroup posts has been a bit more sparse. Some of you are probably wonder why I am even doing web development considering I work on the technologies that are "underneath it all", but that is another story. Now, I still have some more work to do in this area, so I will probably be MIA for a short while yet.

Before I move on, I want to say that I get about half a dozen public/private comments a day that I really should respond to, and I usually end up making blog posts out of the questions in one way or another within 72 hours, but not always... so if you are wondering when my reply is coming... it is not because I am intentionally ignoring you. I plan to getting around to answering them all. I just have a very long queue. :-)

Now, the Web Developer role has got me doing things that I normally do not touch or even remotely work on (hey, I work on IIS core, so even writing an ASP page is not exactly the norm). Things like relational model design, programming SQL views, stored procedures, and triggers, and accessing data from the database using ADO and ADO.Net (I am writing both a commandline script and an ASP.Net web application). So, I also took the opportunity to take a personal lap around the various Microsoft web-related technologies to see how they work together and how easy (or hard) it is to figure it out, given someone who does not have a whole lot of clue and fairly new to the whole area.

I have to say that overall, I am satisfied with the accomplishments of the task, but I am far from extremely satisfied with the entire experience. All of the information to do my tasks are probably all there on MSDN and web-accessible, but I just cannot find them fast and easy enough to be of any use.

The Joys of SQL (NOT)

I first started with the design of the relational data model since I figured everything else should drive off of the data and its relationships. I realize that relational database design is a topic all its own, but basic prescriptive guidance SOMEWHERE would be nice. I mean, I have never taken a database course nor designed a relational model, so it took me a lot of time and thinking to get started. Fortunately, I ended up doing the right relations and table design, mostly through intuition/luck, but boy was I uncomfortable about many of those choices while doing it - because I had no idea whether it was right or not. In hindsight, I now see so many pitfalls that should be avoided by everyone, but it is not clear how to best disseminate that information...

Then, I immersed in SQL Enterprise Manager to write the queries, views, stored procedures, and triggers, and boy was that an interesting experience. I have no idea whether that UI is good or not when compared with its competitors, but to me, it seems incredibly dated, awkward, and inefficient.

I hate the fact that in order to save any view, stored procedure, or trigger that it has to be syntactically correct. Most of the time I just need to view the various table and column names while constructing the correct SQL statement, but the Editor is modal and I cannot figure out how to simultaneously be in Design View for one table while writing a stored procedure or trigger in the Editor of another.

For the longest time, I had to cut the non-functional statements into the clipboard, close the Editor and save the functional portions, quickly navigate to the right Design View, memorize a couple words of missing information, navigate back to the Editor of the right stored procedure/trigger, open the Editor, paste back the non-functional statement in the correct location, hopefully still remember what I just memorized, spell it correctly, and then continue on.

The only work-around I eventually found is to use the SQL Query Analyzer to simultaneously open a view onto the same database so that I can have both the design of the database and the modal Editor usable at the same time.

I have no idea whether this represents the right way or the "state of the art", but if it is, I think it is insane. I am expecting non-modal Editors, simultaneous access to Editors and database Design, Intellisense to help me fill in the missing names, syntax coloring with a MONO-SPACED FONT by default (I have no idea how to read much less write code if it is not mono-spaced - how do you consistently align code statements for ease of readability/debugging?), etc.

And while I am talking about fonts... it is incredibly goofy that I have to repeatedly set a mono-spaced font as default in the various Editors. It is like the stored procedure Editor is disconnected from the view Editor and they are both disconnected from the rules Editor and the design table trigger Editor. And none of them obey the "Tools\Font..." menu choice. Hello?!? Is SQL actually one product or not, and do people actually use this? At least the editors remember their custom dimensions, but unfortunately they do not remember where they were located... which may not matter right now since the entire UI involves modal Editors, but still...

Much ADO about Nuttin'

I swear, without looking at sample script code from someone else which I knew worked, I could not have figured out how to use ADO from Windows Scripting Host. MSDN is great at telling me the properties and methods of the Connection object, the Command object, the Parameter object, etc, but it tells me absolutely nothing about how to effectively use ADO, what objects need to be Closed() and when, what objects need to be null-referenced, and what a successful sequence of steps look like to accomplish something like calling a stored procedure or making a SQL query. Yes, I found the Programmers Guide, Getting Started, ADO Fundamentals, Appendix, etc, but they seemed to presume that I knew the terminology, how they translated to the object model, and what objects belonged in conjunction, in what sequence, and with what effect. Now, I eventually figured out how the objects work with each other and in what sequence, but the experience could be better.

Unfortunately, KB articles on ADO gave me very little in terms of knowledge but lots of grief and frustration because there are SO MANY OF THEM. Many KB articles are just slightly different from one another, and many are basically duplicates of each other and probably 95% shared content. Then, there is the occassional KB which advocates doing something completely different for the exact same task of opening a database to call a stored procedure. And I still have no idea which one is better nor why.

I tried a couple of them, and I found in one case that a single variable assignment of the .Execute() method rendered the stored procedure's @RETURN_VALUE inaccessible. I still have no idea why that was the case, other than that I did not need the assignment and upon removing it, the sample code started working. Hmm...

ASP.Net 2.0 (aka Whidbey)

After I had all the fun wrangling with SQL and its interesting development environment and wandering through a simultaneous deluge of noise and deafening silence that is ADO, I arrived at ASP.Net 2.0.

Frankly, I had high expectations here because I had both seen and heard the hype about how you can drag and drop your way to create awesome, dynamic, and complex data-bound and data-driven web applications without typing in a single line of code and taking maybe a lunch hour to do it all. Sounds great! Sign me up!

I work closely with the ASP.Net team from a IIS core perspective, so I knew where to get the binaries and how to unattend install it all, opened up Visual Web Developer, and got ready to be amazed. And...

I was absolutely amazed.

To start with, the term "data-binding" had absolutely no meaning in my life until that moment. I had never used a data-binding control, never bothered with wiring an application to use SQL as a data repository, and certainly never even created an ASP.Net page before. And now, all of a sudden, it all came together and data-binding started making sense to me. Sure, it made me learn about SQL Views and how I should put as much logic/queries into SQL itself in the form of Views, Stored Procedures, and Triggers and leave ASP.Net to handle the client-side UI experience, but it was fun doing so. No nagging editors and dearth of information - just scrolling through the properties pane of the small number of objects, clicking either the "?" button or F1 help to get some quick info, and then just darn playing around in the UI to figure it all out.

In particular, the "Create a Connection" Wizard solidified in my mind how one should consume data in a data-binding relationship. Using the Wizard made more sense than my hours reading SQL syntax or building the relational data model. Your input data is either in a table or explicit query, or it is a view or stored procedure. You choose the table columns to data-bind, add some WHERE constraint clauses, and the binding gets done. If you want to get fancy you plug the data controls into each other so that depending on the selected ID the other control refreshes in response; I thought that was cool.

Hey, some of you experts probably think nothing of this since it is old hat, but give me a moment to reflect and go gah-gah... :-)

Ok, where was I. Oh, yes, and the other Wizard that really blew my mind was the Query Builder inside the Connection Wizard. At first, I had no idea why it was so compact and had a scrollable surface area, a grid view, a text box containing a starter SQL query, and a result pane with an "Execute" button. It wasn't until I typed a SQL query into the text box, clicked Execute, and saw how the rest of the window reacted that I realized how nifty of a tool it was.

Hey, this tool taught me more about INNER JOIN intuitively than my hours of reading SQL documentation and samples about the various types of JOIN. See, I was not kidding when I said that I knew nothing about relational databases. I definitely need to find the PM/Dev/Test of this Wizard somewhere down the hall and thank them. ;-)

Now, my first thought after using the Query Builder in Visual Web Developer was "where is the similar thing in SQL Enterprise Manager / SQL Query Analyzer?", and I went back to the SQL tools looking through every single menu option, left/right mouse click, context menu, etc. I eventually found it in SQL Enterprise Manager, in the most non-obvious place (to me, anyway) for such an awesome tool - right click on a Table, select "Open Table", and then "Query".

Huh? Why there? I mean, I wondered by it hundreds of times before because I would just select the "Get all rows..." option to manipulate the Table's contents. Now, if I had control of that codebase, I would have put it on ALL right click context menus under a catchy name like "Query Builder" that described what it does - because you need to do some aspect of Query Building to construct Views, Tables, Stored Procedures, Triggers... but I could be mistaken and that it is only tied to a SQL Table's operation. Go figure.

Ok, some of the Visual Web Developer UI is still goofy (for example, the column editor dialog cannot be resized, and it definitely contains long text that eventually needs resizing), but I consider them minor blemishes for the most part. The wealth of data-bound controls and a clickable UI that allowed me to experiment did more to help me along than any amount of reading on SQL, Stored Procedures, Triggers, Views, Tables, Relational Model, ADO, etc. I only wish I had started here first.


ASP.Net 2.0 definitely moved up a couple of notches in my mind in terms of coolness from this interaction. I still very much believe in native code, but maybe this managed code stuff isn't that bad...

I guess you live and learn...