Web development #7: Dynamic page updates with AJAX

So in theory you should now have all the tools to create the most awesome websites. However, I don’t think any tutorial on web development is complete without doing some AJAX. No idea what I’m talking about? Read my previous posts.

  1. Web development #1: Internet and the World Wide Web
  2. Web development #2: Our first website using HTML
  3. Web development #3: Styling our page with CSS 3
  4. Web development #4: PHP in the back
  5. Web development #5: User input with HTML Forms
  6. Web development #6: Getting interactive with JavaScript
  7. Web development #7: Dynamic page updates with AJAX
  8. Web development #8: Where to go from here

So what is AJAX all about? It’s an abbreviation for Asynchronous JavaScript And XML. It enables us to send a request to the server and receive a result without having to refresh our entire page. Now that’s pretty awesome! What’s also pretty awesome is that we don’t need to use XML. As Linus Torvalds, creator of Linux, already said “XML is crap. Really. There are no excuses. XML is nasty to parse for humans, and it’s a disaster to parse even for computers. There’s just no reason for that horrible crap to exist.” So what we’ll be using is a form of AJAX called AJAJ, where the last J stands for JSON (JavaScript Object Notation). To make it a bit more confusing, we’ll just call it AJAX anyway. Oh yeah, and it doesn’t have to be asynchronous either…

JavaScript objects and JSON

First of all we need to know what JavaScript Objects are and what they look like. After that JSON will come naturally. So JavaScript has objects and much like objects in other languages JavaScript objects are just wrappers around certain functionality. The difficulty in JavaScript, of course, lies in that anything can be anything and then can become anything else… Let’s just look at an example.

// Creates an empty object.
var obj = {};
// Gives the object a firstName property.
obj.firstName = 'Sander';
// Gives the object a lastName property.
obj.lastName = 'Rossel';
// Gives the object a getFullName function.
obj.getFullName = function () {
    return this.firstName + ' ' + this.lastName;
};

// Alternative for the above.
var person = {
    firstName: 'Sander',
    lastName: 'Rossel',
    getFullName: function () {
        return this.firstName + ' ' + this.lastName;
    }
};

Now this wouldn’t be JavaScript if we didn’t have even more methods to create objects (JavaScript also knows constructors). We’re not interested in those though. What we’re interested in is the second method I’m using. Notice that, to create an object, we use curly braces. We can then basically add anything to that object by simply defining keys (property or function names) and values (default values for properties or implementations for functions) seperated by colons. Each key and value is seperated by a comma. And of course objects can contain other objects too.

var outerObj = {
    innerObj: {
        someValue: null
    },
    anotherInnerObj: {
        innerInnerObj: {}
    },
    message: 'Phew, what syntax!'
};

You can work with objects as ways of namespaces so you can keep the ‘global namespace’ clean and minimize the chance for name conflicts with other libraries. Objects are also often used to create some sort of options that are given to a function. A function then simply checks if some property or method on the option object exists, what its value is, and acts accordingly. We’ll see this usage a little later when working with AJAX.

So then, what is JSON? It’s simply some text using almost the exact notation we just saw. So let’s look at some examples.

{
    "firstName": "Sander",
    "lastName": "Rossel"
}

{
    "innerObj": {
        "someValue": null
    },
    "anotherInnerObj": {
        "innerInnerObj": {}
    },
    "message": "Phew, what syntax!"
}

That’s looking pretty familiar now, doesn’t it? And, if you compare it to XML, it’s a lot more compact, saving you precious bits and bytes when you’re sending your data over the internet!

Making an AJAX request

So let’s look at an AJAX example. I’ll start very simple making a synchronous request that returns just plain text (so no XML or JSON) when the page loads. You’ll know that the request doesn’t refresh the page because if it would the page would keep refreshing (as it makes the request upon loading). Alternatively, you can track all requests your browser makes using your browsers developer tools (press F12 in IE, FireFox or Chrome and navigate to the Network tab). I’m using jQuery to get the page load event and to show the result of our AJAX request. So here’s the HTML (in a file called Ajax.html):

<!DOCTYPE html>
<html>
    <head>
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
        <script type="text/javascript" src="Ajax.js"></script>
    </head>
    <body>
        <p id="result"></p>
    </body>
</html>

Here’s our JavaScript (Ajax.js):

$(function () {
    var ajax = new XMLHttpRequest();
    ajax.open('GET', 'Ajax.php', false);
    ajax.send();
 
    $('#result').text(ajax.responseText);
});

And finally our PHP (Ajax.php):

<?php
    echo 'Result of AJAX request!';
?>

So all of these files go into the htdocs folder of your XAMPP installation and you’ll have to access your page by browsing to localhost/Ajax.html (notice I named all files ‘Ajax’). So what’s really interesting here is that in my JavaScript I’m using an XMLHttpRequest object. So we call the open method and pass in a string specifying the HTTP method, GET or POST. Next we’re telling it what URL to navigate to and last we’re specifying whether the request should be asynchronous (we’re calling it synchronous). Then we send the request to the server. So, because we’re calling it synchronous the browser blocks until we get response from the server. That means that on our next line of code we simply have our result, ajax.responseText. Now you’ll never do this, in fact, our browser even gives off a warning saying that this method is deprecated because it negatively affects the user experience. In our case we won’t notice the blocking, but if we’re downloading larger chunks of data our page will freeze up completely!

So let’s make that call asynchronous so the user can go about doing his business while our page gets our data for us. This isn’t actually to hard. We simply change our JavaScript to the following:

$(function () {
    var ajax = new XMLHttpRequest();
    ajax.onreadystatechange = function () {
        if (ajax.readyState == 4) {
            $('#result').text(ajax.responseText);
        };
    };
    ajax.open('GET', 'Ajax.php', true);
    ajax.send();
});

So I’m specifying an onreadystatechange function which gets called when the state of our ajax object changes. State 4 means it’s done and we can get the result.

Now there are quite some options we can set and check, but I’ll not get into that. You can check them on the XMLHttpRequest documentation page and play around with them yourself. Instead, we’re going to use jQuery to make AJAX requests.

Using jQuery for AJAX requests

You might be thinking if there’s anything jQuery cannot do? There’s actually plenty of stuff you don’t want to do using jQuery, but AJAX isn’t one of them. jQuery makes AJAX pretty easy actually. We were already using jQuery, so we can simply edit our JavaScript.

$(function () {
    $.ajax({
        type: 'GET',
        url: 'Ajax.php',
        complete: function (jqXHR) {
            $('#result').text(jqXHR.responseText);
        }
    });
});

So remember I was telling you about using an object to specify options? This is one such an example. We’re passing in an object and specify the type, the url and a function that will be called when the requests completes (jqXHR stand for jQuery XMLHttpRequest, but you can name it anything). We could’ve passed in much more, but we didn’t. So this does the same as the code we had earlier.

The jQuery.ajax function can actually do a whole lot of stuff! Next we’re going to pass in some parameters and echo them on our page.

$(function () {
    $.ajax({
        type: 'GET',
        url: 'Ajax.php',
        data: {
            artist: 'Led Zeppelin',
            song: 'Immigrant Song'
        },
        complete: function (jqXHR) {
            $('#result').text(jqXHR.responseText);
        }
    });
});

And now we need to alter our PHP too.

<?php
    if (isset($_REQUEST['artist']) &&
        isset($_REQUEST['song']))
    {
        $artist = $_REQUEST['artist'];
        $song = $_REQUEST['song'];
        echo "The song '$song' by $artist rocks!";
    }
?>

So that was pretty easy, right? We just passed in a JavaScript object with some properties and they were converted to parameters in our PHP automatically! So how does this work the other way around? This is where JSON comes into play! JSON stands for JavaScript Object Notation, so it should be pretty easy for JavaScript to create actual objects that we can use. So let’s start by modifying our PHP so that it returns JSON.

<?php
    class song
    {
        public $artist = NULL;
        public $name = NULL;
    }
 
    if (isset($_REQUEST['artist']) &&
        isset($_REQUEST['song']))
    {
        $song = new song();
        $song->artist = $_REQUEST['artist'];
        $song->name = $_REQUEST['song'];
 
        header('Content-Type: application/json');
        echo json_encode($song);
    }
?>

First of all, I’m using an object to store our data. We’ve seen this before. I’m setting our response header so that it tells our browser it’s json. Then I’m using the json_encode function to convert our object into JSON. If you now check out the result in your browser you’ll notice that we’ve received some actual JSON!

But what we really want is to use this JSON in our JavaScript as if it were just an object. Actually this is pretty easy! We can get the object from the XMLHttpRequest.

complete: function (jqXHR) {
    var song = jqXHR.responseJSON;
    $('#result').text("The song '" + song.name + "' by " + song.artist + " rocks!");
}

And that’s it. Alternatively, we can use the ‘success’ option, a function that passes us the returned data, the status of the request and the XMLHttpRequest.

$(function () {
    $.ajax({
        type: 'GET',
        url: 'Ajax.php',
        data: {
            artist: 'Led Zeppelin',
            song: 'Immigrant Song'
        },
        success: function (song, status, jqXHR) {
            $('#result').text("The song '" + song.name + "' by " + song.artist + " rocks!");
        }
    });
});

And likewise there is an ‘error’ option.

$(function () {
    $.ajax({
        type: 'GET',
        url: 'Ajaj.php',
        data: {
            artist: 'Led Zeppelin',
            song: 'Immigrant Song'
        },
        success: function (song, status, jqXHR) {
            $('#result').text("The song '" + song.name + "' by " + song.artist + " rocks!");
        },
        error: function (jqXHR, status, errorThrown) {
            $('#result').text('An error occurred: ' + jqXHR.status + ' ' + errorThrown);
        }
    });
});

Notice that I’m calling ‘Ajaj.php’, a page that doesn’t exist. Naturally we’ll get 404, Not found. When using the ‘complete’ option we used earlier you’d have to check for success yourself, our current implementation would actually break our page if we called ‘ajaj.php’.

So there’s actually quite a bit to think about when using AJAX. Did the server return a result, what kind of result did it return, was the request successful, etc. We can use the XMLHttpRequest object for this. Check ‘status’ to see if our request was successful (status 200 means success) and then ‘responseType’ and any of ‘response’, ‘responseText’, ‘responseJSON’ or ‘responseXML’ for the result.

Posting data

So in the previous examples we’ve taken a look at the GET method. In the next example I’m going to take our movies page we created in my previous blog posts, Web development #4: PHP in the back and Web development #5: User input with HTML Forms. So first of all we’ll have to edit the PHP file. We want the form to go, because that refreshes the page. We also want to add jQuery and a custom JavaScript file to our header. Last, we need to give some ids to some elements because now we need to update the page ourselves once we add a new movie (after all, the page isn’t refreshing). So without further delay, here’s the modified PHP.

<?php
    if (isset($_POST['movieName']))
    {
        $movieName = $_POST['movieName'];
        if ($movieName)
        {
            file_put_contents('movies.txt', htmlspecialchars($movieName) . "rn", FILE_APPEND);
        }
 
        exit();
    }
?>
<!DOCTYPE html>
<html>
    <?php
        function fileAsUnorderedList($fileName, $notFoundMessage)
        {
            if (file_exists($fileName))
            {
                echo '<ul id="movies">';
                $lines = file($fileName);
                foreach ($lines as $line)
                {
                    echo '<li>' . htmlspecialchars($line) . '</li>';
                }
                echo '</ul>';
            }
            else
            {
                echo $notFoundMessage;
            }
        }
    ?>
    <header>
        <title>My favourite movies!</title>
        <meta charset="utf-8">
        <meta name="description" content="A list of my favourite movies.">
        <meta name="keywords" content="Favourite,movies">
        <meta name="author" content="Sander Rossel">
        <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
        <script type="text/javascript" src="Movies.js"></script>
    </header>
    <body>
        <h1>My favourite movies!</h1>
        <p>
        <?php
            fileAsUnorderedList('movies.txt', 'No favourite movies found!');
        ?>
        </p>
        <h2>Add a movie!</h2>
        <div>
            <label for="movieName">Movie name</label>
            <input type="text" name="movieName" id="movieName" />
            <button id="submit">Submit</button>
        </div>
    </body>
</html>

And then we need to add a JavaScript file (I’ve called it Movies.js) to add an event handler to our button click event which takes the value of the input, posts it to the server and on success adds the movie to our list and empties the movie input.

$(function () {
    $('#submit').on('click', function () {
        var movieInput = $('#movieName');
        var movieName = movieInput.val();
        if (movieName) {
            $.ajax({
                type: 'POST',
                url: 'Movies.php',
                data: {
                    movieName: movieName
                },
                success: function (data, status, jqXHR) {
                    $('#movies').append('<li>' + movieName + '</li>');
                    movieInput.val(null);
                },
                error: function (jqXHR, status, errorThrown) {
                    alert("An error occurred while adding movie '" + movieName + "'.");
                }
            });
        };
    });
});

And there you have it. Looks complicated? I guess it is a bit, but once you get the hang of it it’s not so bad. And luckily the documentation is rather good!
Another cool addition to our page would be pagination. Let’s say we are going to add hundreds of movies. Getting those all in once might slow down our page quite a bit. So we can use AJAX to get just ten, or twenty, or fifty at a time. The page could load them on a button click or when we scroll to the end of the page (that is, get the movie titles and add them to our list using jQuery, also make sure you ‘remember’ what page you’re on). This principle is called pagination and can greatly enhance user performance (and for large websites it’s even a must).

So that’s it for AJAX. And actually, that’s it for web development! You’ve now seen everything involved in creating awesome, responsive web pages. Of course there’s still a long way to go as we’ve only scratched the surface of web development. We’ve seen the tools, but are by no means expert in any of them. In my next, and last, blog post I’ll give you some tips on frameworks and libraries that can help you create the pages you want.

Stay tuned!

Web development #6: Getting interactive with JavaScript

We’ve come pretty far by now! We’re almost there actually. After reading this post you should be able to create pretty awesome websites using the full web development stack! So what’s left for us to discover? We have seen we can build dynamic websites on the back end using PHP, but what about being dynamic once the page is loaded? Well that’s where JavaScript comes into play!

  1. Web development #1: Internet and the World Wide Web
  2. Web development #2: Our first website using HTML
  3. Web development #3: Styling our page with CSS 3
  4. Web development #4: PHP in the back
  5. Web development #5: User input with HTML Forms
  6. Web development #6: Getting interactive with JavaScript
  7. Web development #7: Dynamic page updates with AJAX
  8. Web development #8: Where to go from here

The websites we’ve build were all pretty amazing. We’ve started simple using just HTML, added some CSS, then we added some dynamic content and we could even upload our own content. I don’t know about you, but I think that’s pretty amazing. However, I still feel like our pages are missing a little… schwung! How about highlighting buttons, sliding content in and out of our page? Just some fancy visuals that will make our website stand out. JavaScript comes to the rescue.
As an added bonus we can make calls to our back end without refreshing the entire page using AJAX. More on that later, let’s look at some fundamentals first.

JavaScript fundamentals

JavaScript has been around since 1995. It’s easy to learn, but hard to master. I’m not going to spend a lot of time on the syntax because it looks (and behaves) a lot like PHP, including truthy and falsey). I’m just going to write code. Try to keep up. I’m still writing in Notepad++ by the way. We also won’t be needing XAMPP to run basic JavaScript in this post (we’ll be needing it later for AJAX though).

One thing you should know about JavaScript is that, like CSS, it can be embedded into your HTML. But we don’t want that. In my post about CSS we learned to keep HTML and CSS seperated, which makes it easier for you to remodel your page by simply replacing some CSS files. We’ll use the same approach for JavaScript.

So let’s take a look at our first example. Yes, it’s going to be Hello world! Not the most exciting example, but you’ll see JavaScript in action. So we start out by creating a very simple HTML file. Call it whatever you like.

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>My first JavaScript!</title>
        <script type="text/javascript" src="MyJS.js"></script>
    </head>
    <body>
        <button onclick="sayHello();">Press me!</button>
        <p id="output"></p>
    </body>
</html>

And now in the same folder create a file called MyJS.js. Inside it put the following code:

function sayHello () {
    var output = document.getElementById('output');
    output.innerHTML = 'Hello JavaScript!';
}

So let’s look at both the HTML and JavaScript. First in the header of the HTML you’ll notice I’ve added a script element. In this element I define the type and src attributes. Pretty straightforward I think. Your HTML now simply loads the specified JavaScript. In an actual server-client environment this means that the JavaScript files are sent to the client together with the HTML page.
Other than that we see a button element with an onclick event. Now whenever the user presses a button an event fires. An event is simply a notification you can subscribe to. In this case we subscribe by specifying onclick and telling it we want the function sayHello to be executed.

Now this function, sayHello, is defined in our MyJS.js file. It does the following. We declare a variable called ‘output’ using the var keyword. We assign it the result of a function called getElementById that takes a string as input parameter and is defined on something called document. Now your document is a variable that your browser gives to you and it represents your HTML content. So we’re almost literally saying “document, give me the element with id ‘output'” and guess what it returns? Yes, our p element with id ‘output’! Now that we have our paragraph we can alter it any way we see fit. In this case I’m simply going to give it the text “Hello JavaScript!”.
There’s more functions like getElementById, like getElementsByName() and getElementsByClassName(). Once you have an element you can get its child elements (using children()), its parent element (using parentElement()), its ‘neighbouring’ elements (using previousSibling() and nextSibling()) and much more. What we’re basically doing is traversing and altering the DOM (Document Object Model, or your HTML document).

Now here’s why I like and dislike JavaScript. It took me about fifteen minutes to get this example running… Fifteen!? Yes. Why? Because I spelled ‘innerHTML’ as ‘innerHtml’ and what does JavaScript do? You might say it gives us an error saying innerHtml does not exist. Wrong, what it actually does is create innerHtml on the ‘output’ variable (or more precisely the object it references). It doesn’t do anything with it, it just sits there. But in the meantime innerHTML remains empty, my page doesn’t show “Hello JavaScript!” and I’m not getting an error of any kind.
So with JavaScript you can actually alter already existing objects! How cool is that? Pretty cool, but it can lead to very subtle and hard to troubleshoot bugs, so be careful. That is also why you should always use the var keyword when creating variables. Failing to do so will work fine, but will actually create a property on your this object (the object, or context, you’re currently operating in).

And that’s actually all I’m going to tell you about basic JavaScript. The standard library is actually pretty difficult to work with and you’ll often deal with bugs like the one I just described. To top it off different browsers can behave differently using the same JavaScript code. Luckily (and also a bit unlucky) there are literally thousands of libraries and frameworks that do all the hard stuff for us. One of those libraries has become so popular that it has become the de facto standard when working with the DOM, I’m talking about jQuery.

Getting started with jQuery

There are two ways to get jQuery included in our page. One we have already seen. We get the jQuery files from jQuery.com, put them on our server (or during development our local machine) and get them in our page using the HTML script element. There are two versions you can get, the regular or the minimized version. The regular version is great for development as it is easier to debug. The minimized version has all unnecessary characters like spaces, meaningful variable names, white lines etc. removed from the file, making them practically unreadable, but making the file a little smaller in size making it to load a bit faster on our webpage. A lot of libraries have regular and minimized versions.

For this example we’re going to use the second method to get jQuery in our page though, which is through a CDN, or Content Delivery Network. A CDN is just some host providing you with popular files you might need, like jQuery. A possible benefit of using a CDN is that they might have servers across the globe, which means that if someone in America is using our website hosted in Europe they could still get jQuery from a server near them. Another possible benefit is that you don’t have to host the file yourself. So if you can, use a CDN.
Popular CDNs are Microsoft and Google, so let’s go with the Google CDN.
One word of caution though. Whenever your website requests a file from any CDN the host of that CDN may track the users of your page (they’re making a request after all). So while this is probably no problem for your personal website, it may be an issue for corporate environments where privacy and security are of bigger importance.

Let’s have a look at how our header looks with jQuery from a CDN.

<head>
    <meta charset="utf-8">
    <title>My first JavaScript!</title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
    <script type="text/javascript" src="MyJS.js"></script>
</head>

So you see, it actually looks the same, but we’re putting a link in the src attribute. Also notice that I put jQuery above MyJS because MyJS is going to need jQuery to run. Now let’s rewrite that sayHello function we had so it uses jQuery.

function sayHello() {
    var output = $('#output');
    output.text('Hello jQuery!');
}

