tomasfabian / joker Goto Github PK
View Code? Open in Web Editor NEWReactive data changes from SQL server to .NET clients. SqlTableDependency extensions, Joker.OData, Joker.Redis, Joker.MVVM and ksqlDB LINQ provider
License: MIT License
Reactive data changes from SQL server to .NET clients. SqlTableDependency extensions, Joker.OData, Joker.Redis, Joker.MVVM and ksqlDB LINQ provider
License: MIT License
Hello Tomas,
In your "temp fix" to IsNemoEqualTrue/monitor-table-change-with-sqltabledependency#188
I see the only difference was to disable dropping all objects when there are no conversation handlers anymore, as that was causing a deadlock, e.g. you removed the below clauses:
DECLARE @conversationHandlerExists INT
SELECT @conversationHandlerExists = COUNT(*) FROM sys.conversation_endpoints WHERE conversation_handle = '{19}';
IF @conversationHandlerExists = 0
BEGIN
{20}
RETURN
END
the fix works great, but for cases when there is a transaction that goes beyond watch dog timeout, users still receive a blocking exception "The conversion handle XXX is not found", like here IsNemoEqualTrue/monitor-table-change-with-sqltabledependency#214
What do you think about returning back the clauses above with a single change:
DECLARE @conversationHandlerExists INT
SELECT @conversationHandlerExists = COUNT(*) FROM sys.conversation_endpoints WHERE conversation_handle = '{19}';
IF @conversationHandlerExists = 0
BEGIN
-- doing nothing, just quit
RETURN
END
e.g. for cases when we have some dropped conversation handlers but triggers are still staying in DB for some reason (or they appear to be present in DB in the scope of that running long-term transaction), we just silently skip any logic for that trigger.
As a result, users are not blocked from doing updates to the table?
Thanks!
Not an issue, per se.
We are using SqlTableDependency have an issue with the drop and recreate of triggers and broker items each time it "hiccups" A windows service makes use of the project to listen to the broker. If our service idels or drops and has to be restarted, the drop and create from SqlTableDependecny fires...during that time, the db is nearly unusable. Your extension mentions the ability to maintain that conversation handle. Would your extension help us in those situations?
Hi There,
I'm following your examples and have tried looking though the source code for some answers, but I don't seem to be able to receive an event with just the ID of the target Table.
If I comment out the EXCEPT statement in the trigger created I can get my desired results.
Is there a way to do this programmatically?
Hi Tomas,
I use Extensions 2.3 and it works fine if I override the attribute TableName within the SqlTableDependencyProvider<> derivate with the real table name respectively the settings.SchemaName with the real schema name, because my Entity-class is named different to the table name because of different convention (camel case vs. big case, English vs. German).
I used before the original SqlTableDependency. There it was possibe to define the table and schema and column namnes as attributes in the entity class. This brings all definition at one location together. Could you implement it for the Extensions, too?
Thx,
Tobi
In that moment it is not possible to get the former data row state during an OnUpdate event, even if the IncludeOldValues settings attribute is set true. Is it possible to fix that?
Hi
The sql server objects need to get disposed immediately when I call the dispose method from the application, this does not seem to work at all and it looks like it waits for the watch dog to do the clean up instead. Please can I get some insight on this please?
Rajath
Great work on this extension, keep it going.
With the sqltabledependency constructor I could pass the schema name of the table as a parameter. This seems to not be possible with the sqltabledependencyprovider abstract class.
I may be wrong, please can you suggest a way to do this?
Hi
I had a situation recently where the SqlTableDependencyOnError method was executed but the OnError method which takes in an exception object was never executed when the application abruptly stopped.
This OnError method is important as I have an event source which I log into the windows event viewer for SCOM updates.
Thanks,
Rajath
Hello,
I'm the same person that reported before on the topic:
SqlTableDependency Recover Connection (Reconnecting)
First, I'm happy that I was the origin of your contribution, I want to thank you for this nice work.
I just have tested it right now, and I want to inform you that using the basic usage, if something is wrong on StringConnection or the Service Broker is not enabled, we didn't get an exception, it just not give up an notification.
Thank you again for this nice work.
UseAuthorization should be called between UseRouting and UseEndpoints:
An unhandled exception has occurred while executing the request.
System.InvalidOperationException: Endpoint /dataChangesHub/negotiate contains authorization metadata, but a middleware was not found that supports authorization.
Configure your application startup by adding app.UseAuthorization() inside the call to Configure(..) in the application startup code. The call to app.UseAuthorization() must appear between app.UseRouting() and app.UseEndpoints(...).
at Microsoft.AspNetCore.Routing.EndpointMiddleware.ThrowMissingAuthMiddlewareException(Endpoint endpoint)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)
at Microsoft.AspNetCore.Routing.EndpointRoutingMiddleware.Invoke(HttpContext httpContext)
at Joker.OData.Middleware.Logging.ErrorLoggerMiddleware.Invoke(HttpContext context) in C:\Github\SqlTableDependency.Extensions\Joker.OData\Middleware\Logging\ErrorLoggerMiddleware.cs:line 23
at Microsoft.AspNet.OData.Batch.ODataBatchMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
Database objects are still deleted after disconnection。
Infomation of data change still lost between disconnection time.
I have derived SqlTableDependencyProvider<T>
with Unique Scope and overrided OnCreateSettings()
as follow :
protected override SqlTableDependencySettings<TEntity> OnCreateSettings()
{
var settings = base.OnCreateSettings();
using var serviceScope = _scopeFactory.CreateScope();
var findEntityType = serviceScope.ServiceProvider.GetRequiredService<TContext>()
.Model.FindEntityType(typeof(TEntity));
var dmlTriggerType = ConfigurationProfiles.Select(e => e.DmlTriggerType)
.Aggregate((t1, t2) => t1 | t2);
settings.IncludeOldValues = true;
settings.NotifyOn = dmlTriggerType;
settings.FarServiceUniqueName = serviceScope.ServiceProvider.GetRequiredService<IConfiguration>().GetSection("FarServiceUniqueName").Value;
settings.SchemaName = findEntityType.GetSchema();
settings.TableName = findEntityType.GetTableName();
settings.TimeOut = (int)TimeSpan.FromDays(5).TotalSeconds;
settings.WatchDogTimeOut = (int)TimeSpan.FromDays(6).TotalSeconds;
return settings;
}
But when I start monitoring with SubscribeToEntityChanges();
It seems that SqlTableDependency is instancied as follow :
case LifetimeScope.UniqueScope:
SqlTableDependencyWithUniqueScope<TEntity> sqlTableDependency = new SqlTableDependencyWithUniqueScope<TEntity>(this.connectionString, this.TableName, this.SchemaName, modelToTableMapper, settings.UpdateOf, settings.Filter, settings.NotifyOn, settings.ExecuteUserPermissionCheck, settings.IncludeOldValues);
sqlTableDependency.Settings = this.Settings;
return (ITableDependency<TEntity>) sqlTableDependency;
But the base constructor at the first instruction doesn't know FarServiceUniqueName
at the time leading base constructor to store an outdated value wich is not refreshed even if Settings are set afterward
protected TableDependency(
string connectionString,
string tableName = null,
string schemaName = null,
IModelToTableMapper<T> mapper = null,
IUpdateOfModel<T> updateOf = null,
ITableDependencyFilter filter = null,
DmlTriggerType dmlTriggerType = DmlTriggerType.All,
bool executeUserPermissionCheck = true)
{
...
this._dataBaseObjectsNamingConvention = this.GetBaseObjectsNamingConvention();
...
}
Setting should be passed to SqlTableDependencyWithReconnection
constructor with an optional aditionnal parameter added and _dataBaseObjectsNamingConvention
should be reset
public SqlTableDependencyWithReconnection(
string connectionString,
string tableName = null,
string schemaName = null,
IModelToTableMapper<TEntity> mapper = null,
IUpdateOfModel<TEntity> updateOf = null,
ITableDependencyFilter filter = null,
DmlTriggerType notifyOn = DmlTriggerType.All,
bool executeUserPermissionCheck = true,
bool includeOldValues = false,
SqlTableDependencySettings<TEntity> settings)
: base(connectionString, tableName, schemaName, mapper, updateOf, filter, notifyOn, executeUserPermissionCheck, includeOldValues)
{
Settings = settings;
_dataBaseObjectsNamingConvention = GetBaseObjectsNamingConvention();
}
Or reset the value properly before Start
call
A VARCHAR(MAX) becomes like:
DECLARE @modifiedRecordsTable TABLE ([RowNumber] INT IDENTITY(1, 1), [Note] VARCHAR(-1) ....
Hello,
We are trying to use your extension to solve the issue with bad triggers / deadlock with original SqlTableDependency component.
IsNemoEqualTrue/monitor-table-change-with-sqltabledependency#188
And after preliminary testing we see it's working just great.
The only issue we have is an exception that is thrown in Dispose method that is currently implemented like:
protected override void Dispose(bool disposing)
{
if (LifetimeScope != LifetimeScope.UniqueScope)
DropDatabaseObjects();
base.Dispose(disposing);
}
for main Dispose call it works fine, but when a container disposes objects, the following is called:
~TableDependency() => this.Dispose(false);
and we get "Handle is not initialized" from System.Data.ProviderBase.DbConnectionInternal.PostPop exception, most probably because parent object is already disposed.
So I think the proper implementation of that Dispose should include disposing check:
protected override void Dispose(bool disposing)
{
if (disposing && LifetimeScope != LifetimeScope.UniqueScope)
DropDatabaseObjects();
base.Dispose(disposing);
}
Could you please check the issue?
Thank you!
On the Kafka.DotNet.ksqlDB.KSql.Linq
package, are there any plans to support CREATE OR REPLACE STREAM
statements when using CreateQueryStream<T>()
?
I tried to find a way to pass some kind of parameter but couldn't find one.
Thanks!
I am using SqlTableDependency.Extensions on a .NET Framework 4.8 web MVC application.
I am using a SqlTableDependencyProvider with a lifetime UniqueScope (with a fixed FarServiceUniqueName).
On application restart, the new SqlTableDependencyProvider causes an SQL Exception: "There is already an object named 'dbo_MyTable_MyProjet/StartMessage/Insert' in the database. This causes a crash on my app, since I am doing these dependencies on Application_Start().
Shouldn't the Provider check for these objects in the database, and reuse them?
("If the application was closed without cleaning the conversation, it will be reused upon app restart")
I have a .NET application, with SignalR integration.
I am using SqlTableDependency.Extensions and using the UniqueScope for the Lifetime. However I want to change the name of the objects on my database (trigger, service, contracts, SP, ...).
protected override SqlTableDependencySettings<Notification> OnCreateSettings()
{
var settings = base.OnCreateSettings();
settings.FarServiceUniqueName = "ProjectName";
settings.IncludeOldValues = true;
return settings;
}
The code above doesn't appear to change the objects name on the database, and the Environment.MachineName is what is being considered. Should I add anything else to the settings object in order for the database objects to have the FarServiceUniqueName?
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.