HOW TO：SQL Server 2008使用行过滤器更改跟踪 [英] HOW TO: SQL Server 2008 Change Tracking with row filter

问题描述

我更新了SQL Server 2008中的跟踪功能和更改跟踪。

以下是Serverside通用数据库适配器的代码
----- -------------------------------------------------- ------------------------------

private SyncAdapter GetDownloadOnlyTableAdapterAssetMaster（ 字符串 tableName， string idColumnName， string [] columnsToSync）

{

//将其命名为与表格相同

SyncAdapter syncAdapter = new SyncAdapter （tableName）;

< font color ="＃008000"size = 2> //获取可在sql语句中使用的字符串

string columnString = GetColumnsString（columnsToSync， " originalTable" ）;

//使用更改跟踪的SQL Server 2008功能。

//没有为表创建特殊列。内置机制

//为我们解决这个问题。使用Changes（）函数，我们可以跟踪

//自上一版本以来已更改的行。

//必须在数据库和单个表范围内启用跟踪

//从服务器中选择插入

SqlCommand incrementalInsertCommand = new SqlCommand （）;

incrementalInsertCommand.CommandText = String .Format（

@"选择{0} FROM {1}作为originalTable

INNER JOIN ChangeTable（更改{1}，{2}）ct

ON ct。{ 3} = originalTable。{3}

其中ct.SYS_CHANGE_OPERATION ='{4}'

" ，columnString，tableName， " @" + SyncSession .SyncLastReceivedAnchor，idColumnName，OPERATION_INSERT）;

incrementalInsertCommand.Parameters.Add（ " @" + SyncSession .SyncLastReceivedAnchor， SqlDbType .BigInt）;

syncAdapter.SelectIncrementalInsertsCommand = incrementalInsertCommand;

}

-------
以下是客户端同步代理代码：

----------------

this .Configuration.SyncParameters.Add（ new SyncParameter （ " @ Param" ， " 1" ））;

--------------


有人可以指导我如何将过滤器添加到服务器端代码以仅下载整个表的子集吗？

谢谢，

---

bhavin

解决方案

您好bhavin，

您过滤数据子集的方式很大程度上取决于您的设置和数据库模型。我们使用像"employee_id"这样的参数。和一些状态标志（"只有员工x的数据，状态> y和< z"）。

你需要做的是首先定义你想要在SyncAgent级别传递的参数（比如你已经在你的示例代码中做了），例如：

this .Configuration.SyncParameters.Add（ new SyncParameter （ < font color ="＃a31515"size = 2>" @ Employee" ， < somevaluehere> ））;

稍后你必须在插入/更新中定义这个参数/删除脚本。为了坚持你的样本，你必须添加类似的东西

"和employee_id = @ Employee"

到SQL命令，然后再次在命令级别定义参数。 >

incrementalInsertCommand.Parameters.Add（ " @ 员工" ， SqlDbType .BigInt）;

HTH，
Andreas

< blockquote dir = ltr style ="margin-right：0px">

Hi,

I am bit new to change tracking features in SQL Server 2008 With Change Tracking.

Following is the code for Serverside Generic DB Adaptor
-------------------------------------------------------------------------------------

private SyncAdapter GetDownloadOnlyTableAdapterAssetMaster(string tableName, string idColumnName, string[] columnsToSync)

{

//name it same as the table

SyncAdapter syncAdapter = new SyncAdapter(tableName);

//get string that can be used in a sql statement

string columnString = GetColumnsString(columnsToSync, "originalTable");

//Using the SQL Server 2008 feature of Change Tracking.

//No special columns are created for the tables. The in built mechanism

//takes care of this for us. Using the Changes() function we can track the

//rows that have changed since the last version.

//Tracking must be enable both at database and individual table scope

//select inserts from the server

SqlCommand incrementalInsertCommand = new SqlCommand();

incrementalInsertCommand.CommandText = String.Format(

@"SELECT {0} FROM {1} as originalTable

INNER JOIN ChangeTable(changes {1}, {2} ) ct

ON ct.{3} = originalTable.{3}

where ct.SYS_CHANGE_OPERATION = '{4}'

", columnString, tableName, "@" + SyncSession.SyncLastReceivedAnchor, idColumnName, OPERATION_INSERT);

incrementalInsertCommand.Parameters.Add("@" + SyncSession.SyncLastReceivedAnchor, SqlDbType.BigInt);

syncAdapter.SelectIncrementalInsertsCommand = incrementalInsertCommand;

}

-------
Following is the client Sync Agent code:

----------------

this.Configuration.SyncParameters.Add( new SyncParameter("@Param","1"));

--------------


Can somebody guide me on how to add filter to server side code to download only subset of whole table ?

Thanks,

---

bhavin

解决方案

Hi bhavin,

the way you filter your data subset strongly depends on your setting and db model. We use parameters like "employee_id" and some state flags ("only data for employee x with state > y and < z").

What you have to do is first define the parameter you want to pass at the SyncAgent level (like you already do in your sample code), e.g:

this.Configuration.SyncParameters.Add(new SyncParameter("@Employee", <somevaluehere>));

Later you have to define this parameter in your insert/update/delete scripts. To stick to your sample you have to add something like

" and employee_id = @Employee"

to the SQL command and afterwards again define the parameter at the command level.

incrementalInsertCommand.Parameters.Add("@Employee" , SqlDbType.BigInt);

HTH,
Andreas

这篇关于HOW TO：SQL Server 2008使用行过滤器更改跟踪的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！