Looks weird? $ is actually a perfectly valid function name in JavaScript and jQuery is utilizing it. $ is also called the jQuery function and alternatively you could’ve used jQuery(‘#output’). Now notice what we pass as a parameter to the jQuery function. It’s a CSS selector! Yes, you can get ANY element (or elements) on the page using CSS selectors. That’s great because we already know CSS. And when we have our output element we set the text (the part between the opening and the closing tags) to ‘Hello jQuery!’.

Now remember that I said we shouldn’t have JavaScript in our HTML? Well, we still have our onclick event in the button tag, so I guess I lied. JavaScript doesn’t really have a simple elegant solution to this problem. But jQuery does (they made it simple)! First of all let’s change our button tag so it doesn’t hard wire that onclick event.

<button id="btn">Press me!</button>

And next let’s take a look at our JavaScript.

$(document).ready(function () {
    $('#btn').on('click', sayHello);
});

function sayHello() {
    var output = jQuery('#output');
    output.text('Hello JavaScript!');
}

As you can see I’ve added a $(document).ready(function) call to our JavaScript page. That might look arcane, but it’s really simple actually. We call the jQuery function and pass it our document. We’re getting something back which obviously has a ready function that takes a function as parameter. This function, also called a callback function, is called when the document is ready (the HTML has loaded and is ready for traversal and manipulation). We then create an anonymous function to be called. It’s really the same as our sayHello function, except that we don’t give it a name. So in this anonymous function we get our button element and then call the on function on the result. With the on function we can hook up events to our elements. In this case we want the click event and we specify a function to be called when the click event fires (the button is clicked).
Here’s an alternative way of writing the above. I’m using a shorthand notation for $(document).ready by simply passing an (anonymous) function directly to the jQuery function.

$(function () {
    $('#btn').on('click', function () {
        $('#output').text('Hello jQuery!');
    });
});

Let that sink in. Study the syntax and compare the two examples. Take a look at how I rewrote the sayHello function to an anonymous function in particular, it may help understanding what’s going on. You may also want to try rewriting this using no anonymous functions.

There’s a whole lot to jQuery that I cannot show you in this blog, but the jQuery documentation is actually pretty good, so be sure to use it!

Now I’m going to show you another trick with which you can create beautiful pages using classes and CSS. First create a CSS file called MyStyle.css and add the following style to it:

.hovering {
    color: white;
    background-color: blue;
}

Now let’s create a single paragraph we want to light up once you hover your mouse over it. Don’t forget to link your stylesheet!

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>My first JavaScript!</title>
        <link rel="stylesheet" type="text/css" href="MyStyle.css">
        <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
        <script type="text/javascript" src="MyJS.js"></script>
    </head>
    <body>
        <p id="p">Try hovering over me!<br>
        An extra line<br>
        so it's easier<br>
        to hover...</p>
    </body>
</html>

Nothing special there. Now for our JavaScript:

$(function () {
    $('#p').hover(function () {
        $(this).addClass('hovering');
    }, function () {
        $(this).removeClass('hovering');
    });
});

So we get out p element and then call the hover function which takes two functions as input. One function is called when your mouse enters the paragraph and the other function is called when your mouse leaves the paragraph (moving your icon over an element is called hovering). Now in this function we call the jQuery function and pass it this. The this keyword is a little difficult in JavaScript. This is the context in which you are currently operating, so this can refer to the window object (the ‘general context’), to an object in which you are currently operating, or in this case the HTML element on which the hover event is fired. So when we pass the p element (this) to the jQuery function we get a jQuery object on which we can call jQuery functions, such as text or addClass and removeClass. So we’re dynamically adding classes. But since our CSS applies a certain style to those classes we now get dynamic styling on our page!

jQuery UI

I want to show another quick example of how powerful JavaScript, and jQuery in particular, really is. For this we’re going to need jQuery UI. Another library that’s made by the jQuery people and uses jQuery, but adds functionality specifically for your UI (User Interface). Next to a JavaScript file the jQuery UI library makes use of a CSS file too. We can get both from the Google CDN we’ve used earlier. So here is the HTML. It looks large, but it’s quite simple.

<!DOCTYPE html>
<html>
    <head>
        <meta charset="utf-8">
        <title>My first JavaScript!</title>
        <link rel="stylesheet" href="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.3/themes/smoothness/jquery-ui.css" />
        <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
        <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jqueryui/1.11.3/jquery-ui.min.js"></script>
        <script type="text/javascript" src="MyJS.js"></script>
    </head>
    <body>
        <div id="accordion">
            <h3>Page 1</h3>
            <div>
                <p>Some content...</p>
            </div>
            <h3>Page 2</h3>
            <div>
                <p>More content...</p>
            </div>
            <h3>Page 3</h3>
            <div>
                <p>Lots of content...</p>
            </div>
        </div>
        <br>
        <div id="tabs">
            <ul>
                <li><a href="#page1">Page 1</a></li>
                <li><a href="#page2">Page 2</a></li>
                <li><a href="#page3">Page 3</a></li>
            </ul>
            <div id="page1">
                <p>Some content...</p>
            </div>
            <div id="page2">
                <p>More content...</p>
            </div>
            <div id="page3">
                <p>Lots of content...</p>
            </div>
        </div>
    </body>
</html>

And now for some amazingly difficult JavaScript…

$(function () {
    $('#accordion').accordion();
    $('#tabs').tabs();
});

So check out the result with and without those two lines of JavaScript. You’ll be amazed! And that’s the power of JavaScript.

So in this post we’ve become familiar with JavaScript and jQuery. You can build amazing websites using JavaScript. There are literally thousands of JavaScript (and CSS) libraries and frameworks. Some, like jQuery, are pretty all-round, but some are really specific about doing one particular thing. In a later blog post I’ll point out some of the more popular ones.
Try to play around a bit with JavaScript. We’ve only scratched the surface. I haven’t even mentioned prototype, which is vital in fully understanding JavaScript. You can start getting your JavaScript skills up to date with a free ebook from SyncFusion: JavaScript Succinctly. Now as luck would have it there’s also a free jQuery ebook from SyncFusion: jQuery Succinctly. So I recommend creating a free account and downloading these sweet free resources (and no, I’m not affiliated with SyncFusion, I just like the Succinctly series).

JavaScript also adds functionality to get data from or send data to our server without having to refresh our entire page. This technology, called AJAX, is what I will talk about in my next blog post.

Stay tuned!

Web development #5: User input with HTML Forms

In my last blog post we’ve seen how to create dynamic web pages using PHP. The examples in this post are using the code examples from that blog post, so if you haven’t read it I suggest you do. You can find my other posts here:

  1. Web development #1: Internet and the World Wide Web
  2. Web development #2: Our first website using HTML
  3. Web development #3: Styling our page with CSS 3
  4. Web development #4: PHP in the back
  5. Web development #5: User input with HTML Forms
  6. Web development #6: Getting interactive with JavaScript
  7. Web development #7: Dynamic page updates with AJAX
  8. Web development #8: Where to go from here

In the last post we created a page that would show movies (or music, or whatever) based on a simple text file (because I’m not covering databases). You could simply add new lines to the text file and they would show on your page (after a refresh). But now you probably want to add new lines directly from your website and not in the file. Why would you want that? Well, for example, because your users don’t have access to your file (and rightfully so)! Also because your web interface is more user friendly than direct file access. When you’re going to implement an actual website you’ll probably be using an actual database and you’ll be working with more data than just lines in a file. You may want to ask users for their name, address, gender, password, for webshops you want to know their preferred shipping and payment methods. And of course you want users to add content to your site, like blogs, product reviews, messages, etc. There’s plenty of cases where you’d want user input!

So we have basically two options: HTML Forms or AJAX calls. AJAX calls are discussed in a later blog post, so in this one I’m going to focus on HTML Forms.

What are forms?

Forms are actually just a couple of HTML tags! <form></form> with some input tags, <input /> of which one has the type submit, <input type=”submit” />. Sounds easy, right? After all, you want your users to fill out a (digital) form where they give their input and then submit it to the server. How does this look?

<form action="MySite.php">
    <input type="text" name="name" />
    <input type="submit" />
</form>

Is it that easy? Almost! There are quite a few input types, such as checkboxes, radio buttons, date pickers and file pickers. We’ll look at some of them a bit later in this post. First let’s see what happens in the above HTML.

Save the above code in a file called MySite.php in your xampp htdocs folder. Now if you’d view this page you would see a field for the user to add text and a button saying “Submit” (it actually says “Verzenden” on my browser, which is the Dutch translation for “Submit”). Now what happens when you type in your name and click the submit button? You’ll be redirected to the site localhost/MySite.php?name=Your+name, which simply shows the same page again (because it was specified in the action attribute), but with your input emptied. You may not have noticed, but the entire page refreshed when you hit submit. The part behind the questionmark was added by the form and it contains the names and values of the input tags you have in your form tags. Now here’s the deal. You can actually get these values from your PHP code!

Now change your code to the following:

<?php
    if (isset($_REQUEST['name']))
    {
        echo $_REQUEST['name'];
    }
?>
<form action="MySite.php">
    <input type="text" name="name" />
    <input type="submit" />
</form>

Now what happens when you fill out your name and hit submit? Your name is being printed at the top of the page. But what’s happening in the code? Well, notice the isset($_REQUEST[‘name’])? PHP has some ‘superglobal’ variables. They’re variables that are visible in all your PHP code. $_REQUEST is one of them and it’s an array that contains any parameters that are send to your page. In this case we’re checking if the ‘name’ item was set. If it isn’t (when you first load the page) it does nothing. When it is (after a submit) we echo the value of ‘name’.

Notice that the action attribute of the form element indicates what page to navigate to. In this example I’m navigating to the page we’re already on, but it could’ve been any page. For example, try the following:

<form action="http://www.google.com/search">
    <input type="text" name="q" />
    <input type="submit" />
</form>

This will successfully open Google and search for your requested search term (Google uses ‘q’ for their parameter name).

GET and POST

So that’s the short version of it. The next thing you need to know is that in the previous examples we used the HTTP method GET to retreive our pages. Perhaps you remember from the first article in this series that GET is used to request a file from the server. In this case we requested the current page with parameter ‘name’ and we requested Google/search with parameter ‘q’.

Now what if we wanted to store information? We could use GET, sure. After all we simply send some data to our server where PHP can do with it what we want, right? All true, but that’s not always what we want. Suppose you want to save someone’s contact details. Would it look good in this huge URL www.mysite.com?name=sander+rossel&country=netherlands&address=…? No, it wouldn’t. Besides, your URL has a max length, so it won’t always work either.
Second, there is some data that you don’t want to send twice. What if someone was about to pay and they hit the refresh button (or even the back button)? Navigating to yourpage.com?payment=… would simply handle the payment again!
Last, there are some safety issues with GET. URL’s are stored in your browser history and on the server in plain text. Imagine sending your password like that! www.mysite.com?password=123456. Right, so is there another way? Yes, there is!

Whenever you want to send data to your server for storage, or data that is a bit more sensitive we can use the HTTP POST method. Let’s look at our first example again.

<?php
    if (isset($_REQUEST['name']))
    {
        echo $_REQUEST['name'];
    }
?>
<form action="MySite.php" method="POST">
    <input type="text" name="name" />
    <input type="submit" />
</form>

Wow, all I did was add the method attribute to my form element and assigned it the value of POST (method is GET by default). Now if you run this you’ll see your name on top of the page again (after a submit), but your URL will simply be MyPage.php. And try refreshing the page, your browser will warn you that information might be re-submitted.

PHP has specific superglobal variables for handling GET and POST variables specifically. They’re $_GET and $_POST, so you can use them instead of $_REQUEST (which has both GET and/or POST variables).

Other input

So we can now send some text input to the server. Surely you want more than that! Let’s look at some other input types. We’ll also tidy up our form a bit by using the label element.

<form action="MySite.php" method="POST">
    <label for="name">Name</label>
    <input type="text" name="name" id="name" required /><br><br>
 
    <label for="male">Male</label>
    <input type="radio" name="gender" id="male" value="male" checked /><br>
    <label for="female">Female</label>
    <input type="radio" name="gender" id="female" value="female" /><br><br>
 
    <label for="email">Email</label>
    <input type="email" name="email" id="email" /><br>
 
    <label>Receive newsletter
        <input type="checkbox" name="receiveNewsletter" checked /><br><br>
    </label>
 
    <label>Date of birth
        <input type="date" name="dateOfBirth" /><br><br>
    </label>
 
    <label for="avatar">Avatar</label>
    <input type="file" name="avatar" id="avatar" accept="image/*" /><br><br>
 
    <input type="submit" />
</form>

So that’s quite something. First, notice the <label> tags. I’m using them in two different ways. The first is to just declare them and associate them to an input field by specifying the for attribute and giving it the value of the id of the input I want to associate it with. The second way is by wrapping the input tags inside the label tags. Both ways are fine. Now if you click on a label the input gets focus or is selected/unselected. Nice!

Now for the different input types. We’ve seen the text type, so I’ll skip that one. I did add the required attribute though. Try submitting your form without filling out your name. There are more attributes like this one like max, maxlength, min and pattern.

Next is the password type. It looks very much like the text type, but if you type in it you get to see password characters rather than text.

Then comes the radio type. Notice I have two radio types, male and female. Also notice they have the same name. That means that if a radiobutton with a certain name is selected all other radiobuttons with the same name are unselected. In this case that means we can pick either male or female, but not both. I’ve also used the checked attribute for the male input. You don’t have to specify a value for checked. It will simply make this radiobutton checked. You could specify checked for both male and female, but your browser is only going to select one. It also makes for invalid HTML, so just don’t do that.

Following we see the email type. Again, looks like the text type, but if you commit your form your browser will check if the email address has valid syntax. That’s great, but don’t forget to check on the server too! Email is new in HTML 5 and will behave like text on older browsers.

Moving on we see the checkbox type. Not much to say, except that you can specify checked to have it checked, or omit it to have it unchecked.

Next is the date type. The user can pick or enter a date. The browser will validate if it’s a valid date (no 32st januari, etc.). This one is new in HTML 5 too and will also behave like text on older browsers.

Last, but not least, is the file type. You can use this to have a user select one or more files (if you need more than one simply add the multiple attribute, much like checked).

And of course you can handle all of those in your PHP code too. But I’m sure you can figure that out. There are other input types too, but I’ll leave it up to you to check them out. Just Google for HTML input.

Completing our example

So at this point we want to make our favourite movies example from my following blog work. Actually all we want to do is POST a movie title, add it to our text file and display it on our page. There’s actually a bit more to it than you might think… Here’s the complete code for the page.

<?php
    if (isset($_POST['movieName']))
    {
        $movieName = $_POST['movieName'];
        if ($movieName)
        {
            file_put_contents('movies.txt', htmlspecialchars($movieName) . "rn", FILE_APPEND);
        }
 
        header('Location: ' . htmlspecialchars($_SERVER['REQUEST_URI']));
        exit();
    }
?>
<!DOCTYPE html>
<html>
    <?php
        function fileAsUnorderedList($fileName, $notFoundMessage)
        {
            if (file_exists($fileName))
            {
                echo "<ul>";
                $lines = file($fileName);
                foreach ($lines as $line)
                {
                    echo '<li>' . htmlspecialchars($line) . '</li>';
                }
                echo "</ul>";
            }
            else
            {
                echo $notFoundMessage;
            }
        }
    ?>
    <header>
        <title>My favourite movies!</title>
        <meta charset="utf-8">
        <meta name="description" content="A list of my favourite movies.">
        <meta name="keywords" content="Favourite,movies">
        <meta name="author" content="Sander Rossel">
    </header>
    <body>
        <h1>My favourite movies!</h1>
        <p>
        <?php
            fileAsUnorderedList('movies.txt', 'No favourite movies found!');
        ?>
        </p>
        <h2>Add a movie!</h2>
        <?php echo '<form action="' . htmlspecialchars($_SERVER['REQUEST_URI']) . '" method="POST">' ?>
            <label for="movieName">Movie name</label>
            <input type="text" name="movieName" id="movieName" />
            <input type="submit" />
        </form>
    </body>
</html>

So that’s quite a lot! No worries. First of all you should notice that I’m handling our POST input. If movieName is set and it has a value (it’s truthy) we append it to our text file, followed by a new line (rn, or “line feed, carriage return” from ye olden days when we used typewriters).
What happens next is that we redirect to the current page using $_SERVER[‘REQUEST_URI’], another superglobal variable in PHP. So once we submit form data we get to our page with POST data. We handle the POST data and again load our page, but this time without POST data (a GET). If we wouldn’t do this we’d get an annoying pop-up every time we wanted to refresh our page. This technique is called the Post/Redirect/Get Design Pattern.

But there’s more happening. I’m using a function called htmlspecialchars, what’s that all about? Well, here’s a little practice for you. Remove all the htmlspecialchars from the code and add the following movie “<script>alert(‘hacked!’);</script>“. Now if you refresh the page you’ll get a popup saying “hacked!”. Quite annoying isn’t it? See it this way, people are going to submit text to your page and you’re going to echo that text as-is. But that text may be/contain valid HTML (and script)! And that would mess up your page or worse! It’s a huge security risk also known as Cross-Site Scripting or XSS. Now put the htmlspecialchars back in place and your page will simply display a movie with a rather weird name. So that’s what htmlspecialchars does. It makes sure your text is transformed into non-HTML, so > would be  echoed as &gt; etc. I’ve used this trick in a few places.

In our form elements I’m using the same tricks. I use $_SERVER[‘REQUEST_URI’] and htmlspecialchars. Why the $_SERVER[‘REQUEST_URI’]? If the name of your PHP file changes so would the URL to access it. If the URL was hard coded you’d have to check your PHP file and change all references to ‘movie.php’ to ‘myNewFileName.php’. With this trick we’ve got that covered!

A last remark on the PHP above the !DOCTYPE tag. Is this bad? Nope. Remember that all this PHP code is executed on the server and that your HTML is sent back. So by the time this file reaches your browser it has no knowledge of any code above !DOCTYPE.

Other than that you should know all the other stuff I’ve put in there. So now you have a website that actually takes user input, stores it on the server and serves that data back to the user. In the next blog we’re going to take a look at JavaScript, or code that runs from the browser! Good stuff.

Stay tuned!

Web development #4: PHP in the back

Missed me? It’s been a while since I last blogged (about three months). I’ve been busy moving to my own house and getting up-to-speed at a new job. Lots of good stuff, but no blogging. Well today is the day I’m picking this up again and I’m just going to act like it’s three months ago and continue with the series.

  1. Web development #1: Internet and the World Wide Web
  2. Web development #2: Our first website using HTML
  3. Web development #3: Styling our page with CSS 3
  4. Web development #4: PHP in the back
  5. Web development #5: User input with HTML Forms
  6. Web development #6: Getting interactive with JavaScript
  7. Web development #7: Dynamic page updates with AJAX
  8. Web development #8: Where to go from here

So in the previous installments we’ve created a web page using HTML and CSS. We can actually build pretty nice websites using just those, but we still run into some trouble. What if we wanted to add content to our site? We’d have to edit our HTML each time. What if we wanted to display our website in multiple languages? What if we wanted users to be able to add content to the site (such as a blog)? In other words, we want our content to be dynamic. That’s all not possible using just HTML and CSS. We’re going to need a bit more. We’re going to need something creating our HTML on the back-end.

For this blog post I assume you are familiar with HTML and CSS, which you can read about in my previous blog posts, and that you’ve worked with some programming language before, preferably some C-based language such as C# or Java.

Introduction to PHP

So our back-end is just a server listening for requests. Whenever a request comes in we want to handle it and send a response (such as an HTML web page). We can handle these requests with a variety of tools and languages, but for this article I’m using PHP.

Why PHP? First of all it’s free. Second, it’s one of the most popular web languages that has been around for a good long while. It has a huge community and lots of documentation and tutorials. Third, virtually all web hosts, even the free ones, have support for PHP. Other languages, such as C#, are not always supported. Last, but not least, PHP is quite easy to learn because it can be pretty lightweight. Actually I’m simply going to write my PHP in Notepad++.

So PHP was created twenty years ago, in 1995, by Rasmus Lerdorf. Back then the PHP stood for Personal Home Page. They changed it to PHP: Hypertext Preprocessor, making it a recursive acronym (because the first P stands for PHP, of which the first P stands for PHP, of which the first P…). Clever, huh? Anyway, PHP is a scripting language, meaning it is interpreted rather than compiled. It’s also procedural, object oriented, weakly typed and it has C-based syntax. Enough talking, let’s code!

Setting up your environment

Unfortunately we can’t code just yet. Remember that your browser renders HTML and CSS. It can also run JavaScript (which I will discuss in another blog post), but it can’t execute PHP files. Usually your server handles PHP execution. Chances are you don’t have a web server laying around. Luckily you can configure your own PC to act as a web server.

To do this simply install web server software. You have a few choices, but I’m going for XAMPP. You can download XAMPP here and then simply install it (choose all default options, or change them if you know what you’re doing). When the installation is done XAMPP will ask you to start the XAMPP Control Panel. Start it and you’ll see a list of options. We’ll need Apache, so just start it. Now start your favourite browser and navigate to http://localhost. You should see a XAMPP page. Congratulations, you have just installed a (local) web server and you can now run PHP files!

To check if it really works create a new text file and name it “hello.php”. Inside the file place the following text:

<?php
    echo "Hello, PHP!";
?>

Now go to the installation folder of XAMPP (you can go there quickly by using the ‘Explorer’ button in the XAMPP Control Panel), find the htdocs folder and place your hello.php file there. Now, in your browser, navigate to http://localhost/hello.php. You should see the text “Hello, PHP!” And now that we’ve got the mandatory Hello world example out of the way let’s start writing some real PHP.

Learning the syntax

So let’s first look at some basic PHP syntax. Basically you’re going to write an HTML page with some PHP in it. The PHP is going to create some text representing more HTML. To indicate that you’re going to use PHP use the <?php open tag and to indicate that you’re done with PHP use the ?> closing tag. You’ve seen this in the example earlier.

The echo, or alternatively the print, statement outputs your code to HTML. In the above example it creates the HTML “Hello, PHP!”, which is just some text. But we could make that echo “<h1>Hello, PHP!</h1>” and we would get a header on our page.

So as I mentioned PHP is weakly, or loosely, typed. That doesn’t mean PHP doesn’t have types, it means a variable can change its type while the code executes. It also means you can add 3 to “3” and the result might be 6 or “33”, so a bit of caution is required when working with weakly typed languages (and in fact PHP always converts text to numeric when adding).
So what are the types in PHP? First we have the int for numerics without fractional components (so 1, 8, 42 and 986 are valid integers, 1.12 is not). Second there is float, or double, for numerics with fractional components. Then we’ve got the boolean or bool for a simple true or false. Next is the string for text (or an array of characters). Then we’ve got the array, or a 0-based indexed collection of stuff. There’s the object type, which encapsulates state and behaviour. And last, and actually pretty literal least, there’s NULL indicating the absence of any value.
Nothing special if you’ve worked with other languages before.

So how do we go about and use these types? Usually we’d want to store them in variables. So how do we declare variables? We actually don’t… Just assign a value to some variable and all of a sudden it’ll be there. It’s a kind of magic! And a variable always start with the $ sign. The typical variable declaration then looks like this:

$intVar = 42;
$floatVar = 3.142;
$boolVar = true;
$stringVar = "Some string"; // Double quotes.
$stringVarAltern = 'Alternative string'; // Single quotes.
$arrayVar = array('Pulp Fiction', 'Fight Club', 'Star Wars');

class person
{
    public $name = NULL;
    public $age = NULL;
}

$objectVar = new person();
$objectVar->name = 'Sander';
$objectVar->age = 27;

$nullVar = NULL;

Now aside from the class and objectVar that looks pretty straightforward right? But beware, the following is completely legal (PHP is weakly typed, remember?):

$stringVar = 'Some string';
$stringVar = 42;

Now that’s something you do want to look out for.
There’s something else about strings too. See how you can declare a string using single or double quotes? Well, the double quote strings are interpreted strings, meaning that any variable name you place in there will be evaluated before the string prints. If you ever need to concatenate string you can use the . operator.

$hello = 'Hello';
echo '$hello world!'; // Outputs $hello world!
echo "$hello world!"; // Outputs Hello world!
echo "$helloish world!"; // Error, $helloish is undefined!
echo "{$hello}ish world!"; // Outputs Helloish world!
echo 'Conc' . 'aten' . 'ate!'; // Outputs Concatenate!

Another thing that might surprise you is that PHP can treat anything as a boolean! Any non-default value will be true while all default values will be false, also called truthy and falsey. The next example uses an if/else statement to illustrate this. Try playing around with it.

if ("Hello") // Try 0, '', '0', 0.1, NULL and array().
{
    echo "Yep...";
}
else
{
    echo "Nope!";
}

So I’ve also shown you an if/else statement. Let’s look at some loops too.

$movies = array('Pulp Fiction', 'Fight Club', 'Star Wars');

for ($i = 0; $i < count($movies); $i++)
{
    echo "$movies[$i] <br>";
}

foreach ($movies as $movie)
{
    echo "$movie <br>";
}

Now there’s actually quite something going on there! First we initialize an array, which we’ve seen before. Now in the first for loop we start by declaring our counter variable $i, then we do a boolean test to check if we need to loop once more and finally we do an update (increment $i) after each iteration. In the boolean check we also use the count(array) function to check if we still have more elements in the array. Ideally you would perform this count outside of the statement so it only gets performed once.
The second loop is a bit more readable. I’m basically saying “for each element, which I will call $movie, in the array $movies do this…”. Now in the loop’s body you can freely use the current element using the $movie variable.
There are also do– and while loops, but I won’t discuss them here. For a programmer this shouldn’t be anything new!

Writing a page

Now let’s write an entire page. Usually you’d have a database, such as MySQL, running, but that’s a bit overkill for this post. So we’re going to read lines of text from a text file and show them on the page. You’ll see that you can update your page without actually modifying your HTML or PHP file.

So let’s first take the example with my favourite movies. We’re going to put our favourite movies in a text file and print them on our page. So create a new PHP file, call it movies.php and put it in the hpdocs folder (which is in your XAMPP folder). Now create a text file called movies.txt and put it in your htdocs folder with your PHP file. You can put some movie names in your text file. Each name goes on a seperate line (I’m calling it movies, but any value would suffice, of course).

For this example I’m going to use an unordered list. I haven’t shown this yet, but the HTML is as follows:

<ul> <!-- Unordered List -->
    <li>Some item</li> <!-- List item -->
    <li>Another item</li>
    <li>Third item</li>
    <li>etc...</li>
</ul>

You could also use an ordered list, in which case you’d use <ol> tags instead of <ul> tags.

So your PHP file could look like this (excluding the !DOCTYPE, html, header and body tags):

<h1>My favourite movies!</h1>
<p>
    <?php
        if (file_exists('movies.txt'))
        {
            echo '<ul>';
            $lines = file('movies.txt');
            foreach ($lines as $line)
            {
                echo "<li>$line</li>";
            }
            echo '</ul>';
        }
        else
        {
            echo 'No favourite movies found!';
        }
    ?>
</p>

So using the PHP function file_exists(string) we’re first checking if the movies.txt file exists at all. If it doesn’t we’ll simply show “No favourite movies found!”, but if it does we’re going to create an unordered list and read the files contents using the file(string) function. The file(string) function reads all lines of a file and puts them in an array. There’s more functions for file manipulation, but I won’t discuss them here. Next we’re looping through the lines we just got from the file. For each line we’re appending a list item element to our HTML. When all the lines are processed we close our unordered list.
And that’s our page! You can view it by navigating to http://localhost/movies.php. You can use some CSS to make it look a bit prettier and you’ll probably want to add some content too.

Now imagine you’d get that out of a database? Pretty neat, huh!

Functions

Now there are times when you have certain code that you want to reuse. Let’s say we want to create another unordered list based on another file. The same rules apply, except maybe we don’t have movies, but songs that we’d like to display. We can create function for this and have the filename passed in as a parameter. So let’s look at how we can put the code above in a function. At the top of your page put the following:

<?php
    function fileAsUnorderedList($fileName, $notFoundMessage)
    {
        if (file_exists($fileName))
        {
            echo "<ul>";
            $lines = file($fileName);
            foreach ($lines as $line)
            {
                 echo "<li>$line</li>";
            }
            echo "</ul>";
        }
        else
        {
            echo $notFoundMessage;
        }
    }
?>

As you can see we’ve defined a function that does the same as our previous code, except the filename and the message when the file is not found are passed in as parameters. Now how can we call this?

<h1>My favourite movies!</h1>
<p>
    <?php
        fileAsUnorderedList('movies.txt', 'No favourite movies found!');
    ?>
</p>
<h1>My favourite music!</h1>
<p>
    <?php
        fileAsUnorderedList('music.txt', 'No favourite music found!');
    ?>
</p>

And look at that! You can now add a second text file to your htdocs folder and your favourite music will be displayed the same as your movies. You still see almost identical code here, so we could change our function and make it do a bit more or we could create a second function that creates the header and paragraph for us.

And what if you wanted to use this function on other pages? Simply put the function in a seperate file and include the following line of code in the PHP file where you want to use the function.

<?php include("MyFunctions.php"); ?>

If you want to further organize your code, and you’ll want that, you’ll have to use objects and namespaces. You’ve already seen a little example of an object earlier and I’m leaving it at that. Be careful with putting functions directly in your files though. What if two files you want to include contain the same functions (or variables)!? You’d have a problem. Using objects and namespaces help toward preventing this.

So that was a very short introduction to PHP. Probably shorter than it deserves. PHP has lots of functions, libraries, third party tooling and a large and active community. If you want to learn more about PHP I suggest you start by Googling for tutorials or perhaps read a book about it. For now you’ve created your first website with dynamic content using a server-side language though! In my next post (which won’t take me another three months) we’ll look at sending data from our website to the server so we can use our page to add movies or music to the text files.

Stay tuned!

Web development #3: Styling our page with CSS 3

This is the third installment of a blog series about web development. You can find other blogs here:

  1. Web development #1: Internet and the World Wide Web
  2. Web development #2: Our first website using HTML
  3. Web development #3: Styling our page with CSS 3
  4. Web development #4: PHP in the back
  5. Web development #5: User input with HTML Forms
  6. Web development #6: Getting interactive with JavaScript
  7. Web development #7: Dynamic page updates with AJAX
  8. Web development #8: Where to go from here

In this part of the ongoing series on web development we’re going to apply some style to the web page we created in the previous blog post. So if you haven’t read that one please do, or at least get the page’s HTML at the bottom of that post.

About CSS

CSS stands for Cascading Style Sheet and is used to apply styles to your web page (for example setting backgrounds or changing fonts) and create a layout (position elements relative to each other). CSS, like HTML, has a history of incompatibilities and non-supportive browsers too. While CSS 1 (actually level 1) has been around since 1996 it wasn’t until 2010 that most browsers fully (and more or less correctly) supported CSS. Again, each browser renders web pages differently, making it important to test your CSS in different browsers.

We’re currently at CSS (level) 3. What’s different from CSS 3 compared to it’s predecessors (CSS 1, CSS 2 and CSS 2.1) is that it’s divided into modules like Color, Font and Animations (there’s actually over fifty modules *gasp!*). This allows for various styling options to be developed independently. As a result various modules have various statuses and only a few are actual recommendations (as opposed to draft or work in progress).

Now why is CSS so important? It clearly seperates your content from your visuals. It’s basically what do you want to convey vs. how do you want to convey it. The ‘what’ goes into your HTML and the ‘how’ goes into your CSS. And having seperated this it’s easy to create a new page for your website without having to worry about styling. Or you may want to give your website a cosmetical make-over without having to change it’s contents. As a bonus both your HTML and your CSS documents will look cleaner and be easier to read!

Syntax of CSS

So enough with all those history lessons already! Let’s look at some CSS. There are actually three ways to apply CSS to your HTML. The first is inline, meaning you’re defining it in your HTML elements with a style attribute, which we just said we don’t want to do. The second way is to embed your CSS into your HTML’s head section. That already sounds better than inline, but it’s still not good enough (we’d still have to edit our HTML file for style changes). The third method, and the one we will use, is to define your styling in a seperate file.

So simply start up your text editor (again Notepad or Notepad++) and save it as “mystyle.css” (where “mystyle” is a name of your choosing). The first thing you need to do when applying a style is thinking to what you want to apply the style. Let’s say we want our headers (more specifically our h1 elements) to have a red text color. You’d start by writing a selector. In this case the selector is simply h1. Notice that a comment (for human readers) is started with /* and ended with */.

h1 {
    /* Your style here... */
}

Easy as that. Now inside the curly braces (you know them from C, C++, C#, Java, etc.) we’re going to define the style, which will be applied to our h1 elements. This looks like “property: value;”. So let’s define the red color.

h1 {
 color: red;
}

That’s deceptively easy. Yes, CSS can be that easy, but once your layout gets a bit more complicated… So does your CSS. You can put multiple properties in a single selector too. So save your document (for now preferably in the same folder as your HTML document, or you’ll have to change the path in the href attribute below). We’re going to apply this style to our HTML document (the one from the previous blog). Open up the HTML and add the following line of code to your header (for example at the bottom, just above your </head> closing tag):

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

I’m not going to elaborate on the link element any further. We’ll see it again when we’re going to use JavaScript. Now open your HTML document and you’ll notice that your header is actually colored red! Pretty awesome.

Selectors

So we just started by writing a selector, in our case the selector for h1. Selectors can be pretty tricky though. Remember that HTML elements can be nested? In our page we have an aside element containing an h2 element and some p(aragraph) elements. Let’s say way want to target that h2 element and make it blue. If we used h2 as our selector all h2 elements would be blue (go on, try it out). We can target elements within elements by combining them in the selector.

aside h2 {
    color: blue;
}

This will make all our h2 elements within aside elements blue. And you can keep combining this. I should mention that any h2 element within the aside element, even when it’s nested into other elements, is now blue. But suppose you only want the h2 elements that are directly parented to the aside element. You can now use a context selector by using the > symbol.

aside > h2 {
    color: blue;
}

Now what if you had two aside elements, both containing a h2 element, and you only wanted one of the two to be blue. For this we have two choices and both requires us to go back to our HTML. The first is working with IDs and the second is working with classes. We’ll look at them both.

Every HTML element can have at least the following two attributes: id and class. We can use them in our CSS (and later JavaScript) to group and/or identify specific elements on our page. In the next example I have modified a piece of our page so it contains some IDs and classes.

<h1 id="title">Our first webpage!</h1>
    <p><abbr class="info" title="HyperText Markup Language">HTML</abbr> stands for <b id="HTMLfull" class="info">HyperText Markup Language</b>.</p>
    <p>The language consists of <i class="info">tags</i> that describe the content of a document.
 For example, a tag can indicate that a certain text belongs to a single paragrah,
 that certain text is more important, less important, that an image should be displayed, or that a new line must be inserted.</p>

As you can see the h1 element has the ID “title” and the b element has ID “HTMLfull”. Furthermore I’ve added the “info” class to the abbr, b and i elements. Now let’s style them in our CSS. Let’s say we want everything that’s “info” to be light blue and we want the text HyperText Markup Language to be bigger too.

.info {
    color: lightblue;
}

#HTMLfull {
    font-size: 150%;
}

Give your IDs and elements a descriptive name, for example “info”, instead of “lightblue”, “lightblue” says something about your style, while “info” says something about your meaning. So in this example I’ve shown you how you can use IDs and classes and even combine them (in case of the b element which is now lightblue and big). Another tip, keep your IDs unique, even though HTML and CSS don’t enforce it.

Now let’s say you want all i elements of class “info” to be purple. No problem!

.info {
    color: lightblue;
}

i.info {
    color: purple;
}

But in this case we have a conflict! The i element should be lightblue, because it has the info class. However, it should also be purple, because it is an i element with the info class. As you can see CSS applies some rules of precedence. As a rule of thumb the most specific selector take precedence over less specific selectors. In this case an i element with class info is more specific than every element with class info, so the i element gets a purple color.

Last, but not least, you can use a * as a wildcard to specify any element. For example, you want every element in an element (let’s say an aside) to have a specific style, but exclude the aside itself. You can use the following.

aside * {
    /* Your style here... */
}

Layout

Now let’s make our page more fancy. Let’s add some layout. First we want our page to only cover a part of the screen, let’s say 50%. We also want to center it in the middle of the screen. That’s a bit tricky, but we’ll have to work with margins (the area around an element). So we’ll define a margin of 0 and let our browser figure it out. We’re also going to put some background picture on our page. I’ve taken a picture from Google and decided to use it as a background (be sure you’re not using any copyrighted stuff!). So we’re going to apply this style to our entire page, which is the body element.

body {
    background-image: url(http://p1.pichost.me/i/24/1475865.jpg);
    width: 50%;
    margin: 0 auto;
}

Wow! Our page is already beginning to look quite nice! Now remember that aside element we had? I want it to stand out a bit. Let’s put a (solid 1 pixel) border around it. I also want it to have a sort of semi-transparent white-y background, if you know what I mean (and if you don’t you’ll see for yourself). Here’s the CSS for our aside element.

aside {
    border: 1px solid black;
    background-color: rgba(255, 255, 255, 0.6);
    padding: 3px;
}

I put the padding there to create some distance between the border and the text. The padding is like the margin we used earlier, but where margin defines the space outside the element padding defines the space inside the element. 3 pixels proved to be enough space (found through a little experimenting).

I’m also still not satisfied with the h2 element within the aside element. I’ve added some extra styling.

aside h2 {
    color: blue;
    border: 2px solid black;
    width: 25%;
}

Blocks

As a finishing touch I want our image to be centered horizontally. Unfortunately that’s not as easy as it sounds. I haven’t mentioned this before, mainly because this is where it matters most, but HTML has two kinds of elements: block elements and inline elements. Basically, block elements represent a significant item that represents a rectangular block of space on a page. Examples of such elements are the p elements, h* elements and ul and ol elements (unordered list and ordered list respectively). Inline elements are smaller items on a page that reside within block elements. Examples are the a, u, em and strong elements.

So far, when we wanted to change the position of an element, they were block elements (the body and h2 elements). The img element, however, is an inline element. That means browsers usually just render them on the same line as their surrounding content and inline position jumps are just a bit weird. So we’ll have to let CSS know to treat the img as a block element before we can reposition it. Luckily that’s pretty easy! We can reposition the image the same way we repositioned the body, using the margin.

img {
    display: block;
    margin: 0 auto;
}

Now look what happens when you alter your paragraph as follows (remember that HTML actually ignores line breaks, so the below example shows the text and image on the same line by default).

<p>HTML 5 is awesome!<br>
Text before the image! 
<img src="http://www.w3.org/html/logo/downloads/HTML5_Logo_128.png" alt="The HTML 5 Logo" title="The HTML 5 Logo">
Text after the image!</p>

Now try turning display: block; on and off (by removing the CSS). See the difference? Also notice that the margin does nothing for the image when it’s not treated as a block element. Now suppose we really need that text on the same line as the image, but we want the image in the center. In this case we can use float to lift up the image and take it out of the normal content flow. You can do this for all block elements and it’s specifically handy to create a menubar on the left or right side of the screen.

img {
    display: block;
    float: right;
    margin-right: 50%;
}

Notice that I float to the right side of the screen and then use the right margin. I do this because if I floated to the left the text would be placed behind the image instead of at the beginning of the line. The float property can also mess up your layout, try using the clear property on adjacent blocks to fix this.

We’ve just seen a small part of CSS. There’s many more properties that you can use to create awesome styles and layouts. Like with HTML CSS is pretty forgiving, meaning that if you made a type your browser will probably figure out what you meant and display your page correctly. Again I advise you to follow the standards and validate your CSS against the W3C CSS Validation Service. Try experimenting with HTML and CSS to get the hang of it and learn new things. Also, don’t miss the next blog!

Stay tuned!

Web development #2: Our first website using HTML

This is the second installment of a blog series about web development. You can find other blogs here:

  1. Web development #1: Internet and the World Wide Web
  2. Web development #2: Our first website using HTML
  3. Web development #3: Styling our page with CSS 3
  4. Web development #4: PHP in the back
  5. Web development #5: User input with HTML Forms
  6. Web development #6: Getting interactive with JavaScript
  7. Web development #7: Dynamic page updates with AJAX
  8. Web development #8: Where to go from here

At this point I presume you know a bit about the internet and the world wide web. If you don’t please read my previous blog first. In this part we’re going to take a look at HTML and create our first web page.

The history of HTML

HTML, which stands for HyperText Markup Language, was first presented in 1991 by Sir Tim Berners-Lee, “inventor” of the internet as we know it today and chairman of the World Wide Web Consortium (W3C). HTML was based on SGML (Standard Generalized Markup Language) and was supposed to be the standard language in which web pages would be built. A proper HTML document consists of elements which consist of tags, usually a start and end tag. For example, a header followed by a paragraph in HTML would look as follows:

<h1>This is a header.</h1>
<p>This is a paragraph.</p>

In this example you can see the descriptive (markup) nature of HTML. The tag <h1> indicates “this is where a header starts” and </h1> indicates “this is where a header ends”. The forward slash (/) denotes a closing tag. The entire <h1>This is a header.</h1> makes up an element in HTML. The same goes for the paragraph and the <p> tag.
It is important to note that elements can be nested, but that a nested element must be closed before the outer element is closed. In the following example I will demonstrate this. The identation is purely for readability, it is not actually part of HTML. I’ve also added a little comment. The <!– … –> denotes a comment and is ignored in the HTML, but visible for (human) readers.

<p>
   This is a paragraph.
   <h1>
      This is a header inside a paragraph
   <!--Close h1 before closing p-->
   </h1>
</p>

Whenever a client requests a web page from a web server the web server sends an HTML document. Web browsers, like Internet Explorer, FireFox and Chrome interpret these documents and show you the page (without tags).

In 1995 the HTML 2 standard was released by IETF, introducing new tags and concepts. Subsequent HTML versions (standards) were released by W3C exclusively. HTML 3.2 was released in january 1997 and HTML 4 in december 1997, adding and dropping tags and rules (for both writing and interpreting HTML).

In 2000, after version 4.01, came XHTML (Extensible HTML) which was based on XML rather than SGML. XML is actually a more restrictive subset of SGML and by using XML browsers were able to parse HTML as XML. XHTML was supposed to be a backward compatible XML version of HTML. With XHTML 2 the W3C wanted to make a clean start and break free from the past. Backward compatibility with HTML 4 and XHTML 1 was dropped. Because of the controversy this caused XHTML 2 never saw the light of day as a standard.

In 2008 a first draft version of HTML 5 appeared, building upon XHTML. Almost seven years later (and 14 days ago at the time of this writing) HTML 5 was released as the new standard! Although browsers and developers have been working with HTML 5 for a while now. HTML 5 introduces some new concepts. In this blog post I’m working with HTML 5 and I’m going to explain what makes HTML 5 different while we’re building our first web page.

I recommend you take a look at the HTML 5 standard. It contains a description of all elements and how to use them.

Browser incompatibilities

As said it is up to web browsers to interpret HTML and translate it into visuals on screen. Each browser does this a little differently. As such your HTML code may look different in different browsers. While most browsers nowadays follow the standard pretty well it’s important to check that your website looks okay in different browsers. It’s also one of the reasons you should typically update your browser when updates are available.
Especially Internet Explorer is notorious for being just a little different. Back in the 90’s, when the internet was still young, IE and Netscape were the two browsers you typically used. Of course both wanted to be better than the other. They started making up their own supported HTML tags like <blink> and <marquee> (yes, this was a time when blinking text was considered flashy and high-tech). Netscape disappeared and IE had to support older, non-standard HTML.

Getting started

So enough with those history lessons already! Let’s start creating our very own HTML document. Open up Notepad, type something, save it as “something.html”, open it from the location where you saved it and it will display your text in your default browser. Congrats, you’ve just created your very first web page. That was a bit of an anti-climax, right? While it works, after all you can see your typed text in your browser, creating a well formed HTML document is a bit harder than that (although creating an HTML document is actually that easy). So you can keep using Notepad or use a more sophisticated text editor. I prefer Notepad++, which even has some IntelliSense for HTML. There are other editors too, but I recommend you keep your editor simple while learning.

Now we’re heading somewhere

So, now that you have your editor in place let’s start by creating a real HTML document. First you’ll need to specify the type and version of HTML you’re using (we’ve seen there’s a few). We can do this by using the DOCTYPE tag. The version for HTML 5 has been drastically simplified. The following example illustrates this.

<!--HTML 5-->
<!DOCTYPE html>

<!--XHTML 1.1-->
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">

<!--HTML 4.01 Strict (not backward compatible)-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

After that we need some <html> tags (starting and ending tags) between which our document will be formed. A document consists of a header, which contains the title and possibly some metadata, and a body, which contains the content of our page. While your page is displayed perfectly fine when you place the header under the body, or when your header does not have a element, it is recommended to place your header above your body and put a title inside your header because that’s the W3C standard.

<!DOCTYPE html>
<html>
    <head>
        <title>Our first web page!</title>
    </head>
    <body>
        
    </body>
</html>

There’s more information you can put in your header, like links to other scripts (CSS, JavaScript…), but we’ll get to that in later blog posts. One thing that is important to mention is the <meta> element. The meta tag has a name attribute and a content attribute. Together they tell something about your page. Let’s look at an example and I think this will be clear. Notice that the <meta> tag has no ending tag.

<head>
    <title>Our first web page!</title>
    <meta charset="utf-8">
    <meta name="description" content="HTML tutorial on sanderrossel.wordpress.com">
    <meta name="keywords" content="HTML,XHTML,HTML 5,Web Development">
    <meta name="author" content="Sander Rossel">
</head>

The body of your document

Now let’s add some body to the document. We have already seen the <h1> and <p> elements. In HTML there are a couple of header tags, <h1> to <h6>. Suppose you have a header for your whole document, this would be h1. But in your document you want to have some additional headers, these would be h2. And any headers inside your h2 would be h3, etc. Again, this is not mandatory, it’s a recommendation. When a page shows more articles (like a blog home page) each article can, of couse, have a h1 header.

<body>
    <h1>Our first webpage!</h1>
        <p>HTML stands for HyperText Markup Language.</p>
        <h2>Important tags</h2>
            <p>Paragraphs are enclosed in &lt;p&gt; tags</p>
</body>

You may be wondering why &lt;p&gt; is looking so weird. Well, imagine that we said “<p>”, and a browser is going to read our HTML, it would think we started a new paragraph! So because the < and > symbols serve a purpose in HTML we can’t use these symbols in ‘regular’ text. Instead we use &lt; for the ‘lesser than’ symbol (<) and &gt; for the ‘greater than’ symbol. We must escape the ampersand symbol (&) and the quote (“) in a likewise manner (&amp; and &quot;).

At this point you may be satisfied with the content, but you may want to style it a bit. Perhaps give the header text another color, use a background, center it in the middle of the screen, etc. This is where HTML 5 is different from its predecessors. In HTML 5 we define no style whatsoever. We use elements only describe the text semantically. For example, we want to make “HyperText Markup Language” bold, because it’s important and we want to italicize “<p> tags” because it’s a technical term. For this we can use the <b>, <strong>, <i> or <em> (for emphasize) tags. Now in earlier versions of HTML the <b> tag meant bold and the <i> tag meant italic, but in HTML 5 they have a different meaning. <b> means we want to draw attention to this text without conveying extra importance. <strong> does convey this extra importance. <i> represents text in an alternative mood, but without emphasizing the importance of the text. <em> is used when you want to emphasize some text.
And that’s the big difference between HTML 5 and everything that came before. In HTML 5 we think of tags in terms of meaning (or semantics). What do we want to convey with this text, without thinking about the visual aspect. In this case I’m going with <b> and <i> because they fit the meaning of the words best (alternatively, I could’ve used <code> for the “<p> tags”).

<body>
    <h1>Our first webpage!</h1>
        <p>HTML stands for <b>HyperText Markup Language<b>.</p>
        <h2>Important tags</h2>
            <p>Paragraphs are enclosed in <i>&lt;p&gt; tags</i></p>
</body>

When you view this body in your browser you’ll probably see some style already. Your <h1> header is pretty large while your <h2> header is a bit smaller. Your <b> element is bold and your <i> element is italicized. This is just some default style, you can easily override it. If the elements fit your meaning, but the way your browser renders them doesn’t (maybe we wanted <b> elements to be red) still go for the elements that convey the right meaning.

There’s plenty of more elements that can help you give meaning to your document. For example there is <article>, <section>, <aside>, <footer>, <small>, <blockquote>, <code>, <abbr> (from abbreviation), <address> (for contact information), etc.

The <a> element represents a hyperlink. It can link to other websites, pages, or a specific place on your current page. The place you want to link to is specified by the href attribute. The text the user has to click on to be redirected comes between the starting and ending <a> tags. Often you’ll find something like: “For my awesome blog click here”, where “here” redirects you to the blog. Don’t be like that, make your linking text descriptive, for example: “You can read more on my awesome blog” where “awesome blog” redirects you to the blog. This is, again a recommendation from the W3C. Here’s a small example of the <a> element (including a target attribute):

<p>You can read all about web development on <a href="http://www.sanderrossel.wordpress.com" target="_blank">my awesome blog</a>!</p>

There’s one last element I wish to discuss, the <img> element. I want to discuss it mostly because we can learn two things from it. First, that we should really use HTML 5 for the meaning of our text, not our styling. And second why we should create our HTML document according to the W3C standards (in a later section).
First let’s look at how we use the <img> tag. Like the <meta> tag the <img> tag has no ending tag. It has one mandatory attribute, the src (source) attribute. Next to the src attribute it is recommended to provide an alt (alternative) attribute, which is used when the image cannot be loaded from the source. It is important that your image is a part of your text (it visualizes or supports your story). DO NOT use <img> to set your background! Again, that would be styling your document and in HTML 5 we are not styling! We’ll see an example in the next section.

Putting it all together

So let’s put all of what we have learned together in a single document. I have created a small HTML file with some elements. I recommend you study the code and read the actual output page.

<!DOCTYPE html>
<html>
    <head>
        <title>Our first web page!</title>
        <meta charset="utf-8">
        <meta name="description" content="HTML tutorial on sanderrossel.wordpress.com">
        <meta name="keywords" content="HTML,XHTML,HTML 5,Web Development">
        <meta name="author" content="Sander Rossel">
    </head>
    <body>
        <article>
            <h1>Our first webpage!</h1>
                <p><abbr title="HyperText Markup Language">HTML</abbr> stands for <b>HyperText Markup Language</b>.</p>
                <p>The language consists of <i>tags</i> that describe the content of a document.
                For example, a tag can indicate that a certain text belongs to a single paragrah,
                that certain text is more important, less important, that an image should be displayed, or that a new line must be inserted.</p>
                <p>A typical piece of HTML may look as follows:<br>
                <code>&lt;p&gt;This is a paragraph with &lt;strong&gt;important text&lt;/strong&gt;&lt;/p&gt;.</code></p>
                <p>Because the &lt; and &gt; symbols are used as part of HTML you'll need to use a special code to display them as plain text.<br>
                You can write them as &amp;lt; (lesser than) and &amp;gt; (greather than).</p>
            <aside>
                <h2>Important tags</h2>
                    <p>Paragraphs are enclosed in <i>&lt;p&gt; tags</i></p>
                    <p>HTML ignores line breaks. Instead we use the <i>&lt;br&gt; tag</i><br>
                    Like this!</p>
                    <p></p>
            </aside>
            <h2>More cool stuff</h2>
            <p>You can read all about web development on <a href="http://www.sanderrossel.wordpress.com" target="_blank">Sander's bits</a>!</p>
            <p>HTML 5 is awesome!<br>
            <img src="http://www.w3.org/html/logo/downloads/HTML5_Logo_128.png" alt="The HTML 5 Logo" title="The HTML 5 Logo"></p>
        </article>
    </body>
</html>

You may want to use more or less elements than I did. Maybe you want to use <section> elements for the different parts, maybe you want more <abbr> elements (by the way, notice the tooltip on <abbr>?). Since HTML 5 is all about semantics (meaning) it’s a bit difficult to tell what is wrong and right. Do we want <strong> or <em>, do we need the <article> element and is it logical to have an <article> element inside an <article> element (or <section> inside a <section>)? It’s mostly up to you!

Why use standards

You might wonder why I’m going through so much trouble to get my HTML right. The <article> element doesn’t change the way my page looks, neither does the <aside> element. Why would I need an alt attribute in my <img> tag if I know the image is available? HTML is pretty forgiving, meaning that if you miss a starting or ending tag your page will probably be correctly displayed anyway.
However, if your HTML page is well formed, meaning your ending tags match your starting tags and all your ‘mandatory’ elements (such as <title> element in your <head> element) and attributes (such as alt attribute in the <img> tag) are in place, the chances that they are correctly displayed in any browser is bigger. In addition your page might load faster.
Search engines can better index your page if your document is well formed, meaning people might sooner find your page.
But what is perhaps most important, and probably something you didn’t think of, is that people with disabilities will have it a little easier to access your page. Someone who is blind cannot see your image, but some software can read the text in your alt attribute to the person. That’s also the reason you should not have “here” link to some page, but rather a description of that page, like “my awesome blog”!

How do you know if your document is well formed? The W3C has a validator that checks various inputs, The W3C Markup Validation Service. I suggest you use it.

We’ve seen a bit of HTML 5, but there’s much more. We might get to that in a later blog. For now we know enough to move on. In the next blog we’re going to add some style to the page we just created using CSS!

Stay tuned!

Web development #1: Internet and the World Wide Web

This is the first installment of a blog series about web development. You can find other blogs here:

  1. Web development #1: Internet and the World Wide Web
  2. Web development #2: Our first website using HTML
  3. Web development #3: Styling our page with CSS 3
  4. Web development #4: PHP in the back
  5. Web development #5: User input with HTML Forms
  6. Web development #6: Getting interactive with JavaScript
  7. Web development #7: Dynamic page updates with AJAX
  8. Web development #8: Where to go from here

For the past four years I’ve worked in VB.NET, C# and WinForms. Wanting to see more than just Microsoft and WinForms I applied for a new job and was hired. Hooray for me! Anyway, I’m starting januari and I’ve been told my first assignment would be to create a website using ASP.NET MVC and Knockout.js. So that’s great! Away from WinForms and into new territory. I can’t wait to start. I’ve never done serious web development though. Sure, I’ve seen some HTML, CSS and JavaScript, but that’s about it. Of course that’s something that can be easily fixed. So I’m starting a series on web development.
I’ve been looking into web development before and the first thing I noticed was that there’s a huge amount of protocols, languages and frameworks I supposedly need to know about. But where do I start? We have TCP/IP, HTTP(S), Apache, IIS, XML, HTML, CSS, JavaScript, AJAX, PHP, ASP.NET, ASP.NET MVC, Java, Ruby On Rails, Python, SQL, jQuery, AngularJS, Knockout.js, Node.js, Bootstrap, and there’s new stuff coming almost daily… It doesn’t seem to stop!
So in this post I want to start by explaining some of those technologies and why you do or do not need them (but might want to use them). In next blog posts we are actually going to use some of them.

Understanding the Internet

Do we need to know how the internet works to create beautiful, responsive web pages? Probably not, but knowing what the internet is, what is involved, and how it works on a high level can certainly help in giving you that egde you need to become a great web developer. So in this post I’m not going to show you code and we’re not going to create a web page, instead I’m going to explain, without too much detail, how the internet works.

In short the internet is a worldwide set of computer networks. It spans many technologies, like email, chat, file transfer and web pages. The World Wide Web (or www) is a set of web pages and sites that make up a (substantial) subset of the internet. In fact, the www is so big that the terms internet and www are often used interchangeably.

A little history

You may think that the internet is a phenomenon from the past 15 to 20 years, but the first version, called ARPANET (Advanced Research Projects Agency Network), goes as far back as the late 60’s! It was initially developed to connect universities and laboratories in the U.S. to make it easier to share data. You might be surprised that many of the idea’s and technologies that were founded back then are still present in the internet as we know it today! Some of those idea’s include: sending data between computers in packets, linked networks should still work if they’re not connected to other networks, computers can be added or removed dynamically, everyone should be able to create programs and devices to connect to it through a uniform interface (protocols), and last, but not least, there is no centralized control over the network.

Organizations

Those last two points seem to contradict each other, there is no centralized control, but we do want standardization and consistency throughout the internet by using protocols. There are actually some organizations that more or less regulate internet technologies. The Internet Engineering Task Force (IETF) creates specifications for internet protocols for the way information is exchanged on the internet. ICANN, the Internet Corporation for Assigned Numbers and Names, controls web site names. The World Wide Web Consortium, also known as W3C, creates recommendations for web standards for various web programming languages like HTML and CSS and how browsers should interpret those languages.
Additionally there is the ISO (International Organization for Standardization) that provides various standards, including many for IT.

Technologies

We now know a little about the internet and its history. But we still don’t know how it all works. In the next section we’re going to take a look at some technologies and how they work together to get data across the globe.

IP (Internet Protocol)

For now, try to think as the internet as old fashioned snail mail. A packet of data is send from point A to B. Like with regular mail a computer must know where to send a packet. In the real world we have addresses and zipcodes or postal codes (unfortunately we have no worldwide standard for this). Our computers have something similar, called an IP address (Internet Protocol address). An IP address consists of four bytes (that is 4×8 bits, making a total of 32 bits). One byte can make the numbers 0 to 255. A typical IP address could look something like 192.0.78.17 (that’s the IP of WordPress.com). Because 32 bits can ‘only’ have 429.496.7296 (232) unique addresses, and this proved to be insufficient, another IP protocol is available, consisting of a total of 128 bits (or eight groups of hexadecimal digits). The two versions of IP are also called IPv4 (32 bits) and IPv6 (128 bits).
A special IP address is the IP that a computer can use to connect to itself, 127.0.01, also known as localhost.

Now whenever you send data to another computer, let’s say a blog post, your data is cut into smaller packets. Those packets are then send to a computer with a unique IP address. A router is used to locate the computer with the specific IP address. Multiple routers typically work together  to get your packets to the other computer. All computers are ultimately connected to a router. That means your computer is not connected to the other computer, but you are connected through a long chain of routers.

TCP (Transmission Control Protocol)

We have seen that the IP protocol is essential in getting data from A to B. The IP protocol is quite minimal and doesn’t actually do much other than getting that data from A to B. If, for some reason, packets of data get lost, corrupted, duplicated or delayed we need more sophisticated protocols. One such protocol is TCP, or Transmission Control Protocol, also called TCP/IP because it makes use of the IP protocol.
TCP solves the problems I just mentioned. It does this by adding extra information to packets of data. With this information TCP can put packets back in the correct order (which is the order in which they were sent, not the order in which they are received) and it can detect errors and duplicates or missing packages and try to solve these problems. So in short TCP guarantees reliable and in-order delivery of data between computers.

Additionally, TCP associates each program or service with a unique number, making it possible for multiple programs to share the same computer and internet connection. This number is also called a port. Some common internet services have been given a standard port, such as 21 for file transfer (FTP), 25 and 110 for email (SMTP and POP3) and 80 for web (HTTP).

Other protocols run on top of IP too, like UDP (User Datagram Protocol), which is faster than TCP, but at the cost of reliability. I will not discuss any here.

The World Wide Web

So that’s the internet in a nutshell (a really very tiny nutshell…). That’s cool, but what we really want is to write web pages for the www! But before we get to that let’s take a closer look at how the www works.

The www is basically a worldwide set of documents formatted in a language called HTML (HyperText Markup Language), a form of XML (Extensible Markup Language). A web server is a computer that is running a special piece of software, like Apache or Microsoft’s Internet Information Services (IIS), that can serve these documents to clients (other computers). Users request these pages through web browsers like IE, Firefox and Chrome.

Domain Name System (DNS) and Uniform Resource Locators (URLs)

As we know each computer on the internet can be identified by IP. Yet, in our browser we type wordpress.com and not 192.0.78.17. We owe this convenience to the Domain Name System (DNS). The plain text name for a web server is called a domain name. Businesses and other users can buy domain names, which are managed by a known set of root DNS servers. So every time you type the name of a website the IP is fetched based on domain name and you can connect to the server using the IP address.
For a more comprehensive explanation of DNS I can recommend the following article: Domain Name System Explained.

The domain name becomes part of a larger text, known as a Uniform Resource Locator (URL). A URL starts with a protocol, then the host or domain name and finally a path to a file or document on the server. For example http://www.codeproject.com/Members/SanderRossel indicates that we are going to connect to the codeproject.com server using the HTTP (HyperText Transfer Protocol). On the server we want the folder Members/SanderRossel, which contains the default index.html document. When you type the URL in your browser window the result is that my CodeProject profile is displayed.
The www part of the URL is a convention and is completely optional (it’s not a standard).
The .com part is the top-level domain and roughly organizes websites by geography, type of organization or content. .com for commercial, .edu for educational, .nl for websites in the Netherlands, .fr for French websites, etc. The most popular top-level domain is, of course, .com (even for non-commercial websites, such as CodeProject!).
URLs can contain different information too, like the port to use and even a specific point on a document (also called an anchor).

HyperText Transfer Protocol (HTTP)

To request data from and to a web server another protocol is used, the HyperText Transfer Protocol. This is another layer on top of TCP. HTTP is a set of commands that a computer can send to a web client. These commands include, but are not limited to:

  • GET, for requesting a file from the server.
  • POST, for submitting form information to the server.
  • PUT, for uploading files to the server.

Whenever you request a webpage your browser send a GET message to the server, the server sends back the page and your browser displays it.

Along with the requested document comes a code, the HTTP status code, which indicates whether your request was successful. These status codes include, but are not limited to:

  • 200, OK.
  • 403, Forbidden to access a page.
  • 404, Page not found.

HTTP is a stateless protocol, which means there is no persistent connection between a client and a server. This is important when developing websites! The state of an application cannot be tracked on the server. I’ll get to that in later blog posts though.

You may have heard of HTTPS as well. The S stands for Secure (not Stateless!). I’m not discussing it here, but I thought I should mention it.

You can get a free ebook on HTTP from SyncFusion: HTTP Succinctly.

So what do we need?

So in the introduction of this blog post I mentioned some technologies. I have already explained some in this post. What others are we going to need for building websites?

I’ve already mentioned HTML, which is the main language to describe the contents of a website. In addition there’s CSS (Cascading Style Sheet) to supply stylistic information of a page. JavaScript can be used to make webpages interactive. To create dynamic pages you’ll need a language that generates HTML on a web server, like PHP, Java, Ruby On Rails, ASP.NET (C#), Python or others.
So there’s the good news. Basically you need to know only HTML to create the most basic of websites. To create a website that looks nice you need some CSS too. Add JavaScript to your stack and your webpage can be pretty sweet. And you probably want some server side language to actually present up-to-date information, like Java or ASP.NET.

All the others, jQuery, AngularJS, Knockout.js etc. are libraries (or files) in these languages. They are nice to have, and some are indispensable when solving specific problems, but they aren’t strictly necessary.

So we now have a basic understanding about the internet and the World Wide Web!
In the next blog post we are going to have a look at HTML and CSS, followed by JavaScript. After that we’ll take a look at generating HTML using a server side language.

Stay tuned!

Using DataSets to work with (relational) in-memory data

After a previous blog, Using C# to connect to and query from a SQL database, I got a request to write a bit more about the SqlDataAdapter and DataSets. So here it goes.

Looking at the DataSet

So what exactly is a DataSet? Besides it obviously being a class in the .NET Framework the DataSet represents an in-memory cache of data. This data can be stored in multiple DataTables. DataTables contains DataRows, which are made up of DataColumns. DataTables in a single DataSet can be related to each other by what can best be described as foreign key relations, the DataRelation is used to establish such a relation. Additionally DataTables can contain contraints such as UniqueContraints and ForeignKeyContraints. In case you hadn’t noticed yet, this is the relational model we know from SQL databases!
So yes, you’d think that working with DataSets and SQL kind of go hand-in-hand and it is in fact quite easy to fill a DataSet using the SqlDataAdapter. We’ve seen this in the previous blog post, but let’s quickly take a look at it again.

DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(table);
}

Notice that I’ve used a DataTable instead of a DataSet. In this case all I’m getting from the database is a single resultset. If I would’ve used a DataSet the DataAdapter would’ve filled it with one DataTable, the one we have now. So in the next example I’m going to use a DataSet and do something with the data we get.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(set);
}

foreach (DataTable table in set.Tables)
{
    foreach (DataRow row in table.Rows)
    {
        Console.WriteLine("ID: {0}, Name: {1} {2}.", row["ID"], row["FirstName"], row["LastName"]);
    }
}

Console.ReadKey();

Notice that I loop through the tables of the DataSet and through the Rows of the DataTable. the row["ColumnName"] accesses the value (boxed in an object) of the column in that row. Obviously if I had two tables, the other not being a Person, this code would break.

Accessing multiple tables and schema’s

So in the next example we’re going to load multiple tables into our DataSet. By default, the DataSet creates tables with the name “Table”, “Table1”, “Table2”, etc. If we want to access our tables by name rather than index, like we do with our columns, we’ll have to name them ourselves. You’ll see this in the next example.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(set);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
}

