.NET Playground

WpfStyles.com (http://www.wpfstyles.com) launched the other day (hooray!). The site lets WPF and Silverlight developers and designers upload their components and share them with the community.

It's all free, so not only are you able to share your components, but you can also download those that other likeminded developers have put up there.

If you have any such components that the community will find useful, throw them up there. I urge you to get behind this resource and show your support.

I've gone through a number of phases in SSIS. The first was fascination, the next was anger, then came love, and now I'm somewhere in the middle. One major flaw I've noticed is how SSIS handles double quotes (") in imported flat file data, or should I say how it doesn't handle it at all?

Simple scenario, I have a *csv file that I'm importing to a db table. The flat file is all text delimited using double quotes. A number of the fields contain literal double quotes, which in any CSV text delmited implementation you would normally escape by using another double quote delimeter. To illustrate:

This:

he cried out "SSIS!" in frustration

Becomes This:

"he cried out ""SSIS!"" in frustration"

Simple, standard csv behaviour, parses fine in excel.

Try importing to SSIS and your import will crash out, and if you try to preview that row you'll get: "The flat file parser does not support embedding text qualifiers in data"

Solution? The only way I've found to get around this is to write your own escape functionality. I piggy-backed off html encoding and replaced literal quotes with "  , and then used derived columns with REPLACE(ImportedField,""","\"") to change them back prior to putting them in their destination table.

I hate having to write work arounds like this, especially for functionality which should be part of the underlying core system. If anyone has a better way, please drop your comments in below.

Another little data manipulation I've had to do recently is to split a heap of sql server datetime values into 15 minute segments (eg: 10:00, 10:15, 10:30 etc). Although there's no inherit function that I know about in sql server that achieves this, there's a nifty little trick using the modulus operator (%) that can do this.

Where DateTimeval is the column that holds the... date time value.

Firstly the < (15/2) part determines the range of the midpoints (ie: the midpoint 15 ranges from 7.5 to 22.5).  Next it gets the minutes part from the value (eg: 10, 49, 15, etc), then does % 15. This will give the remainders (10, 04, 00). If the remainder is less that the range value (ie: < (15/2)), then by simply subtracting the remainder from the original value will give us the midpoint (eg: N/A, 49 - 4 = 45, 15 - 0 = 15).

If, however, the remainder is > (15 / 2), then we need to add the difference of (15 - remainder) to get the upper midpoint value (eg: 10 + (15 - 10) = 5, 45, 15).

And voila, the date time values are segmented into 15 minute chunks.

Of course you can substitute 15 with however many minutes  you want to segment by (eg: 5, 10, 30 etc).

Performance has been Ok. I ran this over 200,000 records and it took about 3 seconds on a shared dev server. Not bad.

At times, especially when working with cubes with date/time dimensions, it's necessary for me to dynamically generate a list of date values for use in those dimensions. That single date dimension often services a number of cubes, and the range of date values can be determined from the min/max date values from serveral tables.

I've written the following SQL that will retrieve the min/max date values, and create a single record for every day in that min/max range. It's useful to turn this sql into a view which will refresh itself automatically every time the cube calling it is processed.  

Problem: How can I QUICKLY create an install script automatically in sql server every time I change my database? Creating an install script in sql server is pretty easy thanks to the "Script Database As..." command. However, this is generally a long winded process that doesn't give you too much control.

In this post, I propose a solution to create build scripts with the following requirements

1. Don't need to write any code
2. Easily maintainable
3. Can script schema AND data
4. Can specify which objects to script
5. Can run automatically before an MSI setup/deployment project is built
6. It has to be FREE

The first thing you need to do is download Microsoft's Database Publishing Wizard (http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en)

Next, in your development sql server, add two new accounts named: ScriptSchema and ScriptData. Make sure they are created as a sql login. At this point, don't give them any other access than public access to the database you want to script.

Open the database you want to script. For each table/view/stored proc etc that you want to include in the install, grant view definition access to the ScriptSchema user as follows:

grant view definition on <schema>.<object name> to scriptschema

Next, for each table that you want to script the data for, grant select permission on that table to the ScriptData user, ie:

grant select on <schema>.<table name> to scriptdata

You can double up, so for instance if you want to script the creation and population of data of a certain table called [dbo].[customers] you'd run the two commands:

grant view definition on [dbo].[customers] to scriptschema

grant select on [dbo].[customers] to scriptdata

Once you've set up the permissions, you can check out what will be scripted by connecting to the database as the scriptschema and scriptdata users. As you've now defined what will be scripted, it's time to do the scripting itself.

Jump out into a cmd.exe, and run the two commands;

sqlpubwiz script -C "data source=[server]; initial catalog=[database]; uid=scriptschema; pwd=password;" schema.sql -schemaonly -f

sqlpubwiz script -C "data source=[server]; initial catalog=[database]; uid=scriptdata; pwd=password;" data.sql -dataonly -f

The first command connects to the database using the scriptschema account, and generates the definition of all the objects it can see, ie: those which you gave grant view definition access to.

Likewise the second command connects to teh database using the scriptdata account and generates the scripts to insert the data, based on all the tables it can select from.

You now have two scripts: schema.sql, data.sql. You can add these to your msi/setup projects in visual studio (outside the scope of this post). Furthermore you can then add the two commands mentioned to the pre-build events of your setup's resource project to build the install scripts prior to the MSI etc being built.

I really like this solution to automatically generate sql install scripts. I feel it's elegant, there's no code to be maintained, and no manual updating of sql scripts every time you change the database.

There are times when you need to write a query joining on dates, that is the day of a date, but not worry about the time portion. Without using the between x and y, as sometimes this isn't possible from places like SSAS Data source views, what's the most efficient way to remove the time part of a datetime?

There are a number of ways to drop off the time part, as shown here:

select CONVERT(datetime, CONVERT(varchar(10), c.date, 111), 111) from dbo.phonecalls c
go

So it appears that casting the date to a 5 point decimal, dropping the decimal portion, and then casting back to a date time is by far the most efficient. I'm putting this down the fact that it's a completely numerical operation, and doesn't resort to casting to different value types (ie: varchar).

Saw this little strip today from http://xkcd.com/

SQL injection's a nasty by-product of poor development. Anywhere a user can enter data, there's the potential for an exploit. In my opinion, performing input validation on the web/ui layer is repetitive and insecure. I've always taken the approach to move all that to the Data layer.

Here resides a few exclusive set of methods that are the only ones that can interface with the database. Performing input checking here ensures that everything that goes to the database isn't going to cause any grief. Furthermore you can do it once and not have to worry about validating input from every new field you put on your display layer.

Furthermore it's unecessary and bad practice to be generating sql in any of your layers except for the few times when it's necessary to gen dynamic sql in stored procs. Effectively if you design towards these sorts of goals, you can pretty much guarantee little Bobby tables won't be causing you any issues.

This little one isn't very obvious and can cause a lot of hair pulling, so I decided to write it down here.

Basically in reporting services you can make use of reporting parameters using a combination of dataset query parameters and Member(<<string>>) functions. Hence you can define a parameter that returns a string ie: '[Organisation].[Hierarchy].&[1232]'

Sometimes, like today, I need to send down just the Ids themselves, as these get plucked directly out of a querystring, so I might have say: 1232,144,664  as my key values. I wrote a quick CLR function that I plugged into analysis services which will convert n values into a string of:

{[Organisation].[Hierarchy].&[1232],[Organisation].[Hierarchy].&[144],[Organisation].[Hierarchy].&[664]}, which then gets returned as a set and life goes on.

However, when I was defining the query parameters, @DepartmentID (and all the other parameters) needed a default value. This was annoying as this value was bound to the [Organisation] dimension, and hence it would insist on binding to the values therein.

Little did I know that you're allowed to define a query parameter that's NOT bound, although it still needs a default parameter. Unbinding from the dimension will save a headache, and let you pass parameters in as mentioned.

GZip / DEFLATE compression's a quick and simple trick you can employ to reduce the size of your pages that get sent down the wire. IIS supports this natively (IIS5 & IIS6, the latter improving the process somewhat). The best part is that it's quick simple to get up and running.

Jump onto the box you run IIS in as admin, open up a new cmd, and punch away the following:

go to your InetPub\adminscripts directory

net stop iisadmin   //To stop the services

cscript adsutil.vbs set W3Svc/Filters/Compression/GZIP/HcScriptFileExtensions  "aspx" "asp" "dll"  //Register the additional types to compress

net start w3svc   //Start the services back up

This sort of technology has been around for a while, so only users running on the most outdated browsers might have a few issues. But for the rest of us, compression can significantly reduce download times. For instance a Reporting Services report would come down as 500KB uncompressed, but only 50KB when compressed with GZip. That's 10% of the original.

That's quite significant when you're looking at slow connections, and the additional CPU load/time to compress is irrelevant when compared to savings in the download time side of things.

A quick side note is that there's hardly any difference between GZip and DEFLATE compression technologies. They'll both give you pretty much the same ratio's, but from memory DEFLATE contains a number of additional header fields, hence will be a number of bytes larger than its GZip counterpart.

I pay a bit of money for software each year - whether it be outright payments for off-the-shelf type packages, or bundled into hardware that I buy. But the fact is, purchasing software is neither cheap or convenient. Even after you burn your internet quota downloading it, or wait 2 weeks for a disc to arrive in the mail, your computer turns into a zoo of apps that need you to manually update them, or are continuously blowing up balloons in your tray telling you that they're trying to do it themselves.

Even if they get updated, what are the chances that they're not compatible with the other mash up of software on your PC? That's if you have a PC - what about Mac & Linux compatibility?

With so many headaches its suprising that client software is such a big industry. It goes without saying that prior to the internet and high speed DSL links there wasn't much of an option for anything else. But now companies like Google are trying their hand at their own "Office" type software, but offering it all online.

It's not bad either. You can create your own documents, even open MS Word docs, and when you're done save it to your space in Google so you can access it whereever you happen to be. Sure it lacks all the features of the MS one, but it's acceptable as a word processor, and a trail blazer in terms on online productivity.

Futhermore, no longer do you have to upgrade your own software, worry about compatibility issues, or even think which hard drive you saved that document on - it's all contained in the cloud; so as long as you have a link then you can access your stuff.

The best part about it all is that it's free. Free in the sense that their advertisers pay for you to use it. But as online software increases its presence and abilities, is such a sponsored solution really feasible, and is it able to pull a decent chunk of revenue?

Probably not.

So how about the other options? Aside from the aformentioned advertising model, there are a number of ways web based software can make some cash:

1. Subscriptions
2. Free usage, but additional costs for additional features
3. Pay per use (purchase usage credits directly from the site)
4. Trickle payments (ie: $0.01 per session, but deducted from your eg. paypal account)
5. Get your software hosted/purchased from a big player with established revenue models
6. Purchase 'package' subscriptions - like Pay TV

Perhaps there is no clear winner. Some would will continue to opt for advertising, some will force subscriptions; some might ofter a choice to either pay for the service, or accept advertising. The point is that as software moves further into the online environment, pricing models and structures will differ signifcantly than traditional off-the-shelf packages.

It'll be interesting to see how it unfolds as time goes on, but ultimately we all end up paying for it somehow.