July 30, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Using SQL Server 2008 Change Data

  • April 14, 2009
  • By Jani Järvinen
  • Send Email »
  • More Articles »

Although these functions are fine, it is often more useful to be able to get changes up to a certain date and time. To allow this, SQL Server 2008 supports a function called sys.fn_cdc_map_time_to_lsn which you can use to convert a regular datetime value to an LSN number. For instance, you could use the following statements to retrieve changes up to the current date and time:

DECLARE @from_lsn binary(10),
  @to_lsn binary(10)
SET @from_lsn =
  sys.fn_cdc_get_min_lsn('dbo_MyTable')
SET @to_lsn = sys.fn_cdc_map_time_to_lsn(
  'largest less than', GETDATE());
SELECT
  sys.fn_cdc_map_lsn_to_time(
  __$start_lsn) as changetime,
  [__$operation], CAST(
  [__$update_mask] AS int)
  AS updatemask,
  id, name, value
FROM
  cdc.fn_cdc_get_all_changes_dbo_testtable(
@from_lsn, @to_lsn, N'all')
ORDER BY changetime

The results could then be processed as needed. For example, the sample application shows the rows in a simple DataGridView (see again Figure 3). Here is an example of a C# method that runs the above SQL statement:

private void listChangesButton_Click(
  object sender, EventArgs e)
{
  SqlConnection conn =
    DataAccess.GetSqlConnection();
  try
  {
    string sql = "DECLARE ...";
    SqlDataAdapter da =
      new SqlDataAdapter(sql, conn);
    DataTable changesTable =
      new DataTable();
    da.Fill(changesTable);
    dataGridView.DataSource =
      changesTable;
  }
  finally
  {
      conn.Dispose();
  }
}

If you wanted to make the code more generic, you could construct the SQL statements so that you can take the capture instance name as a parameter. And if you had the need to get changes from certain point of time to another, you could retrieve both LSN numbers with the sys.fn_cdc_map_lsn_to_time function.

Conclusion

In this article, you saw how you can use the change data features in SQL Server 2008 Enterprise Edition. This edition supports two useful, but slightly different techniques called Change Tracking (CT) and Change Data Capture (CDC). Both are able to identify the rows that have changed in selected tables, but CDC is also able to tell how the data has changed.

Although using either of these features requires some setup work from the SQL Server administrator, using them from application code is quite easy. Even if the sample application is written in C#, you can actually use these features from any application - Windows, web or mobile - and any language that can connect to an SQL Server.

With these new change tracking functions, you can speed up your application development and leave the grinding work to the SQL Server.

Good luck with your database applications!


About the Author: Jani Järvinen is a software development trainer and consultant in Finland. He is a Microsoft C# MVP and a frequent author and has published three books about software development. He is the group leader of a Finnish software development expert group at ITpro.fi. His blog can be found at http://www .saunalahti.fi/janij/. You can send him mail by clicking on his name at the top of the article.





Page 4 of 4



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel