# Sunday, 12 February 2017

When I worked in consulting, part of my job was to interview potential new hires. This often included a technical screening. Here are a few of the questions I would ask on Object Oriented Programming (OOP)

What is the difference between an object and a class?

An object is an instance of a class. A class is a template - or blueprint - that describes what an object based on it will look like and behave. Further, objects have a defined lifetime - they are created and eventually destroyed; not so with classes.

What is encapsulation?

Encapsulation is an OOP principle describing an object hiding its implementation details form the outside world.

What is inheritance?

Inheritance describes a class that is derived from another class or from an interface. The derived class (or child class) inherits the properties and methods of the parent class or interface from which it is derived.

What is polymorphism?

Polymorphism describes to objects that can accept the same message (e.g., the same method call with the same parameters) and respond differently, but appropriately to that message. For example, a Customer class and an Invoice class may each have a "Print" method. Calling the Customer's print method could print a report listing details about the current customer, whereas calling the Invoice's Print method might print a specific invoice.  Polymorphism can be accomplished by implementing the same interface in multiple classes.

What is the difference between a Class and an Interface?

Although each can serve as a templates for inherited classes, an Interface includes only the names and signatures of properties and methods, whereas a class also includes the implantation of each method and property.

Sunday, 12 February 2017 20:32:16 (GMT Standard Time, UTC+00:00)
# Tuesday, 16 June 2015

C# contains a single operator that handles conditional logic in a single statement. It is generally written as "?:" (making it difficult to find using a search engine) and it is often referred to as the conditional operator, although the official documentation does not give it this name.

I write about it here, not because it is new or extra cool, but because I believe it is underused by C# developers.

The syntax is:
condition ? ValueIfTrue : ValueIfFalse;

where

  • condition is a boolean expression to test
  • ValueIfTrue is the value to return if the condition expression is true
  • ValueIfFalse is the value to return if the condition expression is false

An example should clarify this:

   1: int x;
   2: string y;
   3: x = 1;
   4: y = x >= 0 ? "Positive" : "Negative";
   5: Console.WriteLine(y); 
   6:  
   7: x = -1;
   8: y = x >= 0 ? "Positive" : "Negative";
   9: Console.WriteLine(y); 

The output of this code snippet is:

Positive
Negative

The expression

x >= 0 ? "Positive" : "Negative";

returns the string "Positive" if x is at least 0 and "Negative" if x is less than 0.

Of course the same expression could be written like the following:

   1: x = 1;
   2: if (x >= 0)
   3: {
   4:     y = "Positive";
   5: }
   6: else
   7: {
   8:     y = "Negative";
   9: }
  10: Console.WriteLine(y);
  11: 
  12: x = -1;
  13: if (x >;= 0)
  14: {
  15:     y = "Positive";
  16: }
  17: else
  18: {
  19:     y = "Negative";
  20: }
  21: Console.WriteLine(y);

But this requires more typing (which provides more changes for errors); and (more importantly), it takes more time to read, so it cannot be digested as quickly by someone reviewing the code later. Of course, I'm assuming that the code reviewer is familiar with this operator, but I don't think that's an unreasonable expectation, given that it has been in the C# language over 10 years.

Many developers are not familiar with ?: operator, but they should be. It simplifies code and makes it easier to read.


Here is the full code of a console application demonstrating these concepts:

   1: class Program
   2: {
   3:     static void Main(string[] args)
   4:     {
   5:         int x;
   6:         string y;
   7:         x = 1;
   8:         y = x >= 0 ? "Positive" : "Negative";
   9:         Console.WriteLine(y);
  10: 
  11:         x = -1;
  12:         y = x >;= 0 ? "Positive" : "Negative";
  13:         Console.WriteLine(y);
  14: 
  15:         x = 1;
  16:         if (x >;= 0)
  17:         {
  18:             y = "Positive";
  19:         }
  20:         else
  21:         {
  22:             y = "Negative";
  23:         }
  24:         Console.WriteLine(y);
  25: 
  26:         x = -1;
  27:         if (x >;= 0)
  28:         {
  29:             y = "Positive";
  30:         }
  31:         else
  32:         {
  33:             y = "Negative";
  34:         }
  35:         Console.WriteLine(y);
  36: 
  37:         Console.ReadLine();
  38:     }
  39: }
Tuesday, 16 June 2015 17:34:00 (GMT Daylight Time, UTC+01:00)
# Wednesday, 21 January 2015

It's hard to imagine a scenario in which a software developer would not benefit from source control. GitHub provides a repository for developers to store their source code and other digital assets.

To use GitHub, you will need to create an account at https://github.com/ and log in.

To set up a new repository, click the "+" icon next to your name and photo at the top right of the main GitHub page. This displays a dropdown list (Fig 1).

1-NewRepository
Fig 1

Select "New repository" from this list.  The "New Repository" page displays (Fig 2)

2-CreateRepository 
Fig 2

At the "Repository name" textbox, enter a name for your repository. This should be brief, but enough to identify the project to you and others.

At the "Description" textbox, you may enter a short description of the project, if you like. This is useful if you want others to contribute.

Use the radio buttons to select whether this will be Public (anyone can view the files in the repository) or Private (only those you invite can see the files). You can only create a private repository if you have a paid GitHub account. Free accounts can only have public repositories.

Checking the checkbox labeled: "Initialize this repository with a README" will create a README.MD file in your repository. This file is displayed on the main page of your project below the list of other files. It should contain text and Markdown language. For a description of Markdown, check out http://daringfireball.net/projects/markdown/.

There are two dropdowns at the bottom of the page: "Add gitignore" and "Add a license".

The "Add gitignore" dropdown (Fig 3)allows you to quickly create gitignore file.

3-GitIgnore 
Fig 3

A gitignore contains a list of files that might be on your disc, but that you don't want in source control. These might be user configuration files or binary output files.
Selecting a type of projects creates a gitignore file containing files typically ignored by that project type. For example, if I select "Visual Studio" from the "Add gitignore" dropdown, a file will be created that tells GitHub to not check in "SUO" files or files in my "bin" folder.  Of course, you can always edit the gitignore file after it is created.

The "Add a license" dropdown (Fig 4)allows you to assign a license to the files in your repository.

4-License
Fig 4

The license dictates how others can legally use these files and programs - for example, whether they can make money off of code that you share here.  There is a good description of each software license at http://opensource.org/licenses/.

After you complete the form, click the [Create Repository] button. Your repository will be created in a few seconds.

Wednesday, 21 January 2015 15:20:00 (GMT Standard Time, UTC+00:00)
# Friday, 29 November 2013
 #
 
Back To Basics

A cascading style sheet (CSS) is used to apply styling to an HTML web page. Separating styling from markup provides cleaner markup, easier reuse of styles, and greater flexibility when maintaining a web page.

This article describes the basics of using CSS to applying styles to a web page.

Cascading style sheets are applied to a web page by either enclosing CSS syntax between a beginning and ending STYLE tag or by creating a separate file (typically with a “.CSS” extension) containing CSS syntax and linking that file to the web page, via the LINK tag. The two syntaxes are shown below:

Inline CSS:

<style>
selector {
     style-name: style-value;
}
tag {
     style-name: style-value;
}
#id {
     style-name: style-value;
}
.classname {
     style-name: style-value;
}
</style> 

Link to stylesheet

<LINK REL=StyleSheet  HREF="style.css" TYPE="text/css">

The CSS syntax consists of a set of selectors, followed by curly brackets (“{” and “}”), containing a set of stylename:stylevalue pairs. For example, in the example below:

div {
     background-color: yellow;
} 

The selector is the word “div”. It tells the style sheet to select all div tags on the page and apply the corresponding style (in this case, yellow background color) to each div.

Historically, the most common type of selectors are

  • Text, to identify a tag name and select all tags of that type (e.g., “div”)
  • Text, preceded by “#”, to identify any element on the page with a given ID (e.g., “#Div1”, which selects any element with the attribute id=“Div1”.)
  • Text, preceded by “.”, to identify any elements on the page with a given class applied to them (e.g., “.Class1”, which selects any element with the attribute class= “Class1”).

Examples of each are below:

Select by Tag

Style:

<style> div
{
background-color: yellow;
} </style>

HTML Markup:

<div>Life, the Universe</div>
<div>and Everything!</div>

Output:

image

Select by ID

Style:

<style> #MyDiv
{
background-color: green;
} </style>

HTML Markup:

<div id="MyDiv">Life, the Universe</div>
<div id="YourDiv">and Everything!</div>

Output:

image

Select by Class name

Style:

<style>
.CoolDiv
 {
    background-color: purple;
 }
</style>

HTML Markup:

<div>Life, the Universe</div>
<div class="CoolDiv">and Everything!</div>

Output:

image

Friday, 29 November 2013 20:28:00 (GMT Standard Time, UTC+00:00)
# Wednesday, 30 May 2012

 

In .NET applications, Connection Strings are easily stored in and retrieved from a web.config file or an app.config file. Add a <connectionStrings> section to the config file, directly within the <configuration> section. Within the <connectionStrings> section, place an <add> tag for each connection string you wish to store and retrieve. This <add> tag contains two important attributes: name and connectionString. The name attribute will be used to look up an entry, so each <add> tag should have a unique name. The connectionString attribute contains the connection string you will use to connect to a database. If your application needs to connect to a number of different data types, it is worth setting the Provider property as well. This is the name of a database provider installed on the current machine, that will allow you to connect to a database.
An example is shown below:

<configuration>
  <connectionStrings>
    <add name="MyApp_Dev" 
        connectionString="Data Source=Server01;Initial Catalog=AwesomeDB_Dev;Integrated Security=True"/> <add name="MyApp_QA"
        connectionString="Data Source=Server01;Initial Catalog=AwesomeDB_QA;Integrated Security=True"/> <add name="MyApp_Prod"
        connectionString="Data Source=Server01;Initial Catalog=AwesomeDB;Integrated Security=True"/> </connectionStrings> </configuration>

To retrieve a connection string by its associated name attribute, use the utilities found in the System.Configuration namespace of the System.Configuration assembly. First, set a reference to System.Assembly by selecting the menu option Project | Add Reference, selecting the System.Assembly in the Component Name column of the .NET tab of the Add Reference dialog; and, clicking the OK button. Then, add the following using statement to the top of your class file.

This namespace contains the static ConfigurationManager class, which exposes a number of methods for retrieving configuration information. ConfigurationManager.ConnectionStrings returns a collection containing all connection strings settings in the config file of the current assembly. Once we have this collection, we can retrieve a single item by specifying its name. Doing so returns a ConnectionStringSettings object that contains properties retrieved from a single <add> element of the config file. The important property is ConnectionString, which can be used with ADO.NET to connect to a database. Knowing the Provider property may also be useful if your application connects to different types of data.
Sample code is below.

ConnectionStringSettingsCollection cssCollection =
    ConfigurationManager.ConnectionStrings;
ConnectionStringSettings devCsSettings = cssCollection["MyApp_Dev"];
string devCs = devCsSettings.ConnectionString;
Wednesday, 30 May 2012 17:21:00 (GMT Daylight Time, UTC+01:00)
# Sunday, 01 November 2009

Back To Basics

I remember how excited I was in the early days of .Net when I discovered how easy it was to write a Windows Service. I probably wrote a half dozen services that first year. But I hadn't written one in years and (oddly) hadn't even heard anyone talking about writing a Windows service in as long.

Perhaps the reason one hears so little about Windows Services is because the way we write them has changed so little since .Net 1.0.

A Windows Service is not dependent on a particular user being logged in. In fact, a Windows Service can run if no one is currently logged onto the machine on which it is running. This makes a Windows Service inherently more secure than a Windows Forms application or a Console application because you can run it on a server, set credentials and log out of the console.

To create a Windows Service, open Visual Studio and select File | New Project. In the Project Types tree of the New Project dialog, expand either Visual C# or Visual Basic node and select the Windows node. In the Templates area of the dialog, select Windows Service. Set the Name, Location and Solution as you would any other project and click OK to create the project.

By default, a Windows service contains one class named "Service1". The name of this class isn't really important because it isn't called externally, so I always leave this default name. If you double-click this class in the Solution Explorer, it opens in a Design view. Select View | Code to switch to a code view of the class. Notice that the class inherits from ServiceBase and that it contains overrides of two methods: OnStart and OnStop.

As you might guess, OnStart contains code that runs when a service starts and OnStop contains code that runs when a service stops. I put setup code into OnStart, such as initializing variables that my service will need. I generally put very little code in the OnStop method, but this is where cleanup code goes.

Services are designed for long-running processes and are meant to stay in memory for a long time -sometimes months or years. Most of the services I've written use a timer object to periodically wake up and perform some check and respond if that check finds something. For example, you might check the contents of a folder every 10 minutes and, if an XML file is found in that folder, move it to a new location and parse it appropriately.

For example I  recently wanted a program that would check an error log every few minutes and automatically attempt to correct any errors found there. I had already written a class to read the error log, retry each error found and remove from the log any errors that were retried successfully. So my service only needed to call this class every 5 minutes. I used a timer class to do this. A partial code listing is shown below.

protected override void OnStart(string[] args)
{
    // Every 30 seconds, a timer will do some work
    timer1.Elapsed += new ElapsedEventHandler(timer1_Elapsed);
    timer1.Interval = 30000; 
    timer1.Enabled = true;
    timer1.Start();

}

protected override void OnStop()
{
    timer1.Enabled = false;
}

private void timer1_Elapsed(object sender, EventArgs e)
{
    // Wake up and perform some action.
    // [Cpde omitted]
}

I prefer to keep the code in the service to a minimum and abstract complex logic to a separate assembly. This makes testing and debugging easier. So the omitted code in the above example would call out to another assembly to do the hard work.

Installing a Service
In order to install a service, you will need to add an Installer class. Select Project | Add New Item; then select the Installer Class template. This class also opens in the designer by default. Select View | Code to see the code. The Installer class inherits from Installer, but you don't need to override any methods.

You can set some attributes of the service to make it easier to find. The Installer class's constructor is the place to do this. Instantiate a new ServiceInstaller and ServiceProcessInstaller, set properties of these objects, and add these objects to the Installer Class to affect the Windows Service when it is installed. Common properties that I like to set are

Class Property Description of property
ServiceInstaller ServiceInstallerServiceName The name of the service. This must match the ServiceName specified in WindowsService1.
ServiceInstaller DisplayName A name displayed in the Services Manager applet.
ServiceInstaller Description A description displayed in the Services Manager applet
ServiceProcessInstaller Account A built-in account under which the service will run.
ServiceProcessInstaller Username The name of the user account under which the service will run.
ServiceProcessInstaller Password The password of the user account under which the service will run.

For the ServiceProcessInstaller, you will set either the Account property or the UserName and Password properties. Typically, I set the Account property to System.ServiceProcess.ServiceAccount.LocalSystem, so that it can be installed. This account probably won't have sufficient privileges to accomplish what my code is trying to do, so someone will need to open the Services Manager and change this to a valid account. I could hard-code the Name and Password of an account that I know has sufficient privileges, but this ties my application too tightly to a single domain or server or organization. I would rather keep it flexible enough that it can run anywhere. And besides, the account under which a service runs is really a deployment issue, so others should be making these decisions and they should be forced to think about this at deployment time.

Below is sample code for the installer class

[RunInstaller(true)]
public partial class Installer1 : Installer
{
    public Installer1()
    {
        InitializeComponent();
        ServiceInstaller si = new ServiceInstaller();
        ServiceProcessInstaller spi = new ServiceProcessInstaller();

        si.ServiceName = "DGWinSvc"; // this must match the ServiceName specified in Service1.
        si.DisplayName = "DGWinSvc"; // this will be displayed in the Services Manager.
        si.Description = "A test service that takes some action every 30 seconds";
        this.Installers.Add(si);

        spi.Account = System.ServiceProcess.ServiceAccount.LocalSystem; // run under the system account.
        spi.Password = null;
        spi.Username = null;
        this.Installers.Add(spi);
    }
}

After your code is tested and compiled, you can deploy it to a server. Copy to a location on the server the service EXE and any associated DLLs, Config files or other objects required for it to run. The server must have the .Net framework installed. If it has the framework, it should have a program called InstallUtil.exe. You can find this program in the Windows folder under Microsoft.NET\Framework in the subfolder corresponding to the .Net CLR version under which you compiled the service. On my server, I found InstallUtil.exe in c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. Open a command prompt, change to the location of InstallUtil.exe and run the following command
INSTALLUTIL <full path of Windows Service Executable>

You can later uninstall the service with the following command

INSTALLUTIL /u <full path of Windows Service Executable>

Now open the Services Manager applet (under Windows Administrative Tools) and refresh the list of services. You should see your service listed with the DisplayName you assigned in the Installer class. Right-click this service and select Properties to display the Service Properties dialog. Click the "Log On" tab, select "A particular user" and enter the name and password of the user under which this service should run. You may need to create this user. It should have permission to access all resources that the service needs to access. Click the OK button to save these changes and close the dialog.

Of course, you can also write a setup project to your solution if you want to automate the deployment process. This article does not cover that.

To start the service, right-click the service in the Services Manager and select Start.  You can use the same applet to stop the service. Right-click its name and select Stop. Alternatively, you can stop and start the service from the command line with the following commands

NET START <ServiceName>
NET STOP <ServiceName>

where <ServiceName> s the ServiceInstaller ServiceName property specified in our installer class. For our example, we would type. This is a useful if you want to script the starting and stopping of your service.

Windows Services are something that .Net got right very early and hasn't needed to change. Creating useful services is easy with the .Net framework.

You can download a simple Windows service at DGWinSvc.zip (28.05 KB).

 

Sunday, 01 November 2009 04:40:13 (GMT Standard Time, UTC+00:00)
# Sunday, 13 September 2009

Back To Basics

The yield return and yield break keywords are shortcuts introduced in C# 3.0. They are designed to assist you in a method that returns IEnumerable <T>.
Often such methods consist of the following steps

  1. Create an empty IEnumerable set.
  2. Loop through some data (via a while, foreach or other construct)
  3. Add to the set within your loop.
  4. Exit the loop when some limit is reached.
  5. Return the set.

A sample of such code is shown below.

public IEnumerable<Int32> GetOddNumbers_Old(Int32 maxNumber)
{
    List<Int32> oddSet = new List<Int32>();
    int num = 0;
    while (true)
    {
        System.Threading.Thread.Sleep(1000);
        num++;
        if (num % 2 == 1)
        {
            oddSet.Add (num);
        }
        if (num >= maxNumber)
        {
            break;
        }
    }

    return oddSet;
}

The new yield keywords allow you to shorten your code to only the following steps

  1. Loop through some data
  2. yield return within the loop
  3. yield break if a limit is reached before the loop is complete

That’s it. There is no need to explicitly create or return your IEnumerable set.

The yield return statement appends a value to the IEnumerable set. The yield break statement exits any looping construct to prevent more items being added to the return set. If you omit yield break, the loop exits as it normally would.

The difference between using the yield return and simply returning a set is subtle. Returning a set at the end of the method returns the entire set at once. The client must wait until the method is complete before it can use any values in the return set. Each time the yield return statement executes, the client has access to another element in the set. Essentially the set trickles back a little at a time. This can provide a gain in performance or perceived performance if you have a large set to return.

Here is a sample that returns the same results as above, using yield return and yield break.

public IEnumerable<Int32> GetOddNumbers_Yield(Int32 maxNumber)
{
    int num = 0;
    while (true)
    {
System.Threading.Thread.Sleep(1000); num++; if (num % 2 == 1) { yield return num; } if (num >= maxNumber) { yield break; } } }

If we call the two methods above, we will get the same results but they will return in slightly different ways. I added the System.Threading.Thread.Sleep(1000); line to help demonstrate this. That line simply pauses execution for 1 second each time the loop executes.

Assume the above two methods are in a class named MyMethods.  The following code will call each.

Console.WriteLine("Get Odd Numbers using old way:");
MyMethods mm = new MyMethods();
IEnumerable<Int32> oddNumbers2 = mm.GetOddNumbers_Old(11);
foreach (Int32 n in oddNumbers2)
{
    Console.WriteLine(n);
}

Console.WriteLine();


Console.WriteLine("Get Odd Numbers using new YIELD keyword:");
IEnumerable<Int32> oddNumbers1 = mm.GetOddNumbers_Old(11);
foreach (Int32 n in oddNumbers1)
{
    Console.WriteLine(n);
}

Console.ReadLine();

The above code produces the following output

Get Odd Numbers using old way:
1
3
5
7
9
11

Get Odd Numbers using new YIELD keyword:
1
3
5
7
9
11

Notice that both methods return the same data. However, if you watch the code as it runs, you will notice the first method waits 12 seconds, then immediately outputs all odd numbers; while the second method outputs a number every 2 seconds. Each method returns the same values and each takes the same amount of time, but the second method uses yield return, so the client is able to output a bit of data each time a yield return executes.

There are a few restrictions to using the yield keywords

  • These keywords cannot appear in blocks marked "unsafe"
  • These keywords cannot appear in a method with ref or out parameters
  • The yield return statement is not allowed inside a try-catch block, although it may be located inside a try-finally block.
  • A yield break statement may not be located inside a finally block.

You can download this sample code at DemoYield.zip (45.06 KB)


Mike Wood contributed to this article.

Sunday, 13 September 2009 14:44:07 (GMT Daylight Time, UTC+01:00)
# Saturday, 12 September 2009

Back To Basics

Prior to C# 3.0, we had two common ways to initialize the public properties of an object.

  1. We could use the default constructor to instantiate the object; then, use a separate line of code to assign a value to each property. (Listing 1)
  2. We could create a constructor that accepts an argument for each property; and write code in this constructor to assign to each public property the value passed in for each corresponding argument. (Listing 2 and Listing 3)

The first method is quite verbose and requires a lot of code for complex objects.

Listing 1:

Customer cust1 = new Customer();
cust1.CustomerID = 1;
cust1.FirstName = "George";
cust1.LastName = "Washington";
cust1.StreetAddress = "111 A St";
cust1.City = "Aville";
cust1.State = "MI";
cust1.ZipCode = "10001";

Listing 2:

class Customer
{
    public Int32 CustomerID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string StreetAddress { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }

    public Customer
        (
        Int32 customerID,
        string firstName,
        string lastName,
        string streetAddress,
        string city,
        string state,
        string zipCode
        )
    {
        CustomerID = customerID;
        FirstName = firstName;
        LastName = lastName;
        StreetAddress = streetAddress;
        City = city;
        State = state;
        ZipCode = zipCode;
    }
}

Listing 3:

Customer cust2 = new Customer
    (
    2,
    "John",
    "Adams",
    "222 B Ave",
    "Beetown",
    "MI",
    "20002"
    );

The second method is more elegant, but assumes we always want to initialize every single public property at object instantiation. Of course, we could create multiple constructors to accommodate times when we only want to initialize certain variables but this can get complex in a hurry when our class has a lot of public properties.

Beginning with C# 3.0, we can now initialize public properties in the same line of that instantiates an object; and we can do so without creating a new constructor. We use object initializers to do this. 

Using object initializers we can still use the default constructor (or any public constructor we want); but we can set public properties by appending the property assignments within a set of curly braces at the end of the object initializer, as shown in listing 4.

Listing 4:

Customer cust4 = new Customer() 
            {   CustomerID = 4, 
                FirstName = "James", 
                LastName = "Monroe" 
            };

This new C# feature is known as object initializers. This method gives us a great deal of flexibility. At the time we instantiate an object, we can decide which properties to initialize. And we can do so without creating a plethora of constructors for the object or return to the verbose way of assigning property values after an object's instantiation.

Object initializers are most useful when you instantiate a class in many different places throughtout an application.

Download this code to view and run a simple application that uses object initializers DemoObjectInitializers.zip (24.92 KB)
Saturday, 12 September 2009 14:10:03 (GMT Daylight Time, UTC+01:00)
# Friday, 11 September 2009

Back To Basics

Automatic properties were introduced in C# 3.0 as a convenience to developers.

C# and Visual Basic .Net provide two ways for a class to maintain state: Fields and Properties.

A field is a simply a public variable defined inside a class, such as in the following example

public string foo;

A property looks much like a field to the outside world, but exposes two methods – a “getter” to allow you to retrieve the value of the property and a “setter” to allow you to assign a value to the property. The following sample demonstrates how to implement a read-write property in C#.

private string _firstName;
public string FirstName
{
    get
    {
        return _firstName;
    }
    set
    {
        _firstName = value;
    }
}

This is a common use of property getters and setters – to assign and retrieve the value from a private variable. Because there is no extra logic in these methods, similar functionality could be achieved using a field, but I prefer to use properties in case I decide later to add some validations or calculations to the setter or getter. Making a change like that to a property will not break the class signature, forcing a recompile of dependent objects.

But this is a lot of typing for such simple functionality. So much in fact that Visual Studio 2005 provided a right-click accelerator to type much of it for you.

Beginning with C# 3.0, automatic properties provide a shorthand notation for this functionality.  Simply provide the scope, the type and the name of the property, followed by “{get; set; }” and the framework will automatically create a private variable for you and provide code to save and retrieve to/from that private variable. This is all done behind the scenes for us so we don’t need to write any of this code.  Below is a sample of the code we need to write for a public property.

public string FirstName { get; set; }

To make a property read-only, simply omit the "set" keyword. To make it write-only, omit the "get" keyword. Below is syntax for the same property if I wanted it to be read-only.

public string FirstName { get; }

The only disadvantage to automatic properties is that the rest of the code in our class no longer has access to the private variables create by the framework. Even from within the same class, we must go through the public property in order to get or set the private variable.

This is a small price to pay for the amount of code we are saving.

Automatic properties not only require less typing, they are far easier to read when your class has a bunch of properties. Which of the following easier to read?

public string FirstName { get; set; }
public string LastName { get; set; }
public string StreetAddress { get; set; }
public string City { get; set; }
public string State { get; set; }
public string Zip { get; set; }

or

private string _firstName;
public string FirstName
{
    get
    {
        return _firstName;
    }
    set
    {
        _firstName = value;
    }
}

private string _lastName;
public string LastName
{
    get
    {
        return _lastName;
    }
    set
    {
        _lastName = value;
    }
}

private string _streetAddress;
public string StreetAddress
{
    get
    {
        return _streetAddress;
    }
    set
    {
        _streetAddress = value;
    }
}

private string _city;
public string City
{
    get
    {
        return _city;
    }
    set
    {
        _city = value;
    }
}

private string _state;
public string State
{
    get
    {
        return _state;
    }
    set
    {
        _state = value;
    }
}

private string _zip;
public string Zip
{
    get
    {
        return _zip;
    }
    set
    {
        _zip = value;
    }
}

I think you’ll agree that it is far easier to read and understand the more terse syntax of the first example.  And code that is easier to understand is easier to maintain.

Because both syntax versions compile to the same Intermediate Language, I recommend always using the newer Automatic Properties syntax when you create properties that do nothing more than save and retrieve state.

Download the following sample code to see automatic properties in action: DemoAutomaticProperties.zip (26.83 KB)
Friday, 11 September 2009 13:17:17 (GMT Daylight Time, UTC+01:00)
# Saturday, 05 September 2009

Back To Basics

Extensions methods are a new feature of C# 3.0 and they are easier to use than they first appear.

An extension method is a method that is external to an existing class but appears as if it were a method on that class.

The rules for creating an extension method are simple.

  1. Create a static method
  2. The first parameter of the static method should be the type of the class you wish to extend
  3. Precede the parameter type of this first parameter with the "this" keyword.
  4. Call the method as if it were a method of the class. Omit the first parameter.

An example should clarify this. Assume we have a class Customer with properties FirstName and LastName as shown below

    public class Customer
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

We can create a new static class MyExtensions with a static method GetFullName that returns the formatted first and last name of the customer. We do so with the following code

   public static class MyExtensions
    {
        public static string GetFullName(this Customer cust)
        {
            string custName = cust.FirstName + " " + cust.LastName;
            return custName.Trim();
        }
    }

Notice the parameter with the "this" keyword. That parameter format tells the compiler that this is an extension method and that it should extend the Customer class. As long as MyExtensions is in the same namespace or in a namespace available to our code (via the "using" statement), we can call this new extension method with the following code

Customer cust 
    = new Customer 
         { FirstName = "David", 
           LastName = "Giard" 
         };
string fName = cust.GetFullName();
Console.WriteLine(fName);

The code above outputs:

   David Giard

As you can see in the above code, it looks as if the GetFullName method is part of the Customer class.