foreach (DataTable table in set.Tables)
{
    if (table.TableName == "Person")
    {
        foreach (DataRow row in table.Rows)
        {
            Console.WriteLine(String.Format("ID: {0}, Name: {1} {2}.", row["ID"], row["FirstName"], row["LastName"]));
        }
    }
    else if (table.TableName == "BusinessEntity")
    { //... 
    }
}

Console.ReadKey();

And even better would be to not loop through tables at all, but simply access the table you want by name.

DataTable personTable = set.Tables["Person"];
foreach (DataRow row in personTable.Rows)
{ //...
}

As you can see we’re querying for two tables (the first only having the ID column) and the second being our Person. It’s also possible to create your DataSet without having to query for data. This can be done by using the FillSchema method of the DataAdapter. The following will result in the same tables we had in the previous example, but without data.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.FillSchema(set, SchemaType.Source);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
}

Of course the SqlTableAdapter still has to do a roundtrip to the database to get the schema information. It will send the following query to get the schema (I’m not sure if it’s the same for all versions of .NET and/or SQL Server).

SET FMTONLY OFF; SET NO_BROWSETABLE ON; SET FMTONLY ON;SELECT BusinessEntityID AS ID FROM Person.BusinessEntity
SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person
SELECT BusinessEntityID AS ID, AddressID, AddressTypeID FROM Person.BusinessEntityAddress
SELECT AddressID, AddressLine1, AddressLine2, City, PostalCode FROM Person.Address SET FMTONLY OFF; SET NO_BROWSETABLE OFF;

