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;
    }