SqlDependency + SignalR in ASP.NET MVC 5 (edit)
Based on this articles:
https://www.codeproject.com/Tips/1075852/ASP-NET-MVC-SignalR-SqlDependency-and-EntityFramew
https://drive.google.com/file/d/115P2kaeFtScZP7Sn2gaQXTV2Tlj62hu7/ (v1)
https://drive.google.com/file/d/10jM9RVklYbAn_FRVT61y8cnuecnyl5Fj/ (v2)
https://github.com/mewanindula/SignalR-SQL_Dependency/
ALTER DATABASE [SignalRDemo] SET ENABLE_BROKER;
Errors:
Error 1: The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported. Please enable the Service Broker for this database if you wish to use notifications.
Error 2: The model backing the 'SignaRTestContext' context has changed since the database was created. Consider using Code First Migrations to update the database (http://go.microsoft.com/fwlink/?LinkId=238269).
How to enable, disable and check if Service Broker is enabled on a database?
To enable Service Broker run:
ALTER DATABASE [Database_name] SET ENABLE_BROKER;
alter database [<dbname>] set enable_broker with rollback immediate;
If the SET ENABLE_BROKER hangs and never completes read the previous article:
SET ENABLE_BROKER never completes in SQL Server
If you get an error when trying to enable Service Broker read the related blog post:
SQL Server Service Broker cannot be enabled – error
To disable Service Broker:
ALTER DATABASE [Database_name] SET DISABLE_BROKER;
To check if Service Broker is enabled on a SQL Server database:
SELECT is_broker_enabled FROM sys.databases WHERE name = 'Database_name';
How to Enable-Migrations
PM> Enable-Migrations
Checking if the context targets an existing database...
Detected database created with a database initializer. Scaffolded migration '202005240124271_InitialCreate' corresponding to existing database. To use an automatic migration instead, delete the Migrations folder and re-run Enable-Migrations specifying the -EnableAutomaticMigrations parameter.
Code First Migrations enabled for project SignalRWebApp.
PM> Update-Database -Verbose
Using StartUp project 'SignalRWebApp'.
Using NuGet project 'SignalRWebApp'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'SignalRDemo' (DataSource: Localhost, Provider: System.Data.SqlClient, Origin: Configuration).
No pending explicit migrations.
Applying automatic migration: 202005240709176_AutomaticMigration.
ALTER TABLE [dbo].[Products] ADD [IsActive] [bit] NOT NULL DEFAULT 0
ALTER TABLE [dbo].[Products] ADD [IsDeleted] [bit] NOT NULL DEFAULT 0
ALTER TABLE [dbo].[Products] ADD [Created] [int] NOT NULL DEFAULT 0
ALTER TABLE [dbo].[Products] ADD [CreatedBy] [datetime] NOT NULL DEFAULT '1900-01-01T00:00:00.000'
ALTER TABLE [dbo].[Products] ADD [Modified] [int] NOT NULL DEFAULT 0
ALTER TABLE [dbo].[Products] ADD [ModifiedBy] [datetime] NOT NULL DEFAULT '1900-01-01T00:00:00.000'
INSERT [dbo].[__MigrationHistory]([MigrationId], [ContextKey], [Model], [ProductVersion])
VALUES (N'202005240709176_AutomaticMigration', N'SignalRWebApp.Models.SignaRTestContext', 0x1F8B0800000000000400CD58DB6EE336107D2FD07F10F4D402593397976D20EFC2B193C2E826F146DEDD675A1A3B44295225A9C0FEB67DE827F5173AD4FDE29B9C2C5A0408ACD1F0CCE1903C3CF63FDFFFF63EAE23EEBC80D24C8AA17B3138771D10810C99580DDDC42CDFBD773F7EF8F927EF368CD6CED722EFCAE6E148A187EEB331F135213A788688EA41C40225B55C9A4120234243492ECFCF7F2317170410C2452CC7F19E12615804E9033E8EA508203609E5F73204AEF338BEF15354E78146A0631AC0D0F5D94A50FEF40D16A3381E64F9AE33E28C22171FF8D275A810D250834CAFBF68F08D9262E5C718A07CBE8901F396946BC867705DA51F3B99F34B3B19520D2CA082441B19F504BCB8CABB43DAC34FEAB15B760FFB778B7D361B3BEBB4874377A6649804C675DAB5AEC75CD9BCED1D1EE4E3CE9CC6DBB3724BE0CEB17F67CE38E126513014901845F999334B169C057FC0662EFF04311409E7758A4812DF350218C2723128B379826593F874E23AA4399CB4C797A3BB43B3194E85B9BA749D07A442171CCAFD50EB866FA482DF4180A206C2193506142EE73484B4A31D12AD92F67F510D37209E26D7B9A7EB4F2056E679E8E247D7B9636B088B48CEE08B6078F8709051091C2A82C966A65850569A40C022CA5D67A6F0537EA4DFBB8E1F503BCB6D53DE5FE17342F3E9FEA002533D0A0C7B2967702325072A4EC09900075CA9D7028D15D01ACCAE9D7214C84DD5377C9C33BB257A22E1E9634BF65A3E054A6F421EA904A42B2B28DB86323C227999541A9EE6A08D7D03EB6D2A83829C0B8DCE3779937986EC83699E5D94F88A49A6F38352CAB6312EB9557709C92E93E2D2213B6E1DEF9EC6311ED8DA2D94471C3FBB82C6EFFCFECA1C651824D05B04BA645B5642F9A12B68BDC5D2C8F48E296D7001E982DAF51A875127ADBB123BBA5CD46B35BB2DC755EF8B01F6736DD5775C176DA0AA977738BD08B5349D29947CAA0BAA333275039453B55BDDC792279138705FECC3CA64BB0E93458E47A869721DA6163E1EAB52DF3A54153D1EA992D93A5215ED83540A6D13AA0C1F8F556A6D1DA90CF6C6B9D96C45BAE9D5A94A70EB5055B43F529B563DDE45F348EB80B48F22E99CC5961B6A9FED7DE2D84E29AB9722D912432F17A6C33EBDA354598AB50EF28585A94A6DB48168601306FE5F7CCC19CEB74AB8A7822D51C232DFE8A251BE6C19FDFF8FE9265A87FC38E7FD1F7B5F665B7CD0DDF6341875BB2B5EA80A9EA9FA25A2EB5FEB482759DAF0475BDA372FD0B6B40B665E6F674F016959D974E15F6B64437C346F61644F61D3B5B147D2E96763BB1EEB288BBACFA166DA877C1712A9673C2B677BA27FED4AB147EA3FAC7813D0E8CD4A08FB338B80C06A5C055AE44CC55216EDC6A9D5191529EDD50043B1FF74A40C5BD2008FAD0C40EBF4ABEE57CA134CB98D16104EC56362E2C48CB48668C11BB79E47F6D74F4D7A93B3F718DB27FD1653409ACC6EA1477193301E96BCEFB66CA11D1076B3E4428AACF0AB3EC2AD3625D283144702E5ED9B400CC2CAF01CA29823987E143EB56AD29F1B7ED7FA042B1A6C8A1B7537C8E18568B6DD9B30BA5234D2394635DEFE5848ECAF851FFE0559DE863A5F140000 , N'6.1.3-40302')
Running Seed method.
PM> Add-Migration Change_CRUD_Fields
Scaffolding migration 'Change_CRUD_Fields'.
The Designer Code for this migration file includes a snapshot of your current Code First model. This snapshot is used to calculate the changes to your model when you scaffold the next migration. If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running 'Add-Migration Change_CRUD_Fields' again.
PM> Error
Solution Explorer > Delete code in Migrations folder
PM> Enable-Migrations -Force
Checking if the context targets an existing database...
Code First Migrations enabled for project SignalRWebApp.
PM> Update-Database –Verbose
Using StartUp project 'SignalRWebApp'.
Using NuGet project 'SignalRWebApp'.
Specify the '-Verbose' flag to view the SQL statements being applied to the target database.
Target database is: 'SignalRDemo' (DataSource: MANHNV83\MSSQL14, Provider: System.Data.SqlClient, Origin: Configuration).
No pending explicit migrations.
Unable to update database to match the current model because there are pending changes and automatic migration is disabled. Either write the pending model changes to a code-based migration or enable automatic migration. Set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.
You can use the Add-Migration command to write the pending model changes to a code-based migration.
PM>
Code First Approach
https://dzone.com/articles/steps-to-add-new-field-with-code-first-approach-in
Sql column default value
https://stackoverflow.com/questions/27038524/sql-column-default-value-with-entity-framework
Knowledge Base: Limitations of SQL Dependency
https://techbrij.com/database-change-notifications-asp-net-signalr-sqldependency
https://stackoverflow.com/questions/7588572/what-are-the-limitations-of-sqldependency
The most complete list I can find (from here) is as follows:
- The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
- The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
- The statement may not use unnamed columns or duplicate column names.
- The statement must reference a base table.
- The statement must not reference tables with computed columns.
- The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
- A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
- The statement must not include PIVOT or UNPIVOT operators.
- The statement must not include the UNION, INTERSECT, or EXCEPT operators.
- The statement must not reference a view.
- The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
- The statement must not reference server global variables (@@variable_name).
- The statement must not reference derived tables, temporary tables, or table variables.
- The statement must not reference tables or views from other databases or servers.
- The statement must not contain subqueries, outer joins, or self-joins.
- The statement must not reference the large object types: text, ntext, and image.
- The statement must not use the CONTAINS or FREETEXT full-text predicates.
- The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
- The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
- The statement must not use any nondeterministic functions, including ranking and windowing functions.
- The statement must not contain user-defined aggregates.
- The statement must not reference system tables or views, including catalog views and dynamic management views.
- The statement must not include FOR BROWSE information.
- The statement must not reference a queue.
- The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).
- The statement can not specify READPAST locking hint.
- The statement must not reference any Service Broker QUEUE.
- The statement must not reference synonyms.
- The statement must not have comparison or expression based on double/real data types.
- The statement must not use the TOP expression.
Additional reference:
SignalR Database Update Notifications in ASP.NET MVC using SQL Dependency
https://www.codeproject.com/articles/874240/signalr-database-update-notifications-in-asp-net-m
https://github.com/venkatbaggu/signalrdatabasenotifications (HAY HAY HAY)
Showing Data Changes Using SQLDependency With SignalR In ASP.NET MVC
https://www.c-sharpcorner.com/blogs/showing-data-changes-using-sqldependency-with-signal-r
Database Change Notifications In ASP.NET (MVC) Using SignalR & SQL Dependency
https://github.com/mewanindula/SignalR-SQL_Dependency (HAY HAY HAY)
Real-time applications with SignalR and SQLDependency in ASP.NET MVC
Track changes in records in the database using SignalR and SQLTableDependency
http://programmersought.com/article/78561358427/
Hangfire
Hangfire là một trong những cách dễ nhất để thực hiện xử lý nền trong ứng dụng .NET và .NET Core.
https://helpex.vn/article/cach-tich-hop-hangfire-voi-asp-net-core-1-1-5c54c69d507419248c9af1c3
https://www.dotnetcoban.com/2019/08/introduction-to-hangfire.html
https://nhatkyhoctap.blogspot.com/2017/10/hangfire-thuc-thi-background-task-voi.html
https://www.hocviendaotao.com/2015/11/hangfire-cong-cu-tao-tien-trinh-ngam.html
Quartz.net
https://www.quartz-scheduler.net/documentation/quartz-2.x/tutorial/using-quartz.html
https://www.codeproject.com/Articles/860893/Scheduling-With-Quartz-Net
https://bytefish.de/blog/scheduling_with_quartz_net/
Quartz 2.4.1
Làm sao để tạo một task chạy theo lịch trong ứng dụng web?
https://tech.ebayinc.com/engineering/performance-tuning-on-quartz-scheduler/
Tôi đã cài đặt gói Quartz 2.4.1 vì tôi muốn một trình lập lịch nhẹ trong ứng dụng web của mình mà không cần cơ sở dữ liệu SQL Server riêng.
Tôi cần lên lịch cho các phương pháp
- SendUserEmails để chạy mỗi tuần vào Thứ Hai 17: 00, Thứ Ba 17:00 và Thứ Tư 17:00
- SendAdminEmails để chạy mỗi tuần vào Thứ Năm 09:00, Thứ Sáu 9:00
Tôi cần mã nào để lên lịch cho các phương thức này bằng Quartz trong ASP.NET Core? Tôi cũng cần biết cách khởi động Quartz trong ASP.NET Core vì tất cả các mẫu mã trên internet vẫn đề cập đến các phiên bản trước của ASP.NET.