Getting relational

I mentioned that DataSets can also have relations between tables. Unfortunately you’ll have to add those yourselves. Why would you want to do this? Because you can now navigate from a row in one table to child rows in other tables. BusinessEntity and Person are related, one BusinessEntity represents one Person. However, a BusinessEntity can have one or more addresses through the BusinessEntityAddress table which has a relation to Address. Let’s load all those tables into our DataSet, create the appropriate relations and display the names and addresses of our business entities (note that I’m only printing the ID’s and names of people who have at least one address). Remember that in order to create a relation all rows from a child table need a row in their master table (just like in the database). This is especially tricky when you start using filters. For example getting ONLY Persons with FirstName like ‘A%’, but getting ALL addresses will result in addresses without a person (or a child without a master), which prevents you from creating a relation.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Personrn" +
    "SELECT BusinessEntityID AS ID, AddressID, AddressTypeID FROM Person.BusinessEntityAddressrn" +
    "SELECT AddressID, AddressLine1, AddressLine2, City, PostalCode FROM Person.Address", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(set);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
    set.Tables[2].TableName = "BusinessEntityAddress";
    set.Tables[3].TableName = "Address";

    set.Relations.Add("BusinessEntity_Person", set.Tables["BusinessEntity"].Columns["ID"], set.Tables["Person"].Columns["ID"]);
    set.Relations.Add("BusinessEntity_BusinessEntityAddress", set.Tables["BusinessEntity"].Columns["ID"], set.Tables["BusinessEntityAddress"].Columns["ID"]);
    set.Relations.Add("BusinessEntityAddress_Address", set.Tables["BusinessEntityAddress"].Columns["AddressID"], set.Tables["Address"].Columns["AddressID"]);
}