We can add parameters to our extension methods as we would to any other method. The first parameter (with the “this” keyword) is always used to specify the class we are extending. All other parameters act just like normal parameters. The following extension method accepts a parameter “salutation”.

public static string GetGreeting(this Customer cust, string salutation)
{
    string custName = cust.FirstName + " " + cust.LastName;
    custName = custName.Trim();
    return salutation + “ “ + custName + ":"; 
}

Although the extension method has two parameters, we only need to pass the second parameter when calling it, as shown

Customer cust = new Customer { FirstName = "David", LastName = "Giard" };
string greeting = cust.GetGreeting("Dear");
Console.WriteLine(greeting);

The code above outputs:

   Dear David Giard:

In our examples, we were adding extension methods to a class that we just created. Of course, in this case, it would have been simpler to just modify the original class.  But extension methods are more useful if you are working with someone else’s class and modifying the source code is not an option. Extension methods often offer a simpler solution than inheriting from an existing class.

The real power of extension methods comes from the fact that you can even add methods to sealed classes. It is difficult to add functionality to a sealed class because we cannot inherit from it. Change the Customer class to sealed and re-run the code to prove that it still works.

public sealed class Customer

Here is the all code in the above sample

using System;

namespace TestExtensionMethods
{
    class Program
    {
        static void Main(string[] args)
        {
            Customer cust = new Customer { FirstName = "David", LastName = "Giard" };

            string fn = cust.GetFullName();
            Console.WriteLine(fn);

            string greeting = cust.GetGreeting("Dear");
            Console.WriteLine(greeting);

            Console.ReadLine();

        }
    }


    public sealed class Customer
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }


    public static class MyExtensions
    {
        public static string GetFullName(this Customer cust)
        {
            string n = cust.FirstName + " " + cust.LastName;
            return n.Trim();
        }

        public static string GetGreeting(this Customer cust, string salutation)
        {
            string custName = cust.FirstName + " " + cust.LastName;
            custName = custName.Trim();
            return salutation + " " + custName + ":"; 
        }
    }

}

You can download the sample code at TestExtensionMethods.zip (24.26 KB)

 

Saturday, 05 September 2009 02:52:43 (GMT Daylight Time, UTC+01:00)
# Monday, 31 August 2009
 #
 

Back To Basics

This series of articles provides an introduction to relational databases in general and Microsoft SQL Server in particular.

Part 1: What is a Database?

Part 2: Relationships

Part 3: Selecting data from a table

Part 4: Aggregating data

Part 5: Joins

Part 6: Inserts, Updates and Deletes

Monday, 31 August 2009 13:04:07 (GMT Daylight Time, UTC+01:00)
# Friday, 07 August 2009

Back To Basics

This series of articles provides an introduction to the concepts used in Object Oriented Prorgramming and OOP code samples in C#.

Part 1: What is OOP?

Part 2: Understanding Objects

Part 3: Object Oriented concepts

 

Friday, 07 August 2009 03:15:45 (GMT Daylight Time, UTC+01:00)
# Thursday, 06 August 2009

Back To Basics

In this section, we will discuss the key concepts important to Object Oriented Programming (OOP). An object-oriented system has the following characteristics

  • Inheritance
  • Polymorphism
  • Abstraction
  • Encapsulation
  • Decoupling

Some systems (and some languages) don’t fully support all the above constructs and still refer to themselves as “object-oriented”. This is a matter of some debate, but it is my opinion that a language or system must implement each of the above concepts in some way in order to be considered object oriented.

Inheritance

Inheritance is the ability to create a class based on an existing class. In this model, the existing class is known as the “parent class”, or “base class” and the new class is known as the “child class” or “derived class”. By default, a child class will inherit all properties and methods of its parent class.

In C#, we inherit a class from a parent class by adding appending a colon and the name of the parent class to the child class definition, as in the following example:

public void ChildClass : ParentClass {} 

Marking a method as virtual in a parent class allows it to be overridden in a child class. This means that the method can be replaced in the child class with new code.

public virtual Int32 DoMath (Int32 num1, Int32 num2) 
{ 
  return num1 + num2; 
} 

We can then use the “override” keyword in the child class’s method to replace the code in the parent’s class method, as shown below

public override Int32 DoMath (Int32 num1, Int32 num2) 
{ 
  return num1 - num2; 
} 

