BDC and Table maintenance (editing data)
Good day all,
It is a common requirement in our business to create table (SQL table) maintenance web pages to maintain data associated with inventory planning processes (for example). In our latest project we were planning to extend our custom ASP.Net pages to support more tables. As architect, I wanted to influence the team to move this facility from custom code into our portal platform (MOSS 2007), and BDC seemed the ideal option to replace the custom front end. However, BDC only goes halfway since it doesn't support updates. I've spent the last couple of hours researching this on MSDN and TechNet, and although many folks refer to the idea, there aren't any real-world examples out there.
There seem to be two general trends in thinking/possibilities here:
1. Add Method meta-data to update the source (it seems as if it would be possible to use SQL Update statements in the method definitions, but the only Method type available is GenericInvoker, and there doesn't seem to be any default way to pass the column data to the method parameters?
2. Add Actions to call custom forms/pages that apply updates. This seems like overkill, I might as well keep the current custom ASP.Net solution if I have to go rebuild the forms for updating the data.
Has anyone completed such a solution that is willing to share the configuration? Ideally I would like to be able to allow editing on the list in place and saving the updated data back to the custom table.
-Mike
Hi Mike,
you are right about your two scenarios. The problem with offering simple edits from within the list view is that you cannot implement any business logic and validation that may be very important for the system. I'm currently writing some tutorials on using BDC, and InfoPath 2007, if you're interested in a link let me know.
Many thanks
Nick
http://www.bdcmetaman.com
Speaking to Mike Harrison at the recent SUGUK meeting he indicated that Microsoft were looking at the options for doing 2-way BDC integration. Probably something that will appear in Office 14?
What would be useful would be the ability to have something like the RubyOnRails ActiveRecord implementation that enables very quick data entry/edit UI's to be created. ummm perhaps something to add to my weekend code todo list.
Andrew
Mike,
Watch Rob Bagby's (Developer Evangelist) 'BDC for Developers' web cast and he pointed out that using 'BDC Action' is the way to do write back to backend databases. Your option 1 will not work as it is not supported.
A nice way to do the write back is to create an action to invoke an Infopath form with whatever parameters that you want to pass from your Business Data List. Infopath form can go to your database or web service to fetch the data. You can then do the editing as you wish and then do a 'Submit'. In this case, coding is kept to a minimum.
I discovered that MOSS 2007 actually supports table maintenance directly, without using BDC. Using SharePoint Designer 2007 you can use data sources of many types (SQL Server or ODBC being two), and bind lists directly to the data sources with update-ability. Very easy to configure without code. The MSDN folks need to create some scenario-based how-to's so that you don't have to read through every product API or definition to find the solution. As I searched through MSDN for how to create updateable lists to custom data, BDC popped up right away, but I never discovered that you can do this more directly using data source libraries and SharePoint Designer.
-Mike
