Enabling SQL Server 2008 (R2) access over Network

First: enable SQL Server itself to be accessed over the network

  1. Open SQL Server Configuration Manager
  2. Expand SQL Server Network Configuration and click Protocols for MSSQLSERVER
  3. Doubleclick TCP/IP
  4. Set Enabled to Yes

extra info on part one
(click for large size)

Secondly: change the Windows Firewall to allow incoming connections on the TCP port of SQL Server

  1. Open Windows Firewall with Advanced Security
  2. Click on New Rule

firewall explanation
(click for large size)

Now in the wizard you set the type of the rule to Port.

Choose TCP or UDP port

Hit Next.

On the second window you set the Specific local ports to 1433:

Which TCP Port

Hit Next.

Allow the connection.

Allow the connection

Hit Next.

Now enable the checkboxes you want to. I set mine only to Private. Because I only need to access the SQL on my laptop at home:

When does this rule apply?

Hit Next.

New name for the rule

Hit Finish and you’re ready to develop SQL over network :)

VB.NET Casting vs C# casting

Since I’m still working on a VB.NET I start to understand the language more and more. It feels like working with PHP. There are so many hacks, many things happen without you knowing that it happens.

Consider the following classes defined:

class Foo {}
class Bar : Foo
{
    internal static Foo GetInstance()
    {
        return new Bar();
    }
}

In C# you would have:

Console.WriteLine(Bar.GetInstance() as Bar);

the ‘as’ operator tries to cast foo to an instance of Bar. If that doesn’t succeed it returns null.

The code gets compiled to something like this:

{
    Foo __foo = Bar.GetInstance();
    Console.WriteLine(__foo is Bar ? (Bar)__foo : (Bar)null);
}

Doing a cast:

Console.WriteLine((Bar)Bar.GetInstance());

Throws an exception if we cannot cast. So you need to do a try catch around it.

Those are the casting operators in C#.

VB.NET has a little bit more stuff.

I will list them briefly:

CType(obj, Type), DirectCast(obj, Type), TryCast(obj, Type) and some predefined functions like CBool, CStr, Cint.

DirectCast(obj, Type) is the equivalent of the (Type)obj. No problem. TryCast(obj, Type) is the equivalent of obj as Type. No problem either. The problem arrises when we use CType and or one of those predefined functions.

You’d expect CBool(obj) to result in (you cannot do TryCast on a valuetype, hence the DirectCast) DirectCast(obj, Boolean). But no.

Consider the following code:

Dim boolTest As Object = False
Console.WriteLine(CBool(boolTest))

When compiled it gets converted to this:

Dim boolTest As Object
boolTest = CBool(0)
Console.WriteLine(Conversions.ToBoolean(boolTest))

You can take a look at the Conversions class with Reflector. It’s located in the Microsoft.VisualBasic dll (C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Microsoft.VisualBasic.dll) and the full name is Microsoft.VisualBasic.CompilerServices.Conversions.

Reflector Visual Basic

CBool should just try a DirectCast in my opinion.

But CType has some more nuisances. We are still using the Foo and Bar classes defined above.

Consider this code:

Dim foo As Foo = Bar.GetInstance()
Console.WriteLine(CType(foo, Bar))

Gets compiled to:

Dim foo As Foo
Console.WriteLine(DirectCast(Bar.GetInstance, Bar))

Great, it uses DirectCast. So if the conversion fails it throws an exception…

Now the catch:

Dim test As Object = String.Empty
Console.WriteLine(CType(test, String))

You’d EXPECT it to do a DirectCast but no, this is what gets emmited:

Dim test As Object
Console.WriteLine(Conversions.ToString(String.Empty))

Again a roundtrip you don’t see…

That’s why I don’t like VB.NET. Too much happens behind the screens.

I know that you can program VB.NET without all of this (use DirectCast and TryCast yourself). But the problem is that all legacy developers use these functions because they don’t know better.