It is possible to have multiple child classes that inherit from the same parent class. In some languages (but not in C#), it is also possible for a child class to inherit from multiple parent classes.

As a general rule, if I find myself writing a lot of conditional logic in a class’s methods – depending on how the object is created or the environment, the class runs one of several blocks of code – I will consider refactoring that class into a number of subclasses. Each class will inherit from a common parent class but the conditional logic is eliminated because each subclass contains only the code relevant to how it is created.

Interface Inheritance

Inheriting from an interface is similar to inheriting from a class, except the parent class does not contain any implementation, so each subclass contains all method code it needs to run. This is useful if a set of subclasses need to share the same public members but do not have any code in common. Interfaces also have the advantage that a single class can inherit from more than one interface.

Polymorphism

Earlier, we said that objects communicate by passing messages via their public interface.

Polymorphism is the ability for different objects to respond to the same messages in a different, but appropriate, way. In OOP, this is done using inheritance. Because a child class inherits properties, fields and methods from a parent class, we can have several different child classes sharing the same public members. Each of these classes would therefore accept the same message types. However, each base class may override some of the behavior of the base class and may do so in a way appropriate to itself.

For example, we may create a Vehicle class that has a method Drive() that accepts no parameters.

We can derive two child classes - Car and Airplane - from Vehicle and they will each inherit the Drive method. But driving a car is not like driving an airplane, so each of these methods would be overridden and the overridden child methods would be very different from one another. Calling the Car’s Drive method would cause the axels to turn and rotate the four wheels beneath the car. Calling the Airplane’s Drive method would explode jet fuel and propel the airplane through the sky at a high velocity.

This is an example of two objects (Car and Airplane) that accept the same message (Drive) and respond differently but appropriately.

The C# code for this set of classes would look similar to the following

public class Vehicle 
{ 
  public virtual void Drive() 
  { 
    Console.WriteLine(“Driving…”); 
  } 
} 

public class Automobile: Vehicle 
{ 
  public override void Drive() 
  { 
    Console.WriteLine(“Turn axel and wheels and tires…”); 
  } 
} 

public class Airplane: Vehicle 
{ 
  public override void Drive() 
  { 
    Console.WriteLine(“Explode jet fuel and propel through the air…”); 
  } 
} 

The picture below shows the hierarchy of classes that inherit from a single parent class.

Abstraction

Abstraction is the process of simplifying an object or system by focusing only on those parts of the system relevant to the current problem. In OOP, abstraction is typically implemented using encapsulation.

Encapsulation

Encapsulation hides implementation details of an object from that outside world. Again, this goes back to our goal of decreasing complexity. We don’t need to understand all the workings of an object in order to use it. Returning to our Automobile example, in order to start a car, I need only know to put the key into the ignition and turn it for a short time. A lot happens inside the car (gasoline is injected, battery is engaged, sparks fly, gas explodes) but I don’t need to the details of any of that in order to start the car. In fact, it’s easier for me to focus on starting the car if I ignore the other things going on within the automobile.

Decoupling

Finally, decoupling is a key point of object oriented programming that simplifies a system. In a decoupled system, the dependencies between objects are minimized. Encapsulation helps with this because external objects cannot change the internal attributes of an object if they cannot access it.

However, the responsibility for decoupling ultimately rests with the developer. Use messages to communicate between objects and maintain as little state as possible. Expose only those attributes needed by others and avoid coding side effects in your object’s methods.

In my experience, decoupling is the OOP concept that is ignored the most by people trying to build object oriented systems.

Summary

As we stated in part 1 of this series, the goal of Object Oriented Programming is to manage complexity. We do this by modeling our application as many people see their real world systems – as a set of loosely coupled objects that interact with one another. This helps us to split a complex problem into a number of more manageable objects. It also allows us to simplify the management of those objects by encapsulating complexity, maximizing flexibility through inheritance, and keeping the objects independent of one another.


Thanks to Chris Woodruff, who contributed to this article.

Thursday, 06 August 2009 12:53:29 (GMT Daylight Time, UTC+01:00)
# Wednesday, 05 August 2009

Back To Basics

Key Object Concepts

Objects are essentially a collection of structured data stored in memory. An object is based on a class that defines how to create an object.

In this section, I will describe the following concepts.

  • Classes
  • Class members
    • Properties and Fields
    • Methods
    • Events
  • Instances
  • Static Types
  • Interfaces
  • Message Passing

Because all these terms are interrelated, it is difficult to discuss one without mentioning the others. So be patient if I mention a term before I define it – I will get to the definition shortly.

Classes

A class is a definition for an object. It describes the attributes, such as properties, fields and methods (more on this later) of an object. It may also set default values and implementations for these attributes. Think of a class as a blueprint we can use to help us build an object. Generally, we work with classes only at design-time, defining the attributes appropriate for that class. In most cases, we do not work directly with classes while a program is running.

In C#, a class is defined with the following code

public class <CLASSNAME>{} 

So to create a class named MyClass, we use the declaration:

public class MyClass{} 

Public Members

A class (and an object) exposes a finite set of publicly-exposed members. Members are methods, properties and fields (defined below). This public interface is how you interact with an object. An object may be capable of far more than what it exposes by its public interface, but these other capabilities are not seen directly by the outside world. Keeping object interfaces simple is one way that an object can help simplify a complex system.

Properties and Fields

Properties and fields describe static data associated with a class. In C#, fields are implemented with the following syntax:

public <DATETYPE> <FIELDNAME> = ,VALUE; 

as in the sample code below:

public string Color = “White”; 

You can access this property with syntax like the following

obj.Color = “Black”; 
string thisColor = obj.Color; 

A property is similar to a field, but a property provides a “Getter” and “Setter” – methods that run when you attempt access the value of a property. This allows a developer to add code that will automatically run whenever a field’s value is retrieved or assigned. This code might perform validation or calculate a value on the fly. The C# syntax for fields looks like the following

private string _color; 
public string Color 
  { 
  get 
    { 
    return _color: 
    } 
  set 
    { 
    _color = value; 
    } 
  }

 Beginning with C# 3.0, this syntax can be shortened to

public string Color{get; set; }

The syntax for working with a property is identical to that for working with a field.

Methods

A method is a discrete section of code that is associated with a class and therefore with an object. Some methods return a value; others just run code and return nothing. In C#, sample syntax for a method that returns nothing is shown below.

public void WriteSomething(string messageToWrite) 
{ 
Console.WriteLine (messageToWrite); 
}

To return a value, in place of “void” in the method declaration, we specify the data type returned. Below is an example. 

public Int32 AddNumber(Int32 num1, Int32 num2) 
{ 
Int32 sumOfNumbers = num1 + num2; 
return sumOfNumbers; 
}

We can call a method of an object with syntax like the following

obj1.WriteSomething(“Hello World”); 
Int32 sum = obj1.AddNumbers(2,2);

Instances

So far, we’ve been talking about objects without defining what an object is. An object is an instance of a class – it represents a specific set of data.

We said before that a class is like a blueprint. Think of an object as the house, machine or other device built from that blueprint. Methods, properties and fields defined in a class become methods, properties and fields in any object based on that class.

It is possible to produce more than one house from the same blueprint. Similarly, it is possible to create multiple objects from the same class.

To instantiate an object in C#, we use the following syntax

<OBJECTTYPE> <OBJECTNAME> = new <OBJECTTYPE>();

In the Methods sample above, assume the methods were defined in a class named MyMathClass. Before calling the object's methods, we would first instantiate the object like this:

MyMathClass obj1 = new MyMathClass();

Static Types

It is possible to instantiate a class without explicitly creating an instance of that class. You can do this if the class is defined as “static”, as in the following example.

It is possible to instantiate a class without explicitly creating an instance of that class. You can do this if the class is defined as “static”, as in the following example.

public static class Math
{
  public static Int32 MultiplyNumbers(Int32 num1, Int32 num2)
  {
    return num1 * num2;
  }
}

Call this method with the following syntax

Int32 product = Math.MultiplyNumbers(2, 4);

Notice that we did not need to explicitly instantiate an object of type Math.  For static classes, the .Net framework takes care of this for us.

Constructors

A constructor is a special type of method that runs when an object is first created. This is a good place to put initialization code for your object. In C#, a constructor is written by creating a method with the same name as the class.

public class MyClass 
{
   public MyClass()
   {
   // Initialization code goes here
   }
}

You may create constructors that accept parameters, such as in the following C# code

public class MyClass 
{
   public MyClass(string x, string y)
   {
      // Initialization code goes here
   }
}

Events

An event is a notification by an object that something has happened. This something might be user input, such as a mouse-click on a form or it can be something less tangible, such as a customer exceeding his credit limit. Other objects may or may not respond to these events. Generally the object raising an event does not know how it will be consumed.

Interfaces

An interface looks like a class in that it can have properties, fields and methods. The difference is that the properties and methods contain no implementation code. Interfaces are used only to define the public members of a class. A class implementing an interface inherits all the public members of that interface and it is up to the class to provide implementation.

Below is sample syntax for creating an interface

interace IPerson 
{ 
  string FirstName {get; set;} 
  string LastName {get; set;} 
  Order GetAllOrders(); 
}

Use code like the following to create a class that implements an interface

class Customer: IPerson 
{ 
  public string FirstName {get; set;} 
  public string LastName {get; set;} 
  public Order GetAllOrders() 
  { 
    Order ord = new Order(); 
    // Code omitted for brevity 
    return ord; 
  } 
} 

Message Passing

Objects communicate by passing messages. These messages can be primitive data types, such as strings and integers; they can be XML; or they can be other objects.  Generally speaking objects expose public members to accept these messages. This helps to simplify the communication between objects.

In this section, we learned the basics of objects, their definitions, their members and how to work with them. In the next section, we’ll introduce Object Oriented Programming constructs and how these concepts are implemented using objects.


Thanks to Chris Woodruff, who contributed to this article.

Wednesday, 05 August 2009 05:36:25 (GMT Daylight Time, UTC+01:00)
# Tuesday, 04 August 2009

Back To Basics

What is OOP?

Most people think of their business systems in terms of the interactions of people, institutions and systems. When describing a system, they often speak in terms of these interactions between entities. For example, they may describe how a customer communicates order information to a salesperson that inputs this information into an Order Processing program. Describing entities and defining how those entities interact with one another maps very well to Object Oriented Programming (OOP). OOP is a programming paradigm that focuses on the objects in a software system and the messages passed between those objects.

Object Oriented Programming is not a new concept. The ideas behind it were first published in the 1960s and the SmallTalk language, developed in the 1970s, formalized many of the programming constructs used in this paradigm. However, OOP gained popularity in the 1990’s with the increased use of the C++ and the last ten years have seen an explosion in object-oriented languages C#, Visual Basic.Net and Java.

In earlier programming models, a program was viewed as a set of methods or subroutines. A main method would call one or more subroutines that might each in turn call other subroutines. Each subroutine would complete its task and return control to the method that called it. Using this approach, developers viewed their programs as a set of tasks. These tasks became central to their application design.

Managing Complex Systems

It was difficult to manage complexity in this task-oriented approach to programming.

For example, one couldn’t be certain that a subroutine would not substantially change variables, files and other parts of the environment. The only way to know for sure was to examine that subroutine in detail to learn how it worked. In fact, you would need to also examine any subroutines called by that subroutine, to verify that they didn’t cause any unexpected side effects.

Another complexity arises because there tends to be a tight coupling between the subroutines. Changes to a subroutine often require changes to any routine that called it, making maintenance difficult.

Often in such systems, you find code duplicated in multiple places. Making a fundamental change to how a rule is implemented requires changing code several times – another maintenance difficulty that adds to complexity.

The Development process itself adds to the complexity of writing software. On most projects, we need to consider how to divide development tasks among team members; merge this code together; manage code deployment; test code modules independently and together; deploy updates to our application; and fix errors as they are found. The entire process can be quite complex, particularly if all our modules are tightly bound together.

Finally, we cannot ignore the fact that many of the problems we are trying to solve are complex. This complexity is often our motivation to automate these problems in the first place. Anything we can do to mitigate that complexity – such as splitting a problem into manageable components – will assist us in building a solution.

OOP attempts to address these challenges by focusing on objects instead of tasks. The whole goal of OOP is to manage complexity. This is done by splitting a complex system into manageable, independent pieces and only exposing enough of each piece to allow other objects to work with it. Much of the complexity of the object can be hidden from the outside world, making the system as a whole easier to understand.

The following basic principles help to accomplish OOP’s goal of managing complexity.

  • Inheritance
  • Abstraction
  • Encapsulation
  • Polymorphism
  • Decoupling

The above concepts are interrelated in that some are used to accomplish others. In a later article, we will dive into more detail about each.

Before we can do that, it’s important to understand the basics of objects before you can grasp Object Oriented Programming.

Next: Intro to OOP, Part 2: Understanding Objects 


Thanks to Chris Woodruff, who contributed to this article.

Tuesday, 04 August 2009 14:23:52 (GMT Daylight Time, UTC+01:00)
# Monday, 27 April 2009

Episode 18

Halfway through the first West Michigan .Net University, Chris Woodruff and Bill Miller sat down with me to discuss why they organized it and how it was going so far.

For more information on this event, you can visit dodn.org/WestMichiganDotNetU or read my earlier posts here and here

17 mins, 50 secs

Monday, 27 April 2009 14:46:43 (GMT Daylight Time, UTC+01:00)
# Saturday, 28 March 2009

Last year, I noticed there were more local community-driven events than I had time to attend.  I love seeing that kind of enthusiasm for technology among the community. 

These events are great for me because - although I've been in software development for many years - I love to learn and frequently jump at the chance to learn from and share ideas with other experienced developers and architects.

I have noticed that most of these events targeted experienced developers.  Topics tended to be moderate to advanced and speakers assumed a certain level of expertise from their audience in order to grasp their talk. 

This can be frustrating for less-experienced developers.  It's difficult to understand the details of NHibernate when you are still trying to figure out how to write simple queries against SQL Server.

Chris Woodruff first described to me the idea of a .Net University - a community event targeted at those new to .Net programming.  All the content would be introductory, allowing people to learn fundamentals.

I liked the idea immediately.  When I was a trainer, I always spent time on the first day reviewing the basic prerequisites of the class material.  I knew that no one would understand anything about web development if they didn't know how to construct an HTML document. 

So Chris is now organizing the West Michigan .Net University that will be held April 4 in Grand Rapids. 

The morning sessions will all be introductory and the afternoon will feature more intermediate topics.  Each session will be about 3 hours - longer than at a typical code camp.

I'll be there delivering a morning session titled SQL 101, in which I'll explain the basics of working with a relational database - SQL Server in particular.  I"ll describe how to work with data and how to use code and tools to interact with SQL Server.  I've been preparing for this talk by writing a 'Back To Basics' series on this blog.

Other sessions include .Net Bootcamp; Intro To ASP.Net and AJAX; and Real World Architecture.  You can view the complete session list at http://dodn.org/WestMichiganDotNetU/Sessions.aspx

A lot of great speakers have signed up for this event so I'm counting on some great sessions. 

WM .Net University April 4, 2009 - I'll be there!

Saturday, 28 March 2009 22:35:47 (GMT Standard Time, UTC+00:00)
# Friday, 27 March 2009
Back To Basics

NOTE:

For demos in this article, we will use a table named Customer that contains the following columns:

Name Data Type
FirstName nvarchar(50)
LastName nvarchar(50)
StreetAddress nvarchar(255)
City nvarchar(255)
State char(2)
ZipCode nvarchar(10)
TotalSales decimal(18,2)

In addition, I created a primary key on the CustID column and set it to autoincrement by setting the following properties:

Is Identity Yes
Identity Seed 1
Identity Seed 1

Afer adding a couple rows to the table, the data looks like this.

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 Steve Smith 900 Belle St Detroit MI 48888 5000.00
2 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00

We can use the INSERT command to add a new row to this table. The syntax of the INSERT command is

INSERT INTO [TableName]
    (
    [Column List]
    )
VALUES
    (
    [Values List]
    )

We can insert a row for a new Customer - Brad Van Pelt - with the following code.

INSERT INTO Customer
    (
    FirstName, 
    LastName, 
    StreetAddress, 
    City, 
    State, 
    ZipCode, 
    TotalSales
    )
VALUES
    (
    'Brad',
    'Van Pelt', 
    '99 Linebaker Ln', 
    'Owosso', 
    'MI', 
    '47777', 
    4000
    )

Notice that each column name in the first set of parentheses matches a value in the second set of parentheses: 'Brad' with FirstName, 'Van Pelt' with LastName and so on. Notice also that we did not provide a value for the CustID column. This is because CustID is an identity column and, therefore, gets populated with an incremented number when a new row is added.

After executing the above INSERT statement, our data should look like this:

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 Steve Smith 900 Belle St Detroit MI 48888 5000.00
2 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00
3 Brad Van Pelt 99 Linebaker Ln Owosso MI 47777 4000.00

In this article, we showed how to use T-SQL's INSERT, UPDATE, and DELETE commands to modify the data in a table.

The new customer was automatically assigned a CustID value of 3. Because this value uniquely identifies the newly-added row, we can use it to find and update that row. The syntax to update a row in SQL Server is

UPDATE [Table]
    SET [Column1] = [New Value 1],
        [Column2] = [New Value 2],
        [Column3] = [New Value 3],
        etc...
    WHERE [Filter Condition]

Only rows that match the filter condition will be updated and only those columns specified in the SET clause will be updated.  We will use the following command to update the StreetAddress, City, State and ZipCdoe columns of Customer 3:

UPDATE Customer
    SET StreetAddress = '100 Safety St',
        City='New York',
        State='NY'
        ZipCode='01111'
    WHERE CustID = 3

After executing the above UPDATE command, our data should like this:

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 Steve Smith 900 Belle St Detroit MI 48888 5000.00
2 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00
3 Brad Van Pelt 100 Safety St New York NY 01111 4000.00

We use the DELETE command to delete rows in a SQL Server table. The DELETE syntax is

DELETE Customer
    WHERE CustID = 3

The following code will delete Customer 3

DELETE Customer
    WHERE CustID = 3

After executing the above DELETE command, our data will look like this:

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 Steve Smith 900 Belle St Detroit MI 48888 5000.00
2 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00

In this article, we showed how to use the INSERT, UPDATE and DELETE commands to modify data in a SQL Server table.

Friday, 27 March 2009 04:18:46 (GMT Standard Time, UTC+00:00)
# Saturday, 21 March 2009
Back To Basics

NOTE:

For demos in this article, we will use three tables: Customer, SalesOrder and OrderLine. 

The structure of the Customer table is:

Name Data Type
CustID int
FirstName nvarchar(50)
LastName nvarchar(50)
StreetAddress nvarchar(255)
City nvarchar(255)
State char(2)
ZipCode nvarchar(10)

The Customer table contains the following data.

CustID FirstName LastName StreetAddress City State ZipCode
1 David Giard 123 Oxford Ct Erlanger KY 40111
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222
3 Bubba Smith 789 Killbubbakill St Baltimore MD 10111

The structure of the SalesOrder table is
Name Data Type
OrderID int
CustID int
OrderDate datetime

The SalesOrder table contains the following data.

OrderID CustID OrderDate
1 1 2009-03-01
2 1 2009-03-02
3 2 2009-03-07
4 2 2009-03-14
5 3 2009-03-21

The structure of the OrderLine table is
Name Data Type
OrderID int
LineNumber int
ProductName nvarchar(255)
Quantity int

The OrderLine table contains the following data  
OrderID LineNumber ProductName Quantity
1 1 Widget 7
1 2 Super Widget 4
2 1 Widget 5
2 2 Super Widget 3
3 1 Widget 2
4 1 Super Widget 3
5 1 Widget 6
5 2 Super Widget 1

In a previous article, I explained how we can (and often should) split a table into multiple tables in order to eliminate data redundancy - a process known as "normalization".

In this article, I'll explain how to retrieve related data from multiple tables and return them in a single result set.

Recall from the Normalization article that - in order to relate to tables - we add a key to each table.  The Primary key in the parent table is a column that is unique for each row and, therefore, servers to uniquely identify a row.  The child table contains a foreign key which is the same value as a Primary key in the parent table, so it points to a given row in the parent.

To retrieve data from multiple tables into a single result set, we do something called a "JOIN".  In SQL, there are two ways to JOIN tables:

  • Using the JOIN keyword
  • Adding the join condition on a WHERE clause

JOIN keyword

The syntax for joining tables with the JOIN keyword is

The syntax for joining tables with the JOIN keyword is

SELECT [List of Columns]
    FROM [Table 1]
    JOIN [Table 2]
        ON [Join Condition]

 For example, to retrieve the Name, SalesOrder Date and SalesOrder Amount of each customer in our sample tables, use the following query:

SELECT 
        FirstName, 
        LastName, 
        OrderDate 
    FROM Customer 
    JOIN SalesOrder 
        ON Customer.CustID = SalesOrder.CustID

Notice that we need to prefix the CustID column name with the table name in our filter condition.  This is because the CustID column name is not unique.  We need to tell SQL to which column we are referring.

The results of this query are

FirstName LastName OrderDate
David Giard 2009-03-01
David Giard 2009-03-02
David Giard 2009-03-07
Magic Johnson 2009-03-14
Bubba Smith 2009-03-21

WHERE clause

The syntax for joining two tables with the WHERE clause is

SELECT [List of Columns]
    FROM [Table 1], [Table 2]
    WHERE [Join Condition]

The syntax to return the same result set as above is

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer, SalesOrder
    WHERE Customer.CustID = SalesOrder.CustID

Recall that the WHERE clause is also used to filter your result set.  In fact, you can use it for both filtering and joining.  The following two queries yield the same results (showing only those records that match customer 1.

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer
    JOIN SalesOrder
        ON Customer.CustID = SalesOrder.CustID
    WHERE Customer.CustID = 1

SELECT
        FirstName,
        LastName,
        OrderDate
    FROM Customer, SalesOrder
    WHERE Customer.CustID = SalesOrder.CustID
        AND Customer.CustID = 1

Here is the result set for either of the above two queries:

FirstName LastName OrderDate
David Giard 2009-03-01
David Giard 2009-03-02
David Giard 2009-03-07

You can use these same techniques to join more than two tables. Here is the syntax to add the OrderLine table to our queries

SELECT
        SalesOrder.OrderID,
        FirstName,
        LastName,
        OrderDate,
        ProductName,
        Quantity
    FROM Customer
    JOIN SalesOrder
        ON Customer.CustID = SalesOrder.CustID
    JOIN OrderLine
        ON SalesOrder.OrderID = OrderLine.OrderID

SELECT
        SalesOrder.OrderID,
        FirstName,
        LastName,
        OrderDate,
        ProductName,
        Quantity
    FROM Customer, SalesOrder, OrderLine
    WHERE Customer.CustID = SalesOrder.CustID
        AND SalesOrder.OrderID = OrderLine.OrderID

Here is the result set of either of these 3-table queries

OrderID FirstName LastName OrderDate ProductName Quantity
1 David Giard 2009-03-01 Widget 7
1 David Giard 2009-03-01 Super Widget 4
2 David Giard 2009-03-02 Widget 5
2 David Giard 2009-03-02 Super Widget 3
3 David Giard 2009-03-07 Widget 2
4 Magic Johnson 2009-03-14 Super Widget 3
5 Bubba Smith 2009-03-21 Widget 6
5 Bubba Smith 2009-03-21 Super Widget 1

I prefer to use the JOIN keyword syntax when joining tables together because it is more clear what part of the query is a filter and what part of a query is a join. 

In this article, we showed the ways to use SQL Server to join multiple tables into a single result set.

Saturday, 21 March 2009 22:31:36 (GMT Standard Time, UTC+00:00)
# Thursday, 12 March 2009
Back To Basics

NOTE:

For demos in this article, we will use a table named Customer that contains 7 columns:

Name Data Type
FirstName nvarchar(50)
LastName nvarchar(50)
StreetAddress nvarchar(255)
City nvarchar(255)
State char(2)
ZipCode nvarchar(10)
TotalSales money

Afer adding a few rows to the table, the data looks like this.

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 David Giard 123 Oxford Ct Erlanger KY 40111 500.00
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222 1500.00
3 Bubba Smith 789 Killbubbakill St Baltimore MD 10111 1000.00
4 Ron Mason 501 E Grand River Ave Lansing MI 45333 2000.00
5 Steve Smith 900 Belle St Detroit MI 48888 5000.00
6 Ryan Miller 1 Shutout Ct Buffalo NY 32323 250.00
7 Brad Van Pelt 99 Linebaker Ln Owosso MI 47777 4000.00

In the last article in this series, I expleined the basic functionality of the SELECT statement. I showed how to select columns from a table and sort or filter the results.

In this article, I'll show some more things you can do with the SELECT statemnts.

Sometimes we want our results to aggregate data. When aggregating data, we use functions that consolidate multiple rows and return the result of this aggregate function applied to many rows. The most common aggregate functions I use that SQL Server supports are:

Function Descripiton
MAX The maximum value of a column across all included rows
MIN The minimum value of a column across all included rows
SUM The sum of all values in a column across all included rows
AVG The arithmetic average of all values in a column across all included rows
COUNT The number of included rows

The MIN, MAX, SUM, and AVG functions accept a parameter - the name of the column on which to calculate these values. The column must hold a numeric data type, such as an INT, FLOAT or MONEY.

You may pass a column name as a parameter to the COUNT function but it doesn't matter which column because - in SQL Server - every column appears exactly once in each row and the COUNT function is used to count rows. By convention, we pass "*" as the parameter to the COUNT aggregate function to represent all rows.

A few examples will help clarify this. Since our table contains only one numeric column - TotalSales - we will use this in most of our aggregate functions.

The following query returns the maximum value of the TotalSales column.


SELECT MAX (TotalSales) AS MaxSales
    FROM Customer

Here are the results - one row with one column containing the highest numerical value in the TotalSales column.

MaxSales
5000.00

Similarly, the following query returns the sum of all values in the TotalSales column.

SELECT SUM (TotalSales) AS SumSales
    FROM Customer

This returns 14,750 which is the sum of 1000 + 1500 + 1000 + 2000 + 5000 + 250 + 4000

SumSales
14750.00

We can filter before applying an aggregate function. If we only want to sum of TotalSales for customers in michigan, we simply add a WHERE clause to our query.

SELECT SUM (TotalSales) AS SumSales
    FROM Customer
    WHERE State = 'MI'

This returns 12,500, which is 1500 + 2000 + 5000 + 4000, or the sum of the TotalSales column for only those customers in Michigan.

SumSales
12500.00

As mentioned before, we do not need to specify a particular column for the COUNT function.

SELECT COUNT (*) AS NumCusts
    FROM Customer

returns the a row and column containing number 7, which is how many rows are in our table.

So far, each query we have written has returned only one row. Often, however, we want to calculate an aggregation for each distinct value in a column or columns. We can do this by adding a GROUP BY clause to the query. For example, we may want to see the SUM of TotalSales for each state.

SELECT 
        State, 
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUP BY State

The above query returns a row for each distinct value in the State column and calculates the sum of TotalSales of all rows corresponding to that state.

State SumSales
 KY 500.00
MD 1000.00
MI 12500.00
NY 500.00

It's important to note that, when using the GROUP BY clause, you cannot return a column that is not part of the grouping. So

SELECT 
        State, 
        City, 
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUP BY State

results in an error because we are trying to return the city column, but we are not grouping on that column. Because a given state can have multiple cities, SQL does not know which one to display for the row returned.

You can group on multiple columns as in the following query

SELECT 
        State, 
        City, 
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUP BY State, City

In this case, we get a row with a sum for each combination of state and city.

State City SumSales
 KY Erlanger 500.00
MD Baltimore 1000.00
MI Lansing 1500.00
MI Okemos 2000.00
MI Owosso 4000.00
NY Buffalo 250.00

By using the GROUP BY clause on a large table, we may end up with so many rows that it becomes difficult to find relevant data. Sometimes, we are only interested in those times when the aggregate value exceeds some threshhold. In these cases, it would be nice to only show aggregate rows that exceed that threshhold. This sounds like a good place to use a filter. Unfortunately, we cannot use the WHERE clause to accomplish this task because the WHERE clause filters data before the aggregation. We have to wait until after calculating the aggregate values becuase it is the aggregate values on which we want to filter. The HAVING clause is used to filter on aggregate values.

We can run our query to get the TotalSales sum for each state, but show only those states that have total sales of more than 2000, using the following query

SELECT 
        State, 
        SUM (TotalSales) AS SumSales
    FROM Customer
    GROUP BY State
    HAVING SUM (TotalSales) >= 1000

In this case, we don't see the row for Kentucky and New York because they had total sales summing less than $1000

State SumSales
MD 1000.00
MI 112500.00

In this article, we showed how to use T-SQL's grouping and aggregate functions to return summary data from a database.

Thursday, 12 March 2009 14:17:07 (GMT Standard Time, UTC+00:00)
# Wednesday, 11 March 2009

Saturday April 4 at the Robert C. Pew Grand Rapids Campus of Grand Valley State Universityin Grand Rapids, Shane Jordan and I will be delivering a session on the basics of SQL Server.  This will cover many of the same topics as the Back To Basics: SQL 101 series I have writton on this blog.

The session is part of the West Michigan .Net University event.  You can get more informaiton and register at http://www.dayofdotnet.org/WestMichiganDotNetU/

West Michigan .Net University

Wednesday, 11 March 2009 14:59:10 (GMT Standard Time, UTC+00:00)
# Tuesday, 10 March 2009
Back To Basics

NOTE:

For demos in this article, we will use a table named Customer that contains the followong columns:

Name Data Type
FirstName nvarchar(50)
LastName nvarchar(50)
StreetAddress nvarchar(255)
 City nvarchar(255)
State char(2)
 ZipCode nvarchar(10)
TotalSales money

Afer adding a few rows to the table, the data looks like this.

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 David Giard 123 Oxford Ct Erlanger KY 40111 1000.00
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222 1500.00
3 Bubba Smith 789 Killbubbakill St Baltimore MD 10111 1000.00
4 Ron Mason 501 E Grand River Ave Okemos MI 45333 2000.00
5 Steve Smith 900 Belle St Detroit MI 48888 5000.00

Retrieving data is one of the most common tasks performed on a relational database.

Fortunately, SQL Server includes a language that allows users (and programs) to retrieve the data they want. This language is called Structured Query Language. It is usually abbreviated "SQL" and often pronounced "SEE-kwuhl".

Getting data from a database is known as querying the database. The code to retrieve that data is a query. When this code is written in SQL (as most of my queries are), it is known as a SQL query.

The basic syntax for a SQL query that retrieves data from a single table is

SELECT [List of Columns]
    FROM [Table Name]
    WHERE [Filter Condition]
    ORDER BY [Sort column or columns]

In SQL, line breaks and extra spaces are not important. The language parser is smart enough to figure out when a statement or command ends, so I usually try to format my SQL statements to make them easy to read.

Only the SELECT and FROM parts of the query are required, so let's start with those.

EvenEven though the "SELECT" keyword typically comes first in this type of query, I'll start by explaining the "FROM" keyword. When getting data from only one table, follow the keyword FROM with the name of that table. For example, the clause

    FROM Customer 

indicates that we are getting data from a table named Customer. We'll talk later about how to get data from multiple tables in the same query.

It is possible to provide an alias for a table by following the table name with a space, and the alias. For example

    FROM Customer cust

The above SQL clause says that we will get data from the Customer table, but that we will use the string “cust” to refer to this table elsewhere in our query.

This is useful in the following situations

  • You want to provide a shorter name for the table in order to avoid retyping a long name elsewhere in the query
  • You want to avoid ambiguity when listing the same table name twice in the FROM clause.

SELECT is the first keyword of this type of query and tells SQL Server that we want to retrieve data from the database. The word "SELECT" is followed by a list of columns that the query will return. If the column list contains duplicate column names (as when you are getting data from two different tables and they each have a column with the same name), you should precede the column name with the table name or alias.

You can also use the special character "*" in place of (or in addition to) the list of column names in order to return all columns in the tables.

The following query returns all columns and rows in the Customer table.

SELECT * 
    FROM Customer 
CustID FirstName LastName StreetAddress City State ZipCode TotalSales
1 David Giard 123 Oxford Ct Erlanger KY 40111 1000.00
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222 1500.00
3 Bubba Smith 789 Killbubbakill St Baltimore MD 10111 1000.00
4 Ron Mason 501 E Grand River Ave Okemos MI 45333 2000.00
5 Steve Smith 900 Belle St Detroit MI 48888 5000.00

We can return onWe can return only the FirstName and LastName columns from the Customer table with the following table

SELECT 
        FirstName, 
        LastName 
    FROM Customer 
FirstName LastName
David Giard
Magic Johnson
Bubba Smith
Ron Mason
Steve Smith

In the queries above, it is possible to qualify the column names, explicitly indicating that they are from the Customer table. Of course, in this case it is unnecessary because the column names are unique within the Customer table. The following examples qualify the column names and returns the same data.

SELECT
        Customer.FirstName,
        Customer.LastName
    FROM Customer

SELECT
        cu.FirstName,
        cu.LastName
    FROM Customer cu

By default, each column returned by the query retains the name of the corresponding column in the source table. you want to change the name of a If you want to change the name of a column in the query, alias that column by appending the keyword " AS " followed by the alias you want. For example

SELECT
        FirstName AS FirstNm,
        LastName AS LastNm
    FROM Customer

This will return the following output
FirstNm LastNm
David Giard
Magic Johnson
Bubba Smith
Ron Mason
Steve Smith

If wIf we want to sort the output we can add the ORDER BY clause to our query. The syntax for this clause is

ORDER BY [List of Columns on which to sort]

For example, we can sort our output on Last Name by changing our query to

SELECT
        FirstName,
        LastName
    FROM Customer
    ORDER BY LastName

FirstName LastName
David Giard
Magic Johnson
Steve Smith
Bubba Smith
Ron Mason

We can add more columns to the list of sort columns if we separate each with a comma. The second column is only appropriate in our sort if two rows have identical values for the first column.

For example

SELECT FirstNameFor example

SELECT
        FirstName,
        LastName
    FROM Customer
    ORDER BY LastName, FirstName

FirstName LastName
David Giard
Magic Johnson
Bubba Smith
Steve Smith
Ron Mason

The above result set contains 2 rows with the last name "Smith".  These two rows were sorted in order of their FirstName column.

In many cases, we may not want to return eIn many cases, we may not want to return every row in a table. We can use the WHERE clause to filter data. The syntax of the WHERE clause is

   WHERE [Filter Condition]

The Filter condition is a Boolean expression, meaning it evaluates to either TRUE or FALSE for every row. The query will return only those rows for which this condition evaluates to TRUE.

For example, if we want to get only those customers in Michigan we can use the query

SELECT
   FROM Customer 
   WHERE State = 'MI'

CustID FirstName LastName StreetAddress City State ZipCode TotalSales
2 Magic Johnson 456 Hollywood Blvd Lansing MI 45222 1500.00
4 Ron Mason 501 E Grand River Ave Okemos MI 45333 2000.00
5 Steve Smith 900 Belle St Detroit MI 48888 5000.00

This query only returned those rows that match our filter condition.

Of course, we can combine several of these clauses as in the following

SELECT
        cu.FirstName,
        cu.LastName,
        cu.State AS ResidencyState
    FROM Customer cu
    WHERE cu.State = 'MI'
    ORDER BY cu.LastName

which returns the following results
FirstName LastName ResidencyState
Magic Johnson MI
Ron Mason MI
Steve Smith MI

As you can see, we can use the SELECT commmand to retrieve data from a table in a database and customize the way that data comes back.  We've just scratched the surface of this command.  In the next article, we'll look at more options of the SELECT command

Tuesday, 10 March 2009 16:36:54 (GMT Standard Time, UTC+00:00)
# Thursday, 26 February 2009

In my last article, I defined the basic concepts of database, table, column and row. Using these constructs, you can organize data into a rectangular format. This paradigm often works really well, because

  • You can group related information into a single container (a table)
  • Each row represents a single entity (such as a customer, employee, or invoice) and
  • Each column represents an attribute of the entity (such as FirstName, LastName, or TotalSales).

Using this model, we can create a table containing information about a customer's purchases. Each row in this item might represent a single purchase.

When a customer purchases an item, we probably would want to store some information about that purchase. These bits of information about each purchase are attributes of the purchase and are therefore candidates for columns. Below are examples of the information we might want to save about a customer's purchase.

  • Date of Purchase
  • Customer First Name
  • Customer Last Name
  • Customer Street Address
  • Customer City
  • Customer Zip Code
  • Item Purchased
  • Quantity Purchased
  • Price per Item

We can create a table CustomerPurchase with a column for each of the above attributes and begin populating with data each time a customer purchases something. The data would look something like this:

PurchaseDate Customer
FirstName
Customer
LastName
Customer
StreetAddress
Customer
City
Customer
ZipCode
ItemPurchased Quantity PricePerItem
2/26/2009 John Smith 123 Elm Bigg City 48222 Lamp 1 40
2/26/2009 Bill Jones 456 Maple Smallville 48333 Chair 2 100
2/26/2009 Mary Brown 789 Oak Middleton 48444 Table 1 50

This model seems to capture the information we want. Do you see any problems with it?

What happens if a customer orders more than one item? If John Smith purchases a Chair in addition to his Lamp, we can just add another row to the table, like so.

PurchaseDate Customer
FirstName
Customer
LastName
Customer
StreetAddress
Customer
City
Customer
ZipCode
ItemPurchased Quantity PricePerItem
2/26/2009 John Smith 123 Elm Bigg City 48222 Lamp 1 40
2/26/2009 Bill Jones 456 Maple Smallville 48333 Chair 2 100
2/26/2009 Mary Brown 789 Oak Middleton 48444 Table 1 50
2/26/2009 John Smith 123 Elm Bigg City 48222 Chair 1 100
2/27/2009 John Smith 123 Elm Bigg City 48222 Table 1 50

But notice that now we are storing John Smith's name and address multiple times.  Assuming John Smith will never change his name, this is a waste of space.  Granted, this isn't very much wasted space when we have only a few orders, but imagine a system with thousands of customers and millions of orders.  Do you really want all that redundant information cluttering up your database?

Also, imagine that we want to correct an error in the spelling of John's name.  With the current model, we must correct that error three times due to the redundant storage.

To address these issues, we can normalize the data.  Data normalization refers to structuring our data in order to remove redundancy. 

In our example, we accomplish this by creating a table of customers with the following structure

  • FirstName
  • LastName
  • StreetAddress
  • City
  • ZipCode

and and moving the customer data to this table - one row per customer.

FirstName LastName StreetAddress City ZipCode
John Smith 123 Elm Bigg City 48222
Bill Jones 456 Maple Smallville 48333
Mary Brown 789 Oak Middleton 48444
 

Then we add an extra column to the ustomerPurchase tab table.  This new column is special in that the value in it will uniquely identify each row - in other words, no two rows will have the same value.  This unique column goes by many names but we will call it a Primary Key here.  In this case, the Primary Key column will be named "CustomerID" and will hold an integer.

CustomerID FirstName LastName StreetAddress City ZipCode
1 John Smith 123 Elm Bigg City 48222
2 Bill Jones 456 Maple Smallville 48333
3 Mary Brown 789 Oak Middleton 48444
 

Now we can go back to the ustomerPurchase tab table, and replace the columns that describe customer with a column to hold the CustomerID.  This replacement column is known as a "Foreign Key".  It references a Primary Key in another table and is used to point to a single unique record in that other table.

PurchaseDate CustomerID ItemPurchased Quantity PricePerItem
2/26/2009 1 Lamp 1 40
2/26/2009 2 Chair 2 100
2/26/2009 3 Table 1 50
2/26/2009 1 Chair 1 100
2/27/2009 1 Table 1 50
 

This is all we need because, given the CustomerID, we can look in the Customer table, find the record for that customer and get all information about that customer.

This concept of using a key value to point to a row in another table is known as a relationship.  We say that the Customer table is related to the CustomerPurchase tab table. 

This type of relationship is known as a one-to-many relationship, every customer may have many orders.  In this type of relationship the table with one row is known as the parent and the table with (potentially) many rows is known as the child table.  

This relationship is typically represented by a drawing similar to the one below.

Organizing data in this way can make storage of that data far more efficient and flexible.

Thursday, 26 February 2009 13:17:33 (GMT Standard Time, UTC+00:00)
# Tuesday, 24 February 2009

In this article, we will define a database, a table and the main parts of a table - rows and columns.

A database is an organized (or structured) collection of information.

Most companies spend a lot of time and effort collecting information and storing it somewhere, but not all that information is organized, which makes it difficult to retrieve anything relevant later on.  A database adds structure to the information making it easier to maintain and query it.

Database engines like SQL Server provide a structure to organize data in a way that makes sense to a user.  Specifically, SQL Server uses a relational model* to organize its data.

In a relational database, data is partitioned into tables.  Tables are a way of data storing data in rows and columns, kind of like in an Excel worksheet. 


Figure 1 - Data in an Excel workbook

I've always found this rectangular view of data very intuitive.

Just as in a workbook, each table row represents a discrete record.  All information in that row serves to describe the row.  

Similarly, a table column is a placeholder that describes a single attribute for each row.  The advantage SQL Server has over Excel is that you can easily place rules onto a column, restricting the type of data that can be stored there. 

If a SQL Server column is designed to hold a date, a property of that column can be set to throw an error if a person or program tries to store a string.   We can set up such restrictions for many data types, so that a column can be restricted to allow only integers, only TRUE/FALSE values, or only binary objects.  We can even restrict the maximum length of a string or require users to always enter a value into a column - all simply by setting properties on the column.**

For example, a table named "Customers" might be used to store information about your company's customers.  Each row in this table would represent a single customer.  Each column would hold an attribute of that customer, so you could create columns such as FirstName, LastName and StreetAddress that would hold the appropriate values for each customer. 


Figure 2 - Data in a SQL Server table

Looking at the first row, gives us information about the customer.  It should be obvious that this customer has a first name of "David", a last name of "Giard" and an address of "123 Main St".


*SQL Server does provide some non-relational ways of storing data but those are beyond the scope of this article.
** It is possible to configure Microsoft Excel to restrict data input, but this task is relatively advanced and far more easily accomplished in SQL Server.

Tuesday, 24 February 2009 18:14:48 (GMT Standard Time, UTC+00:00)
# Sunday, 22 February 2009

I deal with a lot of smart, passionate people with years of experience in technology. 

I've noticed that these folks love to talk about and write about the next generation of software and advanced topics in software development.  This is great because it gives me a chance to learn new things from the smart people in my life. 

Unfortunately, not everyone is ready for advanced topics.  Developers who are just starting their careers need to understand the basics of languages, programming constructs and relational databases before diving deeper into these and other areas.  These basic topics are often less interesting to experienced developers but they are vitally important.  And who is better capable of explaining them than an experienced developer or architect?

I'll address this partial vacuum in a new Back To Basics feature on this site.  In this feature, I'll explain some fundamental concepts of software development, assuming little or no experience on the part of the reader.

The feature begins this week with a set of articles entitled SQL Server 101, in which I'll describe the basics of SQL Server and relational databases.  This will tie in with a talk I'll be giving at the West Michigan .Net University April 4 in Grand Rapids, MI.

Sunday, 22 February 2009 04:42:15 (GMT Standard Time, UTC+00:00)