DataTable usage

Feb 3, 2016 at 2:54 PM
Hi,

can some one please give me an quick example how to direct use a DataTable with Table Dependency? Something like:
 _dep = New SqlTableDependency(Of DataRow)("MyConnectionString", "MyTable"))
AddHandler _dep.OnChanged, AddressOf Dependency_OnChange
AddHandler _dep.OnError, AddressOf Dependency_OnError
_dep.Start()
...
At the moment i'm getting an syntax error ner NEXT (multiple times). I figured out that the Scripts.CreateTrigger is missing some values so the sql code looks like this (error in the second and sixt line):
[...]
    DECLARE @dmlType NVARCHAR(10)
    DECLARE @modifiedRecordsTable TABLE ([RowNumber] INT IDENTITY(1, 1), )
    IF NOT EXISTS(SELECT * FROM INSERTED)
    BEGIN
        SET @dmlType = 'Delete'
        INSERT INTO @modifiedRecordsTable SELECT  FROM DELETED AS [MyTable]
    END
[...]
Did i misssed something?

Thanks in advance
Coolzer0
Feb 4, 2016 at 5:22 PM
Edited Feb 4, 2016 at 7:16 PM
Hi Coolzer
TableDependency does not work with DataTables.
At least, there is not implementation for it. Is possible to implement but the DataTable must be a Typed DataTable, because the column names can be used to map database table columns.

By the way, you found a bug. When the model passad to TableDependency has no propertyies, the code have to raise an exception.

That if you the sql statement has no columns after SELECT.

INSERT INTO @modifiedRecordsTable SELECT FROM DELETED AS [MyTable]

Bye