I wish these features where deprecated and only available for projects converted from VB6 code.

Property setters and side effects

I ran into this little problem last week. I had a class with some properties and they were implemented like this:

class Foo
{
    private int _bar;

    public int Bar
    {
        get
        {
            return this._bar;
        }
        set
        {
            this._bar = value;
            this.DoSomeThing();
        }
    }

    private void DoSomeThing()
    {
        /* blah */
    }
}

Setting the value of Bar to something triggers DoSomeThing, whether the value of _bar is changed or not. (setting _bar to 5 when it is 5 will still trigger DoSomeThing, for example a UI refresh).

You can avoid this by doing this in your property:

class Foo
{
    private int _bar;

    public int Bar
    {
        get
        {
            return this._bar;
        }
        set
        {
            if (this._bar != value)
            {
                this._bar = value;
                DoSomeThing();
            }
        }
    }

    private void DoSomeThing()
    {
        /* blah */
    }
}

This makes sure that you don’t execute the value when the value hasn’t changed.

This occurs in particular in Excel when trying to update the CurrentPageName of a DataField in a PivotTable. I need to check if the value has changed, and then, if it has changed, assign it.

Dispose and Finalizing, done the right way

I created this class to make life a little bit easier for me.

You are free to use it as you wish!

How to use:

Override this class, override ReleaseManaged() and ReleaseUnmanaged() with the appropriate code, and you are good to go.

namespace SuperDisposeImplementation
{
    using System;

    /// <summary>
    /// Override this class for easy releasing of managed and unmanaged code.
    /// </summary>
    /// <remarks>
    /// By Kristof Mattei
    /// Use as you wish
    /// I don't hold the copyright
    /// Combined from code I found everywhere.
    /// </remarks>
    public abstract class SuperDispose : IDisposable
    {
        /// <summary>
        /// True if managed resources are already cleaned up, false if not
        /// </summary>
        private bool _disposed;

        #region IDisposable Members

        /// <summary>
        /// Implementation of IDisposable.Dispose(). Don't make virtual
        /// </summary>
        public void Dispose()
        {
            this.Dispose(true);
            // This object will be cleaned up by the Dispose method.
            // Therefore, you should call GC.SupressFinalize to
            // take this object off the finalization queue
            // and prevent finalization code for this object
            // from executing a second time.
            GC.SuppressFinalize(this);
        }

        #endregion

        /// <summary>
        /// Dispose(bool disposing) executes in two distinct scenarios.
        /// If disposing equals true, the method has been called directly
        /// or indirectly by a user's code. Managed and unmanaged resources
        /// can be disposed.
        /// If disposing equals false, the method has been called by the
        /// runtime from inside the finalizer and you should not reference
        /// other objects. Only unmanaged resources can be disposed.
        /// </summary>
        /// <param name="disposing">True when called from the Dispose, false when called from the ~. Don't call yourself</param>
        private void Dispose(bool disposing)
        {
            // Check to see if Dispose has already been called.
            if (!this._disposed)
            {
                // If disposing equals true, dispose all managed
                // and unmanaged resources.
                if (disposing)
                {
                    this.ReleaseManaged();
                }

                // Dispose unmanaged resources.
                this.ReleaseUnmanaged();

                // disposing has been done, make sure we don't dispose the managed ones again.
                this._disposed = true;
            }
        }

        /// <summary>
        /// Override this method, and release unmanaged resources in that method
        /// </summary>
        protected abstract void ReleaseUnmanaged();

        /// <summary>
        /// Override this method, and release managed resources in that method
        /// </summary>
        protected abstract void ReleaseManaged();

        /// <summary>
        /// Use C# destructor syntax for finalization code.
        /// This destructor will run only if the Dispose method
        /// does not get called.
        /// It gives your base class the opportunity to finalize.
        /// Do not provide destructors in types derived from this class.
        /// </summary>
        ~SuperDispose()
        {
            // make sure we don't dispose managed resources, hence the false
            // this is because we can't control the called order of
            this.Dispose(false);
        }
    }
}

