Wednesday, May 17, 2006
SQL EverywhereI read Steve Lasker's blog post about SQL Everywhere http://blogs.msdn.com/stevelasker/archive/2006/04/10/SqlEverywhereInfo.aspx and just thought, "This is the coolest thing". SQL Everywhere basically means that Microsoft is making SQL Server Mobile edition available on all devices including desktops, tablets, oragami devices, etc... "So what", you say? The cool thing about it is that this is a file based database. This version of SQL Server doesn't run as a service and you don't have to install it. It runs completely out of .dlls that you install as part of your app. There are a lot of instances where you want a simple yet powerful data store, but don't want to have to install SQL Server to get it. This sure beats persisting DataSets to XML as a local data store! In addition, you can have this local DB participate in replication to sync with a "real" SQL database. Add the power of DLINQ to allow you to run queries right in the C# or VB.NET language and you have some incredible capabilities at your fingertips.
Posted @ 9:13 AM by Yeager, Mike (firstname.lastname@example.org) - Comments (1)
Monday, May 15, 2006
My first experience with Reporting Services
Aside from running through a tutorial in the SQL Server 2000 version, I hadn't used reporting services - until two weeks ago. I needed to put a couple of simple reports into an app I was working on. While I've been working in .NET since the original betas, I've never had the need to work with reporting, not even the Crystal reports built into earlier versions of VS. I started looking around and found that I had a two options right out of the VS 2005, Crystal Reports and SQL Server Reporting Services. So what did I do? Well, I tried both of them of course, so I could see which would fit the bill best.
As expected, the Crystal reports documentation was, let's say, less than optimal. After almost an hour, I still couldn't get a report to come up. That day, I didn't have internet connectivity, so I couldn't find sample code and cut through the docs. So I tried reporting services. In about ten minutes, I had a report with a TON of functionality for free. I found out that in the new version, you can create reports that are 100% local and don't require SQL Server. Very cool.
I did find some quirks that cost me a lot of time and thought I'd share those with you.
1) DO NOT PUT SPACES IN FOLDER OR FILE NAMES FOR REPORTING SERVICES.
Since I like to keep things organized, the first thing I did was make a folder for my report definitions. Using my super power for creating interesting and meaningful names, I named the folder "Report Definitions" <g>. I dropped my .RDLC file in there. (The "C" in RDLC stands for "client" and is used for local reports that dont' reside in SQL Server.) I then tested and everything worked as planned. Cool. Then I installed on a separate machine and found that it was searching for the file in a hard-coded path from my development machine. For my purposes, I wanted to embed the report definition in my .EXE. No problem, just use the ReportEmbeddedResource property (under the LocalReport group in the "Misc" section) of the ReportViewer control, then set the .RDLC file to be an embedded resource. No problem, except that it didn't work! The docs tell you that the resource name is in the format "Namespace.FileName". In my case that was SX.TaxRateListing.rdlc. What the docs aren't so clear on is that if you put the file in a folder instead of the root, you use this syntax: Namespace.Folder.FileName. So I changed it to SX.Report Definitions.TaxRateListing.rdlc. Lo and behold ... it still didn't work. Next I found out that if you have spaces in your path, they get changed to underscores. So next I type SX.Report_Definitions.TaxRateListing.rdlc and it worked! ... for a while. One of the ReportViewer control's "Tasks" (on the control's smart tag) lets you choose the report layout that you want to use. After you choose the layout, you have to go back into the smart tag and select a new task that only shows up after you've already selected a layout called "ChooseData Sources". If you then change your data source, you have to go back in and choose "Rebind Data Sources". Behind the scenes, this control is creating some code that makes the data available to merge with the layout to create a report. For some reason, when you do this, it breaks the ReportEmbeddedResource property. The short version is: DON'T PUT SPACES IN FOLDER OR FILE NAMES FOR REPORTING SERVICES. Removing the space from the "Report Definitions" folder and changing the ReportEmbeddedResource property to reflect that made verything work.
2) DO NOT USE MORE THAN ONE CUSTOM EXPRESSION FOR HIDING ITEMS IN THE SAME BAND OF A REPORT DEFINITION.
I had four sets of grouped items in the detail band and wanted to print only one of the 4 sets, depending on a value in the data. So I put the first set of textboxes on the detail band and tested it out. So far so good. I then copied and pasted that set of items (another quirk, you can only copy items one at a time, you can't select a bunch of them and copy the entire group), changed the background color so I could easily see which group was printing and tried it again. Both groups of items printed - good. I then added an expression (Reporting Services reports use a subset ofr VB.NET ~ no C# support) that looked something like =Fields!TreeLevel.Value <> 1 into the custom expression dialog for hiding an item for each control in the first group. I ran it and when the TreeValue in the data was not 1, the first group of items was supressed. Very cool. All I had to do now was add the third and fourt groups of items to the detail band and put a similar expression in each control of each group. That's when it bombed. Unfortunately, when the ReportViewer control runs across an error, it simply displays the error on the control - you can't jump into the debugger. At least I haven't figured out how to yet. This is very familiar behavior if you've worked with Visual FoxPro's reports. As long as you have only one custom expression in the band, in my case =Fields!TreeLevel.Value <> 1, you can add it to as many controls as you like. If you add a second expression, say =Fields!TreeLevel.Value <> 2, then it breaks. I worked around this by instead creating 4 separate detail bands and appling the expression to the entire band - not the individual items on the band.
Overall, I'm very impressed with the tool, but like every reporting tool I've ever used, there are quirks that you must come to know. I hope this saves you some time.
Posted @ 10:31 AM by Yeager, Mike (email@example.com) - Comments (1)
Tuesday, May 02, 2006
Don't Pass In Front Of Your Own Net!
I remember with astonishing clarity my first "real" hockey game with full equipment and the beautiful pass I made to one of my teammates ... Which was intercepted by the opposing team and turned into a one-timer shot that scored a goal on us. Nobody really cared because it was a rec league, but I sure cared (probably more than I should). One of my teammates who'd played a lot skated over and said, "Don't pass in front of your own net. It gives the other team a possible scoring opportunity that they shouldn't have. We want to keep the puck out of the area in front of our net whenever we can. Pass it down the boards." I learned THAT lesson well and never forgot it. All good coaches know stuff like that and pass it along to their teams. I later passed it along to my team. I'd ask, "Where do we NEVER pass the puck" and thirteen 7 and 8 year olds would shout back, "In Front Of Our Own Net!” Sometimes you get away with it, but when you don't, you usually find out too late.
There are a few tidbits like this in programming that I keep in mind.
Some of my favorites are:
l 3 tiers is a crappy rule of thumb. The number of tiers in a good design depends on how robust, scalable and interoperable you need the end result to be.
l If your service object can't sit in a pool of 10 instances on an entirely different machine then you haven't done a good job.
I have more opinions on tiered architecture than this server will probably hold, so I won't get into that too deeply today, but I will tell you what "the ultimate" solutions probably looks like. For one, I don't particularly like the word "tier" because it implies a hierarchy and that doesn't really fit. A very robust, scalable, interoperable application is more like a collection of specialized parts.
l Data Store Part
l Data Access Part
l Data Object Part
l Business Logic Part
l Control Logic Part
l Communication Logic Part
l Binding Part
l UI Part
Of course, not every app needs all that. I’ll even admit to having written apps where I pulled data off the disk and presented it to the user all in a single form with no separation of functionality at all. I’ve even done it with drag and drop. I know, I’m probably going to hell for that, but I’ll risk it. When it’s a single-user app for a fiend that I need to do in a couple of hours and the app will have a lifespan of a weekend, this gets the job done, but if you’re doing this for a client and doing it professionally, you never, ever pass in front of your own net!
Here’s how I describe each of the parts I listed.
Data Store Part – You store data in it and usually enforce data rules shared with other parts.
Data Access Part – Pulls data from a data store or stores and puts it in a generic format.
Data Object Part – Contains data from the data access part and usually enforces data rules shared with other parts.
Business Logic Part – Data rules plus business processes. You use Data Object Parts as building blocks for these.
Control Logic Part – This is the glue that binds the parts together. In a way, you can think of it as the main program. You use Business Logic Parts as building blocks for these and these also contain business processes that span multiple business logic parts.
Communication Logic Part – If you need to expose parts of the app to the outside world, you do it here.
UI Binding Part – Just as you separate Data Store Parts from Data Object Parts with a Data Access Part (Data Access Layer), you separate Business Logic Parts and Control Logic Parts from UI Parts.
UI Part – This is pretty much the realm of designers and layout people (and that may be the programmer in a lot of cases <g>).
Things like rules on data that say that the start date can’t be before the end date “should” be shared among parts. For example, the data store part should enforce that rule as should the data object part and possibly the UI part. Unfortunately, the existing tools aren’t quite there yet. A surprising number of people pile data access, data object and business logic into a single pile and call it a “business object”. I don’t like the term and I hate how intermingled and co-dependant everything in there becomes.
What do I mean by “If your service object can't sit in a pool of 10 instances on an entirely different machine then you haven't done a good job”? A “service object” is my term for an object that you can use as a black box component. You ask it something and it sends a response. I think that these should be stateless and usually exist in a pool. If you can fire up 10 of them in a pool, you can probably handle a thousand clients. Of course that means they need to be stateless, because as a client app, you never know which one of the 10 you’re going to get on each call. Of course, you want this to work out-of-process so you can move it from a local .cs file to a local .dll to a .dll on a server to a .dll on a server farm (maybe exposed as a COM object, maybe as a web service, maybe as Indigo…) as you need to scale your app. Service objects are very useful and very few programmers do a good job of building them. One of the things I see a lot and hate is when one of these things exposes a property. Properties are for things that have state. Every interaction with a service object should be in the form of a request and a response – methods only! The other thing I see is dependencies to the outside world. If it’s not a method call, it’s wrong, just plain wrong. Passing parameters by reference is also wrong.
Depending on state is also wrong. If you can’t make every single call to a completely different instance of your service object and get the same result, then you’re doing it wrong. Some objects should have state and some shouldn’t.
Decide up front if your object should have state and don’t use state where you shouldn’t. There are methods of enforcing this stuff. For creating COM objects for example, try doing all of your testing with VBScripts where you shut down the COM object and create a new one between every call and don’t use any variables between calls in the VBScript.
I learned each of these lessons that hard way. You don’t have to. Remember, “Don’t pass in front of your own net”.
Posted @ 4:49 PM by Yeager, Mike (firstname.lastname@example.org) - Comments
Monday, May 01, 2006
One less reason why identity columns suck
There still is that replication 'thing', but one of the things that was tough with identity columns was retrieving the identity value from an INSERT statement. In the past, you did something like:
INSERT INTO MYTABLE (descrip) VALUES ('my new item that I don't know the PK for')
Of course, if a trigger fired and inserted a record, you'd get the identity value for THAT row instead. You could use @@SCOPE_IDENTITY to take care of the problem, but if you're updating a bunch of tables, this method got ugly quick.
I recently ran across a new clause that you can use with INSERT, UPDATE and DELETE in SQl Server 2005. The OUTPUT clause lets you return values from the command. This is great not only for identity columns, but also calculated columns and columns with defaults that aren't static like datestamps. For me, this is one of those, "Well it's about time" things.
SQL BOL gives you this example:
DECLARE @MyTableVar table( ScrapReasonID smallint,
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate
VALUES (N'Operator error', GETDATE());
--Display the result set of the table variable.
SELECT ScrapReasonID, Name, ModifiedDate FROM @MyTableVar;
--Display the result set of the table.
SELECT ScrapReasonID, Name, ModifiedDate
Posted @ 12:26 PM by Yeager, Mike (email@example.com) - Comments (1)