adamkdean

software engineering

SQL INSERT with WHERE clause

By Adam K Dean on

So here I am, again, resigned to having to deal with SQL. I don't like SQL and it doesn't like me. One of the main reasons is because when I put a nicely formatted piece of code into Management Studio, it mangles it and disfigures it horribly as if saying "not only does this SQL not work but I have chewed it up and spat it back out at you."

I am currently writing a util to extract email addresses from a customer database and generate the SQL to insert them into our mailing list database. Rather than risk having two email addresses and therefore maybe spamming a customer, I wanted the get some SQL that would first check if the email existed within the database, and if not, then insert it.

After lots of horribly small examples SQL and about 45 Googles, I finally came up with some working code! Below you will see that I first check if a select statement does not exist. And then if it doesn't, I run the insert code. Nice and simple, yeah ...once it's done and working..

IF NOT EXISTS (SELECT EmailAddress FROM t_MailingList
 WHERE EmailAddress = 'test') 
BEGIN
 INSERT INTO t_MailingList
 (EmailAddress, AcceptsPostal, AcceptsEmail)
 VALUES ('test', 0, 1) 
END

P.S, if you get the compound errors like I did, then I suggest either 1) asking an experienced sqler, 2) keeping calm or my preferred method 3) a hammer. Enjoy!

Detect printing with CSS & ASP.NET

By Adam K Dean on

So, as in my previous post, I had an awesome idea of how to detect people printing a page of a website. Basically, when they go to print from our site, they get a different layout, that doesn't have anything that looks like a site. So surely if I just put a little tracker file in between the CSS file and the request, that'd work like a charm, right?

Wrong. In order to protect peoples privacy, all css files are now downloaded at page load, so every view registered as a print. Naturally, I checked what the bearded men of Google had to say, which wasn't much. HTC files, printer drivers and one even suggested something about an intranet...

So I posted on Dream.In.Code, asked the masses, and even RDC'd into my server to ask in #css on freenode irc.. then my own question gave me the answer.

To detect when someone prints a page on your site you just use CSS, it's really simple actually. Think, they will load the CSS when the page loads, but they won't process it unless someone prints, and you can use css to request images.. but .. how does CSS know if an image is an image or if it is a microwave meal? It doesn't until it requests it!

Don't forget to reference your stylesheet!

<link rel="stylesheet" href="print.css" media="print" type="text/css" />

So you just put your script inside an url()!!

body { background: url(PrintTracker.aspx); }

Works brilliantly, and the 'image' is requested every single time a page is printed, even the same one twice!

Awesome? I agree.

Update: It has been noted that print preview will fire this also. Bear that in mind.

Dynamic CSS with ASP.NET - in one line of code

By Adam K Dean on

Whether you want a separate sheet for each browser, or you want to track when people print a page from your site (as is my case), how do you get code to run in a CSS file? You can't. How do you get an ASPX file to serve up CSS? Well, you can't. But like House MD, I have the answer and I'll tell you eventually.

What you want to do is create an ASPX file, and in the Page_Load event, write whatever tracking or logic code you want, then simply redirect the page without any output.

protected void Page_Load(object sender, EventArgs e)
{
    // code here
    Response.Redirect("style.css", true);
}

And there you have it, Dynamic CSS with ASP.NET - in one line of code.

Simple abstract classes explained

By Adam K Dean on

Blogular activity has suffered a little here; but setting up a workshop and devising plans to build steam engine in said workshop will do that to you. Today some simple code, written to simplify showing abstract classes. Every tutorial I see they throw in stupid code that isn't really needed to show the primal functionality of abstract classes. So here is an example that has nothing more than what it needs.

class Program
{
    static void Main()
    {
        // here we create a happy and pass it to print mood to screen
        // happy is of type happy but also of type mood
        Happy happy = new Happy();
        PrintMoodToScreen(happy);

        // again, we create a mood, this one is a sad mood but still
        // a mood nonetheless
        Sad sad = new Sad();
        PrintMoodToScreen(sad);

        Console.ReadKey();
    }

    // here we take an object of the type Mood, which means we don't require
    // one method for sad, one method for happy and one for all others
    // and it means we dont have to box them into objects either
    static void PrintMoodToScreen(Mood mood)
    {
        mood.PrintMood();
    }
}

As you can see we pass the objects to a single method taking a Mood type, not Sad or Happy. Think about your local vets, you may have a GermanShepherd or a Poodle, but they are all derived from the Dog class. If that isn't enough, think about coffee, beautiful coffee, we have Coffee, then we have InstantCoffee and GroundCoffee, and then we have KenkoInstantCoffee and NescafeInstantCoffee, each would be a child of the other, Coffee -> InstantCoffee -> KenkoInstantCoffee etc. You could write a method to only accept instant coffee and then takes water and milk and makes a nice quick 'normal' brew.

abstract class Mood
{
    // protected means it's accessible to Mood, and to anything derived
    // from it, i.e. sad, happy, etc
    protected string CurrentMood { get; set; }

    // here we write a method to print the mood, we only need to code
    // it once, that's one reason why abstract classes exist
    public void PrintMood()
    {
        Console.WriteLine(CurrentMood);
    }
}

// we create the happy class and derive from mood
class Happy : Mood
{
    public Happy()
    {
        // we set the protected property, which is accessible to us
        // but not to anyone using a mood object
        CurrentMood = "Current Mood is Happy :)";
    }
}

class Sad : Mood
{
    public Sad()
    {
        // cheer up Sad class!
        CurrentMood = "Current Mood is Sad :(";
    }
}

If more explaining is needed, then I'm really not sure if there's any hope for you.

Good luck!

Get Average using SQL

By Adam K Dean on

More SQL again today, to say I'm not a fan, I'm doing a lot of it lately.

Todays snippet is quite cool, I was inspired by this post: http://www.dreamincode.net/forums/topic/190437-floating-point-division/

Basically, you return the average by counting up the price of every item in a table, then divide it by the number of rows in said table. All in SQL.

SELECT (SELECT SUM(Price) FROM t_Items) /
    (SELECT COUNT(*) FROM t_Items)
        AS Expr1

Enjoy!