C# to VB.NET, && and And and AndAlso

There is a difference in operators when a C# developer has to do VB.NET

For example: && vs And.

THEY ARE NOT THE SAME. The && equivalent in VB.NET is AndAlso

If you do this:

Sub Main()
    Dim result = Part1() And Part2()

    Console.ReadLine()
End Sub

Function Part1() As Boolean
    Console.WriteLine("Part 1")
    Return False
End Function

Function Part2() As Boolean
    Console.WriteLine("Part 2")
    Return True
End Function

What do you think it prints?

If And would behave like && in C# it would print

Part 1

But unfortunately BASIC is different (sigh), it prints

Part 1

Part 2

This can be solved by using AndAlso, which is the short circuited version of And.

Entity Framework 4.0 improvements

I’ve been a huge fan of LINQ to SQL. But EF 3.5 (first release) didn’t quite do it for me. I didn’t like the lack of support for foreign keys, no Single() LINQ support,

No Single support

An example of lack of foreign keys:

using(NorthwindEntities northwindEntities = new NorthwindEntities())
{
    Product p = new Product()
                {
                    Category = northwindEntities.Categories.Where(c => c.CategoryID == 1).First,
                    ProductName = "French fries",
                    UnitPrice = 20,
                    Discontinued = false,
                    QuantityPerUnit = "1 kg bag",
                    Supplier = northwindEntities.Suppliers.Where(s => s.SupplierID == 1).First

                };

    northwindEntities.AddToProducts(p);
    northwindEntities.SaveChanges();
}

As you can see you need to fetch  the category and the Supplier seperatly. You can’t just do CategoryID = 1

But when we launch the wizard for EF 4.0 we get this:

Include foreign key columns in the model

Which results in this:

using(NorthwindEntities northwindEntities = new NorthwindEntities())
{
    Product p = new Product()
                {
                    CategoryID = 1,
                    ProductName = "French fries",
                    UnitPrice = 20,
                    Discontinued = false,
                    QuantityPerUnit = "1 kg bag",
                    SupplierID = 1

                };

    northwindEntities.AddToProducts(p);
    northwindEntities.SaveChanges();
}

And there is Single support!

using(NorthwindEntities ne = new NorthwindEntities())
{
    var result = ne.Suppliers.Where(sup => sup.SupplierID == 1).Single();
}

So it just got a little more mature.

I’m hoping to be able to test it (and use it!) more in the future and new projects :)

Hope it helps

-Kristof

VB.NET: getting rid of With statements

Because With clutters your code, you get too much indentations and it removes readability. Now I saw code with 4 to 5 times nested With statements. Pure Horror. So my first job was to remove all these statements. But what if you get ambiguous Methods/Properties?

The documentation is clear on that:

However, because members of outer statements are masked inside the inner statements, you must provide a fully qualified object reference in an inner With block to any member of an object in an outer With block.

This means that in an inner with block you cannot access the members of the outer with block with the . qualified name. You need the full qualified name.

So when you need to de-with your code you start from the outside, taking the most outer block first, solving the references, building, removing the next one, and so on…

But you shouldn’t use With statements in the first place.

Just name your variable if you don’t want to enter the fully qualified name for every statement.

VB.NET, why I don’t like it

A little introduction: I’m working on some legacy VB.NET & Excel project. Pretty old stuff, quite the challenge of getting through the code. I’ve discovered multiple things about VB.NET which I didn’t knew (like the Microsoft.VisualBasic namespace, late binding, Option Strict).

The code I’m working with is cluttered with these kinds of abominations to the .NET framework. The Microsoft.VisualBasic is purely there for legacy VBA developers so that they can easily transfer to VB.NET.

But that doesn’t mean you need to use it in new projects (vbNullString? C’mon…)

