Macros beyond the Web: use. NET to develop Access Services event Receivers

Document directory

• Visual Studio 2010 and SharePoint templates
• Attach event to list
• Inter-boundary Communication
• Access client and offline mode
• Concurrency consequences
• Notes
• Conclusion

One challenge facing Access Services is to ensure that the logic behind various objects is portable between Access itself and the Access web database hosted by the SharePoint website. Use macros to implement logic in Access. Specifically, Access Services means that we need to use the VBA behind various objects in the Access content in a Web browser.

Similarly, for Access developers, we need to face new challenges: how to implement the functions provided by clients through macros on the Web?

Fortunately, Richard Fennell, A Visual Studio Team System MVP, once wrote many sharing articles on Access 2010 for us to learn, we can use the SharePoint object model and Visual Studio 2010 to implement the business logic in the application. As the development environment changes to the. NET Framework and standard language, Web databases do not have to be limited by macros. Therefore, it is necessary to create a proof-of-concept demonstration to explain how to use SharePoint events to expand the new Access Web database.

For this reason, I also created a video to work with this blog post. The video lasts for about 10 minutes and can be downloaded from: AccessServicesEventReceiver. mov (64 MB ).

Visual Studio 2010 and SharePoint templates

One of the major enhancements of Visual Studio 2010 is that it is now very simple to operate on SharePoint object models-there are many ready-made templates for different SharePoint applications, including (but not limited to) Web parts, workflow and event receiver. As you can see in the video, Visual Studio 2010 now supports packaging of automated processing solutions, adding them to Feature, and activating them. In the past, these tedious work had to be done manually. Relatively simple installation-use guidgen.exe (part of the Visual Studio tool) to create a new GUID to uniquely identify the Assembly and then determine which lists and events need to be customized, this allows us to focus on coding custom business logic. After that, you can select "Deployment Solution". Visual Studio automatically performs the required steps to update SharePoint so that all components in all solutions are available immediately.

Here we are particularly interested in the "event receiver" template. This template guides you to select a SharePoint website, select a list to associate with the event receiver, and select the event you want to receive to start encoding. Although the event receiver can only be associated with a single list, it supports customizing multiple events on the list. We can add many lists and event receivers to a project.

When we publish an Access web database to a SharePoint site, the web data table is converted to a SharePoint list, so that we can use the SharePoint object model and Visual Studio to customize the operation. Mr. Richard Fennell generously shared his code for associating events on the SharePoint list. He is not a wizard, because the application can only be attached manually. With his code, we can select any Access Services list to associate any events.

Attach event to list

In this demonstration, we want to be able to read data from the MySQL database and write data to the Access web database. We need to be able to implement the same functions in browsers and rich clients at the same time. On the rich client, the solution is simple: directly connect to the table. In browsers, we are restricted by the scope of published applications. Web macros cannot run code or link to external data (Note: Connecting tables in Web databases will be solved in future versions ). Therefore, we will implement the receivers that contain two events: ItemAdding and ItemAdded. Then the event receiver is mounted with a list named "Action. In the first form, the human resources staff will operate on a Filter Form and enter filtering conditions to determine what kind of human resource data subsets should be retrieved from MySQL. When the "LOAD" button is pressed, the new filter conditions are saved in the Action list, triggering the ItemAdding event.

When the C # code in the event is triggered, we can check the information we just added, open a connection to the MySQL database, and pass the filter conditions to the MySQL storage process. Then we can use the DataReader class in this event to read only one human resource record. This completes the ItemAdding event. According to the design, all "-ing" events in the SharePoint object model are executed synchronously, so you must wait for it to complete.

When ItemAdded is triggered, it runs asynchronously in its own thread and is separated from the application thread, both on the client and in the browser. After reading data using DataReader in ItemAdding, the record will be added. Specifically, this means that the user runs the macro in Access Services and returns the command to save the record, including the following BrowseTo macro before opening the form bound to the HR record, you only need to wait for record loading. When the form is loaded to the user's browser, at least one record has been extracted from MySQL to the Access Services list to be browsed and edited by the user. When you browse the HR records, the ItemAdded event is still running. You can click "get more records" to load the New Records added by the ItemAdded event in the form.

The best part of this solution is that you do not need to write code specially for the management thread-this part of work has been processed by SharePoint, reducing the workload of developers, and enables it to focus on the internal business logic of the event. Using Visual Studio 2010's SharePoint enhancement, developing an Access web application SharePoint solution is completely feasible, it is undoubtedly helpful for those who need to go beyond the built-in Web macro for custom development.

Inter-boundary Communication

The next challenge is to find a way to return the progress and final result of. NET code to the application. As noted earlier, the web application cannot interact with the outside world and. NET code is outside. as mentioned above, Web applications cannot interact with the outside world. NET code is an external part. The solution to this problem is to use the Action list to store information, so that the list can be updated continuously during code running. Here is an instance of the Action list:

 

It is displayed in the video.. NET code to obtain a complete personnel record (note that an employee record contains three subrelationships. Therefore, you must also consider obtaining the relevant records of these three sub-tables as the true 'complete'). Before obtaining the next individual record, the operation list is updated and a "fetched" record is recorded. Edge Note: For performance considerations, it is better to update the Action list in batches, instead of adding a single record. This should be considered when the code is transplanted to the production environment.

In the footer of the Web form, links to the returned Action list and different status reports are included, depending on which part of the code is being executed. When the code is run asynchronously, the footer is displayed as follows:

You can press the button to refresh the web form to view the latest records obtained by the code after the last loading. This is basically the same as a static progress bar.

. When the code has completed collecting all matching records, it will update the 'matched' column with the final number to indicate that all the work to obtain records has been completed. The footer will look like this:

This provides a useful feedback for users who want to view the current data, so that they can understand the loading progress. In addition, this is achieved by using macros and checking the rows in the Action list.

Access client and offline mode

Because the event receiver is associated with the SharePoint list, this code runs whether we access the application through a browser or by a client (or any related client. However, the Access client can work offline. When the Access client is in offline mode, this means that the event receiver will not execute until the next synchronization. In this demonstration, this means that the code in offline mode cannot work for the HR user normally, because the event will not be triggered during this process and the code will start to be executed during synchronization. Therefore, we must consider the scenario of running in offline mode in the code. The simplest method is to remind users that this operation cannot be used in offline mode.

Concurrency consequences

One issue that needs to be carefully considered is how to make the code run in a multi-user environment. The SharePoint object model has been carefully designed to adapt to multi-user operations, but developers still have the responsibility to verify the code running in this environment, so that the modification of the list will not interfere with the workflow of other users, at least make sure that no adverse operation is performed. You should also realize that the SharePoint list modification made by the code in the event receiver is visible to any other user, which may be what you expect, or perhaps not.

For simplicity, I chose to use a dedicated table to accept filtering conditions and only insert events, so that I don't have to worry about the two users starting to modify the attachment at a time point, possible update conflicts. Another possible method is to distribute rows to users sequentially, and each user only updates its own rows. In addition, because the purpose of this demonstration is to provide each user with a "workspace" to use their own filters, it is necessary to distinguish the data of the filters from which they belong, and how to process a user request employee record that has been "checked out" by another user. Therefore, in the production environment, I must implement some audits to track the employee table and identify which row is called by which action_id, this effectively associates the Action list items and employee records corresponding to the user's request conditions. This may not be necessary in another different application, but developers still have the responsibility to verify that code runs in a multi-user environment.

Notes

A huge capacity represents a huge responsibility. This is exactly the reason why only one record is loaded in the ItemAdding event. based on the same principle, the Access team limits some operations of the Web macro In the event before data modification: ItemAdding is synchronous, too long operations may cause timeout and other problems. This point is especially obvious when the browser is disconnected-if it cannot get a response from the server, it may time out and the user may need to perform the operation again. This is not a pleasant situation! There is another reason to narrow the Web macro scope: the SharePoint Server is a shared resource, and the poor performance of a solution will deepen the damage to SharePoint performance, endangering the experience of all users. Although the SharePoint Team provides functions similar to the sandbox solution to deal with such scenarios, you cannot always sit on the opposite side of the system administrator and secretly explain to him what you have done!

As you can see in the video, Visual studioenables us to debug and execute the code in a single step by appending the code to an iisjob (w3wp.exe). You can observe the Code running. However, if you set a breakpoint and run the code in one step, the timeout may occur. Access Services disconnects the table in the client and assumes that the recent saving has failed .. After the code is successfully executed in a single step and the Access client has been redirected to the data table, it requires you to retry the previous SAVE operation, but it is actually redundant because the SAVE is successful, the time point for completing the operation is not within the timeout period.

When tracking ItemAdded events (which should be asynchronous execution), another exception event I have observed is that when a breakpoint is added to ItemAdded, the browser will continue to wait until the execution of ItemAdded is completed to trigger the breakpoint. However, if a breakpoint is not set in ItemAdded, the event becomes asynchronously executed.

Conclusion

After all these factors are taken into account, the customer will answer this question: "Can this be done in Access Service ?" We do not have to limit ourselves to Web macros. For the vast majority of solutions, web macros will undertake very important work. In the remaining 10% solution, we can tell the customer that we can rely on. NET to implement it on the other hand.

Although this demonstration provides a method to "Link" to an external data source, and it has been said that this feature will become a standard feature in future versions, it is not its only use. Here are some other possibilities to help you brainstorm:

1) use SQL Server Reporting Services to generate a complex report and send it to users asynchronously through email. They only need to fill out a web form. After a few minutes, the report will appear in their mailbox.

2) connect to the existing SharePoint workflow, so that the Access web application can participate in a long and complex workflow.

3) communication with other Web Services.

4) ________ (wait for you to fill in the blanks)

 

References

Going beyond Web Macros: Using Event Receivers &. NET with Access Services