Change Data Capture (CDC) in SQL Server 2008

SQL Server 2005 introduced the new features of ‘after update’, ‘after insert’ and ‘after delete’ triggers that almost solved the problem of tracking changes in data. A better solution was introduced in SQL Server 2008 and is called Change Data Capture (CDC). CDC has allowed SQL Server developers to deliver SQL Server data archiving and capturing without any additional programming.


Enabling Change Data Capture on a Database:

first you need to know if the database enabled
using this SQL statement you can know,

USE masterGO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases

You can run the CDC at database level

USE NorthwindGO
EXEC sys.sp_cdc_enable_db
GO

some system tables will be created into the database

the cdc.captured_columns return list of captured columns
the cdc.change_tables reurn list of all tables enabled with capture
the cdc.ddl_history contains the history of the ddl changes scince the capture data enabled
Enable the CDC on one or more than one table

USE Northwind
Go
SELECT [Name], is_traced_by_cdc FROM sys.tables
Go

to enable the CDC on the tables you must make sure that the SQL Server Agent is running
when the CDC is enabled on tables, it will create two jobs and they will use the SQL Server Agent
If everything goes right, you will find inside the system tables a new table with this cdc.dbo_Categories_CT
inside this table you will find five columns important to you

• __$start_lsn
• __$end_lsn
• __$seqval
• __$operation
• __$update_mask

There are two values are very important _$operation and _$update_mask
inside the _$operation there are some values
1. Delete Statement =1
2. Insert Statement = 2
3. Value before update = 3
4. Value after update = 4
The _$update_mask shows which columns where updated.
Now running the following

SELECT *
FROM Categories
GO
SELECT * from cdc.dbo_Categories_CT
Go

Insert into the table and run the above query again

Now you can find the Primary key of your original table into the new created table by CDC you can create a simple select to get all the rows from the CDC tables where they equal your primary key

SELECT * FROM cdc.dbo_Categories_CT
where cdc.dbo_Categories_CT.CategoryID = 9

And because this is insert statement then the _$operation = 2
Now to create an update statement

UPDATE [Northwind].[dbo].[Categories]
SET [CategoryName] = 'New Refat'
Description = 'something'
WHERE Categories.CategoryID = 9
UPDATE [Northwind].[dbo].[Categories]
SET [CategoryName] = 'New Refat',
Description = 'something else'
WHERE Categories.CategoryID = 9

I updated the record 2 times, the _$operation = 3 is the record before update and as you can see it will be the inserted record all the time,
and the _$operation = 4 is the record after update
Understanding Update mask:
the value of the _$update_mask is a hexadecimal stored into the field but it is a binary
For the insert and the delete the after converting the hexadecimal to a binary you will find the all the value is 0b1111 for exampleJ, this means all the columns modified.
In the update statement the binary value will contains 1’s and 0’s the from the right the 1’s means columns updated and the 0’s means there is no changes into these columns.
Also there is two columns _$start_lsn and _$end_lsn these two values are the log Sequential Number. This number is associated with committed transaction of the DML operation on the tracked table.
Disabling Change Data Capture on a table
to disable the CDC, you can do this over two levels on database level or the table level.
You need 3 things to disable the CDC, source_schema, source_name and capture_instance
It is easy to get the source_schema, source_name but it is hard to know the capture_instance
use this to get the capture_instance

You can disable as I said the CDC on the table level

USE Northwind;
GO
EXECUTE sys.sp_cdc_disable_table
@source_schema = N'Categories',
@source_name = N'dbo',
@capture_instance = N'dbo_Categories';
GO

And you will see the table created for the CDC will be droped
To disable the CDC on the Database level use this

USE Northwind;
GO
EXEC sys.sp_cdc_disable_db
GO

Capture Selected Column
CDC can be used for columns also
First I will enable the CDC on my Database again

USE Northwind;
GO
EXEC sys.sp_cdc_enable_db
GO

Now I will use the CDC on the columns level

USE Northwind;
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Categories',
@role_name = NULL,
@captured_column_list = '[CategoryName],[Description]'
GO

Expand only the selected node in asp.net treeview

well, I created a treeview, but when I try to expand only one node, I faced some problems,
so I try to write this article, to show you how easy you can do this. 

here is the HTML code

<asp:treeview ID="tv" runat="server" ShowLines="True" Width="200px">
            <Nodes>
                <asp:TreeNode Text="Item1" Value="Item1"></asp:TreeNode>
                <asp:TreeNode Text="Item2" Value="Item2">
                    <asp:TreeNode Text="item21" Value="item21"></asp:TreeNode>
                    <asp:TreeNode Text="item22" Value="item22"></asp:TreeNode>
                </asp:TreeNode>
                <asp:TreeNode Text="item3" Value="item3">
                    <asp:TreeNode Text="item31" Value="item31">
                        <asp:TreeNode Text="item311" Value="item311"></asp:TreeNode>
                    </asp:TreeNode>
                </asp:TreeNode>
            </Nodes>
        </asp:treeview>