I think that this namespace shouldn’t be offered by default anymore in new VB.NET projects. It should be there to merely quickly convert old VBA projects to .NET.

Late binding is another thing which I truly hate. It gets compiled to a bunch of reflection calls which make your code more error prone (no compiler checking!). Although I’ve managed to cast the System.__ComObject to it’s corresponding interface most of the time I had to resort to reflection one time myself (shame shame).

And finally there is this problem of cluttered code, for some reason, when I look at VB.NET code there is SO much text and all the essence get’s lost! While the C# code is so clean.

I will write a post about cleaning up With statements + best practices for nested With Statements from VB.NET code pretty soon.

Multi-Column ListView

Ever wanted to have a multi column ListView in WPF? It’s not that hard once you get to know WPF.

We take a ListView and set the ItemsPanel to UniformGrid:

<Window x:Class="ColumnTest.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="MainWindow" Height="350" Width="525">
    <ListView x:Name="MyList">
        <ListView.ItemsPanel>
            <ItemsPanelTemplate>
                <UniformGrid Columns="3" />
            </ItemsPanelTemplate>
        </ListView.ItemsPanel>
        <ListView.ItemTemplate>
            <DataTemplate>
                <Label Content="{Binding}" />
            </DataTemplate>
        </ListView.ItemTemplate>
    </ListView>
</Window>

 

And the backend code:

using System.Windows;

namespace ColumnTest
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            this.InitializeComponent();

            for (int x = 0; x <= 100; x++)
            {
                this.MyList.Items.Add(x);
            }
        }
    }
}

 

Which results in:

Uniform Grid Result

C# Late binding,

Today I was working on a project in VB.NET, which I had to convert to C#.

The problem was that they used late binding, they knew that some properties on objects existed, but they couldn’t be deduced by the compiler since the object’s type was Object.

For Example, let’s say we want to get the workbook of a chart.

A chart resides in a ChartObject (the chart.Parent) and a ChartObject resides in a Worksheet.

So chart.Parent.Parent returns the corresponding worksheet.

Like this:

Option Strict Off

Imports Microsoft.VisualBasic

Public Class TestClass
    Public Sub Test()
        Dim chart As Excel.ChartObject

        Dim workbook = chart.Parent.Parent
    End Sub

End Class

Now of course, if you compile this, it will work since Option Strict is off.

But when you set it on (or port it to C#) it won’t compile, since chart.Parent returns a type of Object. And the compiler cannot find the .Parent property on that Object, so we would need to cast to ChartArea to get the .Parent property appropriately, and then cast this result to an Excel.Worksheet.

Option Strict On

Imports Microsoft.VisualBasic

Public Class TestClass
    Public Sub Test()
        Dim chart As Excel.ChartObject

        Dim workbook As Excel.WorkSheet = CType(CType(chart.Parent, Excel.Worksheet).Parent, Excel.Workbook)
    End Sub

End Class

This will work.

Unfortunately sometimes there are properties you can set, but they don’t appear in your Intellisense, so you can’t get/set that property.

If you are using VB.NET with Option Strict Off this is no problem, but again: with Option Strict On or in C# it IS a problem.

Since the late binding internally uses Reflection I thought might fetch the PropertyInfo for that particular property and set it that way:

Dim propertyInfo As PropertyInfo = GetType(Excel.Chart).GetProperty("ThePropertyYouWant")

propertyInfo.SetValue(someChart, someValue, BindingFlags.SetProperty, Nothing, Nothing, Nothing)

This won’t work either, since the compiler cannot find the PropertyInfo of that property, it just doesn’t exist. You will get a NullReferenceException. You can only set it at runtime.

The solution is:

GetType(Excel.Chart).InvokeMember("ThePropertyYouWant", BindingFlags.SetProperty, Nothing, someChart, New Object() {someValue})

This way you just say to the runtime: set that value on that property, don’t try to read the metadata first!