foreach (DataRow entityRow in set.Tables["Businessentity"].Rows)
{
    foreach (DataRow personRow in entityRow.GetChildRows("BusinessEntity_Person"))
    {
        foreach (DataRow entityAddressRow in entityRow.GetChildRows("BusinessEntity_BusinessEntityAddress"))
        {
            foreach (DataRow addressRow in entityAddressRow.GetChildRows("BusinessEntityAddress_Address"))
            {
                Console.WriteLine("The entity with ID {0} has name {1} {2} and address {3} {4} {5}.",
                    entityRow["ID"], personRow["FirstName"], personRow["LastName"],
                    addressRow["AddressLine1"], addressRow["City"], addressRow["PostalCode"]);
            }
        }
    }
}

Console.ReadKey();

Now that’s quite a bit of code… I’m using a lot of (magical) strings to illustrate how it works. I suggest you create some constants or something. Don’t duplicate strings like that.
So take a good look at that example and let it sink in. You’ve just loaded your database, including relations, in code! That’s pretty neat! But it gets even better!

CRUD operations using DataSets

So we’ve looked at reading data, but what about creating, updating and deleting data? To do this you need to set the Insert, Update and DeleteCommands of your DataAdapter and call the Update method. You can do this manually, simply create three SqlCommands, one with an INSERT statement, one with an UPDATE statement and one with a DELETE statement. You can do with less if you know that certain actions are impossible, for example records from a certain table could never be deleted.
You can also use the SqlCommandBuilder, like I will do in the next example. The SqlCommandBuilder can create the insert, update and delete commands for you based on your (single table!) select query. So make sure you use the same select query in both your actual select and your update routine! The SqlCommandBuilder needs to get the schema of your table and does this using your select query. That also means it needs to make a roundtrip to the database (the same we saw with FillSchema). So when you really need that performance, or if you want to do things ‘right’, set your commands manually.
Another caveat when updating records is that you can only update one table at a time and it will do so row by row. If you update a DataSet and don’t specify a table the Update method will use the first table in the DataSet.
Before updating you can call HasChanges (to prevent going to the database if you have no changes) and GetChanges, which returns a new DataSet containing only changed DataTables and DataRows. When you do you’ll need to explicitly call AcceptChanges on your original DataSet to indicate the changes were successfully saved to the data source. Alternatively you can call RejectChanges to restore the DataSet to its original state (since the last Accept- or RejectChanges was called or since it was created). You can also accept or reject changes on DataTable and even DataRow level.
That’s A LOT of information, let’s look at some code!

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person ORDER BY ID", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(set);
}

set.Tables[0].Rows[0]["FirstName"] = "Sander";

if (set.HasChanges())
{
    DataSet changes = set.GetChanges();
    using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
    using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person ORDER BY ID", connection))
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    using (SqlCommandBuilder builder = new SqlCommandBuilder(adapter))
    {
        adapter.Update(changes);
        set.AcceptChanges();
    }
}

You can insert and delete rows in the same manner. Let’s look at inserting a record. In this case we always need to insert a BusinessEntity or we’ll create a Person without a BusinessEntity. This is actually more complicated than it sounds. First, we’ll need all the columns that do not allow NULL values and set values. Second we need to let our DataSet know that there’s a relation between BusinessEntity and Person. We’ll also need to let the DataSet know that the BusinessEntityID is an ID field with an Identity Specification (which means we don’t have to set it ourselves), but our Person needs that same ID (we can use FillSchema for this!). Last we need to issue an update for each table seperately, which means creating seperate SqlCommands, SqlDataAdapters and SqlCommandbuilders…

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, PersonType, NameStyle, FirstName, MiddleName, LastName, EmailPromotion FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.TableMappings.Add("Person.BusinessEntity", "BusinessEntity");
    adapter.TableMappings.Add("Person.Person", "Person");
    adapter.FillSchema(set, SchemaType.Source);
    adapter.Fill(set);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
    set.Relations.Add("Relation", set.Tables["Businessentity"].Columns["ID"], set.Tables["Person"].Columns["ID"]);
}

DataTable businessEntityTable = set.Tables["BusinessEntity"];
DataRow newBusinessEntityRow = businessEntityTable.NewRow();
businessEntityTable.Rows.Add(newBusinessEntityRow);

DataTable personTable = set.Tables["Person"];
DataRow newPersonRow = personTable.NewRow();
newPersonRow["PersonType"] = "EM";
newPersonRow["NameStyle"] = 0;
newPersonRow["FirstName"] = "Sander";
newPersonRow["LastName"] = "Rossel";
newPersonRow["EmailPromotion"] = 0;
newPersonRow.SetParentRow(newBusinessEntityRow);
personTable.Rows.Add(newPersonRow);

if (set.HasChanges())
{
    DataSet changes = set.GetChanges();
    using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
    using (SqlCommand businessEntityCmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntity", connection))
    using (SqlDataAdapter businessEntityAdapter = new SqlDataAdapter(businessEntityCmd))
    using (SqlCommandBuilder businessEntityBuilder = new SqlCommandBuilder(businessEntityAdapter))
    using (SqlCommand personCmd = new SqlCommand(
        "SELECT BusinessEntityID AS ID, PersonType, NameStyle, FirstName, MiddleName, LastName, EmailPromotion FROM Person.Person", connection))
    using (SqlDataAdapter personAdapter = new SqlDataAdapter(personCmd))
    using (SqlCommandBuilder personBuilder = new SqlCommandBuilder(personAdapter))
    {
        businessEntityAdapter.Update(changes, "BusinessEntity");
        personAdapter.Update(changes, "Person");
        set.AcceptChanges();
    }
}

We can also delete rows. In this database it’s a bit difficult because everything is related and some entities simply cannot be deleted (only made inactive). But we can, of course, delete our just inserted row. Most of the code above remains the same, except the part where we created the DataRows. I’ll use some LINQ to retrieve the last inserted BusinessEntity (assuming it’s the row we just inserted). Once I got the row I’ll first delete the child rows (Persons) and then I’ll delete the BusinessEntity.
Now here comes the tricky part, in the example above we first needed to insert the BusinessEntity to generate an ID so we could insert the Person. For deletion it’s the other way around! That means that if you would do updates, inserts and deletes all in one you’d get an exception. Either your inserts fail because you insert child rows before their parent rows are inserted or you delete parent rows before their child rows are deleted.
You can use an overload on GetChanges that accepts a DataRowState to create two DataSets, one with updates and inserts and one with deletes and execute them in the correct order.
In the next example I’ll use the DataRowState, but I’m only deleting rows.

DataSet set = new DataSet();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntityrn" +
    "SELECT BusinessEntityID AS ID, PersonType, NameStyle, FirstName, MiddleName, LastName, EmailPromotion FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.TableMappings.Add("Person.BusinessEntity", "BusinessEntity");
    adapter.TableMappings.Add("Person.Person", "Person");
    adapter.FillSchema(set, SchemaType.Source);
    adapter.Fill(set);
    set.Tables[0].TableName = "BusinessEntity";
    set.Tables[1].TableName = "Person";
    set.Relations.Add("Relation", set.Tables["Businessentity"].Columns["ID"], set.Tables["Person"].Columns["ID"]);
}

DataTable table = set.Tables["BusinessEntity"];
List rows = table.Rows.Cast().ToList();
int maxId = table.Rows.Cast().Max(b => b.Field("ID"));
DataRow row = rows.Single(be => be.Field("ID") == maxId);
foreach (DataRow personRow in row.GetChildRows("Relation"))
{
    row.Delete();
}
row.Delete();

if (set.HasChanges())
{
    DataSet changes = set.GetChanges(DataRowState.Deleted);
    using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
    using (SqlCommand businessEntityCmd = new SqlCommand("SELECT BusinessEntityID AS ID FROM Person.BusinessEntity", connection))
    using (SqlDataAdapter businessEntityAdapter = new SqlDataAdapter(businessEntityCmd))
    using (SqlCommandBuilder businessEntityBuilder = new SqlCommandBuilder(businessEntityAdapter))
    using (SqlCommand personCmd = new SqlCommand(
        "SELECT BusinessEntityID AS ID, PersonType, NameStyle, FirstName, MiddleName, LastName, EmailPromotion FROM Person.Person", connection))
    using (SqlDataAdapter personAdapter = new SqlDataAdapter(personCmd))
    using (SqlCommandBuilder personBuilder = new SqlCommandBuilder(personAdapter))
    {
        personAdapter.Update(changes, "Person");
        businessEntityAdapter.Update(changes, "BusinessEntity");
        set.AcceptChanges();
    }
}

And there you have it! No one said working with DataSets was easy… In this post we looked at DataSets and SqlDataAdapters, it is possible to use DataSets with other data sources such as XML though! In fact, DataSets and DataTables are unaware of their data source, you can fill them however you like. Their structure also makes them perfect for binding to DataGridViews in WinForms (and probably WPF too, but I wouldn’t really know).
Their weakness is that they are not strong-typed. We are forced to work with strings to refer to table and column names, and all values contained in them are objects (which means they could be anything).
Microsoft saw this and created so-called Typed DataSets. I recommend you skip Typed DataSets and move straight to LINQ-To-SQL or the Entity Framework. That’s a whole different topic though.

Comments are welcome. Happy coding!

A first look at NoSQL and MongoDB in particular

So today I decided to have a look at NoSQL. It’s not exactly new and actually I’m a bit late to jump on the NoSQL train, but so far I had no need for it (and actually I still don’t, but I had some time to spare and a blog to write). Since NoSQL can be quite complicated, as it imposes a new way of thinking about storing data, and I can’t possibly discuss everything there is to discuss, I’ll add some additional reading at the end of the article.

An overview of NoSQL

First things first, what is NoSQL? As the name implies it’s not SQL (Structured Query Language), a standard for databases to support the relational database model. As SQL has been the standard for about thirty to twenty years I’m not going to discuss it, you probably know it. A common misunderstanding with NoSQL is that it stands for “no SQL”, while it actually means “Not Only SQL”, which implies there is at least some SQL-y goodness to be had in NoSQL as well. Whatever that SQL-y goodness may be it’s not the relational model. And this is where NoSQL is fundamentally different from SQL, expect de-normalized and duplicated data. This ‘feature’ makes it possible to make schema’s flexible though. In NoSQL it’s generally easy to add fields to your database. Where in a SQL database you would possibly lock a table for minutes if it contains a bit of data, in NoSQL you can add fields on the fly (during production!). Querying data can also go faster than your typical SQL database, because of the de-normalization you reduce or even eliminate expensive joins. A downside to this method of storing data is that is it harder to get consistency in your data. Where in SQL consistency is more or less guaranteed if you have normalized your database NoSQL offers consistency or eventual consistency. How NoSQL databases provide this (eventual) consistency differs per vendor, but it doesn’t come as natural as in SQL databases. Also, because of the way data is stored and queried NoSQL databases tend to scale better across machines than SQL databases.
Other than that no uniform definition can be given for NoSQL because there is no standard. Still NoSQL can be roughly divided into four database models (some would say more, let’s not get into such details): Document, Graph, Key-value and Wide Column. So let’s get a quick overview of those and try one out!

The Document Model

First there’s the Document model. When thinking of a document don’t think of a Word or Excel document, think of an object like you would have in an object-oriënted language such as Java or C#. Each document has fields containing a value such as a string, a date, another document or an array of values. The schema of a document is dynamic and as such it’s a breeze to add new fields. Documents can be queried on any field.
Because a value can be another document or array of documents data access is simplified and it reduces or even eliminates the use for joins, like you would need in a relational database. It also means you will need to de-normalize and store redundant data though!
Document model databases can be used in a variety of applications. The model is flexible and documents have rich query capabilities. Additionally the document structure closely resembles objects in modern programming languages.
Some examples of Document databases are MongoDB and CouchDB

The Graph Model

Next there’s the Graph model. This model, like its name implies, stores data in graphs, with nodes, edges and properties to represent the data. A graph is a mathematical structure and I won’t won’t go into it any further. Graph databases model data as networks of relationships between entities. Sounds difficult? I think so too. Anyway, when your application is based on various relationships, such as social networks, the graph database is the way to go.
Some examples of Graph databases are HyperGraphDB and Neo4j.

The Key-value Model

Key-value databases are the simplest of the NoSQL databases. They basically provide a key and a value, where the value can be anything. Data can be queried by key only. Each key can have a different (type of) value. Because of this simplicity these databases tend to be highly performant and scalable, however, because of this simplicity, they’re also not applicable to many applications.
Some examples of Key-value databases are Redis and Riak.

The Wide Column Model

Last is the Wide Column model. Like the Key-value model the Wide Column model consists of a key on which data can be queried, can be highly performant and isn’t for each application. Each key holds a ‘single’ value that can have a variable number of columns. Each column can nest other columns. Columns can be grouped into a family and each column can be part of multiple column families. Like the Object model the schema of a Wide Column store is flexible. Phew, and I though the Graph model was complicated!
Some examples of Wide Column databases are Cassandra and HBase.

Getting started with MongoDB

So anyway, there you have it. I must admit I haven’t actually used any of them, but I’m certainly planning to get into them a bit deeper. And actually, as promised, I’m going to try one out right now! I’ve picked MongoDB, one of the fastest growing databases of the moment. It’s a Document store and so has a wider applicability than the other types. You can download the free version at www.mongodb.org. There’s also a lot of documentation on there, so I recommend you look around a bit later. Installation is pretty straightforward. Just click next a few times and install. If you change any settings I won’t be held responsible if it doesn’t work or if you can’t follow the rest of this post. So go ahead, I’ll wait.
Ready? Once you have installed MongoDB you’ll need to run it. I was a bit surprised it doesn’t run as a service (like, for example, SQL Server) by default.
So how do you start MongoDB? Open up a command window (yes, really). First you need to create the data directory where MongoDB stores its files. The default is data\db, to create it type md data\db in your command window. Next you need to navigate to the folder where you’ve installed MongoDB. For me this was C:\Program Files\MongoDB 2.6 Standard\bin. Then start mongod.exe. If, like me, you’ve never had to work with a command window here’s what you need to type in your command window:

cd C:\
md data\db
cd C:\Program Files\MongoDB 2.6 Standard\bin
mongod.exe

If you still encounter problems or you’re not running Windows you can check this Install MongoDB tutorial. It also explains how to run MongoDB as a service, so recommended reading material there!

You might be wondering if MongoDB has a Management System where we can query and edit data without the need of a programming language. You can use the command window to issue JavaScript commands to your MongoDB database. To do this you’ll need to start mongo.exe through a command window. The Getting Started with MongoDB page explains this in greater detail. However I would HIGHLY RECOMMEND that you download MongoVUE instead. It’s an easy to use, graphical, management system for MongoDB. Do yourself a favour and install it before you read any further. You can check out the data we’ll be inserting and editing in the next paragraphs.

One more thing before we continue. Mongo stores its documents as BSON, which stands for Binary JSON. It’s not really relevant right now, but it’s good to know. We’ll see some classes named Bson*, now you know where it comes from. MongoVUE let’s you see your stored documents in JSON format.

The C# side of MongoDB

So now that we are running MongoDB start up a new C# Console project in Visual Studio. Make sure you have saved your project (just call it MongoDBTest or something). Now open up the Package Manager Console, which can be found in the menu under Tools -> Library Package Manager -> Package Manager Console. Getting MongoDB to work in your project is as simple as entering the following command: PM> Install-Package mongocsharpdriver. The MongoDB drivers will be installed and added to your project automatically. Make sure you import the following namespaces to your file:

using MongoDB.Bson;
using MongoDB.Bson.Serialization.Attributes;
using MongoDB.Driver;
using MongoDB.Driver.Builders;
using MongoDB.Driver.Linq;
using System;
using System.Linq;

So are you ready to write some code? First we’ll need something we want to store in our database, let’s say a Person. I’ve created the following class to work with when we start.

public class Person
{
    public ObjectId Id { get; set; }
    public string Name { get; set; }
}

Classes don’t come easier. Notice I’ve used the ObjectId for the Id field. Using this type for an ID field makes Mongo generate an ID for you. You can use any type as an ID field, but you’ll need to set it to a unique value yourself (or you’ll overwrite the record that already has that ID).  Another gotcha is that you need to call your ID field Id (case-sensitive) or annotate it with the BsonIdAttribute. And since we’re talking about Attributes, here’s another one that’ll come in handy soon, the BsonIgnoreAttribute. Properties with that Attribute won’t be persisted to the store.

public class Person
{
    [BsonId()]
    public ObjectId MyID { get; set; }
    public string Name { get; set; }
    [BsonIgnore()]
    public string NotPersisted { get; set; }
}

