The best way to handle BCS without visual studio

How to build a BCS system quickly, and make it flexible and easy to maintain?  No one would doubt that we should use SharePoint designer 2010.  How ever, there are quite a lot of tips (or tricks) we need to keep in mind.

Let's assume there is a scenario which has:

1. Hundreds of tables, views and stored procedures;

2. Some tables and views has more than 30 fields

3. In some tables, there are more than 10 million records;

4. Only need to retrieve less than 2000 records to browser in all cases;

5. Complex SQL functionalities needed;

I prefer to do it this way:

1. Use some tool to generate stored procedures for tables and views;

The freeware Mygeneration is good enough.

Why do we need this step?   I really hate to create all fields for all external content types manually. If we specified improper field type, or made some other minor mistakes accidently, it would take ages to do the trouble shooting.

Be careful of the SQL keyword "TSEQUAL".  It is not supported since SQL 2005.  Please use "=" instead.

2. Create these stored procedures in database;

3. Create external content types and external lists through SharePoint designer;

This include "Fields", "Operations" and "Associations".

SharePoint fields display name need to be specified with "Read Item" operation. The field "Required" property also need to be with "Read Item" operation.

To avoid "Unable to display this Web Part" error, always use "select top 2000 field1, field2, .... from table" in the "Read List" stored procedure. We can do that by modifying the "Mygeneration" template.

Please check here to Customize External List Forms Using Microsoft InfoPath.

4. Create "Profile page" for these external lists if necessary;

5. Create web part pages and then insert BDC web parts into them;

Configure web part connections among web parts if necessary.

6. Done.

The main point of this solution is:  Utilizing the functionalities of SQL Server as much as possible, and reduce the data transfer volume as much as possible. We all know how powerful SQL Server is, and how fast it is compare to SharePoint. So why not move the data access layer and even business logic layer into SQL server?  Although traditional stored procedure is not perfect for business logic layer, but with "Managed stored procedure", it's good enough to almost all systems.

By the way, if really necessary, we still can export the BDC model to visual studio to improve it!  If we can build 95% of external lists in SharePoint, you can image how much time we can save.

If you have any thoughts, please let me know.  I plan to keep improving this post.