first I want to collapse all of the nodes, this by using the ExpandDepth property ExpandDepth="0" 

now, using the TreeNodeExpanded event I will collapse all the nodes then open the expanded one,
but you must know that when you call the Expand method it will call the TreeNodeExpanded event again, so there will be an infinite loop.

we need to do something before expanding the node.
I will create a bool property to mark that the treeview control has already collapsed once.

the codes looks like

private bool tvCollapsed
    {
        get
        {
            if (ViewState["tvCollapsed"] != null)
                return (bool)ViewState["tvCollapsed"];
            return false;
        }
        set
        {
            ViewState["tvCollapsed"] = value;
        }
    }

and here is the code that will be used to do the required task

protected void tv_TreeNodeExpanded(object sender, TreeNodeEventArgs e)
    {
        if (!tvCollapsed)
        {
            tv.CollapseAll();
            tvCollapsed = true;
        }

      ExpandNode(e.Node);
    }

    private void ExpandNode(TreeNode node)
    {
        node.Expand();
        if (node.Parent != null)
            ExpandNode(node.Parent);

        tvCollapsed = false;
    }

At least one object must implement IComparable

At least one object must implement IComparable

First of all, if there is any one doesn’t like this kind of emails, please feel free to say so in a private email , and I will not send again to him/her any more J

now, under the namespace System.Collections.Generic, there is more than one class that I love, and really I mean it, I love them all, one of them is SortedDictionary class.

The SortedDictionary is a Key/Value pairs sorted on the key, the key and the value can be any object type.

I created a simple class and I want its objects to be added in to a SortedDictionary object as a key.
/// my class
class MyClass

    {

        private string _myClassName;

        public string MyClassName

        {

            get { return _myClassName; }

            private set { _myClassName = value; }

        }

        public MyClass(string className)

        {

            MyClassName = className;

        }

    }

 /// adding my class to the SortedDictionary Object
MyClass mc = new MyClass("myClassName");

MyClass anotherMc = new MyClass("myClassNameAgain");

SortedDictionary<MyClass, string> SD = new SortedDictionary<MyClass, string>();

SD.Add(mc, mc.MyClassName);

SD.Add(anotherMc, anotherMc.MyClassName);

The first item will be added smooth, there is no complain from the compiler, the second Item the compiler will rise an argument exception “At least one object must implement IComparable”

First we have to understand why this exception is raised?
When we added the first item “mc” there was no other items into the SortedDictionary object, so the SortedDictionary object does not need to sort anything yet. But when we try to add the next item “anotherMc” , the SortedDictionary object try to sort them, actually it try to compare between them.
But I didn’t provide any method to compare between 2 objects from my class.
So the compiler asked me to implement IComparable Interface.

My class after implementing IComparable Interface

  class MyClass : IComparable<MyClass>

    {

        private string _myClassName;

        public string MyClassName

        {

            get { return _myClassName; }

            private set { _myClassName = value; }

        }

        public MyClass(string className)

        {

            MyClassName = className;

        }

        #region IComparable<MyClass> Members

        public int CompareTo(MyClass other)

        {

            if (Equals(other))

                return 0;

            else

                return MyClassName.CompareTo(other.MyClassName);

        }

        #endregion

    }

No I will define the equality if the same object has been added twice
or the name of the first object MyClassName are the same as the second object.

now you can add your objects into the sortedDictionary collection class 

Invalid Cast Exception Between an Inherited Class and Its Base

Invalid Cast Exception Between an Inherited Class and Its Base

Problem :  I want to cast my object to its base class
Parent  p  = (Child) c; // this can be done
Child c  = (Parent)  p; // this can’t be done

/// Parent class
public class Parent

    {

        public string Name { get; set; }

    }

/// Child class
public class Child : Parent

    {

        public string Extrainfo { get; set; }

    }

Well to convert between two classes you need to define  implicit operator and it must be static

So the Child class will be

///Child class

public class Child : Parent

    {

        public static implicit operator Parent(Child c)

        {

            return new Parent();

        }

    }

But the compiler will raise an error “user-defined conversions to or from a base class are not allowed”
the compiler prevent the conversions to or form a base class L

To solve this you need to add a new constructor to your derived class (Child) and pass the parent into this constructor and by using this Key word
you can copy your information from the base class to the derived class.

so the new child object will have all the information from the base class and will also have the default values for the extra properties into the child class.

/// Child class
public class Child : Parent

    {

        public string Extrainfo { get; set; }

        public Child()

        {

        }

        public Child(Parent p)

        {

            this.Name = p.Name;

        }
}

class Program

    {

        static void Main(string[] args)

        {

            Parent p = new Parent();

            p.Name = "something";

            Child c = new Child(p);           

        }

    }