For now we’ll work with the default Id field. So now let’s make a connection to our instance and create a database. This is actually rather easy as you’ll see. Mongo creates a database automatically whenever you put some data in it. After we got a connection to our database we’ll want to put some data in that database. More specific, we want to create a Person and store it. To do this we’ll first ask for a collection of Persons with a specific name (a table name, if you like). You can store multiple collections of Persons if you use different names for the collections, so beware for typo’s! After we got a collection from the database we’ll create a Person and save it to the database. That’s a lot of stuff all at once, but actually the code is so simple you’ll get it anyway!

// Connect to the database.
string connectionString = "mongodb://localhost";
MongoClient client = new MongoClient(connectionString);
MongoServer server = client.GetServer();
MongoDatabase database = server.GetDatabase("testdb");

// Store a person.
MongoCollection persons = database.GetCollection("person");
Person p1 = new Person() { Name = "Sander" };
persons.Save(p1);
Console.WriteLine(p1.Id.ToString());
Console.ReadKey();

Wow, that was pretty easy, wasn’t it!? Mongo generated an ID for you, as you can see. Next we’re going to get this Person back from our database. There’s a few ways to do this. We can work using the Mongo API or we can use LINQ. Both present multiple methods of querying for one or multiple records. I suggest you read the documentation and experiment a bit. I’ll already show you a couple of methods to get our Person back from the database.

// Using the MongoDB API.
ObjectId id = p1.Id;
Person sanderById = persons.FindOneById(id);
Person sanderByName = persons.FindOne(Query.EQ(p => p.Name, "Sander"));

// Using LINQ.
var sandersByLinq = from p in persons.AsQueryable()
                    where p.Name == "Sander"
                    select p;
Person sander = sandersByLinq.SingleOrDefault();

You’ll notice the Query.EQ. EQ stands for equal and builds a query that tests if a field is equal to a specific value. There are other query types like GT (Greater Than), LT (Less Than), In, Exists etc.

But wait, I’m not happy with this code at all! What Person really needs are LastName and Age fields. Now here comes this flexible schema I’ve been telling you about. Simply add the properties to your class. If you’ll fetch a Person that doesn’t have these fields specified they’ll be set to a default value. In case of Age you might want to use an int? rather than an int, or your already existing Persons will have an age of 0 rather than null.

Person incompleteSander = persons.FindOne(Query.EQ(p => p.Name, "Sander"));
Console.WriteLine(String.Format("{0}'s last name is {1} and {0}'s age is {2}",
    incompleteSander.Name, incompleteSander.LastName, incompleteSander.Age.ToString()));

incompleteSander.LastName = "Rossel";
incompleteSander.Age = 27;

// Let's save those new values.
persons.Save(incompleteSander);

Console.ReadKey();
// Retrieve the person again, but this time with last name and age.
Person completeSander = persons.FindOne(Query.EQ(p => p.Name, "Sander"));
Console.WriteLine(String.Format("{0}'s last name is {1} and {0}'s age is {2}",
    completeSander.Name, completeSander.LastName, completeSander.Age.ToString()));

Console.ReadKey();

Now let’s also add an address to Person. Address will be a new class and Person will hold a reference to an Address. Now you can just model this like you always would.

public class Person
{
    public ObjectId Id { get; set; }
    public string Name { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }
    public Address Address { get; set; }
}

public class Address
{
    public string AddressLine { get; set; }
    public string PostalCode { get; set; }
}

Notice that Address doesn’t need an Id field? That’s because it’s a sub-document of Person, it doesn’t exist without a Person and as such doesn’t need an Id to make it unique. Now fetch your already existing Person from the database, check that it’s address is empty, create an address, save it and fetch it again.

Person addresslessSander = persons.FindOne(Query.EQ(p => p.Name, "Sander"));
if (addresslessSander.Address != null)
{
    Console.WriteLine(String.Format("Sander lives at {0} on postal code {1}", addresslessSander.Address.AddressLine, addresslessSander.Address.PostalCode));
}
else
{
    Console.WriteLine("Sander lives nowhere...");
}

addresslessSander.Address = new Address() { AddressLine = "Somewhere", PostalCode = "1234 AB" };
persons.Save(addresslessSander);

Person addressSander = persons.FindOne(Query.EQ(p => p.Name, "Sander"));
if (addressSander.Address != null)
{
    Console.WriteLine(String.Format("Sander lives at {0} on postal code {1}", addressSander.Address.AddressLine, addressSander.Address.PostalCode));
}
else
{
    Console.WriteLine("Sander lives nowhere...");
}

Console.ReadKey();

Make sure you check out the JSON in MongoVUE. Also try experimenting with Lists of classes. Try adding more Addresses, for example. We haven’t deleted or updated any records either, we’ve only overwritten entire entries. Experiment and read the documentation.

We’ve now scratched the surface of NoSQL and MongoDB in particular. Of course MongoDB has a lot more to offer, but I hope this post has helped getting your feet wet in NoSQL and MongoDB. Perhaps it has given you that little push you needed to get started. It has for me. Expect more NoSQL blogs in the future!

Additional reading

As promised, here’s some additional reading:
NoSQL – Wikipedia
MongoDB White Papers
Document Databases : A look at them
How to take advantage of Redis just adding it to your stack

Comments are welcome. Happy coding!

Using C# to connect to and query from a SQL database

As a developer you’ll probably spend a lot of time getting data in and out of a database. Data is important in any organization and your job as a developer is to present that data to a user, have them add or edit that data and store it back to the database.

Yet I have found that many developers really have no clue how to work with a database! Many developers can get data out of databases, but do so in an unsafe way that may break your code and, worse, give hackers an opportunity to get direct access to your database! Others use an ORM like NHibernate, Entity Framework or LINQ To SQL, but have no idea what’s going on. In this blog post I will address these issues: how to setup a database connection, query for data in a secure manner and use that data in your code. I’ll also show you how to push data back to a database.

I am assuming you know how to set up a database and you know your way around C# and the .NET Framework. For my example I have used the Adventure Works 2014 Sample Database on a SQL Server 2014 database.

So let’s start. To create a connection to a database you’ll first need a database connection object. In our case we need a specific type of connection object, being the SqlConnection. Using the SqlConnection you can configure all kinds of settings that are used for your current session to the database. In this blog we’ll use defaults only. For creating the SqlConnection we’ll use the constructor that takes a (connection)string as input parameter. Usually you’d get the connectiongstring from a config file or some such. Alternatively you can create one using the SqlConnectionStringBuilder, but I won’t go into that here. Notice that I’ve wrapped the SqlConnection in a using block. This ensures that the connection is actually closed once we’re done with it. Make sure you actually open the connection only when needed.

using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
{
    connection.Open();
}

Unfortunately this doesn’t do anything yet. We’ll need a SqlCommand which takes the query we want to send to the database. In this case I’m going to select all persons from the table Person.Person. We can create a command object in different ways, but I’m going to create one using the constructor that takes the query and our just created connection. Once we have created the command we must open the connection (which we already did) and have it execute our query. There are a few ways to have the command actually execute your query.
The first is ExecuteNonQuery, which seems odd because we are going to execute a query, right? Well actually you use this method when you don’t expect a result (perhaps and update statement, or a call to a Stored Procedure that returns no resultset).
The second method, and the one we’ll need in this example, is ExecuteReader. This method returns a SqlDataReader which represents a forward-only stream of rows from the database. The columns of each row can be accessed by index or name. We’ll see how to use the SqlDataReader in the next example.
The third method, and last I will discuss, is ExecuteScalar. You can use this method when you expect exactly one result from a query.
There’s also an ExecuteXmlReader method which I will not discuss here. Additionally every method has its async versions. For older versions of .NET these are the BeginExecute and EndExecute methods and for later versions of .NET these are the ExecuteAsync methods. I will not discuss them here.
So let’s look at our example. We’re going to create a command to fetch some data from the Person.Person table and use ExecuteReader to get our results.

List persons = new List();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
{
    connection.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // Check is the reader has any rows at all before starting to read.
        if (reader.HasRows)
        {
            // Read advances to the next row.
            while (reader.Read())
            {
                Person p = new Person();
                // To avoid unexpected bugs access columns by name.
                p.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                p.FirstName = reader.GetString(reader.GetOrdinal("FirstName"));
                int middleNameIndex = reader.GetOrdinal("MiddleName");
                // If a column is nullable always check for DBNull...
                if (!reader.IsDBNull(middleNameIndex))
                {
                    p.MiddleName = reader.GetString(middleNameIndex);
                }
                p.LastName = reader.GetString(reader.GetOrdinal("LastName"));
                persons.Add(p);
            }
        }
    }
}
// Use persons here...

You may have noticed that getting a value from a SqlDataReader isn’t easy! There are methods like GetString, GetInt32, GetBoolean, etc. to convert values from their database representation to their CLR type equivalents. Unfortunately they throw on DBNull values. So in case of MiddleName, which is a NULLABLE column in the database, we need to check for DBNull before setting the MiddleName value. In case of integer or booleans (or any non-nullable type) we would use the nullable equivalents of those types like int? or bool? (which is short for Nullable<T>).

Another method to get data from the database is by using a SqlDataAdapter. This results in a DataTable or DataSet (for multiple resultsets) containing the database data. I won’t go into the use of DataTables and DataSets, but they are like in-memory GridViews. They even track if a row was changed and can automatically generate update, insert or delete commands when used with a SqlCommandBuilder.
The next code snippet shows how to fill a DataTable (that’s a lot less code than the SqlDataReader example, but keep in mind that the result is also very different).

DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person", connection))
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
    adapter.Fill(table);
}
// Use table here...

For the next example we are going to select a subset of persons by first name. That means we’ll have to change our query. Let’s look at an example.

string firstName = "John";
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = '" + firstName + "'", connection))
{
    // ...
}

Looking good, right? NO! THIS IS REALLY VERY WRONG! For John this works great (I’ll tell you in a moment why it works, but still isn’t great), but for D’Artagnan (a musketeer) this won’t work at all! While the apostrophe is all good in C# it ends a string in SQL. So the query you’ll be sending to SQL is SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = D'Artgnan. Go to SQL server Management Studio, open a new query window and try to run that exact query. You’ll get an error message saying something about an unclosed quotation mark. What it should’ve been was D”Artagnan. But even replacing every apostrophe with double apostrophe won’t work.

Whenever you send a query to SQL Server a query plan is made and the fastest way to get your data is calculated. For our query SQL Server might decide it will use an index we placed on FirstName. Once the plan is decided it’s cached and re-used when the exact same query is called. In our example that would mean a plan is made and cached for each name we look for! That’s not very efficient since every plan will probably be the same anyway…

What’s even worse and THIS IS VERY IMPORTANT is that by concatenating strings to form a query like that is a HUGE SAFETY RISK! Maybe you’ve heard of SQL Injection Attacks. Let me demonstrate this. Let’s assume for a moment that the user gets a textbox to enter a name and that name is concatenated to your query like above. Now the user enters John'; USE master; DROP DATABASE AdventureWorks2014 -- and BAM! There goes your database… Really, it’s gone. I hope you have a backup. This technique is used to get personal information of users like email addresses and passwords.
Here is a mandatory xkcd on the subject:

xkcd: Exploits of a Mom

So how are we going to solve these problems? Parameterisation! By creating parameterized queries the query plan can be re-used for different values and SQL injection belongs to the past! So how does this look?

string firstName = "John";
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("SELECT BusinessEntityID AS ID, FirstName, MiddleName, LastName FROM Person.Person WHERE FirstName = @FirstName", connection))
{
    cmd.Parameters.AddWithValue("FirstName", firstName);
    connection.Open();
    using (var reader = cmd.ExecuteReader())
    {
        // ...
    }
}

And that’s how easy it is! Notice that by adding a parameter we also improved the readability of our code. Wow, that’s a win-win-win situation!
There is one caveat though, when you want to pass a NULL to the database you’ll have to use the DBNull.Value object instead of simply null. So when fetching data we converted DBNull to null and now we’ll have to convert null to DBNull. We’ll see this happening in the next example.

Now what if we want to update, insert or delete a record in the database? We can go about it in much the same way, but use ExecuteNonQuery (which returns the number of affected rows only).

int businessEntityID = 1;
string firstName = "Sander";
string middleName = null;
string lastName = "Rossel";
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("UPDATE Person.Person SET FirstName = @FirstName, MiddleName = @MiddleName, LastName = @LastName WHERE BusinessEntityID = @BusinessEntityID", connection))
{
    cmd.Parameters.AddWithValue("FirstName", firstName);
    if (middleName == null)
    {
        cmd.Parameters.AddWithValue("MiddleName", DBNull.Value);
    }
    else
    {
        cmd.Parameters.AddWithValue("MiddleName", middleName);
    }
    cmd.Parameters.AddWithValue("LastName", lastName);
    cmd.Parameters.AddWithValue("BusinessEntityID", businessEntityID);
    connection.Open();
    cmd.ExecuteNonQuery();
}

I have to add that it’s generally a good idea to check for null for ALL your parameters. You can make a helper function to prevent your code from cluttering up to much.
And in case you want your original Person back, here are his first-, middle- and last name: Ken J Sánchez.

So far we have only worked with plain text queries. Many times you’ll want to execute a stored procedure. This works in much the same way as sending your query to the database. You simply have to set the CommandType of your command to StoredProcedure and pass in the parameters.

int businessEntityID = 1;
string nationalIDNumber = "295847284";
DateTime birthDate = new DateTime(1987, 11, 8);
char maritalStatus = 'S';
char gender = 'M';
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks2014;Integrated Security=SSPI"))
using (SqlCommand cmd = new SqlCommand("HumanResources.uspUpdateEmployeePersonalInfo", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("BusinessEntityID", businessEntityID);
    cmd.Parameters.AddWithValue("NationalIDNumber", nationalIDNumber);
    cmd.Parameters.AddWithValue("BirthDate", birthDate);
    cmd.Parameters.AddWithValue("MaritalStatus", maritalStatus);
    cmd.Parameters.AddWithValue("Gender", gender);
    connection.Open();
    cmd.ExecuteNonQuery();
}

In case you want your original employee back, here is his original birthdate: 1969-01-29.

Perhaps you have noticed that the SqlConnection inherits from DbConnection which implements IDbConnection. We have also used other classes like the SqlCommand and SqlDataReader which inherit from DbCommand and DbDataReader in a same manner. The only thing you need to know right now is that many database providers have these classes as a common base class which means that if you know how to connect to SQL Server you (more or less) know how to connect to most SQL databases like Oracle, MySQL, PostgreSQL, Firebird, etc. In theory (and probably in practice too, although I’ve never tried) you can create a flexible data layer that can switch seamlessly between (SQL) databases because of these common base classes and interfaces.

Well, there you have it. We have successfully and correctly selected data, updated data and executed a stored procedure using C#. I assume you can now guess how to use ExecuteScalar, which I mentioned, but haven’t discussed further. Things don’t stop here though. There’s much more like queries that return multiple result sets, stored procedures that return output parameters, BLOB’s, bulk operations, transactions… Way to much to discuss here. Luckily there are many books, articles and blogs on the subject.

Happy coding!

Writing the code you need

%d bloggers like this: